Working with Fusion Tables, and Chrome Extensions

Firstly, Google Fusion Tables v1.0 is simply great.  This post is an attempt to put into tutorial form the presentation that I gave at the Google Developers Group - Twin Cities on November 1st, 2012.

My general outline is to have you

1. Using a sample csv dataset
2. upload that into Fusion Table, geocode it.
3. Practice viewing the data on a fusion table map.
4. Explore the Fusion Table SQL.
5. Create a javascript html page the can query the fusion table data
6. Create a simple Chrome Plugin
7. Have the plugin query the fusion table data.

 Google Fusion Tables

The primary location for documentation and references is https://developers.google.com/fusiontables/

1 - Using a sample csv dataset

Starting with a simple example.  Let's map all the White Castles in MN, ( as scraped by loving hands from http://www.whitecastle.com/locations/all) Download the MNWhiteCastle CSV file here.. (open and save as or right click and save destination, you know the drill).

2 - upload that into Fusion Table, geocode it.

In your Google drive folder, create a new document, choose More, then Fusion Tables.

Follow the steps and upload the CSV.  Change the name if you like, don't worry too much about other settings, they should be good.  You should now have a Fusion Table that looks like 

The Address column is already highlighted because it recognizes it as an Address Type field.  Since it saw it was an address field, a Map of Address tab was also created.  Go ahead and click there.  It will geocode the addresses and display a map.  YAY.  You mapped a bunch of addresses.  If you chance you table structure you can go to File -> Geocode and it will geocode new addresses.

3 - AGAIN WITH MORE DATA

1. FriendFace dataset
2. This time the data is already geocoded, but labeled differently.  You have to specify the lat lon as the address.
1. Go to Edit->Change column
2. Change lat to type = location, make it a two column field, and make the two columns lat and lon.

Add a tab to the Fusion Table of a "Map".  You should now see your data points.  BAM!  Like that you mapped 1000 points.  Nice work!

4 - Explore the Fusion Table SQL.

https://developers.google.com/fusiontables/docs/v1/sql-reference has a good description of what you can do with your FusionTable data now that it is up there.  First, set the permissions of your table so that it is viewable to anyone with the link.

Make a note of the docid of your table.  

Now select all rows.  SELECT * FROM DOCID

https://www.googleapis.com/fusiontables/v1/query?sql=
SELECT * FROM 1t3pUD-6Nt2i9tCZpSC_gi3-H0AL1tpO5TGAsCLw

huh.  Pretty quickly I hit a limit.  So tacking on your API Key helps.  Get that from

https://code.google.com/apis/console

https://www.googleapis.com/fusiontables/v1/query?sql=SELECT * FROM 1t3pUD-6Nt2i9tCZpSC_gi3-H0AL1tpO5TGAsCLw&key=<>

Note, the response is JSON, in the order it is in the table.

So.... Check this out you can do spacial operations on the query.  Like say show me the 10 white castles closest to Leal Tillman (44.884779,-93.000282).  We need some tools

1. Fusion Tables distance function: ST_DISTANCE(<>,<>)
2. Define a point: LATLON(44.884779,-93.000282), in Lael's case.

so:   

SELECT * 

  FROM [table] 

 ORDER BY ST_DISTANCE(Address,LATLNG(44.884779,-93.000282))

try it

5 - Create a javascript html page the can query the fusion table data

Let's play with Intersect, like on this example: Fusion Tables Layer Example: Intersects Radius

1. Copy the HTML from the above link.  Save it somewhere locally.  I called it circle.html
2. It won't work because you need the css.  It's here.
3. update the  circle.html file.
1. Line23.  Chance the css to be
   default.css" rel="stylesheet" type="text/css">

Also update the tableid to your table. (line 28)

Update all lat,lon points from Mountain View to Minneapolis.
37.4, -122.1
to
44.988265,-93.259191     (Code42's office)
YOU SHOULD FIND 4 

NOW load the page.

TAHDAH......

Note: when you change the select box, the radius updates and re-queries the data.