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

OCR Meter Readings using Raspberry Pi?

I have a wireless energy meter and thermostat at home. I could try to hack them, taking them apart and listening to certain key voltages. However, the circuits are likely small and breakable. And I would like to use the units again and not pay for replacements.

So I was wondering whether I could cheat and input data values using OCR from a webcam or camera. The Raspberry Pi would be well placed to do this. My thoughts so far for the process are set out below. I can probably tackle each independently.

  1. Place meters;
  2. Acquire image;
  3. OCR on image;
  4. Output of OCR to DB or file.

1. Place meters

  • Needs to be a set distance from acquisition device;
  • Mark out so can replicate even if need to take meters in and out;
  • Illumination for night time:
    • Low power (LED?)
    • Filter image when LED is lit?

2. Acquire image

  • Frame grab from webcam;
    • Need to get webcam working;
    • Need to learn command to acquire image;
  • Segment image for different data:
    • Set x,y area in image if meters are placed consistently;
      • Is there a command line tool for this?
    • Test with crop in iPhone/iPad;
    • Output image files for different areas – use these as input for OCR.

3. OCR on image

  • No obvious OCR tool on Raspberry Pi – keep looking;
  • Web services? Does Google/Tesseract have a web service? Use URL?
  • Did common sense check on output:
    • Values will be integer (input parameter for OCR)
    • Values will have decimal point;
  • Create own OCR tool?

4. Output of OCR to DB or file

  • MySQL DB?
  • Key field = time stamp (inc. seconds);
  • Other fields for each item of OCR data;
  • Or flat file, e.g. CSV, with {timestamp, data} tuple.