I am trying to figure out some basic routing with PostgreSQL/pgRouting and I'm struggling a bit.

Our network of linestrings are not topologically correct, tracks that should be connected don't touch, or if they do, it's by chance. example linestrings

It is my understanding that to make a network topology for routing I will have to split all the tracks at each intersection or crossing. The problem is most the tracks end/start points don't touch, though they are close.

So I'm looking for a way to either split the tracks into segments using a tolerance for points that touch or to modify the linestrings themselves, moving the end/start point to snap to the nearby line.

But I don't know how to go about that?

I have this query for splitting the tracks, but it relies on points touching. It works for creating segments where the trail crosses another line, perfectly. But the ST_Touches() part is useless when there is no intersection.

CREATE TABLE trail_split_points as
SELECT DISTINCT ST_GeometryN(ST_Intersection(a.track, b.track), 1) as geom      
  trails as a, trails as b
WHERE ST_Touches(a.track, b.track)
OR ST_Crosses(a.track, b.track)    
AND a.id != b.id
GROUP BY ST_Intersection(a.track, b.track);


I determined I had no choice but to fix my tracks so they are all topologically correct. It was a bit of a pain to figure out, but I've mostly done that.

The first steep was to get the start and end point of each line and then see if there was a nearby point on another line within <5 meters. If there was then I move that point to that nearby point.

I then use some query to populate a table full of all the points where these lines touch or cross.

Then using that new point table generate new lines for each segment between points. This new table is the one used for routing. The one I created a network map and indexes on for pgRouting.


I was snapping the nearby trail to the line, but not creating a new point on that line at the intersection. This is how I managed to do that.

                ST_Line_Substring(track, 0, ST_Line_Locate_Point(track, ST_GeomFromText('".$point['newpoint']."', 4326))),
                ST_Line_Substring(track, ST_Line_Locate_Point(track, ST_GeomFromText('".$point['newpoint']."', 4326)), 1)
  • This looks like a fun problem. Sadly, I don't have time right now, but you might get some ideas from blog.cleverelephant.ca/2008/04/snapping-points-in-postgis.html. This will allow you to find the nearest point on each linestring to the end of another one, which you could then use to extend that linestring till they touch. – John Powell Nov 27 '14 at 6:58
  • Having had another look at the above article, it is about snapping lines that already intersect. You can still use the technique, but I think you'll have to extend the line to the nearest intersection point. I am very intrigued by this problem, so will have a go, when time permits. – John Powell Nov 27 '14 at 14:06
  • Is this question now closed, then? I was going to suggest a query using ST_AddPoint to snap to nearest lines, extending the blog article approach, above. – John Powell Nov 29 '14 at 15:19
  • Yah I'm still having problems, buts beyond the scope of this question, so I might start another. Added another update with the method I used to also insert a point on the nearby line. – Canadaka Nov 30 '14 at 1:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.