On August 26th, the Alex Bordens and Alex Richards from the TribApps team talked about how the Tribune analyzed more than 4 million tickets and created an interactive database to help readers find out if they were ticketed during a spike.
Chicago Red Light Camera, photo by Flickr user Tripp
The Chicago Tribune recently found that the City’s red light camera program may have been ticketing thousands of drivers unfairly during suspicious spikes
Because there were so many problems in the way the red light contracted was awarded, the Chicago Tribune team wanted to see if the red light cameras were working as advertised.The team first asked the city for the ticket data from Xerox who was the vendor. The city initially said no, that the request was too burdensome. After several months of pushing, they finally got Xerox to gave the data. Initially, it was just the citation number, data and time it was issued, the tag number, and the vehicle type. In a separate file, the city gave them information on any of the tickets that had been challenged and the result of that.
The team took the initial text dump (about four million records) and poured that into a mysql database. After some sorting, they found some useful tidbits. For example, they found one resident who had received sixty red light tickets. However, it didn’t really answer the question. After sorting the data by day, the team noticed large spikes in the data at certain intersections.
And it wasn’t just one intersection on one day. The spikes occurred at different locations on different days and then suddenly went back to normal levels. After publishing the story, the city announced that they were reviewing all the tickets who were impacted by the spikes.
In looking at the results of appeals, drivers who appealed their tickets that were part of the spike had a much higher rate of getting those tickets thrown out. So far, there hasn’t been any explanation as to why the cameras were spiking.
The application itself uses the D3 programming language to display the data. They store the data internally on an open source platform called PANDA. (Which was a 2011 Knight News Challenge Winner.) PANDA was designed for news room to make it easy for the developer team to share data and information with the entire newsroom.
In addition to building their web app that lets users explore the data, they’ve also made the data available to download in a zip file.
You can catch their entire presentation to hack night below:
You can find the full story about the Tribune’s investigation on the Tribune webpage. The TribApps Team also contributes to the civic innovation community through their open source offering including the Tarbell platform.
