Category: Data

How to upload shapefiles to Google Fusion Tables

September 25, 2011 / / 0 Comments

It is now possible to upload a shapefile (and its companion files SHX, PRJ, and DBF) to Google Fusion Tables (GFT).

Before we go any further, keep in mind that the application that does this will only process 100,000 rows. Additionally, GFT only gives each user 200 MB of storage (and they don’t tell you your current status, that I can see).

  1. Login to your Google account (at Gmail, or at GFT).
  2. Prepare your data. Ensure it has fewer than 100,000 rows.
  3. ZIP up your dataX.shp, dataX.shx, dataX.prj, and dataX.dbf. Use WinZip for Windows, or for Mac, right-click the selection of files and select “Compress 4 items”.
  4. Visit the Shape to Fusion website. You will have to authorize the web application to “grant access” to your GFT tables. It needs this access so that after the web application processes your data, it can insert it into GFT.
  5. If you want a Centroid Geometry column or a Simplified Geometry column added, click “Advanced Options” and check their checkboxes – see notes below for an explanation.
  6. Choose the file to upload and click Upload.
  7. Leave the window open until it says it has processed all of the rows. It will report “Processed Y rows and inserted Y rows”. You will be given a link to the GFT the web application created.

Sample Data

If you’re looking to give this a try and see results quickly, try some sample data from the City of Chicago data portal:

Notes

I had trouble many times while using Shape to Fusion in that after I chose the file to upload and clicked Upload, I had to grant access to the web application again and start over (choose the file and click Upload a second time).

Centroid Geometry – This creates a column with the geographic coordinates of the centroid in a polygon. It lists it in the original projection system. So if your projection is in feet, the value will be in feet. This is a function that can easily be performed in free and open source QGIS, where you can also reproject files to get latitude and longitude values (in WGS84 project, EPSG 4326). The centroid value is surrounded in the field by KML syntax “<Point><coordinates>X,Y</coordinates></Point>”.

Simplified Geometry – A geometry column is automatically created by the web application (or GFT, I’m not sure). This function will create a simpler version of that geometry, with fewer lines and vertices. It also creates columns to list the vertices count for the simple and regular geometry columns.

Introduction to DIY bike ridership research

September 20, 2011 / / 0 Comments

A lot of people ask me how many people are out there bicycling.

“Not a lot”, I tell them.

And I explain why: the primary source of data is the American Community Survey, which is a questionnaire that asks people questions about how they got to work in a specific week. (More details on how it does this below.) We don’t have data, except in rare “Household Travel Surveys”, about trips by bike to school, shopping, and social activities.

It’s comparable across the country – you can get this data for any city.

Here’s how:

  1. Visit the “legacy” American FactFinder and select American Community Survey, operated by the United States Census Bureau.
  2. Select 2005-2009 American Community Survey 5-Year Estimates (or the latest 5-year estimate). This is the most accurate data.
  3. In the right-side menu that appears, click on “Enter a table number”.
  4. In the new window, input the table number ” S0801″ (“Commuting Characteristics by Sex”) and submit the form. The new window will close and the other window will go to that table.
  5. Now it’s time to select your geography. In the left-side menu, under “Change…” click on “geography (state, county, place…)”
  6. In the window to change your geography, select “Place” as your “Geographic Type”.
  7. Then select the state.
  8. Then select your city and click “Show Result”.
Notes:
  • This data shows all modes people take to work, who live in that city. It’s highly probable that people are leaving the city to their jobs on these modes. For example, someone who lives in Rogers Park may ride their bike to work in Evanston.
  • The URL is a permanent link to this dataset. Each city has a unique URL. You should save these as bookmarks so you can easily reference the data later.
  • The question on the survey doesn’t allow multiple choices: “People who used more than one means of transportation to get to work each day were asked to report the one used for the longest distance during the work trip”.

Policy insight for Monday, August 1, 2011

August 3, 2011 / / 0 Comments

This isn’t refined. These are just my notes that I speak from. I may not have spoke about everything written here and I may not have written here everything I spoke about. This is for Moving Design. 

There was report of cyclist crashing on the Tuff Curb at the on-street bike parking facility in Wicker Park.

Installing the Tuff Curb

experimental projects need reviews. I don’t mean projects that are considered experiments, I mean projects that are new to the people who designed it, and new to the people who will be using it.

we need good data collection.

Did the Kinzie bike lane cause congestion? So what if it did?
We would need data points that were collected using well-known methods, and probably at different times of the day and week. And we’d have to be sure to count cyclists, too.
Then 3, 6, or 12 months later, we’d have to do it again.

What was the change?
Is that a change that meets our goals?

Back to the cyclist crashing on tuff curb, what is the city’s plan to monitor the use (or disuse) of the facility? How will the city collect data on something like this?

