mysql - puntos - coordenadas geograficas sql server
latitude/longitude encuentra la latitud/longitud más cercana-sql complejo o cálculo complejo (16)
Tengo latitud y longitud y quiero extraer el registro de la base de datos, 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
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 como mínimo el espacio de almacenamiento requerido para su mesa, 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 id para 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 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 formatos de archivo, incluido CSV (valores separados por comas). Las hojas de cálculo de Microsoft Excel y Google se exportan al formato CSV, por lo que puede transferir fácilmente datos de hojas de cálculo a tablas de MySQL mediante la exportación / importación de 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 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 coordenadas 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 programación está en el sitio de Movable Type.
Aquí está la declaración de SQL que encontrará las 20 ubicaciones más cercanas que se encuentran dentro de un radio de 25 millas con 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 la 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;
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 alrededor 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 aumentar el rendimiento mediante el uso de un procedimiento almacenado de 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.
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;
En casos extremos, este enfoque falla, pero para el rendimiento, me salté la trigonometría y simplemente calculé el cuadrado diagonal.
Estás buscando cosas como la fórmula de haversine . Vea here 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).
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 base de datos 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 ingreses en el código, calcula la distancia entre cada latitud / longitud y tu original con la fórmula de Haversine y ordénalos.
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 coordenadas con las requeridas. Los valores deben almacenarse como dobles. Este es un ejemplo funcional de MySQL 5.x.
Aclamaciones
Lo que necesita es traducir la distancia en grados de longitud y latitud, filtrar en función de aquellos para unir las entradas que están aproximadamente en el cuadro delimitador, y luego hacer un filtro 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
Parece que debería usar PostGIS, SpatialLite, SQLServer2008 u Oracle Spatial. Todos pueden responder esta pregunta por usted con SQL espacial.
Pruebe esto, muestra los puntos más cercanos a las coordenadas 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 ubicación 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.
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 | [email protected] | Isaac | 17.2675625 | -97.6802361 |
| 14 | [email protected] | 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)
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.
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();
}