Seleccionar con SQL puntos geográficos dentro de un radio, conocidas sus coordenadas geográficas
El problema planteado surge constantemente hoy en día. Supongamos que conocemos la localización geográfica (latitud y longitud) de un conjunto de elementos (personas, comercios, poblaciones, …) y nos preguntamos cuáles de esos elementos se encuentran dentro de un determinado radio de acción conocida nuestra posición geográfica actual (latitud y longitud).
Como muestra la imagen, ese radio de acción se puede representar como un círculo en cuyo centro nos encontramos. El problema se puede resolver entonces preguntándonos ¿Cuáles de los elementos conocidos se encuentra dentro del círculo?
Si los elementos buscados se encuentran en una base de datos MySQL, en una tabla que contenga las coordenadas geográficas de cada elemento, podremos extraerlos utilizando una única sentencia SQL, basándonos en el uso de funciones trigonométricas, como expongo a continuación.
Supongamos que esos elementos cuya posición conocemos se encuentran en una tabla llamada «mitabla» que contiene las coordenadas geográficas de cada elemento (campos que llamo «lat» y «lon») y además, por ejemplo, el nombre del elemento (campo «nombre»).
Supongamos además que el radio dentro del cual quiero buscar es 70 kilómetros, y que estamos situados en el siguiente punto geográfico que tomaremos como centro:
Centro (latitud, longitud) = (41.671958, -3.685049)
La sentencia sql sería:
SELECT nombre, lat, lon, ( 6371 * acos(cos(radians(41.671958)) * cos(radians(lat)) * cos(radians(lon) - radians(-3.685049)) + sin(radians(41.671958)) * sin(radians(lat)))) AS distance FROM mitabla HAVING distance < 70 ORDER BY distance;
excelente gracias por tu aporte
Muchas gracias por el aporte. Sólo quisiera preguntar por qué debe usarse el valor «6371». ¿Qué indica?
Hola Jessrom:
6371 es el radio medio de la tierra medido en Km. Se usa el radio medio porque no en todos los puntos es igual (en los polos es 6357 Km y en el ecuador 6378 Km).
Tengo pendiente hacer una ampliación de este artículo, con la explicación geométrica/trigonométrica de la fórmula.
¡Un saludo!
Excelente aporte, esto es justo lo que necesitaba y no tenia idea de como hacerlo.
Muchas gracias !
Excelente, gracias por compartir
Tengo una duda..
si el rango que quiero medir no son 70km sino 7 metros como serian los valores de la consulta ?
muchas gracias… muy buen articulo
Hola Camilo:
Las unidades de la fórmula son los kilómetros. Como un kilómetros son mil metros, haciendo una regla de tres puedes comprobar que siete metros son 0,007 Km.
Por lo tanto, en el ejemplo, sustituirías 70 por 0,007.
¡Un saludo!
en el caso de que tenga una lista de coordenadas, esta sentencia me sirve para un solo punto en el caso de querer ordenar todos los puntos por distancia ¿como haría en ese caso?
muchas gracias, me sirvio ese codigo a la perfeccion.
Yeison de Colombia
Muchas gracias por la formula, aceleró el proyecto de lo que estoy avanzando xd
Hola, cuando escribo todo el comando me dice que no existe la columna distance. Hay que crear una? Creía que el sql entendería que distance se refiere a la distancia que quieres poner.
Hola Roque,
«distance» es un campo calculado, se crea durante la ejecución de la sentencia sql.
Verifica que hayas puesto todos los paréntesis correctamente.
¡Saludo!
Hola Roque, hola Pablo,
Si usas Postgres, es normal que te de el error de que la columna distance no existe. Requieres separar en otro nivel del query el filtro de distance para que pueda ver el campo. Por ejemplo:
with pos as(
SELECT *, ( 6371 * acos(cos(RADIANS(18.01826)) * cos(RADIANS(cast(latitud as double precision))) * cos(RADIANS(cast(longitud as double precision)) – RADIANS(-94.54112))
+ sin(RADIANS(18.01826)) * sin(RADIANS(cast(latitud as double precision))))) AS distance
FROM posiciones_log
where fechahora > ‘2019-07-21 00:00:00’
)
select *
from pos
where distance < 20 ORDER BY distance;
En este ejemplo, mis campos de longitud y latitud son texto, por eso uso un cast. Mi radio es de 20Km. Mi centro de referencia es 18.01826 , -94.54112.
Saludos
Hola Gauss:
Muchas gracias por tu aportación. Es cierto que el ejemplo propuesto está pensado para mysql, y no lo he probado en otros SGBD.
¡Un saludo!
Buenas. Una ayuda tengo un software que tiene la direcciones para la entrega de los pedidos, pero no se como organizar en SQL, dichas direcciones geográficamente, para que sea mas fácil para el mensajero.
Muchas gracias por tu aporte. es lo que estaba buscando para una aplicación en Dirección Meteorológica de Chile.
Saludos
excelente aporte muchas gracias