Sam Jacoby

In this project, I looked at the freeway digital sign alerts in Iowa charted against statewide accident totals, both obtained via FOIA requests to the Iowa DOT.

Source: Iowa DOT

If you drive in Iowa—on the interstate, at least—you can’t miss the highway alert signs. I remember seeing these growing up in LA. They were usually switched off or had a nursery rhyme reminding you to buckle up.. I never once thought, “what a missed opportunity!”

They are not such dull thinkers in Iowa. Here, in the famously mild-mannered midwest, the highway signs have real sass. When we arrived and began our daily commute (Iowa City to Cedar Rapids), we immediately noticed the signs.

I often wonder about the Iowa DOT official in charge of the weekly sign change.

A photo posted by Madison (@melizbre) on Jan 26, 2015 at 6:00am PST

“IN THE BLINK OF AN EYE, YOU COULD DIE.” Good morning to you, too. These signs alternate with a sobering daily ticker — “34 traffic deaths so far this year.” Driving is serious stuff. That aside, the signs bring some humor and a note of (morbid) grace to the gray morning commute.

I decided to learn more. I wanted all the signs.

The IowaDOT has extensive resources online, including an impressive interactive map of the state’s roadways, not to mention a surprisingly comprehensive staff phone book. That said, they’re much more interested in relaying information about current traffic conditions than historical ones.

I got in touch with a few friendly folks out there—muckraking this ain’t—and after after some back and forth, they encouraged me to file a FOIA request. With the friendly help of the FOIA Machine and the USA I learned that this was a codeword for “email with the magic words ‘FOIA request’ in it.” Done. I immediately received a vacation auto-responder. Not bad. Not great.

Nonetheless, about ten days later, an an EXCEL file with 82 weeks worth of sign messages and dates was gleaming in my inbox. What I didn’t have, though, was any numerical data. Another request turned that around quickly—traffic data is easy, because it’s clear that agencies monitor this stuff very closely themselves. The trick, I suppose, is to get at the data that people don’t want to know. At any rate, another lovely EXCEL file in hand, I summed by weeks (not knowing the way to do this right in Excel, I generated the appropriate sum formulas, =SUM(B7:B13) in Python and pasted em’ in).

1
2
3
4
5
6
7
8

#!/usr/bin/env python
# print =SUM(BX:BX+n)
start = 8
interval = 6
for i in range(52):
    end = start + interval
    print "=SUM(B%d:B%d)" % (start, end)  
    start += interval + 1

Ha! I felt brilliant and stupid all at once. Honestly, I find it a lot more readable than the cryptic offset(…) Excel soutions, and easier than doing it all in Python. Numbers so summed, I turned to d3.js, the much beloved visual library. I learned a few things in getting up to speed. Having spent quite a bit of time working with jQuery, it was a pleasure to use a library that offered a bit more out-of-the-box functionality.

For those looking to get into gear, the basic beginning tutorials on d3js.org—are really useful. The list of tutorials here is hit or miss, but there are some great ones too. I’d recommending reading Bostock’s write-ups on joins more than anything else, as that explains the basics of the whole chaining syntax. I struggled somewhat with the scales, as well. It’s just the kind’ve thing that takes a little banging.

It’s interesting how rapidly patterns become clear. Traffic accidents spike immediately before Thanksgiving.

The full CSV is available for download here.