## Forum Stats

• 3,827,316 Users
• 2,260,761 Discussions

Discussions

# Custom aggregate function: Collapse vertex rows to polyline

Member Posts: 219 Red Ribbon
edited May 2, 2022 2:47PM

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?

Tagged:
«1

• Member Posts: 6,195 Silver Crown
edited May 1, 2022 12:37PM

Custom Aggregate functions are implemented via Oracle Data Cartridge Interface (ODCI).

They're UDTs that have a specific set of functions. (ODCIAggregate)

• Member Posts: 19,482 Red Diamond

Not exactly sure what you need:

```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
),
t1 as (
select  line_id,
part_num,
vertex_num,
x x_from,
y y_from,
count(*) over(partition by line_id,part_num) cnt,
lead(x) over(partition by line_id,part_num order by vertex_num) x_to,
lead(y) over(partition by line_id,part_num order by vertex_num) y_to
from  vertices
),
t2 as (
select  line_id,
part_num,
vertex_num,
sdo_geometry(
2002,
null,
null,
sdo_elem_info_array(1,2,1),
sdo_ordinate_array(x_from,y_from,x_to,y_to)
) section
from  t1
where vertex_num < cnt
)
select  line_id,
part_num,
sdo_aggr_concat_lines(section) line
from  t2
group by line_id,
part_num
order by line_id,
part_num
/

LINE_ID   PART_NUM LINE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
------- ---------- -----------------------------------------------------------------------------------------------------
001              1 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0, 5, 10, 10, 30, 0))
001              2 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(50, 10, 60, 10))

SQL>

```

SY.

• Member Posts: 543 Bronze Trophy
edited May 2, 2022 2:05PM

Another approach:

```WITH vertices (line_id,part_num,vertex_num,x,y,m) AS
(SELECT '001',1,1, 0, 5,   0 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),
t1 AS
(SELECT x coord, line_id, part_num, vertex_num FROM vertices
UNION ALL
SELECT y     , line_id, part_num, vertex_num FROM vertices)
SELECT line_id, part_num
, sdo_geometry(2002, NULL, NULL
, sdo_elem_info_array(1,2,1)
, CAST(collect (coord ORDER BY line_id, part_num, vertex_num) AS sdo_ordinate_array)) line
FROM t1
GROUP BY line_id, part_num;

LINE_ID   PART_NUM LINE(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
------- ---------- -----------------------------------------------------------------------------------------------------
001              1 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0, 5, 10, 10, 30, 0))
001              2 SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(50, 10, 60, 10))
```

• Member Posts: 219 Red Ribbon

Thanks! Does that approach assume that we'd need to do an additional step afterwards: merge the two single-part geometries into a multi-part geometry?

• Member Posts: 543 Bronze Trophy
edited May 2, 2022 3:11PM

Yes, with an additional merge step, based on an adapted and extended (for M) approach of @SY or mine. The present draft was ready 😉

This collapse is certainly possible in one very smart step...

• Member Posts: 19,482 Red Diamond

@_jum: Another approach:

Careful, you need to weigh X & Y, othersise you get non-deterministic results and Y can end up first:

```WITH vertices (line_id,part_num,vertex_num,x,y,m) AS
(SELECT '001',1,1, 0, 5,   0 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),
t1 AS
(SELECT x coord, line_id, part_num, vertex_num, 1 weight FROM vertices
UNION ALL
SELECT y     , line_id, part_num, vertex_num,2 weight FROM vertices)
SELECT line_id, part_num
, sdo_geometry(2002, NULL, NULL
, sdo_elem_info_array(1,2,1)
, CAST(collect (coord ORDER BY line_id, part_num, vertex_num,weight) AS sdo_ordinate_array)) line
FROM t1
GROUP BY line_id, part_num;
```

SY.

• Member Posts: 543 Bronze Trophy

Of course, I need a break - Thanks @Solomon Yakobson!

• Member Posts: 219 Red Ribbon

This collapse is certainly possible in one very smart step...

What's the smart step for merging?

• Member Posts: 543 Bronze Trophy

I honestly have no idea currently!

• Member Posts: 543 Bronze Trophy
edited May 3, 2022 1:00PM

Last step could be:

```SELECT sdo_geom.sdo_union(
SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0, 5, 0, 10, 10, 11.18, 30, 0, 33.54))
, SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(50, 10, 33.54, 60, 10, 43.54))
, 0.01) ugeom
FROM dual;

UGEOM
---------------------------------﻿
MDSYS.SDO_GEOMETRY(3006, NULL, 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))
﻿
```