GEOG 863
GIS Mashups for Geospatial Professionals

Using Fusion Tables as a Data Source

PrintPrint

Google’s Fusion Tables technology offers a number of advantages to map developers. Among these is the ability to load a “layer” of overlays with just a single line of code and to define symbology without writing any code. Let's walk through the usage of Fusion Tables as a data source for a Google Map with our familiar Jen and Barry's data. (If you don't have the Jen and Barry's data from GEOG 483, you can download it here.)

Obtaining a Google account

  1. If you don't already have one, sign up for a Google Account.
  2. After creating and signing in to your account, navigate to the Google Fusion Tables home page.
  3. Click on the Create link to get started.

Typically the easiest way to populate a Fusion Table is to import the data from an existing spreadsheet, delimited text file or KML file. It is also possible to create a new table from scratch.

Importing from a csv file

Point feature classes are convenient because it is possible to add fields to the attribute table in ArcMap, populate those fields with the coordinate values associated with the points, then export the feature class to a comma-delimited text file. That text file can then be imported into a Fusion Table. Let's follow those steps with the cities shapefile.

  1. Load the cities shapefile into ArcMap and open its attribute table.
  2. Add two new Double fields to the attribute table named X and Y.
  3. Right-click on the X field header and select Calculate Geometry.
  4. Choose X Coordinate of Point from the Property list and confirm that the values will be in the GCS/NAD27 coordinate system and in Decimal Degree units. Click OK.
  5. Repeat this step to populate the Y field.
  6. Open the cities layer's attribute table and select Export from the Table Options drop-down list (upper-left corner of the table).
  7. Select Text File from the Save as type drop-down list, give the file a logical name and click OK.
    Note: Number formats applied in ArcMap don't appear to be "stick" when exporting; if this matters to you, may want to open the exported file in Excel, format there and import the Excel file to a fusion table.
  8. Open the text file in a text editor and verify that it was exported correctly.
  9. Back in your web browser, you should be seeing an Import new table dialog. Click the Browse button.
  10. Browse to the location of the exported file. Note that it is possible to specify the character that separates the values in the import file as a comma, tab, colon or some other character.
  11. Click the Next button to move to the next part of the import process.
  12. If satisfied that your file is being read correctly, click Next.
  13. The last step in the import process is to add/modify some properties of the new Fusion Table. For example, its name defaults to that of the import file, but can be changed to something else. When satisfied with these properties, click Finish to complete the import process.

Preparing your Fusion Table to be mapped

Once your ArcMap export file has been imported as a fusion table, you should confirm that the table is set to use the values in the X/Y columns to map the points.

  1. Select Edit > Change Columns.
  2. Highlight the X column to view its details on the right side of the dialog. The Two column location checkbox should be checked, the Latitude column should be set to Y and the Longitude column to X. (The X and Y columns were set the opposite by default when I went through this step.) Click Save when finished specifying the lat and long columns.
  3. Click on the Map of X tab to see your points on a map. You can return to the tabular view by selecting the Rows tab.
    Note: If you don't see a map in this step, try clicking the red "plus" icon to the right of all the tabs and select Add map. You should see a map now.
  4. When you click on a point, an info window will present values from each column of the table by default. You can override this by clicking the dropdown arrow on the Map tab and selecting Change info window layout. The resulting dialog allows for two levels of customization. Under the Automatic tab, you can uncheck the columns that you’re not interested in seeing. Under the Custom tab, you can modify the HTML used to present the information.
  5. It is also possible to modify the symbology of the points by clicking the Change map styles from the same dropdown menu. Options include displaying all the points in the same style (Fixed tab), displaying them using a marker icon specified in one of the table’s columns (Column tab) and defining classes and using a different icon for each class (Buckets tab).

    Try This

    See if you can set up a 3-bucket classification based on the Population column with the following bucket definitions: 0-50000, 50000-100000, 100000+. Then try a 2-bucket classification based on the 1/0 values in the University column.

    The final step in preparing your fusion table to be incorporated into a custom Google Map is to make it possible for anyone to view its contents. (It’s only visible to you by default.)
  6. Click on the Share button in the upper right corner of the page. In the resulting dialog, click the Change link under the Who has access heading. Note that it is also possible in this dialog to specify a list of people that you’d like to grant editing privileges.
  7. Select Public on the web and Save to finish setting the table's visibility.
  8. Click Done to dismiss the Sharing settings dialog.

With that, you are now ready to incorporate your Fusion Table data into a map.

Adding a FusionTableLayer to a Google Map

  1. With your Fusion Table open, go to File > About this table.
  2. In the resulting dialog, you should see a long alphanumeric ID. Highlight and copy this ID.

  3. Next, add the following lines of JavaScript code to your page:

    var lyr = new google.maps.FusionTablesLayer('xxxxxxx');  // Replace xxxxxxx with your table’s ID
lyr.setMap(map); // where ‘map’ is a reference to the Map object in your code

If you look at the FusionTablesLayer section of the API Reference, you should note that its constructor has a single FusionTablesLayerOptions parameter, whereas the code snippet above shows specifying the table ID (a string). The ID string syntax was documented in older versions of the API and still appears to work. Whether that syntax should be considered deprecated (i.e., going away eventually) or undocumented at this point is unclear.

In any case, let's talk about the FusionTablesLayerOptions object that appears in the documentation. This Options object offers a number of properties for configuring the layer. Most notable among these are:

  1. query – used to retrieve a subset of records rather than the full fusion table
  2. styles –used to override the style applied to the table through the point-and-click interface.

