Device to Database — Beste Saylar

Responses for fallowing questions, using the farm PostgreSQL database.

a. When did the outside sensor break and stop sending data?

SELECT device, min(recorded_at), max(recorded_at)

FROM sensor_data

GROUP by device

Screen Shot 2020-02-27 at 10.35.16 AM.png

Outside sensor break down and stop sending data

b. Show the min and max temperature in the root cellar by year;

SELECT extract(YEAR FROM recorded_at)as day, device,

max(reading),  min(reading), avg(reading)

FROM sensor_data

Screen Shot 2020-02-24 at 1.52.10 PM.png

The minimum temperature in the root cellar; 27.80

and maximum temperature in the root cellar; 69.00

c. What was the lowest temperature recorded 2018?

SELECT extract(YEAR FROM recorded_at) ad day, device

max(reading), min(reading), avg(reading)

FROM sensor_data

WHERE mesurement=’temperature’

AND recorded_at BETWEEN’2018-01-01’ and ‘2018-12-31‘

GROUP BY day, device;

Screen Shot 2020-02-24 at 2.16.51 PM.png

Outside temperature was the lowest one in 2018.

Write two queries that use data from your sensor.

SOIL

TEMPERATURE

For this assignment I’ve deployed a sensor to monitor a plant on the ITP floor. Measurements I’ve monitored are followings;

Temperature
Humidity
Pressure
Illuminance
Uva
Uvb
Soil moisture

I’ve sent my data to the MQTT broker. After deploying, I ensured that it's sending data and the MQTT broker is receiving data.

WIRING

I’ve used an Arduino MKR 1010 and a LED for this project. Capacitive moisture soil sensor has three wires that connects GND, VCC, AUOT to respectively, GND, Vin, A0. LED connected to ground with pull down resistor and positive leg is connected to pin analog 5.

My device’s name device_17 and here we can see getting the data.

CODE

#include <WiFiNINA.h>
#include <Arduino_MKRENV.h>
#include <ArduinoMqttClient.h>

#include "config.h"

WiFiSSLClient net;
MqttClient mqtt(net);

String temperatureTopic = "itp/" + DEVICE_ID + "/temperature";
String humidityTopic = "itp/" + DEVICE_ID + "/humidity";
String ledTopic = "itp/" + DEVICE_ID + "/led";
String pressureTopic="itp/" + DEVICE_ID + "/pressure";
String illuminanceTopic="itp/" + DEVICE_ID + "/illuminance";
String uvaTopic="itp/" + DEVICE_ID + "/ uva";
String uvbTopic="itp/" + DEVICE_ID + "/uvb";
String moistureTopic="itp/" + DEVICE_ID + "/soil";

// //// Publish every 5 minutes for the workshop
unsigned long publishInterval = 5 * 60 * 1000;
unsigned long lastMillis = 0;
const int ledPin = 5;

void setup() {
Serial.begin(9600);

// Wait for a serial connection
while (!Serial) { }

// initialize the shield
if (!ENV.begin()) {
Serial.println("Failed to initialize MKR ENV shield!");
while (1);
}

// initialize ledPin as an output.
pinMode(ledPin, OUTPUT);

Serial.println("Connecting WiFi");
connectWiFi();

// define function for incoming MQTT messages
mqtt.onMessage(messageReceived);
}

void loop() {
if (WiFi.status() != WL_CONNECTED) {
connectWiFi();
}

if (!mqtt.connected()) {
connectMQTT();
}

// poll for new MQTT messages and send keep alives
mqtt.poll();

if (millis() - lastMillis > publishInterval) {
lastMillis = millis();

float temperature = ENV.readTemperature(FAHRENHEIT);
float humidity = ENV.readHumidity() ;

float pressure = ENV.readPressure();
float illuminance = ENV.readIlluminance();
float uva= ENV.readUVA();
float uvb = ENV.readUVB();
float soilMoisture =analogRead(A0);

Serial.print(temperature);
Serial.print("°F ");
Serial.print(humidity);
Serial.println("% RH");
Serial.print(illuminance);
Serial.print("lux ");
Serial.print(uva);
Serial.println("uvw/cm2");
Serial.print(uvb);
Serial.println("uvw/cm2");
Serial.print(soilMoisture);
Serial.println(" Moisture value");
Serial.print(pressure);

Serial.print("kPa");

mqtt.beginMessage(temperatureTopic);
mqtt.print(temperature);
mqtt.endMessage();

mqtt.beginMessage(humidityTopic);
mqtt.print(humidity);
mqtt.endMessage();

mqtt.beginMessage(illuminanceTopic);
mqtt.print(illuminance);
mqtt.endMessage();

mqtt.beginMessage(uvaTopic);
mqtt.print(uva);
mqtt.endMessage();

mqtt.beginMessage(uvbTopic);
mqtt.print(uvb);
mqtt.endMessage();

mqtt.beginMessage(moistureTopic);
mqtt.print(soilMoisture);
mqtt.endMessage();

mqtt.beginMessage(pressureTopic);

mqtt.print(pressure);

mqtt.endMessage();
}
}

void connectWiFi() {
// Check for the WiFi module
if (WiFi.status() == WL_NO_MODULE) {
Serial.println("Communication with WiFi module failed!");
// don't continue
while (true);
}

Serial.print("WiFi firmware version ");
Serial.println(WiFi.firmwareVersion());

Serial.print("Attempting to connect to SSID: ");
Serial.print(WIFI_SSID);
Serial.print(" ");

while (WiFi.begin(WIFI_SSID, WIFI_PASSWORD) != WL_CONNECTED) {
// failed, retry
Serial.print(".");
delay(3000);
}

Serial.println("Connected to WiFi");
printWiFiStatus();

}

void connectMQTT() {
Serial.print("Connecting MQTT...");
mqtt.setId(DEVICE_ID);
mqtt.setUsernamePassword(MQTT_USER, MQTT_PASSWORD);

while (!mqtt.connect(MQTT_BROKER, MQTT_PORT)) {
Serial.print(".");
delay(5000);
}

mqtt.subscribe(ledTopic);
Serial.println("connected.");
}

void printWiFiStatus() {
// print your WiFi IP address:
IPAddress ip = WiFi.localIP();
Serial.print("IP Address: ");
Serial.println(ip);
}

void messageReceived(int messageSize) {
String topic = mqtt.messageTopic();
String payload = mqtt.readString();
Serial.println("incoming: " + topic + " - " + messageSize + " bytes ");
Serial.println(payload);
if (payload.equalsIgnoreCase("ON")) {
analogWrite(ledPin, 255); // need analog write for older firmware
} else if (payload.equalsIgnoreCase("OFF")) {
analogWrite(ledPin, LOW);
} else {

// see if we have a brightness value
int percent = payload.toInt();
// check the range
if (percent < 0) {
percent = 0;
} else if (percent > 100) {
percent = 100;
}
// map brightness of 0 to 100 to 1 byte value 0x00 to 0xFF
int brightness = map(percent, 0, 100, 0, 255);
analogWrite(ledPin, brightness);
}
}