February 17, 2009 at 12:58 pm
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).
[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:01 pm
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.
[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:03 pm
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"
Ba-dum 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[/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 1:43 pm
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. 🙂
[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 18, 2009 at 1:35 am
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...)
February 18, 2009 at 7:46 am
It's a fair point, alastair. In retrospect, although I knew about this layers of formula accuracy issue, I failed to take it into account in my article's original statement. There i was only taking into account the mathematical accuracy of the Law of Cosines equations. I failed to account for the physical inaccuracy of the model assumed by the formula, which does reduce it's actual accuracy down to about 2-3 places.
[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]
May 21, 2009 at 8:14 pm
The sample code is wrong.. the function is named LatLonDistance - so replace calculateDistance with LatLonDistance
May 21, 2009 at 8:45 pm
stevewagner (5/21/2009)
The sample code is wrong.. the function is named LatLonDistance - so replace calculateDistance with LatLonDistance
Yes, the sample test code has a typo in it. jcrwf02 caught this earlier also. Silly me I decided to "improve" the name right before I posted it, so ofcourse I didn't change it everywhere. :(.
[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]
May 22, 2009 at 6:26 am
RBarryYoung (5/21/2009)
stevewagner (5/21/2009)
The sample code is wrong.. the function is named LatLonDistance - so replace calculateDistance with LatLonDistanceYes, the sample test code has a typo in it. jcrwf02 caught this earlier also. Silly me I decided to "improve" the name right before I posted it, so ofcourse I didn't change it everywhere. :(.
Still, only a very minor issue with an extremely useful set of code. Working on a project right now using it, which will develop into a whole suite of reporting I'm sure once the users sink their teeth into it.
Thanks again!
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."
May 22, 2009 at 1:00 pm
jcrawf02 (5/22/2009)
RBarryYoung (5/21/2009)
stevewagner (5/21/2009)
The sample code is wrong.. the function is named LatLonDistance - so replace calculateDistance with LatLonDistanceYes, the sample test code has a typo in it. jcrwf02 caught this earlier also. Silly me I decided to "improve" the name right before I posted it, so ofcourse I didn't change it everywhere. :(.
Still, only a very minor issue with an extremely useful set of code. Working on a project right now using it, which will develop into a whole suite of reporting I'm sure once the users sink their teeth into it.
Cool! Thanks for the feedback, 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]
July 24, 2009 at 11:54 am
FYI, first report using this is estimated to save 5-7 hours a month in FTE time. They used to have to hunt and peck to find the closest location, guessing by zip code or county, now they have a report that runs in 1.5 minutes.
Thanks again!
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."
July 28, 2009 at 10:48 pm
jcrawf02 (7/24/2009)
FYI, first report using this is estimated to save 5-7 hours a month in FTE time. They used to have to hunt and peck to find the closest location, guessing by zip code or county, now they have a report that runs in 1.5 minutes.Thanks again!
Jon
Sweet! Always nice to hear something like that. 🙂
[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]
April 15, 2011 at 6:30 am
OK guys, on the SQL knowledge scale, I'm about 100 degrees latitude south of all of you. Can someone give me an example of how I would use this?
Wallace Houston
Sunnyland Farms, Inc.
"We must endeavor to persevere."
April 15, 2011 at 7:03 am
Caruncles (4/15/2011)
OK guys, on the SQL knowledge scale, I'm about 100 degrees latitude south of all of you. Can someone give me an example of how I would use this?
Assuming you meant *when* you would use this, since Barry gave some examples of code using it in the article?
I'm using it in a healthcare setting, when we have a doctor who terms their contract with us, and we need to re-assign members. This allows us to find the next closest doctor for that member, from the previous doc's office location.
---------------------------------------------------------
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."
April 15, 2011 at 7:14 am
The formula given is the "law of cosines" -- the constant 3963.0 is the earths mean radius (in miles).
To change the formula to km, replace 3963.0 with 6371.0
Altenatively, and I think its already been mentioned, you can simply take the result and multiply by 1.609344 ( 1 mile = 1.609344 km).
Viewing 15 posts - 16 through 30 (of 58 total)
You must be logged in to reply to this topic. Login to reply