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