Hardware components | ||||||
| × | 1 | ||||
| × | 1 | ||||
 |
| × | 1 | |||
Software apps and online services | ||||||
 |
| |||||
Then collect the data using your Hydroponics Control System and use it to monitor and make smarter decisions. View trends and see what's really going on!
After reading the temperature, humidity, soil moisture, or any hydroponic data, we need to first post the data to a database using the WiFi connection. This is done by using a simple REST API. A REST or Representational State Transfer is the use of the HTTP method (POST) with a JSON (JavaScript Object Notation) formatted payload.
void HttpPost(const char *url, String &post_data)
{
HTTPClient http;
http.begin(url);
http.addHeader("Content-Type", "application/x-www-form-urlencoded");
int http_code = http.POST(post_data); // Send the request
String payload = http.getString(); // Get the response payload
SerialDebug.println(http_code); // Print HTTP return code
SerialDebug.println(payload); // Print request response payload
if (payload.length() > 0) {
int index = 0;
do
{
if (index > 0) index++;
int next = payload.indexOf('\n', index);
if (next == -1) break;
String request = payload.substring(index, next);
if (request.substring(0, 9).equals("<!DOCTYPE")) break;
SerialDebug.println(request);
StaticJsonDocument<100> doc;
DeserializationError error = deserializeJson(doc, request);
if (!error) {
if (doc["OVERRIDE_LIGHTS_TIME"]) OVERRIDE_LIGHTS_TIME = doc["OVERRIDE_LIGHTS_TIME"];
if (doc["OVERRIDE_LIGHTS"]) OVERRIDE_LIGHTS = doc["OVERRIDE_LIGHTS"];
if (doc["OVERRIDE_VENT_FAN_TIME"]) OVERRIDE_VENT_FAN_TIME = doc["OVERRIDE_VENT_FAN_TIME"];
if (doc["OVERRIDE_VENT_FAN"]) OVERRIDE_VENT_FAN = doc["OVERRIDE_VENT_FAN"];
}
index = next;
} while (index >= 0);
}
http.end(); // Close connection
}
...
void loop() {
...
char buffer[80];
strftime(buffer, sizeof(buffer), "%m/%d/%Y %H:%M:%S", &rtc);
// Allocate JsonDocument
// Use arduinojson.org/assistant to compute the capacity
StaticJsonDocument<500> doc;
// Create the root object
doc["ReadingTime"] = buffer;
doc["InsideTemp"] = (inside.error) ? ERROR_READ : inside.temp;
doc["InsideRelative"] = (inside.error) ? ERROR_READ : inside.relative;
doc["InsideAbsolute"] = (inside.error) ? ERROR_READ : inside.absolute;
doc["OutsideTemp"] = (outside.error) ? ERROR_READ : outside.temp;
doc["OutsideRelative"] = (outside.error) ? ERROR_READ : outside.relative;
doc["OutsideAbsolute"] = (outside.error) ? ERROR_READ : outside.absolute;
doc["VentFan"] = vent_fan;
doc["Lights"] = lights;
doc["Power"] = power;
doc["DailyCost"] = cost;
doc["ColorTemp"] = color_temp;
doc["Lux"] = lux;
doc["CO2"] = co2;
doc["CO2Temp"] = co2_temp;
doc["CO2Relative"] = co2_relative;
doc["GerminationTemp"] = germination_temp;
doc["ChillerTemp"] = chiller_temp;
doc["pH"] = pH;
doc["DO"] = DO;
JsonArray array = doc.createNestedArray("GrowBed");
for (i = 0; i < sizeof(grow_bed_table) / sizeof(GROWBED_t); i++) {
JsonObject object = array.createNestedObject();
object["WaterTemp"] = (grow_bed_table[i].water_temp_error) ? ERROR_READ : grow_bed_table[i].water_temp;
object["WaterTDS"] = grow_bed_table[i].water_tds;
object["WaterLevel"] = grow_bed_table[i].water_level;
}
String json_data;
serializeJson(doc, json_data);
post_data = "data=" json_data;
SerialDebug.println(post_data);
#ifdef MySQL
HttpPost(mysql_url, post_data);
#endif
#ifdef MSSQL
HttpPost(mssql_url, post_data);
#endif
...
}There are two different popular user-controlled database platforms that you can use.
- Windows Web Hosting using Microsoft SQL Database Server.
- Local Raspberry Pi 4 server using MySQL Database Server.
You can also use any IoT service using an MQTT Client, but that method is not explained here.
Windows Web Hosting using Microsoft SQL Database ServerFor the Windows Web Hosting I've been using WinHost. They offer a Basic plan for only $3.95/month that should fit the budget of most home hobbyists including myself, but you can use any Windows Web Hosting you like. I've used them for years and have been very pleased with their products and services.
Pros
- Web based, accessable anywhere on any computer web browser.
- No hardware to maintain.
- More secure with Windows/Database Security.
- Faster than MySQL.
- Scalable. Need more performance subscribe to larger platforms, even Dedicated Servers.
Cons
- On the Web and hackable if security not implemented (SSL etc.) Security costs $$$.
- If your internet connection goes down you won't have access to your systems.
- Basic plans limited to 500MB SQL. Power plans to a max of 10GB. Larger costs $$$.
Use SQL Server Management Studio to connect to your Microsoft SQL Server and create your database and tables using the following SQL script.
CREATE TABLE [dbo].[Hydroponics](
[ReadingTime] [datetime] NOT NULL,
[InsideTemp] [DECIMAL](9, 2) NULL,
[InsideRelative] [DECIMAL](9, 2) NULL,
[InsideAbsolute] [DECIMAL](9, 2) NULL,
[OutsideTemp] [DECIMAL](9, 2) NULL,
[OutsideRelative] [DECIMAL](9, 2) NULL,
[OutsideAbsolute] [DECIMAL](9, 2) NULL,
[VentFan] [bit] NULL,
[Lights] [bit] NULL,
[POWER] [SMALLINT] NULL,
[GrowBed1WaterTemp] [DECIMAL](9, 2) NULL,
[GrowBed1WaterTDS] [SMALLINT] NULL,
[GrowBed1WaterLevel] [bit] NULL,
[GrowBed2WaterTemp] [DECIMAL](9, 2) NULL,
[GrowBed2WaterTDS] [SMALLINT] NULL,
[GrowBed2WaterLevel] [bit] NULL,
[DailyCost] [DECIMAL](9, 2) NULL,
[ColorTemp] [INT] NULL,
[Lux] [INT] NULL,
[CO2] [DECIMAL](9, 2) NULL,
[CO2Temp] [DECIMAL](9, 2) NULL,
[CO2Relative] [DECIMAL](9, 2) NULL,
[GerminationTemp] [DECIMAL](9,2) NULL,
[ChillerTemp] [DECIMAL](9,2) NULL,
[pH] [DECIMAL](9,2) NULL,
[DO] [DECIMAL](9,2) NULL,
PRIMARY KEY CLUSTERED
(
[ReadingTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Request](
[RequestTime] [DATETIME] NOT NULL,
[JsonData] [VARCHAR](MAX) NULL,
[Processed] [BIT] NULL,
PRIMARY KEY CLUSTERED
(
[RequestTime] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]Use Microsoft IIS Manager to setup and manage your web site. Use the following Active Server Page Extended 'adddata.aspx' to connect and transfer the data payload.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.Script;
using System.Web.Script.Serialization;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace Hydroponics
{
public partial class adddata : System.Web.UI.Page
{
class GrowBed
{
public decimal WaterTemp { get; set; }
public int WaterTDS { get; set; }
public bool WaterLevel { get; set; }
}
class HydroponicsData
{
public DateTime ReadingTime { get; set; }
public decimal InsideTemp { get; set; }
public decimal InsideRelative { get; set; }
public decimal InsideAbsolute { get; set; }
public decimal OutsideTemp { get; set; }
public decimal OutsideRelative { get; set; }
public decimal OutsideAbsolute { get; set; }
public bool VentFan { get; set; }
public bool Lights { get; set; }
public int Power { get; set; }
public decimal DailyCost { get; set; }
public int ColorTemp { get; set; }
public int Lux { get; set; }
public decimal CO2 { get; set; }
public decimal CO2Temp { get; set; }
public decimal CO2Relative { get; set; }
public decimal GerminationTemp { get; set; }
public decimal ChillerTemp { get; set; }
public List<GrowBed> GrowBed { get; set; }
}
protected void Page_Load(object sender, EventArgs e)
{
var data = new JavaScriptSerializer().Deserialize<HydroponicsData>(Request["data"].ToString());
String strSQL = "INSERT INTO Hydroponics (ReadingTime,"
"InsideTemp,InsideRelative,InsideAbsolute,"
"OutsideTemp,OutsideRelative,OutsideAbsolute,"
"VentFan,Lights,Power,DailyCost,"
"ColorTemp,Lux,"
"CO2,CO2Temp,CO2Relative,"
"GerminationTemp,ChillerTemp,"
"GrowBed1WaterTemp,GrowBed1WaterTDS,GrowBed1WaterLevel,"
"GrowBed2WaterTemp,GrowBed2WaterTDS,GrowBed2WaterLevel)"
" VALUES ("
"'" data.ReadingTime.ToString() "',";
if (data.InsideTemp >= 0) strSQL += data.InsideTemp.ToString() "," data.InsideRelative.ToString() "," data.InsideAbsolute.ToString() ",";
else strSQL += "NULL,NULL,NULL,";
if (data.OutsideTemp >= 0) strSQL += data.OutsideTemp.ToString() "," data.OutsideRelative.ToString() "," data.OutsideAbsolute.ToString() ",";
else strSQL += "NULL,NULL,NULL,";
strSQL += ((data.VentFan) ? "1" : "0") "," ((data.Lights) ? "1" : "0") "," data.Power.ToString() "," data.DailyCost.ToString() ",";
if (data.ColorTemp >= 0) strSQL += data.ColorTemp.ToString() "," data.Lux.ToString() ",";
else strSQL += "NULL,NULL,";
if (data.CO2 >= 0) strSQL += data.CO2.ToString() "," data.CO2Temp.ToString() "," data.CO2Relative.ToString() ",";
else strSQL += "NULL,NULL,NULL,";
if (data.GerminationTemp >= 0) strSQL += data.GerminationTemp.ToString() ",";
else strSQL += "NULL,";
if (data.ChillerTemp >= 0) strSQL += data.ChillerTemp.ToString() ",";
else strSQL += "NULL,";
if (data.GrowBed[0].WaterTemp >= 0) strSQL += data.GrowBed[0].WaterTemp.ToString() ",";
else strSQL += "NULL,";
if (data.GrowBed[0].WaterTDS >= 0) strSQL += data.GrowBed[0].WaterTDS.ToString() ",";
else strSQL += "NULL,";
strSQL += ((data.GrowBed[0].WaterLevel) ? "1" : "0") ",";
if (data.GrowBed[1].WaterTemp >= 0) strSQL += data.GrowBed[1].WaterTemp.ToString() ",";
else strSQL += "NULL,";
if (data.GrowBed[1].WaterTDS >= 0) strSQL += data.GrowBed[1].WaterTDS.ToString() ",";
else strSQL += "NULL,";
strSQL += ((data.GrowBed[1].WaterLevel) ? "1" : "0") ")";
try
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ServerConnectionString"].ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand(strSQL, con);
cmd.ExecuteNonQuery();
strSQL = "SELECT * FROM Request WHERE Processed IS NULL ORDER BY RequestTime ASC";
SqlCommand req = new SqlCommand(strSQL, con);
SqlDataAdapter sda = new SqlDataAdapter(req);
DataTable dtRequest = new DataTable();
sda.Fill(dtRequest);
if (dtRequest.Rows.Count > 0)
{
for (int rows = 0; rows < dtRequest.Rows.Count; rows++)
{
if (dtRequest.Rows[0]["JsonData"] != DBNull.Value)
{
Response.Write(dtRequest.Rows[0]["JsonData"].ToString());
Response.Write("\n");
}
}
DateTime dt = (DateTime)dtRequest.Rows[dtRequest.Rows.Count-1]["RequestTime"];
strSQL = "UPDATE Request SET Processed=1 WHERE Processed IS NULL AND RequestTime <='" dt.ToString("yyyy-MM-dd HH:mm:ss.fff") "'";
SqlCommand upd = new SqlCommand(strSQL,con);
upd.ExecuteNonQuery();
}
con.Close();
}
catch (SqlException sqlex)
{
Response.Write(sqlex.Message.ToString() "\r\n");
}
}
}
}Using the IIS Manager add a connection string 'ServerConnectionString' that will allow the.aspx web page to connect to your database.
By now we should be capturing data every minute. To dynamically view the database data we will be using Grafana. This dynamic graphical tool will allow you to modify and add graphs that will help you view the time series data to manage your hydroponics system.
Download and install Grafana on your computer or get your free Managed Grafana instance and create a localhost server.
After you login to Grafana create a database connection with MSSQL and then import the folloing script to create the Garage Hydroponics dashboard.
To allow requests from grafana such as turning on the lights or fan add the following 'addrequest.aspx' that will allow for a response, the next time a payload is sent. This would mean that once the option is selected it could take as long as a minute before the action is processed.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;
namespace Hydroponics
{
public partial class addrequest : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (Request["data"] != null)
{
String strSQL = "INSERT INTO Request (RequestTime, JsonData) VALUES (GETUTCDATE(),'" + Request["data"].ToString() + "')";
try
{
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ServerConnectionString"].ConnectionString);
con.Open();
SqlCommand cmd = new SqlCommand(strSQL, con);
cmd.ExecuteNonQuery();
con.Close();
}
catch (SqlException sqlex)
{
Response.Write(sqlex.Message.ToString() + "\r\n");
}
}
ClientScript.RegisterStartupScript(typeof(Page), "closePage", "window.close();", true);
}
}
}Use the new Raspberry Pi 4 as a Server. With the addition of 4GB RAM, USB 3.0, and 1Gb Ethernet, the Raspberry Pi 4 has finally become a real contender as a low cost home server.
Pros
- On local home network. All data is collected and stays at home.
- Not reliant on an internet connection for data collection.
- Database size limited to your drive size.
- No monthly fee.
Cons
- On your local home network so it is not accessable via the web, unless opened.
- Hardware to maintain.
- Slower database performance than Microsoft SQL.
- Not scalable.
- Security not strong.
x1 CanaKit Raspberry Pi2 4GB Starter Kit - 4GB RAM.
x1 Toshiba HDTB410EK3AA 1TB 2.5" USB 3.0 Black.
Download and install the latest version of the Raspberry Pi OS.
Make sure the OS boots from the USB 3.0 hard drive.
Download and install the Apache Web Server with PHP.
Download and install the MySQL Database Server.
Download and install PHPMyAdmin for easy administration of MySQL.
Download and install the Grafana Server.
Setup Ethernet to your home static IP address.
Download and install VNC Viewer for Remote Access.
Setup your Raspberry Pi 4 as a Headless Server (No Keyboard/Mouse/Monitor).
Use phpMyAdmin to create a new database called 'mydata', and create the following table using the SQL script.
CREATE TABLE Hydroponics(
ReadingTime DATETIME NOT NULL,
InsideTemp DECIMAL(9, 2) NULL,
InsideRelative DECIMAL(9, 2) NULL,
InsideAbsolute DECIMAL(9, 2) NULL,
OutsideTemp DECIMAL(9, 2) NULL,
OutsideRelative DECIMAL(9, 2) NULL,
OutsideAbsolute DECIMAL(9, 2) NULL,
VentFan BIT NULL,
Lights BIT NULL,
Power SMALLINT NULL,
GrowBed1WaterTemp DECIMAL(9, 2) NULL,
GrowBed1WaterTDS SMALLINT NULL,
GrowBed1WaterLevel BIT NULL,
GrowBed2WaterTemp DECIMAL(9, 2) NULL,
GrowBed2WaterTDS SMALLINT NULL,
GrowBed2WaterLevel BIT NULL,
DailyCost INT NULL,
ColorTemp INT NULL,
Lux INT NULL,
CO2 DECIMAL(9, 2) NULL,
CO2Temp DECIMAL(9, 2) NULL,
CO2Relative DECIMAL(9, 2) NULL,
GerminationTemp DECIMAL(9,2) NULL,
ChillerTemp DECIMAL(9,2) NULL,
pH DECIMAL(9,2) NULL,
DO DECIMAL(9,2) NULL,
PRIMARY KEY(ReadingTime)
);
CREATE TABLE Request(
RequestTime DATETIME NOT NULL,
JsonData VARCHAR(4096) NULL,
Processed BIT NULL,
PRIMARY KEY(RequestTime)
);Use the following PHP file 'adddata.php' in your Apache Web Server to connect to MySQL and injest the JSON data payload.
<?php
$servername = "localhost";
$dbname = "mydata";
$username = "admin";
$password = "password";
if ($_SERVER["REQUEST_METHOD"] == "POST") {
$data = json_decode($_POST["data"]);
// Create database connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
ini_set("date.timezone", "UTC");
$sql = "INSERT INTO Hydroponics (ReadingTime," .
"InsideTemp,InsideRelative,InsideAbsolute," .
"OutsideTemp,OutsideRelative,OutsideAbsolute," .
"VentFan,Lights,Power,DailyCost," .
"ColorTemp,Lux," .
"CO2,CO2Temp,CO2Relative," .
"GerminationTemp,ChillerTemp," .
"pH,DO," .
"GrowBed1WaterTemp,GrowBed1WaterTDS,GrowBed1WaterLevel," .
"GrowBed2WaterTemp,GrowBed2WaterTDS,GrowBed2WaterLevel) " .
"VALUES (" .
"STR_TO_DATE('" . $data->ReadingTime . "','%m/%d/%Y %H:%i:%s'),";
if ($data->InsideTemp >= 0) $sql .= $data->InsideTemp . "," . $data->InsideRelative . "," . $data->InsideAbsolute . ",";
else $sql .= "NULL,NULL,NULL,";
if ($data->OutsideTemp >= 0) $sql .= $data->OutsideTemp . "," . $data->OutsideRelative . "," . $data->OutsideAbsolute . ",";
else $sql .= "NULL,NULL,NULL,";
$sql .= ($data->VentFan ? "1":"0") . "," . ($data->Lights ? "1":"0") . "," . $data->Power . "," . $data->DailyCost . ",";
if ($data->ColorTemp >= 0) $sql .= $data->ColorTemp . "," . $data->Lux . ",";
else $sql .= "NULL,NULL,";
if ($data->CO2 >= 0) $sql .= $data->CO2 . "," . $data->CO2Temp . "," . $data->CO2Relative . ",";
else $sql .= "NULL,NULL,NULL,";
if ($data->GerminationTemp >= 0) $sql .= $data->GerminationTemp . ",";
else $sql .= "NULL,";
if ($data->ChillerTemp >= 0) $sql .= $data->ChillerTemp . ",";
else $sql .= "NULL,";
if ($data->pH >= 0) &sql .= $data->pH . ",";
else $sql .= "NULL,";
if ($data->DO >= 0) &sql .= $data->DO . ",";
else $sql .= "NULL,";
if ($data->GrowBed[0]->WaterTemp >= 0) $sql .= $data->GrowBed[0]->WaterTemp . ",";
else $sql .= "NULL,";
if ($data->GrowBed[0]->WaterTDS >= 0) $sql .= $data->GrowBed[0]->WaterTDS . ",";
else $sql .= "NULL,";
$sql .= ($data->GrowBed[0]->WaterLevel ? "1":"0") . ",";
if ($data->GrowBed[1]->WaterTemp >= 0) $sql .= $data->GrowBed[1]->WaterTemp . ",";
else $sql .= "NULL,";
if ($data->GrowBed[1]->WaterTDS >= 0) $sql .= $data->GrowBed[1]->WaterTDS . ",";
else $sql .= "NULL,";
$sql .= ($data->GrowBed[1]->WaterLevel ? "1":"0") . ")";
if ($conn->query($sql) == FALSE) {
echo "Error: " . $sql . "\r\n" . $conn->error . "\r\n";
}
$sql = "SELECT * FROM Request WHERE Processed IS NULL ORDER BY RequestTime ASC";
$result = $conn->query($sql);
if ($result->num_rows > 0) {
$lasttime = "";
while($row = $result->fetch_assoc()) {
echo $row["JsonData"] . "\n";
$lasttime = $row["RequestTime"];
}
$sql = "UPDATE Request SET Processed=1 WHERE Processed IS NULL AND RequestTime <= '" . $lasttime . "'";
$conn->query($sql);
}
$conn->close();
}
else {
echo "No data posted with HTTP POST.";
}After you login to Grafana on the Pi 4 at port 3000, create a database connection with MySQL and then import the folloing script to create the Garage Hydroponics dashboard.
Note: In order for Grafana to retrieve the correct time series data make sure you set the default time zone in MySQL to UTC.
To allow requests from grafana such as turning on the lights or fan add the following PHP file 'addrequest.php' that will allow for a response, the next time a payload is sent. This would mean that once the option is selected it could take as long as a minute before the action is processed.
<?php
$servername = "localhost";
$dbname = "mydata";
$username = "admin";
$password = "mysql";
if ($_SERVER["REQUEST_METHOD"] == "GET") {
// Create database connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
ini_set("date.timezone", "UTC");
$sql = "INSERT INTO Request (RequestTime,JsonData) " .
"VALUES (" .
"UTC_TIMESTAMP(),'" . $_GET["data"] . "')";
if ($conn->query($sql) == FALSE) {
echo "Error: " . $sql . "\r\n" . $conn->error;
}
$conn->close();
echo "<script>window.close();</script>";
}
else {
echo "No data posted with HTTP POST.";
}
echo "\r\n";Garage Hydroponics
Hydroponics Deep Water Culture Bucket System
Hydroponics Growbed Sensors/Display Module
Hydroponics Chiller
Hydroponics Water/Nutrient Control
Hydroponics Database Management
Hydroponics Germination Control
Hydroponics CO2 Monitoring
Hydroponics Light Monitoring
Hydroponics pH and DO Monitoring
Comments
Please log in or sign up to comment.