'Custom aggregate function to collapse vertices to SDO_GEOMETRY
I have multi-part polyline vertices stored as individual rows in an Oracle 18c table.
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
DDL db<>fiddle
CTE db<>fiddle
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 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
asset_id,
sdo_geometry(partition by id, part num, vertex order, x, y, m, gtype, srid) as sdo_geom
from
vertices
group by
asset_id
Output:
ASSET_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))
--SDO_GEOMETRY docs: https://docs.oracle.com/en/database/oracle/oracle-database/19/spatl/spatial-datatypes-metadata.html
--Info about multi-part lines: https://community.oracle.com/tech/apps-infra/discussion/4497547/sdo-geometry-output-how-to-know-if-geometry-is-multi-part
Is there a way to create a custom aggregate function to do that?
Solution 1:[1]
Create a type to store the point:
CREATE TYPE PointLRS AS OBJECT(
X NUMBER,
Y NUMBER,
M NUMBER
);
Then create a user-defined aggregation type:
CREATE TYPE Line3DAggType AS OBJECT(
ordinates SDO_ORDINATE_ARRAY,
STATIC FUNCTION ODCIAggregateInitialize(
ctx IN OUT Line3DAggType
) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateIterate(
self IN OUT Line3DAggType,
point IN PointLRS
) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateTerminate(
self IN OUT Line3DAggType,
returnValue OUT SDO_GEOMETRY,
flags IN NUMBER
) RETURN NUMBER,
MEMBER FUNCTION ODCIAggregateMerge(
self IN OUT Line3DAggType,
ctx IN OUT Line3DAggType
) RETURN NUMBER
);
/
CREATE OR REPLACE TYPE BODY Line3DAggType
IS
STATIC FUNCTION ODCIAggregateInitialize(
ctx IN OUT Line3DAggType
) RETURN NUMBER
IS
BEGIN
ctx := Line3DAggType( SDO_ORDINATE_ARRAY() );
RETURN ODCIConst.SUCCESS;
END;
MEMBER FUNCTION ODCIAggregateIterate(
self IN OUT Line3DAggType,
point IN PointLRS
) RETURN NUMBER
IS
BEGIN
IF point IS NOT NULL
AND point.X IS NOT NULL
AND point.Y IS NOT NULL
AND point.M IS NOT NULL
THEN
self.ordinates.EXTEND(3);
self.ordinates(self.ordinates.COUNT - 2) := point.X;
self.ordinates(self.ordinates.COUNT - 1) := point.Y;
self.ordinates(self.ordinates.COUNT - 0) := point.M;
END IF;
RETURN ODCIConst.SUCCESS;
END;
MEMBER FUNCTION ODCIAggregateTerminate(
self IN OUT Line3DAggType,
returnValue OUT SDO_GEOMETRY,
flags IN NUMBER
) RETURN NUMBER
IS
BEGIN
IF self.ordinates.COUNT > 0 THEN
returnValue := SDO_GEOMETRY(
3302,
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,2,1),
self.ordinates
);
ELSE
returnValue := NULL;
END IF;
RETURN ODCIConst.SUCCESS;
END;
MEMBER FUNCTION ODCIAggregateMerge(
self IN OUT Line3DAggType,
ctx IN OUT Line3DAggType
) RETURN NUMBER
IS
BEGIN
FOR i IN 1 .. ctx.ordinates.COUNT LOOP
self.ordinates.EXTEND;
self.ordinates(self.ordinates.COUNT) := ctx.ordinates(i);
END LOOP;
RETURN ODCIConst.SUCCESS;
END;
END;
/
Then define a custom aggregation function:
CREATE FUNCTION Line3DAgg( point PointLRS )
RETURN SDO_GEOMETRY
PARALLEL_ENABLE AGGREGATE USING Line3DAggType;
/
Then you can aggregate the points for each part into a line and then concatenate the lines:
SELECT asset_id,
SDO_AGGR_LRS_CONCAT(SDOAGGRTYPE(part, 0.005)) AS geom
FROM (
SELECT asset_id,
part_num,
Line3DAgg(PointLRS(x, y, m)) AS part
FROM vertices
GROUP BY asset_id, part_num
)
GROUP BY asset_id
db<>fiddle here
Solution 2:[2]
This builds the individual linestrings.
with cte as (
select 001 as asset_id, 1 as part_num,1 as vertex_num,0 as x,5 as y, 0 as m from dual union all
select 001 as asset_id, 1 as part_num,2 as vertex_num,10 as x,10 as y,11.18 as m from dual union all
select 001 as asset_id, 1 as part_num,3 as vertex_num,30 as x,0 as y, 33.54 as m from dual union all
select 001 as asset_id, 2 as part_num,1 as vertex_num,50 as x,10 as y,33.54 as m from dual union all
select 001 as asset_id, 2 as part_num,2 as vertex_num,60 as x,10 as y,43.54 as m from dual
)
SELECT asset_id,
part_num,
mdsys.sdo_geometry(
3302,
null,
null,
mdsys.sdo_elem_info_array(1,2,1),
CAST(MULTISET( select case when r.rin = 1 then x
when r.rin = 2 then y
when r.rin = 3 then m
end
from cte b,
(select level rin from dual connect by level < 4) r
where b.asset_id = a.asset_id
and b.part_num = a.part_num
order by b.vertex_num, r.rin
) as mdsys.sdo_ordinate_array
)
) as geom
from cte a
group by asset_id, part_num
order by part_num;
Note how the X, Y and M ordinates are "serialised" into an array (of type mdsys.sdo_ordinate_array) using the MULTISET operator. Result is:
ASSET_ID PART_NUM GEOM
---------- ---------- ----
1 1 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))
1 2 SDO_GEOMETRY(3002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(50, 10, 33.54, 60, 10, 43.54))
Creating a multilinestring involves aggregating the linestrings using the asset_id attribute.
with cte as (
select 001 as asset_id, 1 as part_num,1 as vertex_num,0 as x,5 as y, 0 as m from dual union all
select 001 as asset_id, 1 as part_num,2 as vertex_num,10 as x,10 as y,11.18 as m from dual union all
select 001 as asset_id, 1 as part_num,3 as vertex_num,30 as x,0 as y, 33.54 as m from dual union all
select 001 as asset_id, 2 as part_num,1 as vertex_num,50 as x,10 as y,33.54 as m from dual union all
select 001 as asset_id, 2 as part_num,2 as vertex_num,60 as x,10 as y,43.54 as m from dual
)
SELECT asset_id,
SDO_AGGR_UNION(SDOAGGRTYPE(geom,0.005)) as mGeom
FROM (SELECT asset_id,
part_num,
mdsys.sdo_geometry(
3302,
null,
null,
mdsys.sdo_elem_info_array(1,2,1),
CAST(MULTISET( select case when r.rin = 1 then x
when r.rin = 2 then y
when r.rin = 3 then m
end
from cte b,
(select level rin from dual connect by level < 4) r
where b.asset_id = a.asset_id
and b.part_num = a.part_num
order by b.vertex_num, r.rin
) as mdsys.sdo_ordinate_array
)
) as geom
from cte a
group by asset_id, part_num
order by part_num
) f
GROUP BY asset_id;
Result:
ASSET_ID MGEOM
---------- -----
1 SDO_GEOMETRY(3006, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1, 10, 2, 1), SDO_ORDINATE_ARRAY(0, 5, 0, 10, 10, 11.18, 30, 0, 33.54, 50, 10, 33.54, 60, 10, 43.54))
See also my article [Building linestrings from GPX GPS data]: https://www.spdba.com.au/loading-and-processing-gpx-1-1-files-using-oracle-xmldb-2/
Solution 3:[3]
You can concatenate the it into a multi-line string of parts and then generate the SDO_GEOMETRY from that string:
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
db<>fiddle here
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | MT0 |
| Solution 2 | |
| Solution 3 | MT0 |