postgresql - 有效地将点分配给多边形

Question

我有多边形表(千)和点表(百万)。两个表在几何列上都有 GIST 索引。重要的是，多边形不会重叠，因此每个点都恰好包含在一个多边形中。我想用这个关系生成表 (polygon_id + point_id)。

当然是简单的解决方案

SELECT a.polygon_id, p.point_id
FROM my_polygons a
JOIN my_points p ON ST_Contains(a.geom, p.geom)

这行得通，但我认为它没有必要太慢，因为它将每个多边形与每个点匹配 - 它不知道每个点只能属于一个多边形。

有什么方法可以加快速度吗？

我尝试对每个多边形进行循环，通过 ST_Contains 选择点，但只选择那些不在结果表中的点:

CREATE TABLE polygon2point (polygon_id uuid, point_id uuid);

DO $$DECLARE r record;
BEGIN
    FOR r IN SELECT polygon_id, geom
         FROM my_polygon             
    LOOP

    INSERT INTO polygon2point (polygon_id, point_id) 
    SELECT r.polygon_id, p.point_id
    FROM my_points p
    LEFT JOIN polygon2point t ON p.point_id = t.point_id
    WHERE t.point_id IS NULL AND ST_Contains(r.geom, p.geom);

    END LOOP;
END$$;

这比普通的 JOIN 方法还要慢。有什么想法吗？

Answer 1

提高速度的一种方法是 subdivide多边形变成更小的多边形。

您将创建一个新表(如果多边形经常变化，则创建一个物化 View )，为其编制索引，然后运行查询。如果分割有 128 个或更少的顶点，默认情况下，数据将以未压缩的方式存储在磁盘上，从而使查询速度更快。

CREATE TABLE poly_subdivided AS 
   SELECT ST_SUBDIVIDE(a.geom, 128) AS geom , a.polygon_id 
   FROM poly;

CREATE INDEX poly_subdivided_geom_idx ON poly_subdivided  USING gist(geom);

ANALYZE poly_subdivided;

SELECT a.polygon_id, p.point_id
FROM poly_subdivided a
JOIN my_points p ON ST_Contains(a.geom, p.geom)

这是一个很棒的article关于这个话题。