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 @Ans float = 0
DECLARE @Miles float = 0

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