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.