Accessing Our Sensor Data: PHP + SQLite + SVG

So we have our sensor(s) dutifully logging their data to an SQLite database. Now we need to process and view this data.

LASP

First we need to set our Raspberry Pi up as a webserver. This typically requires: Linux, Apache, MySQL and PHP – a LAMP framework. There is a handy guide: here (thanks Dave!).

In my case I wanted a light-weight alternative to MySQL so I decided to use SQLite. You can also swap Apache for something like Lighttpd if you feel like it (I may in the future but didn’t this time).

On Raspian (and in summary of the guide in Dave’s link) I installed the following packages:

sudo apt-get update
sudo apt-get install apache2 php5 libapache2-mod-php5
sudo apt-get install php5-sqlite

To avoid permission complications I set up my web directory in my home directory. This meant I could easily access and upload files from the iPad. To do this edit: /etc/apache2/sites-available/default, e.g.:

sudo nano edit: /etc/apache2/sites-available/default

In the section:

DocumentRoot /var/www
    <Directory />
        Options FollowSymLinks
        AllowOverride None
    </Directory>
    <Directory /var/www/>
        Options Indexes FollowSymLinks MultiViews
        AllowOverride None
        Order allow,deny
        allow from all
    </Directory>
 

Change /var/www for /home/[yourusername]/[newfolder], e.g.:

DocumentRoot /home/jimbojones/www
    <Directory />
        Options FollowSymLinks
        AllowOverride None
    </Directory>
    <Directory /home/jimbojones/www/>
        Options Indexes FollowSymLinks MultiViews
        AllowOverride None
        Order allow,deny
        allow from all
    </Directory>
 

Hurray! Now you should be ready to write some PHP.

Accessing the Database

In versions 5.3+ of PHP SQLite is enabled by default. To check run:

<?php phpinfo(); ?>

and search the page for ‘sqlite’.

I found that getting this working took a bit of fiddling. There were several different versions of the access code floating about and not all of them worked. I would only look for recent entries if you google.

However, eventually this bit of PHP worked for me:

$db = new SQLite3('EnergyDB/energymonitor.db');
	
$results = $db->query('SELECT * FROM readings');
while ($row = $results->fetchArray()) 
{
var_dump($row);
}

Fun with SQL

Now we have database access working we can experiment with some SQL queries. While testing it is easier to do this via the SQLite command line. This way we can obtain a set of working queries without the added burden of PHP debugging. These working queries can then be added to our PHP code for testing.

My first attempt was:

SELECT * FROM readings WHERE r_datetime > (SELECT DATETIME('now', '-1 day'))

This worked!

You can change ‘day’ to ‘hour’ to get the last hour’s report:

SELECT * FROM readings WHERE r_datetime > (SELECT DATETIME('now', '-1 hour'))

Of course, when copying to the PHP remember to change the inner single quotes to double quotes (d’oh!).

Graph It Up

No-one likes dry numbers. Everyone loves a pretty graph. Now we will create our own using PHP and SVG (scalable vector graphics).

HTML5 has support for SVGs out the box. This makes life easy for us. I found this summary from IBM useful. W3Schools is also useful. To knock up a quick graph I followed the following steps:

  • Dump the data from the SQL query into data arrays;
  • Set the dimensions of our graph;
  • Set scale factors for our y-axes (basically: chart_height / max(array_value));
  • Draw our x, y axes;
  • For each data value:
    • Draw an x ticker, and
    • Determine a y value; and
  • Plot polylines for each set of data values.

And hey presto:

[12 hours]

[24 hours]

The PHP code is as follows:

<?php
	
	$db = new SQLite3('EnergyDB/energymonitor.db');
	
	if (!$db) die ($error);
	
	/*$results = $db->query('SELECT * FROM readings');*/
	
	$statement = $db->prepare('SELECT * FROM readings WHERE r_datetime > (SELECT DATETIME("now", "-24 hour"))');
	$results = $statement->execute();
	if (!$results) die("Cannot execute statement.");
	$i = 0;
	while ($row = $results->fetchArray()) 
	{
		/*var_dump($row);*/
		/*echo $row['r_temp']  . " : " . $row['r_watts'];
		echo "<br>";*/
		$watts[$i] = $row['r_watts'];
		$temp[$i] = $row['r_temp'];
		$i=$i+1;
	}
	$maxwatts = max($watts);
	$maxtemp = max($temp);
	
	/*Set parameters for width & height of chart*/
	$width = 800;
	$height = 600;
	$tickerlength = 5;
	$scalefactorwatts = $height / $maxwatts;
	$scalefactortemp = $height / $maxtemp;
	$totalheight= $height + (2*$tickerlength);
	$xticker = floor($width/count($watts));
	
	echo '<svg xmlns="http://www.w3.org/2000/svg" version="1.1" width="'.$width.'" height="'.$totalheight.'" >
		<line x1="0" y1="'.$height.'" x2="'.$width.'" y2="'.$height.'" 
					           style="stroke:black;stroke-width:2"/>
		<line x1="0" y1="0" x2="0" y2="'.$height.'" 
					           style="stroke:black;stroke-width:2"/>';
	for ($j = 0; $j < count($watts); $j++) {
		$x = $j*$xticker;
		$tickerheight = $height+$tickerlength;
		echo '<line x1="'.$x.'" y1="'.$height.'" x2="'.$x.'" y2="'.$tickerheight.'" style="stroke:black;stroke-width:1"/>';
		
		$ywatts = $height - $watts[$j]*$scalefactorwatts;
		$polystringwatts = $polystringwatts." ".$x.",".$ywatts;
		
		$ytemp = $height - $temp[$j]*$scalefactortemp;
		$polystringtemp = $polystringtemp." ".$x.",".$ytemp;
		
	}
	echo '<polyline points="'.$polystringwatts.'" style="fill:none;stroke:red;stroke-width:4"/>';
    echo '<polyline points="'.$polystringtemp.'"
         style="fill:none;stroke:green;stroke-width:4"/>';    
	echo '</svg>';
	
?>
Advertisements

Databases for Sensor Data in Python

Here are some options to persistently store sensor data in a database using python.

Anydbm

For simple key-value pairs the anydbm module looks the best.

It stores values as strings. Because of this you may need to use another module called pickle to convert non-string values into strings.

For example:

import anydbm, pickle
from datetime import datetime

#Open a database - if it doesn't exist: create it
db = anydbm.open('test_data.db', 'c')

time_now_in = datetime.now()
string_version_in = pickle.dumps(time_now_in)

#Store value
db['1'] = string_version_in
#Close database
db.close()

#Open database
db2 = anydbm.open('test_data.db')

string_version_out = db2['1']
time_now_out = pickle.loads(string_version_out)
print time_now_out

db2.close()

SQLite

For more advanced database features the next step is sqlite3.

A tutorial on sqlite3 can be found here.

You may need to install sqlite3:

sudo apt-get install sqlite3

Then in python you can use SQL statements using code such as:

import sqlite3 as lite

#... - data in variables

# Set up a new database connection - db will be created if it doesn't exist
con = lite.connect('energymonitor.db')

#with the db connection (simplifies commit etc)
with con:

cur = con.cursor()

#Create a READINGS table if it doesn't already exist
cur.execute('CREATE TABLE IF NOT EXISTS readings (r_datetime TIMESTAMP, r_watts INT, r_temp INT)')

#Store sensor readings
cur.execute('INSERT INTO readings VALUES(?,?,?)', (time, watts, temp))

This then stores my energy monitor readings in a database called (unoriginally) "readings":

Simple Energy Monitoring on Raspberry Pi

So. Reading an LCD screen was more difficult than I first assumed. My code is still good for reading my Thermostat values. However for energy monitoring and £20 I found a better solution.

Looking on the net a particular model of energy monitor was recommended for easy monitoring: the Current Cost EnviR.

This monitor has two big advantages:

  • A USB data cable that allows a serial port connection; and
  • Simple XML output over this connection.

These two features were a breath of fresh air after dealing with the proprietary mess that is the Owl/Electrisave energy monitor (just google).

A little trick: the EnviR was supplied by EON to its customers. While the EnviR is listed for about £50-70 new you can also buy cheap it on eBay by way of this EON link. Look for items listed as EON energy monitor and compare with the picture above (also look for the white RJ45 to USB cable – the monitor also comes in black).

Once you have a monitor get code from https://github.com/mapkyca/Current-Cost-EnviR

cd ~/Code
git clone https://github.com/mapkyca/Current-Cost-EnviR

