By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
WITH elements (line_id, elem_no, starting_offset, next_offset, ordinates) AS (
SELECT '001' as line_id,
elem_no,
starting_offset,
next_offset,
ordinates
FROM JSON_TABLE(
JSON_OBJECT(
SDO_GEOMETRY(
'MULTILINESTRING ((0.0 5.0, 10.0 10.0, 30.0 0.0), (50.0 10.0, 60.0 10.0))'
)
),
'$?(@.SDO_GTYPE==2006)'
COLUMNS (
ordinates VARCHAR2(4000) FORMAT JSON PATH '$.SDO_ORDINATES',
NESTED PATH '$.SDO_ELEM_INFO[*]' COLUMNS (
item_no FOR ORDINALITY,
item NUMBER PATH '$'
)
)
)
MATCH_RECOGNIZE(
ORDER BY item_no
MEASURES
MATCH_NUMBER() AS elem_no,
FIRST(starting_offset.item) AS starting_offset,
FIRST(etype.item) AS etype,
FIRST(interpretation.item) AS interpretation,
NEXT(item) AS next_offset,
FIRST(ordinates) AS ordinates
PATTERN (starting_offset etype interpretation)
DEFINE starting_offset AS 1 = 1
) m
WHERE (etype, interpretation) IN ((2, 1))
)
SELECT m.*
| LINE_ID | ELEM_NO | COORD_ID | X | Y |
|---|---|---|---|---|
| 001 | 1 | 1 | 0 | 5 |
| 001 | 1 | 2 | 10 | 10 |
| 001 | 1 | 3 | 30 | 0 |
| 001 | 2 | 1 | 50 | 10 |
| 001 | 2 | 2 | 60 | 10 |
SELECT t.line_id,
e.elem_no,
v.id AS coord_id,
x,
y
FROM ( SELECT '001' AS line_id,
SDO_GEOMETRY(
'MULTILINESTRING ((0.0 5.0, 10.0 10.0, 30.0 0.0), (50.0 10.0, 60.0 10.0))'
) AS shape
FROM DUAL
) t
CROSS JOIN LATERAL (
SELECT LEVEL AS elem_no,
SDO_UTIL.EXTRACT(t.shape, LEVEL) AS elem
FROM DUAL
CONNECT BY LEVEL <= SDO_UTIL.GETNUMELEM(t.shape)
) e
CROSS APPLY TABLE(SDO_UTIL.GETVERTICES(e.elem)) v
| LINE_ID | ELEM_NO | COORD_ID | X | Y |
|---|---|---|---|---|
| 001 | 1 | 1 | 0 | 5 |
| 001 | 1 | 2 | 10 | 10 |
| 001 | 1 | 3 | 30 | 0 |
| 001 | 2 | 1 | 50 | 10 |
| 001 | 2 | 2 | 60 | 10 |