Join edge table on pgRouting trsp()

Question

I am trying to figure out why pgr_trsp() isn't giving me the interpolated route (start and end positions are the full line instead of somewhere along that line).

I believe it has to do with the way I a joining on the edge table. My theory is that when I join on the edge table it uses the geometry stored in this table which doesn't have substrings of the line and so the whole line is selected.

Below is a screenshot of what I am trying to get.

With the pgRouting plugin option trsp(edge), it provides the red line as shown but I am getting the blue line.

Code below:

SELECT st_linemerge(edgeTable.geom_way) FROM pgr_trsp('SELECT id, source, target, cost FROM edgeTable', 
(SELECT id FROM origin),
.5,
(SELECT id FROM destination),
.5,
false, false) AS shortestPath
JOIN edgeTable ON shortestPath.id2 = edgeTable.id;

Can anyone tell me how to join this properly so I can use the results from the function?

Answer 1

The pgRouting functions in general don´t return geometries, only references to the input tables. Using pgr_trsp doesn´t create any new edges for you (which would be necessary to get those partial geometries), it calculates 'only' routes. You can join those tables all you want (the way you did it is the right one, usually), you´ll only get the original geometries. It´s actually a little annoying to get those partial edges as result.

This is one of several ways to get it done (I wrote this out of my head, didn´t test for syntax or against test data; structure should be fine, though):

WITH
  start_pt AS (
    SELECT ST_LineInterpolatePoint(b.geom, 0.5) AS geom
    FROM origin AS a
    JOIN edgesTable AS b
      ON a.id = b.id
  ),
  end_pt AS (
    SELECT ST_LineInterpolatePoint(b.geom, 0.5) AS geom
    FROM destination AS a
    JOIN edgesTable AS b
      ON a.id = b.id
  ),
  route AS (
    SELECT *
    FROM pgr_trsp(
           'SELECT id,
                   source,
                   target,
                   cost
            FROM edgeTable', 
           (SELECT id FROM origin),
           0.5,
           (SELECT id FROM destination),
           0.5,
           false,
           false
         )
  ),
  route_edges AS (
    SELECT edges.id,
           edges.geom
    FROM edgeTable AS a
    JOIN route AS b
      ON a.id = b.id
  ),
  route_edges_union AS (
    SELECT ST_LineMerge(
             ST_Union(geom)
           ) AS geom
    FROM route_edges
  ),
  route_edges_union_sub AS (
    SELECT ST_LineSubstring(
             a.geom,
             ST_LineLocatePoint(
               a.geom, s.geom
             ),
             ST_LineLocatePoint(
              a.geom, e.geom
             )
           ) AS geom
    FROM route_edges_union AS a,
         start_pt AS s,
         end_pt AS e
  )
SELECT a.id,
       ST_Intersection(a.geom, b.geom) AS geom
FROM route_edges AS a
JOIN route_edges_union_sub AS b
  ON ST_Intersects(a.geom, b.geom)

Now, this is ridiculously long; that's partially due to writing that query in a step-by-step fashion using CTEs to make it easier to comprehend, but this is also simply a non-trivial thing to do.

What I´m doing here is

• getting the route via pgr_tsrp into route
• getting the corresponding edges into route_edges
• creating a substring of the merged LINESTRING from those edges (built in route_edges_union) using the created start and end points from start_pt and end_pt (interpolated POINTs on the respective edges at 0.5 distance along the line) into route_edges_union_sub
• and eventually find the intersection of route_edges with the line substring in route_edges_union_sub to actually be able to extract only the parts you are interested in, as original edge geometries (skip this last part if a single linestring without references is all you need).

There are other ways, maybe even some better performing, but none much less circuitous in plain SQL (I think) as of yet. The QGIS plugin (see function code here) for example achieves this by individually splitting the start and end edge and union those with the remaining edges of the route, which, in practise, is equally annoying...

Much of the above is already performed (and implemented very efficiently) into pgr_trsp to actually calculate the distances on partial edges, so doing this doubles (or rather worse) the effort. Unfortunately, there is no way to retrieve any of those interals from the function.

---

In case you haven´t already, update to pgRouting 2.6 and use the (still unofficial but stable) pgr_withPoints function.