Google Fusion Tables API

Getting Started

This document explains how to get started using the Google Fusion Tables API 1.0.

Contents

Introduction

Google Fusion Tables is a web application used for sharing, visualizing, and publishing tabular data. You can upload your own CSV, KML, ODS, XLS, or Google Spreadsheet data to a Fusion Tables table. Once your data is in Fusion Tables, you can collaborate on it with others in real time, publish it for Google Search, create map and chart visualizations for private use or for embedding on websites, filter it according to specific criteria, and update the data behind your visualizations or filters at any time.

The Fusion Tables API allows you to use HTTP requests to programmatically to perform these tasks, which are also available in the Fusion Tables web application:

  • create and delete tables
  • read and modify table metadata such as table and column names and column types
  • insert, update, and delete the rows in a table
  • create, update, and delete settings for certain visualizations
  • query the rows in a table

For example, you can use the Fusion Tables API to apply the same map style and info window appearance to several tables, update the table's data and therefore update embedded visualizations, add and populate a new column for a table, read a public table's metadata, or query a table's contents.

Table structure, metadata, and visualization settings are represented as JSON data structures accessible through RESTful HTTP requests. Row data is handled using a subset of SQL statements sent as HTTP requests, and can be retrieved in either CSV or JSON formats.

Fusion Tables API documentation includes:

  • This document, Getting Started, orients developers to the Fusion Tables API: what it is and how it works.
  • A guide for developers, called, "Using the API," provides step-by-step instructions on how to use the API's common features.
  • A Reference document provides detailed information about the data structures, method calls, and authentication scopes of the API.
  • A Row and Query SQL Reference guide that provides details for the SELECT, INSERT, UPDATE, and DELETE statements used to manage rows and query the table's content.

Note: You may find it helpful to look at some of our sample code and try the available client libraries for the Google Fusion Tables API 1.0.

About REST

REST is a style of software architecture that provides a convenient and consistent approach to requesting and modifying data.

The term REST is short for "Representational State Transfer." In the context of Google APIs, it refers to using HTTP verbs to retrieve and modify representations of data stored by Google.

In a RESTful system, resources are stored in a data store; a client sends a request that the server perform a particular action (such as creating, retrieving, updating, or deleting a resource), and the server performs the action and sends a response, often in the form of a representation of the specified resource.

In Google's RESTful APIs, the client specifies an action using an HTTP verb such as POST, GET, PUT, or DELETE. It specifies a resource by a globally-unique URI of the following form:

https://www.googleapis.com/apiName/apiVersion/resourcePath?parameters

Because all API resources have unique HTTP-accessible URIs, REST enables data caching and is optimized to work with the web's distributed infrastructure.

For more information about REST, you may find the following third-party documents useful:

Before you start

If you're unfamiliar with Google Fusion Tables, read this document and try the basic tutorials before starting to code. This document assumes that you're familiar with web programming concepts and web data formats.

Get a Google account

Make sure that you have a Google account set up. We recommend that you use a separate Google account for development and testing purposes to protect yourself from accidental data loss. If you already have a test account, then you're all set; you can visit the Google Fusion Tables user interface to set up, edit, or view your test data. If you don't, go ahead and create one now for Fusion Tables use.

Learn about authorizing requests and identifying your application

When your application requests private data or resources, the request must be authorized by an authenticated user who has access to that data. When your application requests data or resources from a public or unlisted table, the request doesn't need to be authorized, but it does need to be accompanied by an API key.

For information about how to authorize requests and use API keys, see Authorizing requests and identifying your application.

Migrate your SQL API application to API V1.0

We have put together a migration guide for converting your SQL application code to the new Fusion Tables platform. The guide details what to look out for and how to quickly adapt your application to the new API.

Fusion Tables API overview

The Google Fusion Tables API 1.0 is a RESTful API that allows developers to create and manage Fusion Tables resources such as tables, columns, rows, templates, and styles. Developers use SQL statements to query and manipulate rows and they use the RESTful API to work with tables, columns, styles, and templates.

Basic concepts

