Tip: Reading settings from a public table

When viewing a table, you can do many things with the data, such as filter and download just the data you want, make your own map or chart, and share that with others.

Some things aren't in the web application, but can be done with the Fusion Tables API. This article introduces you to constructing URLs to use in your browser and read information from a public table.

You can use this technique to:

  • Get a list of the column names and types
  • Get a complete description of the style or template applied to a map
  • Filter rows using a spatial query
  • Use SQL-like syntax to filter rows
  • Retrieve data rows in JSON for use by Javascript in a web browser

For this technique to work, the table must be exportable and not require sign-in to view it.

Construct a URL to read data from the table 

Here's the general idea: You can read from a table by carefully constructing a URL that identifies the table, the information you want from the table, and identifying yourself.  Paste this URL into a web browser to view the result.

1. Identify the table

Every table has a unique identifier. In the URL for a table, look for the string following "docid=" That's the table's ID.

Or, look in File > About and find the Encrypted ID

Try it yourself with a table of caterpillars collected in Costa Rica.

2. Identify yourself

In the examples, there are a string of characters called a key. Every specific person or project using this technique to access tables should have their own key, so you'll need to get one and swap it into the examples when you try them. 

To get your key, go to the Google Developer console and sign in. Turn on the Fusion Tables API and get your own key.

A hint

The key will always be in the part of the URL with parameters. The first part of the URL ends with a ?, and then the parameters begin. Parameters start with an &, then the parameter name, an =, and the value. As you construct the URL, you'll see: &key={somekeyhere}

If the URL you're trying to use doesn't have a key yet, add this to the end of the URL: ?&key={yourkeyhere}

3. Identify the information you want from the table

Here are some examples of information you can retrieve from a table using this technique.  Where you see { }, replace everything including those curly brackets with the appropriate identifying text.  Then paste into your browser URL bar and press return.

Get a list of the column names and types
https://www.googleapis.com/fusiontables/v1/tables/{tableIDhere}?&key={yourkeyhere}

Read more

 
View the styles used by maps within a table
https://www.googleapis.com/fusiontables/v1/tables/{tableID}/styles/?&key={yourkeyhere}
 
View a specific style
https://www.googleapis.com/fusiontables/v1/tables/{tableID}/styles/1?&key={yourkeyhere}
 
Replace the 1 above with the StyleID of the style you're interested in. Read more
 
Filter rows using SQL-like syntax, including the spatial filters
 
https://www.googleapis.com/fusiontables/v1/query?sql=SELECT+*+FROM+{tableID}&key={yourkeyhere}

Tip: Use a + character for spaces in the URL.

 
This pattern finds all rows where the location is within a given radius of a point.
The latitude, longitude of the center of the circle should be two numbers, in decimal degrees. The radius is a number of meters.  

https://www.googleapis.com/fusiontables/v1/query?sql=SELECT+*+FROM+{tableID}+WHERE+ST_INTERSECTS({columnnamewithlocation},+CIRCLE(LATLNG({latitudeofcirclecenter},{longitudeofcirclecenter}),{radiusinmeters}))&key={yourkeyhere}

Add your key to this example to retrieve rows from the caterpillar table

Read more

One more hint

As you read more about these patterns, look for URLs with GET in front of them. These are the ones to which you can apply this technique.