Why does CROSS JOIN LATERAL break up array’s SDO_GEOMETRY object into individual attributes?

Oracle 18c:

I have mapping software that has a limitation where it can only handle a single geometry column per table. If there are multiple geometry columns in a given table, then it will throw an error.

So, I want to find a way to add an additional geometry column to a table, but store it as a datatype that the mapping software doesn’t recognize — so it will ignore that column (TBD).

One idea is to store an SDO_GEOMETRY as an SDO_GEOMETRY_ARRAY datatype, since the software wouldn’t recognize SDO_GEOMETRY_ARRAY. I would always only store a single geometry in the array.

Similar to this:

with data (geom_array) as (
select sdo_geometry_array(sdo_geometry('point(10 20)')) from dual union all
select sdo_geometry_array(sdo_geometry('point(30 40)')) from dual union all
select sdo_geometry_array(sdo_geometry('point(50 60)')) from dual
)
select geom_array from data

GEOM_ARRAY (SQL Developer)
----------------------------------------------
MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY])
MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY])
MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY])

Unsurprisingly, when I select from the array, it returns the entire array, not the SDO_GEOMETRY (even though there’s only a single value in the array).

So, I want to find an easy/succinct way to extract the SDO_GEOMETRY from the array.

I could use a custom function, which works as expected:

with 
function get_geom_from_array(geom_array sdo_geometry_array) return sdo_geometry is
begin
    return geom_array(1);
end;    
data (geom_array) as (
select sdo_geometry_array(sdo_geometry('point(10 20)')) from dual union all
select sdo_geometry_array(sdo_geometry('point(30 40)')) from dual union all
select sdo_geometry_array(sdo_geometry('point(50 60)')) from dual
)
select
    get_geom_from_array(geom_array)
from   
    data

SDO_GEOM
---------------
[MDSYS.SDO_GEOMETRY]
[MDSYS.SDO_GEOMETRY]
[MDSYS.SDO_GEOMETRY]

Or I could use a CROSS JOIN LATERAL:

select
    v.*
from   
    data d
cross join lateral (
    select sdo_geometry(sdo_gtype, sdo_srid, sdo_point, sdo_elem_info, sdo_ordinates) as sdo_geom
    from   table(d.sdo_array)
    ) v

SDO_GEOM
---------------
[MDSYS.SDO_GEOMETRY]
[MDSYS.SDO_GEOMETRY]
[MDSYS.SDO_GEOMETRY]

That CROSS JOIN LATERAL works, but I don’t understand why it splits the SDO_GEOMETRY into it’s attribute components:

select
    v.*
from   
    data d
cross join lateral (
    select *
    from   table(d.sdo_array)
    ) v

SDO_GTYPE SDO_SRID SDO_POINT              SDO_ELEM_INFO SDO_ORDINATES
--------- -------- ---------------------- ------------- -------------
     2001     null [MDSYS.SDO_POINT_TYPE]          null          null
     2001     null [MDSYS.SDO_POINT_TYPE]          null          null
     2001     null [MDSYS.SDO_POINT_TYPE]          null          null

As such, I need to reconstruct the geometry from those attributes, like this: sdo_geometry(sdo_gtype, sdo_srid, sdo_point, sdo_elem_info, sdo_ordinates).

Reconstructing the SDO_GEOMETRY is slightly inconvenient. But also, I want to understand why that happens in the CROSS JOIN LATERAL, but not in the custom function.

Question:

Why does the CROSS JOIN LATERAL break up the array’s SDO_GEOMETRY object into individual attributes? (but the function doesn’t)

Answer

Why does the CROSS JOIN LATERAL break up the array’s SDO_GEOMETRY object into individual attributes? (but the function doesn’t)

CROSS JOIN LATERAL is treating the table collection expression as if it is an object-derived table (which it effectively is) and uses each object of the collection to form a row of a table and then the columns are the attributes of the object.

The function is returning a single object into one column of one row. They are very different operations.

If you want to get the objects that comprise each row from the table collection expression then use the VALUE function:

SELECT v.obj
FROM   data d
       CROSS JOIN LATERAL (
         SELECT VALUE(t) AS obj
         FROM   TABLE(d.sdo_array) t
       ) v

db<>fiddle here

Advertisement

Answer