Calculate distance between two latitude/longitude points in SQL

Comments (3) | Share

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