从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 LINE顶点，并将其存储为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. 内部查询： 获得点几何形状作为SDO_GEOMETRY LINE中每个顶点的行。

2. 外部查询：将点收集到sdo_geometry_array值。

相关：

• collapse collapse collapse decortex low'' id）
• 创建sdo_geometry对象的ODCI列表

---

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的改进版本：

您可以在横向子查询中汇总，以消除需求
在整个结果集中使用组：

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