Geeks With Blogs

I'm working on a mobile location search app, and needed a way to find addresses within a certain radius of the device's current position. A little Googling found a simple function that will return the distance in miles:

```CREATE FUNCTION dbo.Distance( @lat1 float , @long1 float , @lat2 float , @long2 float)      RETURNS float            AS   BEGIN          DECLARE @DegToRad float = 57.29577951    DECLARE @Ans float = 0    DECLARE @Miles float = 0            SET @Ans = SIN(@lat1 / @DegToRad) * SIN(@lat2 / @DegToRad) + COS(@lat1 / @DegToRad ) * COS( @lat2 / @DegToRad ) * COS(ABS(@long2 - @long1 )/@DegToRad)               SET @Miles = 3959 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans)               SET @Miles = ROUND(@Miles,1)             RETURN (@Miles)            END```

Test it out with this statement:

`select dbo.Distance ( 43.005895, -71.013202, 40.922326, -72.637078)`

To use it, you'd create a SQL statement something like:

```-- Assuming you have an address table with Latitude and Longitude columns, -- and you want anything within 25 miles of your current location DECLARE @CurrentLatitude float = 43.005895 DECLARE @CurrentLongitude float = -71.013202   SELECT <all my fields> FROM <my table> WHERE dbo.Distance(Latitude, Longitude, @CurrentLatitude, @CurrentLongitude) <= 25 ORDER BY dbo.Distance(Latitude, Longitude, @CurrentLatitude, @CurrentLongitude) desc```

Technorati Tags: SQL

Related Posts on Geeks With Blogs Matching Categories

Comments on this post: Calculate distance between two latitude/longitude points in SQL

# re: Calculate distance between two latitude/longitude points in SQL Mongo rocks for these kinds of queries

http://www.mongodb.org/display/DOCS/Geospatial+Indexing
Left by Ryan on Sep 08, 2011 9:29 PM

# re: Calculate distance between two latitude/longitude points in SQL Hi,
I am testing this with an Australia address and it does not seem to work (It works with a US address). For example - enter this Lat and Long: http://maps.google.com/maps?f=q&hl=en&geocode=&q=-33.79852,151.28612&ie=

and the query above returns records like this: 38.70716 -9.13552
Left by Rodney Joyce on Sep 04, 2012 5:48 AM

# re: Calculate distance between two latitude/longitude points in SQL Right, I see, the distance is negative, so you need to add this clause:
WHERE dbo.Distance(Latitude, Longitude, @CurrentLatitude, @CurrentLongitude) <= 25 AND dbo.Distance(Latitude, Longitude, @CurrentLatitude, @CurrentLongitude) > 0
Left by Rodney Joyce on Sep 04, 2012 5:50 AM