PostGIS：提高复杂几何图形的查询速度

Question

我是PostgreSQL和PostGIS的新手，但这个问题并不简单。我使用PostgreSQL 9.5和PostGIS 2.2。

我需要运行一些需要大量时间的查询。

首先，让我用非GIS术语来解释这个问题：
基本上，我有一组数十万个点，分布在大约五十万平方公里的领土上（国家）。在这个领土上，我有大约十几组来自各种数据库的区域。在每组中，我都有几百到几千个区域。我想找出这些领域中的哪些点。

现在，我是如何用地理信息系统的术语解决这个问题的：

每组区域都是一个Postgresql表，其中包含一个multipolygon类型的几何列，并且如前所述，包含几百到几千条记录。< br >所有这些表都包含在一个模式donnees中，但是我对这些操作使用了一个不同的模式，称为traitements。

因此，该过程是a/将所有几何图形合并为单个几何图形，然后b/查找该几何图形中包含的点。

问题是，如果步骤a/花费了合理的时间（几分钟），步骤b/需要很长时间。我目前只处理我必须处理的点的一个样本（大约1%，即大约7000个），几个小时后还没有完成（数据库连接最终超时）。我正在通过将返回行数限制为10或50来测试运行查询，并且仍然需要大约半小时。
如果你想知道的话，我正在使用一台LinuxMint 18机器，它有4个CPU和8 Gb的RAM。

我已经在几何列上创建了索引。所有几何列都使用相同的SRID。

创建表格：

CREATE TABLE traitements.sites_candidats (
    pkid serial PRIMARY KEY,
    statut varchar(255) NOT NULL,
    geom geometry(Point, 2154)
); 

CREATE UNIQUE INDEX ON traitements.sites_candidats (origine, origine_id ) ;
CREATE INDEX ON traitements.sites_candidats (statut);
CREATE INDEX sites_candidats_geométrie ON traitements.sites_candidats USING GIST ( geom );

CREATE TABLE  traitements.zones_traitements (
    pkid serial PRIMARY KEY,
    définition varchar(255) NOT NULL,
    geom geometry (MultiPolygon, 2154)
);
CREATE UNIQUE INDEX ON traitements.zones_traitements (définition) ;
CREATE INDEX zones_traitements_geométrie ON traitements.zones_traitements USING GIST ( geom );

请注意，我在表特征中指定geom列的几何类型只是因为我想指定SRID，但我不确定任何类型的几何的正确语法是什么。也许是“几何几何（几何，2154）”？

合并不同区域集合的所有几何图形:< br >如前所述，所有表格都保存多多边形类型的几何图形。这是我用来合并一个表中所有几何图形的代码:

INSERT INTO traitements.zones_traitements
    ( définition,  , geom )
    VALUES
    (
        'first-level merge', 
        (
            SELECT ST_Multi(ST_Collect(dumpedGeometries)) AS singleMultiGeometry 
            FROM
            (
                SELECT ST_Force2D((ST_Dump(geom)).geom) AS dumpedGeometries
                FROM donnees.one_table
            ) AS dumpingGeometries
        )
    ) ;

我发现一些记录中的一些几何图形是3D的，所以这就是我使用_ST_Force2D_的原因。

我对所有表执行此操作，然后再次合并几何图形，使用：

INSERT INTO traitements.zones_traitements
    ( définition, geom )
    VALUES
    (
        'second-level merge',
        (
            SELECT ST_Multi(ST_Collect(dumpedGeometries)) AS singleMultiGeometry 
            FROM
            (
                SELECT (ST_Dump(geom)).geom AS dumpedGeometries 
                FROM traitements.zones_traitements
                WHERE définition != 'second-level merge'
            ) AS dumpingGeometries
        )
    ) ;

如前所述，这些查询需要几分钟，但没关系。

不是永远需要的查询：

SELECT pkid
    FROM traitements.sites_candidats AS sites
    JOIN (
        SELECT geom FROM traitements.zones_traitements 
        WHERE définition = 'zones_rédhibitoires' ) AS zones
    ON ST_Contains(zones.geom , sites.geom)
    LIMIT 50;

分析问题 ：显然，是
子查询选择点需要花费大量时间，而不是更新。所以我对查询运行了一个解释（分析，缓冲区）：

EXPLAIN (ANALYZE, BUFFERS)
    SELECT pkid
    FROM traitements.sites_candidats AS sites
    JOIN (
        SELECT geom FROM traitements.zones_traitements 
        WHERE définition = 'second_level_merge' ) AS zones
    ON ST_Contains(zones.geom , sites.geom)
    LIMIT 10;

---------------------------------

