Giambattista
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oops! You're right. The logic is backwards.
Greg
Greg
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
IF @latitude1 <> @latitude2 AND @longitude1 <> @longitude2
?