TED Open Source: How we went live with our electricity use.

As promised, below you will find our recipe for the TED, The Energy Detective hack that enabled us to publish our electricity use on the Energy Circle site, starting Earth Day. Thanks to Peter Murray for putting this together. If you take this on, good luck! Please report back to let us know how it goes.

Recipe for: ASUS WL-500g router as TED data harvester

Please note, these instructions are designed for folks comfortable hacking Linux at a fairly low level.

1. Purchase an ASUS WL-500g Premium (I used v2). Here's a link at NewEgg:Asus WL-500gP. Technically you could use any device that will run Linux and has a USB port that can be plugged into your TED - we liked the small form factor and energy efficiency of the ASUS router over running a full computer for this purpose.
2. Follow the instructions here to build and install OpenWRT Kamikaze for the ASUS. Change the root password and reboot before proceeding.
3. Once it's up an running, log in and install and remove the following packages. This will involve editing your opkg.conf file and setting up your host with http running and serving the build modules.

    opkg remove kmod-ppp
    opkg remove kmod-pppoe
    opkg remove ppp-mod-pppoe
    opkg remove ppp
    opkg install kmod-usb-serial
    opkg install kmod-usb-serial-ftdi
    opkg install libpthread
    opkg install python-mini
    opkg install pyserial
    opkg install usbutils
    opkg install curl
    opkg install ntpclient

4. Configure the wifi for client operation. Below are configurations I used to connect to a WPA protected network with a static IP address. You will need to substitute your network's IPs - or use DHCP.
   

    #    edit /etc/config/network to configure the wifi interface
        config interface    wifi
        option ifname       "wl0"
        option proto        static
        option ipaddr       <IP ADDRESS>
        option netmask      255.255.255.0
        option gateway      <GW ADDRESS>
        option dns          <DNS SERVER ADDRESS>
    
    #   edit /etc/config/wireless appropriately for the local network
    ---------------------------------
    config 'wifi-device' 'wl0'
        option 'type' 'broadcom'
        option 'channel' '6'
        option 'disabled' '0'
    
    config 'wifi-iface'
        option 'device' 'wl0'
        option 'network' 'wifi'
        option 'mode' 'sta'
        option 'ssid' ''
       option 'encryption' ''
       option 'key' ''
   ----------------------------
      wifi up
   #   install nas start script
      /etc/init.d/nas start
      ln -s /etc/init.d/nas /etc/rc.d/S55nas

   YMMV with these configurations - consult the www.openwrt.org site for loads of forum posts etc on configuring OpenWRT as a client in various situations.

5. Manually install the termios.so library from the full python build. This is required to run the python serial interface (pyserial) to access TED.

 scp <USER>@<YOUR_HOST>:/<path_to_kamikaze_build_of>/termios.so 
    /usr/lib/python2.6/lib-dynload

6. Plug in TED USB cable to router. You should shortly see dmesg output like this:
   

    usb.c: registered new driver serial
    usbserial.c: USB Serial support registered for Generic
    usbserial.c: USB Serial Driver core v1.4
    usbserial.c: USB Serial support registered for FTDI SIO
    usbserial.c: USB Serial support registered for FTDI 8U232AM Compatible
    usbserial.c: USB Serial support registered for FTDI FT232BM Compatible
    usbserial.c: FTDI FT232BM Compatible converter detected
    usbserial.c: FTDI FT232BM Compatible converter now attached to ttyUSB0
      (or usb/tts/0 for devfs)
    usbserial.c: USB Serial support registered for USB-UIRT Infrared
      Tranceiver
    usbserial.c: USB Serial support registered for Home-Electronics TIRA-1
      IR Transceiver
    ftdi_sio.c: v1.3.5:USB FTDI Serial Converters Driver

   And you should have the device node /dev/usb/tts/0 available.

7. Download the ted.py file originally written by Micah Dowty:

 wget http://www.bananabend.net/energy_detective/python/ted_orig.py

8. Run with
   

       python ted_orig.py /dev/usb/tts/0 

   and you should get output from your TED.

9. At this point, you can modify the python script to do whatever you like with the output from TED. We modified the ted_orig.py to dump the results every second into a file in the OpenWRT's ramdisk. The modified version of the TED python script is here. We started this script with an init.d entry:

 #!/bin/sh /etc/rc.common
 # Copyright (C) 2006 OpenWrt.org
 START=50
 
 start () {
         /usr/bin/python /root/ted2.py /dev/usb/tts/0 &
 }
 
 stop() {
         killall -9 python
 }

