Distance between two locations using Latitude and Longitude way off what Google says.

Question

I've spent a few days trying to figure this one out and can't seem to pinpoint the problems. I have a SQL 2005 database storing latitude and longitude as Decimal(18,8), all of which I received by querying Google.

For these two locations: From: 10715 Downsville Pike Ste 100 MD 21740 to: 444 East College Ave Ste 120 State College PA, 16801

Taking into account that distance will be 'as the crow flies', my results are still way off. In this example my result says 21.32 miles, but Google Maps says 144 miles.

I think the topping that makes it even more frustrating is I found this site: http://jan.ucc.nau.edu/~cvm/latlongdist.html and came up with almost the exact same results as me.

Here's my functions and query:

Functions: CalculateDistance

DECLARE @Temp FLOAT

SET @Temp = SIN(@Latitude1/57.2957795130823) * 
    SIN(@Latitude2/57.2957795130823) + 
    COS(@Latitude1/57.2957795130823) * COS(@Latitude2/57.2957795130823) * 
    COS(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823)

IF @Temp > 1
    SET @Temp = 1
ELSE IF @Temp < -1
    SET @Temp = -1

RETURN (3958.75586574 * ACOS(@Temp) )

LatitudePlusDistance

RETURN (SELECT @StartLatitude + SQRT(@Distance * @Distance / 4766.8999155991))

LongitudePlusDistance

RETURN (SELECT @StartLongitude + SQRT(@Distance * @Distance / 
    (4784.39411916406 * 
    COS(2 * @StartLatitude / 114.591559026165) * 
    COS(2 * @StartLatitude / 114.591559026165))))

Query:

DECLARE @Longitude DECIMAL(18,8),
        @Latitude DECIMAL(18,8),
        @MinLongitude DECIMAL(18,8),
        @MaxLongitude DECIMAL(18,8),
        @MinLatitude DECIMAL(18,8),
        @MaxLatitude DECIMAL(18,8),
     @WithinMiles DECIMAL(2)

Set @Latitude = -77.856052
Set @Longitude = 40.799159
Set @WithinMiles = 50

-- Calculate the Max Lat/Long
SELECT @MaxLongitude = dbo.LongitudePlusDistance(@Longitude, @Latitude, 
           @WithinMiles),
       @MaxLatitude = dbo.LatitudePlusDistance(@Latitude, @WithinMiles)

-- Calculate the min lat/long
SELECT @MinLatitude = 2 * @Latitude - @MaxLatitude,
       @MinLongitude = 2 * @Longitude - @MaxLongitude

SELECT Top 20 *, dbo.CalculateDistance(@Longitude, @Latitude, 
    LocationLongitude, LocationLatitude) as 'Distance'
FROM   Location
WHERE  LocationLongitude Between @MinLongitude And @MaxLongitude
       And LocationLatitude Between @MinLatitude And @MaxLatitude
       And dbo.CalculateDistance(@Longitude, @Latitude, LocationLongitude, 
           LocationLatitude) <= @WithinMiles
ORDER BY dbo.CalculateDistance(@Longitude, @Latitude, LocationLongitude, 
    LocationLatitude)

Answer 1

Why are you resetting @Temp to 1 or -1 in your CalculateDistance function?

Update. Ok, never mind the above. Are you sure your latitude / longitude are correct? I've calculate the following using geocoder.us:

10715 Downsville Pike Ste 100, 21740 returns (39.607483, -77.753747)

444 E College Ave Ste 120, 16801 returns (39.607483, -77.753747)

Using your formulas (rounded to 6 digits after decimal point as that's the precision returned above) you get the following:

  sin(39.607483/57.295779) * sin(40.798594/57.295779)
+ cos(39.607483/57.295779) * cos(40.798594/57.295779)
* cos(77.753747/57.295779 - 77.856110/57.295779) = 0.99978299

3958.75586574 * arccos(0.99978299) = 82.4748331

which seems reasonable.