Store

OK we are getting good data from our sensors, lets corral it into more useful chunks and store it in a database. We could make a database on the computer, but since we'd like to share this data, it makes more sense to put it online. There are custom services that are specifically designed to do this sort of thing like Pachube but I'm going to reinvent the wheel and design my own web-app that stores and displays energy data. (Mostly I want to play around with Google App Engine!)

You have 5 minutes!

We get data every few seconds from the XBee modem inside the kill-a-watt. We could, in theory, put data into our database every 2 seconds but that would quickly balloon the amount of storage necessary. It would also make sorting through the data difficult. So instead lets add up all the sensor data for 5 minutes and then take the average.

We'll do this by keeping two timers and one tally. One timer will track how long its been since the last sensor signal was sent, and the other will track if its been 5 minutes. The tally will store up all the Watt-hours (Watt measurements * time since last sensor data). Then at the end we can average by the 5 minutes

This chunk of code goes near the beginning, it creates the timers and tally and initializes them

...
fiveminutetimer = lasttime = time.time()  # get the current time
cumulativewatthr = 0
...

Then later on, after we get our data we can put in this chunk of code:

        # add up the delta-watthr used since last reading
        # Figure out how many watt hours were used since last reading
        elapsedseconds = time.time() - lasttime
        dwatthr = (avgwatt * elapsedseconds) / (60.0 * 60.0)  # 60 seconds in 60 minutes = 1 hr
        lasttime = time.time()
        print "\t\tWh used in last ",elapsedseconds," seconds: ",dwatthr
        cumulativewatthr += dwatthr

        # Determine the minute of the hour (ie 6:42 -> '42')
        currminute = (int(time.time())/60) % 10
        # Figure out if its been five minutes since our last save
        if (((time.time() - fiveminutetimer) >= 60.0) and (currminute % 5 == 0)):
            # Print out debug data, Wh used in last 5 minutes
            avgwattsused = cumulativewatthr * (60.0*60.0 / (time.time() - fiveminutetimer))
            print time.strftime("%Y %m %d, %H:%M"),", ",cumulativewatthr,"Wh = ",avgwattsused," W average")
            
            # Reset our 5 minute timer
            fiveminutetimer = time.time()
            cumulativewatthr = 0
Note that we calculate delta-watthours, the small amount of power used every few seconds. Then we can get the average watts used by dividing the watthours by the number of hours that have passed (about 1/12th). Instead of going by exact 5 minutes, I decided to only report on the 5's of the hour (:05, :10, etc) so that its easier to send all the data at once if theres multiple sensors that started up at different times.

Download wattcher-5minreporter.py from the Download page. If you run this, youll get a steady stream of:

Near the end you can see the timestamp, the Watthrs used in the last few minutes and the average Wattage.

Multisensor!

We have good data but so far it only works with one sensor. Multiple sensors will mess it up! Time to add support for more than one XBee so that I can track a few rooms. I'll do that by creating an object class in python, and using the XBee address (remember that from part 1?) to track. I'll replace the code we just wrote with the following:

At the top, instead of the timer variables, I'll have a full class declaration, and create an array to store them:

####### store sensor data and array of histories per sensor
class Fiveminutehistory:
  def __init__(self, sensornum):
      self.sensornum = sensornum
      self.fiveminutetimer = time.time()  # track data over 5 minutes
      self.lasttime = time.time()
      self.cumulativewatthr = 0
      
  def addwatthr(self, deltawatthr):
      self.cumulativewatthr +=  float(deltawatthr)

  def reset5mintimer(self):
      self.cumulativewatthr = 0
      self.fiveminutetimer = time.time()

  def avgwattover5min(self):
      return self.cumulativewatthr * (60.0*60.0 / (time.time() - self.fiveminutetimer))
  
  def __str__(self):
      return "[ id#: %d, 5mintimer: %f, lasttime; %f, cumulativewatthr: %f ]" % (self.sensornum, self.fiveminutetimer, self.lasttime, self.cumulativewatthr)

######### an array of histories
sensorhistories = []
When the object is initialized with the sensor ID number, it also sets up the two timers and cumulative Watthrs tracked. I also created a few helper functions that will make the code cleaner

Right below that I'll create a little function to help me create and retrieve these objects. Given an XBee ID number it either makes a new one or gets the reference to it

####### retriever
def findsensorhistory(sensornum):
    for history in sensorhistories:
        if history.sensornum == sensornum:
            return history
    # none found, create it!
    history = Fiveminutehistory(sensornum)
    sensorhistories.append(history)
    return history
