PgRouting : Shortest path starting from the closest vertex not the closest node

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!