"Limit  (cost=4.18..20.23 rows=1 width=22) (actual time=6052.069..4393634.244 rows=10 loops=1)"
"  Buffers: shared hit=1 read=688784"
"  ->  Nested Loop  (cost=4.18..20.23 rows=1 width=22) (actual time=6052.068..4391938.803 rows=10 loops=1)"
"        Buffers: shared hit=1 read=688784"
"        ->  Seq Scan on zones_traitements  (cost=0.00..1.23 rows=1 width=54939392) (actual time=0.016..0.016 rows=1 loops=1)"
"              Filter: (("définition")::text = 'zones_rédhibitoires'::text)"
"              Rows Removed by Filter: 17"
"              Buffers: shared hit=1"
"        ->  Bitmap Heap Scan on sites_candidats sites  (cost=4.18..19.00 rows=1 width=54) (actual time=6052.044..4391260.053 rows=10 loops=1)"
"              Recheck Cond: (zones_traitements.geom ~ geom)"
"              Filter: _st_contains(zones_traitements.geom, geom)"
"              Heap Blocks: exact=1"
"              Buffers: shared read=688784"
"              ->  Bitmap Index Scan on "sites_candidats_geométrie"  (cost=0.00..4.18 rows=4 width=0) (actual time=23.284..23.284 rows=3720 loops=1)"
"                    Index Cond: (zones_traitements.geom ~ geom)"
"                    Buffers: shared read=51"
"Planning time: 91.967 ms"
"Execution time: 4399271.394 ms"

我不确定如何阅读这个输出。

然而，我怀疑查询如此缓慢是因为将所有这些多重多边形合并成一个单一多边形所获得的几何图形。

问题：
使用不同类型的几何来合并其他几何会更好吗？在这种情况下，索引是如何工作的？

有比ST_Contains（）更有效率的吗？

Answer 1

再次感谢。

我得出了与你的建议相同的结论：与其将成千上万个多边形合并成一个巨大的多边形，它的bbox太大了，不如使用ST_Dump将所有多边形分解成简单的多边形，并将它们插入一个带有适当索引的专用表中，效率更高。然而，要做到这一点，我首先必须纠正几何形状：某些多边形确实有无效的几何形状。St_MakeValid将其中90%作为多边形有效，但其余的被转换成几何集合或MultilineStrings。为了纠正这些，我使用了ST_Buffer，缓冲区为0.01米，结果是正确的多边形。一旦这样做了，我所有的多边形都是有效的，我可以把它们转储到简单的多边形中。

这样做，我将搜索时间减少了/- 5000倍！

:D

Answer 2

让我们看看。首先，您应该在GIS Stackexchange上询问特定于GIS的问题。但我会尽力帮忙:

• 从技术上讲，您的几何体列定义是正确的，使用“图元”（例如POINT、LINE、POLYGON及其MULTIs）优于GEOMETRYCOLLECTION
  然而，在尽可能小的几何体上运行空间关系函数几乎总是更好的选择；对于大多数这些功能，PostGIS必须检查输入几何图形的每个垂直面（因此在这种情况下，它必须遍历多边形的数百万个顶点一次，以便在ST_Contains中检查每个点）
  PostGIS实际上会在关系检查之前（如果两个几何图形上都存在索引）启动一个bbox比较，以限制可能的匹配，并有效地将检查速度提高几个数量级；这在这里是无用的
  （我几乎建议将MULTI转储到简单的多边形中，但不要在不知道数据的情况下）
• 为什么要将MULTI几何图形转储，以便将其收集回MULTI？如果源表的几何图形实际上存储为MULTIPOLYGONS（希望是有充分理由的），只需将它们复制到中间表中，MULTIs上使用ST_Force2D，WHERE块中使用ST_IsValid（您可以在几何图形上尝试STM_MakeValid，但不能保证它会工作）
  如果已将所有表插入zones_traitements表，请运行VACUUM ANALYZE和REINDEX以实际使用索引
• 在您的“第二次合并”查询中……您是否只是将“合并”几何图形添加到表中的现有几何图形？不要，这是错误的。它会打乱表统计数据和索引，而且是不必要的开销。您应该在查询中执行这些操作，但这里没有必要
• 请记住，由查询或在查询中创建或派生的不同类型或扩展的几何体既不能有索引，也不能使用初始索引。这适用于您的“合并”查询

然后运行

SELECT pkid
FROM traitements.sites_candidats AS sites
JOIN traitements.zones_traitements AS zones
  ON ST_Intersects(zones.geom, sites.geom)

为每个与区域相交的点返回一个pkid，这样，如果一个点与两个多边形相交，您将得到该点的两行。使用< code >选择不同的pkid...对于与任何区域相交的每个pkid，只获取一行。< br >(注意:我使用了< code>ST_Intersection，因为这意味着对关系的检查更少。如果您绝对需要< code>ST_Contains，只需替换它)< br>
希望这有所帮助。如果没有，说一句。