SQL 2008 Geography - Combine Data Points into a Geography Line

Friday, June 4, 2010

I have encountered many instances where having the decimal values for coordinates in a line is more useful than the having the Geography value for that same line. Likewise, in some instances a Geography value is more appropriate. Here is what I mean:

I need all points in a polygon on a map. For my purposes, it is much (MUCH) faster to determine the overall rectangle that surrounds the polygon, add a bit of latitude and longitude to all axis and search against the individual points. With an index on the decimal values this is fast and guarantees that I get everything in the polygon. I don't mind too much that I also get a bunch of stuff not in that polygon. I use this to find what is visible to the user, not to determine actual spatial relationships among points or between points and the polygon.

There are also times when I need to find distances and spatial relationship between points in a line. This is where the Geography type comes in and is both fast and accurate.

In the end, the Geography type seems best suited to calculations and the decimal values are better off used for searching.

So, once you have a bunch of coordinates in a table (PointsTable) and have them ordered properly and referenced by a header value, here is how you can quickly turn the line into a Geography instance. I do this when I am adding records to the PointsTable and store the output in the header table. (HeaderID is the inbound parameter.) I'm not really fond of the SubString statement in here but I haven't worked out how to append to LineList conditionally.

Declare @HeaderID Int
Select @HeaderID = 123

Declare @LineList VarChar(Max)
Declare @GeoVal Geography
Select @LineList = @LineList +
      Convert(VarChar(100), Longitude)
      + ' '
      + Convert(VarChar(100), Latitude) + ', '
From dbo.PointsTable
Where HeaderID = @HeaderID
Order By Ordinal

Select @LineList =
      SubString(@LineList, 1, (Len(@LineList) - 2))
Select @LineList =
      'LINESTRING(' + @LineList + ')'
Select @GeoVal =
      Geography::STLineFromText(@LineList, 4326);

1 Comment