Weeks 3 & 4: Querying Sensor Data

During the past two weeks I got my IoT device, with it’s temperature/humidity and light/color sensors publishing data continuously to the MQTT broker. I also practiced writing SQL queries against a couple of databases, including one with data from my own sensors, to learn more about the collected information.

Part 1: Sending Continuous Data
Last week I added a new sensor to my IoT device. At that time, it was powered by a USB connection with my computer. For it run continuously and independent of my laptop, which travels with me often, I added a YwRobot Breadboard Power Supply Module (thanks, Nick!) and ordered a 9V 1500mA AC/DC power adapter to connect that to a power outlet.

Although not documented in my last post, the code I used last week activates the onboard LED of the color sensor, and it’s really bright! Too bright to leave on in my studio apartment at night. My understanding is that the light is necessary for reading reflected light of close objects, and I’m more interested in the incident light. This Adafruit tutorial instructed wiring the LED to ground to completely turn it off.

Here is an updated schematic and images to reflect these changes:


Here is my device next to my desk at home:


I also removed the hexColor reading as I can recreate the combined color values by combining the red, green, and blue readings. It might interesting to create a data visualization of those along side durational graphs of the individual R, G, and B data points.

Finally, I refactored the code a bit to collect and publish sensor data every ten minutes, including removing the interrupts to turn the onboard LED off and on (no longer necessary). I decided to publish all sensor readings at the same time. An alternative would have been to publish when the sensors noticed a change, but my testing from past couple weeks indicated that the readings for both were constantly fluctuating. I suppose another alternative is to publish when a threshold of change is reached.

Updated Code on Github

Part 2: Querying Data with SQL
My notes for this part of the assignment are in this document here. (<— Update to Part 2 in this document, the Node.js script is running on the DigitalOcean Droplet. It subscribes to MQTT topics and inserts data into the PostgreSQL database which is on AWS.)

A highlight was retrieving red, green, and blue color sensor readings to represent each hour of February 18, 2019, as a different color running from midnight to midnight. So interesting that it turns white during hours ~6-10am. This same pattern checks out on subsequent days, too.

Screen Shot 2019-02-19 at 9.51.45 PM.png