## Top 5 Recent Articles

##### ARTICLES CATEGORIES

- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)

# 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.

## Documentation

- MySQL Spatial General Functions
- Oracle LRS Objects
- Oracle Spatial Exporter (Java + pl/SQL)
- Oracle Spatial Object Functions
- Oracle Spatial Object Functions (Multi Page)
- PostGIS pl/pgSQL Functions
- SC4O Oracle Java Topology Suite (Java + pl/SQL)
- SQL Server Spatial General TSQL Functions
- SQL Server Spatial LRS TSQL Functions