Geeks With Blogs
Bill Osuch - Random geek notes

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

Posted on Thursday, September 8, 2011 8:44 AM SQL | Back to top


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

# re: Calculate distance between two latitude/longitude points in SQL
Requesting Gravatar...
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
Requesting Gravatar...
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
Requesting Gravatar...
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

Your comment:
 (will show your gravatar)


Copyright © Bill Osuch | Powered by: GeeksWithBlogs.net