Oracle按ID聚合顶点行生成MDSYS.VERTEX_SET_TYPE嵌套表方法

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

• 按ID字段分组聚合，将同ID下的多行顶点数据折叠为嵌套表结构
• 输出字段类型为Oracle Spatial的MDSYS.VERTEX_SET_TYPE

目标类型说明 #

MDSYS.VERTEX_SET_TYPE是MDSYS.VERTEX_TYPE构成的对象表，定义如下：

CREATE TYPE vertex_set_type as TABLE OF vertex_type;

其中MDSYS.VERTEX_TYPE顶点对象的定义如下：

CREATE TYPE vertex_type AS OBJECT    
(x   NUMBER,
 y   NUMBER,
 z   NUMBER,
 w   NUMBER,
 v5  NUMBER,
 v6  NUMBER,
 v7  NUMBER,
 v8  NUMBER,
 v9  NUMBER,
 v10 NUMBER,
 v11 NUMBER,
 id  NUMBER);  -- 顶点ID属性位于定义末尾

共返回3组顶点集合，结构参考：

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

-- 上述格式可通过如下示例查询模拟：
-- select  sdo_util.getvertices(sdo_geometry('linestring(100 101, 200 201)')) from dual

直接用COLLECT聚合函数搭配类型转换即可实现，完整SQL如下：

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,
  CAST(
    COLLECT(
      MDSYS.VERTEX_TYPE(
        x, y,
        NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
        id
      )
    ) AS MDSYS.VERTEX_SET_TYPE
  ) AS vertices
FROM cte
GROUP BY id;

说明 #

• COLLECT会按分组将多行对象聚合为集合，默认返回系统生成的嵌套表类型，通过CAST显式转换即可得到目标MDSYS.VERTEX_SET_TYPE类型
• 构造VERTEX_TYPE对象时，未用到的z、w、v5~v11字段直接传NULL即可，末尾的id字段传入分组ID值
• 最终返回的vertices字段类型和sdo_util.getvertices()函数返回的类型完全一致，可直接作为参数传入Oracle Spatial的各类空间处理函数使用。

内容的提问来源于stack exchange，提问作者User1974