How to get distance given the coordinates using SQL?

First, this function in PHP is incorrect (maybe it was adapted from a C ++ version?). A corrected 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 this release, I did 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 quite satisfactory, because by car the distance is approximately 100km, but a straight line is really somewhere between 80 and 90.

View Code in Codepad

Based on this, I did 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

The same as PHP!

See Sqlfiddle

Update

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

I did 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 get the value 84.393323136458 . It is not the same result of the function and I do not know which one is more correct, but it is a much simpler form and with good approximation!

Performance Considerations

The above function does the calculation well and will not be very inefficient because it does not access the database. The problem is that it requires scanning all table rows ( table scan ), since no index will be used. The same thing happens 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 as if the city has a N:N relationship 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 the query to coordinate ranges

This would be a hybrid approach, somewhat similar to the idea of @Miguel Angelo.

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

Suppose you want to find the -23.524487, -47.441711 coordinate. Then filter 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 the breadth and length. 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 convert the value to a "raw" approximation, that is, in account of the curvature of the earth.

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

In MySQL , I have something like this (not using functions), passing -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)))

I would not calculate the distance in the database. Instead, I would take all the points inside a square, which makes the query easier, and you can still do it much faster because you only use operators of greater than > and less than < , and so after returning the results to PHP, then yes, it would select those you had within a certain distance.

Example: Suppose GPS coordinates are $x , $y , that the maximum distance is $d . So 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. 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.

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 it 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 you can create functions in the latest versions of mysql , but I've never tested it.

If it is possible, you call function directly in the select ...

follows the function of the "utluiz" distance calculation for 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)

Working with geospatial data and mysql already supports geolocation. In this case, just use the function st_distance :

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