Google Fusion Tables is built on the following concepts:

  • Table: Tables consist of data arranged in rows and columns, similar to a spreadsheet. There are three types of tables: base, view, and merged.
    • base: A table created by uploading data from a repository or through inserting rows with SQL SELECT statements. This is the table from which you create views.
    • view: A table created by selecting a subset of columns from a base table with or without filters on the row data. A view is a virtual table, with its own metadata, providing a window into the underlying base table. A view may have a filter applied so that it includes only a subset of rows and columns. Currently, you can only programmatically create a view with the SQL API, not with Fusion Tables API.
    • merged: A table created by combining two or more tables, each of which must have a column containing values that match a key column in the other table(s). Merged tables inherit the column types, data, and edit permissions on data from the base table. You can merge tables owned by different people, but you cannot insert new rows into a merged table.

  • Table ID: The table identifier is an encrypted string value such as 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc.

    There are several ways to get a table's ID:

  • Column: A column within a table. Each column has an ID, a name, and an assigned data type: "STRING", "NUMBER", "DATETIME", or "LOCATION". The column ID is a string value. See Working with columns for details on how the ID is parsed.

  • Template: A layout template that defines the content and appearance of the row data that is displayed when a user clicks on a marker, line, or polygon in a map visualization. According to the Google Maps API documentation, which calls this information an info window, "The info window looks a little like a comic-book word balloon; it has a content area and a tapered stem, where the tip of the stem is at a specified location on the map." You can customize the template by selecting which columns to display and by specifying a custom layout using HTML code.

  • Style: A style definition for map features. Features displayed on a map can be assigned a single style, styled per a column containing style definitions, or styled per a column containing numerical data. You can configure the styles in a map.

  • Row: Fusion Table data is stored in rows, each of which has an internally generated ROWID that identifies it uniquely within a table. To locate a specific row of data, issue an SQL query statement with criteria to match that row. The response includes the row's ROWID, which you can now use to update or delete that row.

Basic operations

For Table, Column, Template, and Style Resources, you can use HTTP requests as described in the following table.

Operation Description HTTP mappings
list Lists all resources of this type. GET on a resource list URI.
get Gets a specific resource. GET on a resource URI.
insert Inserts a new resource (create a new resource). POST on a resource list URI, where you pass in data for a new resource.
update Updates a specific resource. PUT on a resource URI, where you pass in data for the updated resource.
delete Deletes a specific resource. DELETE on a resource URI.

For rows, you can use SQL statements with HTTP requests, as described in the following table. SQL queries are sent to the following URL endpoint as the value of an "sql" parameter: https://www.googleapis.com/fusiontables/v1/query?sql=. See Sending requests for examples.

Operation Description Query format of the "sql" parameter
list Lists all rows within a table. GET with a specific table ID:
  SELECT ROWID FROM <table_id>
get Gets a specific row. GET with a specific table ID and criteria:
  SELECT ROWID FROM <table_id> WHERE <your filter>
insert Inserts a new row into a table. POST with a specific ROWID, where you pass in data for a new row:
  INSERT INTO <table_id> (<column_name> {, <column_name>}*)
    VALUES (<value> {, <value>}*)
update Updates a specific row. POST with a specific ROWID, where you pass in data for the updated row:
  UPDATE <table_id> SET <column_name> = <value> {, <column_name> = <value> }*
    WHERE ROWID = <row_id>
delete Deletes a specific row. POST with a specific ROWID:
  DELETE FROM <table_id> {WHERE ROWID = <row_id>}
You can also use SQL statements to create views and merged tables.

Authentication requirements

The authentication requirements for owners and editors of a table are summarized in the table below. Viewers can only perform simple GET operations on individual resources. See Table access permissions.

Resource
Authentication Required?
List resources Get resource Insert Update Delete
Tables yes
Private tables only
yes yes yes
Columns Private tables only
Private tables only
yes
yes
yes
Templates Private tables only
Private tables only
yes
yes
yes
Styles Private tables only
Private tables only
yes
yes
yes
Rows Private tables only
Private tables only
yes
yes
yes

Sending requests

The Google Fusion Tables API 1.0 supports these approaches to sending requests to the Fusion Tables server:

  • Using REST for tables, columns, styles, and templates
  • Using SQL for rows

Regardless of which you use, the URI is:

https://www.googleapis.com/fusiontables/v1

Sending directly from the browser

If a table is exportable and either public or unlisted, you can send a GET request directly from the URL bar of your browser, but you need to include an API key. Here are a couple examples for you to try.

This example lists all columns in a given table (try this in your browser):

https://www.googleapis.com/fusiontables/v1/tables/1KxVV0wQXhxhMScSDuqr-0Ebf0YEt4m4xzVplKd4/columns?key=your API key

This example runs a query to select all rows in a given table (try this in your browser):

https://www.googleapis.com/fusiontables/v1/query?sql=SELECT * FROM 1KxVV0wQXhxhMScSDuqr-0Ebf0YEt4m4xzVplKd4&key=your API key

Invoking the API for tables, columns, styles, and templates

The supported API 1.0 operations map directly to REST HTTP verbs, as described in Google Fusion Tables API operations.

The specific format for API 1.0 URIs are:

https://www.googleapis.com/fusiontables/v1/resourceID?parameters

where resourceID is the identifier for a resource, and parameters are any parameters to apply to the query. See RESTful URL query parameters.

The format of the resourceID path extensions lets you identify the resource or resource list you're currently operating on, for example:

