latitude / longitude encuentra la latitud / longitud más cercana – sql complejo o cálculo complejo

Tengo latitud y longitud y quiero extraer el logging de la database, que tiene la latitud y la longitud más cercanas por la distancia, si esa distancia es más larga que la especificada, y luego no la recupero.

Estructura de la tabla:

id latitude longitude place name city country state zip sealevel 

Lo que necesita es traducir la distancia en grados de longitud y latitud, filtrar en function de aquellos para unir las inputs que están aproximadamente en el cuadro delimitador, y luego hacer un filter de distancia más preciso. Aquí hay un gran documento que explica cómo hacer todo esto:

http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL

 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 position 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 attributes 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 como mínimo el espacio de almacenamiento requerido para su table, puede especificar que los attributes 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 id para que sirva como key 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 table

Después de crear la tabla, es hora de llenarla con datos. Los datos de muestra proporcionados a continuación son para unas 180 pizzarias esparcidas por los Estados Unidos. En phpMyAdmin, puede usar la pestaña IMPORTAR para importar varios formattings de file, incluido CSV (valores separados por comas). Las hojas de cálculo de Microsoft Excel y Google se exportan al formatting CSV, por lo que puede transferir fácilmente datos de hojas de cálculo a tablas de MySQL mediante la export / import de files 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-Finetworking 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 una cierta distancia de radio de una latitud / longitud determinada, puede usar una instrucción SELECT basada en la fórmula de Haversine. La fórmula de Haversine se usa generalmente para calcular distancias de círculo máximo entre dos pares de coorderadas en una esfera. Una explicación matemática en profundidad es dada por Wikipedia y una buena discusión de la fórmula en lo que se refiere a la progtwigción está en el sitio de Movable Type.

Aquí está la statement de SQL que encontrará las 20 ubicaciones más cercanas que se encuentran dentro de un radio de 25 millas con la coorderada 37, -122. Calcula la distancia en function de la latitud / longitud de esa fila y la latitud / longitud objective, y luego solicita solo filas donde el valor de la distancia es inferior a 25, ordera toda la consulta por distancia y la limita a 20 resultados. Para search 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; 

https://developers.google.com/maps/articles/phpsqlsearch_v3#creating-the-map

Aquí está mi solución completa implementada en PHP.

Esta solución utiliza la fórmula Haversine como se presenta en http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL .

Cabe señalar que la fórmula Haversine experimenta debilidades alnetworkingedor de los polos. Esta respuesta muestra cómo implementar la fórmula Vincenty Great Circle Distance para evitar esto, sin embargo, elegí simplemente 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). ¡Espero que esto ayude!

showClosest.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" ); ?> 

Puede ser posible boost el performance mediante el uso de un procedimiento almacenado de MySQL como lo sugiere el artículo "Geo-Distance-Search-with-MySQL" publicado anteriormente.

Tengo una database de ~ 17,000 lugares y el time de ejecución de la consulta es de 0.054 segundos.

