Check Out Dave's New game for iPhone and iPod Touch: Smiled Out!

Distance Between Two Latitude and Longitude Points in SQL Server

I had to create a process to search locations within a given radius recently. I found this page which describes the Haversine formula to calculate distances. I implemented this as a SQL Server Function to calculate the distance.

The formula to calculate distance with latitude/longitude is actually pretty complicated, but this function implements it:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

CREATE FUNCTION [dbo].[DistanceBetweenCoordinatesMiles](
@Latitude1 float,
@Longitude1 float,
@Latitude2 float,
@Longitude2 float
)
RETURNS float
AS
BEGIN
 
DECLARE @radiusofearthmiles float
SET @radiusofearthmiles = 3956.6
 
DECLARE @PIRadians  float
SET @PIRadians = PI() / 180;
 
declare @a float
declare @c float
declare @dlon float
declare @dlat float
 
SET @dlat = (@latitude2 - @latitude1) * @PIRadians;
SET @dlon = (@longitude2 - @longitude1) * @PIRadians;
 
SET @a = sin(@dlat / 2) * sin(@dlat / 2) +
cos(@latitude1 * @PIRadians) * COS(@latitude2 * @PIRadians) *
sin(@dlon / 2) * sin(@dlon / 2)
SET @c = 2 * atn2(sqrt(@a), sqrt(1-@a))
 
RETURN @radiusofearthmiles * @c
 
END
Follow Dave on Twitter

This entry was posted on Friday, August 28th, 2009 at 12:54 pm and is filed under SQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply