Latitude/Longitude Distance Calculation in SQL Server

By BylineJohn Dyer on

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.

9 thoughts on “Latitude/Longitude Distance Calculation in SQL Server”

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

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

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

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

Comments are closed.