locked
How to find the distance between coordinates? RRS feed

Answers

  • Is there any reason you can't use the native SQL Server geography spatial data type for this problem?  For example:

    DECLARE @loc1 geography
    	,@loc2 geography;
    	
    SELECT @loc1 = geography::Point(40.742828, -73.952418, 4326)
    	,@loc2 = geography::Point(0.008064, 0.020664, 4326);
    	
    SELECT @loc1.STDistance(@loc2);

    Jason


    • Edited by Jason L Brugger Friday, September 27, 2013 3:00 PM
    • Marked as answer by ryguy72 Saturday, September 28, 2013 11:00 PM
    Friday, September 27, 2013 3:00 PM
  • The result is in meters. When I use a Google Maps Distance Calculator (http://www.daftlogic.com/projects-google-maps-distance-calculator.htm) to show the distance between your revised points, the result is 4219.957 meters which is pretty close to your result (4224.064) considering they are different systems with potentially different algorithms and projections, and given the nature of spatial reference systems in the first place.

    Also, don't confuse the distance between-two-points as the length of a walking-route between them.

    4326 is the Spatial Reference System Identifier (SRID). SpatialReference.org has a list, but I don't know for certain which of the many SRIDs are supported by SQL Server, and the only SRID I have consistently used is 4326, which is described as "Used by the GPS satellite navigation system and for NATO military geodetic surveying." It is also used in Microsoft's spatial data documentation.  For fun, I tried SRID 4979 in place of 4326 and it yielded the same result.

    Wikipedia is a great place to start learning about the Open Geospatial Consortium (OGC), World Geodetic System (WGS), European Petroleum Survey Group (EPSG) to better understand where all of these spatial reference systems come from.  Its a huge subject of which I have only scratched the surface myself.

    Please mark as answer if I've helped you!


    Jason

    • Marked as answer by ryguy72 Saturday, September 28, 2013 10:59 PM
    Saturday, September 28, 2013 3:56 PM

All replies