PostGIS基于空间相交结果更新Accomplishment表phys_state字段求助

现有SQL错误原因 #

• PostgreSQL的UPDATE语法中，SET后赋值的字段不需要加表别名前缀，SET a.phys_state = xxx属于语法错误
• 仅关联了面几何表，未覆盖线、点两类几何数据
• LEFT JOIN面表后，在WHERE子句中使用poly.geom相关判断，实际会转为内连接，导致未匹配到面表的记录全部被过滤

注意：你给出的三个几何表名拼写存在笔误（多写了一个o：Accoomplishment），若实际库中表名是正确的Accomplishment前缀，可直接使用下述SQL，否则请修正对应表名。

修正后可运行的SQL #

UPDATE accomplishment a
SET phys_state = us_state.abbrev
FROM (
    -- 合并三类几何表的id与对应几何中心，避免单几何跨多州的匹配冲突
    SELECT id, ST_Centroid(geom) AS center_geom FROM accomplishment_poly
    UNION ALL
    SELECT id, ST_Centroid(geom) AS center_geom FROM accomplishment_line
    UNION ALL
    SELECT id, ST_Centroid(geom) AS center_geom FROM accomplishment_point
) all_geom
JOIN support_gis.state_g us_state 
    ON ST_Intersects(all_geom.center_geom, us_state.geom)
WHERE 
    a.phys_state IS NULL
    AND a.poly_point_line_id = all_geom.id;

可选优化点 #

• 如果存在无效几何，可以在子查询中增加WHERE ST_IsValid(geom)过滤异常数据
• 如果需要优先匹配几何覆盖占比最高的州而非取中心，可替换为通过ST_Area(ST_Intersection(geom, us_state.geom))排序后取最高值的逻辑
• 若几何表数据量较大，建议提前为三个几何表的geom字段建立空间索引，州表的geom也建议提前建空间索引，可大幅提升查询效率

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