Matha Goram
Published © GPL3+

Everybody's Logging

Logging telemetry from IoT sensors is a saturated rites of passage exercise after the obligatory Hello World exercise is complete.

IntermediateShowcase (no instructions)2 hours5,181
Everybody's Logging

Things used in this project

Hardware components

Elegoo Arduino Super Starter Kit
×1
ELEGOO Raspberry Pi 3 Model B Basic Starter Kit with NOOBs Pre-Loaded 8GB SD Card
ELEGOO Raspberry Pi 3 Model B Basic Starter Kit with NOOBs Pre-Loaded 8GB SD Card
×1
ELEGOO 37-in-1 Sensor Module Kit V1.0
ELEGOO 37-in-1 Sensor Module Kit V1.0
×1

Software apps and online services

Arduino IDE
Arduino IDE
Raspbian
Raspberry Pi Raspbian
MQTT
MQTT
MariaDB
Node-RED
Node-RED
R: The R Project for Statistical Computing

Story

Read more

Schematics

Process Workflow

The Farm - high-level diagram

Telemetry Schemata

Parent-child relationship for device telemetry

Service Broker Role

A paradigm

Dashboard - partial

A sample dashboard in the home automation farm

Code

Publish Client

Python
A client code snippet to publish a single topic to MQTT broker
def MyPubSingle(topic, payload): # connect, publish a message to broker and disconnect with default values primarily
  global hostname, qos, retain, port, client_id, keepalive, will, auth, tls, protocol, transport
  publish.single(                # the parameters are static (for now!)
    topic,                       # string, to which the payload will be published
    payload,                     # if "" or None, a zero length payload will be published
    qos, retain, hostname, port, client_id, keepalive, will, auth, tls, protocol,     transport)
  return 0

Message Payload

JSON
The data transferred from the data acquisition boards follows this basic structure for feeds to the service broker and the relational database server.
{"Status": 3, "DeviceId": "DS18B20-08-28", "Reading": 77.7872, "UOM": "\u00b0F", "Waqt": "2019-05-29T17:51:12.161400", "Dimension": "Temperature"}
{"Waqt": "2019-05-29T17:51:12.893204", "Reading": 34.78177711186752, "UOM": "\u00b0C", "Dimension": "Temperature", "Status": 0, "DeviceId": "BME680-08-1"}
{"Waqt": "2019-05-29T17:51:12.893204", "Reading": 974.2429958879013, "UOM": "hPa", "Dimension": "Pressure", "Status": 0, "DeviceId": "BME680-08-1"}
{"Waqt": "2019-05-29T17:51:12.893204", "Reading": 31.206078188994084, "UOM": "%rH", "Dimension": "Humidity", "Status": 0, "DeviceId": "BME680-08-1"}
{"Waqt": "2019-05-29T17:51:12.893204", "Reading": 1114504.3165641152, "UOM": "IAQ", "Dimension": "AirQuality", "Status": 0, "DeviceId": "BME680-08-1"}

Load Database

SQL
A stored procedure to load telemetry preserving parent-child relationship
BEGIN 
	/* DECLARE EXIT HANDLER FOR SQL EXCEPTION; */
	DECLARE l_DeviceId INT; /* device unique identifier */ 
	START TRANSACTION; /* new transaction */
	/* does named device exist in table */
	SELECT DISTINCT DeviceId INTO l_DeviceId
		FROM Device
		WHERE DeviceName = p_DeviceName;
		/* since device name does not exist, insert & obtain identifier */ 
	IF ISNULL(l_DeviceId) THEN
		INSERT INTO Device
			(DeviceName, DIMENSION, UOM, CurrentStatus) 
			VALUES (p_DeviceName, p_Dimension, p_UOM, p_Status);
		/* retain the auto incremented unique id for status update later */
		SET l_DeviceId = LAST_INSERT_ID();
	 END IF;
	/* update telemetry table with supplied values & link to parent table */
	INSERT INTO Telemetry 
		(DeviceId, STATUS, Reading, DIMENSION, UOM) 
		VALUES (l_DeviceId, p_Status, p_Reading, p_Dimension, p_UOM);
		/* update device status */
	UPDATE Device SET LastHeard = CURRENT_TIMESTAMP, CurrentStatus = p_Status 
		WHERE DeviceId = l_DeviceId;
	COMMIT; /* make changes permanent */
END;

Credits

Matha Goram

Matha Goram

22 projects • 14 followers
Working with discrete electronic components for a very long time but still suffering from the occasional dry soldering results.

Comments