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

Rate

4 (1)

Share

Share

Rate

4 (1)