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.