Link to home
Start Free TrialLog in
Avatar of Giambattista
GiambattistaFlag for Italy

asked on

ms sql function error: distance between two points

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?
ASKER CERTIFIED SOLUTION
Avatar of JestersGrind
JestersGrind
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Giambattista

ASKER

Uhm ... :-) ... yes, in line 14 you mean


IF @latitude1 <> @latitude2 AND @longitude1 <> @longitude2

?
Oops!  You're right.  The logic is backwards.

Greg

SOLUTION
Avatar of Lowfatspread
Lowfatspread
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial