26 Dec 2010

For the last couple years, I’ve been logging my energy usage and producing daily graphs of the data. Back then, I wrote about how the Google Charts API could be used to generate nice looking graphs; the result looking like this:

But in practise this isn’t what I have been using. Instead, I have been using rrdtool to log the data and produce much less attractive graphs like this:

Although rrdtool is very powerful, it suffers from a steep learning curve to figure out how to do just about anything with it; my whole set-up was cribbed from Paul’s blog. One advantage of using rrdtool for this type of long-term data collection is its ability to consolidate data – the older the data, the less fine-grained detail is kept.

Last year I stitched a bunch of the rrdtool-generated images together and produced a video of the graphs. That was mostly a learning exercise in how to do video on Linux, but it did highlight a drawback with rrdtool. As I mentioned in that previous post, the effect of the video is somewhat spoilt by the y-axis jumping around. Ideally, I would regenerate the graphs with a fixed axis to stop this. But rrdtool’s data consolidation makes this impossible – I no longer have the fine-grained data.

Whilst I don’t necessarily plan to redo the video, last month I decided to start keeping the data intact for future use.

The raw data is now going into a sqlite database so I can do interesting things with it – and worry about how to consolidate the data later on. As I am using an mqtt-powered pub/sub system behind all of this, it was trivial to add a new subscriber to the appropriate topic to grab the data without touching the rrdtool subscriber.

After a few days of collecting the data, I started to think about what to do with it. Rather than produce just the current day’s power graph, I wanted to be able to browse the full data. This called for something more interactive than just the static graphs – which gave me enough of an excuse to play with the JavaScript library Protovis, which is capable of some very impressive results. While it also suffers from a steep-ish learning curve, it benefits from fairly comprehensive documentation and a lot of useful examples to learn from.

Here’s a screenshot of the site I’ve produced:

The site allows you to browse by year, month and day and also provides an indication of the actual cost of the energy usage. The data is served from a set of static files for each day, rather than driving it straight off the sqlite database. This makes it quite scalable – particularly as only the current day’s data will ever change.

I have made a copy of the site available to play with here. This isn’t being updated with any new data, for privacy reasons, but does demonstrate the interactivity of the graphs very nicely.

There are a few features I’m still thinking about adding, such as being able to compare two days usage and making it easier to identify patterns. I’ll also probably have a couple bugs to fix in 6 days time when I’ll have data spanning two years.

  1. Rexyboy • January 10, 2011

    I am playing with a Currentcost meter at the moment and love your Protovis charts. I have no experience with Protovis (I will read up) but wondered how your sqlite db data was queried and sent to Protovis.
    Can you explain further? Did you have to do much AVERAGEing of your data?

  2. Rexyboy • January 10, 2011

    Sorry my mistake: MySQL not sqlite.

  3. nickJanuary 10, 2011

    Rexyboy, I decided not to drive the site directly off the database as it was going to be hosted on a server that didn’t have access to it. Instead, I created a set of python scripts that extract data from the database into javascript files which are then securely copied up to the server. These scripts run every 10 minutes – as that is the the interval I average the date over. There isn’t much to these scripts – just some simple sql. You can see the data files here: http://knolleary.net/code/power/data/

    The page uses jQuery to load the appropriate data file for the day being viewed. You can see that in action on line 107 of http://knolleary.net/code/power/power.js

    Hope that helps.

  4. Alan • July 20, 2011

    Hi Nick

    This is fantastic.

    Any chance I can get a copy of the code to get this all up and running for myself?

  5. nickJuly 28, 2011

    Hi Alan, thanks for the comment.
    I’m sorry to say it isn’t something I can easily share. I have toyed with the idea of setting up a hosted version that people could sign up to, but haven’t even begun to think about what that would actually entail.

  6. leave a comment

    You must be logged in to post a comment.