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

Wayfinding at UBC

I’m lucky to be invited out to UBC tomorrow to give a little talk and meet with a few folks. I’ve been there maybe a half-dozen times but I really don’t know my way around the campus that well – it is truly immense! I was about to email my contact there to ask for direction, but then thought to check the university’s own website for maps.

Wow! Was I gald I did. I would have felt foolish to be asking after what I found at the Wayfinding at UBC site. The link above points to the final map I produced and printed – what I loved was the ability to plot not one but at least two distinct locations (and get directions between them if need be) and to add or subtract information to my final printout page (like closest parking, local details) if I wanted. I also loved they way they tied into Google where needed or where more appropriate.

Perhaps I don’t get on to enough campuses and the quality represented by this site is commonplace elsewhere and I just don’t realize it. But I think not – apparently the Canadian Council for the Advancement of Education were also impressed enough to award it the 2004 Gold Prix d’Excellence for ‘Best Department or Program Site on the World Wide Web.’ And well deserved at that; the gold standard as far as I am concerned for helping people find their way around campus. (Have I just inevitably damned myself to be driving around downtown Surrey while my talk is going on though, I wonder 😉 – SWL

Personal Bugaboo – no pointers to online map services

This is not an edtech related post but I felt the need to rant and well, it’s my blog so I’ll rant if I want to…

It is beyond me how there still exist any institutional websites that offer ‘Contact Pages’ with their address but that don’t also use one the myriad online maps services that exist to point to an online map of their location. You know, handy sites like Yahoo Maps or Mapquest. Maybe these are the same people who think the Internet is simply a ‘fad,’ just like ‘horse-less carriages.’

Unless my relationship with the person is restricted solely to face to face or the phone (which is increasingly rare for me) I basically refuse to write out in long directions to anywhere anymore – instead, I simply point folks to a URL and let them build their own map. For instance, if you ever want to come visit my office, you can find me here. Lucky for me, there is a campus next door to the one I have to visit tomorrow (that visit having prompted the above rant) that gets it. – SWL