I am currently working on a routing solution with PgRouting (Dijkstra's algorithm), but the shortest path it creates always start from the nearest nodes generated from the edge table, and sometimes the distance between the node and the point is too long(4km or more) and it makes my calculations wrong. Is there a way to calculate the shortest path from the closest line vertex to my starting point? I included a picture to clarify more my problem
1 Answer
While still not in the official release, the withPoints
family of functions is in a stable state since pgRouting 2.2 (?) and provides a dynamic interface for temporary nodes in a graph, i.e. routing between arbitrary points.
The key here is the points_sql
where you specify the closest edge to any of your temporary nodes, and the fraction of line length their closest point is located at.
Assuming points_table(id INT, geom GEOMETRY(POINT, 4326))
holds all custom source and target points, you can
either query dynamically, i.e. running
SELECT * FROM pgr_withPoints( '<edges_sql>', 'SELECT pnt.id AS pid, edg.edge_id, edg.fraction FROM points_table AS pnt CROSS JOIN LATERAL ( SELECT <id> AS edge_id, ST_LineLocatePoint(<geom>, pnt.geom) AS fraction FROM <edges_table> ORDER BY <geom> <-> pnt.geom LIMIT 1 ) AS edg', -1, -2, details := TRUE ) ;
would route between
points_table.id
1 & 2 [*]or add and update the required fields to your
points_table
in advance for more performance; the abovepoints_sql
query can be applied for the update
With the details
parameter set to TRUE
, the function treats all dynamic nodes from the points_sql
(i.e. all other than those used as source
& target
) as 'normal' nodes of the graph in the result set; setting to FALSE
would return only edges and vertices of the edge_sql
(but always including start and/or end node if they are of the points_sql
).
[*] for all temporary nodes passed in via points_sql
, the function will use negative pid
values as input as well as in the result set!
-
1Nice explanation! We could add your example to the gRouting documentation.– dkastlOct 27, 2020 at 14:03
-
1@dkastl thx, and by all means, please go ahead if this would add value to the docs!– geozelotOct 27, 2020 at 14:18
-
I really want to thank you for your answer, you opened my eyes on something I had no idea about– IsmailOct 28, 2020 at 9:34
-
@Ismail share our knowledge, learn from each other; it can be so simple and effective! You are very welcome, if you think this answers your question, consider accepting the answer by activating the little tick symbol next to it.– geozelotOct 28, 2020 at 9:42