Oracle 18c:

I have SDO_GEOMETRY objects:

create table test_table (shape sdo_geometry);

insert into test_table (shape) values (sdo_geometry('linestring(10 20, 30 40, 50 60)'));
insert into test_table (shape) values (sdo_geometry('linestring(70 80, 90 100)'));
insert into test_table (shape) values (sdo_geometry('linestring(110 120, 130 140, 150 160, 170 180)'));

select
    (shape).sdo_ordinates as sdo_ordinate_array
from
    test_table


SDO_ORDINATE_ARRAY
------------------
MDSYS.SDO_ORDINATE_ARRAY(10, 20, 30, 40, 50, 60)
MDSYS.SDO_ORDINATE_ARRAY(70, 80, 90, 100)
MDSYS.SDO_ORDINATE_ARRAY(110, 120, 130, 140, 150, 160, 170, 180)

I want to extract the ordinates from the objects as comma delimited strings:

ORDINATES
------------------------------
10,20,30,40,50,60
70,80,90,100
110,120,130,140,150,160,170,180

I want to perform the extraction using a function — so that I can use the function in a function-based index.

Example:

create index ordinates_string_idx on test_table my_owner.ordinates_to_varchar2(shape);

What I tried:

I'm able to convert the array to a string using an SQL query:

select
    ordinates
from
    test_table t
       cross join lateral (
         select listagg(column_value, ',') within group (order by rownum)
                  as ordinates
         from   table(t.shape.sdo_ordinates)
       ) s

ORDINATES
------------------------------
10,20,30,40,50,60
70,80,90,100
110,120,130,140,150,160,170,180

But I'm not sure how to migrate that functionality to a function.

I tried using the query inside a function:

with function ordinates_to_varchar2(shape in sdo_geometry) return varchar2 
deterministic is 
    v_ordinates varchar2(32767);
begin
    select
        ordinates into v_ordinates
    from
        test_table t
           cross join lateral (
             select listagg(column_value, ',') within group (order by rownum)
                      as ordinates
             from   table(t.shape.sdo_ordinates)
           ) s;    
    return v_ordinates;
end;

select
    ordinates_to_varchar2(shape)
from
    test_table

But I got an error:

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 16
01422. 00000 -  "exact fetch returns more than requested number of rows"
*Cause:    The number specified in exact fetch is less than the rows returned.
*Action:   Rewrite the query or change number of rows requested

I'm not sure how to return only a single row from that query. I'm also not sure if an SQL query is the most performant way to do the conversion in a function.

db<>fiddle

Question:

How can I convert an SDO_ORDINATE_ARRAY to a string using a function? (for the purpose of a function-based index)

CodePudding user response：

I came up with a function that seems to work.

But as a novice, I wonder if it might be a bit backwards. It seems strange to (select v_shape from dual) in a function.

create or replace function ordinates_to_varchar2(v_shape in sdo_geometry) return varchar2 
deterministic is 
    v_ordinates varchar2(32767);
begin
    select
        ordinates into v_ordinates
    from
        (select v_shape from dual)
           cross join lateral (
             select listagg(column_value, ',') within group (order by rownum)
                      as ordinates
             from   table(v_shape.sdo_ordinates)
           ) s;    
    return v_ordinates;
end;

create index ordinates_string_idx on test_table (my_owner.ordinates_to_varchar2(shape));

select
    ordinates_to_varchar2(shape) as ordinates
from
    test_table
where
    ordinates_to_varchar2(shape) is not null

Result:

ORDINATES
------------------------------
10,20,30,40,50,60
70,80,90,100
110,120,130,140,150,160,170,180

The explain plan shows that the index is being used:

-----------------------------------------------------------------------------------------
| Id  | Operation        | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                      |     3 |    63 |     1   (0)| 00:00:01 |
|*  1 |  INDEX FULL SCAN | ORDINATES_STRING_IDX |     3 |    63 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("INFRASTR"."ORDINATES_TO_VARCHAR2"("SHAPE") IS NOT NULL)

I'd be happy to hear about any possible design improvements or performance improvements to that function.

db<>fiddle

CodePudding user response：

Converting to JSON also seems to work:

select json_array(t.x.sdo_ordinates returning clob) str
from (select sdo_geometry('linestring(10 20, 30 40, 50 60)') x from dual) t;

STR                
-------------------
[10,20,30,40,50,60]

Source: Function to convert varray to delimited string