Please mind the gap between the software and the platform

Excuse the awful pun. Gapminder.org developed this really neat graphical flash engine back in 2006, Google were quick to snap it up the following year, and have also been so graciously kind as to publish an API for it under the guise of Motion Chart.

You can use this engine freely to include whatever data you want, though getting data in and out can be a pain, if, like me, you want to enable non-programmers to experiment with their own data.

So, the first step might be reconfiguring the source from explicit javascript declarations, to something a little more familiar - xml. I used a structure like this:

<?xml version="1.0" encoding="UTF-8"?>

<bubbleXml>
<title>Hello, this is my first motion chart!</title>
<dataContainer>
<dataRow>
<WeekNo>1</WeekNo>
<Category>Canned Fruit</Category>
<Metrics>Items Sold</Metrics>
<Value>17</Value>
</dataRow>
<dataRow>
<WeekNo>1</WeekNo>
<Category>Canned Fruit</Category>
<Metrics>Sales Value</Metrics>
<Value>23.78</Value>
and so on..

Which gives you a nice extensible data platform, although it is quite verbose (duh.. it's xml!).
Each dataRow captures the time variable (WeekNo), the entity names (Category), along with the name and value of each metric.

You can then write a parser for the xml in your favourite scripting language, and embed the results into the javascript. Here's an example for PHP - verbatim:

<?php

$xmlfile = "dataYouWantToUse.xml";
$data = simplexml_load_file($xmlfile);

$categories = $data->xpath("dataContainer/dataRow/Category");
$timestamp = $data->xpath("dataContainer/dataRow/WeekNo");
$values = $data->xpath("dataContainer/dataRow/Value");
$metrics = $data->xpath("dataContainer/dataRow/Metrics");
$title = $data->xpath("title");

$uCatN = count(array_unique($categories));
$uTimeN = count(array_unique($timestamp));
$uMet = array_unique($metrics);
$uMetN = count(array_unique($metrics));
$rowCount = $uCatN*$uMetN*$uTimeN-1;
$googleCount = $uCatN*$uTimeN;

function listColumns($uMet)
{
echo "\n";
foreach ($uMet as $thisMet) {
echo "\tdata.addColumn('number', \"$thisMet\");\n";
}
}
function generateJs($categories, $timestamp, $values, $rowCount, $uMetN)
{
echo "\n";
$c = 0;
for ($i=0; $i<=$rowCount; $i+=$uMetN) {
if ($timestamp[$i] < 10)
$time = "0".$timestamp[$i];
else
$time = $timestamp[$i];
echo "\tdata.setValue($c, 0, '$categories[$i]'); \n" ;
echo "\tdata.setValue($c, 1, '2009W$time'); \n" ;
for ($j=$i; $j<=$i+$uMetN-1; $j++) {
$n = $j-$i+$uMetN-1;
echo "\tdata.setValue($c, $n, $values[$j]); \n" ;
}
$c++;
}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title><?php echo $title[0]; ?></title>
<script type="text/javascript" src="http://www.google.com/jsapi"></script>
<script type="text/javascript">
google.load('visualization', '1', {'packages':['motionchart']});
google.setOnLoadCallback(drawChart);
function drawChart() {
var data = new google.visualization.DataTable();

data.addRows(<?php echo $googleCount; ?>);
data.addColumn('string', 'Category');
data.addColumn('string', 'Time');
<?php listColumns($uMet);
generateJs($categories, $timestamp, $values, $rowCount, $uMetN); ?>

var chart = new google.visualization.MotionChart(document.getElementById('chart_div'));
chart.draw(data, {width: 800, height:460});
}
</script>
<style type="text/css">
body {text-align: center;}
</style>
</head>

<body>
<h3><?php echo $title[0]; ?></h3>
<p> data in motion</p>
<div id="chart_div"></div>
<h5><a href="http://worldofones.blogspot.com">adam marshall 2009</a> | <a href="http://worldofones.blogspot.com">worldofones.blogspot.com</a> | <a href="http://code.google.com">powered by google</a></h5>
</body>
</html>

Notes:
That's set up to process time in the '2009W1' format - haven't got round to generalising that yet, but it's not too hard to modify if you want to use different formats. Likewise for the inclusion of text (colour category) metrics - you just need to modify the listColumns() function, and make sure you've got quotes around their values.

Phew! So, you can load that xml file directly into the page at runtime. Maybe you can get your IT guys to help making a current data source available as xml in this way; if not - here's a little script for converting a csv file to xml courtesy of Chris M over at bytemycode.com, with just a few modifications to create the chosen format.

<?php

// define params
$containerLabel = "dataContainer";
$rowLabel = "dataRow";
$fileLocation = "csvdata/".$_GET["from"].".csv";
$fileDestination = "xmldata/".$_GET["to"].".xml";
$title = $_GET["title"];
/**
* Converts a grid layout CSV to an XML
* Rows are nested within the container variable
* Column headers in the CSV become tags containing the data, within each row
*/
function csv2xml($file, $container = 'data', $rows = 'row')
{
$r = "\t<{$container}>\n";
$row = 0;
$cols = 0;
$titles = array();

$handle = @fopen($file, 'r');
if (!$handle) return $handle;

while (($data = fgetcsv($handle, 1000, ',')) !== FALSE) {
if ($row > 0) $r .= "\t\t<{$rows}>\n";
if (!$cols) $cols = count($data);
for ($i = 0; $i < $cols; $i++) {
if ($row == 0) {
$titles[$i] = $data[$i];
continue;
}
$r .= "\t\t\t<{$titles[$i]}>";
$r .= $data[$i];
$r .= "</{$titles[$i]}>\n";
}
if ($row > 0) $r .= "\t\t</{$rows}>\n";
$row++;
}
fclose($handle);
$r .= "\t</{$container}>\n";
return $r;
}

$xml = csv2xml($fileLocation, $containerLabel, $rowLabel);
$fullxml = '<?xml version="1.0" encoding="UTF-8"?>'."\n
<bubbleXml>\n\t<title>$title</title>\n".$xml."</bubbleXml>";
file_put_contents($fileDestination,$fullxml);

?>


I've added GET parameters to this, so if you had a csv file caled 'data1' in a folder on the server called 'csvdata', you can call using the parameters:

?from=data1&to=fileYouWantToUse&title=Hello, this is my first motion chart!

to create the xml file. Now, you should be able to use excel/analytics software/business intelligence tools to produce the csv, compile it to xml, and display it in a cool motion chart!

Why not show your CEO his company's history in motion, or your sales team the last few weeks of consumer trends?

Don't ask questions you already know the answer to - REST apis part 3

Okay, let's have a look at something else.

You probably don't want to go trawling for data every time you need to process some, so let's look at saving some data.

State your REST source as usual, but instead of a simplexml load, do:

$somefile=fopen($somecall,"r");
file_put_contents("somedata.xml",$somefile);
fclose($somefile);

Which loads your resource, saves it to the server as xml (or whatever), then closes the connection. That's all you need to put it this file - end with ?> as usual and save as whatever - fetchdata.php

Then, whenever you need to refresh your source: include("fetchdata.php");

Whenever you want to use the data that's already there:

$sxmlobject = simplexml_load_file("somedata.xml");

if it's XML. If you have to deal with JSON you can still loop if you use an array, instead of a simplexml load.

Here's one last trick. On your main page, you can make it refresh the data in time intervals. Or specifically, you tell it to refresh if it's been at least X mins/hours/days since the last refresh.



//This function refreshes the data and records the time it does it.
function refreshData()
{
$t = time();
include("fetchdata.php");
file_put_contents("lastupdate.txt",$t);
}

//This is now
$timenow = time();

//If you can't find that record, refresh anyway. This is so it works first time!
if(!file_exists("lastupdate.txt"))
{
refreshData();
}

//Find out the last time you refreshed - might have been just a few lines above though!
$timeload = file_get_contents("lastupdate.txt");

//If it's been 10 hours - 36000 seconds - since refresh, refresh!
if ($timenow > $timeload + 36000)
{
refreshData();
}

$sxmlobject = simplexml_load_file("somedata.xml");
//and do something with it!

?>

Particularily Happy Place - REST apis part 2

Okay, if you want to start querying, you'll need a server to run your PHP code. I recommend giving XAMPP a look - you'll get Apache, MySQL and PHP support all ready to go out of the box (okay, almost - I think all I had to do was initialise openssl in apache/bin/ini.php - google it) for free!

Ready to go? I'm a big fan of PHP for it's pretty open typing - I hate having to declare everything, I find this language makes it relatively easy to juggle resources without actively having to keep track of everything and worrying about type conversions etc. Let's just jump in.

The first thing to do is define your source. Most apis will have a token system to stop malicious users abusing the system, so I'm going to add that string in here too. Something like this:

<?php
$call = "http://example.com/api/weather?token=v5onv689g35";

creates a variable called "$call" and inserts your resource address as the value. Note the dollar sign denotes a variable. You'll then want to insert some other query strings:

$call = $call."&country=uk";
$call = $call."&city=london";

Note we could have lumped all three of these together in one declaration, but we'll want to come back later to change these, as it'd be useful to actually change their values. I'll come back to this.

$xml = simplexml_load_file($call);

Keeping this really simple, in one line we've: contacted the server ( here, at example.com), retrived the response and loaded it into a simplexml object called "$xml". If you look back to the last post, we had a response which contained one level of hierarchy - so we need to drill inside the "response" tag to get to the tags we want e.g. "temp"...

We can do this easily using a foreach statement, which looks at each child of the $xml variable (calling it $xmlchild). We then call the name of the tag $xmlname - and the print $xmlname and $xmlchild, with a hyphen between them, and a break at the end.

foreach($xml->children() as $xmlchild)
{
$xmlname = $xmlchild->getName();
echo $xmlname."-".$xmlchild."<br />";
}
?>

Once the server executes this PHP page, it would output the following to the browser:

temp-12.7
windspd-34.6
winddir-170
humid-54

Of course, you'd also want to build in controls to alter the query, do calculations with the data, display it in a meaningful way etc. but this is the basic technique I've been using - use PHP to send a request to a third party server, process and parse the response on a local sever, and serve the results back to the browser.

Give It a Rest: REST apis part 1

So, first off I'm going to talk about one of the simplest ways to get a web service to send you data - it's called REST.

REST stands for representational state transfer and is an architecture, a way of laying out resources on a server. The idea is that the information you want to get at (the "resource") can be asked for by describing a "representation" of that data using HTTP calls. Services which do this are "RESTful".

For example, say you have a service which provides you with details of the weather in a specific location. One way to query this service might be:

[serverlocation]?country=uk&city=london

Which is to say, what's it like in London, UK right now?
Another equally valid way to ask for this information might be:

[serverlocation]?lat=5132N&long=0005W

Which refers to exactly the same place (resource), but in a different way. As long as the server accepts all of these query parameters, they're both fine to use.

The key here is that you don't have to send a HTTP header, you don't need any funny protocols, you just ask for a page with well defined query parameters saying what you need, and bing! that page contains the appropriate data.

The response itself can be in pretty much any format, indeed - you'd often use one query parameter for the response format, if you have a choice. Suppose it was XML, it might look like this:

<response>
<temp>12.7</temp>
<windspd>34.6</windspd>
<winddir>170</windir>
<humid>54</humid>
</response>

Which I've just made up, but you get the idea. If you ask for the weather info for the city of "Gropiertegbun", you might instead get this:

<response>
<error>city not recognised</error>
</response>

With the possible exception that "recognised" would probably end up getting spelt the American way. That's because I made that city up, and even if I didn't, perhaps asking for a city without a country ID returns an error too. REST apis generally have fairly simple rules about which parameters you can use with which, and generally have defaults for when specific parameters are missing.

Part 2 will deal with how to use a server scripting language, in this case PHP, to make these calls and parse (read out) the results. I'll then probably follow on with details of how to build a user inteface to modify the queries, and how you can feed the results into other apis to display and use your data.