How to explode geometries in Spatialite, eg convert every MultiLineString feature into several LineString features

Question

I'm using sqlite with the Spatialite extension to query a geopackage file.

Consider the following test table where each row have geometry type either LineString or MultiLineString.

sqlite>load_extension('mod_spatialite');
sqlite>select EnableGpkgMode(); -- I'm testing on 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) )

I need a result where each MultiLineString is "exploded" into a number of LineStrings, like this:

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

If possible I would like both to keep the original id and create a new newid as shown above.

The following solution works in PostGIS, but I still have not found a solution for Spatialite.

postgis=> SELECT
    id,
    array_to_string(
        id || (ST_Dump(geom)).path,
        '.'
    ) AS newid,
    ST_AsText((ST_Dump(geom)).geom) AS geom
FROM cubetown.MixLS_1
;
 id | newid |          geom          
----+-------+------------------------
  1 | 1     | LINESTRING(10 0,10 60)
  2 | 2.1   | LINESTRING(40 0,40 60)
  2 | 2.2   | LINESTRING(50 0,50 60)

Answer 1

Sqlite by nature does not support set returning functions such as ST_Dump. You need to use a workaround, usually by means of a recursive CTE. The following sqlite/spatialite SQL statement should do the trick:

WITH RECURSIVE mix (id, geom) AS (VALUES
  (1, ST_GeomFromText('LINESTRING(10 0,10 60)')),
  (2, ST_GeomFromText('MULTILINESTRING( (40 0,40 60), (50 0,50 60) )'))
), n_max AS (
  SELECT max(ST_NumGeometries(geom)) as m
  FROM mix
), nlist AS (
  SELECT 1 AS n
UNION ALL
  SELECT n + 1
  FROM nlist, n_max
  WHERE n < m
)
SELECT id, n, 
  CASE
    WHEN ST_NumGeometries(geom) = 1 THEN 1
    ELSE id || '.' || n
  END AS newid,
  ST_GeometryN(geom, n) AS geom
FROM mix, nlist
WHERE ST_GeometryN(geom, n) IS NOT NULL;

Short explanation:

• the keyword RECURSIVE must be put directly after WITH, even though the actual recursive CTE "nlist" follows much later
• CTE "n_max" gets the maximum number of linestrings in any (Multi)Linestring, stored in column "m"
• recursive CTE "nlist" generates a record set with numbers from 1 to n_max.m in sequential records (which is the functional equivalent of a set returning function)
• the main SELECT at the end uses ST_GeometryN to extract the n-th linestring from a MultiLinestring, by using a cross join between tables "mix" and "nlist"
• in many cases there will be fewer actual LineStrings in an original geometry then the requested n-th, resulting in a NULL-geometry. These are excluded from the final result set with a WHERE-clause.