Week 7: Connecting the IoT Pipeline

We’ve been learning about the individual pieces of an IoT pipeline and how to use them. This week we connected all the pieces.

But first!
I noticed my sensors had ceased publishing to the MQTT broker a few days earlier. I logged into the InfluxDB and used one of the helpful queries from last week to determine the most recent readings:

Since I’m only one publishing to the red topic in our shared class database, it was a simple query:

> precision rfc3339
> select last(value) from red tz('America/New_York')
name: red
time                              last
----                              ----
2019-03-13T10:48:58.7449232-04:00 84

Writing queries is fun, so just to double-check:

> select last(value) from temperature where device = 'device_en' tz('America/New_York')
name: temperature
time                                last
----                                ----
2019-03-13T10:48:58.712896503-04:00 65.3

Yep, the last reading was posted on March 13, 2019, at 10:48am, three days prior to starting this post. I’m not sure why it stopped.

I decided to take the opportunity to update my Arduino code to include color temperature and lux (perceived brightness) readings from the TCS34725 sensor. I originally wanted to include these but my initial tests five weeks ago gave readings that were all over the place depending on how I was initiating the sensor. Tom suggested I used this for more accuracy:

Adafruit_TCS34725 tcs = Adafruit_TCS34725(TCS34725_INTEGRATIONTIME_700MS, TCS34725_GAIN_1X);

…as well as use this updated method to calculate color temperature:

colorTemp = tcs.calculateColorTemperature_dn40(red, green, blue, clear);

Note: I had to update the TCS34725 library to version 1.3.0 to access that dn40 algorithm.

Updated Code on Github


Part 1: Processing MQTT Data
A quick recap before we continue. So far I learned how to publish sensor data from my WiFi-connected Arduino MKR1000 to a server using MQTT protocol. That server, which was set up especially for this class, is running Mosquitto, an open source MQTT broker, and I can subscribe to the broker’s data topics to see my sensor’s data in real time. I also figured out how to connect my Arduino to a power supply to publish data continuously to the broker to start logging it over an extended period of time. To save this data, Don wrote a Node.js script on the same server that subscribes to the MQTT topics and then inserts data into a shared class PostgreSQL database living elsewhere on AWS. We practiced writing SQL queries against that PostgresQL database, as well as a few other databases (InfluxDB, MongoDB, and TimescaleDB) for comparison.

For this week, we learned how to write our own Node.js script to subscribe to the MQTT topics we care about and insert them into our own database. Because I’m interested in setting up my own Timescale database in the future, I modified the provided code to subscribe to the MQTT topic for my device and send the values for all readings (temperature, humidity, red, green, blue, color temperature, and lux) into my personal database in the TimescaleDB. I really didn’t do much work here—just updated MQTT subscription route to my device. However, I solidified my conceptual understanding of what’s happening behind the scenes. (Note that this Node.js script only runs when launched from my Terminal and while my computer is active.)

Here’s a screenshot indicating that the specified values were inserted into the database.

Here’s a screenshot indicating that the specified values were inserted into the database.

I logged into that database to ensure that the data was indeed there.

I logged into that database to ensure that the data was indeed there.

Code on Github

Part 2: Graphing
Next I wrote an Express server, again guided by the example in class, to query data from a database and visualize the results using Google Charts. Instead of using my personal database as mentioned above, I queried the shared class Timescale database because I wanted to graph all the data that my Arduino has been sending for the past couple of weeks, in addition to the newly added color temperature and lux values.

In our in-class example, we selected a student’s device to display all the values for one designated measurement, in this case, temperature. For this exercise, I rewrote the server to allow me to select and graph any measurement from my particular device.

Step 1. First, I rewrote the example code with a server route that provided me with a list of all the measurements for my particular devices. All the code is linked below, but here’s the output:

Request: http://localhost:4000/measurements

Response: ["blue","colorTemp","green","humidity","lux","red","temperature"]

(Note: thisBlue, thisGreen, and thisRed were from early attempts to publish sensor readings to the MQTT broker. I never used them and don’t care about them for this exercise.)

I logged into TimescaleDB database to draft the SQL query before incorporating it into my JavaScript server code.

I logged into TimescaleDB database to draft the SQL query before incorporating it into my JavaScript server code.

Step 2. Next, I created a route to return the values for a specific measurement type. Again, I practiced the query beforehand inside the TimescaleDB database. Again, here the output with the practice query below:

Request: http://localhost:4000/lux

Response (abbreviated): [{"recorded_at":"2019-03-16T23:23:31.428Z","reading":48},{"recorded_at":"2019-03-16T23:33:31.342Z","reading":46},…]

More practice!

More practice!

Step 3. With that working, I modified the front end files to make requests to server, retrieve the data, graph it using the Google Charts library. A good next step would be to figure out how to graph multiple sensor readings on one chart.

Actually, figuring this out wasn’t as straight forward as these three steps, but it all makes sense now.

Code on Github