When I changed jobs, my commute changed from mostly surface streets to mostly highway. I-80 is notoriously congested and I wanted to strategically plan my commute to avoid as much traffic as possible.
I started by watching traffic online and then found Traffic.com, where I could create custom drives and receive alerts, etc. Pretty cool…but I wanted a way to SEE traffic trends, so I designed a way to get data from Traffic.com and to then display that data in a graph.
Traffic.com offers a RSS feed, so my plan was to use data from that feed to generate the graphs. So, I signed up at Traffic.com and created two custom drives; “Home to Work” and “Work to Home”. Next I created a database and tables to hold the data:
CREATE DATABASE `traffic`;
CREATE TABLE `data` (
`time` int(11) NOT NULL,
`route_id` tinyint(3) NOT NULL,
`jam_factor` decimal(4,2) NOT NULL,
`num_incidents` tinyint(2) NOT NULL,
KEY `route_id` (`route_id`)
);
CREATE TABLE `route` (
`route_id` int(11) NOT NULL auto_increment,
`name` varchar(20) NOT NULL,
PRIMARY KEY (`route_id`)
);
Next, I created a cron job that retrieved traffic data from the feed every 15 minutes:
#!/usr/local/bin/php -q
<?php
$db = 'traffic';
$user = 'username';
$pass = 'password';
// Connect and select database
mysql_connect('localhost', $user, $pass) or die('Could not connect: ' . mysql_error());
mysql_select_db($db) or die('Could not select database');
retrieveTrafficData();
function retrieveTrafficData() {
$rssUrl = 'https://rss.traffic.com/rss.xml?c=XXX';
$rssXml = file_get_contents($rssUrl);
$xml = new SimpleXMLElement($rssXml);
foreach ($xml->channel[0]->item as $item) {
$time = strtotime($item->pubDate);
$description = $item->description;
$data = split('-', $description);
$route = trim($data[0]);
$jf = split(':', str_replace(' ', '', $data[2]));
$inc = split(':', str_replace(' ', '', $data[3]));
$inc = preg_replace('/(.*)/', '', $inc);
$route_id = getRouteId($route);
$insert = "INSERT INTO data (time, route_id, jam_factor, num_incidents) VALUES ('$time', $route_id, $jf[1], $inc[1]);";
mysql_query($insert) or die('Route data insert failed: ' . mysql_error());
}
}
function getRouteId($route_name) {
// look for route_id
$query = "SELECT route_id FROM route WHERE name = '$route_name';";
$result = mysql_query($query) or die('RouteId query failed: ' . mysql_error());
if (mysql_num_rows($result) < 1) {
// insert route_id
$insert = "INSERT INTO route (name) VALUES ('$route_name');";
mysql_query($insert) or die('Route name insert failed: ' . mysql_error());
$id = mysql_insert_id();
} else {
$row = mysql_fetch_assoc($result);
$id = $row['route_id'];
}
return $id;
}
?>
Now that I had the data, I needed to find a way to display it in a graph. I looked at a few existing graph libraries and ended up writing my own…which was very basic. I put up a site and used AJAX to call a PHP script that retrieves the data from the database and returns it in JSON format. It worked, but it wasn’t anything fancy…
About a month later, I stumbled upon Timeplot. The library is very impressive but the only problem was that it didn’t support JSON as a data format. So, I added the ability to use JSON as a data source for the graphs:
/*
* Mimic the timeplot.loadText function
* - Only needed to change eventSource.loadText to eventSource.loadJSON
*/
Timeplot._Impl.prototype.loadJSON=function(url,separator,eventSource,filter){
if(this._active){
var tp=this;
var fError=function(statusText,status,xmlhttp){
tp.hideLoadingMessage();
$('#my-timeplot').empty().html("<span class='error'>Failed to load JSON data from " url ". Error: " statusText "</span");
};
var fDone=function(xmlhttp){
try{
if(xmlhttp.responseText.replace(/([|])/g,'').length>0){ // added to check for empty result set
eventSource.loadJSON(xmlhttp.responseText,separator,url,filter);
}
else {
$('#my-timeplot').empty().html("<span class='error'>No data found</span>");
}
}catch(e){
SimileAjax.Debug.exception(e);
}finally{
tp.hideLoadingMessage();
}
};
this.showLoadingMessage();
window.setTimeout(function(){SimileAjax.XmlHttp.get(url,fError,fDone);},0);
}
}
/*
* Mimic the eventSource.loadText function
* - Do not parse all data, only the JSON value when creating an evt
* - Parse the JSON into an Object
*/
Timeplot.DefaultEventSource.prototype.loadJSON=function(jsonText,separator,url,filter){
if(jsonText==null){
return;
}
var data = jsonText.parseJSON(); // parse JSON into an Object
this._events.maxValues=new Array();
var base=this._getBaseURL(url);
var dateTimeFormat='iso8601';
var parseDateTimeFunction=this._events.getUnit().getParser(dateTimeFormat);
var added=false;
if(filter){
data=filter(data);
}
if(data){
for(var i=0;i<data.length;i ){
var row=data[i];
if(row.date){
var evt=new Timeplot.DefaultEventSource.NumericEvent(
parseDateTimeFunction(row.date),
this._parseJSONValue(row.value,separator)
);
this._events.add(evt);
added=true;
}
}
}
if(added){
this._fire('onAddMany',[]);
}
}
/*
* Turn the JSON value into an array so that it can be correctly processed
* by Timeplot
*/
Timeplot.DefaultEventSource.prototype._parseJSONValue=function(value,separator){
value=value.replace(/rn?/g,'n');
var pos=0;
var len=value.length;
var line=[];
while(pos<len){
var nextseparator=value.indexOf(separator,pos);
var nextnline=value.indexOf('n',pos);
if(nextnline<0)nextnline=len;
if(nextseparator>-1&&nextseparator<nextnline){
line[line.length]=value.substr(pos,nextseparator-pos);
pos=nextseparator 1;
}else{
line[line.length]=value.substr(pos,nextnline-pos);
pos=nextnline 1;
break;
}
}
if(line.length<0)return;
return line;
}
Throw it all together and here is the final result!