Category: Transportation

Using Google Refine to get the stories out of your data

Let’s say you’re perusing the 309,425 crash reports for automobile crashes in Chicago from 2007 to 2009 and you want to know a few things quickly.

Like how many REAR END crashes there were in January 2007 that had more than 1 injury in the report. With Google Refine, you could do that in about 60 seconds. You just need to know which “facets” to setup.

By the way, there are 90 crash reports meeting those criteria. Look at the screenshot below for how to set that up.

Facets to choose to filter the data

  1. Get your January facet
  2. Add your 2007 facet
  3. Select the collision type of “REAR END” facet
  4. Choose to include all the reports where injury is greater than 1 (click “include” next to each number higher than 1)

After we do this, we can quickly create a map using another Google tool, Fusion Tables.

Make a map

  1. Click Export… and select “Comma-separated value.” The file will download. (Make sure your latitude and longitude columns are called latitude and longitude instead of XCOORD and YCOORD or sometimes Fusion Tables will choke on the location and try to geocode your records, which is redundant.)
  2. Go to Google Fusion Tables and click New Table>Import Table and select your file.
  3. Give the new table a descriptive title, like “January 2007 rear end crashes with more than 1 injury”
  4. In the table view, click Visualize>Map.
  5. BAM!

I completed all the tasks on this page in under 5 minutes and then spent 5 more minutes writing this blog. “The power of Google.”

The stories the IDOT automobile crash data can tell

Lots of stuff to learn from the 2007-2009 car crash data, with 309,425 crash reports.

What I noticed

  • SUV drivers do not cause all crashes, contrary to some stereotypes. That vehicle type (for both vehicle 1 and vehicle 2) is only involved in 45,776 crashes (14.79% of all crash reports)
  • The vehicle type most commonly involved in crashes is “personal passenger,” with 294,941 “personal passenger” vehicles involved in 208,888 crashes (67.51% of all crash reports)
  • 1,465 crash reports report “unknown” for both vehicle 1 type and vehicle 2 type (0.47% of all crash reports)
  • 61,645 crash reports have vehicle 1 hitting a parked car (19.92% of all crash reports)
  • There were 16,554 taxis involved in 15,223 crashes (4.92% of all crash reports). And there were 1,311 “taxi on taxi” crashes (0.42% of all crash reports)
  • 3,861 CTA transit buses (that can hold more than 15 passengers) were involved in 3,853 crashes (1.25% of all crash reports). And there were 8 “bus on bus” crashes (0.003% of all crash reports)
  • 1,644 school buses were involved in 1,635 crashes (0.53%). And there were 9 “school bus on school bus” crashes (0.003% of all crash reports)

Data does not include estimated cost of property damage.

Not all vehicles may be unique – some vehicles may be involved in more than one crash.

Construction update: Pilsen streetscape improvement

The most popular posts on Steven Can Plan are Chicago infrastructure construction updates.

In October 2009 I told you about the Cermak/Blue Island Streetscape project from the Chicago Department of Transportation. The article was called, “Pollution fighting bike lane coming to Pilsen.” I was kind of skeptical at first, but never mentioned this to anyone.

I’m happy to say it’s a reality and you can see the world’s first (I think) bike lane made of permeable pavers that have an ingredient that reduces the amount of nitrogen oxide in the nearby air. On Tuesday I was riding to Bridgeport via south Halsted Street and saw the construction on Cermak Street. I rode west to check it out. Then I saw work happening on Blue Island Avenue and had to check it out. These’re the results!

The construction situation at the northwest corner of Halsted and Cermak, where new sidewalks will be built.

Some vaulted sidewalks are being filled in.

The pollution fighting bike lane! Not complete: it needs signage and striping so you may see people parking in the future bike lane. The top inch of the permeable pavers has TX Aria from Italcementi.

More evidence of your bicycling culture transition

Low numbers of people in “your” cycling organizations and advocacy groups. From the comments on “Why I’m not a ‘cyclist’ anymore,” a story about moving from a bicycle subculture (United States) to bicycle culture (Copenhagen):

In Amsterdam, 700,000(?) people cycle, only 4,000 are member of the Fietsersbond (cyclist’s union). We always explain this comparing it to the non-existing vacuum-cleaners union. Everybody owns and uses a vacuum cleaner, no-one feels the urge to unite themselves around this.

