InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 25 May 2013 16:53:56 GMT20http://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxBe 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 [url=http://www.csgnetwork.com/degreelenllavcalc.html]http://www.csgnetwork.com/degreelenllavcalc.html[/url])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.Thu, 21 Apr 2011 09:39:00 GMTNadrekhttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxYou work in the healthcare industry and you've got no budget? You surprise me ;)Fri, 15 Apr 2011 15:36:33 GMTalastair-804470http://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]alastair-804470 (4/15/2011)[/b][hr]Well, yes... but in that case you've probably got other problems. Like no support ;)[/quote]And no budget...Fri, 15 Apr 2011 15:16:43 GMTFargoUThttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxWell, yes... but in that case you've probably got other problems. Like no support ;)Fri, 15 Apr 2011 14:42:26 GMTalastair-804470http://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]alastair-804470 (4/15/2011)[/b][hr]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.[/quote]Unless you're on something before 2008 ;-)Fri, 15 Apr 2011 14:18:52 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxYou 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.Fri, 15 Apr 2011 14:11:08 GMTalastair-804470http://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]scott.bernstein (4/15/2011)[/b][hr]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[/quote]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.Fri, 15 Apr 2011 14:01:00 GMTFargoUThttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxI saw that there was talk about the earth not being a perfect sphere. I didn't see anything mentioned about Sedonos though... :-)Fri, 15 Apr 2011 13:58:29 GMTscott.bernsteinhttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]scott.bernstein (4/15/2011)[/b][hr]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[/quote]Read back a page or two, think that topic got beat to death already. :-)Fri, 15 Apr 2011 13:43:16 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxI 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. SMBFri, 15 Apr 2011 12:51:09 GMTscott.bernsteinhttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxYea, I can use that. Except I'll probably convert it to feet. Thanx!Fri, 15 Apr 2011 12:13:47 GMTCaruncleshttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx@jcrawf02: I'm not sure that's what Caruncle wants, but I thought I would just post a tangential use for such a function. I use it for determining location of doctors in the vicinity of a hospital, which helps for reporting purposes. :-)Fri, 15 Apr 2011 12:05:28 GMTFargoUThttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxFargoUT, you're making me realize that perhaps what Caruncles is looking for requires a little more info. If you don't have geocoding software to turn addresses into lat/long, then it might be a little stickier, but you can get that data online too, [url=http://stevemorse.org/jcal/latlonbatch.html]http://stevemorse.org/jcal/latlonbatch.html[/url] being a good example of that. Then use Barry's function (or use it inline if you want) to get the distance between two addresses.**Edit (oops, Caruncles, not Carbuncles lol)Fri, 15 Apr 2011 11:30:39 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxFor US developers, this would be helpful in combination with a dataset of zip codes and their lat/long coordinates (this is one I've found useful: [url]http://federalgovernmentzipcodes.us/[/url]). Additionally, you could adjust the function a bit and compare the distances between zip codes. Microsoft even provides a stored procedure for this functionality: [url]http://msdn.microsoft.com/en-us/library/ms980211.aspx[/url]. I use a modified version of this whenever I want to find the zip codes within N number of miles of a zip code. When we open a new hospital, we will often use this to determine what zip codes will be best served by the hospital.Fri, 15 Apr 2011 11:16:26 GMTFargoUThttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxIs there some reason you didn't simply use the sql radians() function instead of doing the division with your constant?Fri, 15 Apr 2011 11:00:40 GMTmbarracloughhttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxFor Ari and Caruncles both, this is point to point data, "as the crow flies", so road miles are not considered at all. That might mean you're only 2 miles away with this calculation, but there's a mountain, so it's a forty-mile drive to get there. Solution? Don't live near mountains.Fri, 15 Apr 2011 08:11:16 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxActually I meant both, but realized right after I posted that the example (test) was commented right there in the code. Sorry for being such a rookie, but your answer was also helpful. I'm guessing the Google Maps and TerraServers of the world would use code like this.Fri, 15 Apr 2011 07:34:37 GMTCaruncleshttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxBarry,How does this formula compare in acurracy to the road mileage programs like ALK-PC Miler and Rand McNally IntelleRoute Software? How does your formula apply road infastructure to the calculation?Thanks,Ari SmithFri, 15 Apr 2011 07:32:43 GMTAristides (Ari) Smithhttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxThe formula given is the "law of cosines" -- the constant 3963.0 is the earths mean radius (in miles).To change the formula to km, replace 3963.0 with 6371.0Altenatively, and I think its already been mentioned, you can simply take the result and multiply by 1.609344 ( 1 mile = 1.609344 km).Fri, 15 Apr 2011 07:14:30 GMTbdcoderhttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]Caruncles (4/15/2011)[/b][hr]OK guys, on the SQL knowledge scale, I'm about 100 degrees latitude south of all of you. Can someone give me an example of how I would use this?[/quote]Assuming you meant *when* you would use this, since Barry gave some examples of code using it in the article?I'm using it in a healthcare setting, when we have a doctor who terms their contract with us, and we need to re-assign members. This allows us to find the next closest doctor for that member, from the previous doc's office location.Fri, 15 Apr 2011 07:03:46 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxOK guys, on the SQL knowledge scale, I'm about 100 degrees latitude south of all of you. Can someone give me an example of how I would use this?Fri, 15 Apr 2011 06:30:13 GMTCaruncleshttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]jcrawf02 (7/24/2009)[/b][hr]FYI, first report using this is estimated to save 5-7 hours a month in FTE time. They used to have to hunt and peck to find the closest location, guessing by zip code or county, now they have a report that runs in 1.5 minutes.Thanks again!Jon[/quote]Sweet! Always nice to hear something like that. :-)Tue, 28 Jul 2009 22:48:14 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxFYI, first report using this is estimated to save 5-7 hours a month in FTE time. They used to have to hunt and peck to find the closest location, guessing by zip code or county, now they have a report that runs in 1.5 minutes.Thanks again!JonFri, 24 Jul 2009 11:54:37 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]jcrawf02 (5/22/2009)[/b][hr][quote][b]RBarryYoung (5/21/2009)[/b][hr][quote][b]stevewagner (5/21/2009)[/b][hr]The sample code is wrong.. the function is named LatLonDistance - so replace calculateDistance with LatLonDistance[/quote]Yes, the sample test code has a typo in it. jcrwf02 caught this earlier also. Silly me I decided to "improve" the name right before I posted it, so ofcourse I didn't change it everywhere. :(.[/quote]Still, only a very minor issue with an extremely useful set of code. Working on a project right now using it, which will develop into a whole suite of reporting I'm sure once the users sink their teeth into it. [/quote]Cool! Thanks for the feedback, Jon. :-)Fri, 22 May 2009 13:00:31 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]RBarryYoung (5/21/2009)[/b][hr][quote][b]stevewagner (5/21/2009)[/b][hr]The sample code is wrong.. the function is named LatLonDistance - so replace calculateDistance with LatLonDistance[/quote]Yes, the sample test code has a typo in it. jcrwf02 caught this earlier also. Silly me I decided to "improve" the name right before I posted it, so ofcourse I didn't change it everywhere. :(.[/quote]Still, only a very minor issue with an extremely useful set of code. Working on a project right now using it, which will develop into a whole suite of reporting I'm sure once the users sink their teeth into it. Thanks again!JonFri, 22 May 2009 06:26:27 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]stevewagner (5/21/2009)[/b][hr]The sample code is wrong.. the function is named LatLonDistance - so replace calculateDistance with LatLonDistance[/quote]Yes, the sample test code has a typo in it. jcrwf02 caught this earlier also. Silly me I decided to "improve" the name right before I posted it, so ofcourse I didn't change it everywhere. :(.Thu, 21 May 2009 20:45:30 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxThe sample code is wrong.. the function is named LatLonDistance - so replace calculateDistance with LatLonDistanceThu, 21 May 2009 20:14:08 GMTstevewagner-684746http://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxIt's a fair point, alastair. In retrospect, although I knew about this layers of formula accuracy issue, I failed to take it into account in my article's original statement. There i was only taking into account the mathematical accuracy of the Law of Cosines equations. I failed to account for the physical inaccuracy of the model assumed by the formula, which does reduce it's actual accuracy down to about 2-3 places.Wed, 18 Feb 2009 07:46:34 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxOh, 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...)Wed, 18 Feb 2009 01:35:23 GMTalastair-804470http://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]jcrawf02 (2/17/2009)[/b][hr][quote]I believe that my function is appropiate for most general uses.[/quote]That's because you don't know what devious plans we have! Sharks with frickin' lasers on their heads![/quote]In that case, I am actually grateful for any inaccuracy I may have introduced. :)Tue, 17 Feb 2009 13:43:50 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]RBarryYoung (2/17/2009)[/b][hr][quote][b]kevin (2/17/2009)[/b][hr]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.[/quote]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). [/quote]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. [quote]I believe that my function is appropiate for most general uses.[/quote]That's because you don't know what devious plans we have! Sharks with frickin' lasers on their heads!Tue, 17 Feb 2009 13:03:17 GMTjcrawf02http://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]alastair (2/17/2009)[/b][hr]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)...[/quote]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.Tue, 17 Feb 2009 13:01:31 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]fredsimpson (2/17/2009)[/b][hr]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?[/quote]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).Tue, 17 Feb 2009 12:58:57 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]kevin (2/17/2009)[/b][hr]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.[/quote]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 [i]also[/i] 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.Tue, 17 Feb 2009 12:52:00 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]jcrawf02 (2/17/2009)[/b][hr]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?[/quote]Just a typo. Good catch, Jon. :)Tue, 17 Feb 2009 12:23:09 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]Tim Pain (2/17/2009)[/b][hr]Really neat and just what I was looking for.Many thanksTim[/quote]Thanks for the feedback, Tim.Tue, 17 Feb 2009 12:22:19 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspx[quote][b]Henk Schreij (2/17/2009)[/b][hr]Please could you supply us a "km version" of this nice script.In Europe, Australia, Asia, etc. we don't use miles or feet. ;)[/quote]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(": [b]3963.0[/b]. 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):[code]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*/ AsBegin 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[/code]Tue, 17 Feb 2009 12:21:45 GMTRBarryYounghttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxOf 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/1430218290Tue, 17 Feb 2009 11:31:15 GMTalastair-804470http://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxI'm sure it's possible... but to avoid redoing 1 year of maths or waiting for someone to redo the calculations for that constant, I'll just take the small perf hit and do result * 1.6 :w00t:.Tue, 17 Feb 2009 07:48:49 GMTNinja's_RGR'ushttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxQuote: [font="Courier New"]"Just do Result time 1.6. Easy enough to include in the function."[/font]I was hoping for new constants in the formula ;)But I realize the formula doesn't need a 6 or more numbers precision.Tue, 17 Feb 2009 07:43:51 GMTHenk Schreij