SQLServerCentral.com / Article Discussions by Author / Article Discussions / Discuss content posted by RBarryYoung / Calculate Distance in Miles from Latitude and Longitude / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 11 Dec 2016 03:48:15 GMT20RE: Calculate Distance in Miles from Latitude and Longitudehttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxSounds good, however, you could comment out the following line and everything will still work:[code="plain"]IF ((@lat1 = @lat2) AND (@lon1 = @lon2)) RETURN 0 [/code]Also, see the wiki article regarding earth radius at:[url]https://en.wikipedia.org/wiki/Great-circle_distance[/url][quote]A good choice for the radius is the mean earth radius, ... 6371.0 km (for the WGS84 ellipsoid); in the limit of small flattening, this choice minimizes the mean square relative error in the estimates for distance.[/quote]Not sure about your application needs, but the value above might help reduce errors in distance calculations.Wed, 03 Feb 2016 08:02:53 GMTbdcoderRE: Calculate Distance in Miles from Latitude and Longitudehttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxThanks - I've tested this on a database of 1.8 million postcodes and it seems the short-circuit approach is enough to stop this problem happening on my data:IF ((@lat1 = @lat2) AND (@lon1 = @lon2)) RETURN 0 However I will also do your ACOS value check just for belt and braces :)[code="sql"]BEGIN IF ((@lat1 = @lat2) AND (@lon1 = @lon2)) RETURN 0 -- short circuit for case where the same coords are supplied DECLARE @n float SET @n = SIN(@Lat1/57.295779513082323) * SIN(@Lat2/57.295779513082323) + COS(@Lat1/57.295779513082323) * COS(@Lat2/57.295779513082323) * COS((@Lon2-@Lon1)/57.295779513082323) IF @n > 1 SET @n = 1 ELSE IF ( @n < -1 ) SET @n = -1 RETURN ACOS(@n) * 6378137 -- average radius of the earth in metresEND[/code]Wed, 03 Feb 2016 02:20:44 GMTNick GilbertRE: Calculate Distance in Miles from Latitude and Longitudehttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxI had the same problem years ago. You have to do an interim check to the value you send to ACOS; see function below ...[code="other"]SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO/** Given two points in decimal degrees, this* function will return the distance in kilometers* between the two points using the spherical law of* cosines ...** Reference: http://en.wikipedia.org/wiki/Great-circle_distance**/CREATE FUNCTION [dbo].[geodistance]( @lat1 FLOAT, @lon1 FLOAT, @lat2 FLOAT, @lon2 FLOAT)RETURNS FLOATAS BEGIN DECLARE @d FLOATDECLARE @n FLOATDECLARE @k_earth_mean_radius_km FLOATDECLARE @p1_lat FLOATDECLARE @p1_lon FLOATDECLARE @p2_lat FLOATDECLARE @p2_lon FLOATSET @k_earth_mean_radius_km = 6371.0/* Convert first point from degrees to radians ... */SET @p1_lat = RADIANS( @lat1 )SET @p1_lon = RADIANS( @lon1 )/* Convert second point from degrees to radians ... */SET @p2_lat = RADIANS( @lat2 )SET @p2_lon = RADIANS( @lon2 )SET @n = SIN( @p1_lat ) * SIN( @p2_lat ) + COS( @p1_lat ) * COS( @p2_lat ) * COS( @p2_lon - @p1_lon )/** Range check interim result as ACOS only accepts values from -1 through 1.* Values outside this range return NULL and report a domain error; this is* reported as "An invalid floating point operation occurred." ...*/IF @n > 1 SET @n = 1ELSE IF ( @n < -1 ) SET @n = -1SET @d = ACOS( @n ) * @k_earth_mean_radius_kmRETURN @dEND[/code]Tue, 02 Feb 2016 08:37:40 GMTbdcoderRE: Calculate Distance in Miles from Latitude and Longitudehttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxI'm having a problem with this function.I find that in some situations, I get an error: "An invalid floating point operation occurred."This is true for instance, in *some* (not all) cases where both the coordinates are the same.e.g: this results in an error: [code="sql"]DECLARE @lat1 floatDECLARE @lat2 floatDECLARE @lon1 floatDECLARE @lon2 floatSET @lat1 = 51.164429SET @lat2 = 51.164429SET @lon1 = -1.765275SET @lon2 = -1.765275PRINT 6378137 -- average radius of the earth in metres *ACOS(SIN(@Lat1/57.295779513082323) * SIN(@Lat2/57.295779513082323) + COS(@Lat1/57.295779513082323) * COS(@Lat2/57.295779513082323) * COS((@Lon2-@Lon1)/57.295779513082323))[/code]It seems to be because ACOS(1) is essentially called in this scenario. I'm not sure if it's enough to just put in a short-circuit like this:IF ((@lat1 = @lat2) AND (@lon2 = @lon2)) BEGIN SET @result = 0...Or if the error could occur in other circumstances also? (my maths is far from great!)Thanks,NickTue, 02 Feb 2016 07:46:41 GMTNick GilbertRE: Calculate Distance in Miles from Latitude and Longitudehttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxFor cadastral surveying (i.e. property / town / state / country boundaries) with corrected GPS readings, we regularly get less than 1cm error per kilometer, which is 0.001% error. Of course, for this you (or your equipment) has to know how far you are from the center of the earth [i]where you are[/i], for which you may have to take the irregularity of the [url=http://en.wikipedia.org/wiki/Geoid]geoid[/url] into account.Even without doing that, the difference between the equatorial radius and polar radius of the earth is only 0.34%, less than the error you impose by not using the exact ratio of kilometers per mile.It's costs nothing extra to use the exact figure, since it's available, of 1.60934 km / mile. Why use a knotted piece of string to measure when you can use a measuring tape?Nice calculation website though. Thanks !!Fri, 26 Sep 2014 11:04:18 GMTDaveBoltmanRE: Calculate Distance in Miles from Latitude and Longitudehttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxIt would be interesting to make a table of measure distances and calculated distances for different calculations to see if this is a meaningful problem for your users. It is unlikely any one will need more than a 99% accuracy for many uses of the calculation.Here is a web calculator:http://www.chemical-ecology.net/java/lat-long.htmFri, 26 Sep 2014 07:28:22 GMTRobert.SterbalRE: Calculate Distance in Miles from Latitude and Longitudehttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxWell for the real world it would be 1.60934 kilometers per mileFri, 26 Sep 2014 04:54:55 GMTDaveBoltmanRE: Calculate Distance in Miles from Latitude and Longitudehttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxAwesome, 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%20milesFri, 26 Sep 2014 04:53:39 GMTDaveBoltmanRE: Calculate Distance in Miles from Latitude and Longitudehttp://www.sqlservercentral.com/Forums/Topic651657-1226-1.aspxPossibly similar I adapted [url=http://bluemm.blogspot.co.uk/2007/01/excel-formula-to-calculate-distance.html]some code[/url] to create my version:[quote](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[/quote]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.Fri, 26 Sep 2014 02:58:52 GMTalindupRE: Calculate Distance in Miles from Latitude and Longitudehttp://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.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.Thu, 21 Apr 2011 09:39:00 GMTNadrekRE: Calculate Distance in Miles from Latitude and Longitudehttp://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-804470RE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTFargoUTRE: Calculate Distance in Miles from Latitude and Longitudehttp://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-804470RE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTjcrawf02RE: Calculate Distance in Miles from Latitude and Longitudehttp://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-804470RE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTFargoUTRE: Calculate Distance in Miles from Latitude and Longitudehttp://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.bernsteinRE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTjcrawf02RE: Calculate Distance in Miles from Latitude and Longitudehttp://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.bernsteinRE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTCarunclesRE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTFargoUTRE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTjcrawf02RE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTFargoUTRE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTmbarracloughRE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTjcrawf02RE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTCarunclesRE: Calculate Distance in Miles from Latitude and Longitudehttp://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) SmithRE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTbdcoderRE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTjcrawf02RE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTCarunclesRE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTRBarryYoungRE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTjcrawf02RE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTRBarryYoungRE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTjcrawf02RE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTRBarryYoungRE: Calculate Distance in Miles from Latitude and Longitudehttp://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-684746RE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTRBarryYoungRE: Calculate Distance in Miles from Latitude and Longitudehttp://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-804470RE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTRBarryYoungRE: Calculate Distance in Miles from Latitude and Longitudehttp://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 GMTjcrawf02