Smart meter readout – storing data

In a previous post, I set up my Raspberry Pi close to my electricity meter. I connected the P1 serial port to the Raspberry with a special serial to USB cable (it has the data bits inverted as far as I know). Then I created a script to look at the serial telegrams the meter sends out every ten seconds, and filter out the data I want, which for now is my live energy consumption from the grid.

Now I need to send these numbers to a web server, which will do the storing and presentation of the data. Later on I will have it control some devices.

I know some home automation devices will have the Raspberry be the core computing device, but I don’t like that, I want it to be at my hosting provider, being backed-up and all.

Code on the Raspberry

On the Raspberry’s script, I only need to add a few lines to send the data over to the web server. On the web server there is a file called record.php which will receive and store the data. I build up and URL that points to the .php file and add the numbers and my UUID in the parameter part of the URL.

Then, the request.get() function will actually surf to the URL specified.

        url = 'https://krakkus.com/record.php?uuid=krakkus&kwh={}'.format(power)
        print(url)
        
        try:
            r = requests.get(url)
            print(r)
        except:
            print('could not do web request')

On the webserver, record.php

Of course, I will need full error reporting at this time. And a database connection, I got the name and password when I created the database through PhpMyAdmin, which is in my hosting tools.

ini_set('display_errors', 1);
ini_set('display_startup_errors', 1);
error_reporting(E_ALL);

$con = mysqli_connect('localhost', '*******_domotica', '********');
mysqli_select_db($con, '*******_domotica');

Aside from creating the database by hand, I want to do all the rest by script. I learned from WordPress plugins that it is best to do version checking and upgrading through a version property in the database. But I do not have WordPress to do that for me. Therefore, I check for the existence of a settings table I intend to use for those things.

This is a bit of extra work to get started but once it is there, upgrades are easy.

// Select 1 from table_name will return false if the table does not exist.
$val = mysqli_query($con, 'select 1 from `settings` LIMIT 1');

if($val !== FALSE)
{
   //echo('Settings table exists');

This part is long and boring, it uses two queries to create two tables, one for settings as a key->value pair and a second one two track current energy consumption.

I also insert the initial version of the system in the settings table.

You might have noticed me also storing UUID’s on the server. This is to make it possible two host multiple houses/systems on a single web server.

	echo('Settings table NOT exists');
	$sql = "CREATE TABLE settings(
				name VARCHAR(255) NOT NULL,
				value VARCHAR(255) NOT NULL)";
				
	if(mysqli_query($con, $sql)){
		//echo 'succes';
	} else {
		echo 'ERROR: Could not able to execute $sql. ' . mysqli_error($con);
	}
	
	$sql = "INSERT INTO settings (name, value) VALUES ('version', '0.01')";
	
	if(mysqli_query($con, $sql)){	
		//echo 'succes'; 
	} else {
		echo 'ERROR: Could not able to execute $sql. ' . mysqli_error($con);
	}
	
	$sql = "CREATE TABLE kwh (
				uuid VARCHAR(64) NOT NULL,
				time VARCHAR(20) NOT NULL,
				kwh FLOAT NOT NULL, 
				KEY (uuid),
				KEY (time))";
	
	if(mysqli_query($con, $sql)){	
		//echo 'succes'; 
	} else {
		echo 'ERROR: Could not able to execute $sql. ' . mysqli_error($con);
	}

Now the tables are set up (if they weren’t already). I do a query for the current version of the system. On future upgrades, if this version from the database doesn’t match the version of the .php file. We can do an immediate upgrade.

$sql = "SELECT value FROM settings WHERE name = 'version'";
$result = mysqli_query($con, $sql);
$row = mysqli_fetch_array($result, MYSQLI_ASSOC);
$version = $row['value'];

/*********************************************************/

echo 'Current version is ' . $version . '<br>';

First part, extract the UUID and kWh from the URL using the $_GET global variable, and output this for debugging purpose.

Secondly, I declare a segment size two be 60 seconds. Doing this, every minute it will create a new data point. By dividing the current time in seconds by 60, it will give me the current time in minutes.

And finally, the SQL part first does a query to retrieve the current minute/segment it there is one. If there is one, it will be updates with the latest kWh number. If it is not there yet, create a new record with the same data.

$uuid = $_GET['uuid'];
$kwh = $_GET['kwh'];
//$kwhtotal = $_GET['kwhtotal'];

echo 'Current power consumption is ' . $kwh . ' kw';

$segment_size = 60;	// 60 seconds
$minute_time = intval(time() / $segment_size);

$sql = "SELECT * FROM kwh WHERE `time` = $minute_time";
$result = mysqli_query($con, $sql);

if (mysqli_num_rows($result) == 0) {
	$sql = "INSERT INTO kwh (`uuid`, `time`, `kwh`) VALUES ('$uuid', $minute_time, $kwh)";
	$result = mysqli_query($con, $sql);
} else {
	$sql = "UPDATE kwh SET `kwh` = $kwh WHERE `uuid`='$uuid' AND `time`=$minute_time";
	$result = mysqli_query($con, $sql);
}

Now that this script is running, the database is being filled, and I can create a simple consumption graph using PhpMyAdmin at my hosting.

Simple energy consumption graph using PhpMyAdmin.

Whats next

In a future post I will make me a simple automatically refreshing dashboard on the web server.

Leave a Reply

Your email address will not be published. Required fields are marked *