How about
select st_union(t1.wkb_geometry) as wkb_geometry into some_table from
(
select wkb_geometry from r_lines union select wkb_geometry from a_lines
) t1
then viewing the result of some_table
The query above unions all your lines in r_lines and all your lines in a_lines into one result, then merges all of that together in one line and writes this to table some_table. If you need to then split this again, splitting at every intersection, we can explode this table using the query
select (st_dump(wkb_geometry)).geom as wkb_geometry into some_split_table from some_table
The output from above can be seen in some_split_table
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.
Best Answer
I believe the code below is a little cleaner solution than the selected answer for a couple of reasons. First no table joins are necessary and thus an addendum to the 'ON' clause is not needed for each of the street attributes, and second the above methodology can potentially produce multi-linestrings if there are multiple, non-contiguous clusters of streets that have all of the same attribute values, whereas ST_Dump takes care of that issue in this solution.