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 http://maps.google.com/maps/geo?output=csv&q=Vancouver 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

3 thoughts on “Automagic Map of #opened12 Attendees”

  1. Just as a follow up to my own post – once I had the geocodes for each registrant, I was also interested in doing some graphs of attendees by country and province. I realized I could use a reverse geocode lookup to do this, and after once again calling on Tony (instead of RTFM) was referred back to the Google maps API.

    This proved a little trickier- the example they provide returns JSON, and try as I might I couldn’t figure out how to get the JSON to format nicely into a Google spreadhseet. =importData brings it in, but it’s all over the place.

    But I realized you can query this API and get XML, and that Google spreadsheets has an =importXML function. The query point is thus something like

    http://maps.googleapis.com/maps/api/geocode/xml?latlng=51.5174354,-0.1187758&sensor=false

    And to import just the country codes, use something like

    =importXML(http://maps.googleapis.com/maps/api/geocode/xml?latlng=51.5174354,-0.1187758&sensor=false; “/GeocodeResponse/result[type=’country’]/formatted_address”)

    Two things threw me bout this last bit – one is that it unlike all of the other functions I’d used, it uses a semi-colon instead of a comma – doh! The other is that the selector “country” needs to be quoted with single quotes inside the double quoted xpath statement. PITA, but I finally figured it out, and so can automatically generate a list of countries attendees are from simply based on an organization name.

Comments are closed.