从SDO_GEOMETRY直线中提取顶点并在SDO_GEOMETRY_ARRAY中存储为点

Question

Oracle 18c：

使用此示例数据：

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]

我想提取SDO_GEOMETRY直线顶点，并将它们作为SDO_GEOMETRY点存储在SDO_GEOMETRY_ARRAYs中。

结果如下所示：

  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])

有没有办法将这些线转换成数组中的点？

Answer 1

步骤：

1. 内部查询： 几何线。
2. 外部查询：将点收集到SDO_GEOMETRY_ARRAY值中。

相关信息：

• 将顶点行折叠为嵌套表类型(按ID聚合)
• 几何对象

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

结果：

  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])

编辑：

来自@MT0的改进版本：

您可以在横向子查询中进行聚合，这消除了在整个结果集：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

结果：

  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])