Collapse vertex rows into nested table type (aggregated by ID)

Question

Test data:

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

In an SQL query:

I want to collapse the vertices into nested tables, aggregated by the ID column.

The datatype would be Oracle Spatial's MDSYS.VERTEX_SET_TYPE:

MDSYS.VERTEX_SET_TYPE

This function returns an object of MDSYS.VERTEX_SET_TYPE, which consists of a table of objects of MDSYS.VERTEX_TYPE. Oracle Spatial and Graph defines the type VERTEX_SET_TYPE as:
CREATE TYPE vertex_set_type as TABLE OF vertex_type;
Oracle Spatial and Graph defines the object type VERTEX_TYPE as:
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);  --The vertex ID attribute is down here.

I think the result would look something like this: (three sets of vertices)

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

Question:

Is there a way to collapse the vertex rows into the VERTEX_SET_TYPE table type — aggregated by the ID column?

score 1 · Accepted Answer · answered Jun 13 '22 at 08:08

Use the COLLECT aggregation function and then CAST it to 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<>fiddle here

Collapse vertex rows into nested table type (aggregated by ID)

1 Answers1

Linked