Get Distance Between 2 Lat/Long Coordinates

Applicability:

SQL Server 2000: Tested

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: Unknown

Date: 9 Apr 2009

Description

Calculates the straight-line distance, in Km, between two lat/long corodinates.

Code

CREATE FUNCTION [dbo].[DistanceBetween]

(

@Lat1 AS REAL,

@Long1 AS REAL,

@Lat2 AS REAL,

@Long2 AS REAL

)

RETURNS REAL

AS

-- returns distance as the crow flies (in km) between two lat/long coordinates

BEGIN

DECLARE

@dLat1InRad AS FLOAT(53),

@dLong1InRad AS FLOAT(53),

@dLat2InRad AS FLOAT(53),

@dLong2InRad AS FLOAT(53),

@dLongitude AS FLOAT(53),

@dLatitude AS FLOAT(53),

@a AS FLOAT(53),

@c AS REAL,

@kEarthRadius AS REAL,

@dDistance AS REAL

SET @dLat1InRad = @Lat1 * (PI()/180.0)

SET @dLong1InRad = @Long1 * (PI()/180.0)

SET @dLat2InRad = @Lat2 * (PI()/180.0)

SET @dLong2InRad = @Long2 * (PI()/180.0)

SET @dLongitude = @dLong2InRad - @dLong1InRad

SET @dLatitude = @dLat2InRad - @dLat1InRad

/* Intermediate result a. */

SET @a = SQUARE (SIN (@dLatitude / 2.0)) + COS (@dLat1InRad) * COS (@dLat2InRad) * SQUARE(SIN (@dLongitude / 2.0))

/* Intermediate result c (great circle distance in Radians). */

SET @c = 2.0 * ATN2 (SQRT (@a), SQRT (1.0 - @a))

/* SET kEarthRadius = 3956.0 miles */

SET @kEarthRadius = 6376.5

/* kms */

SET @dDistance = @kEarthRadius * @c

RETURN (@dDistance)

END