Fusion Tables & GeoCommons

Background

Google’s Fusion Tables is a tool that allows users to quickly visualize tabular data. Within it there’s a lot of potential for exposing open data on the web in a free, public, and simple way. We here at GeoIQ are ALL about open, both in software development and geo-data, we all share similar ideas about how critical open data and software are to the web and particularly the “geo/location” tech industry.

So I thought I would do some experimenting this weekend. To be honest I haven’t played with Fusion Tables as much as I should have by now, but this weekend I thought, “why not?”. First off, I decided to try messing around and get some Fusion Tables data mapped in GeoCommons. As it turns out GeoCommons was fully ready for this, as was Fusion Tables, so doing this way all REALLY EASY. Since it was so easy I started, and nearly finished another , related hack, but more on that another day…

Let’s get down to it!

Fusion Tables to GeoCommons

I thought about starting off with a list of the similarities and differences between Fusion Tables and GeoCommons, but I decided I’d rather not. So in short I’m treating Fusion Tables like a simple database in the cloud, essentially just assuming its a source for data with an awesome HTTP based API. Simple enough, and its really not that far off from what Fusion Tables is in reality. Granted there are some solid built in tools for visualizing and filtering data but in many ways its a great source of public data.

GeoCommons is a web platform for collaborative data management, sharing, analysis and visualization. We really enjoy the idea of mapping data dynamically from the web, and we feel that we’ve built a tool that really kicks ass at doing it.

So for the rest of this demo/post I’m going to skip over the details of how Fusion Tables works, and simply treat it like a public source for open data. And Fusion Tables turned out to be my friend in this since it makes it easy to find public tables by providing a clear link to several public tables in left hand navigation list (see below), and searching Fusion Tables provides access to a bunch more tables as well.

http://www.google.com/fusiontables/Home

I’m just going to pick one at random (well almost ) for my demo:

The Global Historical Climate Network Station Inventory

I wanted data with a good number of data points, and this one has ~7k which is good enough to show off the speed of the new GeoCommons 2.0 map rendering. Now that we have some data we need to figure out how to get it out of Fusion Tables and into GeoCommons. We could just export the data from Fusion Tables and upload it into GeoCommons, but that’s not really what we want. We want to keep the data in Fusion Tables, but map it in GeoCommons. This way we can go and edit the data in Fusion Tables and our maps in GeoCommons can be updated without re-dumping and loading the data.

Fusion Tables makes it simple to export data as CSV via its user interface:

Its not exactly a simple task to figure out the correct export URL that they’re using to get the data, format and return it all as CSV, but that’ is the URL we want! GeoCommons can upload any URL pointing to CSV data and maintain memory of how to retrieve the data.  In GeoCommons this means the data source remains a link back to the original data. This makes it possible for us to keep data elsewhere but still access, edit, share, and visualize the data.

To get the URL for a CSV export of a Fusion Tables dataset I simply opened up Charles, the extremely awesome web debugging proxy. Charles logs all network traffic while its open and makes it really easy to see actions and responses across the web. From Charles we can see the “Fusion Tables -> File -> Export” command is simply calling the server with this:

http://www.google.com/fusiontables/exporttable?query=select+col0,+col1,+col2,+col3,+col4,+col5,+col6,+col7,+col8,+col9,+col10,+col11,+col12,+col13,+col14,+col15+from+267862

Awesome! Fusion Tables is just doing some SQL via the URL query string. That’s really easy for us, and there’s certainly no magic involved. This is exactly what we need for our GeoCommons experiment. However, using Charles, and all its awesomeness, might be a tricky thing for some people. There’s got to another way right? Yup. Google of course has a Fusion Tables API, glorious!

The API doesn’t mention the “exporttable” endpoint above though? So Charles still remains a special tool However from the API we can see all of the supported SQL calls for accessing Fusion Tables via the browser. So our export URL to dump the whole table into CSV can now look a little cleaner:

http://www.google.com/fusiontables/exporttable?query=select+*+from+267862

Now let’s shift over to GeoCommons and load this data into a map!

In GeoCommons we sign in, then go to the upload page at: http://geocommons.com/home – from here we have two options for uploading data: either as a file or a URL.

We want to select the URL option. In the URL input space we’re going to copy and paste in our Fusion Tables URL from above, and we’re going to select “Spreadsheet (csv)” from the list of format options. Next click “Continue”.

Since this dataset already has a latitude and longitude GeoCommons needs to do very little to get it ready. Thus we’re placed directly into GeoCommons’ data edit page where we fill out all the crucial metadata for this dataset. It’s important to note that if the Fusion Tables dataset doesn’t have lat/lon columns GeoCommons can still work with it by attempting to either perform a Geo-Join or a Geocode on the data.

Once we’re finished we can click “Save” at the bottom. Now our data are ready to be mapped. Again, in GeoCommons this is simple: we click the “Map Data” button in the upper right corner of the screen in newly saved overlay page. Our data will be loaded into a map and we can begin to style it. Once we save the map we can share it! Here’s the map I made:

http://geocommons.com/maps/76030

This proved to be really easy to do. It took a lot more time to simply write this post than actually load in the data and make a sweet map. Also, I was impressed with the speed on both ends of the chain. In terms of ways we can improve this work, GeoCommons could provide complete access to all of the Fusion Tables data simply by integrating our data searches into the Fusion Tables API. This would be way cool, and would open up access to more data which would be a good thing!