Solved

# ms sql function error: distance between two points

Posted on 2011-10-26

Hi, I use this function to calculate distance in KM between two points:

FUNCTION [dbo].[f_Distance] (

@latitude1 DECIMAL(19,16),

@longitude1 DECIMAL(19,16),

@latitude2 DECIMAL(19,16),

@longitude2 DECIMAL(19,16)

)

RETURNS DECIMAL(19,12)

AS

BEGIN

DECLARE @a DECIMAL(19,16)

DECLARE @Distance DECIMAL(19,12)

-- Convert to degrees to radians

SET @latitude1 = @latitude1 / 180.0000000000 * PI()

SET @longitude1 = @longitude1 / 180.0000000000 * PI()

SET @latitude2 = @latitude2 / 180.0000000000 * PI()

SET @longitude2 = @longitude2 / 180.0000000000 * PI()

SET @a = (@longitude1 - @longitude2)

IF ( @a > PI() ) SET @a = 2 * PI()

SET @Distance = ACOS(

SIN(@latitude2) * SIN(@latitude1) + COS(@Latitude2) * COS(@Latitude1) * COS(@a)

) * 6371.00000000000000

RETURN @Distance

END

It gives me "A Domain Error has occourred" when latitude1 = latitude2 and longitude1 = longitude2

Instead to consider that the two points are the same (0 Km distance) it gives error.

Are you able to indicate me what to change in it to avoid the problem?