Google Fusion Tables API

Row and Query SQL Reference

This document describes how to use the SQL queries of the Fusion Tables API to handle rows of data and to execute queries against a table. For more information about how to work with Fusion Tables rows, please see the Working with rows section on the Using the API page.

  1. Querying rows
    1. SELECT
  2. Managing rows
    1. INSERT
    2. UPDATE
    3. DELETE
  3. Managing views and merged tables
    1. CREATE VIEW

    SELECT

    To query for data, send a GET request to the Google Fusion Tables server using the 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> }
    
    

    Returns

    If the SELECT statement is successful, it returns typed JSON data. You can also obtain untyped JSON or CSV formatted data with a query parameter.

    Note: Fusion Tables returns data in UTF-8 format.

    SyntaxDescription
    <column_spec>

    Tells which columns you want to include in the output of the SELECT. When used in an ORDER BY clause, tells which column to use when sorting the rows in the result set. You must include in the SELECT statement any column that you are sorting on (see the examples below).

    Use any of the following:

    • <column_name> — described later in this table.
    • <aggregate_spec> — described later in this table. Note that you cannot use an <aggregate_spec> in an ORDER BY statement.
    • ROWID — retrieve for use in a later INSERT, UPDATE, or DELETE statement.
    • * — use an asterisk to retrieve all columns in the table (except the row IDs). You cannot use * in an ORDER BY statement.

    Note: ORDER BY affects the order in which tabular data is returned, not how it is rendered in layers. You cannot control the order in which map objects, such as polygons, are rendered in a Fusion Table layer.

    If you use ORDER BY, you need to include the column name in the SQL projection. That is, if you want the data to be ordered by Address, you need to write the SELECT statement thus: SELECT 'Store Name', Hours, Address FROM 1tqv1e7y689555xxx5_eeee345ee_CvWxxxhg9gc ORDER BY ST_DISTANCE(Address, LATLNG(37.5242,-121.6806)) LIMIT 10. This includes "Address" in the SELECT projection.

    <column_name>The name of a column in the table. Case sensitive. To find out what columns are available in a given table, use the Google Fusion Tables API 1.0 to retrieve a list of all columns in a table. If the column name is anything besides a letter followed by letters, digits, and underscore characters or if it is a reserved keyword, surround the name with single quotes; examples, 'Volcano Name', 'SELECT', and 'circle'.

    Note: Column names are case sensitive.
    <aggregate_spec>

    An aggregation specification applies a function to all the values of one column from the retrieved records. It returns the one number, the result of the function, instead of the column value. Note that you cannot use an <aggregate_spec> in an ORDER BY statement.

    Use any of the following functions (which behave exactly like their SQL counterparts):

    • COUNT( )
    • SUM ( <column_name> )
    • AVERAGE ( <column_name> )
    • MAXIMUM ( <column_name> )
    • MINIMUM ( <column_name> )

    If the SELECT statement contains a <spatial_condition> or <spatial_relationship>, the query cannot be aggregated by the <location_column>.

    <table_id> The unique table ID of the table from which you want to get data. It must be a table on which you have the necessary permissions.
    <filter_condition>

    A Boolean expression (returns TRUE or FALSE). Only table rows where the condition is TRUE will be included in the results of the SELECT. Use AND to combine multiple filters into a single query. This is a pre-aggregation filter: it is applied before the functions in the <aggregate_spec> are calculated.
    OR is not supported. (If you are looking for OR, consider the IN operator. It will do the trick in certain queries).

    Use one of the following:

    • <column_condition>
    • <row_condition>
    <column_condition>

    Used in the WHERE clause. The syntax is: <column_name> <operator> <value>

    <column_name> is described earlier in this table.

    For <operator>, use one of the following with a <number>:

    • >, <, >=, <=, =

    For <operator>, use one of the following with a <string>:

    • >, <, >=, <=, =
    • LIKE
    • MATCHES (equivalent to LIKE)
    • STARTS WITH
    • ENDS WITH
    • CONTAINS
    • CONTAINS IGNORING CASE
    • DOES NOT CONTAIN
    • NOT EQUAL TO
    • IN

    The <value> can be one of the following:

    • <number> — A floating point number or integer.
    • '<string>' — A text string in quotes. If the string contains quote characters, precede each quote character with a backslash (\).
    • (<number>{, <number>}) | ('<string>'{, '<string>'}) — For IN only. A list of one or more values. Format date and location values as strings.

    Filtering on LOCATION

    When filtering on a column of type LOCATION, the value will behave like the underlying text or string data type. The following is not recommended:

    SELECT * FROM <table_id> WHERE latitude > <number> AND latitude < <number> AND longitude > <number> AND longitude < <number>

    Instead, use a <spatial_condition> with a <rectangle> geometry, as described later in this table.

    Filtering on DATETIME

    When filtering on a column of type DATETIME, the <value> should be formatted as one of the following supported formats:

    • MMM dd, yy
    • MM/dd/yy
    • MM-dd-yy
    • MMM-dd-yy
    • yyyy.MM.dd
    • dd-MMM-yy
    • MMM/yy
    • MMM yy
    • dd/MMM/yy
    • yyyy

    Filtering on formulas or variables

    Formulas and variables are not supported.

    <row_condition>

    Used in the WHERE clause. The syntax is: ROWID = '<string>'

    • ROWID — Use a row ID that you obtained from a previous SELECT or INSERT.
    • '<string>' — A text string in quotes. If the string contains quote characters, precede each quote character with a backslash (\).

    <spatial_condition>

    Used in the WHERE clause. The syntax is: ST_INTERSECTS(<location_column>, <geometry>)

    ST_INTERSECTS cannot be combined with ORDER BY ST_DISTANCE.

    <circle>

    The syntax is: CIRCLE(<coordinate>, <radius>)

    <coordinate> is the center of the circle.

    <rectangle>

    The syntax is: RECTANGLE(<lower_left_corner>, <upper_right_corner>)

    Use two <coordinate> to define a rectangular bounding box.

    <coordinate>

    LATLNG(<number>, <number>) — A latitude, longitude pair describing a point in decimal degrees. Separate with a comma. A space is optional.

    Note that it is the reverse order of how a point is specified in KML.

    GROUP BY

    Places the output rows into groups that share a common value in one column (see <column_name> earlier in this table). The purpose is to calculate summary data for each group and display only one row per group. Each row contains the fields that were used to create the group and one or more aggregate results for the other included fields.

    If the SELECT statement contains a <spatial_condition> or <spatial_relationship>, the query can not be grouped by the <location_column> used in the <spatial_condition> or <spatial_relationship>.

    ORDER BY Sorts the output rows using the values in a single column or <spatial_relationship>. You must include in the SELECT statement any columns that you are sorting on.
    <spatial_relationship>

    Used in the ORDER BY clause. The syntax is: ST_DISTANCE(<location_column>, <coordinate>)

    For <location_column>, use a <column_name> in your table that contains location data.

    Listing the <location_column> as a <column_spec> is optional when using ORDER BY ST_DISTANCE.

    ORDER BY ST_DISTANCE cannot be combined with any of the ST_INTERSECTS conditions.

    ASC | DESCUse ASC to sort the values in the query result set in ascending order (A-Z, 1-100). Use DESC for descending order (Z-A, 100-1). Default: ASC.
    OFFSETSkips the first <number> rows. The first row returned is from position <number> + 1.
    LIMITReturns only a given number of rows. The purpose is to avoid getting more data than can be handled by the application or computer resources on the receiving end.
    <number>An integer. Must be a literal, not an expression.
    <location_column> A <column_name> for the column in your table that contains location data.
    <geometry> Use one of the following:

    • <circle>
    • <rectangle>

    <radius> An integer that represents the radius of the circle in meters.

    INSERT

    To insert one or more rows in a table, use the following syntax in an HTTP POST request:

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

    Note: You can list up to 500 INSERT statements, separated by semicolons, in one request as long as the total size of the data does not exceed 1 MB and the total number of table cells being added does not exceed 10,000 cells. If you are inserting a large number of rows, use the import method instead, which will be faster and more reliable than using many SQL INSERT statements.

    Returns

    If successful, returns the row ID of each new row in JSON format.

    SyntaxDescription
    <table_id>The unique table ID of the table into which you want to add rows. It must be a table on which you have the necessary permissions.
    <column_name>The name of a column, or a list of column names separated by commas. Must exactly match the column names that have already been defined for the table. To find out what columns are available in a given table, use the Google Fusion Tables API 1.0 to retrieve a list of all columns in a table.

    Note: Column names are case sensitive.
    <value>The floating point number, integer, or string value that you want to assign to the column, or a list of values separated by commas. If the value is a string that contains quote characters, precede each quote character with a backslash (\). To leave the column empty, assign it the empty string ('').

    In a column of type LOCATION, the value can be a string containing an address, city name, country name, or latitude/longitude pair. The string can also use KML code to specify a point, line, or polygon, as follows:

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

    For more information, see Parsing different data types and Working with geographic data. For details on KML syntax, see the entries for Point, LineString, and Polygon in the KML Reference.

    ; Separator for listing multiple INSERT statements in a single POST request.
    Note: You can list up to 500 INSERTs, separated by semicolons, as long as the total size of the data does not exceed 1 MB and the total number of table cells being added does not exceed 10,000 cells. The calculation may not be obvious. Let's say your table has 2 columns: name and age. Your update query is as follows:
    INSERT INTO <tableid> (name) VALUES ('kat')
    Even though only one column is listed in the INSERT query, 2 cells are being updated: both the name and the age column. The age column is given an empty value.

    UPDATE

    To update values in one or more columns of an existing row in a table, use the following syntax in an HTTP POST request:

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

    Returns

    If the request succeeds, the server responds with a 200 OK status code and the number of affected rows in JSON format.

    SyntaxDescription
    <table_id> The unique table ID of the table that you wish to update. It must be a table on which you have the necessary permissions.
    <column_name>The name of a column where you want to modify the data. Must be a column name that has already been defined for the table. Must exactly match the column names that have already been defined for the table. Note: Column names are case sensitive.

    To find out what columns are available in a given table, use the Google Fusion Tables API 1.0 to retrieve a list of all columns in a table. In a merged table, this must not be the column that was used to define the merge (the primary key); if you want to change a primary key value, do so in the base table instead.
    <value>The floating point number, integer , or string value that you want to assign to the column. If the value is a string that contains quote characters, precede each quote character with a backslash (\).

    In a column of type LOCATION, the value can be a string containing an address, city name, country name, or latitude/longitude pair. The string can also use KML code to specify a point, line, or polygon, as follows:

    • 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>
      <LinearRing>
      <coordinates> lng,lat[,alt] lng,lat[,alt] ... </coordinates>
      </LinearRing>
      </outerBoundaryIs>
      </Polygon>
      

    For more information, see Parsing different data types and Working with geographic data. For details on KML syntax, see the entries for Point, LineString, and Polygon in the KML Reference.

    <row_id>The ID of the row you want to update. A quoted string. To get the row ID, perform a SELECT before the UPDATE.

    DELETE

    To delete a single row or all rows from a table, use the following syntax in an HTTP POST request:

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

    Returns

    If the request succeeds, the server responds with a 200 OK status code and the number of affected rows in JSON format.

    SyntaxDescription
    <table_id>The unique table ID of the base table from which you want to delete one or more rows. It must be a table on which you have the necessary permissions.
    <row_id>The ID of the row you want to delete. A quoted string. To get the row ID, perform a SELECT before the DELETE.

    CREATE VIEW

    To create a new view, use the following syntax in an HTTP POST request:

    CREATE VIEW <new_table_name>
    
    AS (SELECT  <column_spec> {, <column_spec>}*
    
    FROM <table_id>
    
    {WHERE <filter_condition> { AND <filter_condition> }*})
    

    To create a new merged table, use the following syntax in an HTTP POST request:

    CREATE VIEW <new_table_name>
    
    AS (SELECT  <column_spec> {, <column_spec>}*
    
    FROM <table_list>)
    
    SyntaxDescription
    <new_table_name>A name for the view or merged table.

    Can include letters, numbers, white space, and special characters. If the view name is anything besides a letter followed by letters, digits, and underscore characters or if it is a reserved keyword, surround the name with quotes. Examples: 'My View' and 'circle'.
    <column_spec>

    Tells which columns you want to include in the new view or merged table. Use one of the following:

    • <column_ref>
    • <column_ref> AS <column_alias>
    • * — use an asterisk to retrieve all columns in the table.

    Note that * will be expanded at table creation time to explicitly list the included columns. Thus, even if a column is added or deleted from a source table, the columns in this view will not change. Also, * will be expanded to include all columns in the source tables, except for join columns from non-leftmost tables.

    <column_name>The name of a column in the table. To find out what columns are available in a given table, use DESCRIBE. If the column name is anything besides a letter followed by letters, digits, and underscore characters or if it is a reserved keyword, surround the name with quotes; examples, 'Volcano Name', 'SELECT', and 'circle'. Note: Column names are case sensitive.
    <column_ref>An expression specifying a column in a <column_spec> or the <table_list>. Use one of the following:

    • <table_ref>.<column_name> — The column in the specified table with the given name.
    • <column_name> — The column in the tables listed in <table_list> with the given name.

    When creating merged tables, if the <table_ref> is not provided, then the table is assumed to be the leftmost table in <table_list> that contains a column with the given name. If there are multiple columns in a table with the given name, the <column_ref> will refer to the column with the lowest column ID.
    <column_alias>A name for the column in the view. If the alias is anything besides a letter followed by letters, digits, and underscore characters or if it is a reserved keyword, surround the name with quotes; examples, 'Geological feature', 'SELECT', and 'circle'.
    <table_list>

    An expression listing the tables and join conditions from which to create a merged table.

    The syntax is: <table_spec> {LEFT OUTER JOIN <table_spec> ON <column_ref> = <column_ref>}*

    The LEFT OUTER JOIN clause specifies how to merge other tables into the leftmost table.

    In the ON clause, the left column refers to the leftmost table in the <table_list>, and the right column refers to the table in the LEFT OUTER JOIN clause. Note that you may also use a <column_alias> instead of a <column_ref> in the <ON> clause.

    <table_spec>

    An expression identifying a table. Use one of the following:

    • <table_id>
    • <table_id> AS <table_alias>

    <table_id>The unique table ID of the table from which you want to create the view or merged table. It must be a table on which you have the necessary permissions.
    <table_alias>

    An optional name for a table that can be used to refer to the table elsewhere in the query. The table alias must be a letter followed by letters, digits, and underscore characters and may not be a reserved keyword.

    <table_ref>

    A reference to a table that is used to help specify a projected column in <column_spec> or a join condition in the <table_list>. Use one of the following:

    • <table_id>
    • <table_alias>

    <filter_condition> Note: Filter conditions are only supported for views and not for merged tables.

    A Boolean expression (returns TRUE or FALSE). Only table rows where the condition is TRUE will be included in the results of the SELECT. Use AND to combine multiple filters into a single query. OR is not supported.

    Use one of the following:

    • <column_ref> <operator> <value>
    • <column_alias> <operator> <value>

    The syntax for <column_ref> and <column_alias> is described earlier in this table.

    For <operator>, for now we only support the = operator.

    The <value> can be either of the following:

    • <number> — a floating point number or integer.
    • '<string>' — a text string in quotes. If the string contains quote characters, precede each quote character with a backslash (\).

    Applying an equality filter restricts data that can be inserted into the new view. Any new data has to match the value specified in the equality. For example, if your equality statement is 'location' = 'Spain', any new rows added to the view must have a location equal to "Spain".

    If the column on which the equality is based is not included in the view, the value will be set automatically for all new rows added to the view. For example, new rows in the view will automatically be assigned the value 'Spain' in the 'location' column of the base table.

    Formulas and variables are not supported.

    Returns

    If successful, returns the table ID of the new view or merged table.

    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 create a view from table 1tqv1e7y689555xxx5_eeee345ee_CvWxxxhg9gc with the location as "Canada":

    CREATE VIEW animals AS (SELECT * FROM 1tqv1e7y689555xxx5_eeee345ee_CvWxxxhg9gc WHERE location = 'Canada')
    

    Example output:

    {
     "kind": "fusiontables#sqlresponse",
     "columns": [
      "tableid"
     ],
     "rows": [
      [
       "1v1e7y689cdf6666sss66_fffff579f33nmihgpo"
      ]
     ]
    }
    

    To create a view with two columns, animal and region, from table 1tqv1e7y689555xxx5_eeee345ee_CvWxxxhg9gc:

    CREATE VIEW animals AS (SELECT animal, location AS region FROM 1tqv1e7y689555xxx5_eeee345ee_CvWxxxhg9gc)
    

    To create a view with two columns, animal and favorite_food, from table 1tqv1e7y689555xxx5_eeee345ee_CvWxxxhg9gc with the location as "China":

    CREATE VIEW animals AS
      (SELECT animal, favorite_food AS food FROM 1tqv1e7y689555xxx5_eeee345ee_CvWxxxhg9gc WHERE location = 'China')
    

    To merge a table 1Ej110snYG9y_baaeNoZrmcAXKoTEtQVyAcY- with the table 1YDlqQzZHVMPxSadct1u5cBBiN341xOCMcO6kSkU joined on the column "Coffee Producing Country" from the first table and the column "Country" from the second table:

    CREATE VIEW 'Coffee Production and GDP Per Capita' AS
    (SELECT *
     FROM 1Ej110snYG9y_baaeNoZrmcAXKoTEtQVyAcY- AS T1
       LEFT OUTER JOIN 1YDlqQzZHVMPxSadct1u5cBBiN341xOCMcO6kSkU AS T2
         ON T1.'Coffee Producing Country' = T2.Country)
    

    To merge a table 1Ej110snYG9y_baaeNoZrmcAXKoTEtQVyAcY- with the table 1YDlqQzZHVMPxSadct1u5cBBiN341xOCMcO6kSkU joined on the column "Coffee Producing Country" from the first table and the column "Country" from the second table, projecting only some of the columns:

    CREATE VIEW 'Coffee Production and GDP Per Capita' AS
    (SELECT 'Coffee Producing Country', Population, 'Harvesting per capita', T2.'Income group'
     FROM 1Ej110snYG9y_baaeNoZrmcAXKoTEtQVyAcY- AS T1
       LEFT OUTER JOIN 1YDlqQzZHVMPxSadct1u5cBBiN341xOCMcO6kSkU AS T2
         ON T1.'Coffee Producing Country' = T2.Country)
    

    To merge more than two tables:

    CREATE VIEW 'Coffee Consumption and Production and GDP Per Capita' AS
    (SELECT *
     FROM 1Ej110snYG9y_baaeNoZrmcAXKoTEtQVyAcY- AS T1
       LEFT OUTER JOIN 1YDlqQzZHVMPxSadct1u5cBBiN341xOCMcO6kSkU AS T2
         ON T1.'Coffee Producing Country' = T2.Country
       LEFT OUTER JOIN 1uRqc-vJFeeLvSEbzawei6pIxTJaH5aL547jKtz4 AS T3
         ON T1.'Coffee Producing Country' = T3.Country)
    

    Reserved words

    Fusion Tables has many 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
    JOIN
    LATLNG
    LEFT
    LIKE
    LIMIT
    MATCHES
    NEAR
    NOT
    OFFSET
    ORDER
    OUTER
    POLYGON
    RECTANGLE
    ROWID
    SELECT
    SET
    SHOW
    SKIP
    ST_DISTANCE
    ST_INTERSECTS
    STARTS
    TABLES
    TABLE
    TO
    UPDATE
    VALUES
    VIEW
    WHERE
    WITH
    ID
    NUMBER
    DOCID
    STRING

Authentication required

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

Signing you in...

Google Developers needs your permission to do that.