Multipart geometries: Get line & part number when extracting parts (via SDO_UTIL.EXTRACT_ALL)
I have multipart SDO_GEOMETRIES in Oracle 18c.
I can extract the parts as individual rows using the SDO_UTIL.EXTRACT_ALL() function:
with cte as ( Each part is wrapped in brackets and separated by commas: (p1),(p2) select sdo_geometry('MULTILINESTRING((1 1 1, 2 2 2),(3 3 3, 4 4 4))') as shape from dual union all 3D: (X,Y,Z) select sdo_geometry('MULTILINESTRING((5 5, 6 6),(7 7, 8 8),(9 9, 0 0))') as shape from dual union all 2D: (X,Y) select sdo_geometry('MULTILINESTRING((1 1, 2 2))') as shape from dual 2D: (X,Y) ) select a.object_value, sdo_util.to_wktgeometry(a.object_value) from cte, table(sdo_util.extract_all(shape)) a OBJECT_VALUE SDO_UTIL.TO_WKTGEOMETRY(A.OBJECT_VALUE)   [MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0 1.0, 2.0 2.0 2.0) [MDSYS.SDO_GEOMETRY] LINESTRING (3.0 3.0 3.0, 4.0 4.0 4.0) [MDSYS.SDO_GEOMETRY] LINESTRING (5.0 5.0, 6.0 6.0) [MDSYS.SDO_GEOMETRY] LINESTRING (7.0 7.0, 8.0 8.0) [MDSYS.SDO_GEOMETRY] LINESTRING (9.0 9.0, 0.0 0.0) [MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0, 2.0 2.0)
db<>fiddle here
I want to add the following columns to the query:
 MULTILINE_NUM (the original multipart line number)
 PART_NUM
MULTILINE_NUM PART_NUM OBJECT_VALUE SDO_UTIL.TO_WKTGEOMETRY(A.OBJECT_VALUE)     1 1 [MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0 1.0, 2.0 2.0 2.0) 1 2 [MDSYS.SDO_GEOMETRY] LINESTRING (3.0 3.0 3.0, 4.0 4.0 4.0) 2 1 [MDSYS.SDO_GEOMETRY] LINESTRING (5.0 5.0, 6.0 6.0) 2 2 [MDSYS.SDO_GEOMETRY] LINESTRING (7.0 7.0, 8.0 8.0) 2 3 [MDSYS.SDO_GEOMETRY] LINESTRING (9.0 9.0, 0.0 0.0) 3 1 [MDSYS.SDO_GEOMETRY] LINESTRING (1.0 1.0, 2.0 2.0)
When adding those columns, I don't want to "fake it" afterthefact using window functions, etc.
I want to extract that information directly from the geometries, to ensure the numbers are correct (i.e., part number shouldn't be arbitrary; it should reflect the part number order from the original geometry).
Is there a way to get the MULTILINE_NUM and PART_NUM from the table(sdo_util.extract_all(shape))
query?
1 answer

Adapting my answer to your previous question, you can use:
with cte (shape) as ( Each part is wrapped in brackets and separated by commas: (p1),(p2) select sdo_geometry('MULTILINESTRING((1 1 1, 2 2 2),(3 3 3, 4 4 4))') from dual union all 3D: (X,Y,Z) select sdo_geometry('MULTILINESTRING((5 5, 6 6),(7 7, 8 8),(9 9, 0 0))') from dual union all 2D: (X,Y) select sdo_geometry('MULTILINESTRING((1 1, 2 2))') from dual 2D: (X,Y) ) select c.multiline_num, e.part_no, sdo_util.to_wktgeometry(e.elem) from ( SELECT ROWNUM AS multiline_num, shape FROM cte ) c CROSS JOIN LATERAL ( SELECT LEVEL AS part_no, SDO_UTIL.EXTRACT(c.shape, LEVEL) AS elem FROM DUAL CONNECT BY LEVEL <= SDO_UTIL.GETNUMELEM(c.shape) ) e;
Which outputs:
MULTILINE_NUM PART_NO SDO_UTIL.TO_WKTGEOMETRY(E.ELEM) 1 1 LINESTRING (1.0 1.0 1.0, 2.0 2.0 2.0) 1 2 LINESTRING (3.0 3.0 3.0, 4.0 4.0 4.0) 2 1 LINESTRING (5.0 5.0, 6.0 6.0) 2 2 LINESTRING (7.0 7.0, 8.0 8.0) 2 3 LINESTRING (9.0 9.0, 0.0 0.0) 3 1 LINESTRING (1.0 1.0, 2.0 2.0)
Or, using
SDO_UTIL.EXTRACT_ALL
:with cte (shape) as ( Each part is wrapped in brackets and separated by commas: (p1),(p2) select sdo_geometry('MULTILINESTRING((1 1 1, 2 2 2),(3 3 3, 4 4 4))') from dual union all 3D: (X,Y,Z) select sdo_geometry('MULTILINESTRING((5 5, 6 6),(7 7, 8 8),(9 9, 0 0))') from dual union all 2D: (X,Y) select sdo_geometry('MULTILINESTRING((1 1, 2 2))') from dual 2D: (X,Y) ) select c.multiline_num, e.part_no, sdo_util.to_wktgeometry(e.elem) from ( SELECT ROWNUM AS multiline_num, shape FROM cte ) c CROSS JOIN LATERAL ( SELECT ROWNUM AS part_no, a.object_value AS elem FROM TABLE(SDO_UTIL.EXTRACT_ALL(c.shape)) a ) e;
Which outputs the same.
db<>fiddle here
How many English words
do you know?
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
how many words do you know
Powered by Examplum