Cool Tips‎ > ‎Data Conversion‎ > ‎

Data Conversion - 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


ċ
DistanceBetween two LatLong Points.sql
(1k)
Andy Hughes,
Jun 23, 2012, 1:16 AM
Comments