Creating linestring geometries from a table of ordinates (SQL Server)

Introduction

(This article is about SQL Server 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 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 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 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 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 union all
select 002 as asset_id, 1 as part_num,1 as vertex_num,90 as x,20 as y,    0 as m union all
select 002 as asset_id, 1 as part_num,2 as vertex_num,91 as x,20 as y,    1 as m 
)
SELECT *
  FROM cte;

How does one create linestrings and multilinestrings from this data?

First: Create Linestrings:

DECLARE @srid integer = 0;
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 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 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 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 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 union all
select 002 as asset_id, 1 as part_num,1 as vertex_num,90 as x,20 as y,    0 as m union all
select 002 as asset_id, 1 as part_num,2 as vertex_num,91 as x,20 as y,    1 as m 
)
select f.asset_id,
       geometry::STGeomFromText(
             'LINESTRING(' 
			 + 
			 STRING_AGG(cast(f.x as varchar(50)) + ' ' +
                                    cast(f.y as varchar(50)) + ' NULL ' +
                                    cast(f.m as varchar(50)),
                         ',') WITHIN GROUP (ORDER BY f.asset_id,f.part_num,f.vertex_num)+ 
             ')',
             @srid).AsTextZM() as line
  from cte as f
  group by f.asset_id, f.part_num;

-- asset_id line
-- 1        LINESTRING (0 5 NULL 0, 10 10 NULL 11.18, 30 0 NULL 33.54)
-- 2        LINESTRING (90 20 NULL 0, 91 20 NULL 1)
-- 1        LINESTRING (50 10 NULL 33.54, 60 10 NULL 43.54)

Create MultiLineStrings (1)

Multilinestrings can be created using the geometry::UnionAggregate aggregate operator. However, while we can construct 3D linestrings, the aggrgate operator only supports 2D linestrings:

DECLARE @srid integer = 0;
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 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 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 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 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 union all
select 002 as asset_id, 1 as part_num,1 as vertex_num,90 as x,20 as y,    0 as m union all
select 002 as asset_id, 1 as part_num,2 as vertex_num,91 as x,20 as y,    1 as m 
)
SELECT g.asset_id,
       geometry::UnionAggregate(line).AsTextZM() as line
  FROM (SELECT f.asset_id,
               geometry::STGeomFromText(
               'LINESTRING(' + 
               STRING_AGG(cast(f.x as varchar(50)) + ' ' +
                          cast(f.y as varchar(50)) + ' NULL ' +
                          cast(f.m as varchar(50)),
                          ',') WITHIN GROUP (ORDER BY f.asset_id,f.part_num,f.vertex_num ASC)+ ')',
               @srid) as line
          FROM cte as f
         GROUP BY f.asset_id,f.part_num
       ) as g
 GROUP BY g.asset_id
 ORDER BY g.asset_id;

asset_id line
1        MULTILINESTRING ((60 10, 50 10), (30 0, 10 10, 0 5))
2        LINESTRING (91 20, 90 20)

Create MultiLineStrings (2)

3D+ linesatrings can be created by extending the STRING_AGG processing of the first example.

DECLARE @srid integer = 0;
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 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 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 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 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 union all
select 002 as asset_id, 1 as part_num,1 as vertex_num,90 as x,20 as y,    0 as m union all
select 002 as asset_id, 1 as part_num,2 as vertex_num,91 as x,20 as y,    1 as m 
)
SELECT g.asset_id,
       geometry::STGeomFromText(prefix +
                                case when prefix = 'MULTILINESTRING' then '(' else '' end +
                                STRING_AGG('(' + g.coordinates + ')',',') WITHIN GROUP (ORDER BY g.asset_id) +
                                case when prefix = 'MULTILINESTRING' then ')' else '' end,
                                @srid).AsTextZM() as geom
  FROM (  SELECT f.asset_id,
                 CASE WHEN count(f.part_num) over (partition by f.asset_id) > 1 
                      THEN 'MULTILINESTRING' 
                      ELSE 'LINESTRING' 
                  END as prefix,
                  STRING_AGG(cast(f.x as varchar(50)) + ' ' +
                             cast(f.y as varchar(50)) + ' NULL ' +
                             cast(f.m as varchar(50)),
                              ',') WITHIN GROUP (ORDER BY f.asset_id,f.part_num,f.vertex_num ASC) 
                as coordinates
          FROM cte as f
         GROUP BY f.asset_id, f.part_num
       ) as g
 GROUP BY g.asset_id, g.prefix
  ORDER BY g.asset_id;

asset_id geom
1        MULTILINESTRING ((0 5 NULL 0, 10 10 NULL 11.18, 30 0 NULL 33.54), (50 10 NULL 33.54, 60 10 NULL 43.54))
2        LINESTRING (90 20 NULL 0, 91 20 NULL 1)

I hope this is of use to someone.