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/[email protected]/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;

Answer 2

Here are three options. Hopefully one will help.

v.clean

Using the GRASS tools in QGIS you can clean up the topology of a spatial object. User @R.K. gives a good set of instructions on how to do this in an answer to a different question. The advantage that GRASS gives is that it will infer the shapefile's topology. The disadvantage for your situation is that your data are not in a shapefile. Of course you could export the data from Postgres into a shapefile using the "Add PostGIS Layer" tool, but that's an extra step.

Non-topological PostGIS functions

In PostGIS you can use the ST_EndPoint and ST_StartPoint functions to get the end and start point for a linestring. Then, using a combination of ST_DWithin and ST_Distance, you can find the nearest start or end point on a nearby geometry. If you have a lot of points then ST_DWithin will speed up the query a lot - assuming you have an index in place. From there you will need to establish a rule that defines which points are to be modified, and which are fixed.

The advantage here is that you don't have to send your data out to GRASS for cleaning, but there are some pitfalls to watch out for.

Topological PostGIS functions

The question referenced the PostGIS topological functions. These work great, but, as the wiki describes, you have to explicitly define the edges, nodes and faces. Clearly this will be a problem for your data set since you have known issues with the topology.

Answer 3

PostGIS has snapping functions.. maybe they will help?

ST_Snap: Snap segments and vertices of input geometry to vertices of a reference geometry.

ST_SnapToGrid: Snap all points of the input geometry to a regular grid.