Tagspeeding

Playing around with Chicago data: who’s running red lights?

Range Rover with Illinois license plate "0"

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:

  1. SCHLARS, IL, 78
  2. 9720428, IL, 59
  3. 8919589, IL, 57
  4. A633520, IL, 52
  5. 3252TX, IL, 45
  6. A209445, IL, 44
  7. N339079, IL, 44
  8. X870991, IL, 41
  9. 239099, IL, 41
  10. 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”.

Why do speeding crashes in Chicago lead to worse injuries?

Don’t git behind me. Photo by Richard Masoner. 

A discussion about Chicagoans’ proclivity for tailgating (on a post about speed cameras) prompted me to look at the prevalence of this in causing crashes. I looked at the three-year period of 2010-2012 first, mainly so the numbers wouldn’t be so large, and left this information in a comment. But considering the prerequisites* for a crash to be reported in this dataset, and my desire to compare two multi-year periods, I switched my analysis to the single four-year period 2009-2012.

2009-2012

Total crashes: 318,193. Total fatalities: 554 people.

Tailgating crashes

62,080 crashes, 19.53% of all crash types

Tailgating crashes, injuries breakdown:

  • Killed: .0012 (this represents the number of deaths per crash). 75 people died in these crashes, representing 13.54% of all deaths.
  • Incapacitating injuries: 8.53% (the average distribution of people’s injuries in all tailgating crashes)
  • Non-Incapacitating: 46.32%
  • Possible injury: 45.15%

The share of all crash types that are tailgating has increased steadily from 18.11% in 2009 to 20.79% in 2012.

Speeding crashes

10,339 crashes, 3.24% of all crash types

Speeding injuries:

  • Killed: .0118 (this represents the number of deaths per crash). 122 people died in these crashes, representing 22.02% of all deaths.
  • Incapacitating injuries: 15.55% (the average distribution of people’s injuries in all speeding crashes)
  • Non-Incapacitating: 51.95%
  • Possible injury: 32.50%

The share of all crash types that are tailgating has decreased slightly from 3.72% in 2009 to 3.02% in 2012. While speeding leads to fewer crashes, it leads to a greater incidence of death and serious injury. The probability of a speeding crash leading to at least one death seems to stay steady through the period while the probability of seeing a person with an incapacitating injury versus a different kind of injury varies more, but not so much in a range that overlaps the rates for tailgating crashes.

A future comparison at injuries should look at the top crash causes for death and serious injury.

N/A and Unable to determine crashes

237,729 crashes, 74.71% of all crash types

N/A and unable to determine injuries:

  • Killed: .0013 (this represents the number of deaths per crash). 305 people died in these crashes, representing 55.05% of all deaths.
  • Incapacitating injuries: 9.38% (the average distribution of people’s injuries in all N/A crashes)
  • Non-Incapacitating: 48.26%
  • Possible injury: 42.35%

Notes

Updated December 4, 2013

I updated the wording on how to interpret these numbers. For example, previously for “killed” there was a percentage saying this number represented the amount of crashes that had at least one death. This wasn’t accurate: the same number represents a rate of deaths per crash of that type. Injury percentages represent the distribution of injury types experienced by all the people injured in crashes of that type.

Reliability

Analyzing crash causes is not very reliable as 45.60% of the reported crashes in 2012 had “N/A” or “unable to determine” listed as the primary cause! The third and fourth most frequently ascribed causes were the two tailgating codes (described below). There are some crashes that had the one of these two causes in the secondary cause field but I haven’t calculated that.

Cause code descriptions

Each crash has two cause codes. For tailgating crashes I searched for reports where “failing to reduce speed to avoid crash” or “following too closely” in either the primary or secondary cause field (it’s possible that a report had both of these causes ascribed). For speeding crashes I searched for “speed excessive for conditions” or “exceeding speed limit” in either the primary or secondary cause fields.

Prerequisites

This data excludes crashes where there was no injury or no property damage greater than $500 (2005 to 2008) and $1,500 (2009 to 2012). You cannot compare the two datasets when you want to see a share of all crashes because the number of “all crashes” will be underreported in the second dataset.

Queries

These are some of the MySQL queries I used to get the data out of my own crash database (I’m figuring out ways to make it public, using a shared login). “Cause 1 code” indicates the primary cause of the crash according to the police officer’s judgement. “Cause 2 code” indicates the secondary cause of the crash according to the police officer’s judgement.

