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.
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 FROM 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_LineMerge( ST_Union( 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) ))