How to get distance given the coordinates using SQL?

Question

I’m doing a function in php, in which, given a GPS coordinate, it will search in the database the recorded locations, which are not necessarily fixed, within a certain distance.

My question is, how do I calculate the distance between the past coordinates and the coordinate recorded in the database?

Since I have the following function in PHP calculating the distance between two coordinates:

function calcDistancia($lat1, $long1, $lat2, $long2)
{
    $d2r = 0.017453292519943295769236;

    $dlong = ($long2 - $long1) * $d2r;
    $dlat = ($lat2 - $lat1) * $d2r;

    $temp_sin = sin($dlat/2.0);
    $temp_cos = cos($lat1 * $d2r);
    $temp_sin2 = sin($dlong/2.0);

    $a = ($temp_sin * $temp_sin) + ($temp_cos * $temp_cos) * ($temp_sin2 * $temp_sin2);
    $c = 2.0 * atan2(sqrt(a), sqrt(1.0 - a));

    return 6368.1 * $c;
}

And I can’t seem to translate into an expression like:

SELECT cidade.cid_id 
FROM cidade 
WHERE (expressão) < $distance

What they suggest to me?

---

Another alternative would be to search all locations using SQL (as traditional) and check distances using PHP. But I wonder, is there any difference in performance compared to the previous approach?

Answer 1

First this function in PHP is incorrect (maybe it has been adapted from a C++ version?). A fixed version is:

function calcDistancia($lat1, $long1, $lat2, $long2)
{
    $d2r = 0.017453292519943295769236;

    $dlong = ($long2 - $long1) * $d2r;
    $dlat = ($lat2 - $lat1) * $d2r;

    $temp_sin = sin($dlat/2.0);
    $temp_cos = cos($lat1 * $d2r);
    $temp_sin2 = sin($dlong/2.0);

    $a = ($temp_sin * $temp_sin) + ($temp_cos * $temp_cos) * ($temp_sin2 * $temp_sin2);
    $c = 2.0 * atan2(sqrt($a), sqrt(1.0 - $a));

    return 6368.1 * $c;
}

With that version, I ran the following test:

echo '1: ' . calcDistancia(-11.531371, -46.559372, -11.686069, -46.305313) . "\n";
echo '2: ' . calcDistancia(-23.524487, -47.441711, -23.549078,-46.614304) . "\n";

And the result was:

1: 32.574603346655

2: 84.362785480187

Note: in test 2, I put the distance from Sorocaba to São Paulo. The result seems very satisfactory, because by car the distance is approximately 100km, but in a straight line is really something between 80 to 90.

See the code in Codepad

---

Based on this, I made the following Mysql function:

CREATE FUNCTION DISTANCIA(
  lat1 DOUBLE, 
  long1 DOUBLE, 
  lat2 DOUBLE, 
  long2 DOUBLE) 
RETURNS DOUBLE DETERMINISTIC
BEGIN
    DECLARE d2r DOUBLE;
    DECLARE dlong DOUBLE;
    DECLARE dlat DOUBLE;
    DECLARE temp_sin DOUBLE;
    DECLARE temp_cos DOUBLE;
    DECLARE temp_sin2 DOUBLE;
    DECLARE a DOUBLE;
    DECLARE c DOUBLE;

    set d2r = 0.017453292519943295769236;

    set dlong = (long2 - long1) * d2r;
    set dlat = (lat2 - lat1) * d2r;

    set temp_sin = sin(dlat/2.0);
    set temp_cos = cos(lat1 * d2r);
    set temp_sin2 = sin(dlong/2.0);

    set a = (temp_sin * temp_sin) + (temp_cos * temp_cos) * (temp_sin2 * temp_sin2);
    set c = 2.0 * atan2(sqrt(a), sqrt(1.0 - a));

    return 6368.1 * c;

END//

I tested the function as follows:

select 
  DISTANCIA(-11.531371, -46.559372, -11.686069, -46.305313) as '1',
  DISTANCIA(-23.524487, -47.441711, -23.549078,-46.614304) as '2';

And the result was:

1                  2
32.574603346655    84.362785480187

Same as in PHP!

See the Sqlfiddle

---

Updating

Paulo Rodrigues' answer contains a calculation method inline (without function) simpler and with a result very close to that of the function.

I made a Sqlfiddle for test #2 (Sorocaba/São Paulo) as follows:

select 6371 * 
    acos(
        cos(radians(-23.524487)) * 
        cos(radians(-23.549078)) * 
        cos(radians(-47.441711) - radians(-46.614304)) + 
        sin(radians(-23.524487)) *
        sin(radians(-23.549078))
    )

I got the value 84.393323136458. It is not the same result of the function and I do not know which is more correct, but it is a much simpler form and with good approximation!

---

Considerations of Performance

The above function does the calculation well and will not be very inefficient because it does not access the base. The problem is that it requires scanning of all table rows (table scan), since no index will be used. The same occurs with the above update formula.

Auxiliary table

If the idea is to calculate the distance between two cities already known and registered in the database, you can create an auxiliary table whose PK is composed of two Fks for the city, plus a field with the distance between the two. It’s like city has a relationship N:N with itself. This table would have the calculated distance between all cities.

Obviously this will require a table with enough records. The number of cities squared, more precisely.

Limit query to coordinate ranges

This would be a hybrid approach, a bit like @Miguel Angelo’s idea.