Finally, instead of the average Watt calculation code written up above, we'll replace it with the following chunk, which retreives the object and tracks power usage with the object timers:
        # retreive the history for this sensor
        sensorhistory = findsensorhistory(xb.address_16)
        #print sensorhistory

        # add up the delta-watthr used since last reading
        # Figure out how many watt hours were used since last reading
        elapsedseconds = time.time() - sensorhistory.lasttime
        dwatthr = (avgwatt * elapsedseconds) / (60.0 * 60.0)  # 60 seconds in 60 minutes = 1 hr
        sensorhistory.lasttime = time.time()
        print "\t\tWh used in last ",elapsedseconds," seconds: ",dwatthr
        sensorhistory.addwatthr(dwatthr)

        # Determine the minute of the hour (ie 6:42 -> '42')
        currminute = (int(time.time())/60) % 10
        # Figure out if its been five minutes since our last save
        if (((time.time() - sensorhistory.fiveminutetimer) >= 60.0) and (currminute % 5 == 0)):
            # Print out debug data, Wh used in last 5 minutes
            avgwattsused = sensorhistory.avgwattover5min()
            print time.strftime("%Y %m %d, %H:%M"),", ",sensorhistory.cumulativewatthr,"Wh = ",avgwattsused," W average"

            # Reset our 5 minute timer
            sensorhistory.reset5mintimer()
The code basically acts the same except now it wont choke on multiple sensor data! Below, my two Kill-a-Watts, one with a computer attached (100W) and another with a lamp (40W):

Onto the database!

The App Engine

So we want to have an networked computer to store this data so we can share the data, but we really don't want to have to run a server from home! What to do? Well as mentioned before, you can use Pachube or similar, but I will show how to roll-your-own with Google App Engine (GAE). GAE is basically a free mini-webserver hosted by Google, that will run basic webapps without the hassle of administrating a database server. Each webapp has storage, some frameworks and can use Google accounts for authentication. To get started I suggest checking out the GAE website, documentation, etc. I'll assume you've gone through the tutorials and jump right into designing my power data storage app called Wattcher (a little confusing I know)

First, the app.yaml file which defines my app looks like this:

application: wattcher
version: 1
runtime: python
api_version: 1

handlers:
- url: /.*
  script: wattcherapp.py
Pretty simple, just says that the app uses wattcherapp.py as the source file

Next, we'll dive into the python code for our webapp. First, the includes and database index. To create a database, we actually define it -in the python file-, GAE then figures out what kind of database to create for you by following those directions (very different than MySQL where you'd create the DB separately)

import cgi, datetime

from google.appengine.api import users
from google.appengine.ext import webapp
from google.appengine.ext.webapp.util import run_wsgi_app
from google.appengine.ext import db

class Powerusage(db.Model):
  author = db.UserProperty()           # the user
  sensornum = db.IntegerProperty()     # can have multiple sensors
  watt = db.FloatProperty()          # each sending us latest Watt measurement
  date = db.DateTimeProperty(auto_now_add=True)    # timestamp
We use the default includes. We have a single database table called Powerusage, and it has 4 entries: one for the user, one for the sensor number, one for the last reported Watts used and one for a datestamp

Each 'page' or function of our webapp needs its own class. Lets start with the function that allows us to store data in the DB. I'll call it PowerUpdate.

class PowerUpdate(webapp.RequestHandler):
  def get(self):

    # make the user log in
    if not users.get_current_user():
        self.redirect(users.create_login_url(self.request.uri))

    powerusage = Powerusage()
    
    if users.get_current_user():
        powerusage.author = users.get_current_user()
    #print self.request
    if self.request.get('watt'):
        powerusage.watt = float(self.request.get('watt'))
    else:
         self.response.out.write('Couldnt find \'watt\' GET property!')
         return
    if  self.request.get('sensornum'):
        powerusage.sensornum = int(self.request.get('sensornum'))
    else:
        powerusage.sensornum = 0   # assume theres just one or something
        
    powerusage.put()
    self.response.out.write('OK!')

When we send a request to do that with a GET call (ie requesting the webpage), we'll first make sure the user is authenticated and logged in so we know their name. Then we'll create a new database entry by initializing a new instantiation of Powerusage. Then we'll look the GET request for the watt data, which would be in the format watt=39.2 or similar. That's parsed for us, thankfully and we can also get the sensor number which is passed in the format sensornum=3. Finally we can store the data into the permanent database

Next is a useful debugging function, it will simply print out all the data it has received for your account!

