# Showing Origins on ARRAY multiple routes - pgRouting

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)

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.

• Thankyou so much! Commented May 27, 2022 at 10:25