2

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)

1 Answer 1

2

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.

1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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