Category Archives: tsql

Great Circle Distance in TSQL

CREATE FUNCTION GreatCircleDistance
(
@LatitudeA float
, @LongitudeA float
, @LatitudeB float
, @LongitudeB float
)
Returns float /* meters */
BEGIN
/*
Great Circle Distance
cf: http://en.wikipedia.org/wiki/Great-circle_distance#Formulas
*/

declare @LatB float
declare @LatA float
declare @deltaLng float

set @LatB = RADIANS(@LatitudeB)
set @LatA = RADIANS(@LatitudeA)
set @deltaLng = RADIANS(@LongitudeA) – RADIANS(@LongitudeB)

RETURN( atan
(
(
sqrt(
power(cos(@LatB)*sin(@deltaLng),2)
+
power(cos(@LatA)*sin(@LatB) – sin(@LatA)*cos(@LatB)*cos(@deltaLng),2))
)
/
(
sin(@LatA)*sin(@LatB) + (cos(@LatA)*cos(@LatB)*cos(@deltaLng))
)
)* 6378137 –WGS-84 ellipsoid reference
)

END