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!

5 thoughts on “Fun with Open Data and Excel: Edmonton’s Busiest Streets

  1. Collision data is collected by the city but ultimately its release is governed by the province. The Traffic Safety Act prohibits the release of collision information in a manner that would make it easy or interesting to mash up. However, there is an upcoming annual report of collisions that will feature a more data-friendly release. Look for it in March.

  2. Hey Mac, planning student here from four years into the future. Just wondering if you still have that data, or if you know anything about the city releasing up to date traffic data in their new open data catalogue. Cheers

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s