Category: Web Applications

Stats from the OECD: Comparing traffic injuries of the United States and Netherlands

May 21, 2012 / / 0 Comments

For an article I’m writing for Architect’s Newspaper about the Chicago Forward CDOT Action Agenda, I wanted to know about traffic injuries and fatalities in the United States, but compared to the Netherlands and Denmark and other places with a Vision Zero campaign (to have 0 traffic deaths each year).

I already knew the OECD had a good statistics database and web application. With a few clicks, I can quickly get a table of traffic injuries (casualties) listing just the countries I want. I can easily select the years I want, too.

In one more click the web application will show a time animated bar chart. A feature I’d like to see added is dividing the figure (in this case traffic injuries) by the population. Check out the video to see what it looks like. The United States looks to be in terrible shape, but our country has several times more residents.

I had trouble downloading and opening the CSV file of the data table I created. The XLS file was damaged, also. The built-in Mac OS X Archive Utility app couldn’t open the .gz file, but I used The Unarchiver app successfully.

My calculations, based on data from OECD (national population and traffic fatalities), Illinois Department of Transportation (IDOT), and the American Community Survey:

Fatalities per 100,000 in 2009

  • United States: 11.02472
  • Denmark: 5.48969
  • Netherlands: 4.35561
  • Sweden: 3.84988
  • Chicago: 16.74891
  • United Kingdom: 3.83555

Chicago’s fatality rate per 100,000 citizens in 2009 was 16.75 (473 deaths on the roads). The fatality rate dropped in 2010: just 11.65 deaths per 100,000 residents (315 deaths on the roads; the population also decreased).

Updated September 28, 2012, to add the United Kingdom. 

BikeLock app based on dataset I opened up

April 4, 2012 / / 0 Comments

Bike parking at Daley Plaza, downtown Chicago. 

It’s really cool to see work you did “go places”. A friend of mine who works at Groupon just linked me to an iOS app called BikeLock that finds bike racks near you on iPhones, iPads, and iPod touches. It’s based on bike rack location data in the City of Chicago’s Data Portal. (The data on there is old, while the data in the public API I built is real time.)

Download it from the iTunes Store for 99 cents. The developer is Mike Jahn, another Groupon staffer. You can get the same information for free, though, on my mostly mobile-friendly Can I bring my bike on Metra? web app, and a website I made for the Chicago Department of Transportation (CDOT).

A screenshot of the Can I bring my bike on Metra? bike rack finder website. 

That data comes straight from the Bike Parking Web Application I started developing in 2008 soon after I started working in the Chicago Bicycle Program. It was good that my supervisor had the same perspectives I did about open and transparent data and work. But it didn’t start like that; here’s the full story:

My first job at the Bicycle Parking Program was to deal with abandoned bikes, get them off the street. I was taught the existing method of keeping track of my work, but I used my programming skills (in PHP, MySQL, and with the Google Maps API) to develop a web application that tracked it faster and mapped out the abandoned bikes I had to visit and tag with a notice. I was using this for a few days or few weeks and then show my boss. His reaction was something like, “Great! Now make one for bike racks!”

Why? Well, let’s take this quote from Judy Baar Topinka, Illinois comptroller, speaking Tuesday about her office’s new website, The Ledger, which lists the state’s unpaid bills among other financial data.

“The object of the exercise is to make everything that we know of in the comptroller’s office public. If we know it, you’ll know it.” WBEZ

I made one for bike racks. I created two environments, one for private administration at the office (“Bike Parking Web Application”) and one for the public (“public interface”). A later feature I added to the public interface was the Advanced Search. This allows you to filter by Ward, Community Area, and Status. You can then choose your sorting method. A map will appear above the results. You can download the results as either an XLS file, and XLS file that’s designed to be imported in GIS programs (like QGIS), or a KML file.

I’m aware of just one other app that uses this data set: MassUp.us. I don’t know if MassUp uses the real-time API that my Metra bike rack finder uses.

Cycle mapping

November 10, 2011 / / 13 Comments

A screenshot of Critical Map: Milano. 

What are the sites that will let you either draw or upload a bike route to share with others?

And what are the sites or mobile apps that give you cycle routing?

A screenshot of Bike Share Map: London, UK.

And other bike-related maps?

I’m just simply researching and collecting links to cycling-related map mashups and apps.

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.

Using Google Fusion Tables to create individual Chicago Ward maps

September 13, 2011 / / 0 Comments

I wanted to create a map of the 35th Ward boundaries using Google My Maps for a story on Grid Chicago. I planned to create this by taking the Chicago Wards boundary shapefile and exporting just the 35th Ward using QGIS into a KML file. I ran into many problems and ended up using Google Fusion Tables as the final solution.

The problems

First, QGIS creates invalid KML files. Google Earth will tell you this. I opened the KML file in a text editor and removed the offending parts (Google Earth mildly tells you what these are; you can use this validator to get more information).

Second, Google My Maps would not import the KML file. I tried a different browser and a different KML file; a friend ran into the same issue. I reported this problem to Google.

The solution

I uploaded to Google Fusion Tables a KML file containing all wards. I did this instead of uploading the single Ward because, like a database, I can filter values in the column, selecting only the row I want with “ward=35”.

After applying the filter, the map will show the boundary for just that ward. I grab the HTML code for an embeddable map and voila, the article now displays an interactive map of the 35th Ward.

Whenever I want to create a map for a different ward, I go back to this Fusion Table, make a new filter and copy the new HTML code.

A screenshot of the embedded map, showing just 1 of 50 wards, in the Grid Chicago article. 

Elsewhere

I had the same problems with QGIS exporting and uploading the KML files to My Maps the other day when I was creating maps for the abandoned railroads for Monday’s Grid Chicago article. Not thinking about Fusion Tables, I drew on the map with my mouse the lines.

Screenshot of the map of abandoned railroads.