Calculate Distance in Miles from Latitude and Longitude

  • alastair-804470 (4/15/2011)


    Well, yes... but in that case you've probably got other problems. Like no support 😉

    And no budget...

  • You work in the healthcare industry and you've got no budget? You surprise me 😉

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

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

  • 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

  • Well for the real world it would be 1.60934 kilometers per mile

  • It 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.htm

  • For 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 where you are, for which you may have to take the irregularity of the geoid 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 !!

  • I'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:

    DECLARE @lat1 float

    DECLARE @lat2 float

    DECLARE @lon1 float

    DECLARE @lon2 float

    SET @lat1 = 51.164429

    SET @lat2 = 51.164429

    SET @lon1 = -1.765275

    SET @lon2 = -1.765275

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

    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,

    Nick

  • I had the same problem years ago. You have to do an interim check to the value you send to ACOS; see function below ...

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    /*

    * 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 FLOAT

    AS

    BEGIN

    DECLARE @d FLOAT

    DECLARE @n FLOAT

    DECLARE @k_earth_mean_radius_km FLOAT

    DECLARE @p1_lat FLOAT

    DECLARE @p1_lon FLOAT

    DECLARE @p2_lat FLOAT

    DECLARE @p2_lon FLOAT

    SET @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 = 1

    ELSE

    IF ( @n < -1 )

    SET @n = -1

    SET @d = ACOS( @n ) * @k_earth_mean_radius_km

    RETURN @d

    END

  • Thanks - 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 🙂

    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 metres

    END

  • Sounds good, however, you could comment out the following line and everything will still work:

    IF ((@lat1 = @lat2) AND (@lon1 = @lon2)) RETURN 0

    Also, see the wiki article regarding earth radius at:

    https://en.wikipedia.org/wiki/Great-circle_distance

    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.

    Not sure about your application needs, but the value above might help reduce errors in distance calculations.

  • Hello my fella DBA

    I just accept a position utilize spatial data types in Sql Server. Can someone email me a tutorial on where I can learn as much as possible on the topic.

    rogersdba@yahoo.com

  • Hi Robert,

    Depending on how you plan to use the lat-long calculator for miles, it works great for "as the crow flies" calculations. Unfortunately, not so great for distance calculations for auto or truck mileages. You probably will need to utilize a 3rd party mapping application that can account for the various road infrastructures.

    Just a heads up!

Viewing 14 posts - 46 through 59 (of 59 total)

You must be logged in to reply to this topic. Login to reply