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).

Mapa con radio de acción

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;

Sobre el autor

Pablo Blanco

Llevo 22 años dedicado al desarrollo de software de forma profesional. En la actualidad también centrado en la docencia, como profesor técnico de F.P., en ciclos formativos de informática.
Titulado en Ingeniería Tec. Informática y Diplomado en CC. Empresariales.

17 Comentarios

Deja un comentario
  • 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 !

  • 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 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.

Escribe un comentario

 

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Puede usar estas etiquetas y atributos HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Este sitio usa Akismet para reducir el spam. Aprende cómo se procesan los datos de tus comentarios.

Copyright © 2024. Pablo Blanco.