Multi-part geometries: Get line & part number when extracting parts (via SDO_UTIL.EXTRACT_ALL)

I have multi-part 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 multi-part 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" after-the-fact 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

  • answered 2022-05-06 21:53 MT0

    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?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum