如何使用ST_Split实现按属性匹配的选择性线要素空间分割

你提供的LineString测试场景下代码逻辑完全正确，同颜色的线只会被对应颜色的点切割，出现不符合预期的核心原因是你实际使用的线要素为MultiLineString多部件类型，PostGIS的st_split函数原生仅支持单部件LineString作为被切割对象，直接传入MultiLineString会出现匹配逻辑失效、切割结果错误的问题。

解决方案 #

先将MultiLineString拆分为单部件LineString再执行切割，切割完成后可按需合并回多部件类型，修改后代码如下：

WITH knife AS (
    SELECT st_union(geom) AS geom, colour
    FROM points
    GROUP BY colour
),
-- 拆解多部件线为单部件
single_parts AS (
    SELECT
        id,
        colour,
        (st_dump(geom)).geom AS geom
    FROM lines
)
SELECT
    single_parts.colour,
    (st_dump(st_split(single_parts.geom, knife.geom))).geom AS geom
FROM single_parts
INNER JOIN knife ON single_parts.colour = knife.colour;

如果需要将切割结果恢复为和原始数据一致的MultiLineString类型，增加聚合逻辑即可：

WITH knife AS (
    SELECT st_union(geom) AS geom, colour
    FROM points
    GROUP BY colour
),
single_parts AS (
    SELECT
        id,
        colour,
        (st_dump(geom)).geom AS geom
    FROM lines
),
split_result AS (
    SELECT
        single_parts.id,
        single_parts.colour,
        (st_dump(st_split(single_parts.geom, knife.geom))).geom AS geom
    FROM single_parts
    INNER JOIN knife ON single_parts.colour = knife.colour
)
-- 按原始id和颜色合并为多部件线
SELECT
    id,
    colour,
    st_collect(geom) AS geom
FROM split_result
GROUP BY id, colour;

额外优化建议 #

如果存在点和线坐标浮点精度不匹配导致切割失败的情况，可以在构造切割刀时增加点吸附逻辑，指定合适的容差阈值即可：

WITH knife AS (
    SELECT
        -- 4326坐标系下容差0.00001对应约1米，可根据实际场景调整
        st_union(st_snap(points.geom, lines.geom, 0.00001)) AS geom,
        points.colour
    FROM points
    INNER JOIN lines ON points.colour = lines.colour
    GROUP BY points.colour
)
-- 后续逻辑和上面保持一致

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