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
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()); } |
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.
In a first step I will make a prototype for my sensor board. Usually you build a prototype just on a breadboard. But I would like to use my prototype for some weeks to see how my sensors work and how much energy is needed. Therefore I use a prototype circuit board instead.
My shopping list for that:
Additionally I need:
I know that there a many statements about the right soldering iron in the internet but for me personally a cheap soldering station is more than enough. There is also a good desoldering pump coming with the station and every beginner will need it. If you have too much money then spend it in a quite good solder. As power supply I use a USB powerbank that I’ve received as a giveaway. You can also get one for less than 10€ (e.g. here)
I try to do not solder items directly when building a prototype. Therefore I use pin headers. The disadvantage is that the prototype is not that robust but the advantage is I can easily borrow some parts without taking the soldering iron.
The ESP8266 just takes 3.3V so I take the USB to TTL converter also as voltage converter. Be sure to put the jumper to 3.3V. To safe energy I will put my ESP in deep-sleep. To prevent my sensors taking power when they are not used I use a transistor to power them on only when needed. The ESP needs quite a high current when connecting to the WLAN. I use an electrolytic capacitor parallel to the voltage supply. Sizing of that is no rocket science, it should be somewhere between 400uF and 2200uF (6-10V). For programming the ESP the CH_PD Pin has to be put on ground. Some recommend a button for that but I prefer a jumper.
The manual for the ESP8266 says that for wake up from deep-sleep pin 16 has to be connected to CH_PD. Unfortunately for some reasons this is not working with mine. I use the RESET for the wakeup instead. This prevents using some options for wakeup but I don’t care for the prototype.
It is a puzzle to place all the components on the board. I tried to save as much space because I had a small leftover to be used.
For programming my ESP I use the Arduio IDE. You have to install the ESP board in the IDE. Add in File->Properties->Additional Board Manager URLs: http://arduino.esp8266.com/stable/package_esp8266com_index.json Select Board „…“ > Board Manager… , choose esp8266 and press install. After that you can program the ESP like any other Arduino. For programming the jumper has to be set on starting the board. Sometimes the upload fails with some strange error messages. If this happens I simply restart the ESP and try it again. Remember to remove the jumper after uploading the software. Wake-up from deep-sleep is not working when the jumper ist set.
|
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 |
#include // WLAN Name und Password (PSK) const char* ssid = "SSID"; const char* password = "PSK"; WiFiClient client; // Server Adress of my raspberry char server[] = "10.0.0.3"; int port = 80; //If you use a different port on your webserver adjust the no here // Update Intervall const unsigned long postingInterval = 1800L * 1000000L; //DHT init #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() { // Pin 12 powers the sensors pinMode(12, OUTPUT); digitalWrite(12, HIGH); // Serial interface for debugging Serial.begin(115200); Serial.println("WLAN Logger - Electric Playground"); // start WIFI WiFi.mode(WIFI_STA); WiFiStart(); // waiting delay(3000); //DHT init dht.begin(); // ADC (Soil) init pinMode(A0, INPUT); } void loop() { // debug while (client.available()) { char c = client.read(); Serial.write(c); } // read sensor values float hum = dht.readHumidity(); float temp = dht.readTemperature(); float soil = analogRead(A0); //Debug Serial.println("Humidity:"+String(hum,2)+"\tTemperature:"+String(temp,2)+"\tSoil:"+String(soil,2)); //Send values httpRequest(hum,temp,soil); //power of sensors and go to deep sleep delay(500); digitalWrite(12, LOW); ESP.deepSleep(postingInterval, WAKE_NO_RFCAL); delay(1000); } //Transmit data to webserver void httpRequest(float h, float t, float s) { client.stop(); delay(100); //build URL String url = "/newrecord.php?"; url += "temp="; url += String(t,2); url += "&humidity="; url += String(h,2); url += "&soil="; url += String(s,2); // call URL 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 { Serial.println("connection failed"); } } // Establish WIFI connection void WiFiStart() { Serial.print("Connecting to "); Serial.println(ssid); WiFi.begin(ssid, password); while (WiFi.status() != WL_CONNECTED) { delay(500); Serial.print("."); } Serial.println(""); Serial.println("WiFi connected"); Serial.println(WiFi.localIP()); } |
I love home-grown vegetables. Eat tomatoes straight from the bush and you know how tomatoes have to taste like. I grow my tomatoes in buckets on my balcony and it really works fine. Unfortunately I’m a bit lazy in watering so my harvest was not as good as it could be in the last years.
therefore I decided to make an automatic plant irrigation system. I will use ESP8266 together with temperature/humidity and soil humidity measurements and send the actual values with WLAN to my data central, a Raspberry Pi. The measuring unit should be completely autonomous so they will be quipped with small solar panels each. The Raspberry will be used for data recording and works as controller for the watering unit. For watering I will properly need some pumps because unfortunately I have no chance to make the watering by gravity only.
Last but not least, it should not cost too much!