I am trying to create an application that will require a network to be rebuilt several times in an iterative process, adding connections as it progresses. Every iteration needs to use the network created in the last iteration to create the next network.

I am using pgrouting to calculate routes along the network during every iteration, and hoping that building a pgrouting topology does not try to node every intersection, because there are several under/overpasses in the data that I would prefer to simply leave as they are.

However I need to node the new connections that are being made to the existing network, so that I can include them when rebuilding the next network iteration. I have been able to draw a line connecting new points to the network, as well as creating a set of multipoints where the line meets the existing network. I want to split the existing network only at these points, but have all segments retain the attributes of the original line.

Essentially, this is the situation:

enter image description here

I want to manually split the pink line at all the pink dots, so that I can rebuild a pgrouting topology, and I want all the subsequent segments to retain the attributes of the pink line itself.

This is the code that I am using. I just keep receiving the original line's geometry, un-split.

SELECT ST_AsEWKT(ST_Split(N, P)) AS geom
  starting_network.geom as N,
  cut_points_multi.geom as P
  FROM starting_network, cut_points_multi) AS foo;

ST_Split returns the geometry as a geometry collection.

If you want to break the collection into single geometries, you need to use ST_Dump:

SELECT ST_AsEWKT((ST_Dump(ST_Split(N, P))).geom) AS geom
  starting_network.geom as N,
  cut_points_multi.geom as P
  FROM starting_network, cut_points_multi) AS foo;
  • 1
    The problem is that even the linestring inside the GeometryCollection is just the original line, unsplit – wfgeo Apr 13 '17 at 14:38
  • Are you sure, that your points really split the linestring? The PostGIS documentation mentions a function ST_Snap to make sure, the two geometries really lie on top of each other: postgis.net/docs/ST_Split.html – dkastl Apr 15 '17 at 14:58
  • You could try to split the linestring geometry with another one (instead of a point geometry). With two crossing lines it should be safe to assume, that there is a crossing point. – dkastl Apr 15 '17 at 14:59

It does sound like it's a tolerance issue that is preventing the snapping.

ST_Snap in your answer above from @1saac is one possibility. If you prefer to snap the points to the line, you could use ST_ClosestPoint.

SELECT ST_AsText(ST_Split(n.geom, ST_ClosestPoint(n.geom,p.geom))) FROM starting_network n, cut_points_multi p;


I ended up getting a solution on reddit

The correct code is:

SELECT ST_AsText(ST_Split(ST_Snap(n.geom, p.geom, 1), p.geom)) 
    FROM starting_network n, cut_points_multi p;

However I find this very strange, because it looks like I am snapping the network to the points, rather than the other way around. But the output is exactly what I want - the network split at the points. I am then able to build an updated network. If someone can explain better what is going on, please do because I feel like I am left with a working solution that does not make sense, which is never a good feeling.

  • As the documentation says (postgis.net/docs/ST_Snap.html), you snap the first "input" geometry to the second geometry, so the input geometry may change by the tolerance. You are free to flip the geometries, if you think it should be the opposite. However, since the points are so close to the linestring, it probably won't matter. – dkastl Apr 27 '17 at 16:13

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.