Real-time visualization of electric power consumption

Yesterday I have showed how to read power consumption with a cheap power monitor device. Today I'm gonna put together very basic server and client web applications to store the power usage information in a DB and visualize them in a webpage.

Overall design

Since a picture is worth a thousand words here is the design.

It cannot really get any simpler 🙂 First of all we need a web server with installed Apache, PHP and MySQL. The second step is to create a database with a single table. We will need only these fields:

  • id - To keep order in our data
  • ts - A timestamp where the data was stored.
  • power - Instantaneous power usage in Watts.

The table structure in SQL is this:

--
-- Table structure for table `elusage`
--
CREATE TABLE IF NOT EXISTS `elusage` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `power` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1145941;

Pushing data on the server

Here we need to implement two things, a publisher that will run on your computer at home and the consumer running on the web server. We will push the data on the server by http GET parameter. To make sure no one else will mess up with our data we add a secret token as another GET parameter, something like this:

http://www.server.com/el/power.php?token=a5K4Dli8L0&add=123

The number 123 is the power in Watts that our script needs to add to the request. To do the http GET request from Python is very easy, especially if we use a module like the requests. This is how we need to modify our Python application.

import requests
.
.
def run(uart_port, webapp="", period_sec=4):

    # Open the serial line
    device = serial_for_url(uart_port, timeout=1)

    # Initialize the device
    data = exec_cmd(device, START_CMD)
    print "Startup response: ",
    print data

    # Keep reading the power consumption infinitely
    while True:
        power = read_power(device)
        print str(power) + " Watts   ",

        # Sometimes the power meter returns 0 Watts. This can happen if
        # the power meter itself fails to read data from the main unit over the RF link.
        if power == 0:
            print "  Ignoring *********************************"
            continue

        # Upload on the server if possible
        if webapp != "":
            try:
                result = requests.get(webapp+str(power))
                print "Update request: "+str(result.status_code)
            except Exception as e:
                print "Connection error: ", e
            except:
                print "Unknown exception: ", sys.exc_info()[0]

        # Pause for 'period' seconds
        time.sleep(period_sec)

    device.close()

if __name__=="__main__":

    uart_port = "/dev/ttyUSB1"
    webapp = 'http://www.server.com/el/power.php?token=a5K4Dli8L0&add='
    run(uart_port, webapp)

The full code is available from http://code.google.com/p/electroncastle/source/browse/el_pwr_monitor/meter/elmeter.py

Server update script

The PHP application is even easier, it receives the http request, verifies the token and adds the data to the DB. This is part of the power.php that is responsible just for that.

<?php

function connect_db()
{
   // Connects to the database
   mysql_connect("localhost", "elmonitor_app", "db_password") or die(mysql_error());
   mysql_select_db("elmonitor_app") or die(mysql_error());
}

function updatePower($val)
{
   connect_db();
   $result = "INSERT INTO elusage (power) VALUES (".$val.")";
   mysql_query($result);
}

if ($_GET["token"] == "a5K4Dli8L0"){
   $val = $_GET["add"];
   if ($val != "")
      updatePower($val);
}

?>

The webpage code

To do a real time visualization on a webpage we need to implement an AJAX script that will periodically query the web server for latest data and the PHP code that does this job on the server. To graph the data I'm using freely available library Highcharts This takes care about most of the work, we only need to pre-fetch enough data to show a graph for required time interval and then periodically pull for the latest power usage data. This is written in Javascript. The complete code including the embedded Highcharts you can view here:

http://code.google.com/p/electroncastle/source/browse/el_pwr_monitor/webapp/el/index.php

On the server site, in the power.php, is the bellow attached code. The important functions are getLast($minutes)  that returns all samples for last $minutes and getNow() which returns only the last sample. The data is encoded in JSON where each sample is a two items array.

[timestamp, power]

The timestamp is a unix timestamp (The unix timestamp is the number of seconds since January 1 1970 00:00:00 UTC) in milliseconds and the power is the usage in Watts.


<?php

function connect_db()
{
   // Connects to the database
   mysql_connect("localhost", "elmonitor_app", "db_password") or die(mysql_error());
   mysql_select_db("elmonitor_app") or die(mysql_error());
}

