My problem is that I want to get the shortest route between multiple point pairs which are stored in a table (PostGIS).
In PostGIS I also have a network created using pgr_createTopology on a table containing about a 1000 MultiLineString rows. It's my understanding that to calculate routes between points with pg_routing, you need to either accept that the route goes to and from the nearest network node or you need to work around that limitation by adding new nodes to the network which corresponds to the stops you want to calculate on.
To add new nodes to my network I've tried tried to follow the answer from @alpha-beta-soup in this tread
- Split lines into non-overlapping subsets based on points
Before following the answer I used ArcMap to snap the points to the lines. I then imported the two shapefiles (network and radars both with geometry columns "geom") into PostGIS using the PostGIS Shapefile loader (2.1).
When using alpha_beta_soup's code I get a table with all the right columns, but there are zero rows in it, I've tried running only the first part of the code, but I still only get a table with no content. This is the first part of the code:
CREATE TABLE tmp_lineswithstops AS ( WITH subq AS ( SELECT ST_Line_Locate_Point( roads.geom, ST_ClosestPoint(roads.geom, radars.geom) ) AS LR, rank() OVER ( PARTITION BY roads.gid ORDER BY ST_Line_Locate_Point( roads.geom, ST_ClosestPoint(roads.geom, radars.geom) ) ) AS LRRank, ST_ClosestPoint(roads.geom, radars.geom), roads.* FROM network AS roads LEFT OUTER JOIN radars ON ST_Distance(roads.geom, radars.geom) < 0.0001 WHERE ST_Equals(ST_StartPoint(roads.geom), radars.geom) IS false AND ST_Equals(ST_EndPoint(roads.geom), radars.geom) IS false ORDER BY gid, LRRank ) SELECT LR FROM subq );
The two tables look like this:
Does anyone have a clue as to what i'm doing wrong?