PostGIS空间连接时出现SRID错误的排查求助

第一步：确认原表几何字段的SRID是否正确 #

先查询sd_geo_uk_counties表中geometry字段的实际SRID：

SELECT ST_SRID(geometry) FROM sd_geo_uk_counties LIMIT 1;

如果返回值不是27700，说明该字段未正确绑定SRID，需要先设置：

-- 根据实际几何类型调整（比如Polygon/MultiPolygon）
ALTER TABLE sd_geo_uk_counties 
ALTER COLUMN geometry TYPE geometry(MultiPolygon, 27700) 
USING ST_SetSRID(geometry, 27700);

第二步：检查并修复无效几何 #

无效的几何图形会导致转换失败，先排查无效记录：

SELECT county, ST_IsValid(geometry) 
FROM sd_geo_uk_counties 
WHERE ST_IsValid(geometry) = false;

如果存在无效几何，执行修复：

UPDATE sd_geo_uk_counties 
SET geometry = ST_MakeValid(geometry) 
WHERE ST_IsValid(geometry) = false;

第三步：修正CTE中的转换逻辑 #

确保在转换前先明确原几何的SRID（即使原表已设置，显式声明能避免隐式错误），修改shp CTE：

shp AS (
SELECT 
    county,
    -- 先绑定原SRID再转换为4326
    ST_Transform(ST_SetSRID(geometry, 27700), 4326) AS geometry
FROM 
    sd_geo_uk_counties
)

第四步：验证转换有效性 #

单独运行转换语句测试是否正常：

SELECT county, ST_Transform(ST_SetSRID(geometry, 27700), 4326) 
FROM sd_geo_uk_counties LIMIT 5;

如果这条语句能正常返回结果，再整合到原脚本中执行。

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