for advanced…
A solar powered, ESP8266 based sensor unit for my automatic irrigation system.
Having my working prototype I did some improvements in the software. It allows not the operation of multiple sensors on one server. I also beautified the hardware a bit. Otherwise my wife could think Borg had assimilated our tomatoes. You can find the code on my Github Repo .
A solar powered, ESP8266 based sensor unit for my automatic irrigation system.
Finally all parts arrived and my irrigation sensor is now ready. I took my ESP8266 Breakout Board as basis. During soldering works I realized that there are some details missing in my breakout design (mainly missing designations). Therefore I have updated its design as well (Github). Using the XCSOURCE FT232RL 3.3V 5.5V FTDI USB zu TTL Serielles Adaptermodul Arduino Mini Anschluss TE203 and the soldering jumper there is no need for the “PROG” button. It is handled by the converter it self. I use the same sonsors I have used for the prototype (MTS1EU Greenhouse Sensor Kit Soil Hygrometer Module and DHT11 Temperature/Humidity Module for Arduino. Gewächshaus Pflazen .)
I use a small 5V / 81mA solar panel from Conrad. The panel drives very little current so I don’t need to care about charging logic. A simple Zener diode (BZX85C3V9) should be enough. I also changed the transistor to a PNP type as some of my readers recommended. Also the GPIOs I use have changed due to layout reasons. I have put the latest version of Fritzing and code on my Github.
So thats what the result looks like.
When I started layouting the PCB for my irrigation sensor I realized the advantages of a breakout board. They are handy for developing, sure. But they also bear the big advantage that you do not have to take care about repetitive tasks (such as pull-up resistors). Additionally they ease up layouting by adding two additional layers to your design. That simplifies the design and it also minimizes the footprint of the final PCB.
When I started layouting the PCB for my irrigation sensor I realized the advantages of a breakout board. They are handy for developing, sure. But they also bear the big advantage that you do not have to take care about repetitive tasks (such as pull-up resistors). Additionally they ease up layouting by adding two additional layers to your design. That simplifies the design and it also minimizes the footprint of the final PCB.
Size was the most important factor for me. It isn’t just a question of space, it is also a question of price (PCB cost ist based on cm²). Beside the pull up resistors for CH_PD, GPIO_0, GPIO_2 and RESET and the buttons to ground RESET and GPIO_0 (PROG) I have also foreseen a pin header for connecting my XCSOURCE FT232RL USB<->TTL. The voltage supply of the USB-TTL converter can be enabled or disabled with a jumper. Additionally I have foreseen solder jumpers CTS->REST and DTR->GPIO_0. I’ve not tried personally but some sources say that this connections make the manual buttons obsolete by handing over this task to the USB-TTL converter. Finally there is also a solder jumper to connect GPIO_16 and RESET (for wake up after deep sleep) and a capacitor as voltage stabilizer. Other boards like the Huzzah Board use a voltage converter for this but I have made good experience with the capacitor.
My followers on twitter know that it took several attempts before I got my final version ready (special thanks here to @ccxx72, @i_grr, @bdcatalin und @tzapulica for their help). I also faced some troubles with Fritzing but finally you can download my result at Github: https://github.com/Stromspielplatz/misc/tree/master/ESP8266%20ESP-12%20Breakout
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.
|
1 2 3 4 5 6 7 8 9 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<?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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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!
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 |
<!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; ?> |
Having my first results in hand I have to modify my software a bit. The DHT11 sometimes give strange measuring values (0°C, 6°C) and I want to change my measuring procedure. I will measure 3 values for temperature and humidity and I will take the middle values. The soil moisture measurement is quite unstable so I will take the average of three measurements.
Additionally I have cleaned up the code structure.
I had troubles with two issues:
That’s the result:
|
1 2 3 4 5 6 |
// Struct for measuring values typedef struct { float temp; float hum; float soil; } value_type; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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 |
#include #include "Logger.h" // min macro for ESP #define espmin(X, Y) (((X)<(Y))?(X):(Y)) // WiFi connection const char* ssid = "SSID"; const char* password = "PASSWORD"; char server[] = "SERVERIP"; int port = 80; WiFiClient client; const unsigned long postingInterval = 1800L * 1000000L; // delay between updates, in microseconds //DHT #include #define DHTPIN 4 #define DHTTYPE DHT11 // DHT 11 DHT dht(DHTPIN, DHTTYPE,16); // needed to avoid link error on ram check extern "C" { #include "user_interface.h" } void setup() { value_type values[3]; // start serial pinMode(12, OUTPUT); pinMode(A0, INPUT); dht.begin(); Serial.begin(115200); Serial.println("WLAN Greenhouse Data Logger - Electric Playground"); // First measuring values[0]= domeasuring(); delay(1000); // Second measuring values[1]= domeasuring(); delay(1000); // Third measuring values[2]= domeasuring(); // Finding the middle value float temp; float hum; float minmin = espmin(espmin(values[0].temp, values[1].temp), values[2].temp); if (values[0].temp = minmin) { temp=espmin(values[1].temp, values[2].temp); hum=espmin(values[1].hum, values[2].hum); } else if (values[1].temp = minmin) { temp=espmin(values[0].temp, values[2].temp); hum=espmin(values[0].hum, values[2].hum); } else { temp=espmin(values[0].temp, values[1].temp); hum=espmin(values[0].hum, values[1].hum); } float soil=(values[0].soil+values[1].soil+values[1].soil)/3.0; // send data // Connect to WIFI WiFi.mode(WIFI_STA); WiFiStart(); // debug while (client.available()) { char c = client.read(); Serial.write(c); } Serial.println("Humidity:"+String(hum,2)+"\tTemperature:"+String(temp,2)+"\tSoil:"+String(soil,2)); httpRequest(hum,temp,soil); Serial.println("Good Night"); ESP.deepSleep(postingInterval, WAKE_NO_RFCAL); //WAKE_DEFAULT, WAKE_RFCAL, WAKE_NO_RFCAL, WAKE_RF_DISABLED. delay(1000); } void loop() { // do nothing } // Measueing value_type domeasuring() { digitalWrite(12, HIGH); delay(300); value_type retval; retval.hum = dht.readHumidity(); if (isnan(retval.hum)) retval.hum=999; retval.temp = dht.readTemperature(); if (isnan(retval.temp)) retval.temp=999; retval.soil = analogRead(A0); digitalWrite(12, LOW); Serial.println("Humidity:"+String(retval.hum,2)+"\tTemperature:"+String(retval.temp,2)+"\tSoil:"+String(retval.soil,2)); return retval; } void httpRequest(float h, float t, float s) { // close any connection before send a new request. // This will free the socket on the WiFi shield client.stop(); delay(100); // We now create a URI for the request String url = "/newrecord.php?"; url += "temp="; url += String(t,2); url += "&humidity="; url += String(h,2); url += "&soil="; url += String(s,2); // if there's a successful connection: if (client.connect(server, port)) { Serial.println("connecting..."); client.print(String("GET ") + url + " HTTP/1.1\r\n" + "Host: " + server + "\r\n" + "Connection: close\r\n\r\n"); Serial.println("data submitted."); delay(10); } else { // if you couldn't make a connection: Serial.println("connection failed"); } } void WiFiStart() { // Connect to WiFi network Serial.print("Connecting to "); Serial.println(ssid); WiFi.begin(ssid, password); while (WiFi.status() != WL_CONNECTED) { delay(250); Serial.print("."); } Serial.println(""); Serial.println("WiFi connected"); // Print the IP address Serial.println(WiFi.localIP()); } |
Das war es also das Jahr 2015, draussen explodieren die Raketen und verkünden das Ende. Ja, mein Blog ist noch jung und ich möchte mich allen Lesern bedanken und freue mich schon auf das nächste Jahr. Wie man auch mit 30 keine Biografie macht, macht man auch ein paar Beiträgen noch keinen Jahresrückblick. Aber man fasst gute Vorsätze zu fassen, schmiedet große Pläne und teilt diese mit seiner Leserschaft.
Also, für das neue Jahr habe ich mir vorgenommen die Updaterate von ca. einem Beitrag pro Woche beizubehalten. Ich hoffe das gelingt denn ich mache das hier schließlich nur als Hobby und bin in meinem Brotberuf mitunter auch sehr eingespannt. Außerdem habe ich vor demnächst eine englische Übersetzung meines Blogs anzubieten, in der dann die Beiträge ein paar Tage versetzt auch auf Englisch erscheinen. Vielleicht gelingt es mir auch irgendein Cross-Over Projekt mit einer ganz anderen Welt einzufädeln. Mich würde irgend etwas mit Kunst interessieren also, wenn wer eine Idee hat und einen Partner für das Technische sucht dann meldet euch.
Auf der Softwareseite steht ein Projekt mit Erlang auf dem Raspi sowie ein Projekt mit MATLAB/Simulink auf dem Programm. Bei Erlang gefällt wie das Thema Verteilung dort gehandhabt wird und außerdem ist es mal ein Exot unter den Programmiersprachen. Mathworks macht in letzter Zeit viel in Richtung Raspberry, Arduino, LEGO Mindstorms usw und das möchte ich mir mal ansehen. Was es genau wird weiß ich selber noch nicht also lasst euch einfach überraschen.
Auf der Hardwareseite habe ich noch nicht so konkrete Pläne. Ich habe mir ein Lillypad besorgt und suche noch eine Idee dafür, aber ansonsten ist noch kein Plan in der Pipeline. Solltet ihr Ideen haben oder eine Sache von der Ihr denkt “Hey das würde mich mal interessieren” dann nehmt euch ein Herz und schreibt mir, ich freue mich immer über den Austausch mit Geistesbrüdern.
So, nun gehts aber auch für mich ans feiern und es bleibt mir euch etwas zu wünschen. Da man als Techniker nur all zu gut weiß wie elementar das Thema Scheitern zum Leben dazu gehört wünsche ich euch ein Prosit Neujahr 2016 und wünsche euch für das Jahr 2016, dass ihr aus euren Fehlern und Irrwegen die richtigen Lehren ziehen könnt.
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.
|
1 2 3 4 5 6 7 |
#!/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.
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%.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 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> <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. ' °C</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> |
The result is a nice website that also works great on mobile devices.
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
by
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”
|
1 |
CREATE DATABASE `templogg` /*!40100 DEFAULT CHARACTER SET latin1 */; |
Then I create a table called “tbllogging”
|
1 2 3 4 5 6 7 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
<?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.
|
1 |
SELECT * FROM tbllogging; |
When it works I delete the test data.
|
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.