MariaDB | 3l3c7r1c pl4y6r0und

Tracking Motion Level using Raspberry PI and MATLAB/SIMULINK

Some days ago I have already confessed to be somehow fascinated of the Netflix Socks. Every evening I have to search for the episode (actually I look “The Man in the High Castle” based on the novel from Philip K. Dick) and the time I fell asleep. So something like the Netflix Socks was ideal but I have some major doubts.

First of all I have no problem with cold feet. So I rather take my socks off. Then there is the issue with the battery. I really don’t want wo have another wearable I have to take care about. And last but not least I often watch movies with my wife. And to be honest I’m not even a Netflix customer. For me is Amazon Prime in combination with a Fire TV Stick the better choice but that is another story. So I had the idea to make something video based. I’m not sure if this will work, because if it would be so easy, I’m sure my Samsung TV would have such function. But I want to give it a try and start with a little feasibility study.

The idea of all the sleep tracking is to measure movements. Wearables to this with acceleration sensors. I want to do this with a Raspberry PI and the Raspberry PI Cam. I will use MATLAB/SIMULINK for programming. MATLAB/SIMULINK is a rather more a platform than a programming language and in my opinion predestined for engineers. They follow a common marketing approach and sell it cheap for students and make expensive (really expensive) for professionals. Fortunately there is a Home Edition since some years. When I found it I was a bit disappointed because it is still rather expensive for private persons (for the basis and some toolboxes you pay already more than 200$) But I really like the system and as they support Raspberry, Arduino, Lego Mindstorms, and other platforms and therefore it is a nice playground for makers like me.

After following the installation procedure of the development software itself you have to get the Hardware Support Package for Raspberry. A wizard will lead you through the installation process and at the end you will have SD card with Raspbian and the MATLAB software. In my case the network setup afterwards didn’t work so I had to connect a monitor, keyboard and mouse and make the network setting manually. For programming you have to use SIMULINK to run your software permanently on the Raspberry. I will not explain how to use the software. They have tons of tutorials on their website and in the meanwhile you find a lot of resources in the internet. Before you can use the Raspberry PI Cam you have to follow a few steps.

The SIMULINK model is really easy. I just connect the camera with “Sum of absolute metrics” directly and through a delay block. Then make a small type conversion and put it to a UDP socket. Additionally I connect an Image Viewer to align the camera. For testing I use a socket server I’ve downloaded from the internet.

For data acquisition I use the server of my irrigation project. I create a table tblmotion to store my measuring values.