10. We then created a quick shell script that used curl to post the output to a PHP script on our server using curl. This is fired from cron once per minute.
    

     #!/bin/sh
     
     if [ -f /tmp/ted ]; then
       VALUE=`cat /tmp/ted`
       echo ${VALUE} > /tmp/post.out
       /usr/bin/curl --max-time 15 <SERVER_URL>?${VALUE} >> /tmp/post.out 2>&1
     else
       echo 'skipped' > /tmp/post.out
     fi

11. The server-side php script that SERVER_URL pointed to looks like this:
    

     <?php
     
     #
     # Insert code to extablish mysql connection to database
     #
     
     $housecode = $_GET{'house_code'};
     $kw_rate = $_GET{'kw_rate'};
     $volts = $_GET{'volts'};
     $kw = $_GET{'kw'};
     
     if($kw && $housecode)
       {
         $insert = "insert into ted_data (housecode, ts, value) values ('" .
               $housecode . "', '" . date("Y-m-d H:i:00") . "', " . $kw . ")";
         mysql_query($insert);
         $err = mysql_error();
         if($err)
           {
     	echo "ERROR: " . $err;
           }
         else
           {
     	echo "OK";
           }
       }
     else
       {
         echo "ERROR: Bad input";
       }
     ?>

12. The schema for the database looks like this
    

     CREATE TABLE IF NOT EXISTS `ted_data` (
       `ts` datetime default '0000-00-00 00:00:00',
       `value` double NOT NULL,
       `title` varchar(256) default NULL,
       `annotation` text,
       `housecode` varchar(5) NOT NULL
     );

13. We then used the PHP Google Datasource adapter found here to establish our Google Datasource.
    

     <?php
     ob_start("ob_gzhandler");
     error_reporting (E_ALL ^ E_NOTICE);
     require_once 'MC/Google/Visualization.php';
     $db = new PDO('mysql:host=' . $DBHost . ';dbname=' . $DBase, $User, $Password);
     $vis = new MC_Google_Visualization($db, 'mysql');
     
     $vis->addEntity('ted_data',
       array(
         'table' => 'ted_data',
         'fields' => array(
     	'timestamp' => array('field' => 'ts', 'type' => 'datetime'),
     	'kW' => array('field' => 'value', 'type' => 'number'),
     	'title' => array('field' => 'title', 'type' => 'text'),
     	'annotation' => array('field' => 'annotation', 'type' => 'text'),
     	),
         'where' => 'ts > date_sub(now(), interval 72 HOUR)'
         )
     );
     $vis->setDefaultEntity('ted_data');
     $vis->handleRequest();
     ?>

14. And, finally, used Google's Annotated Timeline widget to render the graph. We used some JS to modify the layout of the timeline widget to integrate with the EnergyCircle look and feel - for clarity that code is not included here...
    

     
     <script type="text/javascript" src="http://www.google.com/jsapi"></script>
     <script type="text/javascript">
     
     google.load("visualization", "1", {packages:["annotatedtimeline"]});
     google.loader.writeLoadTag("css", "ted.css");
     google.setOnLoadCallback(drawChart);
     var query;
     function reload()
     {
       query.send(callback);
     }
     
     function isIE()
     {
       return /msie/i.test(navigator.userAgent) &&
           !/opera/i.test(navigator.userAgent);
     }
     
     function callback(result)
     {
       if(result.isError())
         {
     	alert(result);
         }
       else
         {
     	chart.draw(result.getDataTable(),
               {displayAnnotations: true, allowHtml: true});
         }
       setTimeout("reload();", 60 * 5 * 1000);
     };
     
     function drawChart()
     {
       query = new google.visualization.Query('/ted_query.php');
       query.setQuery('select timestamp, kW, title, annotation');
       var div = document.getElementById('chart_div');
       chart = new google.visualization.AnnotatedTimeLine(div);
       query.send(callback);
     }
     </script>
     <div style="height:825px" id='chart_div'>Loading...</div>
     

Please let us know if this recipe is helpful and feel free to post questions, errors, or omissions here or email info@energycircle.com.

Updated May 29, 2009 @ 13:40 for formatting and tiny content changes - more info coming!

Updated May 29, 2009 @ 14:30 to include server-side code