Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Calculate Distance in Miles from Latitude and Longitude Expand / Collapse
Author
Message
Posted Tuesday, February 17, 2009 11:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 1, 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

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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
*/ 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)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #658737
Posted Tuesday, February 17, 2009 12:22 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
Tim Pain (2/17/2009)
Really neat and just what I was looking for.

Many thanks
Tim

Thanks for the feedback, Tim.


-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #658739
Posted Tuesday, February 17, 2009 12:23 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #658741
Posted Tuesday, February 17, 2009 12:52 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #658763
Posted Tuesday, February 17, 2009 12:58 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #658770
Posted Tuesday, February 17, 2009 1:01 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #658774
Posted Tuesday, February 17, 2009 1:03 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:42 PM
Points: 2,677, Visits: 19,269
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
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."
Post #658778
Posted Tuesday, February 17, 2009 1:43 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,902, Visits: 9,480
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: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #658836
Posted Wednesday, February 18, 2009 1:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 1, 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
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse