[GIS] Snapping start- and end nodes of lines to other lines in PostGIS

Question

There are plenty examples that show how to snap lines to points, but I haven't been able to find any (fast!) way of snapping the start- and end nodes of line strings to nodes of other lines.

Essentially I want to "clean" my layer in postgis (2.0), moving nearly-similar points together, and sewing up tiny openings between line strings.

It wouldn't matter so much whether I add another node, move the first/last node of either line, or move both points to the center.

I have found two options, but I'm not sure how to start with either of them:

• Use topology: http://lists.osgeo.org/pipermail/postgis-users/2012-May/034024.html
• Explode lines, snap nodes, merge nodes. http://www.mail-archive.com/postgis-users@lists.osgeo.org/msg00174.html

The second option sounds feasible, but any help on how to follow this method would be greatly appreciated.

Answer 1

I've managed to solve this, without using the mentioned GRASS tools or topological functions.

Basically I take all start- and endnodes, put them in a new, temporary table, put a buffer around them, union the buffer objects, and move all found nodes in each buffer to the centroid of the buffer.

When that's done I move the original begin and end points to the new location.

Easier than expected, and still fast, but I expected PostGIS to have some built-in function for this - that would be even quicker.

Edit: in the interest of giving back to the community, this is my (quite crappy) code for now.

drop table if exists nodes;
drop table if exists nodes2;
drop table if exists buffers;

-- Get Start and End nodes
select ST_StartPoint(wkb_geometry) startnode,  ST_EndPoint(wkb_geometry) endnode,    ogc_fid into nodes  from sourceTable;
-- Combine all nodes into one table for easier queries
select startnode node, ogc_fid into nodes2 from nodes;
insert into nodes2 select endnode node, ogc_fid from nodes;

-- Some indexes to speed everything up
CREATE INDEX nodesstart_idx ON nodes USING gist  (startnode);
CREATE INDEX nodesend_idx ON nodes USING gist  (endnode);
CREATE INDEX nodes2_idx ON nodes2 USING gist  (node);
CREATE INDEX nodes_ogcfid_idx ON nodes USING btree (ogc_fid ASC NULLS LAST);

-- Create buffers, combine them, split combined objects again
select (ST_Dump(ST_Union(ST_Buffer(node, 1)))).geom geom into buffers from nodes2;
CREATE INDEX buffers_idx ON buffers USING gist  (geom);

-- Update start/end nodes table
UPDATE nodes SET startnode = ST_Centroid((select geom from buffers WHERE geom && startnode));
UPDATE nodes SET endnode = ST_Centroid((select geom from buffers WHERE geom && endnode));
-- Update original points
update sourceTable set wkb_geometry = ST_SetPoint(
ST_SetPoint(wkb_geometry, 0, (select startnode from nodes where ogc_fid=sourceTable.ogc_fid)), 
ST_NumPoints(wkb_geometry) - 1, (select endnode from nodes where ogc_fid=sourceTable.ogc_fid));

DROP TABLE nodes;
DROP TABLE nodes2;
DROP TABLE buffers;