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.

Developer's Guide

Important: The Fusion Tables SQL API has been officially deprecated as of June 26, 2012 and will be shut down after January 14, 2013. Please see the migration guide for instructions on how to move to the new API.

Audience

This documentation is intended for programmers who are writing client applications that interact with Google Fusion Tables. In this documentation, it is assumed that you understand the following concepts:

  • Web application concepts, including HTTP requests.
  • How to send GET and POST requests. You will probably do this using one of the many readily available code libraries for your programming language of choice.
  • How to parse comma-separated-value (CSV) output. The Google Fusion Tables API returns query results in this format.

You do not need to know SQL, but if you do, it will be helpful.

Note: Please sign up for the Fusion Tables API Announcement group to get notice of upcoming changes in the API.

Terminology

repository
A data source outside of Google Fusion Tables. The supported repository types are .CSV files and spreadsheets.
base table
A table that is created by uploading data from a repository.
encid parameter
A parameter for SHOW TABLES, CREATE TABLE, and CREATE VIEW statements that indicates which type of table ID to return from the Fusion Tables server. If encid=true, the server returns encrypted table IDs. If encid=false or if you don't use this parameter in your SQL statements, Fusion Tables returns the deprecated numeric table IDs.
location
In order to display a map visualization, the table must have at least one column defined as type: location with valid latitude and longitude values. For more information, see the "Geographic locations" section.
merged table
A table that is created by combining two or more tables. Each contributing table has a column that serves as the key column, containing values matching those in the key column of the other table(s). For those familiar with SQL: the merge operation is a left outer join.

Merged tables inherit the column types, data, and edit permissions on data from the base table. You can merge tables owned by different people. You cannot insert rows into a merged table.
table ID
The table identifier is an encrypted string value such as 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc. You can locate it by choosing File > About in the Fusion Tables UI. The deprecated numeric table ID, such as 123456, is still available when you use the File > About command. Use the encid parameter with the SHOW TABLES, CREATE TABLE, or CREATE VIEW statements to indicate which type of table ID to return.

view
A table that is created by selecting a subset of columns from a base table with or without filters on some of its columns. See Creating a view.

Writing a Google Fusion Tables SQL API application

The Google Fusion Tables SQL API is a set of statements that you can use to search for and retrieve Google Fusion Tables data, insert new data, update existing data, and delete data. The API 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 Web client application. The API is language agnostic: you can write your program in any language you prefer, as long as it provides some way to embed the API calls in HTTP requests.

The Google Fusion Tables SQL API does not provide the mechanism for submitting the GET and POST requests. Typically, you will use an existing code library that provides such functionality; for example, the code libraries that have been developed for the Google GData API. You can also write your own code to implement GET and POST requests.

There are two approaches that you can follow when using the SQL API. If your application does not require authentication, follow steps 1, 4, 5, and 6 listed below. If authentication is required, follow all steps below.

Note: The examples in parentheses assume you are using the Google GData Java client library.

  1. Start by importing your favorite Web client code library (for example, the Google GData Java client library).
  2. Authorize your application to the Google Fusion Tables server (for example, using the GoogleService class).
  3. Pass the authorization token in to your application.
  4. Write code to pass the specific Google Fusion Tables API statements you want to use to the code library's GET and POST methods (for example, runSelect() and runUpdate()).
  5. Add the code that passes dynamic values to the GET and POST methods, such as your UI for accepting user-entered data.
  6. Test and deploy!

For an example implementation, see the sample code.

Sharing tables: setting permissions

Table and sharing permissions determine which operations your application can perform on a given Fusion Table, as follows:

  • All public and unlisted tables provide viewer-level permissions to all users; therefore, your application can submit queries to retrieve data from any public or unlisted table without authenticating.
  • Tables that are defined as not exportable are accessible only to their owners through the Fusion Tables API.
  • For all other tables, the user credentials that your application submits during authentication determine your application's the level of access.

Design constraints

  • Each request to the Google Fusion Tables server has a maximum size of 1 MB.
  • Applications using the Google Fusion Tables API can send a maximum of 5 read requests per second to the Google Fusion Tables server.
  • Applications sending write operations to the Google Fusion Tables server will be most successful when they limit write requests to 30 per minute or less. Each insert, update, or delete request counts as a write request.
  • The maximum number of INSERT statements you can combine in a single request is 500. The total number of table cells being added cannot exceed 10,000 cells. See Inserting rows for more detail.
  • Note: If you are working with geographic data, there are additional specific constraints you need to be aware of.

URL encoding