Census – not gonna happen in 2020
American Community Survey – 5-year estimates (with data gathered annually) will replace decennial Census.

“Here are a few Streetsblog posts about Census and NYC DOT’s bike counts, and the problems with each. The first post has some stuff about what could be done to improve on them:” (Ben Fried, Editor in Chief, Streetsblog NYC)

http://www.streetsblog.org/2010/04/27/how-many-new-yorkers-bike-each-day/
http://www.streetsblog.org/2010/10/01/did-nyc-bike-commuting-decrease-in-2009-thats-what-the-census-says/
http://www.streetsblog.org/2011/04/13/actually-if-you-build-it-they-will-bike/

Read more policy insights from Steven Vance. 

New site brings together bike crash maps and projects

May 5, 2011 / / 0 Comments

I just finished creating a website that brings together my original Chicago bike crash map and all of its offshoots created by others. It also includes a more details and updated FAQ page as well as a short history of how the map and data came to be.

Enter the Crash Portal.

Right now it features projects from myself, Francesco Villa, Derek Eder, and George Aye’s students at the School of the Art Institute “Living in a Smart City” class. The site also links to my inspiration: Boston and San Francisco. If you have a related project, email me and I’ll figure out a way to add it to the site.

Screenshot of new Crash Portal

Just how many taxi vs. bicycle crashes are there? A Google Refine story

April 26, 2011 / / 0 Comments

On this Chicagoist story about how IDOT will now collect data on doorings (instead of ignoring that crash type as they preferred), I opened the story photo entitled “Cabbie takes down another” by Moe Martinez. His photo caption reads, “you see it alot … thankfully this guy seemed to be relatively ok … coherent and what not.”

I wanted to know just how often “we” see taxi drivers crashing with people riding bicycles. You can’t filter by vehicle type in either mine or Derek’s bike crash maps, but you can via the Fusion Table.

I decided to get the answer via Google Refine and make a screencast to show you just how quick and powerful a tool it is.

It’s dead simple:

  1. Load a CSV of the data into Google Refine.
  2. Click on the VEH1_SPECL column’s down arrow, then Facet>Text Facet.
  3. In the facet box, sorted alphabetically, find “TAXI/FORE HIRE.”
  4. The number of rows that apply is listed: 353.
  5. Divide 353 by the total number of rows, 4931, multiply by 100, and you get your percentage.

Taxi drivers are involved in just 7.2% of bicycle crashes in Chicago in 2007-2009.

The majority of crashes, at 66%, involve people driving “PERSONAL” vehicles. And 80% of those crashes are with a passenger vehicle that’s not a van, minivan, SUV, truck, or bus (so probably a sedan or coupe). Let’s look at more data.

How many taxis are there and how many personal vehicles are there? Are taxicabs involved in a disproportionately higher number of crashes?

About 781,023 people drive to work, either alone or with someone else, in Chicago (data from 2005-2009 5-year American Community Survey). 1,063,047 households have 1,218,594+ vehicles available in Chicago. Let’s assume the 7,000 taxicabs in Chicago are not counted as a “vehicle available.”* That’s 1,225,594 “personal” vehicles. If all were on the road at the same time, only 0.57% of them would be taxicabs. But they’re not on the road at the same time. So let’s take that number of people who drive to work and add 7,000 vehicles to it. So of those 788,023 “vehicles” now on the road, just 0.88% of them are taxicabs.

So it does seem that taxicabs are involved in a disproportionate number of crashes when compared to their presence on the streets. However, taxicabs are most likely driven more more miles and for more time than personal vehicles thus making their exposure to people bicycling greater than drivers of other vehicles. (A majority of “personal” trips are very short.)

New data coming soon

I can’t wait to get the 2010 crash data. Here’s why: In 2007, students in a taxi driver training course at Harold Washington College received some education about sharing the road with bicyclists:

A pilot “Share The Road” education module was launched at the taxi training school at Harold Washington College. It includes a 25-30 minute lecture, with discussion. After the pilot, the class will be required for all people training to drive taxis in Chicago. In the future, bicycle questions will be included on the exams required to become taxi drivers. June 2007 MBAC meeting minutes (PDF).

The number of crashes between taxi drivers and people riding bikes jumped from 2007 to 2008, but declined heavily between 2008 and 2009. More data will show us a clearer trend that may lend insight into the impact of the “Share The Road” education module.

*Notes

The question (PDF) on the American Community Survey asks, “How many automobiles, vans, and trucks of one-ton capacity or less are kept at home for use by members of this household?” This may or may not include taxicabs stored at home.

I don’t know how many taxicabs there are in Chicago, but the Chicago Sun-Times reported there are approximately 7,000.