Tagcrash reporting

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.”

Chicago Police responds to my FOIA request about bicycle theft

A Chicago Police (CPD) officer called me this morning to discuss my FOIA request for bike theft data. It was very revealing.

The first problem is that I forgot to ask for a time frame. No big deal, I can tell him over the phone that I want the last three full calendar years.

The second problem is that there’s not a separate code for recording bicycle thefts. It’s recorded under “Simple Theft” and as being under $300 or over $300.

Third problem is that the database front end (the graphical interface that allows officers to search the reporting database) doesn’t allow him to search all of the report narratives for “bike” or “bicycle” and limit the search to “Simple Theft” in a specific time frame. Some report codes allow narrative searching, and some don’t. He said it would be impractical to search all narratives for the words “bike” or “bicycle” because a lot of reports not about theft would appear in the results.

In my last blog on the Chicago Police Department’s FOIA response (for my request about bike crashes), they explained that they don’t have to create records that don’t already exist (like a list of bike thefts). This response is identical, but they called and gave me a better explanation. The officer also said they don’t have the staff resources to spend on collating their records for bicycle theft reports. I understand this.

He also explained that reporting standards at the CPD are guided by the Federal Bureau of Investigation (FBI) in the department’s “Incident Reporting Standards.” In the FBI’s reporting standards, there exists a line item for “bicycle theft” but it’s the same code as “simple theft.” There are separate codes for “credit card theft” and “motor vehicle theft.”

It seems the solution to the problem of obtaining records on bike theft in Chicago is to update the Incident Reporting Standards and include a new code for bike theft reports. At the end of the call, I understood that I was not going to get a list of bike theft dates and times from the police.

For now, Chicagoans should also report their bicycle theft to the Stolen Bike Registry so there’s a publicly available record of theft locations.

A Chicagoans rides his bike north on Halsted Street through University Village. If his bike is stolen, we can’t expect the Chicago Police Department to keep an easily findable report of it.

Better bike crash map now available for Chicago

I met Derek at a get together for “urban geeks” last Tuesday where he told me he was making a filterable/searchable version of my Chicago bike crash map using the Google Fusion Tables API. It essentially allows you to perform SQL-like queries to show different results on the map than one view. It’s possible to do this yourself if you open the bike crash map in the full Google Fusion Tables interface (do that now).

You can use it now!

New bike crash map, click through to view

Derek’s map has the benefit of great interface to drill down to the data you want. You can select a day, a surface condition, and the injury type. To download the data yourself, you’ll still have to access the full Fusion Tables interface.

Door lane photo and graphic by Gary Kavanagh in Santa Monica, California.

And since the data is the same as my original map, crash reports involving motor vehicle doors are not included. Here’s why doorings are excluded.

Why the Chicago bike crash map doesn’t show doorings

The data on the Chicago bike crash map comes from the Illinois Department of Transportation (IDOT) after reports are made to the Chicago Police Department, but it’s missing certain types of crashes. IDOT currently will not collect data about doorings.

Some Chicago cyclists created this sticker to alert drivers and their passengers to the dangers of the door. “Someone opened a door and killed my friend.” This is version 1 of the sticker; see version 2. Photo by Quinn Dombrowski.

Here’s a summary of the process:

1. Police officers make the reports

Chicago police officers collect information on dooring (outlawed by MCC 9-80-035) because of a recent agreement with the Chicago Department of Transportation (CDOT) – the importance of reporting doorings became a bigger priority after someone died after being doored on LaSalle Street in 2008.

When there is a dooring, Chicago police officers use an “Additional Units Form” in addition to SR-1050 (standard reporting form for Illinois motor vehicle crashes) and write “dooring” where the IDOT barcode would be on the Additional Units Form.

2. How the Chicago Police Department records it

When the Chicago Police Records Office sees that there’s no barcode they know they can’t send it to IDOT, but they see “dooring” and scan it for their own records (so they can provide it to crash parties later) and then email that number to the CDOT Bicycle Program. (There was a general order put out by CPD on this procedure and, yes, they actually send them– at least some of them.)

The CPD also knows that doorings, according to IDOT, are not a “reportable” crash. In addition to doorings, IDOT doesn’t consider the following as “reportable” crashes:

  • Any crash in which the first point of impact does not involve a moving motor vehicle.
  • Any non-injury crash which causes less than $1500 in property damage, unless one or more of the drivers was uninsured.

3. CDOT and Chicago Police Department connect

CDOT can then connect to the Police Departments records system, download a scan of the crash report, reads it and enters specifics into a tracking spreadsheet.

This is how dooring data is collected in Chicago because IDOT will throw away reports or attachments without barcodes. This should change. This process affects ALL cities in the State of Illinois but as far as I’m aware, only Chicago records doorings. It’s unfortunate that local agencies are forced to bear this additional task and provide special training for thousands of officers outside of statewide practices because IDOT doesn’t acknowledge the importance of this issue and revise its reporting policy.

Below is the SR-1050 form and you can see the IDOT barcode with the case number below it. The bike and pedestrian crash data I have from IDOT includes those case numbers.

Read more about doorings on Grid Chicago.

You asked for it, you got it – Chicago bike count data

Note: This post doesn’t have any analysis of the data or report, nor do I make any observations. I think it’s more significant to hear the ideas you have about what you see in the map or read in the data.

A lot of people wanted the Chicago bike crash and injury data overlaid with bike counts data.

In 2009, Chicago Department of Transportation (CDOT) placed automatic bike counting equipment at many locations around the city. It uses pneumatic tubes to count the number of bicyclists (excludes cars) at that point in the street – it counts ALL trips, and cannot distinguish between people going to work or going to school. This is dissimilar from Census data which asks respondents to indicate how they go to work.

Well, good news for you! CDOT today released the bike counts report from data collected in 2009 (just in time). There has been overwhelming response about the bike crash map I published – this shows how rabid the public is for information on their environments (just yesterday someone told me that they switched bike routes based on the crash frequency they noticed on their original route).

The size of the blue dot indicates the bicycle mode share for that count location. Mode share calculated by adding bikes and cars and dividing by bikes.

Get the data

A photo of the EcoCounter counting machine in action on Milwaukee Avenue (this was taken during testing phase, where CDOT compared automatic and manual counts to determine the machine’s accuracy).

How to use this map:

  1. Find a blue dot (count location) in an area you’re interested in.
  2. Zoom into that blue dot.
  3. Click on the blue dot to get the number of bikes counted there.
  4. Then observe the number of purple dots (crashes) near that count location.

What do you see that’s interesting?

What else is coming?

Now let’s hope the Active Transportation Alliance and the Chicago Park District release their Lakefront Trail counts from summer 2010. CDOT may have conducted bicycle counts in 2010 as well – I hope we don’t have to wait as long for that data.

I hope to have a tutorial on how to use GeoCommons coming soon. You should bug me about it if I don’t post it within one week.

Photos of Chicago bike commuters by Joshua Koonce.

Bike crash map in the press

Thank you to the Bay Citizen, Gapers Block, and the Chicago Bicycle Advocate (lawyer Brendan Kevenides). They’ve all written about the bike crash map I produced using Google Fusion Tables. And WGN 720 AM interviewed me and aired it in April 2011.

View the map now. The map needs to be updated with injury severity, a field I mistakenly removed before uploading the data.

The Bay Citizen started this by creating their own map of bike crashes for San Francisco, albeit with more information. I had helped some UIC students obtain the data from the Illinois Department of Transportation for their GIS project and have a copy of it myself. I quickly edited it using uDig and threw it up online in an instant map created by Fusion Tables.

A guy rides his bicycle on the “hipster highway” (aka Milwaukee Avenue), the street with the most crashes, but also has the most people biking (in mode share and pure quantity).

Why did I make the map?

I made this project for two reasons: One is to continue practicing my GIS skills and to learn new software and new web applications. The second reason was to put the data out there. There’s a growing trend for governments to open up their databases, and your readers have probably seen DataSF.org’s App Showcase. But in Chicago, we’re not seeing this trend. Instead of data, we get a list of FOIA requests, or instead of searchable City Council meeting minutes, we get PDFs that link to other PDFs that you must first select from drop down boxes. But both of these are improvements from before.

I would love to help anyone else passionate about bicycling in Chicago to find ways to use this data or project to address problems. I think bicycling in Chicago is good for many people, but we can make it better and for more people.

Read the full interview.

Trying out uDig, a free, multi-platform GIS application

ArcGIS is the standard in geographic information system applications. I don’t like that it’s expensive, unwieldy to install and update, and its user interface is stymying and slow*. I also use Mac OS X most of the time and ArcGIS is not available for Mac. It doesn’t have to be the standard.

I’ve tried my hand at Cartographica and QGIS. I really like QGIS because there’re many plugins, it’s open source, there’s a diverse community supporting it, and best of all, it’s free. I’ve written about Cartographica once – I’m not a fan right now.

My project

  • The data: Bicycle crashes in the City of Chicago as reported to IDOT for 2007-2009
  • Goal: Publish an interactive map of this data using Google Fusion Tables and its instant mapping feature.
  • Visualizing it: Added streets (prepared beforehand to exclude highways), water features, and city boundary (get that here)
  • Process: Combine bike crash data; reproject to WGS84 for Google; remove extraneous information; add latitude/longitude coordinates; export as CSV; upload to Google Fusion Tables; map it!
  • View the final product

Trying out uDig

In reaching my goal I had a task that I couldn’t figure out how to complete with QGIS: I needed to combine three shapefiles with identical table schemes into one shapefile – this one shapefile would eventually be published as one map. The join feature in fTools wasn’t working so I looked for a new solution, uDig, or “User-friendly Desktop Internet GIS.”

