Though a newbie to PostGIS, I'm having trouble solving something that I think should be farily straightforward.

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 oter.

Grateful for any suggestions.

(And apologies for the purely textual description: as a new user of little reputation I'm prevented from uploading a helpful illustration.)

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

  • Thank you -- that appears to work for the most part. There are a few places where an r_line segment hasn't been broken by an a_line node, but I suspect the a_lines may not actually intersect at those points.
    – saint_utz
    Nov 15, 2012 at 9:04
  • Just confirming that it was unconnected a_lines causing the problem. When I extended them by a short distance (so they visibly crossed the r_lines) and then re-ran the process, the network came up as fully segmentized.
    – saint_utz
    Nov 15, 2012 at 13:27

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, privacy policy and cookie policy

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