Instead of applying the distance function to locate nearby cities by comparing all cities, first check that the absolute value of the coordinates is reasonably close.

Suppose you want to seek the coordinate -23.524487, -47.441711. Then identify the cities whose coordinates are close to 23 and 47, then apply the function to get the most accurate result.

Example:

SELECT cidade.cid_id 
  FROM cidade 
 WHERE cidade.latitude BETWEEN ($lat - $distance / 111.19) AND ($lat + $distance / 111.19)
   and cidade.longitude BETWEEN ($long - $distance / 111.19) AND ($long + $distance / 111.19)
   and distancia(cidade.latitude, cidade.longitude,  (expressão) < $distance

The first two clauses will filter latity and longitude. If you create an index for these columns, this filter will be very efficient.

Note that in the comparison of $distancia with the value in decimal degrees, I divided the distance by the magic constant 111.19 to make the conversion of the value into a "gross" approximation, that is, it does not take into account the curvature of the earth.

Then the function distancia() will improve the results, because instead of recovering a geometric "square" whose tips do not respect the distance, the results will be inside the circle whose radius is the distance.

Answer 2

In Mysql, I have something like that (not using functions), passing by -19.83996, -43.94910, for example:

6371 * acos(cos(radians(-19.83996)) * cos(radians(lat)) * cos(radians(-43.94910) - radians(lng)) + sin(radians(-19.83996)) * sin(radians(lat)))

Answer 3

I wouldn’t calculate the distance in the database. Instead, I would take all the points you’re inside of a square, which makes the query easier, and can still make it much faster because it only uses higher-than operators > and less than <, and then after returning the results to PHP, then yes, I would select those you had within a certain distance.

Example: Suppose the GPS coordinates are $x, $y, that the maximum distance is $d. Then the query would look like this:

$sql = 'SELECT * FROM tabela WHERE coordX > '.($x-$d).' and coordX < '.($x+$d).
    ' and coordY > '.($y-$d).' and coordY < '.($y+$d);

This way the query will run much faster. Ai then just use the function of calculating distances in PHP so that instead of returning everything inside the square, everything inside the circle is returned.

Answer 4

If you can create a Function in your bank, it will depend on which bank you are using, you could do the calculations for it.

In PLSQL would look like this

create or replace
FUNCTION CALCULA_DISTACIA_COORD 
(
    LATITUDE1 IN NUMBER,    
    LONGITUDE1 IN NUMBER,   
    LATITUDE2 IN NUMBER,    
    LONGITUDE2 IN NUMBER    
) RETURN NUMBER AS
    GRAU_RAD NUMBER := 0.0174532925;
    DIST_LATITUDE NUMBER;
    DIST_LONGITUDE NUMBER;
    DISTANCIA NUMBER;
    COEFF_A NUMBER;
    LATITUDE1_RAD NUMBER;   
    LONGITUDE1_RAD NUMBER;  
    LATITUDE2_RAD NUMBER;   
    LONGITUDE2_RAD NUMBER;
BEGIN
    --Conversao de graus para radiandos
    LATITUDE1_RAD   := LATITUDE1 * GRAU_RAD; 
    LONGITUDE1_RAD  := LONGITUDE1 * GRAU_RAD; 
    LATITUDE2_RAD   := LATITUDE2 * GRAU_RAD; 
    LONGITUDE2_RAD  := LONGITUDE2 * GRAU_RAD; 

    DIST_LATITUDE := LATITUDE2_RAD - LATITUDE1_RAD;
    DIST_LONGITUDE := LONGITUDE2_RAD - LONGITUDE1_RAD;
    COEFF_A := power(sin(DIST_LATITUDE/2),2)+cos(LATITUDE1_RAD)*cos(LATITUDE2_RAD)*power(sin(DIST_LONGITUDE/2),2);

    --Calculo da distancia em metros. Valor multiplicado por 6378140, que indica o raio da terra em metros
    DISTANCIA := 6378140*(2*atan2(sqrt(COEFF_A), sqrt(1 - COEFF_A)));

    RETURN DISTANCIA;
END CALCULA_DISTACIA_COORD;

Apparently it is possible create functions in the latest versions of mysql, but I never tested it.

If possible, you call Function directly in select...

Answer 5

I work with geospatial data and mysql already has geolocation support. In this case just use the function st_distance:

ST_Distance(Point(lon1, lat1), Point(lon2,lat2))

Answer 6

follows the function of the distance calculation from "utluiz" to Swift:

func calculateDistanceFromCoordinate(_latOrigem: Double, _lngOrigem: Double, _latDestino: Double, _lngDestino: Double) -> Double {
    let d2r: Double = 0.017453292519943295769236

    let dlong: Double = (_lngDestino - _lngOrigem) * d2r
    let dlat: Double = (_latDestino - _latOrigem) * d2r

    let temp_sin: Double = sin(dlat/2.0)
    let temp_cos: Double = cos(_latOrigem * d2r)
    let temp_sin2: Double = sin(dlong/2.0)

    let a: Double = (temp_sin * temp_sin) + (temp_cos * temp_cos) * (temp_sin2 * temp_sin2)
    let c: Double = 2.0 * atan2(sqrt(a), sqrt(1.0 - a))

    return 6368.1 * c;
}

//Exemplo:
let distance: Double = calculateDistanceFromCoordinate(-11.531371, _lngOrigem: -46.559372, _latDestino: -11.686069, _lngDestino: -46.305313)