fusion-tables

This gem lets you easily interact with Google Fusion Tables from your Ruby application. Here is a live visualisation of london bike hire availability and some example maps and charts.

Gem Dependencies

• gdata_19 >= 1.1.2

Installation

gem install fusion_tables

Rubies

Tested on:

• 1.8.7
• 1.9.2-p0

To Use

require 'fusion_tables'

or in Rails 2.3.x

config.gem 'fusion_tables'

API examples

Twitter example
Boris bike example
Tests

Here is a brief rundown:

# Connect to service	
@ft = GData::Client::FusionTables.new      
@ft.clientlogin(username, password)

# Browse existing tables
@ft.show_tables
 # => [table_1, table_2] 

# Getting table id suitable for using with google maps (see more below)
table_1.id #=> 42342 (the table's google id)

# Count data
table_1.count #=> 1

# Select data
table_1.select 
 #=> data

# Select data with conditions
table_1.select "name", "WHERE x=n"
 #=> data

# Select ROWIDs
row_ids = table_1.select "ROWID"

# Drop tables
@ft.drop table_1.id                    # table id
@ft.drop [table_1.id, table_2.id]     # arrays of table ids
@ft.drop /yacht/                      # regex on table name

# Creating a table
cols = [{:name => "friend name",    :type => 'string' },
        {:name => "age",            :type => 'number' },
        {:name => "meeting time",   :type => 'datetime' },
        {:name => "where",          :type => 'location' }]

new_table = @ft.create_table "My upcoming meetings", cols

# Inserting rows (auto chunks every 500)
data = [{"friend name" 	=> "Eric Wimp", 
         "age"          => 25, 
         "meeting time" => Time.utc(2010,"aug",10,20,15,1),
         "where"        => "29 Acacia Road, Nuttytown"}]
new_table.insert data

# Delete row
new_table.delete row_id

Currently UPDATE query is not implemented, you could always delete all data and start over again.

But you could also do this:

require 'active_support/core_ext/array/grouping'

# get the table as above..

data = []
list = table.select("*", "WHERE location contains '%20' ORDER BY permalink LIMIT 2")

list.each do |item|
  data << item
  id  = table.select("ROWID", "WHERE permalink = '%s'" % item[:permalink])
  id  = id.first[:rowid]
  puts "deleting %d" % id
  table.delete(id)
end

#modify location, remove text that breaks longlat value..
data.map {|item| item[:location] = item[:location].gsub('%20','')}

data.in_groups_of(50, false) do |group|
  puts "inserting to fusion table: %d items" % group.size
  table.insert(group)
end

Fusion Tables secret Geospatial Sauce

Geolocated Tweets example

Fusion Tables is a labs product from Google. You can read more here, but the key thing is that it gives you access to the google tile mill for fast generation of google map layers across large datasets

Fusion Tables supports the following geometry types:

• lat/long
• addresses (automatically geocodes them for you)
• KML (point, polyline, polygon, multipolygon)

Integrate with google maps v3

Adding a fusion tables datalayer with many points/polygons to your v3 map is as simple as:

layer = new google.maps.FusionTablesLayer(139529);

That’s it

You can also refine the tiles by SQL, and can even do so dynamically:

layer = new google.maps.FusionTablesLayer(198945, {
  query: "SELECT address FROM 198945 WHERE ridership > 5000"}
);

Finally, fusion tables also lets you make Heatmaps

layer = new google.maps.FusionTablesLayer(136705, {
  heatmap: true
});

You can also export your data (filtered and geocoded) to KML. As an example, here are all the Gasoline filling stations in the UK

read more here

Known Issues

1. The gem uses the Google gdata_19 gem which conflicts with the GData2 gem. Uninstall gdata2 to regain sanity.
2. Currently you have to make a table public before you can display it on a map, unfortunately, this can only be done on the web interface. A suggested workaround is to put all your data in 1 big public table, and then query for the data you want to display based off a key/flag column

Note on Patches/Pull Requests

• Fork the project.
• Make your feature addition or bug fix.
• Add tests for it. This is important so I don’t break it in a
  future version unintentionally.
• Commit, do not mess with rakefile, version, or history.
  (if you want to have your own version, that is fine but bump version in a commit by itself I can ignore when I pull)
• Send me a pull request. Bonus points for topic branches.

Copyright

Largely based on Tom Verbeure’s work for MTBGuru: http://code.google.com/p/mtbguru-fusiontables/

Copyright © 2010 Tom Verbeure, Simon Tokumine. See LICENSE for details.