1. Crash cause reliability: SELECt count(casenumber), sum(`Total killed`), `Cause2`, `Cause 2 code` FROM `CrashExtract_Chicago` WHERE year = 12 GROUP BY `Cause 2 code`  ORDER BY cast(`Cause 2 code` as signed)

2. Speeding crashes: SELECT count(casenumber), sum(`Total killed`), sum(`totalInjuries`), sum(`A injuries`), sum(`B injuries`), sum(`C injuries`) FROM `CrashExtract_Chicago` WHERE (`Cause 1 code` = 1 OR `Cause 1 code` = 27 OR `Cause 2 code` = 1 or `Cause 2 code` = 27) AND year > 8

3. Tailgating crashes: SELECT count(casenumber), sum(`Total killed`), sum(`totalInjuries`), sum(`A injuries`), sum(`B injuries`), sum(`C injuries`) FROM `CrashExtract_Chicago` WHERE (`Cause 1 code` = 3 OR `Cause 1 code` = 28 OR `Cause 2 code` = 3 or `Cause 2 code` = 28) AND year > 8

4. N/A and Unable to determine crashes: SELECT count(casenumber), sum(`Total killed`), sum(`totalInjuries`), sum(`A injuries`), sum(`B injuries`), sum(`C injuries`) FROM `CrashExtract_Chicago` WHERE (`Cause 1 code` = 18 OR `Cause 1 code` = 99) AND year > 8

Revealing driver behavior on Clark Street with a radar gun

People prefer to cross Clark Street at Menomenee Street in groups of unacquainted individuals.

This is a more detailed post of the one at Streetsblog Chicago.

On the overcast morning of Friday, May 4, 2012, I recorded the speeds of 412 cars at four locations along Clark Street in Old Town and Lincoln Park for 15 minutes at each location. I missed counting the speeds of 42 cars. The embedded map shows the locations and some basic statistics.

What did I find? There’s a relationship between street width and the speed people drive. The highest speeds were found on the widest portions, and the lowest speeds on the narrowest portions. However, this basic study is far from scientific. A better study would record the locations simultaneously (necessitating 4 radar guns), calibrated equipment, consistent training for the researchers on data collection methods, a longer recording duration, and comparison to a control street that had a uniform width at four locations.


View Radar gun places on Clark Street in a larger map

1. Southbound Clark Street at Germania Place

My assistant and I set up the radar gun and camera immediately south of Sandburg Terrace and pointed the radar gun at people driving southbound on Clark Street between a row of parked cars at the concrete median (pedestrian refuge island). Classes would start soon at the Latin School on the east side of Clark Street. Compliance with state law requiring drivers to stop for pedestrians in the crosswalk was weak, to say the least, but compliance wasn’t explicitly measured.

  • Average speed: 17.21 miles per hour (MPH)
  • Maximum speed: 30 MPH
  • Cars measured: 151
  • Speed limit: 30 MPH
  • Drivers exceeding the speed limit: 0
  • Width: 224 inches (from west curb to pedestrian refuge island)
  • Effective width: 140 inches (excludes parking by subtracting 7 feet)
  • Crashes: 35, of which 4 were bicycle, and 3 were pedestrian.

Only one car-car crash (actually a 3 car crash) produced an injury. What’s interesting about this location is that in a lot of the crashes, the cars were traveling in the same direction. There’s a lot of school drop off and pick up activity here for Latin School of Chicago students, so it could be that many people are pulling away from the curb to merge into traffic and collide.

2. Northbound Clark Street at Menomenee Street

  • Average speed: 30.83 miles per hour (MPH)
  • Maximum speed: 50 MPH
  • Cars measured: 121
  • Speed limit: 30 MPH
  • Drivers exceeding the speed limit: 53.72%
  • Width: 395 inches (from east curb to dividing line). This includes the parking lane but no cars were parked within 50 feet, north and south, of the measurement location.
  • Crashes: 20, of which 2 were bicycle, and 1 were pedestrian. Many of the non-bike and non-ped crashes involved a parked car or taxi. The only injuries experienced were by the 2 cyclists and 1 pedestrian.

3. Northbound Clark Street at Lincoln Park West

