Extract vertices from SDO_GEOMETRY lines and store as points in SDO_GEOMETRY_ARRAY

Oracle 18c:

Using this sample data:

with data (asset_id, shape) as (
  select 100, sdo_geometry('linestring (10 20, 30 40)')                       from dual union all
  select 200, sdo_geometry('linestring (50 60, 70 80, 90 100)')               from dual union all
  select 300, sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') from dual
)
select * from data

  ASSET_ID SHAPE
---------- --------------------
       100 [MDSYS.SDO_GEOMETRY]
       200 [MDSYS.SDO_GEOMETRY]
       300 [MDSYS.SDO_GEOMETRY]

I want to extract the SDO_GEOMETRY line vertices and store them as SDO_GEOMETRY points in SDO_GEOMETRY_ARRAYs.

The result would look like this:

  ASSET_ID  POINT_ARRAY
----------  ------------
       100  MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY])
       200  MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY])
       300  MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY])

Is there a way to convert those lines to points in an array?

Steps:

1. Inner query: Get point geometries as rows for each vertex in SDO_GEOMETRY line.

2. Outer query: Collect the points into SDO_GEOMETRY_ARRAY values.

Related:

• Collapse vertex rows into nested table type (aggregated by ID)
• Create an ODCI list of SDO_GEOMETRY objects

with data (asset_id, shape) as (
  select 100, sdo_geometry('linestring (10 20, 30 40)')                       from dual union all
  select 200, sdo_geometry('linestring (50 60, 70 80, 90 100)')               from dual union all
  select 300, sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') from dual
)
select 
    asset_id,
    cast(collect(shape order by vertex_index) as sdo_geometry_array) as point_array
from
    (
    select 
        d.asset_id,
        vertex_index,
        p.shape
    from   
        data d
    cross join lateral (
        select 
            sdo_util.get_coordinate(d.shape,level) as shape, level as vertex_index
        from   
            dual
        connect by level <= sdo_util.getnumvertices(d.shape)
        ) p
    )    
group by 
    asset_id        
order by
    asset_id

Result:

  ASSET_ID  POINT_ARRAY
----------  ------------
       100  MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY])
       200  MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY])
       300  MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY])

Edit:

An improved version from @MT0:

You can aggregate inside the LATERAL subquery which removes the need to use GROUP BY across the entire result set: db<>fiddle.

WITH data (asset_id, shape) AS (
  select 100, sdo_geometry('linestring (10 20, 30 40)')                       from dual union all
  select 200, sdo_geometry('linestring (50 60, 70 80, 90 100)')               from dual union all
  select 300, sdo_geometry('linestring (110 120, 130 140, 150 160, 170 180)') from dual
)
SELECT asset_id,
       point_array
FROM   data d
       CROSS JOIN LATERAL (
         SELECT CAST(
                  COLLECT(
                    sdo_util.get_coordinate(d.shape,level)
                    ORDER BY LEVEL
                  )
                  AS SDO_GEOMETRY_ARRAY
                ) AS point_array
         FROM   DUAL
         CONNECT BY LEVEL <= sdo_util.getnumvertices(d.shape)
       ) p
ORDER BY
       asset_id

Result:

  ASSET_ID  POINT_ARRAY
----------  ------------
       100  MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY])
       200  MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY])
       300  MDSYS.SDO_GEOMETRY_ARRAY([MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY], [MDSYS.SDO_GEOMETRY])