Solved

# ms sql function error: distance between two points

Posted on 2011-10-26
Medium Priority
568 Views
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?
0
Question by:Giambattista

LVL 21

Accepted Solution

JestersGrind earned 800 total points
ID: 37032805

I imagine you could code it to look for that specific case and handle it differently like this.

Greg

``````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)

IF @latitude1 = @latitude2 AND @longitude1 = @longitude2

BEGIN

-- 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

END

ELSE

BEGIN

SET @Distance = 0

END

RETURN @Distance

END
``````
0

Author Comment

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

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

?
0

LVL 21

Expert Comment

ID: 37032951
Oops!  You're right.  The logic is backwards.

Greg

0

LVL 50

Assisted Solution

ID: 37033464
i'd be tempted to write it more like this...

``````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

Return Case when @latitude1 = @latitude2 AND @longitude1 = @longitude2
then 0.000000000000
else
ACOS(SIN((@latitude2 / 180.0000000000 * PI())) * SIN((@latitude1 / 180.0000000000 * PI()))
+ COS((@latitude2 / 180.0000000000 * PI())) * COS((@latitude1 / 180.0000000000 * PI()))
* COS((case when @longitude1 - @longitude2 > 180
then 2.0000000000
else ((longitude1-@longitude2)/180.0000000000)
end)*pi()
)
) * 6371.00000000000000
end
End
``````
0

LVL 60

Assisted Solution

Kevin Cross earned 800 total points
ID: 37034000
I am sure others above are correct, but here is a version that I have seen work in MySQL that I have converted to MS SQL. Only change really is ATAN2 is ATN2 in T-SQL. After running code in the attached, you will see that this results in 0.0000...

SELECT dbo.f_Distance(111,-33, 111, -33);

By the way, if you are using SQL 2008 or higher, take note that their are data types and tools you can take advantage of. If I am not mistaken, you can convert both sets of lat/lon to points and then use STDistance() function to get distance.

Here is an example thread with SQL MVP Rob Farley:
http://www.experts-exchange.com/Q_27299276.html
``````/*
Reproduction of MySQL function.
http://www.experts-exchange.com/Database/MySQL/Q_27288006.html
*/
CREATE 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 @dLat DECIMAL(19,16);
DECLARE @dLon DECIMAL(19,16);
DECLARE @a DECIMAL(19,16);
DECLARE @c DECIMAL(19,16);
SET @a = SIN(@dLat/2) * SIN(@dLat/2) + COS(RADIANS(@latitude1)) * COS(RADIANS(@latitude2)) * SIN(@dLon/2) * SIN(@dLon/2);
SET @c = 2 * ATN2(SQRT(@a), SQRT(1-@a));
RETURN (6371 * @c);
END
``````
0

LVL 60

Assisted Solution

Kevin Cross earned 800 total points
ID: 37034077
A very simplified example using geography data type:
``````/*
Using Geography: http://msdn.microsoft.com/en-us/library/bb895266.aspx
SQL 2008 or higher.
*/
ALTER FUNCTION [dbo].[f_Distance] (
@src_lat DECIMAL(19,16),
@src_lon DECIMAL(19,16),
@des_lat DECIMAL(19,16),
@des_lon DECIMAL(19,16)
)
RETURNS DECIMAL(19,12)
AS
BEGIN
DECLARE @srcPt Geography = [Geography]::Point(@src_lat, @src_lon, (4326));
DECLARE @desPt Geography = [Geography]::Point(@des_lat, @des_lon, (4326));

RETURN @srcPt.STDistance(@desPt);
END
``````

This returns 0.0000... still.
``````SELECT dbo.f_Distance(-25.275615, 25.7139817, -25.275615, 25.7139817);
``````

Hope that helps!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question