{"id":1104,"date":"2013-05-24T15:36:05","date_gmt":"2013-05-24T15:36:05","guid":{"rendered":"http:\/\/faganm.com\/blog\/?p=1104"},"modified":"2013-05-25T00:18:54","modified_gmt":"2013-05-25T00:18:54","slug":"doors-open-toronto-2013-map","status":"publish","type":"post","link":"https:\/\/faganm.com\/blog\/2013\/05\/24\/1104\/","title":{"rendered":"Doors Open Toronto 2013 Map"},"content":{"rendered":"<p><a href=\"https:\/\/www.google.com\/fusiontables\/embedviz?q=select+col3+from+1DEs8VwbXUwuhbCmIcglOXO-x8kYA87lYYOBoqJk&amp;viz=MAP&amp;h=false&amp;lat=43.66674034235299&amp;lng=-79.36535696237789&amp;t=1&amp;z=14&amp;l=col3&amp;y=2&amp;tmplt=2\">Full-size Doors Open Toronto 2013 Map<\/a><\/p>\n<p><iframe loading=\"lazy\" src=\"https:\/\/www.google.com\/fusiontables\/embedviz?q=select+col3+from+1DEs8VwbXUwuhbCmIcglOXO-x8kYA87lYYOBoqJk&amp;viz=MAP&amp;h=false&amp;lat=43.67282457760168&amp;lng=-79.38046316354972&amp;t=1&amp;z=12&amp;l=col3&amp;y=2&amp;tmplt=2\" height=\"400\" width=\"600\" frameborder=\"no\" scrolling=\"no\"><\/iframe><\/p>\n<p>Green = Green Building (may have kids activities)<br \/>\nBlue = Has kids activities (and not a green building)<\/p>\n<p>Steps to make this map (note you can follow just the bold steps):<\/p>\n<ol>\n<li>Thursday: Ooh, it&#8217;s Open Doors Toronto, let&#8217;s see what places there are too<\/li>\n<li>List is way too long and too broad geographically to browse, look on Open Doors website for map<\/li>\n<li>Can&#8217;t find a map, search on Google. Still no map<\/li>\n<li>Give up, starting browsing through the list. Argh<\/li>\n<li>Consider how difficulty it would be to scrape the list, geocode it, and publish it online somewhere. Decide I&#8217;ve got better things to do than spend all day on this<\/li>\n<li>Continue browsing list, continue being annoyed<\/li>\n<li>Friday: tell <a href=\"http:\/\/www.biancasayan.com\/\">Bianca <\/a>about issue, she considers doing the scraping and geocoding<\/li>\n<li>Search on google for a map again&#8230; maybe somebody else made one yesterday. Nope.<\/li>\n<li>Continue browsing through list. Contemplate ease of mapping<\/li>\n<li><strong>Suddenly remember that I already saw that this data was <a href=\"http:\/\/www1.toronto.ca\/wps\/portal\/contentonly?vgnextoid=f7d15d9256be6310VgnVCM1000003dd60f89RCRD&amp;vgnextchannel=1a66e03bb8d1e310VgnVCM10000071d60f89RCRD&amp;vgnextfmt=default\">published on Toronto Open Data<\/a>. Go find it.<\/strong><\/li>\n<li>Note that it&#8217;s in XML. Decide the easiest way to geocode will be <a href=\"http:\/\/www.google.com\/drive\/apps.html#fusiontables\">Google Fusion Tables<\/a>, which accepts CSV among other formats.<\/li>\n<li>Find an <a href=\"http:\/\/xmlgrid.net\/xml2text.html\">online XML-to-CSV converter<\/a> that accepts URL input. This should be quick.<\/li>\n<li>Output is a file, so I save it locally and upload to fusion tables. Wait, the &#8216;location&#8217; field doesn&#8217;t include &#8216;Toronto&#8217;, maybe that&#8217;s a problem. Oh wait again, it only has the first five rows, there has been some import problem<\/li>\n<li><strong>Open Excel, click &#8216;Data&#8217; button on the ribbon, click &#8216;From Web&#8217;, input the URL, wait a second, click the &#8216;table&#8217; (the entire xml doc), and finish the wizard<\/strong><\/li>\n<li><strong>Add a new column, &#8216;full address&#8217;. 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&#8217;ll be.<\/strong><\/li>\n<li>Give up trying to write an excel formula to remove the unit\/floor numbers. <strong>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 &#8220;, Toronto&#8221; to each line<\/strong><\/li>\n<li><strong>Copy and paste the text into the new &#8216;full address&#8217; column<\/strong><\/li>\n<li>Create a new fusion table again, with the same metadata, import the file<\/li>\n<li>Geocode the correct column (quite easy) and observe map. Wohoo! We&#8217;re all done(ish)<\/li>\n<li>Realize that I can tweak the info windows, and spend a few minutes formatting them<\/li>\n<li>Realize that I can change the size and colour, and even image of the icons. Decide to make them bigger<\/li>\n<li>Decide to make the &#8216;green building&#8217; locations have green icons. That needs a &#8216;colour&#8217; column. Attempt to create a new formula column in fusion tables. Fail. Online forums suggests formulas don&#8217;t work with text columns<\/li>\n<li>Go back to original \u00a0excel spreadsheet, <strong>create &#8216;Icon&#8217; column, write a formula to output &#8216;green_large&#8217; for green buildings, &#8216;blue_large&#8217; for everything else that has a kids activity, and &#8216;red_large&#8217; for everything else.<\/strong><\/li>\n<li><strong>Create a new google fusion document all over again, add in the metadata, import the spreadsheet, geocode the rows<\/strong><\/li>\n<li><strong>Modify the infowindow code and the &#8216;map styles&#8217; to get the icon using the &#8216;icon&#8217; column<\/strong><\/li>\n<li>Woo!<\/li>\n<li>Change sharing to public, write this blog post<\/li>\n<\/ol>\n<p>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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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&#8217;s &hellip; <a href=\"https:\/\/faganm.com\/blog\/2013\/05\/24\/1104\/\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1104","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/faganm.com\/blog\/wp-json\/wp\/v2\/posts\/1104","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/faganm.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/faganm.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/faganm.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/faganm.com\/blog\/wp-json\/wp\/v2\/comments?post=1104"}],"version-history":[{"count":10,"href":"https:\/\/faganm.com\/blog\/wp-json\/wp\/v2\/posts\/1104\/revisions"}],"predecessor-version":[{"id":1114,"href":"https:\/\/faganm.com\/blog\/wp-json\/wp\/v2\/posts\/1104\/revisions\/1114"}],"wp:attachment":[{"href":"https:\/\/faganm.com\/blog\/wp-json\/wp\/v2\/media?parent=1104"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/faganm.com\/blog\/wp-json\/wp\/v2\/categories?post=1104"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/faganm.com\/blog\/wp-json\/wp\/v2\/tags?post=1104"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}