Doors Open Toronto 2013 Map

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):

  1. Thursday: Ooh, it’s Open Doors Toronto, let’s see what places there are too
  2. List is way too long and too broad geographically to browse, look on Open Doors website for map
  3. Can’t find a map, search on Google. Still no map
  4. Give up, starting browsing through the list. Argh
  5. 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
  6. Continue browsing list, continue being annoyed
  7. Friday: tell Bianca about issue, she considers doing the scraping and geocoding
  8. Search on google for a map again… maybe somebody else made one yesterday. Nope.
  9. Continue browsing through list. Contemplate ease of mapping
  10. Suddenly remember that I already saw that this data was published on Toronto Open Data. Go find it.
  11. Note that it’s in XML. Decide the easiest way to geocode will be Google Fusion Tables, which accepts CSV among other formats.
  12. Find an online XML-to-CSV converter that accepts URL input. This should be quick.
  13. 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
  14. 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
  15. 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.
  16. 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
  17. Copy and paste the text into the new ‘full address’ column
  18. Create a new fusion table again, with the same metadata, import the file
  19. Geocode the correct column (quite easy) and observe map. Wohoo! We’re all done(ish)
  20. Realize that I can tweak the info windows, and spend a few minutes formatting them
  21. Realize that I can change the size and colour, and even image of the icons. Decide to make them bigger
  22. 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
  23. 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.
  24. Create a new google fusion document all over again, add in the metadata, import the spreadsheet, geocode the rows
  25. Modify the infowindow code and the ‘map styles’ to get the icon using the ‘icon’ column
  26. Woo!
  27. 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.

This entry was posted in Uncategorized. Bookmark the permalink.