5

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

enter image description here

1 Answer 1

11

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 above points_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!

4
  • 1
    Nice explanation! We could add your example to the gRouting documentation.
    – dkastl
    Oct 27, 2020 at 14:03
  • 1
    @dkastl thx, and by all means, please go ahead if this would add value to the docs!
    – geozelot
    Oct 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
    – Ismail
    Oct 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.
    – geozelot
    Oct 28, 2020 at 9:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.