A note before we launch into the API: UTF-8 encoding is required in all URL parameters in GET and POST requests, including within Google Fusion Tables API statements. In the examples and syntaxes shown in our documentation, we omit this encoding to make the statements more readable.

Authentication and authorization

The Google Fusion Tables API can be used with public and private tables. If the table is private or not exportable, use of the API requires that you authenticate to the Google Tables server. For selected API calls, if the table is public and exportable, no authentication is required. Many uses of a table through the API will require that you authenticate to the Google Tables server. Fusion Tables API supports authentication using ClientLogin and OAuth 2.0.

When your application requests non-public user data, it must include an authorization token. The token also identifies your application to Google.

About authorization protocols

We recommend using OAuth 2.0 to authorize requests in most cases. However, Client Login is still recommended when using Fusion Tables as a database. For more information about which type of authorization to use, please see the article "Using OAuth 2.0 for Authorization to Fusion Tables in Web Applications".

How to authenticate using ClientLogin

To authenticate the user using ClientLogin, compose a POST request that includes the following parameters:

  • ID and password of the user on whose behalf your application is logging in
  • Name of the Google Fusion Tables service, fusiontables.

The following example shows a basic login request.

Note: The example is shown without the required URL encoding for the sake of readability. If you cut and paste, run it through an encoder before use.

POST /accounts/ClientLogin HTTP/1.0
Content-type: application/x-www-form-urlencoded

accountType=GOOGLE&Email=jondoe@gmail.com&Passwd=north23AZ&service=fusiontables&source=TheDataStewards-DataSyncr-1.05

For more information on composing a POST request using ClientLogin, see Authentication for Installed Applications.

Authorizing requests with OAuth 2.0

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

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 you create your application, you register it with Google. Google then provides information you'll need later, such as a client ID and a client secret.
  2. When your application needs access to user data, it asks Google for a particular scope of access.
  3. Google displays an OAuth dialog to the user, asking them to authorize your application to request some of their data.
  4. If the user approves, then Google gives your application a short-lived access token.
  5. Your application requests user data, attaching the access token to the request.
  6. 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 SQL API:

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

