By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table vertices (
asset_id varchar2(10),
part_num number,
vertex_num number,
x number,
y number,
m number);
insert into vertices (asset_id,part_num,vertex_num,x,y,m) values ('001',1, 1, 0, 5, 0);
1 rows affected
insert into vertices (asset_id,part_num,vertex_num,x,y,m) values ('001',1, 2, 10, 10, 11.18);
1 rows affected
insert into vertices (asset_id,part_num,vertex_num,x,y,m) values ('001',1, 3, 30, 0, 33.54);
1 rows affected
insert into vertices (asset_id,part_num,vertex_num,x,y,m) values ('001',2, 1, 50, 10, 33.54);
1 rows affected
insert into vertices (asset_id,part_num,vertex_num,x,y,m) values ('001',2, 2, 60, 10, 43.54);
1 rows affected
select * from vertices
| ASSET_ID | PART_NUM | VERTEX_NUM | X | Y | M |
|---|---|---|---|---|---|
| 001 | 1 | 1 | 0 | 5 | 0 |
| 001 | 1 | 2 | 10 | 10 | 11.18 |
| 001 | 1 | 3 | 30 | 0 | 33.54 |
| 001 | 2 | 1 | 50 | 10 | 33.54 |
| 001 | 2 | 2 | 60 | 10 | 43.54 |
SELECT asset_id,
SDO_GEOMETRY(
'MULTILINESTRING (' || LISTAGG(part, ',') WITHIN GROUP (ORDER BY part_num) || ')'
) AS geom
FROM (
SELECT asset_id,
part_num,
'(' || LISTAGG(x || ' ' || y || ' ' || m, ',') WITHIN GROUP (ORDER BY vertex_num) || ')'
AS part
FROM vertices
GROUP BY asset_id, part_num
)
GROUP BY asset_id
SELECT asset_id,
JSON_OBJECT(
SDO_GEOMETRY(
'MULTILINESTRING (' || LISTAGG(part, ',') WITHIN GROUP (ORDER BY part_num) || ')'
)
) AS geom
FROM (
SELECT asset_id,
part_num,
'(' || LISTAGG(x || ' ' || y || ' ' || m, ',') WITHIN GROUP (ORDER BY vertex_num) || ')'
AS part
FROM vertices
GROUP BY asset_id, part_num
)
GROUP BY asset_id
| ASSET_ID | GEOM |
|---|---|
| 001 | {"SDO_GTYPE":3006,"SDO_SRID":null,"SDO_POINT":{},"SDO_ELEM_INFO":[1,2,1,10,2,1],"SDO_ORDINATES":[0,5,0,10,10,11.18,30,0,33.54,50,10,33.54,60,10,43.54]} |