People riding their bicycles in Copenhagen, Denmark.

Put the first cycle track somewhere else

Updated 06-03-11: Grew the list below from 11 locations to 15 to match the full list on wiki.stevevance.net.

I propose 15 locations for Chicago (see link for ideal segments):

  • Archer Avenue (whole length)
  • Blue Island Avenue (between UIC and Pilsen, but then connecting Pilsen to Little Village via 26th Street)
  • Chicago Avenue
  • Clybourn Avenue (entire stretch, from Belmont to Division)
  • Damen Avenue (really easy south of Congress; difficult between Chicago and Congress, and north of Chicago)
  • Fullerton Avenue
  • Grand Avenue (at least California or Kedzie to Navy Pier)
  • Halsted Street (in some discrete locations)
  • King Drive (connecting downtown/South Loop to Bronzeville, Hyde Park, Washington Park)
  • Kinzie Street (connecting one major bike laned street, Milwaukee, to another, Wells)
  • Ogden Avenue (the entire street, from the city boundary on the southwest side to its dead end at the Chicago River near Chicago Avenue)
  • Vincennes Avenue (I haven’t figured out the extents for this one)
  • Wabash Street (connecting downtown and IIT)
  • Washington Boulevard/Street
  • Wells Street – this may be one of the easiest locations to pull off, politically at least, especially if Alderman Reilly pays for all or part of it with his annual appropriation of $1.32 million (“menu funds”).
  • Western Avenue

Notice how I didn’t propose Stony Island between 69th and 77th.

I selected streets where there’s already much cycling happening – whether it’s directly on that street and for long distances or neighborhoods the street passes through. I also selected streets where there’s some cycling happening but make the all-important bikeway network connections on streets with high automobile or high speed traffic (like Western Avenue) or lead to places that attract trips by bike (like train stations). And I selected streets that lead towards downtown, to transit stations, to schools, and to jobs. The segment of Stony Island from 69th to 77th leads to a small shopping district on 71st Street and a Metra Electric train station with 197 weekday boardings (from 2006 survey).

A cycle track location will be most effective where it can:

  • attract the most new riders (goal #1 in the Bike 2015 Plan)
  • make the biggest increases in safety by reducing injuries (goal #2 in the Bike 2015 Plan)
  • (and for the city’s first cycle track, be used by the most existing riders)

It is in these locations where these facilities will be quickly adopted by people bicycling to and from that neighborhood for their shopping, school, and social and work trips. It will also help lead the City and its residents to attaining the quite ambitious goals of the Bike 2015 Plan (have 5% of all trips under 5 miles by bike and cut frequency of injuries by half).

NACTO’s new Urban Bikeway Design Guide recommends cycle tracks for “streets on which bike lanes would cause many bicyclists to feel stress because of factors such as multiple lanes, high traffic volumes, high speed traffic, high demand for double parking, and high parking turnover.”

Stony Island between 69th and 77th has many lanes, high speed and high volume traffic, but low parking turnover (there’s a low density of businesses and many have their own parking lots). This area has low cycling levels and a grand bike facility here would do little to help Chicago reach the plan’s goals. We won’t see any benefit in terms of mode shift here.

Without further information on the intentions (see paragraph “On intentions” below) of those who selected this location and their goals for Chicago’s first cycle track, my surmise is that it was selected because of the roadway width (four lanes in each direction with a wide parallel parking lane, see map), where taking away a lane from car driving may be more politically and technically feasible – I believe this is the wrong way to begin a protected bike lane program.

On intentions: A former CDOT employee left a comment on my blog in December 2010 addressing the site selection: “Stony Island was recommended as a part of a Streetscape Master Plan [I can find no information about this plan]. It wasn’t like people were sitting around saying ‘Where can we put a buffered bike lane?’ It was really just a plan of opportunity since Stony Island is crazy-wide. Nevertheless, it will connect with a new bike path along the side of Marquette Dr in Jackson Park, which connects to the Lakefront Trail.”

While Stony Island could be a good demo location to prove that this type of facility won’t be harmful to drivers, as a demonstration of the power of protected bicycling infrastructure, it won’t do a good job.

A two-way protected bike lane (just like Prospect Park West in Brooklyn) in downtown Vancouver. Photo by Paul Krueger.

A one-way protected bike lane on 9th Avenue, New York City’s first cycle track. Photo by Jeramey Jannene.