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.
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!
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.
It would no longer be feasible to have a table with the distance between each city?
– Felipe Avelar
@Felipeavelar It would not be possible because the values would be dynamic. The city in this case is only illustrative, I’m working with "locals", which may not be fixed.
– Lucas Lima
Implement this function in the database (stored Function) and use your SQL command normally. http://dev.mysql.com/doc/refman/5.7/en/stored-routines.html
– user4552
Another point to consider is that Mysql has an extension to work with spatial data: http://dev.mysql.com/doc/refman/5.7/en/spatial-extensions.html
– user4552