1

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

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;
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.