function showUsageTable()
{
   echo "<a href=index.php>Realtime graph</a><br>";
   echo "<center>Electricity consumption</center><br>";
   connect_db();

   $result = mysql_query("SELECT * FROM elusage WHERE ts >= CURRENT_TIMESTAMP - INTERVAL 1 HOUR ORDER BY ts DESC") or die (mysql_error());
?>
<table border="1" align=center>
<tr><td  width=10>Sample</td><td  width=200>Date and time</td><td  width=100>Power (W)</td></tr>
<?php
   while ($row = mysql_fetch_assoc($result)) {
    echo "<tr>";
    echo "<td>".$row['id']."</td>";
    echo "<td>".$row['ts']."</td>";
    echo "<td>".$row['power']."</td>";
    echo "</tr>";
   }
?>
 </table>
<?php
}

function getRange($from, $to)
{
   connect_db();
   $sql="SELECT * FROM elusage  WHERE ts BETWEEN '${from}' AND '${to}' ORDER BY ts ASC";
   $result = mysql_query($sql) or die (mysql_error());

	$ar=array();
	while ($row = mysql_fetch_assoc($result)) {
		$pwr=intval($row['power']);
		if ($pwr != 0){
			array_push($ar, array(strtotime($row['ts'])*1000, $pwr));
		}
	}
	return $ar;
}

function getRangeJsn($from, $to)
{
	header("Content-type: text/json");
	$ar = getRange($from, $to);
	echo json_encode($ar);
}

function getLast($minutes)
{
	$tm = time();
	$from = date('Y-m-d H:i:s', $tm - $minutes*60);
	$to = date('Y-m-d H:i:s', $tm);
	return getRangeJsn($from, $to);
}

function getNow()
{
	 header("Content-type: text/json");
    connect_db();

   $result = mysql_query("SELECT * FROM elusage ORDER BY ts DESC LIMIT 1") or die (mysql_error());
   $row = mysql_fetch_assoc($result);
	if ($row){
		// Convert time to unix timestamp in milliseconds
		// The unix timestamp is the number of seconds since January 1 1970 00:00:00 UTC
    	$ret = array(strtotime($row['ts'])*1000, intval($row['power']));
		echo json_encode($ret);
   }
}

function updatePower($val)
{
   connect_db();
   $result = "INSERT INTO elusage (power) VALUES (".$val.")";
   $add_member = mysql_query($result);
}

// --------------- ENTRY POINT --------------------
foreach($_GET as $key => $val){
   switch ($key){
	case "last":
		getLast($val);
		return;
	case "now":
		getNow();
		return;
	case "add":
		if ($_GET["token"] == "a5K4Dli8L0")
	 	   updatePower($val);
	   return;
	break;
   }
}

// Without parameters show simple html table with the power consumption history
showUsageTable();
?>

If the power.php is called without any parameters, function showUsageTable() gets executed. This function generates simple html table with the timestamps and the power consumption data.

The complete code for the publisher,  webserver and the client can be downloaded from the electroncastle repository on the googlecode

If you put everything together you should be rewarded with a nice real time graph looking like this.

If you want to just see it in action check out my house power consumption on address http://fajtl.net/el/ Note this is just a sandbox for experimentation so the link may not be online permanently.

Conclusion

So that's it for today. This application is so simple so it can be put together on the Sunday afternoon just for fun. Besides from the fun It's very interesting to see a power consumption of your house from any place. There is, of course, a huge space for improvements and tons of features in the term of more data analysis, presentation of historical data, improved security etc. The publisher code can be written in any language and run on any computer with USB host (or possibly even RS232) and internet connectivity. Raspberry PI is one of many very cheap computers that can be used for this kind application. Since it is based around CPU for mobile devices it has very low power consumption so it can run 24/7 without any significant running cost.

Many years ago I got a broken Sony VAIO laptop from my friend. It has broken IDE port for HDD but apart from that it's OK. It boots a Slax linux from a CD with no problem. It also has a few USB ports and very very low power consumption without the HDD and the screen off so I decided to use it for this project. This is it in the action 🙂

This entry was posted in Home automation. Bookmark the permalink.

Leave a Reply