Full-size Doors Open Toronto 2013 Map

Green = Green Building (may have kids activities)

Blue = Has kids activities (and not a green building)

Steps to make this map (note you can follow just the bold steps):

Thursday: Ooh, it’s Open Doors Toronto, let’s see what places there are too List is way too long and too broad geographically to browse, look on Open Doors website for map Can’t find a map, search on Google. Still no map Give up, starting browsing through the list. Argh Consider how difficulty it would be to scrape the list, geocode it, and publish it online somewhere. Decide I’ve got better things to do than spend all day on this Continue browsing list, continue being annoyed Friday: tell Bianca about issue, she considers doing the scraping and geocoding Search on google for a map again… maybe somebody else made one yesterday. Nope. Continue browsing through list. Contemplate ease of mapping Suddenly remember that I already saw that this data was published on Toronto Open Data. Go find it. Note that it’s in XML. Decide the easiest way to geocode will be Google Fusion Tables, which accepts CSV among other formats. Find an online XML-to-CSV converter that accepts URL input. This should be quick. Output is a file, so I save it locally and upload to fusion tables. Wait, the ‘location’ field doesn’t include ‘Toronto’, maybe that’s a problem. Oh wait again, it only has the first five rows, there has been some import problem Open Excel, click ‘Data’ button on the ribbon, click ‘From Web’, input the URL, wait a second, click the ‘table’ (the entire xml doc), and finish the wizard Add a new column, ‘full address’. Suddenly realize that the address field already includes unit #s and floor #s, and decide to remove them to improve geocoding, although not sure how much of a problem they’ll be. Give up trying to write an excel formula to remove the unit/floor numbers. Copy the entire address column and paste into Textpad. Hit F8 a few times to use regular expressions to remove the unit and floor numbers and append “, Toronto” to each line Copy and paste the text into the new ‘full address’ column Create a new fusion table again, with the same metadata, import the file Geocode the correct column (quite easy) and observe map. Wohoo! We’re all done(ish) Realize that I can tweak the info windows, and spend a few minutes formatting them Realize that I can change the size and colour, and even image of the icons. Decide to make them bigger Decide to make the ‘green building’ locations have green icons. That needs a ‘colour’ column. Attempt to create a new formula column in fusion tables. Fail. Online forums suggests formulas don’t work with text columns Go back to original excel spreadsheet, create ‘Icon’ column, write a formula to output ‘green_large’ for green buildings, ‘blue_large’ for everything else that has a kids activity, and ‘red_large’ for everything else. Create a new google fusion document all over again, add in the metadata, import the spreadsheet, geocode the rows Modify the infowindow code and the ‘map styles’ to get the icon using the ‘icon’ column Woo! Change sharing to public, write this blog post

Note that obviously the data fields I selected for the info windows and for the icon colours are subjective. An alternative might be to create a separate map for saturday and sunday, or to highlight wheelchair accessibility.