I have created generated a many-to-many shortest distance route using pgRouting. The output table displays the typical pgRouting outputs (seq, path_seq, edge, node, cost etc.) as well as the road network attributes (gid, road number, road name, function, class etc.).

If I export this table into Excel, I can create a pivot table to view the number of edges traversed for each road name as shown below


I think I can use this script to view number of edges traversed by road name. However, how would I join this back onto the road network, i.e. join onto fixed_Roads where fixed_road.gid = edges?

SELECT name1,
FROM   array_trips

Output example:

"Aberdeen Road" "748"
"Addison Road"  "79"
"Aiken Street"  "53"
"Albert Crescent"   "901"
"Albert Parade" "2"
"Albert Road"   "2911"
  • You can only ever meaningfully aggregate gids per name1 (into an array, or concatenated string), as you are effectively counting them into groups (per name1); is that the desired output?
    – geozelot
    Jul 6 '21 at 13:11
  • Yes, so I am looking at traffic counts which is equivalent to count(edges)/ count(gid). These need to be joined back onto the road network in one query Jul 6 '21 at 13:31
  • Oh wait, you just want the same behavior as with Excel here? Note that, if I'm not mistaken, both counts should always be equal.
    – geozelot
    Jul 6 '21 at 13:39
  • Yes, that is correct. Jul 6 '21 at 14:16

Since I am not entirely sure what your intentions are with respect to aggregation, here just a generic example; I strongly recommend to read the PG docs and some tutorials on basic SQL logic if the mechanics of relational JOINs is the actual question here.

A GROUP BY statement is applied on the full (virtual) set of records and columns that are available after the complete list of FROM expressions are considered, including table JOINs and correlated sub-queries.

In your case, if your intention is to being able to aggregate column values from fixed_Roads, all you have to do is adding a JOIN to it (just like you describe), and apply the grouping (with some random aggregations jsut for demonstration purposes):

SELECT fr.name1,
       COUNT(at.edge) AS full_count,
       COUNT(DISTINCT at.edge) AS unique_count,
       ARRAY_AGG(DISTINCT fr.gid) AS unique_gids,
       ARRAY_AGG(DISTINCT fr.class) AS unique_classes,
       SUM(ST_Length(fr.geom::GEOGRAPHY)) AS total_length_traversed_in_meter
FROM   array_trips AS at
JOIN   "fixed_Roads" AS fr
  ON   fr.gid = at.edge

This assumes all network meta info is pulled from "fixed_Roads", while your question reads as if those values are present in array_trips already; please clarify what columns are found where, and what values you need to aggregate if this doesn't help.

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.