SQL 2003 Distance Latitude Longitude

Posted by J.Hendrix on Stack Overflow See other posts from Stack Overflow or by J.Hendrix
Published on 2010-03-22T17:58:01Z Indexed on 2010/03/22 18:01 UTC
Read the original article Hit count: 488

Filed under:
|
|

I have a table full of Dealers along with their latitude and longitude. I am trying to determine the top n closest dealers to any given lat and lon. I already have the function to calculate distance between locations, but I want to do as few calculations as possible (my table can contain many thousands of entries). Currently I have to calculate the distance for each entry then sort them. Is there any way to sort before I do the calculation to improve performance?

This question is good, but I will not always know my range. Should I just pick an arbitrarily high range then refine my results? I am thankful for any help the community can offer.

declare @Lat real
declare @lon real

Set @lat = 41.05
Set @lon = -73.53 

SELECT top 10
    MemberID,
    Address1,
    City,
    State,
    Zip,
    Phone,
    Lat,
    Lon,
    (SELECT fun_DistanceLatLon] (@Lat,@lon,Lat,Lon)) as mDistance --Calculate distance
FROM
    Dealers
Order by
    (SELECT fun_DistanceLatLon] (@Lat,@lon,Lat,Lon))

© Stack Overflow or respective owner

Related posts about tsql

Related posts about geolocation