SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculate Distance in Miles from Latitude and Longitude


Calculate Distance in Miles from Latitude and Longitude

Author
Message
scott.bernstein
scott.bernstein
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 47
I saw that there was talk about the earth not being a perfect sphere. I didn't see anything mentioned about Sedonos though... :-)
FargoUT
FargoUT
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 312
scott.bernstein (4/15/2011)
I believe you are computing the length of arc on the surface of a sphere - which is cool... But the earth is not a perfect sphere - it's an "oblate spheroid". (it bulges around the equator and is not perfectly symetric) There is an algorithm used in the aerospace industry to compute the distance between two points on the earths surface. Do a google search on "Sedonos Equations". It gets very complicated... Some aerospace companies have patented their implementations of it.

SMB

That will only apply to those who need exact coordinates. I work in the health care industry, and the minute measurements of the earth's circumference is not going to matter quite so much. This function works for those who need inexact-but-close enough coordinates.
alastair-804470
alastair-804470
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 105
You don't need Sedonos equations. Just use SQL Server's own STDistance() method to accurately calculate the distance on the ellipsoid model of the spatial reference system in which your geography data is defined.
jcrawf02
jcrawf02
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5080 Visits: 19324
alastair-804470 (4/15/2011)
You don't need Sedonos equations. Just use SQL Server's own STDistance() method to accurately calculate the distance on the ellipsoid model of the spatial reference system in which your geography data is defined.
Unless you're on something before 2008 ;-)

---------------------------------------------------------
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."
alastair-804470
alastair-804470
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 105
Well, yes... but in that case you've probably got other problems. Like no support Wink
FargoUT
FargoUT
SSC-Addicted
SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)SSC-Addicted (401 reputation)

Group: General Forum Members
Points: 401 Visits: 312
alastair-804470 (4/15/2011)
Well, yes... but in that case you've probably got other problems. Like no support Wink


And no budget...
alastair-804470
alastair-804470
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 105
You work in the healthcare industry and you've got no budget? You surprise me Wink
Nadrek
Nadrek
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4664 Visits: 2741
Be careful; on at least SQL Server 2005 SP3 with a very similar formula, I've seen a very interesting issue where the internal SQL Server rounding results in the ACOS receiving a value very slightly higher than 1, and exploding (with an error or a NULL output of ACOS, depending on ANSI_WARNINGS and ARITHABORT).

Tracking this down was interesting, as the difference is extremely subtle; SQL Server would not show the difference between a working center point and a failing center point in DECIMAL or FLOAT data types. Only a conversion to a (VAR)BINARY type actually showed that the two values were, in fact, different.

To make a long story short, we were operating in a batch environment on a limited set of tens of thousands of points, so the simplest (if not very elegant) answer was at the end of each batch update, set ANSI_WARNINGS and ARITHABORT off, run each point through a trial with the precise math we were using as the center point of a radius, and any points that return a NULL from the ACOS math get moved by 1/100000th of a degree latitude (about 11 cm, per http://www.csgnetwork.com/degreelenllavcalc.html)and retried; there's often a handful that need to be moved by 2/100000th or 3/100000ths of a degree latitude as they turn up NULL on the retest. In all but surveying and specialist applications, even moving a point by a few meters isn't going to be significant compared to the error already present in the calculation (think: elevation + inaccuracies in measuring/recording).

Essentially: whatever lat/long math you're trying to do in SQL, please do test every single point you're going to use as a center to see if it's going to return NULL results for any of them. Postal code tables usually have less than a few million entries in them (for the US, usually less than 50,000 5 digit zip codes are listed), so a full test only takes a few seconds.

ETA: SQL 2005 SP4 build 5432 has the same issue. Worse, the x86 and x64 editions have the same issue, but at different numbers, so you have to check and fix on x86, then x64, and if you're moving zip codes, repeat back and forth until both report 0 problems.
alindup
alindup
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 17
Possibly similar I adapted some code to create my version:

(ACOS(COS(RADIANS(90-SD.StuLat)) *COS(RADIANS(90-BS.SchLat)) +SIN(RADIANS(90-SD.StuLat)) *SIN(RADIANS(90-BS.SchLat)) *COS(RADIANS(SD.StuLong-BS.SchLong))) *3958.756) AS DISTANCE_M


Your version seems more robust and I look forward to playing with it.

I'm new to SQL so haven't gotten my head round variables and spatial data types so this in the Select statement worked.

I used it to work out how far kids travelled to school. And by multiplying it by 1.35 it seemed to give a more accurate distance to account for the fact people don't travel in straight lines.

Many thanks for sharing.
DaveBoltman
DaveBoltman
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 183
Awesome, thanks.

It might be good to put the radius of the earth as a constant at the top of the function (or initialised variable in SQL), so readers of the function can understand how it works, and change it to kilometers, or whatever. The number of degrees per radian (57.29...) would also make a good candidate for a constant since it is repeated several times.

https://www.google.com/webhp?sourceid=chrome-instant&ion=1&espv=2&ie=UTF-8#q=radius%20of%20earth%20in%20miles
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search