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:
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?
CodePudding user response:
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