Traffic charts for my commute

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!