A Range Rover with Illinois license plate “0” is seen moving 40 MPH in a 30 MPH zone through a red light at Ashland and Cortland.
I’m just seeing who’s driving around Chicago one night, using the Tribune-published dataset of over 4.1 million tickets issued from red light cameras.
The City of Chicago has installed at least 340 red light cameras since the mid-2000s to reduce the number of people running red lights and crashing. They’re supposed to be installed at intersections where there’s a higher-than-average rate of right angle (“T-bone”) crashes, which are more injurious than other typical intersection crash types.
Assessing safety wasn’t the Tribune’s story angle, though. It was about showing spikes in the number of tickets issued, which I verified to some extent. The article called the tickets issued during these spikes “undeserved” and “unfair”. The data doesn’t have enough information to say whether or not that is the case; a video or extensive photo review is necessary to rule out rolling right turns while the light was red (a much less dangerous maneuver unless people are trying to cross the street).
The first query I ran assessed the number of people who get more than one ticket from a red light camera. Since I was tired my query was a little sloppy and it missed a lot of more useful order choices and didn’t select the right fields. I fell asleep and started again in the morning. This time, I got it right in just two tries – I needed to try again because I mistakenly put HAVING before the GROUP BY clause.
Here’s the first query, in its final form, to retrieve the number of tickets for each license plate in each state (I assumed there may be identical license plates among states).
select max(ticket_number), max(timestamp), license_plate, state, count(*) AS count FROM rlc_tickets group by license_plate, state HAVING count(*) > 1 order by count DESC NULLS LAST
It resulted in 851,538 rows, with each row representing a unique license plate-state combination and the number of red light violations that combination received. You can reasonably assert that cars don’t change license plates more than a couple times in a single person’s ownership, meaning you can also assert that each row represents one automobile.
851,538 vehicles, which make up 35.1% of all violators, have received 2,601,608, or 62.3%, of the 4,174,770 tickets. (There are 2,424,700 license plate-state combinations, using the query below.)
select count(ticket_number) from rlc_tickets group by license_plate, state
Here’re the top 10 vehicles that have received the most violations:
- SCHLARS, IL, 78
- 9720428, IL, 59
- 8919589, IL, 57
- A633520, IL, 52
- 3252TX, IL, 45
- A209445, IL, 44
- N339079, IL, 44
- X870991, IL, 41
- 239099, IL, 41
- 4552985, IL, 40
The next step would be to design a chart to show these vehicles’ activity over the months – did the vehicles’ drivers’ behavior change, decreasing the number of red light violations they received? Did the vehicle owner, perhaps a parent, tell their child to stop running red lights? Or has the vehicle owner appealed erroneously-issued tickets?
When I ran one of the first, mistaken, queries, I got results that put license plate “0” at the top of the list, with only nine tickets (license plates with two or more zeros were listed next).
I googled “license plate 0” and found a 2009 Tribune article which interviewed the Range Rover-driving owner of license plate “0” and the problems he encountered because of it. The City of Chicago parking meter enforcement staff were testing new equipment and used “0” as a test license plate not knowing such that license plate exists. Tom Feddor received real tickets, though.
I then looked up on PhotoNotice the license plate and ticket violation number to find, indeed, the license plate belonged to someone driving a Range Rover at Ashland Avenue and Cortland Street on July 17, 2008. An added bonus was Feddor’s speed in that Range Rover: the camera recorded the car going 40 MPH in a 30 MPH zone.
I was done browsing around for the biggest offenders so next I wondered how many tickets were issued to vehicles licensed in Arizona, where U-Haul registers all of its nationwide vehicles. Arizona plates came in 29th place for the greatest number of tickets.
select count(*) AS count, state from rlc_tickets group by state order by count DESC NULLS LAST
As you may have expected, four surrounding Midwest states, and Ohio, rounded up the top five states after Illinois – but this isn’t notable because most visitors come from there and they each only comprise less than 1.3% of the total tickets. The next state was Florida.
- 3,986,739, IL
- 51,104, WI
- 40,737, MI
- 27,539, IN
- 8,550, OH
- 7,684, MN
- 7,139, FL
What’s next: I’m working on finding a correlation between the number of reported crashes, and type, at intersections with red light cameras and the number of tickets they issued. I started doing that before running the numbers behind this blog post but it got complicated and it takes a long time to geospatially compare over 500,000 crash reports with over 4.1 million red light tickets.
What else do you want to know?
I will delete all comments that don’t discuss the content of this post, including comments that call red light cameras, or this program, a “money grab”.