February 6, 2009 at 7:32 am
Comments posted to this topic are about the item Calculate Distance in Miles from Latitude and Longitude
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 17, 2009 at 1:16 am
Please could you supply us a "km version" of this nice script.
In Europe, Australia, Asia, etc. we don't use miles or feet. 😉
February 17, 2009 at 2:52 am
Really neat and just what I was looking for.
Many thanks
Tim
Tim
February 17, 2009 at 6:21 am
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?
Thanks,
Jon
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"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."
February 17, 2009 at 7:14 am
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.
(I researched this about a year ago for a web application involving searching for points of interest within a specified radius of a reference point. I found various algorithms and chose one, which I translated to VBScript. It's nice to see the SQL implementation.)
February 17, 2009 at 7:17 am
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.(I researched this about a year ago for a web application involving searching for points of interest within a specified radius of a reference point. I found various algorithms and chose one, which I translated to VBScript. It's nice to see the SQL implementation.)
And that adjustment is?
---------------------------------------------------------
How best to post your question[/url]
How to post performance problems[/url]
Tally Table:What it is and how it replaces a loop[/url]
"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."
February 17, 2009 at 7:19 am
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?
February 17, 2009 at 7:27 am
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. 😉
Just do Result time 1.6. Easy enough to include in the function :D.
February 17, 2009 at 7:43 am
Quote: [font="Courier New"]"Just do Result time 1.6. Easy enough to include in the function."[/font]
I was hoping for new constants in the formula 😉
But I realize the formula doesn't need a 6 or more numbers precision.
February 17, 2009 at 7:48 am
I'm sure it's possible... but to avoid redoing 1 year of maths or waiting for someone to redo the calculations for that constant, I'll just take the small perf hit and do result * 1.6 :w00t:.
February 17, 2009 at 11:31 am
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
February 17, 2009 at 12:21 pm
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? 😛 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
*/ 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
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 17, 2009 at 12:22 pm
Tim Pain (2/17/2009)
Really neat and just what I was looking for.Many thanks
Tim
Thanks for the feedback, Tim.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 17, 2009 at 12:23 pm
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. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
February 17, 2009 at 12:52 pm
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 1 through 15 (of 58 total)
You must be logged in to reply to this topic. Login to reply