Showing Origins on ARRAY multiple routes - pgRouting

Question

I have routed between multiple points using pgRouting. I have aggregated the total cost of each route (length) into a new table using edge = -1 to be imported into an inverse distance weighting calculation.

Question is: How do I state what the Origin is of each route within the final output table?

the pgr_dijkstra outputs include: seq, path_seq, start_vid, end_vid, node, edge, cost, agg_cost, geom and total_distance (m)

Answer 1

Let's say you want to compute a route from A to B then your start and destination vertex will be in the output as start_vid and end_vid. For the sake of simplicity this query is using some arbitrary network generated with from OpenStreetMap data (North California) with osm2po.

WITH start AS (
  SELECT topo.source -- could also be topo.target
  FROM norcal_2po_4pgr as topo
  ORDER BY topo.geom_way <-> ST_SetSRID(
    ST_MakePoint(-122.407546,37.784482),
  4326)
  LIMIT 1
),
destination AS (
  SELECT topo.source --could also be topo.target
  FROM norcal_2po_4pgr as topo
  ORDER BY topo.geom_way <-> ST_SetSRID(
    ST_MakePoint(-122.207546,37.384482),
  4326)
  LIMIT 1
)
SELECT 
start_vid,
end_vid,
node,
edge
FROM pgr_dijkstra('
  SELECT 
  id,
  source,
  target,
  ST_Length(ST_Transform(geom_way, 3857)) AS cost,
  ST_Length(ST_Transform(geom_way, 3857)) AS reverse_cost
  FROM norcal_2po_4pgr',
  array(SELECT source FROM start),
  array(SELECT source FROM destination),
  directed := true) AS di
JOIN   norcal_2po_4pgr AS pt
  ON   di.edge = pt.id;

This will yield something like this

 start_vid | end_vid |  node   |  edge
 ----------+---------+---------+---------
   1253426 |  115310 | 1253426 | 1836162
   1253426 |  115310 | 1253427 | 1836163
   1253426 |  115310 | 1253428 | 1836164
   1253426 |  115310 |  658246 | 1040812
   1253426 |  115310 | 1118202 | 1040813
   1253426 |  115310 |  658244 | 1040811
   1253426 |  115310 |  658245 | 1836156
   1253426 |  115310 |  641843 | 1017222

You can now use array_agg to collect the values or use something like MAX(start_vid) and MAX(end_vid) to output your origin vertex, so something like

SELECT 
ST_Union(geom_way),
MAX(start_vid),
MAX(end_vid),
array_agg(node),
array_agg(edge)
FROM pgr_dijkstra('
...

In your case you will want to add a group by clause using start/end vid to the query to make sure the individual route information is grouped together.