En caso de que seas flojo como yo, aquí hay una solución amalgamada de esta y otras respuestas en SO.

 set @orig_lat=37.46; set @orig_long=-122.25; set @bounding_distance=1; SELECT * ,((ACOS(SIN(@orig_lat * PI() / 180) * SIN(`lat` * PI() / 180) + COS(@orig_lat * PI() / 180) * COS(`lat` * PI() / 180) * COS((@orig_long - `long`) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `cities` WHERE ( `lat` BETWEEN (@orig_lat - @bounding_distance) AND (@orig_lat + @bounding_distance) AND `long` BETWEEN (@orig_long - @bounding_distance) AND (@orig_long + @bounding_distance) ) ORDER BY `distance` ASC limit 25; 

Fácil 😉

 SELECT * FROM `WAYPOINTS` W ORDER BY ABS(ABS(W.`LATITUDE`-53.63) + ABS(W.`LONGITUDE`-9.9)) ASC LIMIT 30; 

Simplemente reemplace las coorderadas con las requeridas. Los valores deben almacenarse como dobles. Este es un ejemplo funcional de MySQL 5.x.

Aclamaciones

Estás buscando cosas como la fórmula de haversine . Vea aquí también.

Hay otros, pero este es el más comúnmente citado.

Si está buscando algo aún más sólido, es posible que desee ver las capacidades SIG de sus bases de datos. Son capaces de algunas cosas geniales como decirle si aparece un punto (Ciudad) dentro de un polígono determinado (Región, País, Continente).

Pruebe esto, muestra los puntos más cercanos a las coorderadas proporcionadas (dentro de los 50 km). Funciona perfectamente

 SELECT m.name, m.lat, m.lon, p.distance_unit * DEGREES(ACOS(COS(RADIANS(p.latpoint)) * COS(RADIANS(m.lat)) * COS(RADIANS(p.longpoint) - RADIANS(m.lon)) + SIN(RADIANS(p.latpoint)) * SIN(RADIANS(m.lat)))) AS distance_in_km FROM <table_name> AS m JOIN ( SELECT <userLat> AS latpoint, <userLon> AS longpoint, 50.0 AS radius, 111.045 AS distance_unit ) AS p ON 1=1 WHERE m.lat BETWEEN p.latpoint - (p.radius / p.distance_unit) AND p.latpoint + (p.radius / p.distance_unit) AND m.lon BETWEEN p.longpoint - (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint)))) AND p.longpoint + (p.radius / (p.distance_unit * COS(RADIANS(p.latpoint)))) ORDER BY distance_in_km 

Simplemente cambie <table_name> . <userLat> y <userLon>

Puede leer más sobre esta solución aquí: http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/

Verifique este código según el artículo Geo-Distance-Search-with-MySQL :

Ejemplo: encuentre los 10 hoteles más cercanos a mi location actual en un radio de 10 millas:

 #Please notice that (lat,lng) values mustn't be negatives to perform all calculations set @my_lat=34.6087674878572; set @my_lng=58.3783670308302; set @dist=10; #10 miles radius SELECT dest.id, dest.lat, dest.lng, 3956 * 2 * ASIN(SQRT(POWER(SIN((@my_lat -abs(dest.lat)) * pi()/180 / 2),2) + COS(@my_lat * pi()/180 ) * COS(abs(dest.lat) * pi()/180) * POWER(SIN((@my_lng - abs(dest.lng)) * pi()/180 / 2), 2)) ) as distance FROM hotel as dest having distance < @dist ORDER BY distance limit 10; #Also notice that distance are expressed in terms of radius. 
 simpledb.execSQL("CREATE TABLE IF NOT EXISTS " + tablename + "(id INTEGER PRIMARY KEY AUTOINCREMENT,lat double,lng double,address varchar)"); simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2891001','70.780154','craftbox');"); simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2901396','70.7782428','kotecha');");//22.2904718 //70.7783906 simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2863155','70.772108','kkv Hall');"); simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.275993','70.778076','nana mava');"); simpledb.execSQL("insert into '" + tablename + "'(lat,lng,address)values('22.2667148','70.7609386','Govani boys hostal');"); double curentlat=22.2667258; //22.2677258 double curentlong=70.76096826;//70.76096826 double curentlat1=curentlat+0.0010000; double curentlat2=curentlat-0.0010000; double curentlong1=curentlong+0.0010000; double curentlong2=curentlong-0.0010000; try{ Cursor c=simpledb.rawQuery("select * from '"+tablename+"' where (lat BETWEEN '"+curentlat2+"' and '"+curentlat1+"') or (lng BETWEEN '"+curentlong2+"' and '"+curentlong1+"')",null); Log.d("SQL ", c.toString()); if(c.getCount()>0) { while (c.moveToNext()) { double d=c.getDouble(1); double d1=c.getDouble(2); } } } catch (Exception e) { e.printStackTrace(); } 

Parece que quieres hacer una búsqueda de un vecino más cercano con un límite en la distancia. SQL no admite nada de esto, hasta donde yo sé, y necesitaría usar una estructura de datos alternativa, como un tree R o un tree kd .

Encuentra los usuarios más cercanos a mi:

Distancia en metros

Basado en la fórmula de Vincenty

tengo una tabla de usuarios:

 +----+-----------------------+---------+--------------+---------------+ | id | email | name | location_lat | location_long | +----+-----------------------+---------+--------------+---------------+ | 13 | xxxxxx@xxxxxxxxxx.com | Isaac | 17.2675625 | -97.6802361 | | 14 | xxxx@xxxxxxx.com.mx | Monse | 19.392702 | -99.172596 | +----+-----------------------+---------+--------------+---------------+ 

sql:

 -- my location: lat 19.391124 -99.165660 SELECT (ATAN( SQRT( POW(COS(RADIANS(users.location_lat)) * SIN(RADIANS(users.location_long) - RADIANS(-99.165660)), 2) + POW(COS(RADIANS(19.391124)) * SIN(RADIANS(users.location_lat)) - SIN(RADIANS(19.391124)) * cos(RADIANS(users.location_lat)) * cos(RADIANS(users.location_long) - RADIANS(-99.165660)), 2) ) , SIN(RADIANS(19.391124)) * SIN(RADIANS(users.location_lat)) + COS(RADIANS(19.391124)) * COS(RADIANS(users.location_lat)) * COS(RADIANS(users.location_long) - RADIANS(-99.165660)) ) * 6371000) as distance, users.id FROM users ORDER BY distance ASC 

radio de la tierra: 6371000 (en metros)

Parece que debería usar PostGIS, SpatialLite, SQLServer2008 u Oracle Spatial. Todos pueden responder esta pregunta por usted con SQL espacial.

En casos extremos, este enfoque falla, pero para el performance, me salté la trigonometría y simplemente calculé el cuadrado diagonal.

Este problema no es muy difícil en absoluto, pero se vuelve más complicado si necesita optimizarlo.

Lo que quiero decir es, ¿tiene 100 ubicaciones en su database o 100 millones? Eso hace una gran diferencia.

Si el número de ubicaciones es pequeño, sáquelo de SQL y codifíquelo simplemente haciendo ->

 Select * from Location 

Una vez que los tenga en el código, calcule la distancia entre cada lat / lon y su original con la fórmula de Haversine y ordénelo.

Intereting Posts