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!

Sign of progress

The new South LRT extension has been the hot topic at water coolers throughout the city this week:

Edmonton Sun

Edmonton Journal

CBC

When was the last time that so many Edmontonians were talking about the LRT? Nothing like a little controversy to bring a topic to the forefront. I’ve almost heard more people talking about the delays at 51 Avenue than about the downtown arena! And you know what they say, there’s no such thing as bad press…

Yes it sucks to wait at a traffic light. The City will get the issues sorted out though, I’m sure of that. In the meantime, we should capitalize on the discussions that are happening! Explain to Edmontonians that we’re forging ahead with new LRT extensions and that we’ll learn from this experience.

And wouldn’t you know it, since I started writing this post I received a notice that Bob Boutilier, GM Transportation at the City of Edmonton, will be addressing strategies and challenges associated with fast tracking the NAIT, southeast, and west LRT lines at a news conference tomorrow morning. Excellent!

How much do traffic signs cost?

I read with great interest this week about the City of Edmonton’s new residential speed reduction pilot. Speed limits have been on my radar since late last year when Patricia Grell of the Woodcroft community started her Safe Speed Limits blog. She and many others have been pushing for a reduction to 30km/h on residential streets. The pilot goes half way, to 40km/h, and will take place in six Edmonton neighbourhoods: Woodcroft, Beverley Heights, Ottewell, King Edward Park, Westridge/Wolf Willow and Twin Brooks.

Those communities were selected based on “the extent of the speeding problem” as well as traffic volume, the number of playgrounds and schools, etc. The City consulted with the Edmonton Federation of Community Leagues to identify community leagues that would be willing to participate. EFCL Executive Director Allan Bolstad told me that community leagues will act as the “window into the neighbourhoods”, both to help inform and educate, as well as gather feedback on how well the program is working. He said the community leagues will meet mid-March to start implementation, and will continue to meet regularly to evaluate.

The City of Edmonton already has traffic safety programs of course, and they will be integrated into the pilot. Specifically, Speed Watch (which shows drivers their speed), Neighbourhood Pace Cars (vehicles that act as mobile speed bumps), and Safe Speed Community Vans will all be used. Dan Jones from the City’s Office of Traffic Safety said there will also be digital readout speed trailers (like the ones you see at construction sites) and of course, new traffic signs.

He also confirmed that the projected cost for the pilot is $100,000 per neighbourhood. I’m in favor of reducing speed limits, if only so that police officers can ticket people at 50km/h instead of the current 60km/h, but when I heard that figure I thought it sounded rather expensive. Allan Bolstad said he too was “puzzled” by the amount. If I understand things correctly, only the signs are new – the other programs already exist and presumably already have the appropriate funding. Which begs the question – how much do traffic signs cost?

To find out, I talked to Rick MacAdams from Edmonton-based hi signs. They manufacture a wide range of signs, including the speed limit signs you’d see around town. Their speed limit sign, the RB-1, comes in two versions: one with a high intensity reflective film and one with a “diamond grade” reflective film (both films are 3M products). The first costs $76.70 per sign while the diamond grade one costs $109.38. That’s if you’re buying one or two signs; there are discounts for large volume orders, of course.

Next question – how many signs are required in each neighbourhood? I decided to go to Google Maps, to count the number of straight street segments in a couple of the neighbourhoods. I took that number, and multiplied it by two (so we have signs for each direction). The range I came up with was between 60 and 120 signs per neighbourhood. You can probably do the math, but at 120 signs per neighbourhood, using the highest price per sign, the total comes to $13,125.60 per neighbourhood. So a grand total for the pilot of $78,753.60. Nowhere close to the $100,000 per neighbourhood that has been projected!

Now this back-of-the-napkin analysis leaves a number of things out. For one, the time and cost required to have crews post the signs in each neighbourhood. For another, the cost of the digital speed readout trailers. There will also likely be marketing costs. But it also leaves out the fact that the City of Edmonton has its own sign creation department, so the cost per sign is probably far less than what hi signs would charge. And my analysis probably significantly overestimates the number of signs required for each neighbourhood.

So I’m left happy but confused and maybe even a little alarmed. Happy that the City has heard residents and is testing residential speed limit reductions to see if it improves community safety. Confused because I can’t imagine why this pilot will cost $600,000.

Freezing Cold & Lots of Snow

Temperatures have been icy cold today here in Edmonton. When I went to work it was minus 30, with a wind chill of minus 46. And it got worse during the day! I had to shovel my way from the house to my car, thanks to the crazy snow drifts.

This graph shows the temperature here in Edmonton since midnight. Nasty isn’t it?

The Edmonton Journal is reporting that there were 106 crashes causing property damage today. There were only 4 collisions causing injury however, compared with 8 a week ago.

109th Street Outside the office Where I usually park

