CODEWITHSUNDEEP
sql-server-2008t-sqlgeocodingspatial-query
Steve Salowitz
Steve Salowitz

Reputation: 1335

Distance Between Two Geo Coordinates

I am just starting to work with spatial data in SQL Server (2008 r2). I am looking to calculate the distance between two coordinates (miles). 

DECLARE @source geography
DECLARE @target geography

SET @source = geography::STGeomFromText('POINT (43.420026 -83.974472)', 4326);
SET @target = geography::STGeomFromText('POINT (43.458786 -84.029471)', 4326);

SELECT @source.STDistance(@target)/1609.344 -- meters to miles

My query results in a value of 3.827 miles but I checked it against the site linked below and they are returning a distance of 3.85 miles. Am I doing this incorrectly?

http://www.boulter.com/gps/distance/?from=43.420026+-83.974472&to=43.458786+-84.029471&units=m

Upvotes: 1

Views: 851

Answers (1)

stakx - no longer contributing
stakx - no longer contributing

Reputation: 84735

I don't think you're doing anything wrong. Your SQL query looks reasonable to me. But…

I'm far from an expert on spatial reference systems and geodesic stuff (have you considered asking the expert folks over at GIS SE?); nevertheless, here's three possibilites that come to my mind:

  • Perhaps they calculate the distance along a straight line instead of the geodesic distance (i.e. the distance along a curved line, the Earth isn't flat after all). SQL Server's geography type should account for that.

    This does not seem very plausible, given that "their" distance is greater than "yours": you'd expect straight-line distance to be smaller than geodesic distance.

  • Perhaps they can do the calculation more accurately than SQL Server (see note on the MSDN reference page for geography.STDistance)‌​:

    "STDistance() returns the shortest LineString between two geography types. This is a close approximate to the geodesic distance. The deviation of STDistance() on common earth models from the exact geodesic distance is no more than .25%. This avoids confusion over the subtle differences between length and distance in geodesic types."

  • Or, both calculations are somewhat inaccurate, but in opposite directions. If I'm not mistaken, the two results you're citing differ by something like 0.5%, that could just be the sum of their deviation and SQL Server's.

But I could be completely wrong.

Upvotes: 4

Related Questions