How to count number of attributes and then link back to road network - pgrouting

Question

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,
       count(edge)
FROM   array_trips
GROUP BY
       name1
;

Output example:

"Aberdeen Road" "748"
"Addison Road"  "79"
"Aiken Street"  "53"
"Albert Crescent"   "901"
"Albert Parade" "2"
"Albert Road"   "2911"

Answer 1

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
GROUP BY
       fr.name1
;

---

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.