class DumpData(webapp.RequestHandler):
  def get(self):

    # make the user log in
    if not users.get_current_user():
        self.redirect(users.create_login_url(self.request.uri))

    self.response.out.write('<html><body>Here is all the data you have sent us:<p>')

    powerusages = db.GqlQuery("SELECT * FROM Powerusage WHERE author = :1 ORDER BY date", users.get_current_user())

    for powerused in powerusages:
        if powerused.sensornum:
          self.response.out.write('<b>%s</b>\'s sensor #%d' %
                                  (powerused.author.nickname(), powerused.sensornum))
        else:
          self.response.out.write(<b>%s</b>' % powerused.author.nickname())

        self.response.out.write(' used: %f Watts at %s<p>' % (powerused.watt, powerused.date))
    self.response.out.write("</body></html>")
This function simply SELECT's (retrieves) all the entries, sorts them by date and prints out each one at a time

Finally we'll make a basic 'front page' that will show the last couple of datapoints sent:

class MainPage(webapp.RequestHandler):
  def get(self):

    self.response.out.write('<html><body>Welcome to Wattcher!<p>Here are the last 10 datapoints:<p>')

    powerusages = db.GqlQuery("SELECT * FROM Powerusage ORDER BY date DESC LIMIT 10")

    for powerused in powerusages:
        if powerused.sensornum:
          self.response.out.write('<b>%s</b>\'s sensor #%d' %
                                  (powerused.author.nickname(), powerused.sensornum))
        else:
          self.response.out.write('<b>%s</b>' % powerused.author.nickname())

        self.response.out.write(' used: %f Watts at %s<p>' % (powerused.watt, powerused.date))
    self.response.out.write("</body></html>")
It's very similar to the DataDump function but its only 10 points of data and from all users, nice to use when you just want to 'check it out' but don't want to log in

Finally, we have a little initializer structure that tells GAE what pages link to what functions:

application = webapp.WSGIApplication(
    [('/', MainPage),
     ('/report', PowerUpdate),
     ('/dump', DumpData)],
    debug=True)

def main():
  run_wsgi_app(application)

if __name__ == "__main__":
  main()

Test!

OK lets try it out, first lets visit http://wattcher.appspot.com/report

Remember we made it a requirement to supply -some- data. Lets try again http://wattcher.appspot.com/report?watt=19.22&sensornum=1
Yay we got an OK! Lets check out the data stored by visiting http://wattcher.appspot.com/dump
There's two entries because I did a little testing beforehand but you can see that there are 2 entries. Nice!

We can also visit the GAE control panel and browse the data 'by hand'

Anyways, now that that's working, lets go back and add the reporting technology to our sensor-reader script.

Getting the report out

Only a little more hacking on the computer script and we're done. We want to add support for sending data to GAE. Unfortunately right now our authentication is done through Google accounts so its not easy to run on an Arduino. To adapt it you'd have to send the username in the Report GET and hope nobody else uses the same one (unless you also add a basic password system)

Anyhow, I totally ripped off how to do this from some nice people on the Internet

Download appengineauth.py from the download page, and change the first few lines if necessary. We hardcode the URL we're going to and the account/password as well as the GAE app name

users_email_address = "[email protected]"
users_password      = "mypassword"
my_app_name = "wattcher"
target_authenticated_google_app_engine_uri = 'http://wattcher.appspot.com/report'

The real work happens at this function sendreport where it connects and sends the Watt data to the GAE site:

def sendreport(sensornum, watt):
    # this is where I actually want to go to
    serv_uri = target_authenticated_google_app_engine_uri + "?watt="+str(watt)+"&sensornum="+str(sensornum)

    serv_args = {}
    serv_args['continue'] = serv_uri
    serv_args['auth']     = authtoken
    
    full_serv_uri = "http://wattcher.appspot.com/_ah/login?%s" % (urllib.urlencode(serv_args))

    serv_req = urllib2.Request(full_serv_uri)
    serv_resp = urllib2.urlopen(serv_req)
    serv_resp_body = serv_resp.read()

    # serv_resp_body should contain the contents of the 
    #  target_authenticated_google_app_engine_uri page - as we will have been 
    #  redirected to that page automatically 
    #
    # to prove this, I'm just gonna print it out
    print serv_resp_body
Finally, we wrap up by adding the following lines to our computer script, which will send the data nicely over to GAE!
  # Also, send it to the app engine
            appengineauth.sendreport(xb.address_16, avgwattsused)
You can download the final script wattcher.py - final from the download page!

Don't forget to visit wattcher.appspot.com to check out the lastest readings:

Last updated on 2015-11-20 at 05.08.01 PM Published on 2014-12-03 at 02.21.00 PM