CartoDB Walk Through

In 2011, the BLS published a map of fatal workplace injuries by state.

Are these colors continuous or categorical? Should they be? Is anyone surprised that CA and TX have a lot of workplace injuries? How can we improve on this?

So we’ll recreate it:
BLS Fatality Data (csv)
2011 Population Estimates (via census.gov)

Combine those two in a spreadsheet, first. Use “Save As” to create a new spreadsheet so you don’t clobber your data.

Use =find() to confirm that state names match. They will until ~New York

Use =G10/E10 to get fatalities per capita, talk about scientific notation, then =(G10/E10)*100000 for per 100,000

Upload to CartoDB

Upload the CSV to CartoDB. Now you need a shapefile. Get a US State file from NationalAtlas.gov or NOAA or this much, much smaller file from GeoCommons. (Use the KML, the shapefile doesn’t have a projection)

GoogleMaps will only take KML files, CartoDB will take ShapeFiles or KML, but don’t use Safari — a shapefile is a bundle of database files, you don’t want to unzip it.

Merging

CartoDB used to make merging harder but they’ve worked a few things out.

Keep both open: what columns can we use to combine these two?

CartoDB has a good explanation of the join process. It isn’t actually straightforward.

UPDATE fatalities_per_capita
SET the_geom =  us_states.the_geom
FROM us_states
WHERE us_states.state = fatalities_per_capita.state

This works for me because my BLS data is in a table called fatalities_per_capita, my state boundaries are us_states, and in both tables the state names are in a column called “state.”

Display

Use the wizard first. Bubbles for population, color for percapita.

What do methods mean? Learn This was a little buggy but might be fixed by now.

Put it on a web page

Where to Find Boundary Files

How to Geocode

If you need to transform addresses into lat/lon pairs, you have a couple of options:

  • Fusion Tables will do it, but their terms of service say you have to use that data on a Google Map.
  • Geocoder.us Will do one address at a time, or you can pay for a batch
  • CartoDB gives you a bunch free and you can pay for more.
  • More suggestions