36

# KB: Calculate distance between two points on globe from latitude and longitude coordinates

 WEDNESDAY, MARCH 17, 2010

Calculation of geographical distance is a very important utility function in GPS related procedures. The Haversine formula is used commonly for this. The haversine formula is an equation important in navigation, giving great-circle distances between two points on a sphere from their longitudes and latitudes. This actually gives the shortest distance between two points, assuming the globe as a clean globoid sphere with no obstacles between the points.

For a large scaled calculation, it would be sufficient to calculate distance between two points alone, but for smaller scales, to get a proper result, the path between the two points should be broken into various mid-points considering all obstacles and roads, etc., and to make a summation of distance between one mid-point to the next one.

As calculation of distance is mostly a data oriented job, it would be better to have the distance calculation procedure written as a T-SQL function. This function would required four inputs totally - latitude and longitude of both points. An important parameter in the distance calculation is the radius of earth at a given point. This cannot be seen as a constant because earth is not a proper sphere, but a globoid with radius 6357 to 6378 kilometers from poles to the equatorial belt.

The Haversine formula for calculation of distance is as follows:

 Haversine formula: R = earth’s radius (mean radius = 6,371km) Δlat = lat2− lat1 Δlong = long2− long1 a = sin²(Δlat/2) + cos(lat1).cos(lat2).sin²(Δlong/2) c = 2.atan2(√a, √(1−a)) d = R.c

The T-SQL implementation of the code would be as follows: (This includes a formula to calculate the radius of earth, unlike the above contant value)

ALTER FUNCTION [dbo].[geo_Distance]
(
@Lat FLOAT,
@Long FLOAT,
@LLat FLOAT,
@LLong FLOAT
)
RETURNS FLOAT
AS
BEGIN
DECLARE @dLat FLOAT, @dLong FLOAT, @A FLOAT, @C FLOAT
DECLARE @NR FLOAT, @DR FLOAT

SET @RADIUSE = 6378135
SET @RADIUSP = 6356750

SET @dLat =(@Lat - @LLat) / 180 * PI()
SET @dLong =(@Long - @LLong) /180 * PI()
SET @A =(SIN(@dLat / 2) * SIN(@dLat / 2))
+( COS(@Lat / 180 * PI()) * COS(@LLat / 180 * PI())
* SIN(@dLong / 2) * SIN(@dLong / 2))
SET @C = 2 * ATN2(SQRT(@A), SQRT(1-@A))

/*
Calculate radius of earth
*/
SET @NR = POWER(@RADIUSE * @RADIUSE * COS(@Lat/180*PI()), 2)
+ POWER( @RADIUSP * @RADIUSP * SIN(@Lat/180*PI()), 2)
SET @DR = POWER(@RADIUSE * COS(@Lat/180*PI()), 2)
+ POWER( @RADIUSP * SIN(@Lat/180*PI()), 2)
SET @RADIUS = SQRT(@NR/@DR)