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.

2 Answers 2


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


Use ST_Snap:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.