views:

1216

answers:

1

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)
+2  A: 

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.

ChssPly76
Good question! I had to look up where I got it from, which is here: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-zipcode-latitude-longitude-pr It was explained as accommodating a rounding error if the result was over 1 or less than -1.
Mikecancook
I've wondered about the precision required to do this accurately. I believe I read somewhere that you should only use 6 decimal points but the explanation didn't make sense and now I can't find where I read it. I've been using Google to do my geocoding. The results seem a little inconsistent. I'm just not sure how inconsistent is significant.
Mikecancook
Ok, so after some more searching I found this site: http://www.daftlogic.com/projects-google-maps-distance-calculator.htm - which uses the Google API for calculating distance and it comes up with ....drum roll.... 82.530! So, it seems I need to figure out how to get more accurate geo codes.
Mikecancook
@Temp is used in the ACOS function. ACOS will return a domain error for any value less than -1 or greater than 1. Slight rounding errors can cause these values (and the error).
G Mastros