Encuentre la latitud/longitud más cercana con una consulta SQL
Tengo latitud y longitud y quiero extraer el registro de la base de datos, que tiene la latitud y longitud más cercanas por la distancia, si esa distancia es más larga que la especificada, entonces no la recupere.
Estructura de la tabla:
id
latitude
longitude
place name
city
country
state
zip
sealevel
SELECT latitude, longitude, SQRT(
POW(69.1 * (latitude - [startlat]), 2) +
POW(69.1 * ([startlng] - longitude) * COS(latitude / 57.3), 2)) AS distance
FROM TableName HAVING distance < 25 ORDER BY distance;
donde [starlat] y [startlng] es la posición donde comenzar a medir la distancia.
La solución de Google:
Creando la tabla
Cuando crea la tabla MySQL, debe prestar especial atención a los atributos lat y lng. Con las capacidades de zoom actuales de Google Maps, solo deberías necesitar 6 dígitos de precisión después del decimal. Para mantener el espacio de almacenamiento requerido para su tabla al mínimo, puede especificar que los atributos lat y lng sean flotantes de tamaño (10,6). Eso permitirá que los campos almacenen 6 dígitos después del decimal, más hasta 4 dígitos antes del decimal, por ejemplo, -123,456789 grados. Su tabla también debe tener un atributo de identificación que sirva como clave principal.
CREATE TABLE `markers` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 60 ) NOT NULL ,
`address` VARCHAR( 80 ) NOT NULL ,
`lat` FLOAT( 10, 6 ) NOT NULL ,
`lng` FLOAT( 10, 6 ) NOT NULL
) ENGINE = MYISAM ;
Poblando la mesa
Después de crear la tabla, es hora de completarla con datos. Los datos de muestra que se proporcionan a continuación corresponden a aproximadamente 180 pizzerías repartidas por todo Estados Unidos. En phpMyAdmin, puede usar la pestaña IMPORTAR para importar varios formatos de archivos, incluido CSV (valores separados por comas). Microsoft Excel y Google Spreadsheets exportan a formato CSV, por lo que puede transferir fácilmente datos de hojas de cálculo a tablas MySQL exportando/importando archivos CSV.
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Frankie Johnnie & Luigo Too','939 W El Camino Real, Mountain View, CA','37.386339','-122.085823');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Amici\'s East Coast Pizzeria','790 Castro St, Mountain View, CA','37.38714','-122.083235');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Kapp\'s Pizza Bar & Grill','191 Castro St, Mountain View, CA','37.393885','-122.078916');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Round Table Pizza: Mountain View','570 N Shoreline Blvd, Mountain View, CA','37.402653','-122.079354');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Tony & Alba\'s Pizza & Pasta','619 Escuela Ave, Mountain View, CA','37.394011','-122.095528');
INSERT INTO `markers` (`name`, `address`, `lat`, `lng`) VALUES ('Oregano\'s Wood-Fired Pizza','4546 El Camino Real, Los Altos, CA','37.401724','-122.114646');
Encontrar ubicaciones con MySQL
Para encontrar ubicaciones en su tabla de marcadores que estén dentro de un cierto radio de distancia de una latitud/longitud determinada, puede usar una instrucción SELECT basada en la fórmula de Haversine. La fórmula de Haversine se utiliza generalmente para calcular distancias de círculo máximo entre dos pares de coordenadas en una esfera. Wikipedia ofrece una explicación matemática detallada y una buena discusión de la fórmula en relación con la programación se encuentra en el sitio de Movable Type.
Aquí está la declaración SQL que encontrará las 20 ubicaciones más cercanas que se encuentran dentro de un radio de 25 millas hasta la coordenada 37, -122. Calcula la distancia en función de la latitud/longitud de esa fila y la latitud/longitud objetivo, y luego solicita solo filas donde el valor de distancia es menor que 25, ordena toda la consulta por distancia y la limita a 20 resultados. Para buscar por kilómetros en lugar de millas, reemplace 3959 por 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 < 28
ORDER BY distance LIMIT 0, 20;
Éste es para encontrar latitudes y longitudes en una distancia inferior a 28 millas.
Otra es encontrarlos en una distancia entre 28 y 29 millas:
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 < 29 and distance > 28
ORDER BY distance LIMIT 0, 20;
https://developers.google.com/maps/articles/phpsqlsearch_v3#creating-the-map
Las respuestas originales a la pregunta son buenas, pero las versiones más nuevas de MySQL (MySQL 5.7.6 en adelante) admiten consultas geográficas, por lo que ahora puede utilizar la funcionalidad integrada en lugar de realizar consultas complejas.
Ahora puedes hacer algo como:
select *, ST_Distance_Sphere( point ('input_longitude', 'input_latitude'),
point(longitude, latitude)) * .000621371192
as `distance_in_miles`
from `TableName`
having `distance_in_miles` <= 'input_max_distance'
order by `distance_in_miles` asc
Los resultados se devuelven en meters. Entonces, si lo desea, KMsimplemente use .001en lugar de .000621371192(que es para millas).
Los documentos de MySQL están aquí
Aquí está mi solución completa implementada en PHP.
Esta solución utiliza la fórmula de Haversine tal como se presenta en http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL .
Cabe señalar que la fórmula de Haversine experimenta debilidades en torno a los polos. Esta respuesta muestra cómo implementar la fórmula Vincenty Great Circle Distance para solucionar este problema; sin embargo, elegí usar Haversine porque es lo suficientemente bueno para mis propósitos.
Estoy almacenando la latitud como DECIMAL(10,8) y la longitud como DECIMAL(11,8). ¡Ojalá esto ayude!
mostrar más cercano.php
<?PHP
/**
* Use the Haversine Formula to display the 100 closest matches to $origLat, $origLon
* Only search the MySQL table $tableName for matches within a 10 mile ($dist) radius.
*/
include("./assets/db/db.php"); // Include database connection function
$db = new database(); // Initiate a new MySQL connection
$tableName = "db.table";
$origLat = 42.1365;
$origLon = -71.7559;
$dist = 10; // This is the maximum distance (in miles) away from $origLat, $origLon in which to search
$query = "SELECT name, latitude, longitude, 3956 * 2 *
ASIN(SQRT( POWER(SIN(($origLat - latitude)*pi()/180/2),2)
+COS($origLat*pi()/180 )*COS(latitude*pi()/180)
*POWER(SIN(($origLon-longitude)*pi()/180/2),2)))
as distance FROM $tableName WHERE
longitude between ($origLon-$dist/cos(radians($origLat))*69)
and ($origLon+$dist/cos(radians($origLat))*69)
and latitude between ($origLat-($dist/69))
and ($origLat+($dist/69))
having distance < $dist ORDER BY distance limit 100";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
echo $row['name']." > ".$row['distance']."<BR>";
}
mysql_close($db);
?>
./assets/db/db.php
<?PHP
/**
* Class to initiate a new MySQL connection based on $dbInfo settings found in dbSettings.php
*
* @example $db = new database(); // Initiate a new database connection
* @example mysql_close($db); // close the connection
*/
class database{
protected $databaseLink;
function __construct(){
include "dbSettings.php";
$this->database = $dbInfo['host'];
$this->mysql_user = $dbInfo['user'];
$this->mysql_pass = $dbInfo['pass'];
$this->openConnection();
return $this->get_link();
}
function openConnection(){
$this->databaseLink = mysql_connect($this->database, $this->mysql_user, $this->mysql_pass);
}
function get_link(){
return $this->databaseLink;
}
}
?>
./assets/db/dbSettings.php
<?php
$dbInfo = array(
'host' => "localhost",
'user' => "root",
'pass' => "password"
);
?>
Es posible aumentar el rendimiento mediante el uso de un procedimiento almacenado MySQL como lo sugiere el artículo "Geo-Distance-Search-with-MySQL" publicado anteriormente.
Tengo una base de datos de ~17.000 lugares y el tiempo de ejecución de la consulta es de 0,054 segundos.
