This document describes how to use the common features of the Fusion Tables API.
Contents
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 IDOr 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:
- When your application needs access to user data, it asks Google for a particular scope of access.
- Google displays a consent screen to the user, asking them to authorize your application to request some of their data.
- If the user approves, then Google gives your application a short-lived access token.
- Your application requests user data, attaching the access token to the request.
- 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 KeyOr 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 HTTPGET
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 HTTPGET
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 HTTPGET
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 yourPOST
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:
- Make sure you have the correct permissions for the table.
- Retrieve the table you want to update by using its table ID.
- Modify the properties you want to change. You cannot modify
the
"kind"
properties or the values in the"columns[]"
property. - 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 yourPOST
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:
- 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. - Modify the properties you want to change. Properties that can be modified include the
name
andtype
of the column. You cannot use the Fusion Tables API to change a column's type in views and merged tables. - 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 yourPOST
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:
- Retrieve the template that you want to update.
- Modify the properties you want to change. You cannot modify the
kind
,templateId
, ortableId
properties for a template. These are set by the Fusion Tables API. - 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:
- Retrieve the resource that you want to update.
- Modify the properties you want to change.
- 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:
- Obtain a table ID.
- Check that you have the appropriate permission levels for the table.
- 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 yourPOST
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 theINSERT
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. |
|
|
alt |
Alternative data format for SQL requests. |
|
|
typed |
Numeric data format for SQL requests. |
|
|
callback |
Specify a JavaScript function to handle query results for pure client-side implementations. |
|
|
maxResults |
Returns a response with at most this many results at a time. |
|
|
pageToken |
Returns the next page of results. |
|
|
prettyPrint |
Returns a response with indentations and line breaks |
|
|
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:
- Make a column of type LOCATION. This can be done in a CREATE TABLE statement.
- Use an INSERT or UPDATE statement to fill the column with a string.
You can also import data through the Google Fusion Tables website:
- Click "New table" and import your data.
- 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.
- 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, but10, 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>
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