CODEWITHSUNDEEP
sql-servert-sqlgeospatialsql-server-2014
cs0815
cs0815

Reputation: 17388

combine GEOGRAPHY Points to GEOGRAPHY LINESTRING

Is there an easy way to combine several GEOGRAPHY Points like this:

CREATE TABLE #Points
(
    Id INT IDENTITY(1,1),
    Point GEOGRAPHY
)

to a GEOGRAPHY LINESTRING in Sql Server 2014?

Upvotes: 0

Views: 1392

Answers (3)

Caius Jard
Caius Jard

Reputation: 74605

I had a scenario where I had some points:

Points

PathId Step PointGeo
1 1 0xE61...
1 2 0xE61...

I wanted to put them into the parent table (Path; a Path has many Points) as a linestring

Paths

PathId PathAsLineStringGeo
1 NULL

I wrote an SQL that wrote an SQL:

  select
    concat(
      'update Paths set PathAsLineStringGeo = geography::STGeomFromText(''LINESTRING(',
      string_agg(cast(concat(PointGeo.Long, ' ', PointGeo.Lat) as varchar(max)), ' ') within group(order by Step),
      ')'', 4326) where PathId = ',
      PathId
    ) 

  from points
  group by pathid

The columns you'll need to adjust to make this relevant for your context are in PascalCase

This generated a bunch of update statements that I copied out of the results window and ran:

UPDATE Paths SET PathAsLineStringGeo = geography::STGeomFromText('LINESTRING(-1.22 3.44, 5.66 7.88)', 4326) where PathId = 1
UPDATE Paths SET PathAsLineStringGeo = geography::STGeomFromText('LINESTRING(-7.89 10.11 -12.13 14.15 -16.17 18.19)', 4326) where PathId = 2

There wouldn't be anything stopping you making this a typical updateable join though.. Perhaps something like:

update x
set somelinestringgeocolumn = geography::STGeomFromText(y.calculatedlinestringWKT, 4326)
from 

  parent_table x
  inner join 
  (
    select
      pathId,
      CONCAT(
        'LINESTRING(',
        string_agg(cast(concat(point.Long, ' ', point.Lat) as varchar(max)), ', ') within group(order by Step),
        ')'
      ) as calculatedlinestringWKT
    from child_table_with_points
  ) y
  ON x.pathid = y.pathid

Upvotes: 0

Michael Entin
Michael Entin

Reputation: 7744

Another approach is to construct string from (x y) coordinates of the points (via point.StX() and point.StY()), concatenate them and build WKT string describing the linestring, then convert it to Geography type.

Upvotes: 0

hcaelxxam
hcaelxxam

Reputation: 786

To combine two points you can do:

@Point1.STUnion(@Point2).STConvexHull()

To create lines, the points have to be in order, so stuff like ConvexHullAggregate doesn't work. You might try writing a loop to combine them in order and then store the combination.

Upvotes: 2

Related Questions