

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/BeginningSpatialSQLServer2008/dp/1430218290




SSCrazy Eights
Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902,
Visits: 9,479


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, 31Jan2009 , KM: 17Feb2009 */ As Begin 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)3750451 blog: MovingSQL.com, Twitter: @RBarryYoung Proactive Performance Solutions, Inc. "Performance is our middle name."




SSCrazy Eights
Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902,
Visits: 9,479





SSCrazy Eights
Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902,
Visits: 9,479


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)3750451 blog: MovingSQL.com, Twitter: @RBarryYoung Proactive Performance Solutions, Inc. "Performance is our middle name."




SSCrazy Eights
Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902,
Visits: 9,479


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 "pearshaped", 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)3750451 blog: MovingSQL.com, Twitter: @RBarryYoung Proactive Performance Solutions, Inc. "Performance is our middle name."




SSCrazy Eights
Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902,
Visits: 9,479


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)3750451 blog: MovingSQL.com, Twitter: @RBarryYoung Proactive Performance Solutions, Inc. "Performance is our middle name."




SSCrazy Eights
Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902,
Visits: 9,479


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)3750451 blog: MovingSQL.com, Twitter: @RBarryYoung Proactive Performance Solutions, Inc. "Performance is our middle name."




SSCrazy
Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:12 AM
Points: 2,627,
Visits: 19,093


RBarryYoung (2/17/2009)
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).
Joke time taking advantage of the margin of error:
Pilot flying over the USA gets lost, asks copilot where he is within reasonable certainty. Copilot says "it's either Florida or Georgia"
Badum dum. Don't forget to tip your Dark Lord.
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!
 How best to post your question How to post performance problems Tally Table:What it is and how it replaces a loop
"stewsterl 80804 (10/16/2009)I guess when you stop and try to understand the solution provided you not only learn, but save yourself some headaches when you need to make any slight changes."




SSCrazy Eights
Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902,
Visits: 9,479


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)3750451 blog: MovingSQL.com, Twitter: @RBarryYoung Proactive Performance Solutions, Inc. "Performance is our middle name."




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...)



