Google Fusion Tables, Alteryx and oh yes... Godzilla!

So you're probably wondering what does Godzilla, Google Fusion Tables (GFT) and Alteryx have in common? Well, GFT and Alteryx have more in common than Godzilla does with either, but just as I stumbled across a Godzilla movie on TV I figured how to pull data from GFT into Alteryx, so I thought it was only appropriate that I give it credit by placing his\her image on this post.

 =(;-)))

Seriously though, if you haven't done much with storing data in the 'cloud', working with GFT is a great and painless way to get comfortable with the process. To get started here are two of my favorite tutorials that cover how to load and edit data placed in GFT, to geocode it and how to create charts, maps, (and heatmaps!) with your data.

The first is Google's own Google Fusion Table Tutorials and the second is a YouTube video by Circle of Blue.

And now back to our regular programming...
Ok, so how do you bring GFT data into Alteryx? The process is rather straight forward yet the formatting is essential - more on that later. Data loaded into GFT can easily be manipulated via the Google Fusion Tables API and one of my favorite utilities, cURL, along with the Run Command Tool. Let's look at this example pointing to a sample table I created of road locations where certain Vermont vehicles were speeding at a particular point in time. If you select the link below a csv file is downloaded containing all records (thus the *) from this GFT which has a numeric identifier of 2745825:

http://www.google.com/fusiontables/api/query?sql=SELECT+*+FROM+2745825"

The API URL request is simply made up of 'http://www.google.com/fusiontables/api/query?sql=' followed by the SQL query you want to run against the data loaded in the GFT identified by its Numeric ID. Here is a slightly more complex example using a SQL WHERE clause:

http://www.google.com/fusiontables/api/query?sql=SELECT+*+FROM+2745825+WHERE+OverLimit+=+'27'

This will request all records where vehicles were travelling greater than 27 miles over the speed limit in this dataset. Configuring the Run Command Tool Properties with cURL to access this data looks like this:

Also remember that I mentioned format earlier? You must remind yourself that the API URL request you use be encoded. This means that the any characters within the URL must be in a format that can be transmitted over the Internet, which explains the 'plus' signs instead of spaces in the SQL query - here's a quick reference:

w3school's URL Encoding Reference Page

If you do not encode the API URL the download will not work. Just replace the desired SQL query shown in the example above with your own and don't forget to reference your GFT Numeric ID. Now you might be wondering how to find the Numeric ID. To do so you must open your GFT, then go to the File pull-down menu and choose About to view the properties for that table (see image below).

You must make certain that the Tables Visibility you are wanting to access is either set to Public or Unlisted. If it is set to Private, you must first authenticate with a username and password to get to the data. Discussing this is a bit more than I wish to cover in this post, yet I will point you to a good reference by Pimin Konstantin Kefaloukos (he goes by Kostas) who has a blog called skipperkongen.dk:

Google Fusion Tables Cheat Sheet

To give you an idea of what can be done with the data you upload into GFT, here is my original file displaying a larger set of Vermont vehicles based on miles over the speed limit as a Heatmap.

Till next time - Enjoy!
Ron @ All About GIS