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.
=find() to confirm that state names match. They will until ~New York
=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.
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.
SET the_geom = us_states.the_geom
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.”
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