To request access using OAuth 2.0, your application needs the scope information, as well as information that Google supplies during application registration (such as the client ID and/or 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 Libraries and Samples page for more details.

Exploring tables

Fusion Tables uses three different kinds of tables: base, view, and merged. Each has its own unique table identifier and each has its own set of sharing permissions that govern user access to its data. When you upload data from a .CSV file or spreadsheet, or otherwise create a table from scratch, that's a base table. You can create a view table based on the base table that includes only the data that matches your defined filter. You can also combine two or more tables into a single merged table.

Getting table information

Use the SHOW TABLES and DESCRIBE statements to get information about the tables to which your application has access. This is useful to:

  • Get the table ID and column names so you can construct a SELECT statement.
  • Get the data types of columns so you know what types of values to use in an INSERT or UPDATE statement.

To get a list of table IDs for any table your application has access to, use SHOW TABLES. To get column names and data types for a given table (for those familiar with SQL: schema information), first get the table ID, then use DESCRIBE on that table ID.

Permissions

DESCRIBE and SHOW TABLES both require authentication and that the user is listed as an viewer or editor on the table.

Syntax

Note: Unless noted, examples are shown without the required URL encoding for the sake of readability. If you cut and paste the code, run it through an encoder before use.

To get encrypted string table IDs for tables the user owns, send an authenticated GET request to the Google Fusion Tables server using this syntax with the encid parameter (with URL encoding for clarity):

https://www.google.com/fusiontables/api/query?sql=SHOW+TABLES&encid=true

To get the deprecated numeric table IDs for tables the user owns, send an authenticated GET request to the Google Fusion Tables server using this syntax with the encid parameter (with URL encoding for clarity):

https://www.google.com/fusiontables/api/query?sql=SHOW+TABLES&encid=false

Syntax Details

To get column names and data types for public tables or tables that have been shared with the user at any access level, send an authenticated GET request to the Google Fusion Tables server using this syntax:

https://www.google.com/fusiontables/api/query?sql=DESCRIBE <table_id>

Syntax Details

Examples

To find out what tables are owned by the current user and get the encrypted table IDs (with URL encoding for clarity):

https://www.google.com/fusiontables/api/query?sql=SHOW+TABLES&encid=true

You will get output similar to the following:

table id,name
1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc,StoreInventory
1e7y6mtqv893333333333_ccccccccc_CvWhg9gc,StoreEmployees
1e7y6mtqv894444444444_ddddddddd_CvWhg9gc,StoreLocations

Or, if you didn't use the &encid=true parameter, you'll get output like this:

table id,name
274409,StoreInventory
364859,StoreEmployees
411231,StoreLocations

Use the table ID with DESCRIBE to find out about the columns in the StoreInventory table:

https://www.google.com/fusiontables/api/query?sql=DESCRIBE 1e7y6mtqv891111111111_aaaaaaaaa_CvWhg9gc

The output looks like this:

column id,name,type
col0,Product,string
col1,Inventory,number

You now know that the StoreInventory table includes a Product column that contains text and an Inventory column that contains numeric data.

Creating a table

Use the CREATE TABLE statement to make a new table. This is useful when you want to upload data without first having to use the Google Fusion Tables UI to manually create the table. If the CREATE TABLE statement is successful, it returns the table ID. You can get encrypted table IDs back by using the encid parameter.

Permissions

If your application is able to authenticate to the Google Fusion Tables server, then you have all the permissions you need to create a table. The table will be owned by whatever account was used for authentication.

Syntax

To create a table, send an authenticated POST request to the URL below. You can get encrypted table IDs back by using the encid parameter.

https://www.google.com/fusiontables/api/query?encid=true

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

CREATE TABLE <table_name> (<column_name>: <type> {, <column_name>: <type>}*)

Syntax Details

Example

To create a table with four columns of sales data (with and without URL encoding for clarity):

CREATE%20TABLE%20SaleInformation%20(customer%3ANUMBER,%20memo%3ASTRING,%20'sale location'%3ALOCATION,%20'time of sale'%3ADATETIME)
CREATE TABLE SaleInformation (customer:NUMBER, memo:STRING, 'sale location':LOCATION, 'time of sale':DATETIME)

If the CREATE TABLE statement is successful, it returns the table ID. For example, if you used the encid=true parameter:

tableid
1e7y6mtqv895555555555_eeeeeeeee_CvWhg9gc

Creating a view

Use the CREATE VIEW statement to make a new view for a base or view table. Each view has its own permissions. This is useful when you want to:

  • retain some columns of a table as private, but make other columns public
  • share a subset of rows in a table for other users to edit, without giving them permission to update the entire table

Before creating a view, you must know the table ID for the table from which you are building a view. If the CREATE VIEW statement is successful, it returns the table ID. You can get an encrypted table ID back by using the encid parameter.

Usage restrictions

You cannot create a view of a merged table.

Permissions

The account used for authentication must have at least Viewer permission for the table from which you're creating the view. The view will be owned by the account used to create the view.

Syntax

To create a view, send an authenticated POST request to this URL:

https://www.google.com/fusiontables/api/query

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

CREATE VIEW <view_name>

AS (SELECT  <column_spec> {, <column_spec>}*

FROM <table_id>

{WHERE <filter_condition> { AND <filter_condition> }*})

Here's a simplified POST request using the encid=true parameter:

https://www.google.com/fusiontables/api/query?sql=CREATE VIEW <table_name> AS (<SELECT-statement>) encid=true

Syntax Details

Examples (requesting the table ID returned as an encrypted string)

To create a view from the table with the ID of1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gcwith the sale location as "Mountain View, CA":

CREATE VIEW 'MV Sales' AS (SELECT * FROM 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc WHERE 'sale loc' = 'MTV') encid=true

To create a view with two columns, customer and location, from the table with the ID of1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc:

CREATE VIEW 'MV Sales' AS (SELECT cust, 'sale loc' AS location FROM 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc) encid=true

To create a view with two columns, customer and location, from the table with the ID of1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gcwith the sale location as "Mountain View, CA":

CREATE VIEW 'MV Sales' AS (SELECT customer, 'sale location' AS location FROM 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc WHERE 'sale location' = 'Mountain View, CA') encid=true

If the CREATE VIEW statement is successful, it returns the table ID. For example, if you used the encid=true parameter:

tableid
1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc

 

Examples (requesting the table ID in the deprecated numeric form)

To create a view from table 274408 with the sale location as "Mountain View, CA":

CREATE VIEW 'MV Sales' AS (SELECT * FROM 274408 WHERE 'sale location' = 'Mountain View, CA')

To create a view with two columns, customer and location, from table 274408:

CREATE VIEW 'MV Sales' AS (SELECT customer, 'sale location' AS location FROM 274408)

To create a view with two columns, customer and location, from table 274408 with the sale location as "Mountain View, CA":

CREATE VIEW 'MV Sales' AS (SELECT customer, 'sale location' AS location FROM 274408 WHERE 'sale location' = 'Mountain View, CA')

If the CREATE VIEW statement is successful, it returns the table ID. For example, without the encid=true parameter:

tableid
274408

Deleting a table

Use the DROP TABLE statement to erase a table. This is useful to:

  • Delete a base table, merged table or view.
  • Allow round-trip code tests to create a table, populate it, and then delete the table.
  • Undo the join represented by a merged table.

Before deleting a table, you must know its table ID.

When you delete a base table, the change is reflected in all views and merged tables that are derived from that base table:

  • In a view, all rows are removed. The view is empty.
  • In a merged table where the base table was the first one present — that is, the table selected in the Google Fusion Tables UI before the Merge button was clicked — all rows are removed, leaving the merged table empty. (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 values drawn from the deleted base table are removed. (For those familiar with SQL: the base table was a lookup table in the join.)

Permissions

You can delete the table if authenticated as an owner of the table.

Syntax

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

https://www.google.com/fusiontables/api/query

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

DROP TABLE <table_id>

Syntax Details

Example

Note: The example is shown without the required URL encoding for the sake of readability. If you cut and paste, run it through an encoder before use.

To delete the table 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc:

sql=DROP TABLE 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc

Querying for data

Use the SELECT statement to find data that matches given criteria. This is useful to:

  • Download 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 nearest neighbors to a coordinate.

Before you can query a table:

  • You must know its table ID.
  • You must know the names of the columns in the table. You can get this from the Google Fusion Tables UI or by using DESCRIBE.

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

  • A query includes at least two sorts of instructions: it tells what criteria a row must match in order for data from that row to be included in the query results; and it tells which columns from the row to include in the query results.
  • You need to follow the column-name rules as defined in the Google Fusion Tables Reference Guide.
  • To get row IDs, include the special identifier ROWID in the list of columns. The query will get the row ID of each row that matches the query criteria. You'll need this row ID to identify the row in subsequent UPDATE or DELETE commands.
  • To use spatial queries a table must have one or more columns of type LOCATION. See Working with Geographic data.

If the SELECT statement is successful, it returns zero or more rows of data in comma-separated value (CSV) format, preceded by a comma-separated header row that shows the column names.

Permissions

You can query the table if it is public and exportable, or if authenticated as an owner, editor, or viewer on the table.

Syntax

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

https://www.google.com/fusiontables/api/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> }

Syntax Details

Examples

Note: The examples are shown without the required URL encoding for the sake of readability. If you cut and paste, run it through an encoder before use.

To get the row ID and the values in the columns named Product and Inventory for all records in table 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc:

https://www.google.com/fusiontables/api/query?sql=SELECT ROWID, Product, Inventory FROM 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc

Note: A SELECT statement to a table marked public and exportable can be written directly in the URL bar of your browser because it doesn't require authentication and is sent as a GET request. Try this example in your browser.

Example output:

rowid,Product,Inventory
1,Amber Bead,1251500558
201,Black Shoes,356
401,White Shoes,100

To get the Store Name, Pharmacy, and Hours for all records within a radius of a latitude and longitude coordinate in table 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc:

https://www.google.com/fusiontables/api/query/?sql=SELECT 'Store Name', Pharmacy, Hours FROM 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc WHERE Pharmacy='yes' AND ST_INTERSECTS(Address, CIRCLE(LATLNG(37.3242,-121.9806),5000))

Note: A SELECT statement to a table marked public and exportable can be written directly in the URL bar of your browser because it doesn't require authentication and is sent as a GET request. Try this example in your browser.

Example output:

Store Name,Pharmacy,Hours
Zmart,yes,9am-10pm

Working with rows

Each row of data in a Fusion Table has an automatically generated row ID as a unique identifier within a table. You need this row ID to identify a specific row in subsequent UPDATE or DELETE commands.

When using row IDs in statements and loops:

  • Row IDs are not sequential.
  • Row IDs in base or view tables are numeric, but row IDs in merged tables are strings concatenated from the row IDs in the base tables.
  • To get row IDs, use a SELECT ROWID statement in your query. This results in Fusion Tables providing the row ID for each row that matches the query criteria.

Row constraints:

  1. You can insert rows into base or view tables. You cannot insert rows into merged tables.
  2. You can update values in the key column only in base tables. You cannot update key values in view or merged tables.
  3. You can delete rows from base tables, but not from or view or merged tables.

Inserting rows

Use the INSERT statement to add one or more rows to a base table or a table view in Google Fusion Tables. This is useful to:

  • Populate a new table with data from a repository.
  • Update the table when a row is added to the repository.

Before adding rows:

  • The table must exist. If it does not, use CREATE TABLE or the Google Fusion Tables website to create a table or use CREATE VIEW to create a view for an existing table.
  • You must know its table ID.
  • You must know the names of all the columns. You can get this from the Google Fusion Tables website or by using DESCRIBE.

To add rows, set up your INSERT statement:

  • Pass in the table ID.
  • Specify values for all columns. To leave a column empty, assign it the empty string ('').
  • To insert more than one row of data in an atomic operation, add more INSERT statements, separated by semicolons (;). This is the recommended technique when inserting more than one row, because including multiple INSERT commands in one HTTP request provides better performance than issuing a separate request for each INSERT.

If the INSERT statement is successful, it returns the row ID of each new row.

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 is added to the base table. Rows inserted into a view must meet any filter conditions that are part of the view's definition. See the section of the CreateView syntax reference.

Permissions

You can insert rows in the table if authenticated as an owner or editor on the table.

Usage restrictions

  • You cannot insert a row into a merged table.
  • The maximum number of INSERT statements you can combine in a single request is 500. Separate the statements with semicolons. Terminate the whole list with a final semicolon, but do not use the semicolon if you are issuing only a single INSERT statement.
  • The maximum data size for a single HTTP request is 1MB and the total number of table cells being added cannot exceed 10,000 cells. Note that the calculation may not be obvious. For example, if you have 100 columns in your table and your INSERT statement includes only 10 columns, the number of cells being added is still 100.

Syntax

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

https://www.google.com/fusiontables/api/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 Details

Examples

Note: The examples are shown without the required URL encoding for the sake of readability. If you cut and paste a command, run it through an encoder before use.

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

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

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

sql=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);

Updating a row

Use the UPDATE statement to change the value in one or more columns of a single row in a table. This is useful to:

  • Change the table in response to user comments, data entry through a form, or other triggers.
  • Update the table when data is changed in the repository.

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.

Before updating a row:

  • You must know the table's table ID.
  • You must have the row ID. You can get this from a previous SELECT statement.
  • You must know the names of the columns you want to update. You can get this from the Google Fusion Tables website or by using DESCRIBE.

To modify the data in a row, set up your UPDATE statement:

  • Pass in the row ID.
  • Specify values for one or more columns.

Permissions

You can update rows in the table if authenticated as an owner or editor on the table.

Usage restrictions

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.

Syntax

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

https://www.google.com/fusiontables/api/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 Details

Example

To adjust inventory levels after the sale of one pair of red shoes:

Note: The example is shown without the required URL encoding for the sake of readability. If you cut and paste, run it through an encoder before use.

First, run a query to find the row that contains Red Shoe:

https://www.google.com/fusiontables/api/query?sql=SELECT ROWID FROM 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc WHERE Product='Red Shoe'

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

sql=UPDATE 1e7y6mtqv892222222222_bbbbbbbbb_CvWhg9gc SET Inventory = 24 WHERE ROWID = '8'

Deleting rows

Use the DELETE statement to erase a single row or all rows from a base table. This is useful to:

  • Remove a row from a table in response to user comments or other triggers.
  • Update a table when a row is removed from the repository.
  • Replace the entire contents of a table with new content.

Before deleting a row:

  • You must know the table's table ID.
  • You must have the row ID. You can get this from a previous SELECT statement.

When you delete row(s) 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(s) 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(s) 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(s) are not removed from the merged table. What is removed? The values in the row(s) that were drawn from the base table where the row(s) were deleted. (For those familiar with SQL: the base table was a lookup table in the join.)

Permissions

You can delete rows in the table if authenticated as an owner or editor on the table.

Usage restrictions

You can delete rows only from base tables. You can not delete a row from a view or a merged table.

Syntax

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

https://www.google.com/fusiontables/api/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.

Syntax Details

Examples

Note: The examples are shown without the required URL encoding for the sake of readability. If you cut and paste, run it through an encoder before use.

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.google.com/fusiontables/api/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'

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 Experimental versions of the web application tutorial) or you can extend the application by using the API with the FusionTablesLayer Maps API v3.0 object. If you're not familiar with the Fusion Tables Layer, you can find more information in this blog post.

Note: If you're using the Fusion Tables Layer 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 100,000 rows of data in a table are mapped or included in spatial query results.*
    Therefore:
    • Maps made with Fusion Tables layer in the Google Maps API or with the Fusion Tables web application will only show data from within this first 100,000 rows. If you apply a filter to a table larger than 100K rows and the filter matches data in rows after the first 100K, these rows will not appear on the map.
    • Queries with spatial predicates only return data from within this first 100,000 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 100K, 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 show replace lines or polygons with dots.*

Maps API Premier customers can contact Enterprise Support about modifications 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 create a correlation between data and 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 syntaxes.

  • 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>'