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 «««23456»»

Calculate Distance in Miles from Latitude and Longitude Expand / Collapse
Author
Message
Posted Friday, April 15, 2011 1:58 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, May 10, 2011 8:16 AM
Points: 29, 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...
Post #1094383
Posted Friday, April 15, 2011 2:01 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 11, 2011 1:51 PM
Points: 341, Visits: 311
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.
Post #1094384
Posted Friday, April 15, 2011 2:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 1, 2012 10:31 AM
Points: 17, 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.
Post #1094390
Posted Friday, April 15, 2011 2:18 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, October 20, 2014 11:55 AM
Points: 1,330, Visits: 19,306
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."
Post #1094393
Posted Friday, April 15, 2011 2:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 1, 2012 10:31 AM
Points: 17, Visits: 105
Well, yes... but in that case you've probably got other problems. Like no support ;)
Post #1094407
Posted Friday, April 15, 2011 3:16 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, October 11, 2011 1:51 PM
Points: 341, Visits: 311
alastair-804470 (4/15/2011)
Well, yes... but in that case you've probably got other problems. Like no support ;)


And no budget...
Post #1094424
Posted Friday, April 15, 2011 3:36 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 1, 2012 10:31 AM
Points: 17, Visits: 105
You work in the healthcare industry and you've got no budget? You surprise me ;)
Post #1094434
Posted Thursday, April 21, 2011 9:39 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 11:56 AM
Points: 880, Visits: 2,435
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.
Post #1097083
Posted Friday, September 26, 2014 2:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 26, 2014 7:34 AM
Points: 2, Visits: 11
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.
Post #1620120
Posted Friday, September 26, 2014 4:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 6:28 AM
Points: 4, Visits: 76
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
Post #1620156
« Prev Topic | Next Topic »

Add to briefcase «««23456»»

Permissions Expand / Collapse