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
SELECT @rlat1 = RADIANS(@lat1), @rlon1 = RADIANS(@lon1), @rlat2 = RADIANS(@lat2), @rlon2 = RADIANS(@lon2)
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 dLat1_rad, dLon1_rad, dLat2_rad, dLon2_rad As Double
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
dLat1_rad = dLat1.Value / DegToRad
dLon1_rad = dLon1.Value / DegToRad
dLat2_rad = dLat2.Value / DegToRad
dLon2_rad = dLon2.Value / DegToRad
a = Sin((dLat2_rad - dLat1_rad) / 2.0)
b = Sin((dLon2_rad - dLon1_rad) / 2.0)
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 
