Most Common Job, By State
Inside AirBNB
CA Drought
- WNYC Sandy Flooding
- ProPublica Government Lends to Rebuild in Flood Zones
- NPR Zoom In On Oklahoma Tornado Damage
- As Bloomberg Built Affordable Housing, City Became Less Affordable
- map as navigation
We don't deal with projections but they matter.
Cartography and GIS aren't the same thing. We're talking about very basic maps as visualizations here. More on that
In 2011, the BLS published a map of fatal workplace injuries by state.
Good discussion questions: 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?
Takeaway: BLS data is mapped, but it isn’t normalized to the population and the gradient makes no sense at all.
So we'll recreate it:
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.
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
The first time I tried this I went the other way and hit some useful errors. Noted here for future reference.
Add a numeric column: "bls_fatalities_per_100k"
UPDATE us_states
SET bls_fatalities_per_100k = fatalities_per_capita.fatalities_per_capita
FROM fatalities_per_capita
WHERE us_states.name = fatalities_per_capita.state
Error: column "bls_fatalities_per_100k" is of type double precision but expression is of type text -- what do we think that means?
A little more on double precision. We have two choices: we can go to the first table and change the column type to numeric. Or we can use to_number(fatalities_per_capita.fatalities_per_capita, '999D9999999') to transform the string into a number in the query.
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.
- NYC:
- CUNY Research Center
- Google Fusion Table search
- NYC Data sets
- GeoCommons
- CartoDB's Common Data
- Census.gov
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
- Putting images on imagur.
- Using http://placehold.it/
- For now, delete the index.html, but add a "storyboard.md" file to your pitch.
- Watch your language. Think about talking to your family at Thanksgiving. You're authoratative, because you know this, but your goal is to share what you know.