johnkeefe.net

One of many redistricting experiments we're working on looks at the effort by some community groups to carve out districts particular to their community.

So we gave it a whirl.

There are many ways to wrestle with this data -- and there is much wrestling yet to come. But by ranking and repeatedly mapping the Asian populations by census tract, we were able to come up with some maps that actually satisfy some of the contraints (except for the one that says you can't draw a district based on race!) For more info, click the "Deeper Data Details" link.

WNYC's Colby Hamilton inspired this map, and has a great post to go with it.

Mostly I worked in Google Spreadsheets and Google Fusion tables, using data from the great census.ire.org site.

I had hoped to create a single shapefile of my new district, but joining all of those census tracts kept choking my computer (and QGIS). So you're actually seeing a bunch of tracts without borders. I did use QGIS to rework some of the shapes so they cross two parks -- allowing me to make a contiguous district.

UPDATE: Here is the list of census tracts I used. And if you're poking around in the code, don't rely on the data in the experiment's fusion table; I mucked with it a lot.The original, intact tract data is here. And the Congressional district map of New York is valid for the 111th Congress.

Random thing I learned: Did you know QGIS can export shapefiles into KML? I didn't. Not used here, but good to know.

In September, I shared how WNYC makes news maps during a talk at the the Online News Association conference.

UPDATE: ONA posted a video of this presentation, which I've embedded here:

'Once Upon a Datum': Telling Visual Stories from Online News Association on Vimeo.

Here are my presentation slides (PDF), and here's a list of links to pages and sites I discussed in my talk:

Same-Sex Couples in NYC: http://www.wnyc.org/articles/wnyc-news/2011/jul/14/census-shows-rising-number-gay-couples-and-dominicans/

Hispanic Origins in NYC: http://www.wnyc.org/articles/wnyc-news/2011/jul/14/census-shows-rising-number-gay-couples-and-dominicans/

The New Littles: http://www.wnyc.org/shows/bl/clusters/2011/jun/02/june-guest-andrew-beveridge-and-new-littles/

Marijuana Arrests: http://www.wnyc.org/articles/wnyc-news/2011/apr/27/alleged-illegal-searches/

Contributions by Zip Code: http://empire.wnyc.org/2011/07/where-are-the-mayoral-candidates-raising-their-money/

Dollars in a District: http://empire.wnyc.org/2011/09/the-54th-assembly-campaign-contribution-breakdown-where-have-all-the-in-district-donors-gone/

NYC Hurricane Evacuation Map: http://wny.cc/EvacZones

NYC DataMine: http://www.nyc.gov/html/datamine/html/data/geographic.shtml

Shpescape: http://www.shpescape.com/

Hurricane Zones Fusion Table: http://www.google.com/fusiontables/DataSource?dsrcid=964884

Fusion Tables Layer Builder: http://j.mp/FusionBuilder or http://gmaps-samples.googlecode.com/svn/trunk/fusiontables/fusiontableslayer_builder.html

Layer-wizard map from presentation: http://dl.dropbox.com/u/466610/preso-map.html

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.

Last Day to Vote! Thursday, October 13 is the last day you can your vote for the ONA Board. Check your inbox for an reminder email sent Wednesday at 11 a.m. ET titled "Deadline is tomorrow -- vote for the 2010 ONA Board of Directors." It has your personal ballot login details. Then go vote!

I'm up for election, and my opponents are awesome.

The race is to fill seats on the board of the Online News Association, an important organization at the intersection of journalism and technology

And while the slate of candidates is crazy strong, I still think I'd be a great addition to ONA's board. So here are some John Keefe Bullet Points to help ONA members consider a vote for me:

• Planner and doer
• Super collaborative
• Public media news director
• Data-news MacGyver
• Journohacker
• Share what I learn so others might, too

I'm especially committed to that last point, working hard to explain our data-news projects in ways other journalists can replicate and build upon. For evidence, just look anywhere else on this blog.

It's journalism in the public interest, infused with the spirit of open-source coding. This isn't unique to me; there are several fantastic teams committed to this. But it is exactly the commitment to propelling journalists, in open and accessible ways, that I would bring to the ONA board.

My full bio and ONA vision statement -- and those of my slatemates -- is right here.

Voting begins Friday, September 23.

A couple of years ago, I had our WNYC engineers use a plotter to print out this huge evacuation map PDF. Seemed like a good thing for the disaster-planning file. Just in case.

Then, back in June of this year, I was browsing the NYC DataMine (like you do), and realized New York City had posted a shapefile for the colored zones on that map.

UPDATE (Feb. 11, 2012): NYC has nicely revamped the DataMine since the summer Irene struck -- even mapping geographic files like this right in the browser. But it's actually tricker to find the shapefiles now. Here's the hurricane zones dataset. Click "About" and scroll down to "Attachments" for the .zip file containing the shapefiles. Or just use this shortcut.

I knew I could use the shapefile to make a zoomable Google map -- which would be a heckuvalot easier to use than the PDF. So I imported the shapefile into a Google fusion table. (It's super easy to do; check out this step-by-step guide.) Next, I added that table as a layer in a Google Map and tacked on an address finder I'd developed for WNYC's census maps.

Then I tucked the code away on my computer. Just in case.

Fast-forward to Thursday morning, as Irene approached. On the subway in to work, I polished the map and added a color key. It was up on WNYC.org by midmorning, long before the Mayor ordered an evacuation of Zone A.

When the order was announced, I used another fusion table to add evacuation center locations, updating that list with info from New York City's Chief Digital Officer Rachel Sterne. (The dots are gone now, since the sites are closed.)

I'm not at liberty to reveal traffic numbers, but the site where we host our maps received, um, a lot more views than it usually does. By orders of magnitude. Huge props to the WNYC.org digital team for keeping the servers alive.

As Hurricane Irene was approaching Puerto Rico, I noticed that the National Hurricane Center posts mapping layers for each element in their storm-track forecast maps.

Since their zoomable maps aren't embeddable, I made one that is. Feel free to use it:

Right now, I'm manually updating the map with new layers as they are issued, every three hours. I'm pretty close to having a script ready to handle that for me, based on information in the storm's RSS feed.

In the process of building this map, I learned how to use "listeners" to dictate the order the layers are rendered. For anyone trying to work that out, here's the code for how I did it.

Our new Empire Blog reporter, Colby Hamilton, dropped by my desk the other day wondering whether we could map contributions to presumptive NYC mayoral candidates by zip code.

He was going to post about it after lunch. I said I'd be ready with a map.

Thanks to Fusion Tables and a little Ruby magic, I had one ready when his story was done shortly after lunch, and we updated it into the evening as the candidates' filings were made available by the NYC Campaign Finance Board.

How I Did It

For anyone looking to do something similar, here's what I did:

-- I downloaded each candidate's donation as an Excel spreadsheet from the homepage of the Campaign Finance Board.

-- I uploaded the spreadsheet to Google Fusion Tables (if it's an Excel file more than 1MB, you have to save it as comma-separated-values, or .CSV, before uploading).

-- I used Fusion Tables' fantastic aggregattion function -- View -> Aggregate -- to sum the contributions by zip code. Then I exported that file as a .CSV, which gave me a file for each candidate with the columns: ZIP, SUM and COUNT -- SUM being the total donations and COUNT the number of donations for the zip code.

-- I re-uploaded that aggregation export back to Fusion Tables. (If anyone knows how to save an aggregation in FT without exporting it and uploading it again, I'm all ears.)

-- Now that I had the contributions by zip code, I need the zip code shapes to go with them. The US Census has zip code shapefiles by state FIPS code, and for the entire United States. (Quick note: Census zip code data and US Postal Service zip codes aren't exactly the same, though we felt comfortable using the Census version for this project.)

-- I uploaded the New York state zip code shapefile to Fusion Tables, too, using Shpescape. (If you're working with New York State, you can save some work and just use mine.)

-- I opened the ZIP-SUM-COUNT file in Fusion Tables and merged it with the zip code shapefile, linking them with the ZIP field in the first file and ZTCA5CE10 in the second file.

-- Using Visualize -> Map, I could see all of the relevant zip codes for that candidate. By using the Configure Styles link, and then tinkering with Fill Color -> Buckets settings, I shaded the map according to total donations.

This map is ready to be embeded!

The Trouble with Tables

An admission, though: I didn't use the Fusion Tables embeddable map for this story. I did share the FT map with Colby, which let us know we had a couple of good stories. FT is great and fast for that. It also works in production with smaller data sets.

But the long time it takes Fusion Tables to populate a map with large data sets can make for a frustrating user experience. That's compounded by the fact there's no way (yet) to "listen" for a sign that the layer has fully loaded, which would let me display a "Please wait ..." sign until it did.

So in this case, I built my own KML, or Keyhole Markup Language, file (5 of them, actually; one for each candidate). I then compressed those files in to much smaller KMZ files, which are just "zipped" KML files, so they load quickly. I then used those files as layers with Google Maps' KmlLayer() constructor. I also used a "listener" to find out when the layer is fully loaded, and display an alert to the user until it is.

More to Come

As for how I built the KML file, I'm going to share that in another post once I clean up the Ruby code I used to automate the process. (If your project can't wait for that post, drop me a note and I'll try to help.)

But the basics are these:

1. The layout of a KML file, and the format for using different styles to color different shapes, is pretty straightforward and nicely documented. In my code, I changed the style name for a given shape based on the value of the "SUM" variable.

2. The hardest part of writing a KML file is defining each shape in the proper format. But the merged file I made linking the ZIP-SUM-COUNT data and the shapefile actually has that information! The "geometry" column of that table is straight KML! (Thank you, Shpescape.) Export that merged file as a .CSV, and you've got all of the building blocks for your map.

If you have ideas, improvements or questions about this post, please don't hesitate to drop a note in the comments or drop me a note via email.

Thousands of people live in the Hudson River.

That's what you'd think, at least, by looking at U.S. Census tract maps for New York City, because census tracts extend to the state line.

But a population map drawn like this isn't attractive, and isn't accurate, either. It suggests inhabited areas at the coast are far larger than actually they are.

So what's a journo-mapper to do?

Fortunately, the Census Bureau also publishes shapefiles of all of the water in the U.S. So one solution is to tell you trusty computer to subtract the water areas from the tracts -- and the difference will be the parts on land.

Doing this turns out to be far easier than I expected. (Thanks to Michael Corey and Nathan Woodrow who responded to my help tweet.) Here's what I did:

1. Opened my census tract shapefile with QGIS (a free, open-source mapping program I'm getting to know).

2. Found the water shapefile for Manhattan (New York County) and opened it as a new layer in QGIS.

3. From the QGIS menu, selected Vector -> Geoprocessing Tools -> Symetrical Difference and followed the prompts, choosing the tract shapefile as the "Input vector layer" and the water shapefile as the "Difference layer."

4. Compressed the resulting shapefile set into one .zip file and uploaded it to Google Fusion tables using shpescape. Once in Fusion Tables, I can play with it, view the map and merge it with population data.

Sweet!

A few extra notes and tips for those trying this at home:

- I've found water shapefiles only for individual counties, which makes for a small pain to do an entire state. For New York City, which is five counties, I loaded the five water shapefiles into QGIS, made sure they were all visible, and used Layer -> Save Selection as Vector File... to save them all as one shapefile. I then used the resulting shapefile in the Symetrical Difference process.

- Be sure the water map represents the same census year as the tract map (and, of course, your data). Very likely you'll be using shapefiles for the previous decennial census. For our New Littles map we had 2009 data, so we used the appropriate tract and water shapes -- which were from the 2000 census.

- I get an error about missing coordinate information when I do step 3, but it hasn't caused me any problems I know of. Also, on my Mac version of QGIS, the Symetirical Difference window and the file-saving dialog box conflict -- but I just moved them to their own side of the screen.

- Census tracts are made up of census "blocks," which are smaller and generally DO follow coastlines. So if you're mapping blocks, you can eliminate the watery ones by excluding any block with an "area land" of zero.

- The difference trick doesn't change the meta-data associated with each tract, which generally is a good thing. 

If you have any questions or suggestions, don't hesitate to post them in a comment below!

When I saw the NYC ancestry data, I immediately thought, "That screams MAP!"

Brian Lehrer Show producer Jody Avrigan had been working on a great project looking for the new "Littles" in New York City -- neighborhoods where people of a certain ancestry or ethnicity live. He had a spreadsheet; I wanted to visualize it.

The result may be my favorite map project so far:

Mostly, I built on what I'd learned making WNYC's Census Maps, adding a few of things:

• An on-map drop-down menu (here's the CSS code for that).

• Code that selects different data from a single Google Fusion Table. 

• Panning and zooming to the neighborhood I want to highlight.

• A better "Share or Embed" pop-up box using jquery.alerts.js. 

I also tried to clean up and refactor my original code to make it easier to read (and reuse).

You can see that code on GitHub. I tried to document it clearly, but post a note below if you have any questions or would like clarification.

UPDATE: In making this map, I used a new (to me) trick to remove the water areas from census tract shapes on the coastline.  Here's how I did it, if you're interested.

We flew a big red balloon over New York City Saturday morning, generating excitement, police inquiries and about 300 aerial pictures of the tippy-top of Manhattan.

And we had a bunch of fun.

I just posted the complete set of pictures shot from about 400 feet up. (Also viewable as a slideshow.)

For the complete story of how we did it -- and how you can, too -- check out my Mapping Inwood series.