?
Solved

ms sql function error: distance between two points

Posted on 2011-10-26
6
Medium Priority
?
568 Views
Last Modified: 2012-06-27
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
Comment
Question by:Giambattista
6 Comments
 
LVL 21

Accepted Solution

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

Open in new window

0
 

Author Comment

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


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

?
0
 
LVL 21

Expert Comment

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

Greg

0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 50

Assisted Solution

by:Lowfatspread
Lowfatspread earned 400 total points
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

Open in new window

0
 
LVL 60

Assisted Solution

by:Kevin Cross
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 @dLat = RADIANS(@latitude2-@latitude1);
  SET @dLon = RADIANS(@longitude2-@longitude1);
  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

Open in new window

0
 
LVL 60

Assisted Solution

by:Kevin Cross
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

Open in new window


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

Open in new window


Hope that helps!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Ready to get certified? Check out some courses that help you prepare for third-party exams.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question