PostGIS puntos más cercanos con ST_Distance, kNN

Este dilema se puede resolver de diferentes formas, pero en este caso te enseñamos la que para nosotros es la respuesta más completa.

Solución:

Estás casi allí. Hay un pequeño truco que consiste en usar el operador distinto de Postgres, que devolverá la primera coincidencia de cada combinación; como está ordenando por ST_Distance, devolverá efectivamente el punto más cercano de cada señal a cada puerto.

SELECT 
   DISTINCT ON (senal.id) senal.id, port.id, ST_Distance(port."GEOMETRY", senal."GEOMETRY")  as dist
FROM traffic_signs As senal, entrance_halls As port   
ORDER BY senal.id, port.id, ST_Distance(port."GEOMETRY", senal."GEOMETRY");

Si sabe que la distancia mínima en cada caso no es más que una cierta cantidad x (y tiene un índice espacial en sus tablas), puede acelerar esto poniendo un WHERE ST_DWithin(port."GEOMETRY", senal."GEOMETRY", distance)por ejemplo, si se sabe que todas las distancias mínimas no superan los 10 km, entonces:

SELECT 
   DISTINCT ON (senal.id) senal.id, port.id, ST_Distance(port."GEOMETRY", senal."GEOMETRY")  as dist
FROM traffic_signs As senal, entrance_halls As port  
WHERE ST_DWithin(port."GEOMETRY", senal."GEOMETRY", 10000) 
ORDER BY senal.id, port.id, ST_Distance(port."GEOMETRY", senal."GEOMETRY");

Obviamente, esto debe usarse con precaución, ya que si la distancia mínima es mayor, simplemente no obtendrá ninguna fila para esa combinación de señal y puerto.

Nota: El orden por orden debe coincidir con el orden distinto, lo cual tiene sentido, ya que distinto toma el primer grupo distinto en función de algún orden.

Se supone que tiene un índice espacial en ambas tablas.

EDITAR 1. Hay otra opción, que es usar Postgres’s <-> y <#> operadores (cálculos de distancia del punto central y del cuadro delimitador, respectivamente) que hacen un uso más eficiente del índice espacial y no requieren el truco ST_DWithin para evitar n^2 comparaciones. Hay un buen artículo de blog que explica cómo funcionan. Lo general a tener en cuenta es que estos dos operadores funcionan en la cláusula ORDER BY.

SELECT senal.id, 
  (SELECT port.id 
   FROM entrance_halls as port 
   ORDER BY senal.geom <#> port.geom LIMIT 1)
FROM  traffic_signs as senal;

EDITAR 2. Como esta pregunta ha recibido mucha atención y k-vecinos más cercanos (kNN) es generalmente un problema difícil (en términos de tiempo de ejecución algorítmica) en GIS, parece que vale la pena ampliar un poco el alcance original de esta pregunta.

La forma estándar de encontrar los x vecinos más cercanos de un objeto es usar una UNIÓN LATERAL (conceptualmente similar a a para cada ciclo). Tomando prestado descaradamente de la respuesta de dbaston, harías algo como:

SELECT
  signs.id,
  closest_port.id,
  closest_port.dist
 FROM traffic_signs
CROSS JOIN LATERAL 
  (SELECT
      id, 
      ST_Distance(ports.geom, signs.geom) as dist
      FROM ports
      ORDER BY signs.geom <-> ports.geom
     LIMIT 1
   ) AS closest_port

Entonces, si desea encontrar los 10 puertos más cercanos, ordenados por distancia, simplemente debe cambiar la cláusula LIMIT en la subconsulta lateral. Esto es mucho más difícil de hacer sin LATERAL JOINS e implica el uso de lógica de tipo ARRAY. Si bien este enfoque funciona bien, puede acelerarse enormemente si sabe que solo tiene que buscar a una distancia determinada. En este caso, puede usar ST_DWithin(signs.geom, ports.geom, 1000) en la subconsulta, que debido a la forma en que funciona la indexación con el <-> operador, una de las geometrías debe ser una constante, en lugar de una referencia de columna, puede ser mucho más rápido. Entonces, por ejemplo, para obtener los 3 puertos más cercanos, dentro de los 10 km, podría escribir algo como lo siguiente.

 SELECT
  signs.id,
  closest_port.id,
  closest_port.dist
 FROM traffic_signs
CROSS JOIN LATERAL 
  (SELECT
      id, 
      ST_Distance(ports.geom, signs.geom) as dist
      FROM ports
      WHERE ST_DWithin(ports.geom, signs.geom, 10000)
      ORDER BY ST_Distance(ports.geom, signs.geom)
     LIMIT 3
   ) AS closest_port;

Como siempre, el uso variará según la distribución de datos y las consultas, por lo que EXPLIQUE es tu mejor amigo.

Finalmente, hay un problema menor, si se usa IZQUIERDA en vez de UNIÓN CRUZADA LATERAL en eso hay que sumar EN VERDAD después del alias de consultas laterales, por ejemplo,

SELECT
  signs.id,
  closest_port.id,
  closest_port.dist
 FROM traffic_signs
LEFT JOIN LATERAL 
  (SELECT
      id, 
      ST_Distance(ports.geom, signs.geom) as dist
      FROM ports          
      ORDER BY signs.geom <-> ports.geom
      LIMIT 1
   ) AS closest_port
   ON TRUE;

Esto se puede hacer con un LATERAL JOIN en PostgreSQL 9.3+:

SELECT
  signs.id,
  closest_port.id,
  closest_port.dist
FROM traffic_signs
CROSS JOIN LATERAL 
  (SELECT
     id, 
     ST_Distance(ports.geom, signs.geom) as dist
     FROM ports
     ORDER BY signs.geom <-> ports.geom
   LIMIT 1) AS closest_port

El enfoque con unión cruzada no usa índices y requiere mucha memoria. Así que básicamente tienes dos opciones. Pre 9.3 usaría una subconsulta correlacionada. 9.3+ puedes usar un LATERAL JOIN.

KNN GIST con un giro lateral Próximamente en una base de datos cerca de usted

(consultas exactas a seguir pronto)

Te mostramos las reseñas y valoraciones de los lectores

Te invitamos a añadir valor a nuestra información participando con tu experiencia en las críticas.

---

---

Utiliza Nuestro Buscador