Latitude/Longitude Distance Calculation in SQL Server

July 11, 2005 | Uncategorized | 8 Comments

Originally, my Google Maps church search page was finding the nearest churches by
making an approximate square around the zip code's latitude and
longitude. For large distances (over ~25 miles), this proved pretty
inaccurate. To fix this, I created a SQL Function so that I could run a
query like this:

SELECT * 
FROM 
dts_Alumni_Churches
WHERE 
dbo.CoordinateDistanceMiles(Latitude, Longitude, @ZipLatitude, @ZipLongitude) < @Radius

Here's the SQL function:

CREATE FUNCTION CoordinateDistanceMiles(
@Latitude1 float,
@Longitude1 float,
@Latitude2 float,
@Longitude2 float
)
RETURNS float 
AS  
BEGIN 
-- CONSTANTS
DECLARE @EarthRadiusInMiles float;
SET @EarthRadiusInMiles = 3963.1
DECLARE @PI  float;
SET @PI = PI();
-- RADIANS conversion
DECLARE @lat1Radians float;
DECLARE @long1Radians float;
DECLARE @lat2Radians float;
DECLARE @long2Radians float;
SET @lat1Radians = @Latitude1 * @PI / 180;
SET @long1Radians = @Longitude1 * @PI / 180;
SET @lat2Radians = @Latitude2 * @PI / 180;
SET @long2Radians = @Longitude2 * @PI / 180;
RETURN Acos(
Cos(@lat1Radians) * Cos(@long1Radians) * Cos(@lat2Radians) * Cos(@long2Radians) + 
Cos(@lat1Radians) * Sin(@long1Radians) * Cos(@lat2Radians) * Sin(@long2Radians) + 
Sin(@lat1Radians) * Sin(@lat2Radians)
) * @EarthRadiusInMiles;
END

If anyone has a faster method for use in SQL Server, I'd love to see it.

8 Responses to “Latitude/Longitude Distance Calculation in SQL Server”

  1. Billy Leo says:

    Hello there,
    Thank you very much for this clear code!
    It has been very helpful to me.
    I seem to have a bit of a problem tho, The function seems to be 2 to 3 miles off to the north in it’s calculations. Did this happen for you?
    Anyway, Great Site Bud!
    Thanks
    Billy

  2. John Dyer says:

    @Billy, I did this 2-3 years ago, so I might very well have had a slight error! I saw another post recently that does something similar and might be more accurate: http://blog.troyd.net/PermaLink,guid,847b0f1f-498c-43d4-80de-d29902fbd2eb.aspx

  3. rap says:

    hi, actually i don’t really understand about this kind of thing.
    but there’s some query here :http://www.viawindowslive.com/Articles/VirtualEarth/Freereversegeocoding.aspx

    it’s different from you, but i think the query is also try to calculate the same thing with you.
    can you tell me, which one better?

  4. Alamak says:

    You can get the sample codes in several programming languages (other than the SQL) from the following URL.

    http://www.zipcodeworld.com/developers.htm

  5. SunilGrewal says:

    I have distance between latitude and longitude

  6. sahilsameer says:

    I have latitudes and longitude values in 17248727N and 078244516E.how to convert into float

  7. sahilsameer says:

    I have latitudes and longitude values in 17248727N and 078244516E.how to convert into float.
    How to use this values into the qeury

  8. George Mastros says:

    I recently blogged about this very thing.

    http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-zipcode-latitude-longitude-pr

    The performance of the distance calculation is not the limiting factor. The problem is that you are calculating ALL of the distance so that you can filter out those further than your distance. In my blog, I demonstrate a technique where you first create a bounding box (min/max lat/long). This will quickly eliminate locations that are outside a rectangle. Then, the distance formula is applied to the remaining locations to filter those locations outside the circle.

    Think about it this way… If you want locations within 10 miles of Miami Florida, do you really need to calculate the distance from Miami to New York or California? The bounding box technique eliminates all the locations that cannot possibly be returned, but does it quickly to avoid the costly/slow calculation.

Leave a Reply

Hi, I'm John Dyer. In my day job, I build websites and create online seminary software for a seminary in Dallas. I also like to release open source tools including a pretty popular HTML5 video player and build tools that help people find best bible commentaries and do bible study. And just for fun, I also wrote a book on the theology of technology and media.

Fork me on GitHub

Social Widgets powered by AB-WebLog.com.