CategoryWeb Applications

Converting shapefiles to GeoJSON, and other format conversions

To develop the Chicago Bike Map app, I had a problem I thought would be simple to solve: load train lines into a Leaflet-powered map. I had the train lines stored as a polyline shapefile but Leaflet can only read the GeoJSON format or a string of geographic coordinates representing lines.

I eventually found a solution (I can’t remember how) and I need to share it with you. The converter can do more than ESRI shapefiles to GeoJSON. It can reproject the data in the conversion. It can convert from several formats to several other formats.

The site is called MyGeodata Converter. You upload a ZIP file of geographic files – .shp and its companion files (.prj, .dbf, .shx), .kml, and .gpx. Let’s take the Chicago Transit Authority train lines shapefile straight from the City of Chicago’s open data portal. It downloads as a zipped collection of a shapefile and its buddies and we can take this file straight to the Converter and upload it. The Converter will unzip it and read the data; it will even identify the projection system (for Chicago-based geographic data, its common to use NAD83 Illinois StatePlane East FIPS 1201 Feet (SRID 102671, the same as SRID 3435).

The Converter will convert to one of the following formats, with same or new projection; accepts SQL statements to extract a subset of data:

  • ESRI shapefile
  • GML
  • KML, KMZ
  • GeoJSON
  • Microstation DGN
  • MapInfo File
  • GPX
  • CSV

Be your own traffic and road planner

Wanna know how many cars were measured to pass by on a street near you (in Chicago)?

Want to know how wide a street is?
Want to do this without leaving your house?

You can.

1. Find the “ADT” (average daily traffic) for roads in Chicago on the city’s Traffic Tracker website. Average is a misnomer, though, because that implies more than one count has been taken or estimate has been made. The last time the city counted cars in Chicago was 2006; I imagine the difference in counts taken this year would be statistically significant (meaning any differences would not be by chance or random).

To find a location in Traffic Tracker:

  1. You can pan and zoom the map until you find it, or you can select one street and then select an intersection street and click the magnifying glass.
  2. Then click the checkbox next to Traffic Signals.
  3. Click on a green dot (with number label) to find direction counts and the count date.

Okay, now let’s measure the street width.

Google Earth and Maps have this tool. For Google Earth, it’s as simple as finding the ruler tool in the toolbar, selecting your units, and then clicking on the start and end points. The distance will also be displayed live as you move your cursor.

Finding the distance in Google Maps has a few more steps:

  1. Turn on the measurement tool, in Maps Labs. Go to Google Maps and click “Maps Labs” at the bottom of the left pane.
  2. In the popup dialog pane, click the radio button next to “Enable” for Distance Measurement Tool.
  3. Click Save Changes.
  4. Find the street you want to measure.
  5. Click the ruler button in the lower left corner of the map.
  6. Click one side of the street and then click the other side of the street. You can keep clicking to get distances of a polyline (a multi-segmented line) that you draw.
  7. Change the map units to your desire (there are tens of archaic ones available).

Now you’ve got two more tools with which to arm yourself in understanding your streets and your neighborhood.

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

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

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

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

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

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. 

Two new beta apps for Chicago geography

I think 2011 is going to be the year of apps for me. I developed one for bikes and one for politics.

Bikes

I made the “Chicago Bike Data Portal” app on Saturday night. It gets your location via the HTML5 geolocation API and loads a list and map of all the reported bike crashes within half a mile. It works on some mobile phones, too (at least Android and iOS). Expect to see more data in the future, like ped and automobile crashes.

Politics

Then on Monday I made an app called, “Who is my Chicago Alderman?” where people can type in their address (or any address) and retrieve the alderman’s name, addresses, and phone numbers. I’ve already identified a problem where the app returns the wrong information for an address. I think this is a problem with the geocoder. Ever since CivicFootprint (a project of Center for Neighborhood Technology) disappeared, the city’s been missing an easy-to-use app for finding alderman. The city clerk’s website takes too long to use and is not as informative.

Other apps I built or worked on

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.

Reminder about open data and Obama’s Open Government Directive

Quickly after taking office, President Obama issued a memorandum about open government and opening government data. Then came the Open Government Directive* which said:

To the extent practicable and subject to valid restrictions, agencies should publish information online in an open format that can be retrieved, downloaded, indexed, and searched by commonly used web search applications. An open format is one that is platform independent, machine readable, and made available to the public without restrictions that would impede the re-use of that information.

Essentially, the executive government (er, Obama Administration) adopts the presumption of openness, that distributing public data is the default position and action to take.

Don’t squat on the data. Don’t fret over how people will view or manipulate the data – this is not your concern. Don’t delay its release. If you do this, you are a frigid dataist and I will remember this.

Photo of visual note taking at an open data seminar by Karen Quinn.

*The Directive has a little more backbone than the original memorandum: “This memorandum requires executive departments and agencies to take the following steps toward the goal of creating a more open government.”

Thank you to Tech President.

© 2017 Steven Can Plan

Theme by Anders NorénUp ↑