I loved this challenge.
WNYC's Colby Hamilton wanted to know: How much money was being raised by candidates for a state legislative district from within the district itself?
Answer: Very little.
Making this Map
This wasn't my typical "just upload it to Fusion Tables" project. It got geeky quickly, intentionally.
My method involved a PostgreSQL / PostGIS database and QGIS mapping software. Everything is free, which is amazing, yet they take some advanced tinkering -- especially the database stuff.
First, I geocoded the donation addresses, getting each one's latitude and longitude, using this nifty batch geocoder. The donor's name and donation amount were also on each line.
Then I fed the data into my PostgreSQL database and pulled it into QGIS (they talk nicely together). I also layered in a shapefile of the district from the US Census Bureau.
I then asked QGIS where the donations and the district "intersect" -- and spit out the resulting shapefile for each candidate.
Next I uploaded each candidates' "intersection" shapefile and their all-donations shapefile to Google Fusion Tables using shpescape. Once there, I used Fusion Tables' aggregation feature to total the donations in the district (the intersection).
Fusion Tables also allowed me me plot all of the donations, and also the shape of the district. (Little trick: I actually copied the "geography" cell from the 54th District table and added it as a new row to the donations table. That way the donations and the district shape appear at the same time.)
Finally, I put the layers together into a map template I've grown since building 2010 Census maps.
You'll notice I'm not diving deep into the details here, but if you're looking into a similar project, drop me a note at john at johnkeefe dot net look at this page, where I share every tidbit, command and SQL "select" statement I used.
Coulda Just Used Fusion Tables
The truth is, I could have used only Fusion Tables. The number of donations within the district turned out to be so small -- 69 in total -- I could have simply uploaded the donations into Fusion Tables, letting it do the geocoding and the drawing of points and the district shape.
Then it's just a matter of clicking on every dot within the pink lines, adding up the donations in each bubble along the way.
Instead, I've created a process to do more complicated inside-an-area calculations. And to help others do them, too.