Citizens’ Agenda, Fusion Tables and JSON (Show your work)

There is a lot of positive buzz around in regards to “The Citizens’ Agenda” project that I’m involved with. You can see all the great work that Studio20 and The Guardian are doing here. And don’t forget to tweet any #unasked questions you might be thinking about.

This initial set of data being published is a set of 839 categorized questions asked at  20 Republican presidential debates. The results were stored in a Google Fusion Table. While not completely normalized, there are a lot of interesting queries and visualizations to be made with the data.

My initial thought was to get it into JSON format. Then we could build on top of it and query it in more fruitful ways.

I had never even seen Google Fusion Tables. I take that back. I had watched a demo at the Knight-Mozilla OpenNews “hacktoberfest” in Berlin in September by Chris Keller, but had never used them myself.

So I said, “Hey Chris, what do I do?” He provided some examples using JQuery which were really helpful to see how the API works in general.

And he pointed to some existing ways to get JSON back from the API including Fusion Tables to JSON and a very interesting undocumented feature of the Google API itself.

As I mentioned, the table data was not quite normalized. For example, in a column called “Question directed to,” the value could be “Romney” or “Romney, Paul.”

So a query for “Romney” might miss that second example, since matches need to be exact. Also, the column with the date had additional text in that field describing the  particular debate.

In other words, getting meaningful queries was going to take some text processing from within the application. So I thought I’d try out the PHP API client that Google provides.

Here is the result. This example returns the entire data set in JSON. I will followup with some more specific queries. You can see a live example at http://citizensagenda.net/debate-questions

// include fusion tables API lib
// http://code.google.com/p/fusion-tables-client-php/
require('../classes/fusiontables/clientlogin.php');
require('../classes/fusiontables/sql.php');
require('../classes/fusiontables/file.php');

// PHP CSV function breaks because of new lines so I use parsecsv library
// http://code.google.com/p/parsecsv-for-php/
require_once('../classes/parsecsv.lib.php');

// authorize (use your creds)
$token = ClientLogin::getAuthToken('my-google-username', 'my-google-password');
$ftclient = new FTClientLogin($token);

//select the data (must know fusion table id)
$fusiontableid = 'fusion-table-id';
$csvreturn = $ftclient->query(SQLBuilder::select($fusiontableid));

// instantiate parsecsv object and process the google data
// this populates $csv->data with an array
$csv = new parseCSV();
$csv->auto($csvreturn);

//encode array into json, set content-type header and print it out
$json = json_encode($csv->data);
header('Content-type: application/json');
echo $json;

Leave a Reply

Your email address will not be published. Required fields are marked *