See below for commands using the Fusion Tables API. Example table is the oldschool message wall public table. Note that examples are shown first without the required url-encoding.
Authenticating: Getting the auth token
To authenticate you may use the following test account myjdoe.
- account: myjdoe@gmail.com
- password: JoesSecret
curl -H "Content-type: application/x-www-form-urlencoded" -d "accountType=GOOGLE &Email=myjdoe@gmail.com&Passwd=JoesSecret&service=fusiontables&source=skipperkongen.dk" https://www.google.com/accounts/ClientLogin |
Response:
SID=DQAAAHcAAAD90F7mQKULu7fY44z-maGyaTBElaKsaBvypHqU88b7mqVU93Lyf_1sr7ZbxSosjx2e6dT4LSOswAYG3fzAFVjQ-Z8VBS7-oloLNfNZF3A9qMhhI-cpDxoUe_3SUfdUsTfmk34a4wnkok4im77J-vBM5OqmMxLTE8JaDlylHgG2RA LSID=DQAAAHoAAADkmXi_iKlUyVI_tuZ7n__6R9PkF3jIior9YnbO5a6lYFtNXxez2Ymw3sXbHKU48IwWhztVfDVUBF4UcCuH2rs3v6s5M1-duCG8gXaJph0oJByr3_Rj9olzm9Zlo5JKgcOiSNN38PY0jnONuuqR2G22KT-meIls05soufg8GEX-Xg Auth=DQAAAHoAAAA3fDq7sZbeekb_x96Z7icle8VzWyucA50O2HnzUxbG_Y_PfaLfv5HmljUvNJoN1Owrgei796p-LGX-3l1KDRacWF_QhhSpAusdAVgSvxVlqrsJAc52Wu0CFb60_m19AwbbnLjd-CvIy-A-gwBI2Oi0O29lEU0qeL-JMOmiq_UtuQ |
Auth is the token.
To make an authenticated POST request use the following header: Authorization: GoogleLogin auth=DQAAAHoAAA... which includes the token.
Query with SELECT
Querying data is done with HTTP GET and the SELECT command. Does not require authentication for public, exportable tables like the oldschool message wall public table.
Select all rows
http://www.google.com/fusiontables/api/query?sql=SELECT * FROM 380584
curl "http://www.google.com/fusiontables/api/query?sql=SELECT+*+FROM+380584" |
Select rows with WHERE clause
http://www.google.com/fusiontables/api/query?sql=SELECT Message FROM 380584 WHERE User='Kostas'
curl "http://www.google.com/fusiontables/api/query?sql=SELECT+Message+FROM+380584+WHERE+User='Kostas'" |
Select rows with spatial clause
http://www.google.com/fusiontables/api/query?sql=SELECT * FROM 380584 WHERE ST_INTERSECTS(Geo, CIRCLE(LATLNG(55.67892,12.58338),5000))
curl "http://www.google.com/fusiontables/api/query?sql=SELECT+*+FROM+380584+WHERE+ST_INTERSECTS(Geo,+CIRCLE(LATLNG(55.67892,12.58338),5000))" |
Add data with INSERT
Adding rows of data is done with HTTP POST and the INSERT command. Requires authentication.
curl -H "Content-type: application/x-www-form-urlencoded" -H "Authorization: GoogleLogin auth=DQAAAHoAAAA3fDq7sZbeekb_x96Z7icle8VzWyucA50O2HnzUxbG_Y_PfaLfv5HmljUvNJoN1Owrgei796p-LGX-3l1KDRacWF_QhhSpAusdAVgSvxVlqrsJAc52Wu0CFb60_m19AwbbnLjd-CvIy-A-gwBI2Oi0O29lEU0qeL-JMOmiq_UtuQ" -d "sql=INSERT INTO 380584 (User, Message, Geo) VALUES ('Kostas', 'Hello again', '55.664503, 12.59953');" "http://www.google.com/fusiontables/api/query" |
Notice we are using the token retrieved in the authentication step.
Response:
rowid
401 |
Get column names with DESCRIBE
Discovering column names is done with HTTP POST and the DESCRIBE command. Requires authentication.
curl -H "Authorization: GoogleLogin auth=DQAAAHoAAAA3fDq7sZbeekb_x96Z7icle8VzWyucA50O2HnzUxbG_Y_PfaLfv5HmljUvNJoN1Owrgei796p-LGX-3l1KDRacWF_QhhSpAusdAVgSvxVlqrsJAc52Wu0CFb60_m19AwbbnLjd-CvIy-A-gwBI2Oi0O29lEU0qeL-JMOmiq_UtuQ" "http://www.google.com/fusiontables/api/query?sql=DESCRIBE+380584" |
Notice we are using the token retrieved in the authentication step.
Response:
column id,name,type col0,User,string col4,Message,string col5,Geo,location |
Client libraries by Google
To help create the API calls, you can use the client libraries developed and shared by Google instead of curl.
Libraries exist for the following languages:
| Client libraries | |
|---|---|
| Java | gdata-java-client |
| Javascript | gdata-javascript-client |
| .NET | google-gdata |
| PHP | Distributed as part of zend. |
| Python | gdata-python-client |
| Objective C | gdata-objectivec-client |
Read data into R:
messages <- read.table("http://www.google.com/fusiontables/api/query?sql=SELECT+User,+Message+FROM+380584",header=TRUE, sep=",")
Cool Martin.
Just tried it and it works. Of course that table is not so interesting as it contains mainly text columns.
To issue a JSONP-enabled SELECT query, add a ‘jsonCallback’ param to the query string.
Example:
http://www.google.com/fusiontables/api/query?sql=SELECT+*+FROM+380584&jsonCallback=foo
Wow, thanks. Didn’t know about that.
Thank you Kamal for this. I was looking for this.
I needed to UPDATE some rows and it worked perfectly for my account. Thousand thanks and thousand more again.
For auth only worked obtaining a google certificate (.pem file) and add –cacert {.pem file} to the curl params.
but… my fusiontable does not show the location points that i’ve modified. and format is correct!
thanks for this
Hola buen día, quiero que me ayuden con un problema que tengo , ya que quiero agregar campos, agregar tablas o hacer otra consulta a fusion tables mediante una consulta SQL,