

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...




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.
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 inexactbutclose enough coordinates.




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.




Ten Centuries
Group: General Forum Members
Last Login: Friday, January 30, 2015 8:42 AM
Points: 1,332,
Visits: 19,324


alastair804470 (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."




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 ;)




Old Hand
Group: General Forum Members
Last Login: Tuesday, October 11, 2011 1:51 PM
Points: 341,
Visits: 311


alastair804470 (4/15/2011) Well, yes... but in that case you've probably got other problems. Like no support ;)
And no budget...




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 ;)




SSC Eights!
Group: General Forum Members
Last Login: Monday, June 27, 2016 8:37 AM
Points: 912,
Visits: 2,656


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.




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(90SD.StuLat)) *COS(RADIANS(90BS.SchLat)) +SIN(RADIANS(90SD.StuLat)) *SIN(RADIANS(90BS.SchLat)) *COS(RADIANS(SD.StuLongBS.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.




Forum Newbie
Group: General Forum Members
Last Login: Tuesday, May 31, 2016 5:03 AM
Points: 7,
Visits: 143


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=chromeinstant&ion=1&espv=2&ie=UTF8#q=radius%20of%20earth%20in%20miles



