Device to Database

Week 8: Node-RED for IoT

Use Node-RED to work with IoT in the browser with very little code! For this week's exercise, I created a HTTP endpoint that displays the red, green, and blue readings from my sensor.

With Node-RED’s drag-and-drop nodes, I subscribed to my red, green, and blue MQTT topics, converted those readings to integers, joined them into an array, and printed that to a log file. Then, I implemented a GET request to display the array on an HTML page.

Ideally I’d figure out how to incorporate that data into CSS code to update the background color of the page. For now, I’ve posted the current setup below:

Click to enlarge

Screen+Shot+2019-03-29+at+1.31.15+AM.jpg

Week 8: Graphing with Grafana

This week introduced us to Grafana, an open source platform for visualizing our time series data. I ended up using data from the InfluxDB for no particular reason except that I happened to click on that first. This program makes it so easy to query data, but it certainly helps that we learned how to do it by scratch of the past couple of weeks. Some of my graphs are below. I could play with these all day.

Click to enlarge

Of course I went for the red, green, and blue sensor readings first, creating heatmap and gauge visualizations for both.

I set the date format of the heatmaps to time series buckets and captured hourly averages, displayed above for the past seven days. The white bands mark when lights are out and I’m likely sleeping or the sun when down before I got home that day. Here’s the query for red (which is basically the same for green and blue):

SELECT mean("value") FROM "red" WHERE ("device" = 'device_en') AND $timeFilter GROUP BY time(1h) fill(null)

The gauge show the average reading from the past 15 minutes:

SELECT mean("value") FROM "red" WHERE ("device" = 'device_en') AND $timeFilter GROUP BY time(15m) fill(null)

Click to enlarge

Here are the average hourly readings of all three colors for the past seven days displayed as a line graph:

SELECT mean(\"value\") FROM \"red\" WHERE (\"device\" = 'device_en') AND time >= now() - 7d GROUP BY time(1h) fill(null);SELECT mean(\"value\") FROM \"green\" WHERE (\"device\" = 'device_en') AND time >= now() - 7d GROUP BY time(1h) fill(null);SELECT mean(\"value\") FROM \"blue\" WHERE (\"device\" = 'device_en') AND time >= now() - 7d GROUP BY time(1h) fill(null)"

Click to enlarge

I also graphed the new lux and color temperature values that I started recording over a week ago. Again, hourly average readings for the past week. Interesting that the highest lux values are recorded during the afternoon when color temperatures are at the lowest. Except on March 21st—did I keep the drapes closed that day? Grafana’s query inspector:

SELECT mean(\"value\") FROM \"lux\" WHERE (\"device\" = 'device_en') AND time >= now() - 7d GROUP BY time(1h) fill(null);SELECT mean(\"value\") FROM \"colorTemp\" WHERE (\"device\" = 'device_en') AND time >= now() - 7d GROUP BY time(1h) fill(null)

Click to enlarge

Finally, the min, max, and mean daily readings for temperature and humidity since February 17, 2019. You can see where three days when the data logging stopped in mid March, and also when the temperature dropped below 40 degrees Fahrenheit on March 4th because I left the window open too much one night. Here’s the query from Grafana’s query inspector for temperature (basically the same for humidity):

SELECT min(\"value\"), mean(\"value\"), max(\"value\") FROM \"temperature\" WHERE (\"device\" = 'device_en') AND time >= 1550463093160ms GROUP BY time(1d) fill(null)

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