Automagic Map of #opened12 Attendees

As registration filled up for OpenEd 2012, I began to wonder where people were coming from, and what kind of representation we were getting across the globe.

Step 1 – Geocoding the Attending Organizations

When people registered, we did not collect physical address info, just names, email addresses and organization names. Still, I thought, that has to be enough, right?

I knew that using a query like would return CSV values for that location, yet I couldn’t think of a simple way to turn an entire list of organization names into a map (this was one of those “I’m bored in this meeting and want to do something in 5 minutes” exercises.)

Enter the network to the rescue, mainly in the form of Tony Hirst (who I knew would know the answer) and Alec Couros. Tony pointed me to a post he had written earlier this year that highlighted the Google doc function =ImportData. By using that function and concatenating the Google Maps API query string with the placename/organization name I already had, it really was simple to get all of the organizations geocoded to then place them on a map.

Two caveats

  1. Google spreadsheets limit the use of the =ImportData function to 50 times per workbook, not sheet, so with around 170 distinct names to geocode, there was a bit of futzing around to put these in different workbooks, run the function, then copy/paste the resulting geocodes into a master sheet.
  2. Automatic geocoding based on organization is not an exact science – using the names exactly as entered in the registration forms did result in 140 good addresses out of 170, but the rest either returned no results, or else in a few cases bad results – BCcampus, the organization I work for, was placed somewhere in the Straights of Tawain! Still, that’s about an 82% success rate with no effort, and the resulting ones were easily fixed by replacing the org name with either a city name or specific address.

Step 2 – Mapping these coordinates

Once you have the resulting sheet of organization names and longitude & latitude data from the first step, the next step is fairly easy. I had stumbled upon Google Fusion Tables myself, an experimental feature aimed at combining datasets and visualizing them in new ways.  Tony mentioned these would handle my data automatically, and sure enough it did, importing the existing Google spreadsheet with one click, and with another turning it into a map.

But I actually ended up going with another approach suggested by Alec Couros, MapAList. MapAList is a 3rd party service that also works off of Google documents, and a simple wizard allows you to select your spreadsheet, worksheet and values you want to map and generates a map along with nice html embed code to use. I think either way works fine, I just ended up liking this one as Fusion’s URLs confused me and I ended up sharing one on twitter that pointed to the unvisualized data.

Below is the resulting map. The big learning here for me – the power of the =ImportData function. Without something like this, you end up having to write some code (not complicated code, but code nonetheless) that steps through your list, generates a http request for each one to the API end point, receives the resulting response, parses the response and compiles the outputs into some format you can use. This is not a super complicated program, but 95% of end users aren’t going to do this. But the above approach seems really feasible, and given the availability of HTTP based APIs that return CSV or JSON, opens up a huge realm of data to non-programmers who can still handle a spreadsheet (which, as you’ll recall, was the home computer’s first killer app.) – SWL