5

I have a table schema that looks like this:

CREATE TABLE [dbo].[LongAndLats](
[Longitude] [decimal](9, 6) NULL,
[Latitude] [decimal](9, 6) NULL,
[SortOrder] [int] NULL
)

Sample data looks like this:

enter image description here

How can I convert these points into a geography polyline using TSQL?

g2server
  • 5,037
  • 3
  • 29
  • 40

1 Answers1

5

try this: (note: the ordering of the points is important for the line to be generated correctly.)

DECLARE @BuildString NVARCHAR(MAX)
SELECT @BuildString = COALESCE(@BuildString + ',', '') + CAST([Longitude] AS NVARCHAR(50)) + ' ' + CAST([Latitude] AS NVARCHAR(50))
FROM dbo.LongAndLats
ORDER BY SortOrder             

SET @BuildString = 'LINESTRING(' + @BuildString + ')';   
DECLARE @LineFromPoints geography = geography::STLineFromText(@BuildString, 4326);
SELECT @LineFromPoints
g2server
  • 5,037
  • 3
  • 29
  • 40
  • I suggest to avoid `SELECT @var = @var + '' ... ORDER BY ...` construct. [Group by value and create geography polyline from points](https://stackoverflow.com/questions/48241483/group-by-value-and-create-geography-polyline-from-points-latitude-and-longitude/48241575#48241575) – Lukasz Szozda Jan 13 '18 at 17:38
  • 1
    @lad2025 Good point. That link says `applying a function or expression to the columns in the SELECT list *should* produce deterministic behavior` (as in the answer), they also go on to say `The correct behavior for an aggregate concatenation query is undefined`. The alternative constructs in your answer [here](https://stackoverflow.com/a/48241575/2293226), based on a similar question are more reliable. Thanks for the comment. – g2server Jan 13 '18 at 21:04
  • String aggregation using COALESCE is apparently not safe: https://marc.durdin.net/2015/07/concatenating-strings-in-sql-server-or-undefined-behaviour-by-design/ – mikeck Jul 06 '21 at 17:06