# Latitude/Longitude Distance Calculation in SQL Server

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.

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

@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

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?

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

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

I have distance between latitude and longitude

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

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

How to use this values into the qeury

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.