有效地将点分配给多边形 [英] Assign points to polygons effeciently

问题描述

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

I have table of polygons (thousands), and table of points (millions). Both tables have GIST indexes on geometry columns. Important this is, polygons do not overlap, so every point is contained by exactly one polygon. I want to generate table with this relation (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)

这是可行的，但我认为这是不必要的缓慢，因为它匹配每个多边形与每个点 - 它不知道每个点只能属于一个多边形.

This works, but I think it is unnecessary slow, since it matches every polygon with every point - it does not know that every point can belong to one polygon only.

有什么办法可以加快速度吗?

我尝试对每个多边形进行循环，通过 ST_Contains 选择点，但仅选择结果表中尚未包含的点:

I tried looping for every polygon, selecting points by ST_Contains, but only those not already in the result table:

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 方法还要慢.有什么想法吗?

This even slower than trivial JOIN approach. Any ideas?

推荐答案

提高速度的一个方法是细分多边形成更小的多边形.

A way to increase the speed is to subdivide the polygons into smaller ones.

您将创建一个新表(或物化视图，如果多边形经常更改)，将其索引，然后运行查询.如果细分具有 128 个或更少的顶点，默认情况下，数据将在未压缩的情况下存储在磁盘上，从而使查询速度更快.

You would create a new table (or a materialized view should the polygon change often), index it, and then run the query. If the subdivisions have 128 vertices or less, the data will, by default, be stored uncompressed on disk, making the queries even faster.

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)

这是一篇关于该主题的精彩文章.

Here is a great article on the topic.

这篇关于有效地将点分配给多边形的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！