Read GPS Coordinates into LineString from Table

Question

I found this beautiful piece of code which takes a linestring and I can get the points from it:

DECLARE @GeographyToConvert geography
SET     @GeographyToConvert = geography::STGeomFromText('LINESTRING (26.6434033 -81.7097817, 26.6435367 -81.709785, 26.6435783 -81.7098033, 26.6436067 -81.709825, 26.6435883 -81.709875, 26.64356 -81.7100417, 26.6434417 -81.710125, 26.6433167 -81.7101467, 26.643195 -81.7101033, 26.6431533 -81.7099517, 26.643175 -81.7097867, 26.643165 -81.7097917, 26.6431633 -81.7097367, 26.6431583 -81.7097083)',4326);
select @GeographyToConvert.STPointN(1)
select @GeographyToConvert.STPointN(2)
select @GeographyToConvert.STPointN(3)

Returns:

0xE6100000010C273339106D6D54C073A32314B6A43A40
0xE6100000010C3F8C101E6D6D54C0984638D2BEA43A40
0xE6100000010CF205D26A6D6D54C050EA268CC1A43A40

From here, I can do what I need to do via the mentioned piece of code.

Instead of the prepopulated demo Linestring however, I have a table with GPS Coordinates which contain the data from which I need to populate the @GeographyToConvert geography

declare @Geo table(long float, lat float)
insert into @Geo(  long, lat ) values
               ( -121.527200, 45.712113 ),
               ( -121.517265, 45.714240 ),
               ( -121.511536, 45.714825)

Select * from @Geo

Can I please ask help in getting the @Geo table values into the @GeographyToConvert geography

Answer 1

You can use dynamic SQL to build the string to feed into STGeomFromText(). I don't think you'll be able to do it relationally (though, to be fair, this is not my expertise). This will get you going until (if) something better comes along.

declare @Geo table(long float, lat float)
insert into @Geo(  long, lat ) values
               ( -121.527200, 45.712113 ),
               ( -121.517265, 45.714240 ),
               ( -121.511536, 45.714825)

DECLARE 
  @cols NVARCHAR(MAX) = N'', 
  @sql NVARCHAR(MAX) = N'DECLARE @g GEOGRAPHY;
     SET @g = GEOGRAPHY::STGeomFromText(''LINESTRING (';

SELECT @cols += N',' + RTRIM(long) + N' ' + RTRIM(lat) FROM @Geo;

SET @cols = STUFF(@cols, 1, 1, N'');

SET @sql += @cols + N')'', 4326);'

SELECT @sql += N'SELECT @g.STPointN(' + RTRIM(ROW_NUMBER() OVER (ORDER BY long))
  + N');' FROM @Geo;

PRINT @sql;
EXEC sys.sp_executesql @sql;

Answer 2

Here's a variation on how I have tackled this in the past.

I have added a grp and seq columns to the @Geo to demonstrate how to build multiple lines and get the order of the points in the line correct. The seq could alternatively be datetime.

DECLARE @Geo table(grp int, seq int, long float, lat float)
INSERT INTTO @Geo( grp, seq,  long, lat ) values
               ( 1, 1, -121.527200, 45.712113 ),
               ( 1, 2, -121.517265, 45.714240 ),
               ( 1, 3, -121.511536, 45.714825)

SELECT geography::STGeomFromText(
    CONCAT('LINESTRING (', STUFF(ls.pointlist,1,1,''), ' )')
    ,4326)
FROM (SELECT grp FROM @Geo GROUP BY grp) G -- Determines points for each line
    CROSS APPLY (
        SELECT CONCAT( ', ', long, ' ', lat) AS [text()]
        FROM @Geo p
        WHERE p.grp = g.grp
        ORDER BY seq
        FOR XML PATH('')
    ) ls(pointlist)

SQLFiddle didn't like the Geography, but this shows the built linestring