We weren’t the only ones experiencing the cold today. Calgary hit minus 49 with the wind chill this morning, and indeed most of Alberta experienced similar temperatures. I heard that St. Albert was minus 62 with the wind chill this morning. Even Yellowknife, where my parents live, was colder than normal today with temperatures of around minus 40 and closer to minus 50 with the wind chill.

I’m working from home tomorrow. It took forever to get to work today, and when I did I almost got stuck! My co-workers ended up turning around and going home. I don’t expect conditions will improve much overnight. City officials claim it’ll be more of the same until later this week. For everyone using transit, officials say buses are running up to 30 minutes late, which means prepare for an hour. Express buses are making more stops than normal, however. Garbage collection is also going to be delayed, apparently.

For everyone in the Edmonton area, here are some sites you may want to keep an eye on:

Stay warm and drive safe!

Thoughts on Digg Podcasting

Post ImageOver at Geek News Central today Todd Cochrane had some harsh words for Digg’s newest feature, their podcast portal. Most of his argument is based on the traffic he apparently isn’t receiving from Digg:

Lately though I have come to the conclusion that for all the traffic Digg gets very little if any of that traffic in the way of downloads or pure referals [sic] comes from that site.

He goes on to offer some advice to podcasters:

My advice to podcasters is this, look at the directories you are listed in and figure out if they are doing anything to build your audience or giving you equal exposure on the front of their respective websites. If they are not find sites that are and support them in your shows.

That plan of attack might have worked when podcasting was just getting started, but we’re beyond that now. I would suggest that podcasters do in fact add themselves into Digg’s directory, flawed as it might be. Why pick one directory over another? The idea isn’t to play favorites, it’s to help the audience find what they want, wherever they might be looking. There’s more to being in a directory than just getting listed on the front page.

As for Digg’s podcast portal, here are my thoughts:

  • The way you add a podcast into the directory sucks. Sometimes it works, sometimes it doesn’t, and the feedback you get is really unhelpful.
  • Only iTunes-compatible feeds may be added into the directory. Why Digg felt the need to perpetuate Apple’s hegemony is beyond me.
  • It would be better if episodes had a “front page” as well, instead of just podcasts. Right now you can only look at episodes for a particular podcast.

The podcast section of Digg hasn’t been around very long, so I’m pretty sure they’ll be making changes over time. There’s definitely room for improvement, but the directory is not useless.

Read: Geek News Central

Blog Herald Sold

Post ImageI guess selling blogs isn’t as surprising nowadays as it used to be, but I was still a little shocked when I found out earlier today that Duncan Riley’s popular Blog Herald had been sold. The word on the street is that the blog sold for around $70,000 USD. From Jeremy Wright:

Why did Duncan sell it? I’ll let him give the full reasons, but the biggest and best were that he was no longer enjoying writing it as much as he used to, and that there was a perceived conflict of interest with a blog that was in a blog network reporting on blog networks.

Duncan’s been considering this move for a while now, but could never get enough interest up with the people he was talking to to make it worth his while. I told him I’d help out, broker the deal and take some of the stress off his shoulders. It’s always hardest to sell something you care deeply about (I know, having been there), so we both felt having someone who wasn’t directly involved with it doing the selling would be best (ie: me).

My congratulations to Duncan and my best wishes for the future of the site. I hope he gets what he wants out of it!

I remember a little over a year ago when I was doing BlogosphereRadio.com, the Blog Herald was one of my primary sources of information. It takes a lot of hard work to consistently post the most up-to-date news and analysis, so I have great respect for Duncan. I haven’t frequented the site as often lately (though I remain subscribed) mainly because my attention has turned to podcasting. I hope the new owner doesn’t destroy everything Duncan has accomplished thus far.

Do I think it’s worth $75K? Not so sure on that. I guess if the blog has the traffic – the right number of eyeballs – you could justify the price. The big question I’d have if I was the buyer is, how can I see a return on this investment?

I guess time will tell.

Read: Blog Herald

Google Analytics Very Slow!

Post ImageI’m not exactly sure when Google released their new Analytics service, but it was recently. Apart from being the cleanest looking of all the various Google offerings, it looks like one of the most useful services too. Who doesn’t want to know more information on their website traffic? Here’s what Analytics is all about:

Google Analytics tells you everything you want to know about how your visitors found you and how they interact with your site. You’ll be able to focus your marketing resources on campaigns and initiatives that deliver ROI, and improve your site to convert more visitors.

Unfortunately, I haven’t really been able to evaluate the service! I added their tracking code two days ago, and my account still says waiting for data. A quick blog search reveals that lots of people have encountered the same problem. How long is it supposed to take?

After you first install the tracking code, it generally takes 24 hours for report data to appear in your account. Google Analytics generally updates your reports every 24 hours.

Well I’m clearly past that 24 hours, and I’m still waiting. I just hope the data is up to date once it starts showing up. More later.

Read: Google Analytics