Creating a FusionTablesQuery object involves the same kind of logic that one might use to build a query with SQL. The difference is that the parts of the query need to be specified in object literal notation. The API Samples page includes a link to a sample demonstrating the use of a FusionTablesQuery.

The styles property must be set to an array of 1 to 5 FusionTablesStyle objects. This array makes it possible to apply different styles to different categories of records in much the same way that the point-and-click interface makes it possible through the Buckets tab. Again, there is a sample in the API documentation that demonstrates styling.

Importing KML into a Fusion Table

The method of adding coordinate values to the attribute table in ArcMap before exporting outlined above works great for point feature classes, but is not practical for line or polygon feature classes. For those geometry types, a better solution is to export the data in KML format. Let’s do that for the counties shapefile.

  1. Apply the desired symbology to the counties layer in ArcMap.
  2. Open ArcToolbox.
  3. Open the Layer to KML tool (under Conversion Tools > To KML).
  4. In the Layer to KML dialog, select the counties layer.
  5. Specify the desired name for the output file. Note that the tool outputs the data as compressed KML (in a .kmz file).
  6. If your layer relies on scale-dependent rendering, enter the scale at which you’d like it to be exported in the Layer Output Scale box. Otherwise, a value of 1 will work in this box.
  7. Skip the rest of the properties in the dialog (unless you have good reason not to) and click OK.
  8. As mentioned above, the output file is actually compressed to minimize its size. The Fusion Tables Import tool can only handle uncompressed KML, so you’ll need to unzip it. Depending on the setup of your computer, you may be able to unzip the .kmz file directly. If not, rename it, changing the .kmz suffix to .zip.
  9. After unzipping, you should be left with a file called doc.kml. I suggest you rename this file to something like counties.kml.
  10. Return to the Fusion Tables page referenced above, click Create, then Browse to your KML file.
  11. After selecting the KML file, you should see a preview indicating that three columns will be imported from the file: description, name, and geometry. Click Next, then give the new Fusion Table a name and specify any other desired properties.
  12. Click Finish to complete the import.
  13. View the contents of the table and note that the description column contains a bit of HTML for each record and the geometry column simply lists the word kml to indicate that there is a block of KML stored in that cell.
  14. Select Map of Geometry to confirm that your data imported properly.
  15. You can now apply styling and add your Fusion Table to a Google Map using the methodology outlined above.

Adding a clickable sidebar

As with KmlLayers, the downside to FusionTablesLayers is that there is no way to work with the individual features that are displayed by the layer. The FusionTablesQuery object can be used only to specify which features from the table to display in the layer. There appears to be nothing built into the Fusion Tables API that enables querying records from a table and working with those records individually. However, it is still possible to build a map with a clickable sidebar using data stored in a Fusion Table. The solution involves sending a query that retrieves the desired data to Google's Visualization API.

The Visualization API is typically used for building dynamic charts, but it is useful in this situation because it can be used to obtain a DataTable object that encapsulates the data requested by a query. The DataTable class and other classes are documented in the Visualization API Reference, which is formatted much like the Maps API Reference you've been consulting throughout the course.

Have a look at this Fusion Table Markers and Sidebar example and view its source code to follow along with the discussion below:

  • The example follows the same basic approach used earlier in the lesson to build a clickable sidebar from data stored in an XML file. The primary difference is in how the data are retrieved.
  • Making use of the Visualization API requires the addition of two lines near the top of the document: a) the script element that sources the API at the URL http://www.google.com/jsapi, and b) the google.load statement. (The API includes a number of packages offering different bits of functionality. We're using the 'table' package. If you wanted to build charts, you would load the 'chart' package as well.)
  • An SQL SELECT statement is used to retrieve information from a table that stores the cities from the Jen and Barry's scenario. Note the ORDER BY clause that causes the results to be returned in alphabetical order by city name.
  • The SQL statement is eventually included as a parameter in a URL. Because it contains special characters that are dangerous to use in URLs (such as spaces and commas), the SQL statement is first passed to JavaScript's encodeURIComponent() function. This function encodes special characters to ensure that they are interpreted correctly.
  • The query.send(getData)statement specifies that when a response comes back from the Viz API, it should be forwarded to the getData() function (defined just below the initialize() function).
  • The API retrieves the data and passes a QueryResponse object to the getData() function. A reference to this object is stored in the response variable.
  • The DataTable object encapsulated within the QueryResponse is obtained using the getDataTable() method and stored in the dt variable.
  • A for loop is used to step through each row in the DataTable. The upper bound of the loop is set by calling the DataTable's getNumberOfRows method.
  • Within the loop, values from the current row are retrieved using the getValue() method. The first argument to getValue() specifies the desired row (in this case, i, which changes on each pass through the loop) and the second argument the column. Note that the column number is based on the columns retrieved by the query, not on the column's position in the parent table.
  • Once the table values are obtained, the rest of the script functions just like the XML example.

This example works well when dealing with point data. Creating a clickable sidebar for line or polygon data in a Fusion Table is a much tougher nut to crack since those geometry types are typically stored in the table as KML. Those of you who are highly experienced programmers may want to consider having a go at this topic for your final project. I have some ideas on how to approach the problem that I would be happy to share.

With that, let's switch gears a bit and move on to the topic of Ajax, a programming paradigm that has revolutionized both web mapping and web publishing in general.