QspatiaLite Quicktip: Convert MULTILINESTRING to LINESTRING

One often encounters the problem, that after digitizing or running processing algorithms, the output geometry is MULTILINESTRING, but we rather wished to have the geometrytype LINESTRING. Until know I used a quite cumbersome, multistep workflow for conversion between these geometry-types - however, as we will see, all of this becomes ridicously easy with spatial SQL:

select 
   replace(replace(replace(replace(replace(replace(astext(Collect(t.geometry)), 'MULTILINESTRING((','§'), '))', '%'), '(', ''), ')', ''), '§', 'LINESTRING('), '%', ')'
) as geom
from (
    select MultiLinestringFromText('MULTILINESTRING((-1 -1, 0 0), (1 1, 4 4))') as geometry
) as t

resulting in:

LINESTRING(-1 -1, 0 0, 1 1, 4 4)

However, if your orginal line was something like MULTILINESTRING((-1 -1, 0 0), (0 0, 4 4))
you'd end up with:

LINESTRING(-1 -1, 0 0, 0 0, 4 4)

which contains double vertices, which we certainly don't want!

So be aware, that the ordering / direction of the linestring will be as in the segments of the original layer! And as we saw, gaps between subsequent end-/startnodes will be closed in the new geometry!! It is adviseable to doublecheck before / after conversion!

If you deal with a multilinestring (or a combination of any type of linesstrings) which share end/startnodes nodes things are even easieruse this SQL:

SELECT AsText(Linemerge(MultiLinestringFromText('MULTILINESTRING((-1 -1, 0 0), (0 0, 4 4))')))

resulting in:

LINESTRING(-1 -1, 0 0, 4 4)