SQL Server - Linestring from Points Geometry

Question

Is there an easy way to create a LineString out of a column of Points in SQL?

For example I have a query:

SELECT Geom FROM api.Table WHERE WellId = 'XYZ1234' ORDER BY MeasuredDepth ASC;

that returns 186 points as wkb:

Geom
0xE6100000010CDEB06D5166275AC024B4E55C8A114440
0xE6100000010CE5F21FD26F275AC061FD9FC37C114440
0xE6100000010C4512BD8C62275AC0FCA5457D92114440
0xE6100000010CF33CB83B6B275AC063450DA661144440
0xE6100000010CA56B26DF6C275AC01E32E54350134440
0xE6100000010CBDFBE3BD6A275AC0C1CAA145B6134440
0xE6100000010CB6D617096D275AC03A92CB7F48134440
0xE6100000010CFD82DDB06D275AC04C4F58E201134440
0xE6100000010C9A081B9E5E275AC0041C42959A114440
...

I can also save these wkb in a temporary table and return them as text:

POINT (-104.61562 40.137035)
POINT (-104.6162 40.13662)
POINT (-104.61539 40.137283)
POINT (-104.61592 40.15923)
POINT (-104.61602 40.150887)
POINT (-104.61589 40.154)
POINT (-104.61603 40.15065)
POINT (-104.61607 40.148495)
POINT (-104.61515 40.13753)
POINT (-104.61605 40.15017)
...

But from the MSSQL documentation I can't find any way to make a LineString out of all of these points. Is this possible, i.e. something like PostGIS ST_MakeLine?

score 2 · Accepted Answer · edited Jan 07 '21 at 14:23

You can concatenate the X and Y properties of your Geom column into a string variable, then use the LINESTRING with all the points contained in that variable.

    declare @string varchar(max)

    Select @string = isnull(@string + ',', '') + cast(Geom.STX as varchar(20)) + ' ' + cast(Geom.STY as varchar(20))
    from api.Table

    Set @string = 'LINESTRING(' + @string + ')';   

    Select geometry::STLineFromText(@string, 4326);

Edit

Adding a 2nd answer for the ordering issue, you could iterate trough a table with an identity column.

In the following example, I'm creating a table variable with Id identity, inserting the records into that table, and then iterating and populating the string.

    declare @points table (Id int identity, Geom geometry)

    Insert into @points (Geom)  
    SELECT Geom 
    FROM api.Table 
    WHERE WellId = 'XYZ1234' 
    ORDER BY MeasuredDepth ASC;

    declare @iterRow    int,
            @rowCount   int,
            @string     varchar(max),
            @x          varchar(20),
            @y          varchar(20)

    Select  @iterRow = 1,
            @rowCount = count(1)
    from @points

    While (@iterRow <= @rowCount)
    Begin   
        Select  @x = cast(Geom.STX as varchar(20)),
                @y = cast(Geom.STY as varchar(20))
        From @points
        where Id = @iterRow
        
        Set @string = isnull(@string + ',', '') + @x + ' ' + @y

        Set @iterRow += 1
    End

    Set @string = 'LINESTRING(' + @string + ')';   

    Select geometry::STLineFromText(@string, 0);

thanks so much for this about to try it out! does the wkb column have .STX and .STY attributes or should i use the wkt column? — steven hurwitt, Aug 11 '20 at 21:39
ok so i got a binary output from it at least! trying to convert to text now - also what does the 4326 stand for? — steven hurwitt, Aug 11 '20 at 21:45
4326 is the most common Spatial Reference Identifier (SRID), this is to take into account the shape of the earth. (At first I thought you were using the coordinates with a geography datatype) I think that for geometry you can just use 0, like: geometry::STLineFromText(@string, 0) — iceblade, Aug 12 '20 at 13:11

score 1 · Answer 2 · answered Aug 11 '20 at 21:49

1

Accepted answer is exactly what I was looking for, but does it lose the ascending order (based on another variable)?? Getting this as output:

LINESTRING (-104.615 40.1378, -104.615 40.1378, -104.615 40.1378, -104.615 40.1378, -104.615 40.1378, -104.615 40.1377, -104.615 40.1377, -104.615 40.1377, -104.615 40.1377, -104.615 40.1377, -104.615 40.1376, -104.615 40.1376, ...)

answered Aug 11 '20 at 21:49

steven hurwitt

183
2
15

Added a 2nd answer to handle the ordering issue, though it's a bit trickier. – iceblade Aug 12 '20 at 13:59

SQL Server - Linestring from Points Geometry

2 Answers2

Edit