Adding multiple points to linestring in PostGIS

Question

I have two linestring tables, both consisting of simple, 2-node line objects: one has the segments of exploded road centre-lines ('r_lines'), while the other has lines ('a_lines') from an addresspoint to the nearest point on the nearest centre-line segment, which in most cases is neither the start nor end node of that roadline segment.

Ultimately I need to have a table consisting entirely of (connected) 2-pt linestrings.

I haven't been able to figure out how to introduce the one endpoint of the a_lines into the r_lines to create new segments, and the problem seems to be that one r_line segment might be intersected by several a_lines. So a previously described method for adding vertices to linestrings by breaking and re-merging can't be directly applied, unless I could do it iteratively (which I don't know how to do).

I have also tried dropping all the points into one table and then creating a new linestring with the grouping method described here. This did create lines, but not the ones I was expecting, as they frequently seemed to double-back on each other.

Answer 1

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

Answer 2

Use ST_Snap:

SELECT ST_AsText( ST_Snap('LINESTRING (0 0, 9 9, 20 20)', 
 'MULTIPOINT( (1 1.1), (12 11.9) )', 0.2));

 st_astext 
-----------------------------------------
 LINESTRING(0 0,1 1.1,9 9,12 11.9,20 20)