CREATE TABLE `tblmotion` (
  `dt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `motionvalue` double DEFAULT NULL,
  `byte0` int(11) DEFAULT NULL,
  `byte1` int(11) DEFAULT NULL,
  `byte2` int(11) DEFAULT NULL,
  `byte3` int(11) DEFAULT NULL,
  PRIMARY KEY (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tblmotion` (

`dt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),

`motionvalue` double DEFAULT NULL,

`byte0` int(11) DEFAULT NULL,

`byte1` int(11) DEFAULT NULL,

`byte2` int(11) DEFAULT NULL,

`byte3` int(11) DEFAULT NULL,

PRIMARY KEY (`dt`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Beside the motion measurment value I also store the raw bytes coming from my model because it was late in the evening when I did this and so I was not sure if my type conversion was working. I didn’t want to loose the data from the trial and with raw data I could have corrected the type conversion afterwards if it would have failed.

As socket server I use a PHP script. To run a PHP script in the Raspberry shell you have to install the PHP-CLI.

<?php
    $servername = "server";
    $username = "user";
    $password = "password";
    $dbname = "templogg";
    $conn = new mysqli($servername, $username, $password, $dbname);
    $socket = socket_create(AF_INET, SOCK_DGRAM, SOL_UDP);
    if (!$socket) die('Unable to create socket');
    if (!socket_bind($socket, '10.0.0.3', 10004)) die('Unable to bind socket');
     $data = '';
    echo 'reading.';
    while(true) {
        $data = @socket_read($socket, 15);
        $val=strval((ord($data[0])*1)+  (ord($data[1])*256) + (ord($data[2])*65536) + (ord($data[3])*16777216));
        echo $val."\r\n";
        $sql = "INSERT INTO tblmotion (motionvalue, byte0, byte1, byte2, byte3) VALUES (".$val.",".ord($data[0]).",".ord($data[1]).",".ord($data[2]).",".ord($data[3]).")";
        $conn->query($sql);
    }
    $conn->close();
    socket_close($socket);
?>

<?php

$servername = "server";

$username = "user";

$password = "password";

$dbname = "templogg";

$conn = new mysqli($servername, $username, $password, $dbname);

$socket = socket_create(AF_INET, SOCK_DGRAM, SOL_UDP);

if (!$socket) die('Unable to create socket');

if (!socket_bind($socket, '10.0.0.3', 10004)) die('Unable to bind socket');

$data = '';

echo 'reading.';

while(true) {

$data = @socket_read($socket, 15);

$val=strval((ord($data[0])*1)+ (ord($data[1])*256) + (ord($data[2])*65536) + (ord($data[3])*16777216));

echo $val."\r\n";

$sql = "INSERT INTO tblmotion (motionvalue, byte0, byte1, byte2, byte3) VALUES (".$val.",".ord($data[0]).",".ord($data[1]).",".ord($data[2]).",".ord($data[3]).")";

$conn->query($sql);

}

$conn->close();

socket_close($socket);

After having a nap in front of the TV I did the data analysis the next day with MATLAB. I did not invest in the database toolbox so I just query the data, export it to CSV and import it in MATLAB. I’ wearing a Garmin Vivosmart so I knew I felt asleep approx. 01:00. Looking at the data there seems to be a trend in the motion value.

So I do a bit of data analytics. I build some statistic values (STDDEV, AVG, VARIANCE) over different periods (1min, 5min, 10min, 20min) in SQL and import the data in MATLAB

%% Analyzing Motion Data - Electric Playground
%% Initialize
clear;
clc;
%% Data Import
% Raw Data
filename = 'C:\Temp\Bewegungstest.csv';
delimiter = ',';
startRow = 2;
formatSpec = '%{yyyy-MM-dd HH:mm:ss}D%f%f%f%f%f%[^\n\r]';
fileID = fopen(filename,'r');
dataArray = textscan(fileID, formatSpec, 'Delimiter', delimiter, 'EmptyValue' ,NaN,'HeaderLines' ,startRow-1, 'ReturnOnError', false);
fclose(fileID);
dt = dataArray{:, 1};
motionvalue = dataArray{:, 2};
byte0 = dataArray{:, 3};
byte1 = dataArray{:, 4};
byte2 = dataArray{:, 5};
byte3 = dataArray{:, 6};
clearvars filename delimiter startRow formatSpec fileID dataArray ans;

% Aggregated Data 1 min
filename = 'C:\Temp\Bewegungstest_agg1min.csv';
delimiter = ',';
startRow = 2;
formatSpec = '%{yyyy-MM-dd HH:mm:ss}D%f%f%f%f%[^\n\r]';
fileID = fopen(filename,'r');
dataArray = textscan(fileID, formatSpec, 'Delimiter', delimiter, 'HeaderLines' ,startRow-1, 'ReturnOnError', false);
fclose(fileID);
dt1min = dataArray{:, 1};
avg_motion1min = dataArray{:, 2};
std_motion1min = dataArray{:, 3};
var_motion1min = dataArray{:, 4};
clearvars filename delimiter startRow formatSpec fileID dataArray ans;

% Aggregated Data 5 min
filename = 'C:\Temp\Bewegungstest_agg5min.csv';
delimiter = ',';
startRow = 2;
formatSpec = '%{yyyy-MM-dd HH:mm:ss}D%f%f%f%f%[^\n\r]';
fileID = fopen(filename,'r');
dataArray = textscan(fileID, formatSpec, 'Delimiter', delimiter, 'HeaderLines' ,startRow-1, 'ReturnOnError', false);
fclose(fileID);
dt5min = dataArray{:, 1};
avg_motion5min = dataArray{:, 2};
std_motion5min = dataArray{:, 3};
var_motion5min = dataArray{:, 4};
clearvars filename delimiter startRow formatSpec fileID dataArray ans;

% Aggregated Data 10 min
filename = 'C:\Temp\Bewegungstest_agg10min.csv';
delimiter = ',';
startRow = 2;
formatSpec = '%{yyyy-MM-dd HH:mm:ss}D%f%f%f%f%[^\n\r]';
fileID = fopen(filename,'r');
dataArray = textscan(fileID, formatSpec, 'Delimiter', delimiter, 'HeaderLines' ,startRow-1, 'ReturnOnError', false);
fclose(fileID);
dt10min = dataArray{:, 1};
avg_motion10min = dataArray{:, 2};
std_motion10min = dataArray{:, 3};
var_motion10min = dataArray{:, 4};
clearvars filename delimiter startRow formatSpec fileID dataArray ans;
% Aggregated Data 20 min
filename = 'C:\Temp\Bewegungstest_agg20min.csv';
delimiter = ',';
startRow = 2;
formatSpec = '%{yyyy-MM-dd HH:mm:ss}D%f%f%f%f%[^\n\r]';
fileID = fopen(filename,'r');
dataArray = textscan(fileID, formatSpec, 'Delimiter', delimiter, 'HeaderLines' ,startRow-1, 'ReturnOnError', false);
fclose(fileID);
dt20min = dataArray{:, 1};
avg_motion20min = dataArray{:, 2};
std_motion20min = dataArray{:, 3};
var_motion20min = dataArray{:, 4};
clearvars filename delimiter startRow formatSpec fileID dataArray ans;

%% plot data

subplot(3,1,1);
plot(dt1min,avg_motion1min,dt5min,avg_motion5min,dt10min,avg_motion10min,dt20min,avg_motion20min);
title('Average');
ylim([5.5E5, 7E5]);

subplot(3,1,2);
plot(dt1min,std_motion1min,dt5min,std_motion5min,dt10min,std_motion10min,dt20min,std_motion20min);
title('Standard Deviation');
ylim([0, 1E5]);

subplot(3,1,3);
plot(dt1min,var_motion1min,dt5min,var_motion5min,dt10min,var_motion10min,dt20min,var_motion20min);
title ('Variance');
ylim([0, 1E10]);

100

101

%% Analyzing Motion Data - Electric Playground

%% Initialize

clear;

clc;

%% Data Import

% Raw Data

filename = 'C:\Temp\Bewegungstest.csv';

delimiter = ',';

startRow = 2;

formatSpec = '%{yyyy-MM-dd HH:mm:ss}D%f%f%f%f%f%[^\n\r]';

fileID = fopen(filename,'r');

dataArray = textscan(fileID, formatSpec, 'Delimiter', delimiter, 'EmptyValue' ,NaN,'HeaderLines' ,startRow-1, 'ReturnOnError', false);

fclose(fileID);

dt = dataArray{:, 1};

motionvalue = dataArray{:, 2};

byte0 = dataArray{:, 3};

byte1 = dataArray{:, 4};

byte2 = dataArray{:, 5};

byte3 = dataArray{:, 6};

clearvars filename delimiter startRow formatSpec fileID dataArray ans;

% Aggregated Data 1 min

filename = 'C:\Temp\Bewegungstest_agg1min.csv';

delimiter = ',';

startRow = 2;

formatSpec = '%{yyyy-MM-dd HH:mm:ss}D%f%f%f%f%[^\n\r]';

fileID = fopen(filename,'r');

dataArray = textscan(fileID, formatSpec, 'Delimiter', delimiter, 'HeaderLines' ,startRow-1, 'ReturnOnError', false);

fclose(fileID);

dt1min = dataArray{:, 1};

avg_motion1min = dataArray{:, 2};

std_motion1min = dataArray{:, 3};

var_motion1min = dataArray{:, 4};

clearvars filename delimiter startRow formatSpec fileID dataArray ans;

% Aggregated Data 5 min

filename = 'C:\Temp\Bewegungstest_agg5min.csv';

delimiter = ',';

startRow = 2;

formatSpec = '%{yyyy-MM-dd HH:mm:ss}D%f%f%f%f%[^\n\r]';

fileID = fopen(filename,'r');

dataArray = textscan(fileID, formatSpec, 'Delimiter', delimiter, 'HeaderLines' ,startRow-1, 'ReturnOnError', false);

fclose(fileID);

dt5min = dataArray{:, 1};

avg_motion5min = dataArray{:, 2};

std_motion5min = dataArray{:, 3};

var_motion5min = dataArray{:, 4};

clearvars filename delimiter startRow formatSpec fileID dataArray ans;

% Aggregated Data 10 min

filename = 'C:\Temp\Bewegungstest_agg10min.csv';

delimiter = ',';

startRow = 2;

formatSpec = '%{yyyy-MM-dd HH:mm:ss}D%f%f%f%f%[^\n\r]';

fileID = fopen(filename,'r');

dataArray = textscan(fileID, formatSpec, 'Delimiter', delimiter, 'HeaderLines' ,startRow-1, 'ReturnOnError', false);

fclose(fileID);

dt10min = dataArray{:, 1};

avg_motion10min = dataArray{:, 2};

std_motion10min = dataArray{:, 3};

var_motion10min = dataArray{:, 4};

clearvars filename delimiter startRow formatSpec fileID dataArray ans;

% Aggregated Data 20 min

filename = 'C:\Temp\Bewegungstest_agg20min.csv';

delimiter = ',';

startRow = 2;

formatSpec = '%{yyyy-MM-dd HH:mm:ss}D%f%f%f%f%[^\n\r]';

fileID = fopen(filename,'r');

dataArray = textscan(fileID, formatSpec, 'Delimiter', delimiter, 'HeaderLines' ,startRow-1, 'ReturnOnError', false);

fclose(fileID);

dt20min = dataArray{:, 1};

avg_motion20min = dataArray{:, 2};

std_motion20min = dataArray{:, 3};

var_motion20min = dataArray{:, 4};

clearvars filename delimiter startRow formatSpec fileID dataArray ans;

%% plot data

subplot(3,1,1);

plot(dt1min,avg_motion1min,dt5min,avg_motion5min,dt10min,avg_motion10min,dt20min,avg_motion20min);

title('Average');

ylim([5.5E5, 7E5]);

subplot(3,1,2);

plot(dt1min,std_motion1min,dt5min,std_motion5min,dt10min,std_motion10min,dt20min,std_motion20min);

title('Standard Deviation');

ylim([0, 1E5]);

subplot(3,1,3);

plot(dt1min,var_motion1min,dt5min,var_motion5min,dt10min,var_motion10min,dt20min,var_motion20min);

title ('Variance');

ylim([0, 1E10]);

The result makes me quite confident.

The data show a significant change in the movement value between 00:30 and 01:00. So the challenge will be to put this information in a reliable algorithm. Most properly I will have to get the neuronal network toolbox soon to make some trials.

Stay tuned!

Irrigation – Part 6: Fuzzy Control

The easiest way to control automatic irrigation would be using the soil moisture as threshold value and start a pump with that. But I don’t think that this is a suitable approach to control watering of plants. The measuring value coming from the soil probe is rather more a reference point than a value to be used for a controller. I also want to implement a real controller keeping my soil moisture rather constant because I think that will lead to a better yield. The standard controller everybody knows is the PI-Controller. It is an ideal solution for many situations but in my case the disadvantage is that it can only process one measuring value. For sure there are some ways to deal with additional values such as feedforward or cascading but I won’t do that. I will use a Fuzzy Controller instead.

The Fuzzy Controller is a multi-input-multi-output (MIMO) control algorithm working with indistinct (fuzzy) definitions. That sounds very creative but the history goes back to 1965. In the 1990s there was a real hype about Fuzzy Control but then this concept sank into oblivion. In my opinion this control concept is still quite underestimated and unknown today and that’s very unfortunate because it deals well with the most common problems in controller design: no exact model and inly empirical knowledge about the needed algorithm.

I want to use our existing webserver so I will implement the controller in PHP. I have found a very nice class from Wojtek Jarzecji. The idea is that later on an Arduino will call the PHP script. The script will return a change request for the water setpoint (-10%…+10%). The Arduino will add this to the actual pump setpoint (0….100%) and transfer it to a pwm signal.

For the parameters I do some measurements in wet (~400) and in dry soil (~800). Google tells me that in my hometown average humidity is between 50% and 90% and temperature in summer times is between 8°C and 36°C. I take this values to define my input membership functions as follows:

That’s my script:

<!doctype html>
<!--?php
    require_once ('./fuzzy-logic-class.php');
    
    /*----------------Aktuelle Messwerte aus Datenbank anfragen----*/
    // <span class="hiddenSpellError" pre="" data-mce-bogus="1"-->MariaDB Server
    $servername = "IP-SERVER";
    $username = "USER";
    $password = "PASSWORD";
    $dbname = "templogg";

    // Create connection
    $conn = new mysqli($servername, $username, $password, $dbname);
    // Check connection
    if ($conn->connect_error) {
    	die("Connection failed: " . $conn->connect_error);
    }

    // Actual Values
    $sql = "SELECT `temp`,`humidity`,`soil` FROM `tbllogging` ORDER BY dt desc LIMIT 1;";
    $actual = $conn->query($sql);
    if ($actual->num_rows > 0) {
	// output data of each row
	while($row = $actual->fetch_assoc()) {
		$acttemp = number_format($row["temp"],0);
		$acthum = number_format($row["humidity"],0);
		$actsoil = number_format($row["soil"],0);
	}
    } else {
	echo "0 results";
    }

    /*----------------Fuzzy Parameters----*/
    $x = new Fuzzy_Logic();
    $x->clearMembers();
    /* ---------- set input members ---------*/
    $x->setInputNames(array('TEMP','HUMIDITY', 'SOIL'));
    $x->addMember($x->getInputName(0),'COLD',  0,  8, 25   ,LINFINITY);
    $x->addMember($x->getInputName(0),'WARM', 20, 35, 100  ,RINFINITY);
    $x->addMember($x->getInputName(1),'LOW', 0  ,40 ,60    ,LINFINITY);
    $x->addMember($x->getInputName(1),'HIGH',50 ,70 ,100   ,RINFINITY);
    $x->addMember($x->getInputName(2),'WET', 0,   400 ,650  ,LINFINITY);
    $x->addMember($x->getInputName(2),'DRY',550, 800 ,1025 ,RINFINITY);

    /* ---------- set output members ---------*/
    $x->setOutputNames(array('OUT'));
    $x->addMember($x->getOutputName(0),'LOWER',-10, -5 ,0 ,LINFINITY);
    $x->addMember($x->getOutputName(0),'RAISE',0, 5 ,10 ,RINFINITY);

    /* ---------- set rule table ------------ */
    $x->clearRules();
    $x->addRule('IF TEMP.WARM OR SOIL.DRY OR HUMIDITY.LOW THEN OUT.RAISE');
    $x->addRule('IF TEMP.COLD OR SOIL.WET OR HUMIDITY.HIGH THEN OUT.LOWER');


    /*---------- Get values from database and calculate output ----*/
    $x->SetRealInput('TEMP',	$acttemp);
    $x->SetRealInput('HUMIDITY' ,$acthum);
    $x->SetRealInput('SOIL' ,	$actsoil);
    $fuzzy_arr = $x->calcFuzzy();
    $Fuzzy = $fuzzy_arr['OUT'];
    echo $Fuzzy;
?>

<!doctype html>

<!--?php

require_once ('./fuzzy-logic-class.php');

/*----------------Aktuelle Messwerte aus Datenbank anfragen----*/

// <span class="hiddenSpellError" pre="" data-mce-bogus="1"-->MariaDB Server

$servername = "IP-SERVER";

$username = "USER";

$password = "PASSWORD";

$dbname = "templogg";

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// Actual Values

$sql = "SELECT `temp`,`humidity`,`soil` FROM `tbllogging` ORDER BY dt desc LIMIT 1;";

$actual = $conn->query($sql);

if ($actual->num_rows > 0) {

// output data of each row

while($row = $actual->fetch_assoc()) {

$acttemp = number_format($row["temp"],0);

$acthum = number_format($row["humidity"],0);

$actsoil = number_format($row["soil"],0);

}

} else {

echo "0 results";

}

/*----------------Fuzzy Parameters----*/

$x = new Fuzzy_Logic();

$x->clearMembers();

/* ---------- set input members ---------*/

$x->setInputNames(array('TEMP','HUMIDITY', 'SOIL'));

$x->addMember($x->getInputName(0),'COLD', 0, 8, 25 ,LINFINITY);

$x->addMember($x->getInputName(0),'WARM', 20, 35, 100 ,RINFINITY);

$x->addMember($x->getInputName(1),'LOW', 0 ,40 ,60 ,LINFINITY);

$x->addMember($x->getInputName(1),'HIGH',50 ,70 ,100 ,RINFINITY);

$x->addMember($x->getInputName(2),'WET', 0, 400 ,650 ,LINFINITY);

$x->addMember($x->getInputName(2),'DRY',550, 800 ,1025 ,RINFINITY);

/* ---------- set output members ---------*/

$x->setOutputNames(array('OUT'));

$x->addMember($x->getOutputName(0),'LOWER',-10, -5 ,0 ,LINFINITY);

$x->addMember($x->getOutputName(0),'RAISE',0, 5 ,10 ,RINFINITY);

/* ---------- set rule table ------------ */

$x->clearRules();

$x->addRule('IF TEMP.WARM OR SOIL.DRY OR HUMIDITY.LOW THEN OUT.RAISE');

$x->addRule('IF TEMP.COLD OR SOIL.WET OR HUMIDITY.HIGH THEN OUT.LOWER');

/*---------- Get values from database and calculate output ----*/

$x->SetRealInput('TEMP', $acttemp);

$x->SetRealInput('HUMIDITY' ,$acthum);

$x->SetRealInput('SOIL' , $actsoil);

$fuzzy_arr = $x->calcFuzzy();

$Fuzzy = $fuzzy_arr['OUT'];

echo $Fuzzy;

Irrigation – Part 4: Automatic Database Backup

I just went trough a Scottish shower of emotions. IT people know this very well. I had the plan to “just” activate the eth0 interface on my RaspberryPI to get if off my WLAN. Unfortunately that was not so easy. For a moment I was afraid I had to power it off. I previous cases this caused a damaged file system. I was lucky, there was no such problem but I realized that I don’t have any backup of my database at all.

I’m not that kind of guy that has several servers running 24/7 in his house so I decided to put the backup into my Dropbox. I create a folder called rpi2backup in my Dropbox and a folder /tmp/dbbackup/ on my Raspberry. For uploading my backup file I use the uploader from Andrea Faprizi. After installing the uploader I just follow the instruction of the installer. I don’t know exactly why but I had to do the procedure twice.

The next step is the MariaDB backup. I will not reinvent the wheel here so I just follow the tutorial here. I have modify the script in the tutorial a bit to upload the backup automatically after creation.

#!/bin/bash
OUTPUT_FILE=/tmp/dbbackup/templogg.bz2
DATABASE_NAME="templogg"

mysqldump --defaults-file=.my.cnf $DATABASE_NAME | bzip2 > $OUTPUT_FILE

/home/pi/dropbox/Dropbox-Uploader/dropbox_uploader.sh upload /tmp/dbbackup/templogg.bz2 /rpi2backup/templogg.bz2

#!/bin/bash

OUTPUT_FILE=/tmp/dbbackup/templogg.bz2

DATABASE_NAME="templogg"

mysqldump --defaults-file=.my.cnf $DATABASE_NAME | bzip2 > $OUTPUT_FILE

/home/pi/dropbox/Dropbox-Uploader/dropbox_uploader.sh upload /tmp/dbbackup/templogg.bz2 /rpi2backup/templogg.bz2

Now I will sleep better.

Irrigation– Part 3: Visualization

For sure I want to see the measuring values from by automatic plant irrigation. But it is a bit annoying to use my database tools all the time. That’s the reason for me to make a small visualisation. I want to see the actual values as text and two trends. One showing the last 24h and the other showing the last week.

I have already my webserver so the drug of choice for me is making a website. But that’s not the only reason. It is becoming state of the art to make front-ends with web technologies. The advantages are obvious, simple deployment, platform independent (especially mobile devices). Furthermore web technologies have gained power over the last years (e.g. web socket, HTML5, SVG). Also rendering performance improved enough to render trends fluidly.

I always develop and test queries separately in Workbench IDE. For a better readability in my trends I compress the data a bit. The 24h data shows the average per hour. The data for the last week shows the average per quarter day. The value for soil moisture is scaled 1024 = 100%.

# Actual Values
SELECT DATE_FORMAT(`dt`, '%H:%i') as `dtshort`,`temp`,`humidity`,`soil`
	FROM `tbllogging`
	ORDER BY dt desc LIMIT 1;

# last 24h (average per hour)
SELECT DATE_FORMAT(`dt`, '%H:%i') `dtshort`, AVG(`temp`) `temp`, AVG(`humidity`) `humidity`, (AVG(`soil`)/1024)*100 `soil` 
	FROM `tbllogging` 
	WHERE TIMESTAMPDIFF(HOUR,`dt`, NOW()) <= 24  
	GROUP BY DATE_FORMAT(`dt`, '%Y-%m-%d %H') ORDER BY dt asc;
    
# last week (average per quarter day)
SELECT DATE_FORMAT(MIN(`dt`), '%a %H:00') as `dtshort`, AVG(`temp`) `temp`, AVG(`humidity`) `humidity`, (AVG(`soil`)/1024)*100 `soil`
	FROM `tbllogging`
	WHERE TIMESTAMPDIFF(HOUR,`dt`, NOW()) <= 168 
	GROUP BY DATE_FORMAT(`dt`, '%Y-%m-%d'),FLOOR(EXTRACT(HOUR FROM `dt`)/6)
	ORDER BY dt asc;s

# Actual Values

SELECT DATE_FORMAT(`dt`, '%H:%i') as `dtshort`,`temp`,`humidity`,`soil`

FROM `tbllogging`

ORDER BY dt desc LIMIT 1;

# last 24h (average per hour)

SELECT DATE_FORMAT(`dt`, '%H:%i') `dtshort`, AVG(`temp`) `temp`, AVG(`humidity`) `humidity`, (AVG(`soil`)/1024)*100 `soil`

FROM `tbllogging`

WHERE TIMESTAMPDIFF(HOUR,`dt`, NOW()) <= 24

GROUP BY DATE_FORMAT(`dt`, '%Y-%m-%d %H') ORDER BY dt asc;

# last week (average per quarter day)

SELECT DATE_FORMAT(MIN(`dt`), '%a %H:00') as `dtshort`, AVG(`temp`) `temp`, AVG(`humidity`) `humidity`, (AVG(`soil`)/1024)*100 `soil`

FROM `tbllogging`

WHERE TIMESTAMPDIFF(HOUR,`dt`, NOW()) <= 168

GROUP BY DATE_FORMAT(`dt`, '%Y-%m-%d'),FLOOR(EXTRACT(HOUR FROM `dt`)/6)

ORDER BY dt asc;s

Usually I would put the queries in stored procedures and use them as they offer some advantages but unfortunately the mysqli does not support stored procedures returning multiple row sets. Therefore you find the queries hard-coded in the PHP script.

For displaying the trend data I use a 3rd party charting library. I use Chart.js in this case. It is for sure not the most powerful library but it is slim, fast, free and open source (MIT license).

That’s the code for the website

<!doctype html>
<?php
	ini_set('display_errors', 1); 
	error_reporting(E_ALL);

	// MariaDB Server
	$servername = "serverip";
	$username = "dbuser";
	$password = "dbpassword";
	$dbname = "templogg";

	// Create connection
	$conn = new mysqli($servername, $username, $password, $dbname);
	// Check connection
	if ($conn->connect_error) {
		die("Connection failed: " . $conn->connect_error);
	}

	// actual
	$sql = "SELECT DATE_FORMAT(`dt`, '%H:%i') as `dtshort`,`temp`,`humidity`,`soil` FROM `tbllogging` ORDER BY dt desc LIMIT 1;";
	$actual = $conn->query($sql);
	if ($actual->num_rows > 0) {
		// output data of each row
		while($row = $actual->fetch_assoc()) {
			$actdt = $row["dtshort"];
			$acttemp = number_format($row["temp"],0);
			$acthum = number_format($row["humidity"],0);
			$actsoil = number_format($row["soil"],0);
		}
	} else {
		echo "0 results";
	}

	// last 24h
	$sqlday = "SELECT DATE_FORMAT(`dt`, '%H:%i') `dtshort`, AVG(`temp`) `temp`, AVG(`humidity`) `humidity`, (AVG(`soil`)/1024)*100 `soil` FROM `tbllogging`  WHERE TIMESTAMPDIFF(HOUR,`dt`, NOW()) <= 24 GROUP BY DATE_FORMAT(`dt`, '%Y-%m-%d %H') ORDER BY dt asc;";
	$daydt="";
	$daytemp="";
	$dayhum="";
	$daysoil="";
	$dayresult = $conn->query($sqlday);
	if($dayresult->num_rows > 0) 
	{
		while($row = mysqli_fetch_array($dayresult, MYSQLI_ASSOC))
		{
			$daydt.='"'.$row["dtshort"].'",';
			$daytemp.=$row["temp"].",";
			$dayhum.=$row["humidity"].",";
			$daysoil.=$row["soil"].",";
		}
	} else {
		echo "0 results";
	}



	// last week
	$sqlweek = "SELECT DATE_FORMAT(MIN(`dt`), '%a %H:00') as `dtshort`, AVG(`temp`) `temp`, AVG(`humidity`) `humidity`, (AVG(`soil`)/1024)*100 `soil` FROM `tbllogging` WHERE TIMESTAMPDIFF(HOUR,`dt`, NOW()) <= 168  GROUP BY DATE_FORMAT(`dt`, '%Y-%m-%d'),FLOOR(EXTRACT(HOUR FROM `dt`)/6) ORDER BY dt asc;";
	$weekdt="";
	$weektemp="";
	$weekhum="";
	$weeksoil="";
	$weekresult = $conn->query($sqlweek);
	if($weekresult->num_rows > 0) 
	{
		while($row = mysqli_fetch_array($weekresult, MYSQLI_ASSOC))
		{
			$weekdt.='"'.$row["dtshort"].'",';
			$weektemp.=$row["temp"].",";
			$weekhum.=$row["humidity"].",";
			$weeksoil.=$row["soil"].",";
		}
	} else {
		echo "0 results";
	}

	// Verbidnung schließen
	$conn->close();
?>
<html>
	<head>
		<title>Temp+Humidity Logger v1</title>
		<meta charset="utf-8" />
		<meta name = "viewport" content ="width = 600">
		<style type="text/css">
			h1 {color:black;font-size: 350%;margin:0;}
			h2 {color:black;font-size: 150%;maring:0;}
			h3 {color:black;font-size: 80%;margin:0;}
		</style>
		<script type="text/javascript" src="Chart.min.js"></script>
	</head>
	<body>
		<center>
			<table border=0 width=600>
				<?php
						echo '<tr><th width=100><h2>' .$actdt.' </h2></th><th><h1>' . $acttemp. ' &degC</h1></th><th width=120><h2>Luft: ' . $acthum. "%<br>Soil: ".$actsoil."</h2></th></tr>";
				?> 
			</table>
			<hr>
			<span class="h3">last 24h</span><br>
			<canvas id="DayChart" width="580" height="300"></canvas><br>
			<hr>
			<span class="h3">last week</span><br>
			<canvas id="WeekChart" width="580" height="300"></canvas><br>
			<table  border=0 width=600>
				<tr><th style="background-color:rgba(150,150,255,0.6)">Luftfeuchte</th></tr>
				<tr><th style="background-color:rgba(0,0,130,0.6)">Temperatur</th></tr>
				<tr><th style="background-color:rgba(172,194,132,0.6)">Boden</th></tr>
			</table>
			<script type="text/javascript">
				// Global Chart definition
				Chart.defaults.global.scaleBeginAtZero=true;
				Chart.defaults.global.showTooltips=false;
				Chart.defaults.global.scaleOverride= true;
				Chart.defaults.global.scaleSteps=10;
				Chart.defaults.global.scaleStepWidth=5;
				Chart.defaults.global.scaleStartValue=10;
				// Day Chart
				var DayChartData = {
					labels : [<?php
							echo $daydt;
							?>],
					datasets : [
						{
							label: "Humidity",
							fillColor : "rgba(150,150,255,0.4)",
							strokeColor : "#9999FF",
							pointDot : false,
							bezierCurve : false,
							data : [<?php
									echo $dayhum;
									?>]
						},
						{
							label: "Soil",
							fillColor : "rgba(172,194,132,0.4)",
							strokeColor : "#ACC26D",
							pointDot : false,
							bezierCurve : false,
							data : [<?php
									echo $daysoil;
									?>]
						},
						{
							label: "Temp",
							fillColor : "rgba(0,0,130,0.4)",
							strokeColor : "#0000AA",
							pointDot : false,
							bezierCurve : false,
							data : [<?php
									echo $daytemp;
									?>]
						}			
					]
				}
				var day = document.getElementById("DayChart").getContext("2d");
				new Chart(day).Line(DayChartData);
				
				// Week Chart
				var DayChartData = {
					labels : [<?php 
							echo $weekdt;
							?>],
					datasets : [
						{
							label: "Humidity",
							fillColor : "rgba(150,150,255,0.4)",
							strokeColor : "#9999FF",
							pointDot : false,
							bezierCurve : false,
							data : [<?php
									echo $weekhum;
									?>]
						},
						{
							label: "Soil",
							fillColor : "rgba(172,194,132,0.4)",
							strokeColor : "#ACC26D",
							pointDot : false,
							bezierCurve : false,
							data : [<?php
									echo $weeksoil;
									?>]
						},
						{
							label: "Temp",
							fillColor : "rgba(0,0,130,0.4)",
							strokeColor : "#0000AA",
							pointDot : false,
							bezierCurve : false,
							data : [<?php
									echo $weektemp;
									?>]
						}			
					]
				}
				var week = document.getElementById("WeekChart").getContext("2d");
				var weekchart = new Chart(week).Line(DayChartData);
			</script>
		</center>
	</body>
</html>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

<!doctype html>

<?php

ini_set('display_errors', 1);

error_reporting(E_ALL);

// MariaDB Server

$servername = "serverip";

$username = "dbuser";

$password = "dbpassword";

$dbname = "templogg";

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

// actual

$sql = "SELECT DATE_FORMAT(`dt`, '%H:%i') as `dtshort`,`temp`,`humidity`,`soil` FROM `tbllogging` ORDER BY dt desc LIMIT 1;";

$actual = $conn->query($sql);

if ($actual->num_rows > 0) {

// output data of each row

while($row = $actual->fetch_assoc()) {

$actdt = $row["dtshort"];

$acttemp = number_format($row["temp"],0);

$acthum = number_format($row["humidity"],0);

$actsoil = number_format($row["soil"],0);

}

} else {

echo "0 results";

}

// last 24h

$sqlday = "SELECT DATE_FORMAT(`dt`, '%H:%i') `dtshort`, AVG(`temp`) `temp`, AVG(`humidity`) `humidity`, (AVG(`soil`)/1024)*100 `soil` FROM `tbllogging` WHERE TIMESTAMPDIFF(HOUR,`dt`, NOW()) <= 24 GROUP BY DATE_FORMAT(`dt`, '%Y-%m-%d %H') ORDER BY dt asc;";

$daydt="";

$daytemp="";

$dayhum="";

$daysoil="";

$dayresult = $conn->query($sqlday);

if($dayresult->num_rows > 0)

{

while($row = mysqli_fetch_array($dayresult, MYSQLI_ASSOC))

{

$daydt.='"'.$row["dtshort"].'",';

$daytemp.=$row["temp"].",";

$dayhum.=$row["humidity"].",";

$daysoil.=$row["soil"].",";

}

} else {

echo "0 results";

}

// last week

$sqlweek = "SELECT DATE_FORMAT(MIN(`dt`), '%a %H:00') as `dtshort`, AVG(`temp`) `temp`, AVG(`humidity`) `humidity`, (AVG(`soil`)/1024)*100 `soil` FROM `tbllogging` WHERE TIMESTAMPDIFF(HOUR,`dt`, NOW()) <= 168 GROUP BY DATE_FORMAT(`dt`, '%Y-%m-%d'),FLOOR(EXTRACT(HOUR FROM `dt`)/6) ORDER BY dt asc;";

$weekdt="";

$weektemp="";

$weekhum="";

$weeksoil="";

$weekresult = $conn->query($sqlweek);

if($weekresult->num_rows > 0)

{

while($row = mysqli_fetch_array($weekresult, MYSQLI_ASSOC))

{

$weekdt.='"'.$row["dtshort"].'",';

$weektemp.=$row["temp"].",";

$weekhum.=$row["humidity"].",";

$weeksoil.=$row["soil"].",";

}

} else {

echo "0 results";

}

// Verbidnung schließen

$conn->close();

<html>

<head>

<title>Temp+Humidity Logger v1</title>

h1 {color:black;font-size: 350%;margin:0;}

h2 {color:black;font-size: 150%;maring:0;}

h3 {color:black;font-size: 80%;margin:0;}

</style>

</head>

<body>

<?php

echo '<tr><th width=100><h2>' .$actdt.' </h2></th><th><h1>' . $acttemp. ' &degC</h1></th><th width=120><h2>Luft: ' . $acthum. "%<br>Soil: ".$actsoil."</h2></th></tr>";

</table>

<hr>

<hr>

<tr><th style="background-color:rgba(150,150,255,0.6)">Luftfeuchte</th></tr>

<tr><th style="background-color:rgba(0,0,130,0.6)">Temperatur</th></tr>

<tr><th style="background-color:rgba(172,194,132,0.6)">Boden</th></tr>

</table>

// Global Chart definition

Chart.defaults.global.scaleBeginAtZero=true;

Chart.defaults.global.showTooltips=false;

Chart.defaults.global.scaleOverride= true;

Chart.defaults.global.scaleSteps=10;

Chart.defaults.global.scaleStepWidth=5;

Chart.defaults.global.scaleStartValue=10;

// Day Chart

var DayChartData = {

labels : [<?php

echo $daydt;

?>],

datasets : [

{

label: "Humidity",

fillColor : "rgba(150,150,255,0.4)",

strokeColor : "#9999FF",

pointDot : false,

bezierCurve : false,

data : [<?php

echo $dayhum;

?>]

{

label: "Soil",

fillColor : "rgba(172,194,132,0.4)",

strokeColor : "#ACC26D",

pointDot : false,

bezierCurve : false,

data : [<?php

echo $daysoil;

?>]

{

label: "Temp",

fillColor : "rgba(0,0,130,0.4)",

strokeColor : "#0000AA",

pointDot : false,

bezierCurve : false,

data : [<?php

echo $daytemp;

?>]

}

]

}

var day = document.getElementById("DayChart").getContext("2d");

new Chart(day).Line(DayChartData);

// Week Chart

var DayChartData = {

labels : [<?php

echo $weekdt;

?>],

datasets : [

{

label: "Humidity",

fillColor : "rgba(150,150,255,0.4)",

strokeColor : "#9999FF",

pointDot : false,

bezierCurve : false,

data : [<?php

echo $weekhum;

?>]

{

label: "Soil",

fillColor : "rgba(172,194,132,0.4)",

strokeColor : "#ACC26D",

pointDot : false,

bezierCurve : false,

data : [<?php

echo $weeksoil;

?>]

{

label: "Temp",

fillColor : "rgba(0,0,130,0.4)",

strokeColor : "#0000AA",

pointDot : false,

bezierCurve : false,

data : [<?php

echo $weektemp;

?>]

}

]

}

var week = document.getElementById("WeekChart").getContext("2d");

var weekchart = new Chart(week).Line(DayChartData);

</script>

</center>

</body>

</html>

The result is a nice website that also works great on mobile devices.

Irrigation- Part 2: Data Recording

I use a Raspberry PI 2 as server for my data recording. Additionally you need a SD card, a power supply (I use just a USB cable to connect it to my access point) and if needed a WLAN stick. I strongly recommend to use a case e.g. like this one

First of all the Raspberry needs an operating system (raspbian) installed. There are plenty of manuals for that in the internet. As you have seen in the Arduino project I send my data using a web server. Writing a small socket server would have been the more lightweight solution but it is really a tough job to program a service that runs 24/7 stable and robust. Therefore I like to use some kind of a well-known and tested middleware. For data recording the combination of Apache webserver, PHP and MySQL (LAMP) is an ideal solution for me. There are many tutorials for setting up such a system in the internet (I used this one). Instead of using the “real” MySQL I use the open source successor MariaDB. I just replace

apt-get install apache2 apache2-utils php5 libapache2-mod-php5 php5-mysql mysql-server mysql-client phpmyadmin -y

apt-get install apache2 apache2-utils php5 libapache2-mod-php5 php5-mysql mariadb-server mysql-client phpmyadmin -y

MariaDB and MySQL are compatible on a wide scale. So you can use almost all the tools you use for MySQL also for MariaDB.

To improve my tool chain I also install a samba server on the Raspberry. You find a tutorial here. I develop on a Windows platform and the Samba server enables me to mount the wwwroot of the raspberry as network share. So I can simply develop on this share without taking a detour using FTP. For developing on the MariaDB you can use the installed PHPMyAdmin or you install the MySQL Workbench instead. The Workbench throws an error when connecting to a MariaDB but it’s working without any problems. It’s a matter of taste which way you prefer. Ich like the Workbench because I’m used to work in a real IDE for database development so the PHPMyAdmin feels a bit unfamiliar for me.

After setting up the Raspberry and the development tool chain you can create a database. I call my database simply “templogg”

CREATE DATABASE `templogg` /*!40100 DEFAULT CHARACTER SET latin1 */;

1	CREATE DATABASE `templogg` /!40100 DEFAULT CHARACTER SET latin1 /;

Then I create a table called “tbllogging”

CREATE TABLE `tbllogging` (
  `dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `temp` double DEFAULT NULL,
  `humidity` double DEFAULT NULL,
  `soil` double DEFAULT NULL,
  PRIMARY KEY (`dt`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `tbllogging` (

`dt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,

`temp` double DEFAULT NULL,

`humidity` double DEFAULT NULL,

`soil` double DEFAULT NULL,

PRIMARY KEY (`dt`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

The column “dt” is a datetime column and acts as my primary key. The columns temp, humidity and soil are data type real and take the measuring values. As you could see in the Arduino code, the PHP script, called “newrecord.php” is missing. I put the script directly in the wwwroot.

<?php
$servername = "raspberryIP";
$username = "root";
$password = "password";
$dbname = "templogg";

echo 'Temp: ' . htmlspecialchars($_GET["temp"]) . "!\r\n";
echo 'Humidity: ' . htmlspecialchars($_GET["humidity"]) . "!\r\n";
echo 'Soil: ' . htmlspecialchars($_GET["soil"]) . "!\r\n";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO tbllogging (temp, humidity, soil)
VALUES (".$_GET["temp"].", ".$_GET["humidity"].",".$_GET["soil"].")";
//echo $sql;
if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "
" . $conn->error;
}

$conn->close();
?>

<?php

$servername = "raspberryIP";

$username = "root";

$password = "password";

$dbname = "templogg";

echo 'Temp: ' . htmlspecialchars($_GET["temp"]) . "!\r\n";

echo 'Humidity: ' . htmlspecialchars($_GET["humidity"]) . "!\r\n";

echo 'Soil: ' . htmlspecialchars($_GET["soil"]) . "!\r\n";

// Create connection

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection

if ($conn->connect_error) {

die("Connection failed: " . $conn->connect_error);

}

$sql = "INSERT INTO tbllogging (temp, humidity, soil)

VALUES (".$_GET["temp"].", ".$_GET["humidity"].",".$_GET["soil"].")";

//echo $sql;

if ($conn->query($sql) === TRUE) {

echo "New record created successfully";

} else {

echo "Error: " . $sql . "

" . $conn->error;

}

$conn->close();

The script requires the MySQL extension in the php.ini.

The mysqli extension is not enabled by default, so the php_mysqli.dll DLL must be enabled inside of php.ini. In order to do this you need to find the php.ini file (typically located in c:\php), and make sure you remove the comment (semi-colon) from the start of the line extension=php_mysqli.dll, in the section marked [PHP_MYSQLI]. – http://php.net/manual/en/mysqli.installation.php

After that I try my script. I call the URL with some test data. You can do that with any browser.

http://raspberryip/newrecord.php?temp=33.00&humidity=44.00&soil=55.00

Then I check if the data has been recorded correctly.

SELECT * FROM tbllogging;

1	SELECT * FROM tbllogging;

When it works I delete the test data.

DELETE FROM tbllogging;

1	DELETE FROM tbllogging;

Now I start my Arduino and check if the recording works fine with that too. If that’s the case the prototype will do measurements on a houseplant for the next weeks.

In the meanwhile I will make a small website to display my measurements without using my database IDE.

Share this:

Share this:

Share this:

Share this:

Share this: