How to / Configure data export to Google Fusion Tables
Google Fusion Tables, which is part of Google Drive, is a tool for visualising large sets of data. EpiSensor’s Gateway supports a direct push of data via the Google Fusion Tables API. The following section describes how to configure this data export.
Note: The Gateway must have access to the Internet to use this feature.
Step 1 – Sign into an existing Gmail / Google Apps account, or create a new account.
Step 2 – Go to: https://code.google.com/apis/console/ and select ‘Create Project’.
Step 3 – On the services page (which should be shown after the project is created) enable the Google Fusion Tables API
Step 4 – Navigate to ‘API Access’ from the menu on the left and click ‘Create an Oauth 2.0 client ID’
Step 5 – For the product name, enter “EpiSensor Gateway”. Use the following link: /wp-content/logo.gif for the product logo and for homepage URL enter: http://episensor.com – these parameters can be changed as required and will not effect the operation of the gateway. Click next.
Step 6 – On the next screen, select ‘Installed Application’ and ‘Other’ as per the screenshot below, then click ‘Create Client ID’ – this will produce a ‘Client ID’ and ‘Client Secret’
Step 7 – Go to Google Drive (signed into the same Google account) and create a new folder. Then select Create > More > Fusion Table from the menu.
Step 8 – When the dialog box below is shown, select ‘Create Empty Table’
Step 9 – When the table has been created, select Edit > Change Columns from the main menu. The following dialog box will be shown.
There are two columns required for the EpiSensor Gateway to push data to Google Fusion Tables – ‘Date’ and ‘Value’. Using the dialog box below, edit the table to have two columns with the following names, types and formats:
Column Name: Value
Column Name: Date
Format: Dec 25, 2008 3:30:45 AM
Step 10 – Note the table’s ‘Encrypted ID’ using by navigating to File > About this table in the main menu. The following dialog box will be shown:
Step 11 – Log into the EpiSensor Gateway and navigate to Settings > Data Export. Select ‘Google Fusion Tables via HTTPS’ as the Data Export Type, and an appropriate data export interval. Enter the Client ID and Client Secret that were generated in Step 6.
Click ‘Save Changes’ – when the form has been submitted the following dialog box should be shown:
It is important to follow these instructions and approve the device (EpiSensor Gateway) for access to the Fusion Tables API within 30 minutes. To do this, navigate to http://www.google.com/device while signed into your Google account. Enter the ‘User Code’ as that was returned in the dialog box above, and click ‘Allow Access’ on the following screen:
If this has not been done within 30 minutes, the approval request will expire. In this case (or to refresh the access credentials) navigate to Settings > Data Export on the EpiSensor Gateway, click ‘Save Changes’ again, and repeat the approval process.
Step 12 – Navigate to Nodes > Node List on the EpiSensor Gateway, and select the node from which you would like to export data to Google Fusion Tables, then select the relevant sensor. Paste the ‘Encrypted ID’ of the fusion table created above into the ‘Export ID’ field of the sensor settings page on the EpiSensor Gateway. Data will then be exported to Google Fusion Tables for this sensor. Repeat for other sensors on the Gateway.
Note: To enable Google Fusion Tables for other sensors, repeat steps 7, 8, 9, 10 and 12.
Note: The Google Fusion Table should be given a more friendly name to identify it, for example: Area 1 Temperature.
In the case that export to the Google Fusion Table fails, the data points will be stored in CSV format in a file on the Gateway’s file system. There will be one file per sensor/table. The “Encrypted ID” of the google fusion table is included in the filename. The data points in these files will be retried periodically. The data points are stored in such a format in these files that they could also be manually imported into a table directly. An example of a file saved for retry is show here:
Date,Value "Nov 08, 2012 00:15:00 PM",36569.0 "Nov 08, 2012 00:30:00 PM",36570.0 "Nov 08, 2012 00:45:00 PM",36571.0
An example table insertion request (into table with table id 1NKTlZjtnS0tY96e3xiIiJbttG4BMqNEwUQWw5t8) is shown below:
INSERT INTO 1NKTlZjtnS0tY96e3xiIiJbttG4BMqNEwUQWw5t8 (Date, Value) VALUES ('Nov 08, 2012 00:15:00 PM', 36569.0)
Quotas – as of January 2013, there is a limit of 25,000 API requests per API project per day. It is possible to request an increase, and the current usage can be checked by logging into the API console of your Google Account (link in step 2 above) and navigating to ‘Quotas’ from the menu on the left. Under normal conditions, this limit will not be exceeded.