 # Calculate distance between two points

,

The calculation of distance between two points using standard spherical geometry can be inaccurate for short distances (ten miles or less) because the sine function of very small angles approaches zero. The haversine approach (http://en.wikipedia.org/wiki/Haversine_formula) turns this around, so it is very accurate at small distances but has larger errors (about ten miles) for points on opposite sides of the earth.

I thought this function was a great candidate for conversion to CLR, but it was actually a little slower on my test system. My theory is that the cost of marshalling the arguments outweighs any benefit of running compiled code for this function.

```CREATE FUNCTION [dbo].[fn_latlondistance] (@lat1 float, @lon1 float, @lat2 float, @lon2 float)
RETURNS float  AS
BEGIN
DECLARE @rlat1 float, @rlon1 float, @rlat2 float, @rlon2 float
DECLARE @a float, @c float, @d float
SET @c = SQUARE(SIN((@rlat2 - @rlat1) / 2.0)) + COS(@rlat1) * COS(@rlat2) * SQUARE(SIN((@rlon2 - @rlon1) / 2.0))
SET @a = 2.0 * ATN2(SQRT(@c), SQRT(1.0 - @c))
SET @d = 3956.088331329 * @a
RETURN @d
END

--------------------------------------------------------
CLR Version

Option Explicit On
Option Strict On

Imports System
Imports System.Data
Imports Microsoft.SqlServer.Server
Imports System.Data.SqlTypes
Imports System.Math

Public Class Geocoding
<Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.None, IsDeterministic:=True, IsPrecise:=False, SystemDataAccess:=SystemDataAccessKind.None)> _
Public Shared Function fn_LatLonDistance( _
ByVal dLat1 As SqlDouble, ByVal dLon1 As SqlDouble, _
ByVal dLat2 As SqlDouble, ByVal dLon2 As SqlDouble) As SqlDouble
Dim a, b, c As Double
Const DegToRad As Double = 180.0 / PI
If dLat1.IsNull OrElse dLon1.IsNull OrElse dLat2.IsNull OrElse dLon2.IsNull Then
Return SqlDouble.Null
End If
c = a * a + Cos(dLat1_rad) * Cos(dLat2_rad) * b * b
Return 3956.088331329 * 2.0 * Atan2(Sqrt(c), Sqrt(1.0 - c))
End Function
End Class```

4 (1)

4 (1)