Fun with Open Data and Excel: Edmonton’s Busiest Streets

Today the City of Edmonton added a new dataset to the open data catalogue – average daily street traffic volumes from 2004-2009. Here’s the description:

Include traffic counts obtained with automatic traffic recorders over the past six years. The volume shown is a total of all vehicles in both directions, over a 24 hour period, for a typical weekday in a year. Volumes shown as a dash "-" indicate no count was undertaken that year. Seasonal adjustments are made to reflect an average weekday during the year. An increase or decrease in volume does not necessarily indicate a trend for a given roadway. Road construction or the introduction of more advanced equipment in 2009 affect the traffic volume numbers.

I’d like to build something interesting with this data, perhaps as part of a future ShareEdmonton release. But to start, I decided to open the data up in Microsoft Excel, something I often do with new datasets. Here’s what I discovered.

First, a few summary points:

• There are 1496 street locations in the dataset.
• Of those, 823 were counted in 2009. The year with the most counted locations was 2006, at 1076. Just 32 locations were counted every year (2004-2009). There were 531 locations counted in three years or more.
• One of the first things I noticed is that every count ends in zero. I know the description outlines that the data is an average, adjusted seasonally, and perhaps for other reasons, but I wonder how close to reality the numbers really are.

Looking at 2009 simply because it is the most recent, there’s a hint of a long tail for the counted locations:

Here are the 200 busiest locations in Edmonton according to the 2009 count:

To create that map, I used a free utility called Excel to KML. There are all kinds of useful free KML tools online!

Here are the top ten busiest locations in Edmonton, according to the 2009 count:

1. Calgary Trail SW North of Gateway Park Road SW
2. Capilano Bridge
3. 178 Street South of 81 Avenue
4. Highway 216 NE North of Sherwood Park Freeway NE
5. Anthony Henday Drive North of  87 Avenue
6. HIghway 216 NE North of Baseline Road NE
7. Anthony Henday Drive West of Calgary Trail
8. 170 Street North of  95 Avenue
9. Groat Road North of Victoria Park Road
10. Highway 216 NE North of Whitemud Drive NE

Some of those are expected, some perhaps not. I decided to look at locations that were counted in at least three different years. If you look at the average daily volumes for that subset, here are the top ten busiest locations in Edmonton:

1. Quesnell Bridge
2. Yellowhead Trail West of 231 Street
3. Calgary Trail SW North of Gateway Park Road SW
4. Capilano Bridge
5. Whitemud Drive West of 149 Street
6. Yellowhead Trail West of Fort Road
7. Cloverbar Bridge
8. Yellowhead Trail West of  97 Street
9. Yellowhead Trail West of 107 Street
10. Yellowhead Trail West of 127 Street

Plotting the top 25 on a map gives a better sense of the really busy roads – Yellowhead Trail and Whitemud Drive:

Interesting, isn’t it? Of course, it could get a lot more interesting with some mashups. I’d love to have collision data for the City of Edmonton, to see how closely the number of collisions is correlated with traffic volumes. That’s just one example.

Check out the new dataset in the open data catalogue. If you do something interesting with it, I’d love to hear about it!