(Many thanks to Marcus Povey for this http://www.marcus-povey.co.uk.)

If pip is not installed:

sudo apt-get install python-pip

Install pySerial:

sudo pip install pyserial

I got an error that the serial port was already open – this was resolved by deleting the following line:

meter.open()

I then found that my energy usage was shown as 0 Watts. Doing a print out of the read data I noticed that there were three channels. I had plugged my monitor sensor into the third input on the energy monitor transmitter: hence the reading was on channel 3 (<ch3>):

Through trial and error I worked out that channel 1 is the central sensor socket on the transmitter:

Success!


Todo:

  • Setup time correctly on monitor (or ignore and use datetime – datetime.datetime.now());
  • Set up a local database and store the tuple: (timestamp, energy, temp);
  • Clone data in Pachube/Cosm/Xively (now owned by LogMeIn!)?

Home Inputs & Outputs

If thinking about the home as a system I need to look at Input/Output (I/O) options. If using a biological model the inputs map to senses and the outputs to muscles and nervous system control.

I do not have all these items but hear are some ideas.

Inputs

  • Energy Monitor e.g. Owl via CM 160 (Owl USB)
  • WeMo Motion sensor
  • Lego Mindstorms Kit (probably coupled to Raspberry Pi via Python bindings)
    • Ultrasound Sensor
    • Light Sensor
    • Sound Sensor
  • Wii Remote via Bluetooth to Raspberry Pi
  • Webcam via USB to Raspberry Pi
  • Withings Scales
    • Weight and Body Mass – via IFTTT
    • CO2
    • Temperature
    • Heart Rate
  • Thermostat – e.g. via something like this – would also give temperature
  • Outputs

  • WeMo Switches
    • Lamps – via plugs
    • TV – on/off (on probably goes into standby – less useful)
    • Kettle – although has separate switch to boil – not amazingly useful
    • Toaster – as for kettle
    • Speakers/Stereo – on/off
    • Coffee Machine
    • Washing Machine – any would need additional input to activate
    • Dishwasher – ditto
  • Lego Mindstorms Kit (probably coupled to Raspberry Pi)
    • Motors (x 3)
  • Audio – via speakers attached to Raspberry Pi
  • Video – Monitor / TV attached to Raspberry Pi
  • Webpage – access via mobile devices – hosted on Raspberry Pi
  • Old Digital JPEG Photoframe – anyway to make wireless via USB hack?
  • Hue Light Bulbs
  • Air Quality Egg
  • If anyone has any ideas or has successfully implemented any other devices please add to the comments below.

    Programming a Raspberry Pi from the Sofa

    I have set up quite a nifty little workflow for interfacing and programming the Raspberry Pi.

    SSH Access

    I have set up the Pi with a static IP address and configured SSH access.

    Setting up a static IP address is easy with an AirPort Extreme. Using AirPort Utility goto Advanced options>DHCP and NAT>Reservations. Set up a new reservation based on the MAC address of your Pi – I recommend one of the higher IP addresses within the range.

    I can then use iSSH on the iPad to log into the Pi. My settings are as follows:

    20130810-161430.jpg
    This allows me to login and have access to the Pi command line from my iPad:

    20130810-161937.jpg

    Coding

    To write programs I use the excellent Textastic. Both iSSH and Textastic are about £6 or £7 but they are worth the price.

    In Textastic I have set up an SFTP link (FTP over SSH) using the same parameters as the iSSH link. I have also linked my Dropbox account. I can then write programs on the iPad within Textastic before copying them across to a directory on the Raspberry Pi. This is as easy as selecting a menu icon and clicking “Upload File”:

    20130810-163352.jpg
    Once you have selected an upload location, this is remembered in Textastic making the updating of files a breeze. For example, I often code, upload, run on Pi command line, get an error, go back to the code, update, upload and run.
    I also link each file with a Dropbox directory. An upload to here works as a backup but also allows easier access and debugging from my Ubuntu machine.

    WeMo Motion / IFTTT Bugs

    Just bought the WeMo Switch and Motion sensor.

    Setup was fine via app.

    Next step was connecting IFTTT. I did this via the “…More” button on the app. Click on “Connect to IFTTT” option. A pin is generated. It took me a couple of attempts to realise you can click on the button under the pin to log in to IFTTT to activate the devices – if you do this the pin is automatically copied across.

    The switch activated without problems on IFTTT. However, when I tried the Motion IFTTT said the pin was not valid (invalid pin message in a red box). Generating different pins did not work, neither did a clear or a factory reset of the Motion (via “Edit”) in the app.

    I tried deactivating, then deleting the Switch. This did something as the app then said it could not generate a pin. I then deleted and reinstalled the app. This time a pin was generated and the Motion connected.

    I then performed a factory reset on the Switch (hold button on top and plug in). Then added this via the app. The first attempt to connect to IFTTT failed but the second time worked.

    Success! Both Switch & Motion connected to IFTTT.