CODEWITHSUNDEEP
sqldatabaselocationdistancelatitude-longitude
Chintan Pandya
Chintan Pandya

Reputation: 13

Calculating distance between 2 points fails when points are the same

I am running following SQL query in my JAVA Spring server. This query works perfect for almost all coordinates except for one specific pair c = <23.065079, 72.511478> (= to_lat, to_long):

SELECT * 
FROM   karpool.ride 
WHERE  Acos(Sin(Radians(23.065079)) * Sin(Radians(to_lat)) + 
            Cos(Radians(23.065079)) * Cos(Radians(to_lat)) * 
            Cos(Radians(to_lon) - Radians(72.511478))) * 6371 <= 10;

My database has many locations within 10 km distance to c. With the above query, I get all those locations' distances, except for the one which exactly matches with c. The distance returned should be 0 in that case, but the query fails.

Is this an SQL issue or is there something wrong with the formula?

Upvotes: 0

Views: 814

Answers (1)

trincot
trincot

Reputation: 351318

This is most probably due to floating point accuracy problems.

First of all, the used formula is the Great circle distance formula:

Let φ1,λ1 and φ1,λ2 be the geographical latitude and longitude of two points 1 and 2, and Δφ,Δλ their absolute differences; then Δσ, the central angle between them, is given by the spherical law of cosines:

Δσ = arccos （ sin φ1 ∙ sin φ2 + cos φ1 ∙ cos φ2 ∙ cos (Δλ) ）.

The distance d, i.e. the arc length, for a sphere of radius r and Δσ given in radians

d = r Δσ.

Now if the two points are the same, then Δλ = 0, and thus cos(Δλ) = cos(0) = 1, and the first formula reduces to:

Δσ = arccos (sin φ ∙ sin φ + cos φ ∙ cos φ).

The argument to arccos has become the Pythagorean trigonometric identity, and thus equals 1.

So the above reduces to:

Δσ = arccos (1).

The problem

The domain of the arccosine is: −1 ≤ x ≤ 1, so with the value 1 we are at the boundary of the domain.

As the value of 1 was the result of several floating point operations (sines, cosines, multiplications), it could occur that the value is not exactly 1, but something like 1.0000000000004. That poses a problem, for that value is out of range for calculating the arccosine. Database engines respond differently to this situation:

SQL Server will raise an exception:

An invalid floating point operation occurred.

MySql will just evaluate the expression as null.

The solution

Somehow the argument passed to the arccosine should be made to stay in the range −1 ≤ x ≤ 1. One way of doing this, is to round the argument to a number of decimals that is large enough to keep some precision, but small enough to round away any excess outside this range caused by floating point operations.

Most database engines have a round function to which a second argument can be provided to specify the number of digits to keep, and so the SQL would look like this (keeping 6 decimals):

SELECT * 
FROM   karpool.ride 
WHERE  Acos(Round(
          Sin(Radians(23.065079)) * Sin(Radians(to_lat)) + 
          Cos(Radians(23.065079)) * Cos(Radians(to_lat)) *
          Cos(Radians(to_lon) - Radians(72.511478)),
          6
       )) * 6371 <= 10;

Alternatively, you could use the functions greatest and least, which some database engines provide, to turn any excess value to 1 (or -1):

SELECT * 
FROM   karpool.ride 
WHERE  Acos(Greatest(Least(
          Sin(Radians(23.065079)) * Sin(Radians(to_lat)) + 
          Cos(Radians(23.065079)) * Cos(Radians(to_lat)) *
          Cos(Radians(to_lon) - Radians(72.511478)),
          1), -1)
       ) * 6371 <= 10;

Note that SQL Server does not provide greatest/least functions. A question to overcome this has several answers.

Upvotes: 1

Related Questions