Distancia del Gran Círculo de MySQL (fórmula de Haversine)
Tengo un script PHP de trabajo que obtiene valores de Longitud y Latitud y luego los ingresa en una consulta MySQL. Me gustaría hacerlo únicamente MySQL. Aquí está mi código PHP actual:
if ($distance != "Any" && $customer_zip != "") { //get the great circle distance
//get the origin zip code info
$zip_sql = "SELECT * FROM zip_code WHERE zip_code = '$customer_zip'";
$result = mysql_query($zip_sql);
$row = mysql_fetch_array($result);
$origin_lat = $row['lat'];
$origin_lon = $row['lon'];
//get the range
$lat_range = $distance/69.172;
$lon_range = abs($distance/(cos($details[0]) * 69.172));
$min_lat = number_format($origin_lat - $lat_range, "4", ".", "");
$max_lat = number_format($origin_lat + $lat_range, "4", ".", "");
$min_lon = number_format($origin_lon - $lon_range, "4", ".", "");
$max_lon = number_format($origin_lon + $lon_range, "4", ".", "");
$sql .= "lat BETWEEN '$min_lat' AND '$max_lat' AND lon BETWEEN '$min_lon' AND '$max_lon' AND ";
}
¿Alguien sabe cómo hacer esto enteramente MySQL? He navegado un poco por Internet, pero la mayor parte de la literatura es bastante confusa.
8 answers
Desde Google Code FAQ-Creación de un Localizador de tiendas con PHP, MySQL y Google Maps :
Aquí está la instrucción SQL que encontrará las 20 ubicaciones más cercanas que están dentro de un radio de 25 millas a la coordenada 37, -122. Calcula la distancia en función de la latitud/longitud de esa fila y la latitud/longitud objetivo, y luego pide solo filas donde el valor de distancia es inferior a 25, ordena toda la consulta por distancia y la limita a 20 resultados. Para buscar por kilómetros en lugar de millas, reemplace 3959 con 6371.
SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) )
* cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin(radians(lat)) ) ) AS distance
FROM markers
HAVING distance < 25
ORDER BY distance
LIMIT 0 , 20;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-03-03 19:38:48
$greatCircleDistance = acos( cos($latitude0) * cos($latitude1) * cos($longitude0 - $longitude1) + sin($latitude0) * sin($latitude1));
Con latitud y longitud en radián.
So
SELECT
acos(
cos(radians( $latitude0 ))
* cos(radians( $latitude1 ))
* cos(radians( $longitude0 ) - radians( $longitude1 ))
+ sin(radians( $latitude0 ))
* sin(radians( $latitude1 ))
) AS greatCircleDistance
FROM yourTable;
Es su consulta SQL
Para obtener sus resultados en Km o millas, multiplique el resultado con el radio medio de la Tierra (3959
millas,6371
Km o 3440
millas náuticas)
Lo que estás calculando en tu ejemplo es un cuadro delimitador. Si coloca sus datos de coordenadas en una columna MySQL con capacidad espacial, puede usar la funcionalidad de compilación de MySQL para consultar los datos.
SELECT
id
FROM spatialEnabledTable
WHERE
MBRWithin(ogc_point, GeomFromText('Polygon((0 0,0 3,3 3,3 0,0 0))'))
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-06-08 11:25:25
Si agrega campos auxiliares a la tabla de coordenadas, puede mejorar el tiempo de respuesta de la consulta.
Así:
CREATE TABLE `Coordinates` (
`id` INT(10) UNSIGNED NOT NULL COMMENT 'id for the object',
`type` TINYINT(4) UNSIGNED NOT NULL DEFAULT '0' COMMENT 'type',
`sin_lat` FLOAT NOT NULL COMMENT 'sin(lat) in radians',
`cos_cos` FLOAT NOT NULL COMMENT 'cos(lat)*cos(lon) in radians',
`cos_sin` FLOAT NOT NULL COMMENT 'cos(lat)*sin(lon) in radians',
`lat` FLOAT NOT NULL COMMENT 'latitude in degrees',
`lon` FLOAT NOT NULL COMMENT 'longitude in degrees',
INDEX `lat_lon_idx` (`lat`, `lon`)
)
Si está utilizando TokuDB, obtendrá un rendimiento aún mejor si agrega clustering los índices en cualquiera de los predicados, por ejemplo, como este:
alter table Coordinates add clustering index c_lat(lat);
alter table Coordinates add clustering index c_lon(lon);
Tendrás la básica lat y lon en grados así como el pecado(lat) en radianes, cos(lat)*cos(lon) en radianes y cos(lat)*sin(lon) en radianes para cada punto. A continuación, se crea una función mysql, smth así:
CREATE FUNCTION `geodistance`(`sin_lat1` FLOAT,
`cos_cos1` FLOAT, `cos_sin1` FLOAT,
`sin_lat2` FLOAT,
`cos_cos2` FLOAT, `cos_sin2` FLOAT)
RETURNS float
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY INVOKER
BEGIN
RETURN acos(sin_lat1*sin_lat2 + cos_cos1*cos_cos2 + cos_sin1*cos_sin2);
END
Esto te da la distancia.
No olvide agregar un índice en lat/lon para que el cuadro de límites pueda ayudar a la búsqueda en lugar de ralentizarla (el índice ya se agregó en la consulta CREAR TABLA anterior).
INDEX `lat_lon_idx` (`lat`, `lon`)
Dada una tabla antigua con solo coordenadas lat / lon, puede configurar un script para actualizarla de la siguiente manera: (php usando meekrodb)
$users = DB::query('SELECT id,lat,lon FROM Old_Coordinates');
foreach ($users as $user)
{
$lat_rad = deg2rad($user['lat']);
$lon_rad = deg2rad($user['lon']);
DB::replace('Coordinates', array(
'object_id' => $user['id'],
'object_type' => 0,
'sin_lat' => sin($lat_rad),
'cos_cos' => cos($lat_rad)*cos($lon_rad),
'cos_sin' => cos($lat_rad)*sin($lon_rad),
'lat' => $user['lat'],
'lon' => $user['lon']
));
}
Luego optimiza la consulta real para hacer solo el cálculo de distancia cuando realmente se necesita, por ejemplo delimitando el círculo (bien, ovalado) desde dentro y desde fuera. Para eso, necesitarás precalcular varias métricas para la consulta en sí:
// assuming the search center coordinates are $lat and $lon in degrees
// and radius in km is given in $distance
$lat_rad = deg2rad($lat);
$lon_rad = deg2rad($lon);
$R = 6371; // earth's radius, km
$distance_rad = $distance/$R;
$distance_rad_plus = $distance_rad * 1.06; // ovality error for outer bounding box
$dist_deg_lat = rad2deg($distance_rad_plus); //outer bounding box
$dist_deg_lon = rad2deg($distance_rad_plus/cos(deg2rad($lat)));
$dist_deg_lat_small = rad2deg($distance_rad/sqrt(2)); //inner bounding box
$dist_deg_lon_small = rad2deg($distance_rad/cos(deg2rad($lat))/sqrt(2));
Dadas esas preparaciones, la consulta es algo como esto (php):
$neighbors = DB::query("SELECT id, type, lat, lon,
geodistance(sin_lat,cos_cos,cos_sin,%d,%d,%d) as distance
FROM Coordinates WHERE
lat BETWEEN %d AND %d AND lon BETWEEN %d AND %d
HAVING (lat BETWEEN %d AND %d AND lon BETWEEN %d AND %d) OR distance <= %d",
// center radian values: sin_lat, cos_cos, cos_sin
sin($lat_rad),cos($lat_rad)*cos($lon_rad),cos($lat_rad)*sin($lon_rad),
// min_lat, max_lat, min_lon, max_lon for the outside box
$lat-$dist_deg_lat,$lat+$dist_deg_lat,
$lon-$dist_deg_lon,$lon+$dist_deg_lon,
// min_lat, max_lat, min_lon, max_lon for the inside box
$lat-$dist_deg_lat_small,$lat+$dist_deg_lat_small,
$lon-$dist_deg_lon_small,$lon+$dist_deg_lon_small,
// distance in radians
$distance_rad);
EXPLAIN on the above query might say that it's not using index unless there's enough results to trigger such. El índice se utilizará cuando haya suficientes datos en la tabla de coordenadas. Puede agregar ÍNDICE DE FUERZA (lat_lon_idx) a la SELECCIÓN para hacer uso de el índice no tiene en cuenta el tamaño de la tabla, por lo que puede verificar con EXPLICAR que está funcionando correctamente.
Con los ejemplos de código anteriores, debería tener una implementación funcional y escalable de búsqueda de objetos por distancia con un error mínimo.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-10-28 22:11:19
He tenido que resolver esto con algún detalle, así que compartiré mi resultado. Utiliza una tabla zip
con tablas latitude
y longitude
. No depende de Google Maps, sino que puedes adaptarlo a cualquier tabla que contenga lat / long.
SELECT zip, primary_city,
latitude, longitude, distance_in_mi
FROM (
SELECT zip, primary_city, latitude, longitude,r,
(3963.17 * ACOS(COS(RADIANS(latpoint))
* COS(RADIANS(latitude))
* COS(RADIANS(longpoint) - RADIANS(longitude))
+ SIN(RADIANS(latpoint))
* SIN(RADIANS(latitude)))) AS distance_in_mi
FROM zip
JOIN (
SELECT 42.81 AS latpoint, -70.81 AS longpoint, 50.0 AS r
) AS p
WHERE latitude
BETWEEN latpoint - (r / 69)
AND latpoint + (r / 69)
AND longitude
BETWEEN longpoint - (r / (69 * COS(RADIANS(latpoint))))
AND longpoint + (r / (69 * COS(RADIANS(latpoint))))
) d
WHERE distance_in_mi <= r
ORDER BY distance_in_mi
LIMIT 30
Mira esta línea en el medio de esa consulta:
SELECT 42.81 AS latpoint, -70.81 AS longpoint, 50.0 AS r
Esto busca las 30 entradas más cercanas en la tabla zip
dentro de 50.0 millas del punto lat/long 42.81/-70.81 . Cuando construyes esto en una aplicación, ahí es donde pones tu propio punto y radio de búsqueda.
Si desea trabajar en kilómetros en lugar de millas, cambie 69
a 111.045
y cambie 3963.17
a 6378.10
en la consulta.
Aquí está una valoración crítica detallada. Espero que ayude a alguien. http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc /
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-07-25 12:19:02
He escrito un procedimiento que puede calcular el mismo, pero tienes que introducir la latitud y longitud en la tabla respectiva.
drop procedure if exists select_lattitude_longitude;
delimiter //
create procedure select_lattitude_longitude(In CityName1 varchar(20) , In CityName2 varchar(20))
begin
declare origin_lat float(10,2);
declare origin_long float(10,2);
declare dest_lat float(10,2);
declare dest_long float(10,2);
if CityName1 Not In (select Name from City_lat_lon) OR CityName2 Not In (select Name from City_lat_lon) then
select 'The Name Not Exist or Not Valid Please Check the Names given by you' as Message;
else
select lattitude into origin_lat from City_lat_lon where Name=CityName1;
select longitude into origin_long from City_lat_lon where Name=CityName1;
select lattitude into dest_lat from City_lat_lon where Name=CityName2;
select longitude into dest_long from City_lat_lon where Name=CityName2;
select origin_lat as CityName1_lattitude,
origin_long as CityName1_longitude,
dest_lat as CityName2_lattitude,
dest_long as CityName2_longitude;
SELECT 3956 * 2 * ASIN(SQRT( POWER(SIN((origin_lat - dest_lat) * pi()/180 / 2), 2) + COS(origin_lat * pi()/180) * COS(dest_lat * pi()/180) * POWER(SIN((origin_long-dest_long) * pi()/180 / 2), 2) )) * 1.609344 as Distance_In_Kms ;
end if;
end ;
//
delimiter ;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2011-08-29 19:45:19
No puedo comentar la respuesta anterior, pero ten cuidado con la respuesta de @Pavel Chuchuva. Esa fórmula no devolverá un resultado si ambas coordenadas son las mismas. En ese caso, la distancia es nula, y por lo que la fila no se devolverá con esa fórmula tal cual.
No soy un experto en MySQL, pero esto parece estar funcionando para mí:
SELECT id, ( 3959 * acos( cos( radians(37) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(-122) ) + sin( radians(37) ) * sin( radians( lat ) ) ) ) AS distance
FROM markers HAVING distance < 25 OR distance IS NULL ORDER BY distance LIMIT 0 , 20;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-11-24 19:38:43
SELECT *, (
6371 * acos(cos(radians(search_lat)) * cos(radians(lat) ) *
cos(radians(lng) - radians(search_lng)) + sin(radians(search_lat)) * sin(radians(lat)))
) AS distance
FROM table
WHERE lat != search_lat AND lng != search_lng AND distance < 25
ORDER BY distance
FETCH 10 ONLY
Para una distancia de 25 km
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-03-08 14:32:33
Pensé que mi implementación de javascript sería una buena referencia a:
/*
* Check to see if the second coord is within the precision ( meters )
* of the first coord and return accordingly
*/
function checkWithinBound(coord_one, coord_two, precision) {
var distance = 3959000 * Math.acos(
Math.cos( degree_to_radian( coord_two.lat ) ) *
Math.cos( degree_to_radian( coord_one.lat ) ) *
Math.cos(
degree_to_radian( coord_one.lng ) - degree_to_radian( coord_two.lng )
) +
Math.sin( degree_to_radian( coord_two.lat ) ) *
Math.sin( degree_to_radian( coord_one.lat ) )
);
return distance <= precision;
}
/**
* Get radian from given degree
*/
function degree_to_radian(degree) {
return degree * (Math.PI / 180);
}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-05-08 00:03:16