The Google Fusion Tables API v1 has been deprecated as of May 3rd, 2016. The API will continue to work until August 1st, 2017, and will shut down on that date. A migration guide is available outlining the steps to take to migrate to v2.

Using the API

This document describes how to use the common features of the Fusion Tables API.

Contents

  1. Introduction
    1. Identifying your application and authorizing requests
    2. Table access permissions
  2. Working with tables
    1. Retrieving a list of tables
    2. Retrieving a table
    3. Creating a table
    4. Updating a table
    5. Deleting a table
    6. Copying a table
    7. Importing rows into a table
  3. Working with columns
    1. Retrieving a list of columns
    2. Retrieving a column
    3. Creating a column
    4. Updating a column
    5. Deleting a column
  4. Working with templates
    1. Retrieving a list of templates
    2. Retrieving a template
    3. Creating a template
    4. Updating a template
    5. Deleting a template
  5. Working with styles
    1. Retrieving a list of styles
    2. Retrieving a style
    3. Creating a style
    4. Updating a style
    5. Deleting a style
  6. Working with rows
    1. Retrieving a row ID or a list of row IDs
    2. Querying for data
    3. Inserting a row
    4. Updating a row
    5. Deleting a row
  7. URL query parameters
  8. Working with geographic data
  9. Quotas
  10. Reserved words

Introduction

This document is intended for developers who want to write applications that can interact with the Fusion Tables API using REST. Google Fusion Tables is data management web application in the cloud for storing and visualizing your data.

If you're unfamiliar with Google Fusion Tables concepts, you should read Getting Started before starting to code.

Identifying your application and authorizing requests

