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
Consider some test data similar top the thick line in the question's figure:
SELECT 'LINESTRING (114 374, 200 380, 250 350, 259 343, 350 280, 380 180, 383 169, 360 80)'::geometry AS geom
INTO TEMP data;
the straight line (dashed) can be constructed from the start and end points:
SELECT ST_AsText(ST_MakeLine(ST_StartPoint(geom), ST_EndPoint(geom)))
FROM data;
st_astext
----------------------------
LINESTRING(114 374,360 80)
(1 row)
Distances from each vertex (coordinate) can then be found to the straight line:
SELECT ST_Distance((ST_DumpPoints(geom)).geom, ST_MakeLine(ST_StartPoint(geom), ST_EndPoint(geom)))
FROM data
GROUP BY geom;
st_distance
------------------
0
69.806901017674
88.9020622825982
91.3124350980067
120.67515848571
79.5109993654224
74.7528608207199
0
(8 rows)
All of this logic can be put into a nested query to get the maximum depth from the geometry:
SELECT geom, max(depth)
FROM (
SELECT geom, ST_Distance((ST_DumpPoints(geom)).geom, ST_MakeLine(ST_StartPoint(geom), ST_EndPoint(geom))) AS depth
FROM data
GROUP BY geom
) AS f
GROUP BY geom;
which is 120.67515848571 for this example.
Best Answer
I seems very similar to post "postgis, extrapolate a line". If I avoid repetition of cited post, I think you just need to extrapolate beyond your extreme points. In a query you get something like this should work: