Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Calculate Distance in Miles from Latitude and Longitude Rate Topic Display Mode Topic Options
Author
 Message
 Posted Friday, April 15, 2011 1:58 PM
 SSC 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 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. SMBThat 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 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 Group: General Forum Members Last Login: Monday, December 8, 2014 6:44 AM Points: 1,332, Visits: 19,320
 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 questionHow to post performance problemsTally 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 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 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 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! Group: General Forum Members Last Login: Today @ 9:36 AM Points: 893, Visits: 2,476
 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 Group: General Forum Members Last Login: Friday, November 14, 2014 6:41 AM 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_MYour 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 Group: General Forum Members Last Login: Tuesday, December 9, 2014 12:48 AM Points: 5, Visits: 88
 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

 Permissions