将顶点排入嵌套表类型（由ID汇总）

Question

测试数据：

with cte as (
select 1 as id, 100 as x, 101 as y from dual union all
select 1 as id, 200 as x, 201 as y from dual union all
select 2 as id, 300 as x, 301 as y from dual union all
select 2 as id, 400 as x, 401 as y from dual union all
select 2 as id, 500 as x, 501 as y from dual union all
select 3 as id, 600 as x, 601 as y from dual union all
select 3 as id, 700 as x, 701 as y from dual union all
select 3 as id, 800 as x, 801 as y from dual union all
select 3 as id, 900 as x, 901 as y from dual)

select id, x, y from cte

        ID          X          Y
---------- ---------- ----------
         1        100        101
         1        200        201

         2        300        301
         2        400        401
         2        500        501

         3        600        601
         3        700        701
         3        800        801
         3        900        901

在SQL查询中：

我想将顶点折叠到由ID列汇总的嵌套表中。

数据类型将是Oracle Spatial的Mdsys.vertex_set_type：

mdsys.vertex_set_type

此功能返回mdsys.vertex_set_type的对象， 由mdsys.vertex_type的对象表组成。甲骨文空间 并图将类型定义为vertex_set_type为：

 创建类型Vertex_set_type作为vertex_type的表;
 

oracle空间和图将对象类型Vertex_type定义为：

 创建类型type vertex_type作为对象    
（x数字，
 y数字，
 z号，
 W号，
 V5号，
 V6号，
 V7号，
 V8号，
 v9号，
 V10号，
 V11号，
 ID号）; - 顶点ID属性在这里。
 

认为结果看起来像这样：（三组顶点）

VERTICES
---------------------
MDSYS.VERTEX_SET_TYPE([MDSYS.VERTEX_TYPE], [MDSYS.VERTEX_TYPE])
MDSYS.VERTEX_SET_TYPE([MDSYS.VERTEX_TYPE], [MDSYS.VERTEX_TYPE])
MDSYS.VERTEX_SET_TYPE([MDSYS.VERTEX_TYPE], [MDSYS.VERTEX_TYPE])

--I mocked up that format using this dummy query:  
--select  sdo_util.getvertices(sdo_geometry('linestring(100 101, 200 201)')) from dual

---

oracle空间和图将

我 顶点排入vertex_set_type表类型 - 由ID列汇总？

Answer 1

使用收集聚合函数，然后 cast 它到 mdsys.vertex_set_type ：

WITH cte (id, x, y) as (
  SELECT 1, 100, 101 FROM DUAL UNION ALL
  SELECT 1, 200, 201 FROM DUAL UNION ALL
  SELECT 2, 300, 301 FROM DUAL UNION ALL
  SELECT 2, 400, 401 FROM DUAL UNION ALL
  SELECT 2, 500, 501 FROM DUAL UNION ALL
  SELECT 3, 600, 601 FROM DUAL UNION ALL
  SELECT 3, 700, 701 FROM DUAL UNION ALL
  SELECT 3, 800, 801 FROM DUAL UNION ALL
  SELECT 3, 900, 901 FROM DUAL
)
SELECT id,
       CAST(
         COLLECT(
           MDSYS.VERTEX_TYPE(x, y, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, id)
           ORDER BY x, y
         )
         AS MDSYS.VERTEX_SET_TYPE
       )
FROM   cte
GROUP BY id

db＆lt;＆gt; fiddle 在这里