Google Fusion Tables API

Data Gathering with Fusion Tables

Kathryn Hurley, Developer Programs Engineer, Fusion Tables
December 2011

Let's imagine a scenario where you have to consolidate data from a number of different sources. For example, say you work at a nonprofit that monitors forest clearcutting in Alaska. You turn to colleagues at similar nonprofits and ask, "Wouldn't it be great to have all our information in one place?"

I've heard this idea in various forms. One enthusiastic and very organized person sees how helpful it would be to keep track of all the different forest monitoring projects that are going on in their larger community. They set up a spreadsheet and ask for data.

The nightmare begins when their colleagues start emailing in spreadsheets in different formats, which is bad enough, but then the data needs to be kept up to date too!

Fusion Tables can help tame that nightmare. You can use it to build a simple data platform to gather data from colleagues. Let's say each row in the table is for a different project or organization. You can create a table that contains only that data and you can limit access to the data's owner. This way each colleague has secure access to their data and no one else can touch it. As each person updates their data, the new information is available immediately. No spreadsheet hand-offs or frantic phone calls.

Google.org's Flu Vaccine Finder project is a nice example of data gathering in action. Each flu season, searches on Google.com trigger a map displaying flu vaccine clinics nearby. The clinic locations, hours, and other data come directly from pharmacy stores and participating health organizations.

To set this up in Fusion Tables, create a table with columns for all the data you want to collect plus a column to identify its owner. Then create a view for each colleague or data provider and give them specific permissions to edit their data. It's easy.

What's a Fusion Tables view? It's a virtual subset of data that acts just like its base table, but only displays the selected columns and rows from the base table. Each view is a new table with its own set of permissions. To do this, first define a filter to find only the data rows for a single data provider, then create a view. Since the new view is a virtual subset of the table, any updates to the view are reflected in the original base table.

What are the steps for building an application like this with Fusion Tables?

  1. Create a table with the data fields (columns) you want to gather.
  2. Create a filtered view for each data provider.
  3. Give each data provider editing permission for their view.

That's it.

views

To show you how this works in practice, let's dig into the Flu Vaccine Finder application.

1. Create a table to hold the vaccine clinic data from all data providers. You can use the Fusion Tables UI or CREATE TABLE in the Fusion Tables SQL API to do this. Give the table these columns: Provider, Website, Address, Hours. This results in a table with a new table ID, say 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc, in our example.

The Provider column holds the name of the pharmacy providing the data. We'll use "ExampleMart" here. The other columns are fairly self-explanatory.


2. Second, use the Fusion Tables UI or CREATE VIEW in the Fusion Tables SQL API to create a view that filters all of ExampleMart's data.

CREATE VIEW ExampleMartView AS (SELECT Website, Address, Hours FROM 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc WHERE Provider = 'ExampleMart')

This creates a view with ExampleMart data for these columns:

Website, Address, Hours

3. Finally, use the Google Documents List API to set permissions for the ExampleMartView table so that only ExampleMart can edit it (called writer in the Docs List API). Here's a snippet of Python code (or you can see a more complete code example) to set a editor permission for the user examplemart@examplemart.com. Note: Once you have the table ID for the ExampleMart view, you can use the UI instead of the Docs List API to set these permissions manually.

class DocsListHelper():
  def __init__(self, appname):
    self.client = gdata.docs.client.DocsClient(source=appname)
    self.client.ssl = True  # Force all API requests through HTTPS
  
  # Returns a single doc entry for a given table name
  # If multiple tables have the same name, only the first will be returned
  def _getDocEntryForTable(self, tablename):
    feed = self.client.GetDocList(
        uri="/feeds/default/private/full/-/table?title=%s" % (tablename))
    if not feed.entry:
      return None
    for entry in feed.entry:
      return entry

  # Sets the permissions for a given table name
  def setPermissions(self, tablename, permissions):
    doc_entry = self._getDocEntryForTable(tablename)
    if not doc_entry:
      print "Sorry, this table does not exist"
      return

    for username,type,user_role in permissions:
      scope = gdata.acl.data.AclScope(value=username, type=type)
      role = gdata.acl.data.AclRole(value=user_role)
      acl_entry = gdata.docs.data.Acl(scope=scope, role=role)
   
      new_acl = self.client.Post(acl_entry, doc_entry.GetAclFeedLink().href)
      print "%s %s added as a %s" % \
          (new_acl.scope.type, new_acl.scope.value, new_acl.role.value)

  # Update the permissions for a given table
  docsListHelper = DocsListHelper("FluVaccineFinder")
    new_permissions = [("examplemart@examplemart.com","user","writer")]
    docsListHelper.setPermissions("ExampleMartView", new_permissions)

The user examplemart@examplemart.com now has access to edit and update the ExampleMartView table, which will show up in their list of tables. This user can add or edit rows to it using the Fusion Tables web application or the Fusion Tables API.

All updates made to the view are reflected in the view's base table, so you can use this table to create a front end similar to Flu Vaccine Finder. Now it's up to you to finish building your application and to let your data providers know when and how to access their data.

Add charts and graphs to your app, run spatial queries to find the closest clinics, and include any other creative functionality! :)

Authentication required

You need to be signed in with Google+ to do that.

Signing you in...

Google Developers needs your permission to do that.