https://www.googleapis.com/fusiontables/v1/tables
https://www.googleapis.com/fusiontables/v1/tables/tableId
https://www.googleapis.com/fusiontables/v1/tables/tableId/columns
https://www.googleapis.com/fusiontables/v1/tables/tableId/columns/columnId
https://www.googleapis.com/fusiontables/v1/tables/tableId/templates
https://www.googleapis.com/fusiontables/v1/tables/tableId/templates/templateId
https://www.googleapis.com/fusiontables/v1/tables/tableId/styles
https://www.googleapis.com/fusiontables/v1/tables/tableId/styles/styleId

The full set of supported URIs is summarized in the Fusion Tables API Reference document. Here are some examples.

List all templates for table 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc:

GET https://www.googleapis.com/fusiontables/v1/tables/1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc/templates

Get template 1:

GET https://www.googleapis.com/fusiontables/v1/tables/1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc/templates/1

Invoking the API for rows

The specific format for API 1.0 URIs used with SQL statements when handling rows is:

https://www.googleapis.com/fusiontables/v1/query

The format for a SQL statement is:

https://www.googleapis.com/fusiontables/v1/query?sql=query

where query is a valid SQL query. Here are a couple of examples of how this works:

To list all the ROWIDs for table 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc, send an authenticated GET request to this URL with this SQL statement:

https://www.googleapis.com/fusiontables/v1/query?sql=SELECT ROWID FROM 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc

To delete row 201, send an authenticated POST request to this URL with this SQL statement:

https://www.googleapis.com/fusiontables/v1/query?sql=DELETE FROM 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc WHERE ROWID = 201

Calling the API from a browser using JavaScript

You can invoke the Google Fusion Tables API using REST or SQL from JavaScript on public or unlisted tables using the callback query parameter and a callback function. This allows you to write rich applications that display Google Fusion Tables data without writing any server side code.

The following example uses this approach to display all templates for table 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc:

<html>
  <head>
    <title>Google Fusion Tables API Example</title>
  </head>
  <body>
    <div id="content"></div>
    <script>
    function handler(response) {
      for (var i = 0; i < response.items.length; i++) {
        var item = response.items[i];
        // Either show the body or the automatic columns of the template
        if (item.body) {
          document.getElementById("content").innerHTML += "<br>" + item.body;
        } else {
          for (var j = 0; j < item.automaticColumnNames.length; j++) {
            document.getElementById("content").innerHTML += "<br>" + item.automaticColumnNames[j];
          }
        }
      }
    }
    </script>
    <script src="https://www.googleapis.com/fusiontables/v1/tables/1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc/templates?callback=handler&key=YOUR-KEY"></script>
  </body>
</html>

Data formats

The Fusion Tables API allows developers to work with specific data type formats. You can define the format for response data that is returned from a RESTful or SQL call. Google Fusion Tables handles different data types in predefined ways, so you can reliably manipulate the data that is returned.

Specifying the response data format

The default format for API 1.0 response data for a successful API call is JSON typed data. If you are using SQL to query rows, you can choose to receive untyped CSV or JSON (typed or untyped) data.

JSON (JavaScript Object Notation) is a common, language-independent data format that provides a simple text representation of arbitrary data structures. For more information, see json.org.

API Resource Default format Alternative format
Rows Typed JSON Use the alt=csv parameter to receive data in untyped CSV format.
Use the typed=false parameter to receive untyped JSON data.
Tables, Columns, Styles, Templates Typed JSON None

Parsing different data types

If response data is returned as JSON, then all numbers are Typed JSON values by default. You can change the default by using "typed=false" parameter. If you do use "typed=false", or the return format is not JSON, then Fusion Tables returns the original string value.

This table summarizes how numbers and location values are returned from the Google Fusion Tables API 1.0 when typed.

Column data type Returned as Examples
Integer numbers Integers Input as: 0, 1, 3435266
Returned as: 0, 1, 3435266
Other numbers Numeric (double) values Input as: 0.0, 1.0, 3435266.0, 12.0, 0.5
Returned as: 0.0, 1.0, 3435266.0, 12.0, 0.5
Extremely large (>1e16) or small (<1e-6) numbers Scientific notation Input as: 0.000000123, 12345678901234560
Returned as: 1.23e-7, 1.234567890123456E16
Unparsable numbers NaN Input as: anything except a number
Returned as: NaN
Individual latitude and longitude values Numeric (double) values Input as: 37, -122
Returned as: 37.0, or -122.0
Location coordinate pairs (A lat-long pair combined in a single location column) Returned as a UTF-8 string. Parsed out as two numeric (double) values and sorted as a string of the two numbers concatenated. Input as: -73.12345678,40.12345678
Returned as: -73.12345678,40.12345678
Latitude or longitude in the master column of a two-column location Numeric (double) values Input as: 37.42114440, or -122.08530320
Returned as: 37.42114440, or -122.08530320
KML GeoJSON
{
  "geometry": {
    "type": "Point",
    "coordinates": [
      -73.12345678,
      40.12345678
    ]
  }
}

Authentication required

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

Signing you in...

Google Developers needs your permission to do that.