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 ;
"Aberdeen Road" "748" "Addison Road" "79" "Aiken Street" "53" "Albert Crescent" "901" "Albert Parade" "2" "Albert Road" "2911"