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
In PostGIS it's a two-stage process. First you need to find the overall length of your line geometry with ST_length()
Say that produces a value of 150. Then you need to divide your desired length, 75, by the total length, giving you 0.5. Finally, with that value call ST_Line_Interpolate_Point() or ST_Line_Substring() to return the substring rather than a point.