Adding M coordinate with different measurings in PostGIS

Question

I have a line table made of several linestring features in succession, so that all linestrings together would form one continuous line, such as:

id  |  geom
----+-----------------------
1   |  LINESTRING(0 0, 1 1)
2   |  LINESTRING(1 1, 2 2)
3   |  LINESTRING(2 2, 3 3)

I want to add measuring to this line. Normally I'd merge all line segments into one and do ST_AddMeasure(), but in this case each segment should have different measurings (e.g. the segment (0 0, 1 1) would go from 0 to 10, segment (1 1, 2 2) would go from 10 to 12, and segment (2 2, 3 3) would go from 12 to 60).

In the line table I have columns with the start and end measures for each line segment, so generating different measurings separately is easy enough. The problem comes from adding them all together in one continuous linestring. ST_Union() drops the M-index, same with ST_UnaryUnion() and ST_MemUnion().

Hence, my question: is there a way to merge line segments without droping the M-coordinate? Alternatively, is there a way to add measure to a linestring, taking into consideration difference in measurings along the line?

Answer 1

Create a new LineStringM Column and use the addMeasure function.

e.g. for your example:

update TABLE set geom2 = ST_AddMeasure(geom,0,10) where id = 1;

update TABLE set geom2 = ST_AddMeasure(geom,10,12) where id = 2;

update TABLE set geom2 = ST_AddMeasure(geom,12,60) where id = 3;

would result in

id |  geom                 |  geom2
1  |  LINESTRING(0 0,1 1)  |  LINESTRING M (0 0 0,1 1 10)
2  |  LINESTRING(1 1,2 2)  |  LINESTRING M (1 1 10,2 2 12)
3  |  LINESTRING(2 2,3 3)  |  LINESTRING M (2 2 12,3 3 60)

EDIT:
Sorry sloppy reading ... my bad :/

but .. if you change from ST_Union to ST_Collect, it works just fine:

SELECT ST_AsText(ST_Collect(geom2)) FROM TABLE;

Output:

MULTILINESTRING M ((0 0 0,1 1 10),(1 1 10,2 2 12),(2 2 12,3 3 60))

including measurement.