GFT - Google Fusion Tables

(GDAL/OGR >= 1.9.0)

This driver can connect to the Google Fusion Tables service. GDAL/OGR must be built with Curl support in order to the GFT driver to be compiled.

The driver supports read and write operations.

Dataset name syntax

The minimal syntax to open a GFT datasource is :

GFT:

Additionnal optional parameters can be specified after the ':' sign such as :

tables=table_id1[,table_id2]*: A list of table IDs. This is necessary when you need to access to public tables for example. If you want the table ID of a public table, or any other table that is not owned by the authenticated user, you have to visit the table in the Google Fusion Tables website and note the number at the end of the URL.
protocol=https: To use HTTPS protocol for all operations. By default, HTTP is used, except for the authentication operation where HTTPS is always used.
email=john.doe@example.com: The email of the Google account used for authentication.
password=your_secret_password: The password of the Google account used for authentication.
auth=auth_key: An authentication key as described below.

If several parameters are specified, they must be separated by a space.

Authentication

Most operations, in particular write operations, require a valid Google account to provide authentication information to the driver. The only exception is read-only access to public tables.

The authentication information can be provided by different means :

specifying the email and password parameters in the dataset name, as described above.
specifying them through the GFT_EMAIL and GFT_PASSWORD configuration options/environment variables.
specifying the authentication key through the GFT_AUTH configuration option/environment variable. This value is a key returned by the server when using the email + password method, and that can re-used for later authentication. You can fetch this key by running a first time ogrinfo with CPL_DEBUG set to ON and authenticating with another method. The value will be displayed after the "Auth key : " string. This method has the advantage of not exposing your plain password.
specifying the authentication key through the auth connection parameter.

Geometry

Geometries in GFT tables must be expressed in the geodetic WGS84 SRS. GFT allows them to be encoded in different forms :

A single column with a "lat lon" or "lat,lon" string, where lat et lon are expressed in decimal degrees.
A single column with a KML string that is the representation of a Point, a LineString or a Polygon.
Two columns, one with the latitude and the other one with the longitude, both values expressed in decimal degrees.
A single column with an address known by the geocoding service of Google Maps.

Only the first 3 types are supported by OGR, not the last one.

Fusion tables can have multiple geometry columns per table. By default, OGR will use the first geometry column it finds. It is possible to select another column as the geometry column by specifying table_name(geometry_column_name) as the layer name passed to GetLayerByName().

Filtering

The driver will forward any spatial filter set with SetSpatialFilter() to the server. It also makes the same for attribute filters set with SetAttributeFilter().

Paging

Features are retrieved from the server by chunks of 500 by default. This number can be altered with the GFT_PAGE_SIZE configuration option.

Write support

Table creation and deletion is possible. Note that fields can only be added to a table in which there are no features created yet.

Write support is only enabled when the datasource is opened in update mode.

The mapping between the operations of the GFT service and the OGR concepts is the following :

OGRFeature::CreateFeature() <==> INSERT operation
OGRFeature::SetFeature() <==> UPDATE operation
OGRFeature::DeleteFeature() <==> DELETE operation
OGRDataSource::CreateLayer() <==> CREATE TABLE operation
OGRDataSource::DeleteLayer() <==> DROP TABLE operation

When inserting a new feature with CreateFeature(), and if the command is successfull, OGR will fetch the returned rowid and use it as the OGR FID. OGR will also automatically reproject its geometry into the geodetic WGS84 SRS if needed (provided that the original SRS is attached to the geometry).

Write support and OGR transactions

The above operations are by default issued to the server synchronously with the OGR API call. This however can cause performance penalties when issuing a lot of commands due to many client/server exchanges.

It is possible to surround the CreateFeature() oepration between OGRLayer::StartTransaction() and OGRLayer::CommitTransaction(). The operations will be stored into memory and only executed at the time CommitTransaction() is called. Note that the GFT service only supports up to 500 INSERTs and up to 1MB of content per transaction.

Note : only CreateFeature() makes use of OGR transaction mechanism. SetFeature() and DeleteFeature() will still be issued immediately.

SQL

SQL commands provided to the OGRDataSource::ExecuteSQL() call are executed on the server side, unless the OGRSQL dialect is specified. The subset of SQL supported by the GFT service is described in the links at the end of this page.

The SQL supported by the server understands only natively table id, and not the table names returned by OGR. For conveniency, OGR will "patch" your SQL command to replace the table name by the table id however.

Examples

Listing the tables and views owned by the authenticated user:

ogrinfo -ro "GFT:email=john.doe@example.com password=secret_password"

Creating and populating a table from a shapefile:

ogr2ogr -f GFT "GFT:email=john.doe@example.com password=secret_password" shapefile.shp

Displaying the content of a public table with a spatial and attribue filters:

ogrinfo -ro "GFT:tables=224453" -al -spat 67 31.5 67.5 32 -where "'Attack on' = 'ENEMY'"

Getting the auth key:

ogrinfo --config CPL_DEBUG ON "GFT:email=john.doe@example.com password=secret_password"

returns:

HTTP: Fetch(https://www.google.com/accounts/ClientLogin)
HTTP: These HTTP headers were set: Content-Type: application/x-www-form-urlencoded
GFT: Auth key : A_HUGE_STRING_WITH_ALPHANUMERIC_AND_SPECIAL_CHARACTERS

Now, you can set the GFT_AUTH environment variable to that value and simply use "GFT:" as the DSN.