But after all that- I was quite happy to see Google Fusion Tables within Google Docs. Databases as a service ? Not quite but still quite good, and lets see how it goes.
https://www.google.com/fusiontables/DataSource?dsrcid=implicit&hl=en_US&pli=1
http://googlesystem.blogspot.com/2011/09/fusion-tables-new-google-docs-app.html
But what interests me more is
http://code.google.com/apis/fusiontables/docs/developers_guide.html
The Google Fusion Tables API is a set of statements that you can use to search for and retrieve Google Fusion Tables data, insert new data, update existing data, and delete data. The API statements are sent to the Google Fusion Tables server using HTTP GET requests (for queries) and POST requests (for inserts, updates, and deletes) from a Web client application. The API is language agnostic: you can write your program in any language you prefer, as long as it provides some way to embed the API calls in HTTP requests.
The Google Fusion Tables API does not provide the mechanism for submitting the GET and POST requests. Typically, you will use an existing code library that provides such functionality; for example, the code libraries that have been developed for the Google GData API. You can also write your own code to implement GET and POST requests.
Also see http://code.google.com/apis/fusiontables/docs/sample_code.html
Google Fusion Tables API Sample Code
Libraries
SQL API
| Language | Library | Public repository | Samples |
|---|---|---|---|
| Python | Fusion Tables Python Client Library | fusion-tables-client-python/ | Samples |
| PHP | Fusion Tables PHP Client Library | fusion-tables-client-php/ | Samples |
Featured Samples
An easy way to learn how to use an API can be to look at sample code. The table above provides links to some basic samples for each of the languages shown. This section highlights particularly interesting samples for the Fusion Tables API.
SQL API
| Language | Featured samples | API version |
|---|---|---|
| cURL |
|
SQL API |
| Google Apps Script |
|
SQL API |
| Java |
|
SQL API |
| Python |
|
Docs List API |
| Android (Java) |
|
SQL API |
| JavaScript – FusionTablesLayer | Using the FusionTablesLayer, you can display data on a Google Map
Also check out FusionTablesLayer Builder, which generates all the code necessary to include a Google Map with a Fusion Table Layer on your own website. |
FusionTablesLayer, Google Maps API |
| JavaScript – Google Chart Tools | Using the Google Chart Tools, you can request data from Fusion Tables to use in visualizations or to display directly in an HTML page. Note: responses are limited to 500 rows of data. | Google Chart Tools |
External Resources
Google Fusion Tables is dedicated to providing code examples that illustrate typical uses, best practices, and really cool tricks. If you do something with the Google Fusion Tables API that you think would be interesting to others, please contact us at googletables-feedback@google.com about adding your code to our Examples page.
- Shape EscapeA tool for uploading shape files to Fusion Tables.
- GDALOGR Simple Feature Library has incorporated Fusion Tables as a supported format.
- Arc2CloudArc2Earth has included support for upload to Fusion Tables via Arc2Cloud.
- Java and Google App EngineODK Aggregate is an AppEngine application by the Open Data Kit team, uses Google Fusion Tables to store survey data that is collected through input forms on Android mobile phones. Notable code:
- Create a Fusion Table – FusionTableServlet.java
- Insert data into a Fusion Table – SubmissionFusionTable.java
- R packageAndrei Lopatenko has written an R interface to Fusion Tables so Fusion Tables can be used as the data store for R.
- RubySimon Tokumine has written a Ruby gem for access to Fusion Tables from Ruby.
Updated-You can use Google Fusion Tables from within R from http://andrei.lopatenko.com/rstat/fusion-tables.R
ft.connect <- function(username, password) {
url = "https://www.google.com/accounts/ClientLogin";
params = list(Email = username, Passwd = password, accountType="GOOGLE", service= "fusiontables", source = "R_client_API")
connection = postForm(uri = url, .params = params)
if (length(grep("error", connection, ignore.case = TRUE))) {
stop("The wrong username or password")
return ("")
}
authn = strsplit(connection, "\nAuth=")[[c(1,2)]]
auth = strsplit(authn, "\n")[[c(1,1)]]
return (auth)
}
ft.disconnect <- function(connection) {
}
ft.executestatement <- function(auth, statement) {
url = "http://tables.googlelabs.com/api/query"
params = list( sql = statement)
connection.string = paste("GoogleLogin auth=", auth, sep="")
opts = list( httpheader = c("Authorization" = connection.string))
result = postForm(uri = url, .params = params, .opts = opts)
if (length(grep("<HTML>\n<HEAD>\n<TITLE>Parse error", result, ignore.case = TRUE))) {
stop(paste("incorrect sql statement:", statement))
}
return (result)
}
ft.showtables <- function(auth) {
url = "http://tables.googlelabs.com/api/query"
params = list( sql = "SHOW TABLES")
connection.string = paste("GoogleLogin auth=", auth, sep="")
opts = list( httpheader = c("Authorization" = connection.string))
result = getForm(uri = url, .params = params, .opts = opts)
tables = strsplit(result, "\n")
tableid = c()
tablename = c()
for (i in 2:length(tables[[1]])) {
str = tables[[c(1,i)]]
tnames = strsplit(str,",")
tableid[i-1] = tnames[[c(1,1)]]
tablename[i-1] = tnames[[c(1,2)]]
}
tables = data.frame( ids = tableid, names = tablename)
return (tables)
}
ft.describetablebyid <- function(auth, tid) {
url = "http://tables.googlelabs.com/api/query"
params = list( sql = paste("DESCRIBE", tid))
connection.string = paste("GoogleLogin auth=", auth, sep="")
opts = list( httpheader = c("Authorization" = connection.string))
result = getForm(uri = url, .params = params, .opts = opts)
columns = strsplit(result,"\n")
colid = c()
colname = c()
coltype = c()
for (i in 2:length(columns[[1]])) {
str = columns[[c(1,i)]]
cnames = strsplit(str,",")
colid[i-1] = cnames[[c(1,1)]]
colname[i-1] = cnames[[c(1,2)]]
coltype[i-1] = cnames[[c(1,3)]]
}
cols = data.frame(ids = colid, names = colname, types = coltype)
return (cols)
}
ft.describetable <- function (auth, table_name) {
table_id = ft.idfromtablename(auth, table_name)
result = ft.describetablebyid(auth, table_id)
return (result)
}
ft.idfromtablename <- function(auth, table_name) {
tables = ft.showtables(auth)
tableid = tables$ids[tables$names == table_name]
return (tableid)
}
ft.importdata <- function(auth, table_name) {
tableid = ft.idfromtablename(auth, table_name)
columns = ft.describetablebyid(auth, tableid)
column_spec = ""
for (i in 1:length(columns)) {
column_spec = paste(column_spec, columns[i, 2])
if (i < length(columns)) {
column_spec = paste(column_spec, ",", sep="")
}
}
mdata = matrix(columns$names,
nrow = 1, ncol = length(columns),
dimnames(list(c("dummy"), columns$names)), byrow=TRUE)
select = paste("SELECT", column_spec)
select = paste(select, "FROM")
select = paste(select, tableid)
result = ft.executestatement(auth, select)
numcols = length(columns)
rows = strsplit(result, "\n")
for (i in 3:length(rows[[1]])) {
row = strsplit(rows[[c(1,i)]], ",")
mdata = rbind(mdata, row[[1]])
}
output.frame = data.frame(mdata[2:length(mdata[,1]), 1])
for (i in 2:ncol(mdata)) {
output.frame = cbind(output.frame, mdata[2:length(mdata[,i]),i])
}
colnames(output.frame) = columns$names
return (output.frame)
}
quote_value <- function(value, to_quote = FALSE, quote = "'") {
ret_value = ""
if (to_quote) {
ret_value = paste(quote, paste(value, quote, sep=""), sep="")
} else {
ret_value = value
}
return (ret_value)
}
converttostring <- function(arr, separator = ", ", column_types) {
con_string = ""
for (i in 1:(length(arr) - 1)) {
value = quote_value(arr[i], column_types[i] != "number")
con_string = paste(con_string, value)
con_string = paste(con_string, separator, sep="")
}
if (length(arr) >= 1) {
value = quote_value(arr[length(arr)], column_types[length(arr)] != "NUMBER")
con_string = paste(con_string, value)
}
}
ft.exportdata <- function(auth, input_frame, table_name, create_table) {
if (create_table) {
create.table = "CREATE TABLE "
create.table = paste(create.table, table_name)
create.table = paste(create.table, "(")
cnames = colnames(input_frame)
for (columnname in cnames) {
create.table = paste(create.table, columnname)
create.table = paste(create.table, ":string", sep="")
if (columnname != cnames[length(cnames)]){
create.table = paste(create.table, ",", sep="")
}
}
create.table = paste(create.table, ")")
result = ft.executestatement(auth, create.table)
}
if (length(input_frame[,1]) > 0) {
tableid = ft.idfromtablename(auth, table_name)
columns = ft.describetablebyid(auth, tableid)
column_spec = ""
for (i in 1:length(columns$names)) {
column_spec = paste(column_spec, columns[i, 2])
if (i < length(columns$names)) {
column_spec = paste(column_spec, ",", sep="")
}
}
insert_prefix = "INSERT INTO "
insert_prefix = paste(insert_prefix, tableid)
insert_prefix = paste(insert_prefix, "(")
insert_prefix = paste(insert_prefix, column_spec)
insert_prefix = paste(insert_prefix, ") values (")
insert_suffix = ");"
insert_sql_big = ""
for (i in 1:length(input_frame[,1])) {
data = unlist(input_frame[i,])
values = converttostring(data, column_types = columns$types)
insert_sql = paste(insert_prefix, values)
insert_sql = paste(insert_sql, insert_suffix) ;
insert_sql_big = paste(insert_sql_big, insert_sql)
if (i %% 500 == 0) {
ft.executestatement(auth, insert_sql_big)
insert_sql_big = ""
}
}
ft.executestatement(auth, insert_sql_big)
}
}
