PostGIS两组大型点之间的最小距离 [英] PostGIS minimum distance between two large sets of points

问题描述

我在PostGIS中有两个点表，分别是A和B，我想知道A中的每个点到B中最近点的距离是多少.指向以下查询:

I have two tables of points in PostGIS, say A and B, and I want to know, for every point in A, what is the distance to the closest point in B. I am able to solve this for small sets of points with the following query:

SELECT a.id, MIN(ST_Distance_Sphere(a.geom, b.geom))
FROM table_a a, table_b b
GROUP BY a.id;

但是，我在每个表中都有几百万个点，并且该查询会无限期地运行.是否有一些更有效的方法来解决此问题.我愿意获得一个近似的距离，而不是一个确切的距离.

However, I have a couple million points in each table and this query runs indefinitely. Is there some more efficient way to approach this. I am open to getting an approximate distance rather than an exact one.

编辑:对JGH提供的答案进行了稍许修改，以返回距离(以米为单位)，而不是度数(如果未投影点).

A slight modification to the answer provided by JGH to return distances in meters rather than degrees if points are unprojected.

SELECT 
    a.id, nn.id AS id_nn, 
    a.geom, nn.geom_closest, 
    ST_Distance_Sphere(a.geom, nn.geom_closest) AS min_dist
FROM 
    table_a AS a
    CROSS JOIN LATERAL
        (SELECT
            b.id, 
            b.geom AS geom_closest
        FROM table_b b
        ORDER BY a.geom <-> b.geom
        LIMIT 1) AS nn;

推荐答案

您的查询速度很慢，因为它无需使用任何索引即可计算每个点之间的距离.您可以将其重写为使用 <-> 运算符，该运算符在条款.

Your query is slow because it computes the distance between every points without using any index. You could rewrite it to use the <-> operator that uses the index if used in the order by clause.

select a.id,closest_pt.id, closest_pt.dist
from tablea a
CROSS JOIN LATERAL
  (SELECT
     id , 
     a.geom <-> b.geom as dist
     FROM tableb b
     ORDER BY a.geom <-> b.geom
   LIMIT 1) AS closest_pt;

这篇关于PostGIS两组大型点之间的最小距离的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！