Creating linestring geometries from a table of ordinates (Oracle)
Introduction
(This article is about Oracle Spatial.)
It is quite a common thing to have ordinates held as separate columns within a table as in the following CTE example.
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 union all select 002 as asset_id, 1 as part_num,1 as vertex_num,90 as x,20 as y, 0 as m from dual union all select 002 as asset_id, 1 as part_num,2 as vertex_num,91 as x,20 as y, 1 as m from dual ) SELECT * FROM cte;
How does one create linestrings and multilinestrings from this data?
First things first: A linestring
A linestring can be created by serialising the separate x, y and m values into an array (of type mdsys.sdo_ordinate_array) using the MULTISET operator.
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 union all
select 002 as asset_id, 1 as part_num,1 as vertex_num,90 as x,20 as y, 0 as m from dual union all
select 002 as asset_id, 1 as part_num,2 as vertex_num,91 as x,20 as y, 1 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 asset_id, part_num;
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))
2 1 SDO_GEOMETRY(3302, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(90, 20, 0, 91, 20, 1))
Finall: Creating MultiLineString objects
Multilinestring objects can be created by aggregating (group by) the individual 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 union all
select 002 as asset_id, 1 as part_num,1 as vertex_num,90 as x,20 as y, 0 as m from dual union all
select 002 as asset_id, 1 as part_num,2 as vertex_num,91 as x,20 as y, 1 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
ORDER BY asset_id;
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))
2 SDO_GEOMETRY(3302,NULL,NULL,SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(90, 20, 0, 91, 20, 1))
I hope this is of use to someone.