PostGIS punto más cercano con ST_Distance

17

Necesito obtener en cada elemento de una tabla el punto más cercano de otra tabla. La primera tabla contiene señales de tráfico y la segunda, los Hall de entrada de la ciudad. El problema es que no puedo usar la función ST_ClosestPoint y tengo que usar la función ST_Distance y obtener el registro mínimo (ST_distance) pero estoy bastante atascado en la construcción de la consulta.

CREATE TABLE traffic_signs
(
  id numeric(8,0) ),
  "GEOMETRY" geometry,
  CONSTRAINT traffic_signs_pkey PRIMARY KEY (id),
  CONSTRAINT traffic_signs_id_key UNIQUE (id)
)
WITH (
  OIDS=TRUE
);

CREATE TABLE entrance_halls
(
  id numeric(8,0) ),
  "GEOMETRY" geometry,
  CONSTRAINT entrance_halls_pkey PRIMARY KEY (id),
  CONSTRAINT entrance_halls_id_key UNIQUE (id)
)
WITH (
  OIDS=TRUE
);

Necesito obtener el ID del entrnce_hall más cercano de cada traffic_sign.

Mi consulta hasta ahora:

SELECT 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")

Con esto obtengo la distancia de cada traffic_sign a cada entrance_hall. Pero, ¿cómo puedo obtener solo la distancia mínima?

Saludos,

    
pregunta Egidi 23.02.2015 - 13:19

4 respuestas

29

Ya casi llegas. Hay un pequeño truco que consiste en utilizar el operador distinto de Postgres, que devolverá el primer coincidencia de cada combinación: a medida que ordena por ST_Distance, efectivamente devolverá el punto más cercano de cada senal 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 cierta cantidad x, (y tiene un índice espacial en sus tablas), puede acelerar esto colocando un WHERE ST_DWithin(port."GEOMETRY", senal."GEOMETRY", distance) , por ejemplo, si todas las Se sabe que las distancias mínimas no son más de 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á una fila para esa combinación de senal y puerto.

Nota: La orden por orden debe coincidir con la orden distinta, lo que tiene sentido, ya que la distinción es tomar el primer grupo distinto basado en algún ordenamiento.

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

EDIT 1 . Hay otra opción, que es usar < - > de Postgres 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 del blog explicando cómo funcionan. Lo general a tener en cuenta es que estos dos operadores trabajan 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;
    
respondido por el John Powell 23.02.2015 - 15:01
9

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

KNN GIST con un giro lateral Próximamente a una base de datos cercana a usted

(consultas exactas a seguir pronto)

    
respondido por el Jakub Kania 23.02.2015 - 15:24
9

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
    
respondido por el dbaston 09.02.2017 - 18:38
1

@John Barça

ORDER BY está mal!

ORDER BY senal.id, port.id, ST_Distance(port."GEOMETRY", senal."GEOMETRY");

a la derecha

senal.id, ST_Distance(port."GEOMETRY", senal."GEOMETRY"),port.id;

de lo contrario, no devolverá el más cercano, solo el que tiene el pequeño ID de puerto

    
respondido por el strech 09.02.2017 - 18:01

Lea otras preguntas en las etiquetas