PostgreSQL中仅筛选有效几何图形行的查询报错求助

错误含义 #

ERROR: parse error - invalid geometry 表示PostGIS无法解析传入的几何文本数据，提示里的<-- parse error at position 1 within geometry说明解析从第一个字符就失败，大概率是传入st_geomfromtext的是空字符串、空白字符，或者完全不符合WKT（Well-Known Text）格式的内容。

产生原因 #

你在WHERE子句中添加了st_isvalid(...) is True，但PostgreSQL的查询优化器可能会优先计算SELECT列表中的表达式，再执行WHERE筛选。当数据量较大时，会碰到一些格式错误的shape字段：

• 比如shape字段不符合SRID=xxx;WKT的格式，导致split_part拆分后得到空的WKT部分
• 或者SRID部分不是有效数字，转成integer时出错
  这些错误会在st_geomfromtext执行时直接抛出异常，根本没走到WHERE的筛选步骤。而少量数据查询时刚好没碰到这些坏数据，所以能正常返回结果。

解决办法 #

1. 先定位坏数据 #

先找出所有格式异常的shape记录，方便后续修复：

SELECT 
    (dataset_object.data -> 'Waterkering'::text) ->> 'shape'::text AS shape,
    split_part((dataset_object.data -> 'Waterkering'::text) ->> 'shape'::text, ';'::text, 2) AS wkt_part,
    split_part(split_part((dataset_object.data -> 'Waterkering'::text) ->> 'shape'::text, ';'::text, 1), '='::text, 2) AS srid_part
FROM dataset_object
WHERE dataset_object.object_type::text = 'Waterkering'::text
AND (
    -- WKT部分为空或空白
    trim(split_part((dataset_object.data -> 'Waterkering'::text) ->> 'shape'::text, ';'::text, 2)) = ''
    -- SRID部分不是有效数字
    OR split_part(split_part((dataset_object.data -> 'Waterkering'::text) ->> 'shape'::text, ';'::text, 1), '='::text, 2) !~ '^[0-9]+$'
    -- shape字段本身不包含分号，拆分失败
    OR (dataset_object.data -> 'Waterkering'::text) ->> 'shape'::text NOT LIKE '%;%'
)

2. 修改查询，避免解析报错 #

使用子查询或CASE语句先过滤无效输入，确保只有合法的文本才会传入st_geomfromtext：

SELECT 
    CASE WHEN geom IS NOT NULL THEN st_length(geom)::text ELSE NULL END AS lengte,
    st_isvalid(geom),
    shape
FROM (
    SELECT 
        (dataset_object.data -> 'Waterkering'::text) ->> 'shape'::text AS shape,
        -- 先判断格式合法性，再生成几何对象
        CASE 
            WHEN (dataset_object.data -> 'Waterkering'::text) ->> 'shape'::text LIKE '%;%'
                 AND trim(split_part(shape, ';'::text, 2)) != ''
                 AND split_part(split_part(shape, ';'::text, 1), '='::text, 2) ~ '^[0-9]+$'
            THEN st_setsrid(
                st_geomfromtext(split_part(shape, ';'::text, 2)),
                split_part(split_part(shape, ';'::text, 1), '='::text, 2)::integer
            )
            ELSE NULL
        END AS geom
    FROM dataset_object
    WHERE dataset_object.object_type::text = 'Waterkering'::text
) sub_query
-- 只保留有效几何对象
WHERE st_isvalid(geom) IS TRUE

如果你的PostGIS版本支持st_safe_geomfromtext函数，可以直接用它替代st_geomfromtext，该函数在解析失败时返回NULL而非报错，简化查询：

SELECT 
    st_length(geom)::text AS lengte,
    st_isvalid(geom),
    shape
FROM (
    SELECT 
        (dataset_object.data -> 'Waterkering'::text) ->> 'shape'::text AS shape,
        st_setsrid(
            st_safe_geomfromtext(split_part(shape, ';'::text, 2)),
            split_part(split_part(shape, ';'::text, 1), '='::text, 2)::integer
        ) AS geom
    FROM dataset_object
    WHERE dataset_object.object_type::text = 'Waterkering'::text
) sub_query
WHERE geom IS NOT NULL AND st_isvalid(geom) IS TRUE

3. 修复或清理坏数据 #

根据第一步查到的坏记录，要么修正shape字段的格式（确保符合SRID=xxx;WKT的标准格式），要么直接删除这些无效记录，从根源上避免后续查询出错。

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