Fusion Tables REST API

This document explains how to get started using the Google Fusion Tables API v2.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 v2.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:

⦁ Building Web Services the REST Way, which is aimed at service providers but provides a good overview of REST

⦁ HTTP 1.1 method definitions; specification for GET, POST, PUT, and DELETE

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.

Fusion Tables API overview

The Google Fusion Tables API v2.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 as1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc. 

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

  ⚬ Choose the File > About menu command in the web application.

  ⚬ Use the API v2.0 to retrieve a list of all tables where the authenticated user is the owner or editor.

  ⚬ Use the Google Docs List API with the table's name (See thecode sample and the Google Docs List API documentation for details).

⦁ 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 theGoogle Maps API documentation, which calls this information aninfo 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 customizethe 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/v2/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 GEToperations 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 v2.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/v2

Sending directly from the browser

If a table is exportable and either public or unlisted, you can send a GETrequest 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/v2/tables/1KxVV0wQXhxhMScSDuqr-0Ebf0YEt4m4xzVplKd4/columns?key=your-api-keykey

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

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

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

The supported API v2.0 operations map directly to REST HTTP verbs, as described in API v2.0 operations.

The specific format for API v2.0 URIs are:

https://www.googleapis.com/fusiontables/v2/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/v2/tables
https://www.googleapis.com/fusiontables/v2/tables/tableId
https://www.googleapis.com/fusiontables/v2/tables/tableId/columns
https://www.googleapis.com/fusiontables/v2/tables/tableId/columns/columnId
https://www.googleapis.com/fusiontables/v2/tables/tableId/templates
https://www.googleapis.com/fusiontables/v2/tables/tableId/templates/templateId
https://www.googleapis.com/fusiontables/v2/tables/tableId/styles
https://www.googleapis.com/fusiontables/v2/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/v2/tables/1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc/templates

Get template 1:

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

Invoking the API for rows

The specific format for API v2.0 URIs used with SQL statements when handling rows is:

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

The format for a SQL statement is:

https://www.googleapis.com/fusiontables/v2/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/v2/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/v2/query?sql=DELETE FROM 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc WHERE ROWID = 201

Calling the API from a browser using JavaScript

You can invoke the API v2.0 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/v2/tables/1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc/templates?callback=handler&key=your-api-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 v2.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 v2.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 ] } }

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License, and code samples are licensed under the Apache 2.0 License. For details, see our Site Policies. Java is a registered trademark of Oracle and/or its affiliates.