Every request your application sends to the Fusion Tables API needs to identify your application to Google. There are two ways to identify your application: using an OAuth 2.0 token (which also authorizes the request) and/or using the application's API key. Here's how to determine which of those options to use:

  • If the request requires authorization (such as a request for an individual's private data), then the application must provide an OAuth 2.0 token with the request. The application may also provide the API key, but it doesn't have to.
  • If the request doesn't require authorization (such as a request for public data), then the application must provide either the API key or an OAuth 2.0 token, or both—whatever option is most convenient for you.

About authorization protocols

Your application must use OAuth 2.0 to authorize requests. No other authorization protocols are supported. If your application uses Google Sign-In, some aspects of authorization are handled for you.

Authorizing requests with OAuth 2.0

Requests to the Fusion Tables API for non-public user data must be authorized by an authenticated user.

This process is facilitated with an OAuth client ID.

Get an OAuth client ID

Or create one in the Credentials page.

The details of the authorization process, or "flow," for OAuth 2.0 vary somewhat depending on what kind of application you're writing. The following general process applies to all application types:

  1. When your application needs access to user data, it asks Google for a particular scope of access.
  2. Google displays a consent screen to the user, asking them to authorize your application to request some of their data.
  3. If the user approves, then Google gives your application a short-lived access token.
  4. Your application requests user data, attaching the access token to the request.
  5. If Google determines that your request and the token are valid, it returns the requested data.

Some flows include additional steps, such as using refresh tokens to acquire new access tokens. For detailed information about flows for various types of applications, see Google's OAuth 2.0 documentation.

Here's the OAuth 2.0 scope information for the Fusion Tables API:

Scope Meaning
https://www.googleapis.com/auth/fusiontables Read/write access.
https://www.googleapis.com/auth/fusiontables.readonly Read-only access.

To request access using OAuth 2.0, your application needs the scope information, as well as information that Google supplies when you register your application (such as the client ID and the client secret).

Tip: The Google APIs client libraries can handle some of the authorization process for you. They are available for a variety of programming languages; check the page with libraries and samples for more details.

Acquiring and using an API key

Requests to the Fusion Tables API for public data must be accompanied by an identifier, which can be an API key or an access token.

Get a Key

Or create one in the Credentials page.

After you have an API key, your application can append the query parameter key=yourAPIKey to all request URLs.

The API key is safe for embedding in URLs; it doesn't need any encoding.

Note: Check the table's permission levels and set appropriate authorizations to ensure your code can succeed with the type of access you want.

If a table is marked as exportable and either public or unlisted, the request can be written directly in the URL bar of your browser by using an API key. For example:

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

Table access permissions

The default settings for a new table is to be private and exportable. Owners and editors can control access to a table in these ways:

  • Private.
    Only the owner and editors have access to your table. Visualizations embedded in other websites and Google Earth network links cannot authenticate to Fusion Tables when they call for data, so the table must be Public or Unlisted for these features to work.
    To change: In the Docs List API, change ACLs to allow "default" type, which makes the table publicly shared with all users. In the web application, click the Share button and select Public in the Sharing settings dialog box.
  • Public.
    No authentication required for read-only HTTP GET access, provided the table is exportable.
    To change: In the Docs List API, change ACLs to limit access by user role or type. In the web application, click the Share button and select Private in the Sharing settings dialog box.
  • Unlisted.
    No authentication required for read-only HTTP GET access, provided the table is exportable. Embedded visualizations and Google Earth network links have access, but your table is not included in Fusion Tables public search and it is not suggested to search engines for indexing although they may discover the URL through links you create online.
    To change: in the web application, click the Share button and deselect the "Anyone with the link" radio button in the Sharing settings dialog box to make the table completely private or completely public. You cannot make this change in the API.
  • Exportable. 
    Anyone can download the table data and authentication is not required for read-only HTTP GET access.
    To change: In the Fusion Tables API, change the table property to be "isExportable = false." In the web application, choose Edit > Modify Table Info and deselect "exportable" checkbox. A table that is public or unlisted but that is not exportable allows read-only access but does not allow data downloading.

Working with tables

Fusion Tables offers a way to easily create, modify, and visualize tables of data. You can import data in .csv or .kml and other file formats or create a table from scratch. In Fusion Tables, data is contained in a table resource that has various columns of data. You can use the API to manage the data columns and to customize the info windows and styles that are used when visualizing the data. A table resource specifies the columns and attributes associated with the table. All tables have a unique tableId that you use to manage the table and its columns, templates, and styles.

Retrieving a list of tables

Listing tables is useful because it provides the table ID and column names of tables that are necessary for other calls. You can retrieve the list of tables a user owns by sending an HTTP GET request to the URI with the following format:

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

This list request does not include public tables or tables that have been shared with the user at any access level. By default, twenty-five results are returned. Use the maxResults parameter to change the number of results returned. Use the pageToken parameters to return additional pages of results.

Syntax

Request

Here is an example of requesting all tables an authenticated user owns:

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

Let's say the user owns two tables: The first table has two columns and the second table has three columns. Each column is defined by its name and type. Each table also has a description parameter that further describes the contents of the table. The isExportable parameter is a boolean value that is set to true by default. You can set it to false to prevent others from exporting the table or accessing it through the API.

Response

If the request succeeds, the server responds with the 200 OK HTTP status code and the list of tables:

200 OK

{
 "kind": "fusiontables#tableList",
 "items": [
  {
   "kind": "fusiontables#table",
   "tableId": "1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc",
   "name": "Insects",
   "columns": [
    {
     "kind": "fusiontables#column",
     "columnId": 0,
     "name": "Species",
     "type": "STRING"
    },
    {
     "kind": "fusiontables#column",
     "columnId": 1,
     "name": "Elevation",
     "type": "NUMBER"
    },
    {
     "kind": "fusiontables#column",
     "columnId": 2,
     "name": "Year",
     "type": "DATETIME"
    }
   ],
   "description": "Insect tracking information.",
   "isExportable": true
  },
  {
   "kind": "fusiontables#table",
   "tableId": "1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc",
   "name": "Mammals",
   "columns": [
    {
     "kind": "fusiontables#column",
     "columnId": 0,
     "name": "Group Size",
     "type": "NUMBER"
    },
    {
     "kind": "fusiontables#column",
     "columnId": 1,
     "name": "Based In",
     "type": "LOCATION"
    },
   ]
   "description": "Table monitoring mammals across the Western US.",
   "isExportable": true
  }
 ]
}

Retrieving a table

You can retrieve a table by sending an HTTP GET request with its table ID to the URI with the following format:

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

Syntax

Request

Here is an example requesting table with id 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc:

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

Response

If the request succeeds, the server responds with the 200 OK HTTP status code and the requested table:

200 OK

{
 "kind": "fusiontables#table",
 "tableId": "1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc",
 "name": "Insects,
 "columns": [
  {
   "kind": "fusiontables#column",
   "columnId": 0,
   "name": "Species",
   "type": "STRING"
  },
  {
   "kind": "fusiontables#column",
   "columnId": 1,
   "name": "Elevation",
   "type": "NUMBER"
  },
  {
   "kind": "fusiontables#column",
   "columnId": 2,
   "name": "Year",
   "type": "DATETIME"
  }
 ],
 "description": "Insect Tracking Information.",
 "isExportable": true
}

Creating a table

You can create a new base table by sending an authenticated HTTP POST request to the URI with the following format:

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

When creating a new table, you must supply a representation of the new table as the message body. Note that for a new table:

  • The server creates a "tableId" in response to your POST request. You cannot modify this value.
  • The "columns" section must contain at least one column. Tables with zero columns are invalid and return an error.
  • The required "name" property must specify a name for the table.

Syntax

Request

Here is an example of a POST request that creates a new table with three columns:

POST https://www.googleapis.com/fusiontables/v1/tables
Authorization: /* auth token here */
Content-Type: application/json

{
 "name": "Insects",
 "columns": [
  {
   "name": "Species",
   "type": "STRING"
  },
  {
   "name": "Elevation",
   "type": "NUMBER"
  },
  {
   "name": "Year",
   "type": "DATETIME"
  }
 ],
 "description": "Insect Tracking Information.",
 "isExportable": true
}

Response

If the request succeeds, the server responds with the 200 OK HTTP status code and the new table data created. This request created a new table with tableId 1e7y6mtqv892233322222_bbbbbbbbb_CvWhg9gc.

200 OK

{
 "kind": "fusiontables#table",
 "tableId": "1e7y6mtqv892233322222_bbbbbbbbb_CvWhg9gc",
 "name": "Insects",
 "columns": [
  {
   "kind": "fusiontables#column",
   "columnId": 0,
   "name": "Species",
   "type": "STRING"
  },
  {
   "kind": "fusiontables#column",
   "columnId": 1,
   "name": "Elevation",
   "type": "NUMBER"
  },
  {
   "kind": "fusiontables#column",
   "columnId": 2,
   "name": "Year",
   "type": "DATETIME"
  }
 ],
 "description": "Insect Tracking Information.",
 "isExportable": true
}

Updating a table

To update an existing table, follow these steps:

  1. Make sure you have the correct permissions for the table.
  2. Retrieve the table you want to update by using its table ID.
  3. Modify the properties you want to change. You cannot modify the "kind" properties or the values in the "columns[]" property.
  4. Send a PUT request to the resource URI and supply the modified resource as the message body.

The URI for the Table Resource has the following format: 

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

Syntax

Request

Here is an example of updating table 1e7y6mtqv892233322222_bbbbbbbbb_CvWhg9gc, which applies changes to the name and description of the table.

PUT https://www.googleapis.com/fusiontables/v1/tables
Authorization: /* auth token here */
Content-Type: application/json

{
 "name": "Insects of South America",
 "columns": [
  {
   "kind": "fusiontables#column",
   "columnId": 0,
   "name": "Family",
   "type": "STRING"
  },
  {
   "kind": "fusiontables#column",
   "columnId": 1,
   "name": "Approx Size of Group",
   "type": "NUMBER"
  },
  {
   "kind": "fusiontables#column",
   "columnId": 2,
   "name": "Date of Eclosion",
   "type": "DATETIME"
  }
 ],
 "description": "The table tracks insects in Sth America only.",
 "isExportable": true
}

Response

If the request succeeds, the server responds with a 200 OK status code and the full representation of the modified resource.

Deleting a table

To delete an existing table, send an authenticated DELETE HTTP request to the resource URI using the table's table ID. Tables may only be deleted by their owner.

The URI for the Table Resource has the following format:

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

Syntax

Request

Here is an example that deletes the table created in the previous section. No body is required in the request.

DELETE https://www.googleapis.com/fusiontables/v1/tables/1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc
Authorization: /* auth token here */

Response

If the request succeeds, the server responds with a 204 No Content status code.

Copying a table

To copy a table, send an authenticated POST HTTP request to the following URI: 

https://www.googleapis.com/fusiontables/v1/tables/tableId/copy
You must have permission to view the table, and the table must be exportable. This operation will copy the table schema, metadata, and rows.

Syntax

Request

Here is an example of a request to copy table 1e7y6mtqv892233322222_bbbbbbbbb_CvWhg9gc:

POST https://www.googleapis.com/fusiontables/v1/tables/1e7y6mtqv892233322222_bbbbbbbbb_CvWhg9gc/copy
Authorization: /* auth token here */

Response

If the request succeeds, the server responds with the 200 OK HTTP status code and the definition of the copied table. This request created a new table with table with tableId ai378sk_jeidlckswe_ccccccccc_29d9Ika.

200 OK

{
 "kind": "fusiontables#table",
 "tableId": "ai378sk_jeidlckswe_ccccccccc_29d9Ika",
 "name": "Copy of Insects",
 "columns": [
  {
   "kind": "fusiontables#column",
   "columnId": 0,
   "name": "Species",
   "type": "STRING"
  },
  {
   "kind": "fusiontables#column",
   "columnId": 1,
   "name": "Elevation",
   "type": "NUMBER"
  },
  {
   "kind": "fusiontables#column",
   "columnId": 2,
   "name": "Year",
   "type": "DATETIME"
  }
 ],
 "description": "Copied at Tue Oct 16 14:54:29 PDT 2012 from http://www.google.com/fusiontables/DataSource?docid=1e7y6mtqv892233322222_bbbbbbbbb_CvWhg9gc.  Insect Tracking Information.",
 "isExportable": true
}

Importing rows into a table

To import more rows into an existing table, send an authenticated POST HTTP request to the following URI (note the upload in the URI below): 

https://www.googleapis.com/upload/fusiontables/v1/tables/tableId/import
You must supply the row data in the message body. The row data should be CSV formatted data, though you may specify alternative delimiters. See the reference documentation for more details.

Syntax

Request

Here is an example of importing more rows into table 1e7y6mtqv892233322222_bbbbbbbbb_CvWhg9gc, 

POST https://www.googleapis.com/fusiontables/v1/tables/1e7y6mtqv892233322222_bbbbbbbbb_CvWhg9gc/import
Authorization: /* auth token here */
Content-Type: application/octet-stream

Moth, 30, 2012
Grass Spider, 12.2, 2011

Response

If the request succeeds, the server responds with the 200 OK HTTP status code and the number of rows it received. Once the response is returned, the rows will be imported asynchronously in the background.

200 OK

{
 "kind": "fusiontables#import",
 "numRowsReceived": "2"
}

Working with columns

Fusion Tables also offers a way to modify columns for a given Fusion Table. A column resource specifies the different attributes associated with a column in a table: ID, name, and type. Column types can be "STRING", "NUMBER", "DATETIME", or "LOCATION".

Note: You cannot use the API to programmatically modify columns that are two-column locations, but you can use the Fusion Tables web application to modify this once the table has been created.

Parsing of the column ID

The column ID is a string value. If the ID can be parsed as an integer, Fusion Tables assumes the value is a column ID and retrieves the column with that ID. If it cannot be parsed as an integer, it is assumed to be a name and Fusion Tables retrieves the column with that name. If there are multiple columns with the same name, the one with the lowest column ID is retrieved.

If a column has a name that is an integer, you can only access it through the ID and not through its name. For example, if you have a table with these two columns:

  • First column: ID 0, name "1"
  • Second column: ID 1, name "foo"

If you try to get the column with column ID=1, you'll get the second column, not the first. To get the first, you must use column ID=0.

Retrieving a list of columns

You can retrieve a list of columns for a table, given its table ID, by sending an HTTP GET request to the URI with the following format:

https://www.googleapis.com/fusiontables/v1/tables/tableId/columns

The list request returns up to 50 results by default. Use the maxResults parameter to change the number of results returned. Use the pageToken parameter to page through results.

Syntax

Request

Here is an example requesting all columns from table 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc:

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

The table has three columns: The columns' names are "Mammal Type", "Group Size", and "Year 1st Tracked". The columns have type "STRING", "NUMBER", "DATETIME" respectively.

Response

If the request succeeds, the server responds with the 200 OK HTTP status code and the list of columns for the table:

200 OK

{
 "kind": "fusiontables#columnList",
 "items": [
  {
   "kind": "fusiontables#column",
   "columnId": 0,
   "name": "Mammal Type",
   "type": "STRING"
  },
  {
   "kind": "fusiontables#column",
   "columnId": 1,
   "name": "Group Size",
   "type": "NUMBER"
  },
  {
   "kind": "fusiontables#column",
   "columnId": 2,
   "name": "Year 1st Tracked",
   "type": "DATETIME"
  }
 ]
}

Retrieving a column

You can retrieve a single column within a table by sending an HTTP GET request with its table ID and column ID to the URI with the following format:

https://www.googleapis.com/fusiontables/v1/tables/tableId/columns/columnId

Syntax

Request

Here is an example requesting column 1 from table 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc:

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

Response

If the request succeeds, the server responds with the 200 OK HTTP status code and the requested column:

200 OK

{
  "kind": "fusiontables#column",
  "columnId": 1,
  "name": "Group Size",
  "type": "NUMBER"
}

Creating a column

You can insert a new column into a base table, given its table ID, by sending an HTTP POST request to the URI with the following format:

https://www.googleapis.com/fusiontables/v1/tables/tableId/columns

When creating a new column, you must supply a representation of the new column as the message body. Note that for a new column:

  • The server creates a "columnId" in response to your POST request and you cannot modify this ID.
  • The column must be assigned a "type" specifying its data type: "STRING", "NUMBER", "DATETIME", or "LOCATION".

Note: You cannot insert a new column into a view or merged table.

Note: You cannot use the Fusion Tables API to create or update columns with type "LOCATION" that have a latitude in one column and a longitude in another and are designated as a "two column location".

Syntax

Request

Here is an example of a POST request that creates a new column of "type" "STRING":

POST https://www.googleapis.com/fusiontables/v1/tables/1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc/columns
Authorization: /* auth token here */
Content-Type: application/json

{
 "name": "Monitoring Hardware Used",
 "type": "STRING"
}

Response

If the request succeeds, the server responds with the 200 OK HTTP status code and the new table data created. This request created a new column in table 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc with columnId 3.

200 OK

{
  "kind": "fusiontables#column",
  "columnId": 3,
  "name": "Monitoring Hardware Used",
  "type": "STRING"
}

Updating a column

To update an existing column, you need to follow these steps:

  1. Retrieve the resource that you want to update. This can be done by making a GET request for a specific column in a table, given its table ID. See Retrieving a column.
  2. Modify the properties you want to change. Properties that can be modified include the name and type of the column. You cannot use the Fusion Tables API to change a column's type in views and merged tables.
  3. Send a PUT request to the resource URI and supply the modified resource as the message body.

The URI for the Column Resource has the following format:

https://www.googleapis.com/fusiontables/v1/tables/tableId/columns/columnId

Note: You cannot use the Fusion Tables API to create or update columns with type "LOCATION" that have a latitude in one column and a longitude in another and are designated as a " two column location". Requests to modify the "type" of any such column will return a 400 Bad Request error.

Syntax

Request

Here is an example of updating column 1 in table 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc, which changes the column "name" from "Size of Group" to "Dominant Markings" and changes the "type" from "NUMBER" to "STRING". 

PUT https://www.googleapis.com/fusiontables/v1/tables/1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc/columns/1
Authorization: /* auth token here */
Content-Type: application/json

{
 "name": "Dominant Markings",
 "type": "STRING"
}

Response

If the request succeeds, the server responds with a 200 OK status code and the full representation of the modified resource.

Deleting a column

To delete an existing column, send a DELETE request to the resource URI. You need to identify the table's table ID.

Note: You cannot use the Fusion Tables API to delete columns from views and merged tables.

The URI for the Column Resource has the following format:

https://www.googleapis.com/fusiontables/v1/tables/tableId/columns/columnId

Syntax

Request

Here is an example that deletes the column created in the previous section. Notice that no body is required.

https://www.google.com/fusiontables/v1/tables/1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc/columns/1
Authorization: /* auth token here */

Response

If the request succeeds, the server responds with a 204 No Content status code.

Working with Templates

You can use the Google Fusion Tables API v1.0 to customize the info-window template in a similar way to the Fusion Tables web application. See Customize info windows.

By default, a table has one default template for displaying a map visualization within the Fusion Tables web application. The template may use the set of automatic columns from the table or it may contain custom HTML code. In either case, you can use the Fusion Tables API to change the default template for a table and to create and update other templates. Updates to a table's default template are reflected in the web application. Maps embedded elsewhere using the Google Maps API can specify different templates.

Templates can be created using a list of automatic columns or by using custom HTML with inline CSS. When defining the custom HTML, use curly brackets around the column name to pull the data from that column and row into the template.

There are several code samples that customize info window templates. You may find it helpful to step through their code.

Retrieving a list of templates

You can retrieve a list of templates for a table, given its table ID, by sending an HTTP GET request to the URI with the following format:

https://www.googleapis.com/fusiontables/v1/tables/tableId/templates

Syntax

Request

Here is an example requesting all templates from table 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc:

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

Response

If the request succeeds, the server responds with a 200 OK HTTP status code and the list of templates:

200 OK

{
 "kind": "fusiontables#templateList",
 "items": [
  {
   "kind": "fusiontables#template",
   "templateId": 1,
   "tableId": "1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc",
   "name": "Simple template",
   "isDefaultForTable": false,
   "body": "<div class='googft-info-window' style='font-family: sans-serif'>\n<b>description:</b> {description}<br>\n<b>name:</b> {name}\n</div>"
  },
  {
   "kind": "fusiontables#template",
   "templateId": 2,
   "tableId": "1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc",
   "name": "Template with address",
   "isDefaultForTable": true,
   "automaticColumns": [
    {
      "name": "description"
    },
    {
      "name": "name"
    }
   ]
  }
 ]
}

Note that the text of the body property is returned in Unicode. Passing the body through a JSON parser handles the encoding automatically.

Retrieving a template

You can retrieve a particular template resource in a table, given its table ID, by sending an HTTP GET request to its URI. The URI for a Template Resource has the following format:

https://www.googleapis.com/fusiontables/v1/tables/tableId/templates/templateId

Syntax

Request

Here is an example requesting template 1 from table 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc:

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

Response

If the request succeeds, the server responds with a 200 OK HTTP status code and the template description:

200 OK

{
 "kind": "fusiontables#template",
 "templateId": 1,
 "tableId": "1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc",
 "name": "Template with images",
 "body": "<div class='googft-info-window' style='font-family: sans-serif'>\n<b>description:</b> {description}<br>\n<b>name:</b> {name}\n</div>"
}

Note that the text of the body property is returned in Unicode. Passing the body through a JSON parser handles the encoding automatically.

Creating a template

You can create a new template for a table, given its table ID, by sending an HTTP POST request to the template URI:

https://www.googleapis.com/fusiontables/v1/tables/tableId/templates

When creating a new template, you must supply a representation of the new template as the message body. Note that for a new template:

  • The server creates a "templateId" in response to your POST request and you cannot modify this ID.
  • The "tableId" and one of "body" or "automaticColumns" are required.
  • The optional "name" property allows you to give the template a name.

Syntax

Request

Here is an example of a POST request that creates a new template for table 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc:

POST https://www.googleapis.com/fusiontables/v1/tables/1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc/templates
Authorization: /* auth token here */
Content-Type: application/json

{
 "name": "template-3",
 "body": "<p><b>{name}</b>: {description}</p>"
}

Response

If the request succeeds, the server responds with the 200 OK HTTP status code and the full representation of the new Template Resource:

200 OK

{
 "kind": "fusiontables#template",
 "templateId": 3,
 "tableId": "1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc",
 "name": "template-3",
 "body": "<p><b>{name}</b>: {description}</p>"
}

Updating a template

To update an existing template in a table, given its table ID and template ID, you need to follow these steps:

  1. Retrieve the template that you want to update.
  2. Modify the properties you want to change. You cannot modify the kind, templateId, or tableId properties for a template. These are set by the Fusion Tables API.
  3. Send a PUT request to the resource URI and supply the modified resource as the message body.

The URI for the Template Resource has the following format:

https://www.googleapis.com/fusiontables/v1/tables/tableId/templates/templateId

Syntax

Request

Here is an example that updates the template created in the previous section. The change sets a body for the template instead of automatic columns.

PUT https://www.googleapis.com/fusiontables/v1/tables/1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc/templates/2
Authorization: /* auth token here */
Content-Type: application/json

{
 "name": "template-3",
 "body": "<p><b>{name}</b><br />{description}</p>"
}

Response

If the request succeeds, the server responds with a 200 OK status code and the full representation of the modified resource.

Deleting a template

To delete an existing template in a table, given its table ID, send a DELETE request to the resource URI.

The URI for the Template Resource has the following format:

https://www.googleapis.com/fusiontables/v1/tables/tableId/templates/templateId

Syntax

Request

Here is an example that deletes the template created in the previous section. Notice that no body is required.

DELETE https://www.googleapis.com/fusiontables/v1/tables/1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc/templates/2
Authorization: /* auth token here */

Response

If the request succeeds, the server responds with a 204 No Content status code.

Working with styles

Fusion Tables offers different ways to style map features for a given table. Map features can be points, lines, and polygons. See Working with geographic data for the types of location information supported in LOCATION columns. You can customize the appearance of features on the map by setting styles in the application or programmatically. You can customize the appearance of features on the map by setting styles in Fusion Tables web application or programmatically.

There are several code samples that customize map styles. You may find it helpful to step through their code.

There are different ways to visualize data on a map:

  • By using the Fusion Tables web application
  • By copying the "Get embeddable code" and pasting an iframe with the map visualization into another website
  • By using the Google Maps API's Fusion Tables Layer with Javascript to integrate the map into a web page

Stored styles

By default, a table has one style for displaying a map visualization within the Fusion Tables web application. You can use the Google Fusion Tables API v1.0 to change this default for a table and to create and update other styles. A single table can have multiple Style Resources and you can define one of them as a default style for a table by setting the "isDefaultForTable" parameter for that Style Resource.

A stored style is a Style Resource created and managed by the Fusion Tables API. To select a stored style to use for styling the features on the map, set the "styleId" property in the Fusion Tables Layer object to its ID. The Google Maps API supports up to five Fusion Tables Layers in one map, and each may specify its own styleId. The sample code page has a useful example of its usage. Syntax is as follows:

var layer = new google.maps.FusionTablesLayer({
    query: {
      select: 'Address',
      from: 297050
    },
    map: map,
    styleId: 1
 });

A map within the Fusion Tables web application or an embedded iframe uses the default stored style for the table. You can use Fusion Tables API 1.0 to edit this default stored style and create new styles.

Dynamic styles

Dynamic style rules, specified using the "styles" property in the Maps API, override the default style or styleId for a layer.

Note: Since dynamic styles are not stored and not available through the Fusion Tables API, all styles mentioned in this document refer to stored styles.

Choosing a style

Maps created using the Google Maps API's Fusion Tables Layer can be styled in several ways:

  • Using the default stored style for the table, as above.
  • Applying a different stored style using the "styleId" parameter in the FusionTablesLayer's Javascript
  • Specifying dynamic styling using the "styles" parameter in the FusionTablesLayer object. These styles are not stored with the table and are suited to dynamically changing the map display in response to users' interactions with the webpage.

An article that discusses the advantages of each type of style.

Considerations

Fusion Tables stored styles (styleId):

  • one style ID per layer
  • multiple layers can each have their own style ID
  • overridden by dynamic map styling applied to the same layer

Maps API dynamic map styles (styles):

  • one dynamic style specification per map
  • only one layer can have dynamic styles, although that styles specification can define up to five styles
  • overrides stored styles as well as styles set in the web application

Retrieving a list of styles

You can retrieve the list of styles for a table, given its table ID, by sending an HTTP GET request to the URI with the following format:

https://www.googleapis.com/fusiontables/v1/tables/tableId/styles

Syntax

Request

Here is an example requesting all styles from table 1e7y6mtqv893333333333_ccccccccc_CvWhg9gc:

GET https://www.googleapis.com/fusiontables/v1/tables/1e7y6mtqv893333333333_ccccccccc_CvWhg9gc/styles

The table has two styles. The first defines a marker option setting all the markers to "small_red" and a polyline option that sets a gradient with a range of 1 to 100 on the values in the column named population and also sets a stroke weight of 2px. The second style sets the fill color of all polygons to #0000FF with an opacity of 0.2, meaning 20% opaque.

Response

If the request succeeds, the server responds with the 200 OK HTTP status code and the list of styles for the table:

200 OK

{
 "kind": "fusiontables#styleSettingList",
 "items": [
  {
   "kind": "fusiontables#styleSetting",
   "tableId": 1e7y6mtqv893333333333_ccccccccc_CvWhg9gc,
   "styleId": 1,
   "name": "style-1",
   "isDefaultForTable": true,
   "markerOptions": {
    "iconName": "small_red"
   },
   "polylineOptions": {
    "strokeColorStyler": {
     "kind": "gradient",
     "columnName": "population",
     "gradient": {
      "min": 1,
      "max": 100,
      "colors": [
       {
        "color": "#FF0000",
        "opacity": 1
       },
       {
        "color": "#00FF00",
        "opacity": 1
       }
      ]
     }
    },
    "strokeWeight": 2
   }
  },
  {
   "kind": "fusiontables#styleSetting",
   "tableId": 1e7y6mtqv893333333333_ccccccccc_CvWhg9gc,
   "styleId": 2,
   "name": "style-2",
   "isDefaultForTable": false,
   "polygonOptions": {
    "fillColor": "#0000FF",
    "fillOpacity": 0.2
   }
  }
 ]
}

Retrieving a style

You can retrieve a single style for a table, given its table ID, by sending an HTTP GET request to the URI with the following format:

https://www.googleapis.com/fusiontables/v1/tables/tableId/styles/styleId

Syntax

Request

Here is an example requesting style 1 from table 1e7y6mtqv893333333333_ccccccccc_CvWhg9gc:

GET https://www.googleapis.com/fusiontables/v1/tables/1e7y6mtqv893333333333_ccccccccc_CvWhg9gc/styles/1

Response

If the request succeeds, the server responds with the 200 OK HTTP status code and the requested style in the table:

200 OK

{
 "kind": "fusiontables#styleSetting",
 "tableId": 1e7y6mtqv893333333333_ccccccccc_CvWhg9gc,
 "styleId": 1,
 "name": "style-1",
 "isDefaultForTable": true,
 "markerOptions": {
  "iconName": "small_red"
 },
 "polylineOptions": {
  "strokeColorStyler": {
   "kind": "gradient",
   "columnName": "population",
   "gradient": {
    "min": 1,
    "max": 100,
    "colors": [
     {
      "color": "#FF0000",
      "opacity": 1
     },
     {
      "color": "#00FF00",
      "opacity": 1
     }
    ]
   }
  },
  "strokeWeight": 2
 }
}

Creating a style

You can create a new style for a table, given its table ID, by sending an HTTP POST request to the URI:

https://www.googleapis.com/fusiontables/v1/tables/tableId/styles

Syntax

Request

Here is an example of a POST request that creates a new style for table 1e7y6mtqv893333333333_ccccccccc_CvWhg9gc:

POST https://www.googleapis.com/fusiontables/v1/tables/1e7y6mtqv893333333333_ccccccccc_CvWhg9gc/styles
Authorization: /* auth token here */
Content-Type: application/json

{
 "tableId": 1e7y6mtqv893333333333_ccccccccc_CvWhg9gc,
 "name": "style-3",
 "isDefaultForTable": false,
 "polygonOptions": {
  "strokeColorStyler": {
   "kind": "buckets",
   "columnName": "population",
   "buckets": [
    {
     "min": 0,
     "max": 100,
     "color": "#FF0000"
    },
    {
     "min": 100,
     "max": 200,
     "color": "#00FF00"
    },
    {
     "min": 200,
     "max": 300,
     "color": "#0000FF"
    }
   ]
  }
 }
}

Response

If the request succeeds, the server responds with the 200 OK HTTP status code and the style data:

200 OK

{
 "tableId": 1e7y6mtqv893333333333_ccccccccc_CvWhg9gc,
 "styleId": 3,
 "name": "style-3",
 "isDefaultForTable": false,
 "polygonOptions": {
  "strokeColorStyler": {
   "kind": "buckets",
   "columnName": "population",
   "buckets": [
    {
     "min": 0,
     "max": 100,
     "color": "#FF0000"
    },
    {
     "min": 100,
     "max": 200,
     "color": "#00FF00"
    },
    {
     "min": 200,
     "max": 300,
     "color": "#0000FF"
    }
   ]
  }
 }
}

Updating a style

To update an existing style in a table, given its table ID, you need to follow these steps:

  1. Retrieve the resource that you want to update.
  2. Modify the properties you want to change.
  3. Send a PUT request to the resource URI and supply the modified resource as the message body.
https://www.googleapis.com/fusiontables/v1/tables/tableId/styles/styleId

You cannot modify the kind, styleId, or tableId properties for a style. These are set by the Fusion Tables API.

Syntax

Request

Here is an example of updating style 2 on table 1e7y6mtqv893333333333_ccccccccc_CvWhg9gc, which applies buckets to the stroke color of all polygons using the data in column "population":

PUT https://www.googleapis.com/fusiontables/v1/tables/1e7y6mtqv893333333333_ccccccccc_CvWhg9gc/styles/2
Authorization: /* auth token here */
Content-Type: application/json

{
 "name": "style-2",
 "isDefaultForTable": false,
 "polygonOptions": {
  "strokeColorStyler": {
   "kind": "buckets",
   "columnName": "population",
   "buckets": [
    {
     "min": 0,
     "max": 200,
     "color": "#FF0000"
    },
    {
     "min": 200,
     "max": 400,
     "color": "#00FF00"
    },
    {
     "min": 400,
     "max": 600,
     "color": "#0000FF"
    }
   ]
  }
 }
}

Response

If the request succeeds, the server responds with a 200 OK status code and the full representation of the modified resource.

Deleting a style

To delete an existing style in a table, given its table ID, send a DELETE request to the resource URI.

The URI for the Style Resource has the following format:

https://www.googleapis.com/fusiontables/v1/tables/tableId/styles/styleId

Syntax

Request

Here is an example that deletes the style created in the previous section. Notice that no body is required.

https://www.googleapis.com/fusiontables/v1/tables/1e7y6mtqv893333333333_ccccccccc_CvWhg9gc/styles/2
Authorization: /* auth token here */

Response

If the request succeeds, the server responds with a 204 No Content status code.

Working with rows

The Google Fusion Tables API v1.0 uses SQL statements to work with rows. Fusion Tables SQL statements are sent to the Google Fusion Tables server using HTTP GET requests (for queries) and POST requests (for inserts, updates, and deletes) from a client application.

You can use SQL statements to construct complex queries to identify rows that match specific criteria. The Querying for data section describes how to set filtering criteria and how to define grouping or sorting of the results.

Retrieving a row ID or a list of row IDs

To update or delete a particular row, you need the row's ID. This is an internally generated number that uniquely identifies each row as it is inserted or uploaded into a Fusion Tables table. By default, data is returned in JSON format. You can obtain CSV format by using the alt=csv query parameter in your SQL statement.

In v2, you can download larger result sets by specifying alt=media. It will respond with the CSV data in the response body.

To get a row ID, follow these steps:

  1. Obtain a table ID.
  2. Check that you have the appropriate permission levels for the table.
  3. Send a GET query request the table.

Obtain row IDs

You can obtain all the row IDs for a table, given its table id, by sending a GET request to the URI with an SQL statement in this format:

https://www.googleapis.com/fusiontables/v1/query?sql=SELECT ROWID FROM tableId&key=your_API_key

You can find a specific row's ID by sending an SQL query with selection criteria in the following format: 

https://www.googleapis.com/fusiontables/v1/query?sql=SELECT ROWID FROM tableId
    { WHERE <filter_condition> | <spatial_condition> { AND <filter_condition> }* }

Syntax

Request

Here is an example of an HTTP GET request for all rows from table 1KxVV0wQXhxhMScSDuqr-0Ebf0YEt4m4xzVplKd4:

https://www.googleapis.com/fusiontables/v1/query?sql=SELECT ROWID FROM 1KxVV0wQXhxhMScSDuqr-0Ebf0YEt4m4xzVplKd4

Response

If the request succeeds, the server responds with a 200 OK status code and a list of the row IDs for the table:

200 OK

{
 "kind": "fusiontables#sqlresponse",
 "columns": [
  "rowid"
 ],
 "rows": [
  [
   "2801"
  ],
  [
   "2802"
  ]
 ]
}

Querying for data

You can use the SELECT statement to find rows that match given criteria. This is useful for many applications, for example:

  • Obtain specific data for use by your application.
  • Retrieve the ID for a row that you want to update or delete.
  • Perform a spatial search to find locations within a certain radius or bounding box.
  • Find the n map features nearest to a given coordinate.

To query for rows, set up your SELECT statement carefully:

  • You must know the table's table ID. It must be a table on which you have the necessary permissions.
  • Rows that match the query criteria are returned in the results in UTF-8 and typed JSON format by default.
  • A query defines which columns to include in the results. Follow the column-name rules.
  • To use spatial queries a table must have one or more columns of type LOCATION. See Working with geographic data.

To query for data, send a GET request to the Google Fusion Tables server using this syntax:

https://www.googleapis.com/fusiontables/v1/query?sql=SELECT  <column_spec> {, <column_spec>}*

FROM <table_id>

{ WHERE <filter_condition> | <spatial_condition> { AND <filter_condition> }* }

{ GROUP BY <column_name> {, <column_name>}* }

{ ORDER BY <column_spec> { ASC | DESC } | <spatial_relationship> }

{ OFFSET <number> }

{ LIMIT <number> }

Request

Here's an example that gets the row ID and the values in the Product and Inventory columns for all records in a table (Try this in your browser):

SELECT ROWID, Product, Inventory FROM 1KxVV0wQXhxhMScSDuqr-0Ebf0YEt4m4xzVplKd4

Response

If the SELECT statement is successful, f the request succeeds, the server responds with the 200 OK HTTP status code and the requested results in typed JSON format. You can also obtain untyped JSON or CSV formatted data with a query parameter.

200 OK

{
 "kind": "fusiontables#sqlresponse",
 "columns": [
  "rowid",
  "Product",
  "Inventory"
 ],
 "rows": [
  [
   "1",
   "Amber Bead",
   "1251500558"
  ],
  [
   "201",
   "Black Shoes",
   "356"
  ],
  [
   "401",
   "White Shoes",
   "100"
  ]
 ]
}

Request: Using a lat-long radius

To get the Store Name, Pharmacy, and Hours for all records within a radius of a latitude and longitude coordinate in a table (Try this in your browser):

SELECT 'Store Name', Pharmacy, Hours FROM 1EIv-k-417_BWrgm4mNANo-Ejvc94hZYsvh7jIA WHERE Pharmacy='yes' AND ST_INTERSECTS(Address, CIRCLE(LATLNG(37.3242,-121.9806),5000))

Response

200 OK

{
 "kind": "fusiontables#sqlresponse",
 "columns": [
  "Store Name",
  "Pharmacy",
  "Hours"
 ],
 "rows": [
  [
   "Zmart",
   "yes",
   "9am-10pm"
  ]
 ]
}

Request: Using ORDER BY

Find all the animals whose favorite food is fish, ordered by the size of the population (Try this in your browser):

SELECT animal, 'population size' FROM 1T071OnIfUwjhwbOQ2Cjgapqq-0e0zHtTdD7a5Q WHERE favorite_food = 'fish' ORDER BY 'population size'

Response

200 OK

{
 "kind": "fusiontables#sqlresponse",
 "columns": [
  "animal"
 ],
 "rows": [
  [
   "penguin"
  ],
  [
   "bear"
  ],
  [
   "seal"
  ],
  [
   "albatross"
  ]
 ]
}

Request: Rectangle Intersection

Find all stores within the specified rectangular bounding box, ordered by Hours (Try this in your browser):

SELECT 'Store Name', Pharmacy, Hours FROM 1EIv-k-417_BWrgm4mNANo-Ejvc94hZYsvh7jIA WHERE ST_INTERSECTS(Address, RECTANGLE(LATLNG(37.0242,-122.2806), LATLNG(37.5242,-121.6806))) ORDER BY Hours

Response

200 OK

{
 "kind": "fusiontables#sqlresponse",
 "columns": [
  "Store Name",
  "Pharmacy",
  "Address"
 ],
 "rows": [
  [
   "Zmart",
   "yes",
   "Santa Clara, CA"
  ]
 ]
}

Request: Order by Distance

Find 10 rows nearest to the given location.

SELECT 'Store Name', Hours, Address FROM 1EIv-k-417_BWrgm4mNANo-Ejvc94hZYsvh7jIA ORDER BY ST_DISTANCE(Address, LATLNG(37.5242,-121.6806)) LIMIT 10

Response

200 OK

{
 "kind": "fusiontables#sqlresponse",
 "columns": [
  "Store Name",
  "Hours",
  "Address"
 ],
 "rows": [
  [
   "Zmart",
   "9am-10pm",
   "Santa Clara, CA"
  ],
  [
   "Zmart Mini",
   "10am-10pm",
   "San Francisco, CA"
  ],
  [
   "Zmart Plus",
   "8am-10pm",
   "Dallas, TX"
  ]
 ]
}

Inserting a row

Note: To insert a large number of rows, use the import method instead, which will be faster and more reliable than using many SQL INSERT statements.

To insert a row, send an authenticated POST request to this URL: 

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

In a sql=<statement> parameter in the body of the POST, write the INSERT statement:

INSERT INTO <table_id> (<column_name> {, <column_name>}*) VALUES (<value> {, <value>}*)
{ {;INSERT INTO <table_id> (<column_name> {, <column_name>}*) VALUES (<value> {, <value>}*)}* ;}

Syntax

Considerations

  • You need to know the table ID for the base table or view. It must be a table on which you have the necessary permissions.
  • When you insert a new row in a base table, any views or merged tables that are derived from that base table will also acquire the new row.
  • When you insert a new row in a view table, the row must meet any filter conditions that are part of the view's definition. The row is added to the base table.
  • The server creates a "ROWID" in response to your POST request. You cannot modify this value.

Usage restrictions

  • You cannot insert a row into a merged table.
  • The maximum data size for a single HTTP request is 1MB.
  • The total number of table cells being added cannot exceed 10,000 cells. Note that the calculation may not be obvious. Let's say your table has 2 columns: name and age. Your update query is as follows: 
    INSERT INTO <tableid> (name) VALUES ('kat')
    Even though only one column is listed in the INSERT query, 2 cells are being updated: both the name and the age column. The age column is given an empty value.
  • You can create a single request by concatenating up to a maximum of 500 INSERT statements separated by semicolons, provided the request remains under 1MB and updates fewer than 10,000 cells.

Request

To add a single row to table 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc where Product is Red Shoes and Inventory is 25:

INSERT INTO 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc (Product, Inventory) VALUES ('Red Shoes', 25)

To add five rows of product inventory data to the same table:

INSERT INTO 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc (Product, Inventory) VALUES ('Red Shoes', 25);
    INSERT INTO 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc (Product, Inventory) VALUES ('Blue Shoes', 50);
    INSERT INTO 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc (Product, Inventory) VALUES ('Yellow Shoes', 130);
    INSERT INTO 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc (Product, Inventory) VALUES ('Purple Shoes', 6);
    INSERT INTO 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc (Product, Inventory) VALUES ('Black Shoes', 3000);

Response

If the request to add a row succeeds, the server responds with a 200 OK status code and the row ID for the new row:

200 OK

{
 "kind": "fusiontables#sqlresponse",
 "columns": [
  "rowid"
 ],
 "rows": [
  [
   "301"
  ]
 ]
}

Updating a row

To update a row, send an authenticated POST request to this URL:

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

In a sql=<statement> parameter in the body of the POST, write the UPDATE statement:

UPDATE <table_id>
SET <column_name> = <value> {, <column_name> = <value> }*
WHERE ROWID = <row_id>

Syntax

Considerations

  • You must know the table's table ID. It must be a table on which you have the necessary permissions.
  • You must have the row ID. You can get this from a previous SELECT or INSERT statement.
  • You must know the names of the columns you want to update. To find out what columns are available in a given table, use the Google Fusion Tables API v1.0 to retrieve a list of all columns in a table.
  • If more than one row matches the selection criteria, multiple rows are returned and you will need to make your SELECT query more specific to identify the particular row you want to update.

Note: You can only update rows in the table if authenticated as an owner or editor on the table. The user must be authenticated to create Google Fusion Tables resources, so you must provide the Authorization HTTP header with the POST request.

Usage restrictions

  • When you update a row in a base table, any views or merged tables that are derived from that base table will also acquire the new data.
  • In a merged table, you cannot update the value in the key column. If you want to update a key value, do so in the base table from which the merged table was derived. The change will be reflected in the merged table.

Request

To adjust inventory levels after the sale of one pair of red shoes, run a query to find the row that contains Red Shoe:

SELECT ROWID FROM 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc WHERE Product='Red Shoe'

Assuming this returns the row ID 301, we can update the inventory level to show how many red shoes are available today:

UPDATE 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc SET Inventory = 24 WHERE ROWID = '301'

Response

If the request succeeds, the server responds with a 200 OK status code and number of affected rows:

Response:
{
 "kind": "fusiontables#sqlresponse",
 "columns": [
  "affected_rows"
 ],
 "rows": [
  [
   "1"
  ]
 ]
}

Deleting a row

To delete rows from a table, send an authenticated POST request to this URL: 

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

In a sql=<statement> parameter in the body of the POST, write the DELETE statement:

DELETE FROM <table_id>{ WHERE ROWID = <row_id>}

The WHERE clause is optional. If it is not included, all rows will be deleted from the table.

Note: You can obtain the table ID by choosing the File > About menu command in the UI or by making a call to retrieve a list of all tables.

Syntax

Considerations

  • You must know the table's table ID. It must be a table on which you have the necessary permissions.
  • You must have the row ID. You can get this from a previous SELECT or INSERT statement.

When you delete a row or rows from a base table, the change is reflected in views and merged tables that are derived from that base table. The reflection looks like this:

  • In a view, the row or rows are removed.
  • In a merged table where the base table was the first one present — that is, the table selected in the Google Fusion Tables website before the Merge button was clicked — the row or rows are removed entirely. (For those familiar with SQL: the base table was the driving table in the join.)
  • In a merged table where the base table was not the first one present, the row or rows are not removed from the merged table. What is removed? The values in the row or rows that were drawn from the base table where the row or rows were deleted. (For those familiar with SQL: the base table was a lookup table in the join.)

Request

To remove all rows from a table:

sql=DELETE FROM 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc

To remove only the information about black shoes:

First, run a query to find the row that contains Black Shoes:

https://www.googleapis.com/fusiontables/v1/query?sql=SELECT ROWID FROM 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc WHERE Product='Black Shoes'

Assuming this returns the row ID 3, we can delete that row:

sql=DELETE FROM 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc WHERE ROWID = '3'

Response

If the request succeeds, the server responds with a 200 OK status code and number of affected rows:

Response:
{
 "kind": "fusiontables#sqlresponse",
 "columns": [
  "affected_rows"
 ],
 "rows": [
  [
   "1"
  ]
 ]
}

Note: When deleting all rows, the response might contain the string "all rows" instead of the exact number.

URL query parameters

The query parameters you can use with the Fusion Tables API are summarized in the following table. All parameter values need to be URL encoded.

Parameter Meaning Notes Applicability
key Indicates an API key.
  • All operations for all resources if you are not using an OAuth 2.0 auth token.
alt Alternative data format for SQL requests.
  • To obtain data from an SQL request in untyped CSV format, use alt=csv.
  • All operations for query resources
typed Numeric data format for SQL requests.
  • To obtain data from an SQL request in untyped JSON format, use typed=false.
  • All operations for JSON-formatted query resources
callback Specify a JavaScript function to handle query results for pure client-side implementations.
  • All operations for all resources.
maxResults Returns a response with at most this many results at a time.
  • If maxResults=10, at most 10 results will be returned in a list request.
  • The default varies depending on the type of resource.
  • Only GET operations for list requests.
pageToken Returns the next page of results.
  • No pageToken is required for the first page of results.
  • The pageToken can be found in the body of the response under "nextPageToken".
  • If there are no more pages of results to display, the response will not return a next token.
  • Only GET operations for list requests.
prettyPrint Returns a response with indentations and line breaks
  • If prettyPrint=true, the results returned by the server will be human readable (pretty printed).
  • Default: prettyPrint=false
  • All operations for all resources

Working with geographic data

Fusion Tables makes it simple to map a database of locations and run spatial queries on it (See the Fusion Tables Reference). To display the results of your query on a Google map, you can use the UI (view the Create a map tutorial in the Classic and the New look versions of the web application tutorial) or you can use the API with the FusionTablesLayer Maps API v3.0 object. If you're not familiar with Fusion Tables Layers, you can find more information in this blog post.

Note: If you're using the Fusion Tables Layer object in the Google Maps API to display data on a map, your map is subject to the limits announced in this blog post.

Geographic design constraints

It's useful to keep in mind these limits:

  • Only the first 350,000 rows of data in a table are mapped or included in spatial query results.*
    Therefore:
    • Maps made with Fusion Tables Layers in the Google Maps API or with the Fusion Tables web application will only show data from within this first 350,000 rows. If you apply a filter to a table larger than 350K rows and the filter matches data in rows after the first 350K, these rows will not appear on the map.
    • Queries with spatial predicates only return data from within this first 350K rows. If you apply a SELECT query with a spatial predicate to a very large table and the query matches data in rows after the first 350K, these rows are not returned.
  • When importing or inserting data, remember:
    • The total size of the data sent in one API call cannot exceed 1MB.
    • API calls to write data should not exceed 0.5 qps per table.
    • A cell of data in Fusion Tables supports a maximum of 1 million characters; it may sometimes be necessary to reduce the precision of coordinates or simplify polygon or line descriptions.
    • The maximum number of vertices supported per table is 5 million.*
  • When looking at the map, you may notice:
    • The ten largest-area components of a multi-geometry are shown.
    • When zoomed far out, map tiles may exceed the 500 feature-per-tile limit, and features will be dropped from the map.*
    • When zoomed farther out, tables with more than 500 features will replace lines or polygons with dots.*

You can email googletables-feedback@google.com requesting exemptions to the limits marked with a (*) for specific tables.

Geographic locations

A geographic location is a string that contains any of the following:

  • A street address, name of a city, name of a country, or any location description understood by Google Maps. If your source repository stores address information in several columns, merge them into a single column before uploading the data to Google Fusion Tables.
  • Latitude, longitude pair. Use decimal degrees. Separate the numbers with a space or comma.
  • KML, as described in the KML section below.

Note: Like all other Google mapping tools, Google Fusion Tables assumes the WGS 84 datum. If your location data is in a different datum, convert it first for the most accurate mapping.

To add geographic location in a table using the API:

  1. Make a column of type LOCATION. This can be done in a CREATE TABLE statement.
  2. Use an INSERT or UPDATE statement to fill the column with a string.

You can also import data through the Google Fusion Tables website:

  1. Click "New table" and import your data.
  2. When your table is imported, check that the geographic information column was correctly detected as type LOCATION. From the Edit menu, select "Modify columns". The column's type is shown to the right of the name.
  3. If the type is not Location, click the column name and change the type, then click Save.

Geocoding location descriptions

Geographic data that is a description of a location must be geocoded in order to place it on a map. Latitude, longitude pairs and KML are already mappable.

To geocode your location descriptions stored in a table, go to the Google Fusion Tables website and choose the Map visualization, or select "Geocode" from the File menu. There is a limited number of geocodes offered for free per user per day, so very large data sets may require repeating this step until the file is entirely geocoded.

If a description geocodes to the wrong location, please see Fix map marker locations. To determine if geocoding any locations failed, use the "highlight untyped values" format for the location column and visually scan the data for descriptions shown with a yellow highlight.

Using KML data

You can store the geographic coordinates of points, lines, and polygons in a table by using KML. Use an INSERT or UPDATE statement to place a KML string in any column where the type has been set to LOCATION. This is useful to associate data with locations so that the data can be visualized on a map.

When using KML syntax, be sure to avoid these common pitfalls:

  • Do not put a space character after a comma when specifying latitude and longitude. For example, 10,10 is correct, but 10, 10 is not.
  • Use upper- or lowercase exactly as shown in the syntax. For example, <Point> is correct, but <point> is not.
  • Remember that the longitude comes first in KML long/lat pairs—just the opposite of the order used when specifying a lat/long pair as an ordinary string.

Syntax

To store KML in a table, include the KML in the <value> argument of an INSERT or UPDATE statement using one of the following syntactic choices.

  • Point: 
    <Point>
<coordinates> lng,lat[,alt] </coordinates>
</Point>
  • Line: 
    <LineString>
<coordinates> lng,lat[,alt] lng,lat[,alt] ... </coordinates>
</LineString>
  • Polygon (specify the coordinates in counter-clockwise order): 
    <Polygon>
<outerBoundaryIs>
<coordinates> lng,lat[,alt] lng,lat[,alt] ... </coordinates>
</outerBoundaryIs>
</Polygon>

Syntax Details

Examples

Suppose you have a table with a table ID of 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc, a STRING column called CityDescription, and a LOCATION column called CityLocation. You can store the location in several ways. To store the location of Tokyo as an ordinary search string: 

sql=INSERT INTO 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc (CityDescription, CityLocation)
VALUES ('Tokyo Metropolis', 'Tokyo')

To store the location of Tokyo as a lat/long pair:

sql=INSERT INTO 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc (CityDescription, CityLocation)
VALUES ('Tokyo Metropolis', '35.69 139.69')

To store the location of Tokyo as KML:

sql=INSERT INTO 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc (CityDescription, CityLocation)
VALUES ('Tokyo Metropolis', '<Point> <coordinates> 139.69,35.69 </coordinates> </Point>')

To store a new airline route from Hyderabad to Delhi to Paris (assuming a flight information table with the ID 1e7y6mtqv894444444444_ddddddddd_CvWhg9gc), use KML to sketch a line:

sql=UPDATE 1e7y6mtqv894444444444_ddddddddd_CvWhg9gc
SET Route = '<LineString> <coordinates> 78.47,17.37 -91.58,32.44 2.35,48.85 </coordinates> </LineString>'
WHERE ROWID = '1'

To map a plot of land for surveying wildlife populations in the Desert Tortoise Natural Area in the Mojave Desert, California (assuming a research volunteer database with ID 1e7y6mtqv893333333333_ccccccccc_CvWhg9gc), use KML to outline the area: 

sql=INSERT INTO 1e7y6mtqv893333333333_ccccccccc_CvWhg9gc (volunteer_id, plot) VALUES (45,
'<Polygon> <outerBoundaryIs>
<coordinates> -117.85,35.3 -117.85,35.301 -117.851,35.301 -117.851,35.3 -117.85,35.3 </coordinates>=>
</outerBoundaryIs> </Polygon>'

Quotas

The default request quota limits are the following:

  • 25,000 requests per day per API project, where reads count as one request and writes count as five requests.
  • 30 write requests per minute per table

You may request more quota at the Google APIs Console under the "Quotas" tab.

The storage quota limits are:

  • 250 MB per table
  • 1 GB total among all your tables

Reserved words

Fusion Tables has the following reserved words. We recommend you not use them as table, view, or column names. If, however, you need to for some reason, make sure you enclose them in single quotes.

AND
ASC
AS
BY
CASE
CIRCLE
CONTAINS
CONTAIN
CREATE
DELETE
DESCRIBE
DESC
DOES
DROP
ENDS
EQUAL
FROM
GROUP
IGNORING
IN
INSERT
INTO
LATLNG
LIKE
LIMIT
MATCHES
NEAR
NOT
OFFSET
ORDER
POLYGON
RECTANGLE
ROWID
SELECT
SET
SHOW
SKIP
ST_DISTANCE
ST_INTERSECTS
STARTS
TABLES
TABLE
TO
UPDATE
VALUES
VIEW
WHERE
WITH
ID
NUMBER
DOCID
STRING