如何在Spatialite中拆分几何对象？将MultiLineString转为多个LineString

问题背景 #

使用带SpatiaLite扩展的SQLite查询GeoPackage文件，现有表mix包含LineString和MultiLineString类型的几何数据：

sqlite> load_extension('mod_spatialite');
sqlite> select EnableGpkgMode(); -- 启用GeoPackage模式
sqlite> SELECT id, geom FROM mix AS ft;
1|LINESTRING(10 0,10 60)
2|MULTILINESTRING( (40 0,40 60), (50 0,50 60) )

需要将MultiLineString拆分为单个LineString，同时保留原始id并生成类似id.index格式的newid，期望结果如下：

1|1|LINESTRING(10 0,10 60)
2|2.1|LINESTRING( 40 0,40 60 )
2|2.2|LINESTRING( 50 0,50 60 )

PostGIS中可通过ST_Dump实现，但需要SpatiaLite的等效方案。

解决方案 #

利用SpatiaLite的ST_NumGeometries（获取几何包含的子几何数量）和ST_GeometryN（提取第N个子几何）函数，结合递归CTE实现拆分：

WITH RECURSIVE exploded_geoms AS (
    -- 初始步骤：处理所有LineString，以及MultiLineString的第一个子几何
    SELECT
        id,
        CASE
            WHEN ST_GeometryType(geom) = 'LINESTRING' THEN id || ''
            ELSE id || '.1'
        END AS newid,
        CASE
            WHEN ST_GeometryType(geom) = 'LINESTRING' THEN geom
            ELSE ST_GeometryN(geom, 1)
        END AS geom,
        ST_NumGeometries(geom) AS total_parts,
        1 AS current_part
    FROM mix
    UNION ALL
    -- 递归步骤：处理MultiLineString的剩余子几何
    SELECT
        id,
        id || '.' || (current_part + 1) AS newid,
        ST_GeometryN(original_geom, current_part + 1) AS geom,
        total_parts,
        current_part + 1
    FROM (
        SELECT
            m.id,
            m.geom AS original_geom,
            eg.total_parts,
            eg.current_part
        FROM mix m
        JOIN exploded_geoms eg ON m.id = eg.id
        WHERE ST_GeometryType(m.geom) = 'MULTILINESTRING'
          AND eg.current_part < eg.total_parts
    )
)
SELECT id, newid, ST_AsText(geom) AS geom
FROM exploded_geoms
ORDER BY id, current_part;

代码说明 #

• 初始CTE分支：直接保留所有LineString，对MultiLineString提取第一个子几何，初始化newid和计数器。
• 递归CTE分支：针对未处理完的MultiLineString，依次提取后续子几何，更新newid和计数器，直到所有子几何处理完毕。
• 最终查询：用ST_AsText转换几何为文本格式，按id和子几何顺序排序。

执行结果 #

运行上述SQL后，将得到期望的输出：

1|1|LINESTRING(10 0, 10 60)
2|2.1|LINESTRING(40 0, 40 60)
2|2.2|LINESTRING(50 0, 50 60)

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