Solved

# ms sql function error: distance between two points

Posted on 2011-10-26
563 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

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

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

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

?
0

LVL 21

Expert Comment

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

Greg

0

LVL 50

Assisted Solution

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 59

Assisted Solution

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 59

Assisted Solution

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

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed