Last Friday we had a ViBRANT sponsored workshop about CartoDB, the open source mapping and visualisation product from Madrid/New York based Vizzuality. The context of the workshop was possible integration of CartoDB with the Scratchpads and OBOE projects in the context of visualising biological datasets. The notes for the workshop demonstrations are here and are what the work done here is based on.
Not having a suitable dataset to hand I have been playing with making maps of the photos I have shared on Flickr. Flickr does provide a map view of a user's photographs (here's mine) although it is very limited in functionality - and unless you only have a handful of photographs you can't get a map view of all of your photographs.
I have previously visualised my Flickr stream by hacking the Drupal flickrsync module to save geolocation data with the Location module. Even with clustering the map points for 7,000+ images the results are slow to load: Drupal Flickr map of my photographs. The plus side of this work was that with just modifying the output of the view I could get a CSV file of my Flickr stream which I easily imported into CartoDB.
The basic map produced by CartoDB from this file is below:
Next I wanted to make a map of countries that are represented in my Flickr stream (perhaps I really wanted to play with PostGIS and polygons). This required downloading a shape file of all the countries from thematicmapping and uploading the file to a new table in CartoDB (CartoDB will accept the URL to the zip file so you can do it without downloading the file if you choose). The following SQL was applied to the world countries table:
This results in the following map:
Finally using some PostGIS I was able to make this map a little more accurate by splitting the countries into separate polygons (e.g. separating Hawaii from the continental United States, Northern Ireland from Great Britain).
Here's the new map: