PostgreSQL下基于多条件与空间相交更新多列的问题求助

PostgreSQL 的 UPDATE FROM 语法对每个待更新的目标行仅会执行一次更新操作。如果同一块网格匹配到多条道路记录，系统只会选取其中一条匹配记录计算赋值逻辑，因此仅能给对应道路类型的列赋值，其他匹配的类型列无法被正确标记。

方案1：聚合后批量更新（推荐，性能更优） #

先通过子查询关联两个表，按网格ID分组聚合出每个网格匹配的所有道路类型，再一次性更新所有列，仅需做一次空间关联，适合百万级数据量的场景。

UPDATE anagr_grid100_marche_clc g
SET
    mob_autostrada = CASE WHEN matched_types @> ARRAY['AUTOSTRADA'] THEN 'x' END,
    mob_strada_principale = CASE WHEN matched_types @> ARRAY['STRADA PRINCIPALE'] THEN 'x' END,
    mob_strada_secondaria = CASE WHEN matched_types @> ARRAY['STRADA SECONDARIA'] THEN 'x' END,
    mob_strada_locale = CASE WHEN matched_types @> ARRAY['STRADA LOCALE'] THEN 'x' END,
    mob_altra_strada = CASE WHEN matched_types @> ARRAY['ALTRA STRADA'] THEN 'x' END,
    mob_ferrovie = CASE WHEN matched_types @> ARRAY['FERROVIA'] THEN 'x' END
FROM (
    SELECT 
        g.id_grid,
        array_agg(s.legenda) AS matched_types
    FROM anagr_grid100_marche_clc g
    JOIN stradebis s ON ST_Intersects(g.geom, s.geom)
    GROUP BY g.id_grid
) AS t
WHERE g.id_grid = t.id_grid;

优化提示：执行前可先给两个表的geom列创建GIST空间索引，大幅提升空间关联速度，索引创建语句如下：

CREATE INDEX idx_anagr_grid_geom ON anagr_grid100_marche_clc USING GIST(geom);
CREATE INDEX idx_stradebis_geom ON stradebis USING GIST(geom);

方案2：EXISTS子查询逐个判断 #

对每类道路单独做存在性判断，逻辑直观不易出错，适合数据量较小的场景。

UPDATE anagr_grid100_marche_clc g
SET
    mob_autostrada = CASE WHEN EXISTS (
        SELECT 1 FROM stradebis s 
        WHERE s.legenda = 'AUTOSTRADA' AND ST_Intersects(g.geom, s.geom)
    ) THEN 'x' END,
    mob_strada_principale = CASE WHEN EXISTS (
        SELECT 1 FROM stradebis s 
        WHERE s.legenda = 'STRADA PRINCIPALE' AND ST_Intersects(g.geom, s.geom)
    ) THEN 'x' END,
    mob_strada_secondaria = CASE WHEN EXISTS (
        SELECT 1 FROM stradebis s 
        WHERE s.legenda = 'STRADA SECONDARIA' AND ST_Intersects(g.geom, s.geom)
    ) THEN 'x' END,
    mob_strada_locale = CASE WHEN EXISTS (
        SELECT 1 FROM stradebis s 
        WHERE s.legenda = 'STRADA LOCALE' AND ST_Intersects(g.geom, s.geom)
    ) THEN 'x' END,
    mob_altra_strada = CASE WHEN EXISTS (
        SELECT 1 FROM stradebis s 
        WHERE s.legenda = 'ALTRA STRADA' AND ST_Intersects(g.geom, s.geom)
    ) THEN 'x' END,
    mob_ferrovie = CASE WHEN EXISTS (
        SELECT 1 FROM stradebis s 
        WHERE s.legenda = 'FERROVIA' AND ST_Intersects(g.geom, s.geom)
    ) THEN 'x' END;

内容的提问来源于stack exchange，提问作者Rosy