Mapping #libday8

Bobbi Newman’s Library Day in the Life project is an awesome idea (which you should go sign up for), but I admit I’ve had trouble getting into it. There’s (traditional ironic librarian problem ahoy!) just so much data I don’t know where to start. So I’ve been thinking about alternative ways to visualize the data and provide entry points. I thought it would be fun to see the #libday8 participants on a map.

Behold, a map!

(The iframe embed Google generated didn’t seem to be working, but you can click through the image to play with the interactive Google map.)

How I did this

I thought I was going to have to write some code, screen-scrape the HTML wiki into CSV, learn a Google maps API, blah blah blah. It turns out I did not! As with so many tech problems, this reduced to “beat it with rocks until it works”. Unlike many tech problems, no actual code was required; it was mostly copy-paste. To wit:

Step 1: =ImportHTML is your friend. Google Docs will automagically import tables from web pages as spreadsheet data. I followed these instructions; just a matter of changing the number N until the right table materialized. (Note that =ImportHTML is case-sensitive; =importHTML won’t work.)

Step 2: Copy-paste as values. The automagic import is a whole bunch of spreadsheet functions, so it can’t be imported into the tools I needed later in the process. However, this can be fixed. Create a second sheet for the spreadsheet. Copy the first one and paste as values into the second; this will keep the parts you can see (turning them into static values) and throw out the functions generating them.

Step 3: Geocode the data. To get good mapping, you’ll need your address data to be geocoded — latitude/longitude rather than just text. Luckily, the robot servants can do that for you. It turns out there are roughly a bajillion gadgets you can stick into your Docs spreadsheet to make it do awesome stuff — lots provided by Google, plus you can invoke arbitrary custom gadgets if you know their URL. Pamela Fox has a geocoding gadget for you, so I could turn the human-readable address data into lat/lng coding with a few clicks, following the instructions at her site.

Here’s the spreadsheet I ended up with after that munging. I used several sheets as intermediate copy-paste steps so you can trace some of my thought process there.

Step 4: Google’s Spreadsheet Mapper tool. Make a copy of their default spreadsheet, according to these instructions, and customize it with your data. To wit:

• Pick a template — doesn’t matter which as you’ll be overwriting most of it
• Alter the static variables to what you want (in my case, just a “Library Day in the Life” title)
• Alter the KML style variables, if you care (totally optional; just lets you change color schemes and so forth)
• Change the Unique Variables to be the column headers you will be using for your data (in my case, the same columns as in the #libday8 wiki) — same order as on your spreadsheet for ease of copy/paste (see, I told you this is mostly a copy-paste problem!)
• Change the HTML layout (scroll down to the bottom of the template sheet — easy to miss). You’ll definitely need to remove references to any static variables you deleted, and add references to your unique variables (it gives you special variable names in curly braces to use). You’ll have to copy-paste to a text editor as you can’t edit the cell in place (make sure to use a text editor, not a word processor — nothing that’ll insert weird invisible characters!). You may want to prettify it.
• In the Placemark Data sheet, copy-paste the data from the final version of your spreadsheet.
• Make sure that “Template #” is filled in with the template you just modified for all the columns, and that your spreadsheet data is filled in to the correct columns (in particular, note that the lat/lng data has a special place). Delete any rows without lat/lng data as they will throw an error and you can’t map them anyway.
• On the “start here” sheet, there’s a Google Maps link. Tada! Make sure to change the visibility on your map to something that other people can see.

Here’s the spreadsheet mapper stuff I ended up with.

Still some things I’d like to improve — I’ll need to re-import & re-munge the data when all the participants have signed up; I don’t love the HTML format I ended up with for the placemarks (would welcome others’ design work here — see that spreadsheet mapper for the format, which is a little wacky); and the import didn’t preserve the links to people’s blogs, twitter accounts, etc. (or the line breaks between them), so I’d like to figure out how to make them live. But as a first pass, I’ll take it.

So: yeah. Lots of beating things with rocks and googling for documentation and a TON of copy-pasting. Not a single actual line of code. Thank you to all the people who did write the code that does the automagical steps, and put it out there for free. And to Bobbi and everyone who signed up for providing data. (Data! Yum.)