We stood on the “pie” (traffic island) that separates northbound Clark Street traffic from northbound Lincoln Park West traffic to measure the traffic driving on Clark Street between the pie and the concrete median separating it from southbound Clark Street.

  • Average speed: 25.60 miles per hour (MPH)
  • Maximum speed: 40 MPH
  • Cars measured: 58
  • Speed limit: 30 MPH
  • Drivers exceeding the speed limit: 27.59%
  • Width: 252 inches (from concrete median curb to west curb on the pie)
  • Crashes: 4, of which 1 was bicycle, and 2 were pedestrian.

4. Northbound Clark Street between Lincoln Park West and Dickens Avenue

This location is 125 feet north of the previous location.

  • Average speed: 22.54 miles per hour (MPH)
  • Maximum speed: 35 MPH
  • Cars measured: 58
  • Speed limit: 30 MPH
  • Drivers exceeding the speed limit: 2.44%
  • Width: 264 inches (from east curb to dividing line).
  • Effective width: 180 inches (excludes parking by subtracting 7 feet)
  • Crashes: 0

Me measuring speeding drivers on Clark Street with the speed gun, my clipboard and paper, and a GoPro camera to record the speeding drivers and the results on the speed gun. 

Bike Walk Lincoln Park’s proposal

In 2011, Michelle Stenzel and Michael of Bike Walk Lincoln Park published a document to “Make Clark a Liveable Street“. The first two pages show an aerial photo of the same section of Clark Street where I measured automobile speeds, North Avenue and Armitage Avenue. On the first page, existing conditions are laid out. The second graphic shows proposed improvements.

At Menomonee Street, measurement location 2, the document says “pedestrians must cross 6 lanes with no safe haven”, a width of just under 66 feet. In the later pages, the first existing condition is blatant: “Wide lanes of auto traffic moving at speeds in excess of the speed limit”. My analysis in May demonstrates this.

How does BikeWalk Lincoln Park propose to “transform this stretch from a car-oriented ‘super-highway’ to a people-oriented liveable street”? By installing protected bike lanes, putting the street on a diet, and installing new and well-marked crosswalks among other ideas.

Width and speed summary

Ordered by location:

  1. 224/140 inches. 0% of drivers exceeded 30 MPH speed limit
  2. 395/395 inches. 53.72% of drivers exceeded 30 MPH speed limit
  3. 252/252 inches. 27.59% of drivers exceeded 30 MPH speed limit
  4. 264/180 inches. 2.44% of drivers exceeded 30 MPH speed limit

Ordered from narrowest to widest to see how width relates to speed:

  • 224/140 inches. 0% of drivers exceeded 30 MPH speed limit
  • 264/180 inches. 2.44% of drivers exceeded 30 MPH speed limit
  • 252/252 inches. 27.59% of drivers exceeded 30 MPH speed limit
  • 395/395 inches. 53.72% of drivers exceeded 30 MPH speed limit

Notes

Crash data is within 100 feet to avoid the overlap of the final two locations, which were 125 feet apart. Crash data comes from the Illinois Department of Transportation for 2005-2010. The Bushnell Velocity Speed Gun was borrowed for this analysis. The radar gun was filmed to show a speeding car and its speed simultaneously. The video below shows a driver traveling at 50 MPH in a Children’s Safety Zone (as it’s within 1/8 mile of a park, Lincoln Park, making it eligible for automated speed enforcement).

Curiously, no traffic counts have been collected on Clark Street near any of the count locations.

View the video on Vimeo.

Screenshot of traffic count website. Go to the Traffic Count Database System and search for “1700 N Clark Street, Chicago, IL” in the map. 

An enjoyable Friday morning collecting car speeds on Clark Street

Watch this 7 second video of a person driving a late model Toyota Camry in Lincoln Park at 50 MPH, next to the park. 

mean 30.83 mph
median 31
mode 30
min 17
max 50
frequency: 121 cars
greater than 30 mph: 65 cars
% greater than 30 mph: 53.72%

Statistics exclude the three buses counted at 26, 18, 20. Time was 8:23 to 8:38 on Friday, May 4, 2012, at Clark Street and Menomenee Street. The street width at where I collected the data is 65’9″ (789 inches). This location is eligible for a speed camera as it is within 1/8 mile of a park and is thus a “Children’s Safety Zone”.

I’m still working on the report for an article to be published on Grid Chicago. I used this Bushnell Velocity Speed Gun.

© 2019 Steven Can Plan

Theme by Anders NorénUp ↑