I have multi-part polyline vertices stored as individual rows (Oracle 18c).
with vertices as (
select '001' line_id,1 part_num,1 vertex_num,0 x,5 y,0 m from dual union all
select '001',1,2,10,10,11.18 from dual union all
select '001',1,3,30,0,33.54 from dual union all
select '001',2,1,50,10,33.54 from dual union all
select '001',2,2,60,10,43.54 from dual
)
select * from vertices
LINE_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
I want to convert the vertices to a multi-part SDO_GEOMETRY polyline (collapsed into a single row).
I've tried a few different ways of doing that (i.e. listagg and a PL/SQL block). Additionally, as a learning exercise, I would also like to explore creating a custom aggregate function as a solution.
It might look like this:
select
line_id,
sdo_geometry(partition by id, part num, vertex order, x, y, m, gtype, srid) as sdo_geom
from
vertices
group by
line_id
Output:
LINE_ID: 001
SDO_GEOM: SDO_GEOMETRY(3306, 26917, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1, 2, 1, 10, 2, 1), MDSYS.SDO_ORDINATE_ARRAY(0, 5, 0, 10, 10, 11.18, 30, 0, 33.54, 50, 10, 33.54, 60, 10, 43.54))
Question:
Is there a way to create a custom aggregate function in Oracle 18c that would collapse the vertices into an SDO_GEOMETRY value?