The Google Fusion Table (Spatial) Reader produces an FME feature for each row in a worksheet in the provided spreadsheet. The reader will create geometry from KML data or latitude/longitude data in the same column. The reader cannot handle geocoded location data or latitude/longitude data in two separate columns. All columns of the Google Fusion Table will appear as attributes in the feature type, including all location columns.
Google Fusion Tables formats allow you to easily upload and manage data in the cloud (for example, spreadsheets and comma-separated value [CSV] files).
Google Fusion Tables makes it easy to create visualizations like maps, timelines and other charts, and either share these with collaborators or make them publicly accessible. Users can also merge datasets that belong to different owners.
It offers a REST API to run SQL-like queries to manage tables (create, delete), manage data rows (insert, update, delete), and query the table for all rows that match spatial or data conditions. The results of queries can be output to a CSV file, or used in the Google Maps API or Google Chart Tools.
There are two versions:
- Google Fusion Tables Non-spatial, which supports all the column types as attributes,
- Google Fusion Tables Spatial which, for convenience, creates geometry from location columns
Both versions can read from and write to private and public tables.
Please note that only KML geometry or latitude/longitude points in the same column will be interpreted as geometry by the Google Fusion Tables Spatial Reader, although multiple latitude and longitude columns or geocoded data will be preserved as attributes for subsequent processing.
The FME Google Fusion Tables Reader can read geometric portions of private or public tables when present, or non-geometry or attribute processing when no location columns are present.
The Google Fusion Tables (both Spatial and Non-Spatial) formats do not use a dataset like most other formats. Instead, connection parameters are supplied similar to a database connection by clicking the Parameters button when generating a new workspace or adding a reader.
Connection
Refresh Token
This parameter specifies a refresh token for OAuth 2.0 authentication to a specific Google account.
If you already know the token, you can copy/paste it into this parameter. To obtain a new token, click the browse button. This will prompt for a Google account authentication page from Google.
It is recommended that you save the defaults (from the Defaults button on the dialog) once the refresh token is retrieved so you will not have to re-authenticate in the future.
Constraints
Table List
Google Fusion Tables allows for blank and duplicate column names. Inside FME, blank column names will be given the name col. Subsequent blank columns will be given the names col0, col1, etc. If there are duplicate column names, the first instance will retain the original name while any following duplicates will have a number starting at 0 appended to the name. Public tables can be accessed by appending table IDs (space delimited) to the Table List text box.
WHERE Clause
An SQL WHERE clause can be applied to the selected tables, to constrain the the row selection in tables chosen in the table list (for example, NUMLANES=2).
Schema Attributes
Additional Attributes to Expose
Use this parameter to expose Format Attributes in Workbench when you create a workspace:
- In a dynamic scenario, it means these attributes can be passed to the output dataset at runtime.
- In a non-dynamic scenario, you can use this parameter to expose additional attributes on multiple feature types.
Use Search Envelope
Using the minimum and maximum x and y parameters, define a bounding box that will be used to filter the input features. Only features that intersect with the bounding box are returned.
If all four coordinates of the search envelope are specified as 0, the search envelope will be disabled.
Clip to Search Envelope
When selected, this parameter removes any portions of imported features being read that are outside the Search Envelope.
The example below illustrates the results of the Search Envelope when Clip to Search Envelope is not selected (set to No) and when it is selected (set to Yes).
- No: Any features that cross the search envelope boundary will be read, including the portion that lies outside of the boundary.
- Yes: Any features that cross the search envelope boundary will be clipped at the boundary, and only the portion that lies inside the boundary will be read. The underlying function for the Clip to Search Envelope function is an intersection; however, when Clip to Search Envelope is selected, a clipping operation is also performed in addition to the intersection.
Advanced
SQL to Run Before Read
You may want to execute some ad-hoc SQL prior to reading or writing a table. For example, it may be necessary to ensure that a view exists prior to attempting to read from it.
Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER keyword, embedded at the beginning of the SQL block. The single character following this keyword will be used to split the SQL, which will then be sent to the database for execution.
Note: Include a space before the character.
For example:
FME_SQL_DELIMITER ;
DELETE FROM instructors;
DELETE FROM people WHERE LastName='Doe' AND FirstName='John'
Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.
Any errors occurring during the execution of these SQL statements will normally terminate the reader with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.
SQL to Run After Read
You may want to execute some ad-hoc SQL after reading or writing a set of tables. For example, it may be necessary to clean up a temporary view after writing to the database.
Multiple SQL commands can be delimited by a character specified using the FME_SQL_DELIMITER directive, embedded at the beginning of the SQL block. The single character following this directive will be used to split the SQL, which will then be sent to the database for execution.
Note: Include a space before the character.
For example:
FME_SQL_DELIMITER ; DELETE FROM instructors; DELETE FROM people WHERE LastName='Doe' AND FirstName='John'
Multiple delimiters are not allowed and the delimiter character will be stripped before being sent to the database.
Any errors occurring during the execution of these SQL statements will normally terminate the reader with an error. If the specified statement is preceded by a hyphen (“-”), such errors are ignored.