The solution was very easy. Highlight all the records in the attribute table of one shapefile, click Edit>Copy, then select the destination table and click Edit>Paste. The new records were added within a couple seconds. I could then bring this data back into QGIS to finish the process (outlined above under Project). I did use fTools later in the process to add lat/long coordinates to my single shapefile.

After adding more data to better visualize the crashes in Chicago, I noticed that uDig renders maps to look smoother and slightly prettier than QGIS or ArcGIS. See the screenshot below.

A screenshot of the three bicycle crash datasets (2007, 2008, 2009) with the visualization data added.

The end product: three years of police reported bicycle crashes in the City of Chicago on an interactive map powered by Google Fusion Tables, another product in Google’s arsenal of GIS for the poor man. View the final product.

*I haven’t used ArcGIS version 10 yet, which I see and read has an improved user interface; it’s unclear to me and other users if the program’s been updated to take advantage of multi-core processors. ESRI has a roundabout way of describing their support.

Bike crash reporting tool: I receive a response to my FOIA request

UPDATE 12-15-10: I forgot to add that the letter stated that the Freedom of Information Act doesn’t require the responding agency to create new datasets or records where one doesn’t already exist. This means that if what you ask for doesn’t exist in their databases or file cabinets, the agency is not about to filter or search through existing data to create a custom set for you.

I continue to prepare to create a bicycle crash reporting tool (or web application). Here are the previous posts. Readers have sent me many great suggestions and concerns about how to create it, what data to use, and how to present such data. I don’t expect to begin any demonstrable work on this until mid-January when I return from my 21-day European vacation.

Today I received a response letter from the Chicago Police Department regarding my recent FOIA request for bicycle crash data.

This was disappointing: “After a thorough search, it was determined that the Department has no existing record responsive to your request.” I thought, “that doesn’t seem right. They don’t make reports on bicycle crashes?”

Police respond to a bicycle crash in Newberg, Oregon. Photo by Matt Haughey.

The letter later states, “The Department  does not currently possess a record which aggregates bicycle crash data.” Ah, this means something now. It seems that while the Chicago Police Department does make reports on bicycle crashes, it doesn’t keep a running tally or stored database query which it can use to produce the data I want – what I want would require a little more work, I guess.

The final paragraph does recommend that I contact the Illinois Department of Transportation Division of Traffic Safety’s Crash Reporting Section, where the police forward their reports. It turns out that I already received crash data on IDOT and I’m “playing around with it” using Google’s Fusion Tables.

Best ways to present bicycle crash data

I started some preliminary work on my crash reporting tool. I haven’t written any code, but I’ve been working on the logistics of analyzing and presenting the data to the public.

I obtained bicycle crash data for 2009 from the Illinois Department of Transportation’s Division of Traffic Safety. I’m not able to distribute raw data (you’ll have to ask for it yourself) and Illinois statutes prevent me from distributing personally identifying data (but it’s really hard to know what this is). In the meantime, based on Ben Sheldon’s suggestion, I loaded some of the data into a private Google Fusion Table that instantly maps geocoded data (it can also geocode the data for you).

Richard cautions me about way I choose to present data. I need to choose terms and descriptions carefully to avoid misinterpretations. Pete from the Boston Cyclist’s Union recommends against accepting self-reported data. I’ll be taking their advice into consideration as I move forward.

You see in the map (top) that a lot of crashes happen on Milwaukee Avenue (above). That’s where a lot of people ride (over 3,000 in 24 hours in the fall).

I have not begun to review the narrative details in the crash reports. Actually, they’re not very narrative because they’re fixed responses – no free writing allowed. And not every record represents a collision (meaning a crash with at least two parties). Many are self-crashes (is that a legit phrase)?

I’m not sure exactly what story I want the data to tell so it will probably be a while before I make anything public. One of my favorite geographic information books, Making Maps, talks about the endless ways maps can be designed and portrayed and that each tells a different story. It’s best if I know the story (a goal) ahead of time.

I want to make a crash reporting tool

UPDATE 12-01-10: Thank you to Richard Masoner for posting this on Cyclelicious. I have started collecting everyone’s great ideas and responses in this development document.

Hot off the heels of making my “Can I bring my bike on Metra right now?” web application, I am ready to start on the next great tool*.

I want to create a bicycle crash reporting tool for Chicago (but release the source code for any city’s residents to adopt) along the lines of B-SMaRT for Portlanders and the Boston Cyclist’s Union crash map based on 911 calls.

I’d rather not reinvent the wheel (but I’m very capable of building a new web application based in PHP and MySQL) so I’ve been trying to get in contact with Joe Broach, the creator of B-SMaRT, to get my hands on that source code.

Not exactly the type of crash I’ll be looking for. Photo by Jason Reed.

I want the Chicago Crash Collector (please think of a better name) to have both citizen-reported data, and data from police reports. I just sent in my FOIA request for police data to the Chicago Police Department, but I’m not holding my breath for that.

© 2017 Steven Can Plan

Theme by Anders NorénUp ↑