如何通过st_distance比对其他表几何字段更新island表的region列

前提说明 #

• 方案适配PostgreSQL+PostGIS空间数据库场景
• 默认island表空间字段为geom，region表空间字段为geom、存储区域标识的字段为region_name，可根据实际表结构替换对应字段名
• 提前为两张表的空间字段创建空间索引，可大幅提升大数量级下的计算效率

核心实现SQL #

用子查询匹配每个island要素对应的最近region：

UPDATE island i
SET region = (
    SELECT r.region_name
    FROM region r
    ORDER BY i.geom <-> r.geom
    LIMIT 1
);

逻辑说明 #

• <->是PostGIS官方提供的距离算子，计算逻辑和st_distance完全一致，且存在空间索引时的排序效率远高于直接调用st_distance
• 如果必须显式使用st_distance函数，可将排序逻辑替换为ORDER BY st_distance(i.geom, r.geom) ASC
• 子查询会为每一条island记录遍历所有region记录计算距离，取距离最近的第一条region的标识回写到island表的对应字段
• 如果需要同时存储最近距离值，可以先给island表新增nearest_distance字段，再用如下语句同步更新两个字段：

UPDATE island i
SET (region, nearest_distance) = (
    SELECT r.region_name, st_distance(i.geom, r.geom)
    FROM region r
    ORDER BY i.geom <-> r.geom
    LIMIT 1
);

注意事项 #

• 如果两张表的空间坐标系不一致，需要先统一坐标系后再计算距离，否则会出现距离计算错误
• 数据量较大时建议先筛选小批量数据测试验证逻辑正确性后再全量更新
• 全量更新前建议备份island表数据，避免误操作导致数据丢失

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