Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculate Distance in Miles from Latitude and Longitude Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, February 17, 2009 11:31 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, May 01, 2012 10:31 AM Points: 17, Visits: 105
 Of course, if you are using SQL Server 2008, you don't need to use spherical approximations such as this - just use the inbuilt spatial functions:Point1.STDistance(Point2)STDistance() gives a more accurate result than the example here because it is based on an ellipsoidal model of the earth rather than a perfectly spherical model. Also you can use it to find the distance between any type of feature, not just points. (i.e. to calculate how close a route passes to a point, or the distance from a point to the edge of a polygon)...http://www.amazon.com/Beginning-Spatial-SQL-Server-2008/dp/1430218290
Post #658700
 Posted Tuesday, February 17, 2009 12:21 PM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, March 06, 2014 5:22 PM Points: 9,893, Visits: 9,463
 Henk Schreij (2/17/2009)Please could you supply us a "km version" of this nice script.In Europe, Australia, Asia, etc. we don't use miles or feet. ;)Then how do you walk? :P Heh, just kidding...You make an excellent point, like most folks who have spent their whole life in the USA, I sometimes forget that everyone else uses metric. Anyway, the key to this rescaling (and the others requested) is the factor that appears on the first line, right before the "acos(": 3963.0. This is the radius (maximum) of the Earth in miles. If you want it in kilometers, just change this number to the radius of the earth in kilometers (6378 max, 6357 avg):`Create Function LatLonDistKM( @Lat1 Float, @Lon1 Float, @Lat2 Float, @Lon2 Float) Returns Float/* Faster way to calculate distance in kilometers using Latitude & Longitude. This is accurate in KM to about 4.5 decimal places, except for very small distances.NOTE: 57.295779513082323 = 180.0 / PI (converts Lat/Lon degrees to spherical radians)Ref: The formula is derived from the Spherical Law of Cosines, --RBarryYoung, 31-Jan-2009, KM: 17-Feb-2009*/ AsBegin Return 6357.0*acos( sin(@Lat1/57.295779513082323) * sin(@Lat2/57.295779513082323) + cos(@Lat1/57.295779513082323) * cos(@Lat2/57.295779513082323) * cos((@Lon2-@Lon1)/57.295779513082323))End` -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #658737
 Posted Tuesday, February 17, 2009 12:22 PM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, March 06, 2014 5:22 PM Points: 9,893, Visits: 9,463
 Tim Pain (2/17/2009)Really neat and just what I was looking for.Many thanksTimThanks for the feedback, Tim. -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #658739
 Posted Tuesday, February 17, 2009 12:23 PM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, March 06, 2014 5:22 PM Points: 9,893, Visits: 9,463
 jcrawf02 (2/17/2009)RBarryYoung, am I missing something here? the function name is LatLonDistance(), but the tests use calculateDistance()? Is there a missing piece to this, or is it just a naming convention typo?Just a typo. Good catch, Jon. :) -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #658741
 Posted Tuesday, February 17, 2009 12:52 PM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, March 06, 2014 5:22 PM Points: 9,893, Visits: 9,463
 kevin (2/17/2009)Because the earth is not strictly spherical (it "flattens" a bit due to its rotation), there's a mathematical adjustment that can be made which makes the result even more accurate, given any two locations worldwide.That's correct, the earth is an oblate spheroid (sort of), so any calculation based on a sphere (as mine is) has some amount of error, that varies from place to place on the planet. In my case, because I have used the equatorial radius (the maximum) the error varies from about 0% at the equator to about +2% at the poles (where we rarely drive or fly). If you bump the radius down to the mean (3959) the error shifts to about +/-1%. And yes, there is a more complicated formula that corrects for this.However, even that formula incorporates some (smaller) error, because the earth is also not strictly an oblate spheriod. Rather it is slightly asymmetric because the northern hemisphere and the southern hemisphere are not the same in their "oblateness" (one is wider, on the average than the other, but I cannot remember which way it goes). Thus the earth is actually slightly "pear-shaped", and yes, there is a still more complicated formula to correct for this.But it doesn't stop there, there are significant regional variations in the earth too. Then there is the fact that the Earth's deformations actually change over time... And yes, there are functions/data tables that can correct for these as well, but at some point along the way here, we've stopped being an easy function for everyday SQL Server use and started to be NASA.The point here is that there is no absolute best formula or approach to use, because you have to trade off your time and performance against what accuracy you actually need. I believe that my function is appropiate for most general uses. -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #658763
 Posted Tuesday, February 17, 2009 12:58 PM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, March 06, 2014 5:22 PM Points: 9,893, Visits: 9,463
 fredsimpson (2/17/2009)As you are using Lat/Long and I do not see any conversion, I am assuming that the distance is measured in Nautical Miles not Statute Miles is it not?Nope, it's in Statute Miles. The conversion is in the scaling factor on the first line right after the "Return ": 3963.0, which is the radius of the Earth(max) in Statute Miles. For Nautical Miles, just change it to the radius of the Earth in Nautical Miles: (3443.7 max, 3440.3 mean). -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #658770
 Posted Tuesday, February 17, 2009 1:01 PM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, March 06, 2014 5:22 PM Points: 9,893, Visits: 9,463
 alastair (2/17/2009)Of course, if you are using SQL Server 2008, you don't need to use spherical approximations such as this - just use the inbuilt spatial functions:Point1.STDistance(Point2)STDistance() gives a more accurate result than the example here because it is based on an ellipsoidal model of the earth rather than a perfectly spherical model. Also you can use it to find the distance between any type of feature, not just points. (i.e. to calculate how close a route passes to a point, or the distance from a point to the edge of a polygon)...This is correct. However, most folks are still on SS2005 or even SS2000 and we do see this question from time to time, so I figure that it's still useful. -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #658774
 Posted Tuesday, February 17, 2009 1:03 PM
 SSCrazy Group: General Forum Members Last Login: Today @ 9:09 AM Points: 2,612, Visits: 19,051
Post #658778
 Posted Tuesday, February 17, 2009 1:43 PM
 SSCrazy Eights Group: General Forum Members Last Login: Thursday, March 06, 2014 5:22 PM Points: 9,893, Visits: 9,463
 jcrawf02 (2/17/2009)I believe that my function is appropiate for most general uses.That's because you don't know what devious plans we have! Sharks with frickin' lasers on their heads!In that case, I am actually grateful for any inaccuracy I may have introduced. :) -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #658836
 Posted Wednesday, February 18, 2009 1:35 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, May 01, 2012 10:31 AM Points: 17, Visits: 105
 Oh, please don't get me wrong - it's absolutely still a useful script - I was just drawing people's attention to the fact that such features are inbuilt in SQL Server 2008 and if you really want to make the most of GPS/Spatial data you will want to do more than simply calculate the distance between two points. Sorry if my post came out as more abrasive than I intended.Regarding the accuracy issue - in my experience people often only state lat/long coordinates to a few degrees of accuracy anyway, in which case the assumptions regarding the earth curvature become rather irrelevant anyway (sigh...)
Post #659147

 Permissions