Updated Accelerometer wand code

#include <SparkFun_ADXL345.h>         // SparkFun ADXL345 Library

// MQTT headers
#include <SPI.h>
#ifdef ARDUINO_SAMD_MKR1010
#include <WiFi101.h>
#define WL_NO_MODULE WL_NO_SHIELD
#else
#include <WiFiNINA.h>
#endif
#include <ArduinoMqttClient.h>

#include "config.h"

WiFiSSLClient net;
MqttClient mqtt(net);

String accelerometerTopic = "itp/" + DEVICE_ID + "/accelerometer"

int state = false;

int button = 6;

void setup() {

  Serial.begin(9600);             
  Serial.println("Connecting WiFi");
  pinMode(button, INPUT);

  connectWiFi();
  setAxl();
  reconnect();
  
}

void loop() {  
  mqtt.poll();

  if (digitalRead(button) == HIGH && state == false) {
    state = true;
    delay(200);
  }  else if (digitalRead(button) == HIGH && state == true) {
    state = false;
    delay(200);
  }

  if (state == true) {
    int x, y, z;
    adxl.readAccel(&x, &y, &z);      
     mqtt.beginMessage(accelerometerTopic);
      Serial.println("begin message");
      mqtt.print(x);
      mqtt.print(",");
      mqtt.print(y);
      mqtt.print(",");
      mqtt.print(z);
      Serial.print(x);
      Serial.print(", ");
      Serial.print(y);
      Serial.print(", ");
      Serial.println(z);
    Serial.println("end message");
    mqtt.endMessage();
    
  } else if (state == false) {
    Serial.println("waiting");
    
  }
  
}

Databasing

Use the `farm` database. Write queries to answer the following questions:

When did the outside sensor break and stop sending data?

select max(recorded_at) from sensor_data where device = 'outside'

Result ---> 2019-01-26 11:57:47+00

Today is the 2019-02-17, so it has not sent data for over a month

Show the min and max temperature in the root cellar by year

Original attempt:

>>>  SELECT recorded_at::date as date, extract(YEAR FROM recorded_at) as year,

            device, max(reading), min(reading) 

        FROM sensor_data 

        WHERE measurement = 'temperature' 

        AND device = 'rootcellar'

        GROUP BY date, year, device

UNION

    SELECT distinct year, max(reading),min(reading);

^ I thought I needed to cast the date into something, but turns out I just needed to extract the year from the date, give it a new column name, along with the rest of the column names - I understand, now that the bit after the extract parenthesis() are naming the column titles of the output - and then execute the rest of the functions

>>> SELECT extract(YEAR FROM recorded_at) as year, device, max(reading), min(reading)

        FROM sensor_data 

        WHERE measurement = 'temperature' 

        AND device = 'rootcellar'

        GROUP BY year, device;

 year |   device   |  max  |  min  

------+------------+-------+-------

 2016 | rootcellar | 69.00 | 35.10

 2017 | rootcellar | 67.10 | 27.80

 2018 | rootcellar | 70.90 | 34.90

 2019 | rootcellar | 48.40 | 29.30

(4 rows)

What was the lowest temperature recorded 2018?

>>> SELECT extract(YEAR FROM recorded_at) as year, device, min(reading)

        FROM sensor_data 

        WHERE measurement = 'temperature' 

        AND recorded_at BETWEEN '2018-01-01' and '2018-12-31'

        GROUP BY year, device;

 year |   device   |  min  

------+------------+-------

 2018 | basement   | 42.00

 2018 | livingroom | 43.16

 2018 | office     | 38.30

 2018 | outside    | -7.42

 2018 | rootcellar | 34.90

 2018 | slab       | 44.06

 2018 | woodstove  | 40.82

Looks like the outside had the lowest temp in 2018, with a chilly -7.42 degrees!

Challenge: Which sensor recorded the lowest temperature 2018 and when? Hint: you need a subquery.

Well we know from above that the outside device had the lowest temp. So this works:

>>> SELECT extract(YEAR FROM recorded_at) as year, device, min(reading)

        FROM sensor_data 

        WHERE measurement = 'temperature'

	AND device = 'outside'

        AND recorded_at BETWEEN '2018-01-01' and '2018-12-31'

        GROUP BY year, device;

year | device  |  min  

------+---------+-------

 2018 | outside | -7.42

But we need the date

>>> SELECT extract(YEAR FROM recorded_at) as year, (SELECT recorded_at::date) as date, 

	device, min(reading)

        FROM sensor_data 

        WHERE measurement = 'temperature'

	AND device = 'outside'

        AND recorded_at BETWEEN '2018-01-01' and '2018-12-31'

        GROUP BY year, device, date;

^^^ this gives us every day of 2018 and the min values, not what we want, but luckily since the min value is -7.42, and you can see it right there, answer solved. However, what if we wanted the highest temp, or the lowest temp was somewhere else in the year? For that we need to do something else..

year |    date    | device  |  min  

------+------------+---------+-------

 2018 | 2018-01-01 | outside | -7.42

 2018 | 2018-01-02 | outside | -4.36

 2018 | 2018-01-03 | outside |  6.62

 2018 | 2018-01-04 | outside | -4.54

 2018 | 2018-01-05 | outside | -1.84

 2018 | 2018-01-06 | outside | -3.82

 2018 | 2018-01-07 | outside |  8.06

 2018 | 2018-01-08 | outside | 24.26

 2018 | 2018-01-09 | outside | 24.62

>>> SELECT extract(YEAR FROM recorded_at) as year, (SELECT recorded_at::date) as date, 

	device, min(reading)

        FROM sensor_data 

        WHERE measurement = 'temperature'

	AND device = 'outside'

        AND recorded_at BETWEEN '2018-01-01' and '2018-12-31'

        GROUP BY year, device, date

	ORDER by min;

^^ Ordering by min helps us see which date is the coldest. I would like to figure out how to see only the day with the minimum value. I was having trouble doing so, I think I have to execute a union function or something...

year |    date    | device  |  min  

------+------------+---------+-------

 2018 | 2018-01-01 | outside | -7.42

 2018 | 2018-01-04 | outside | -4.54

 2018 | 2018-01-02 | outside | -4.36

 2018 | 2018-01-06 | outside | -3.82

 2018 | 2018-02-05 | outside | -3.28

 2018 | 2018-01-05 | outside | -1.84

 2018 | 2018-01-14 | outside |  0.32

 2018 | 2018-02-08 | outside |  1.22

 2018 | 2018-01-13 | outside |  1.94

 2018 | 2018-02-06 | outside |  2.30

3) Write two queries that use data from your sensor.

SELECT recorded_at::date as date, extract(MINUTE FROM recorded_at) as minute, 

            device, max(reading), min(reading)

        FROM sensor_data 

        WHERE measurement = 'temperature' 

        AND device = 'sc_sensor'

        GROUP BY date, minute, device;

date    | minute |  device   |  max  |  min  

------------+--------+-----------+-------+-------

 2019-02-17 |      0 | sc_sensor | 74.66 | 72.68

 2019-02-17 |      1 | sc_sensor | 74.66 | 72.86

 2019-02-17 |      2 | sc_sensor | 74.66 | 72.50

 2019-02-17 |      3 | sc_sensor | 74.66 | 72.86

 2019-02-17 |      4 | sc_sensor | 74.66 | 72.86

 2019-02-17 |      5 | sc_sensor | 74.66 | 72.50

 2019-02-17 |      6 | sc_sensor | 74.66 | 72.50

 2019-02-17 |      7 | sc_sensor | 74.66 | 72.32

 2019-02-17 |      8 | sc_sensor | 74.66 | 72.50

 2019-02-17 |      9 | sc_sensor | 75.02 | 72.50

This gives me minute by minute max and min values, which is interesting, but I'd rather see which minute was the hottest in the hour - so I revised the query a little bit

SELECT extract(MINUTE FROM recorded_at) as minute, 

            round(avg(reading), 2) as avg 

        FROM sensor_data 

        WHERE measurement = 'temperature' 

        AND device = 'sc_sensor'

        GROUP BY minute

	ORDER BY avg desc;

minute |  avg  

--------+-------

     41 | 74.30

     40 | 74.22

     42 | 74.18

     43 | 74.15

     34 | 74.13

     44 | 74.12

     45 | 74.08

And because all the inputs are on the same day, there's no need for the date column.

MQTT

As someone who aspires to become Tony Stark. I am very excited about this class. Without devices talking to each other, we can have no exciting future. This MQTT protocol allows us to deliver messages at what feels like light speed. So that we can do things with info gathered from far away places in what feels like real time. Game changing. Many applications these days are simple monitoring with no output, but soon, we will have other machines listening to these sensors, so that they execute automated functions upon receipt of certain messages. Now that I am finally uncovering this knowledge for myself, I am so very excited for all the wondrous possibilities this opens!

My goal for the class is to create a magic wand. This will use an accelerometer and other sensors to draw in data that is sent to a central server. An app on the central server will then train LSTM models from that data, so that unique sequences can be generated by the user for any future purpose.

So the first step is to get an accelerometer to send data, as it’s the most critical component.

View fullsize

System outlining the process by which someone trains magic tricks on their magic wand. Necessarily I would need to create a front end for simple attachment of other protocols. But perhaps touch designer is good at that? I somewhat feel like if I do turn this into a product tho, it should come with its own free and easy to use software, in between touch designer and arduino.

This is the code for the accelerometer, which I did get working and sending data to the server. It was quite the headache to learn, however, that arrays are of a fixed memory size upon assignment in Arduino, meaning they cannot be added to after being created. They can only have assignments of 0. So, in order to send large data sets, I had to define an array size that made sense, and send it once I filled the array. I’m not sure if the code below is perfect, but it certainly did the trick. I put some necessary login data lines in a separate config header file.

#include <SparkFun_ADXL345.h>         // SparkFun ADXL345 Library

// MQTT headers
#include <SPI.h>
#ifdef ARDUINO_SAMD_MKR1010
#include <WiFi101.h>
#define WL_NO_MODULE WL_NO_SHIELD
#else
#include <WiFiNINA.h>
#endif
#include <ArduinoMqttClient.h>

#include "config.h"

WiFiSSLClient net;
MqttClient mqtt(net);

String accelerometerTopic = "itp/" + DEVICE_ID + "/accelerometer";

// Publish every 10 seconds for the workshop. Real world apps need this data every 5 or 10 minutes.
unsigned long publishInterval = 5 * 1000;
unsigned long lastMillis = 0;
int button = 6;
int state = false;
int xData[50];
int yData[50];
int zData[50];

//int posMoment[3];
int i = 0;

/******************** SETUP ********************/
/*          Configure ADXL345 Settings         */
void setup() {

  Serial.begin(9600);                 // Start the serial terminal
  Serial.println("Connecting WiFi");
  connectWiFi();

  pinMode(button, INPUT);

  setAxl();
  if (WiFi.status() != WL_CONNECTED) {
        connectWiFi();
      }
      
  if (!mqtt.connected()) {
      connectMQTT();
    }
}

void loop() {  

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

  if (digitalRead(button) == HIGH && state == false) {
    state = true;
    delay(200);
  }  else if (digitalRead(button) == HIGH && state == true) {
    state = false;
    delay(200);
  }

  if (state == true) {
//    // Accelerometer Readings
    int x, y, z;
    adxl.readAccel(&x, &y, &z);         // Read the accelerometer values and store them in variables declared above x,y,z
      xData[i] = x;
      yData[i] = y;
      zData[i] = z;

    i++;

  } else if (state == false) {
    Serial.println("waiting");
  }

  if (millis() - lastMillis > publishInterval) {
    lastMillis = millis();  
      
    mqtt.beginMessage(accelerometerTopic);
      Serial.println("begin message");
    for (int k = 0; k <= 49; k++){
      mqtt.print("x: ");
      mqtt.print(xData[k]);
      mqtt.print(", y: ");
      mqtt.print(yData[k]);
      mqtt.print(", z: ");
      mqtt.print(zData[k]);
      mqtt.print(" | ");
      Serial.print("x: ");
      Serial.print(xData[k]);
      Serial.print("y: ");
      Serial.print(yData[k]);
      Serial.print("z: ");
      Serial.println(zData[k]);
      xData[k] = 0;
      yData[k] = 0;
      zData[k] = 0;
    }
    Serial.println("end message");
    mqtt.endMessage();
    i = 0;
  }
  delay(100);
}

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(20);
  }

  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(200);
  }

  Serial.println("connected.");
}

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

After many trials and errors, finally figured it out! Sending data! Too large? Readable? I felt splitting the statements by | and having each direction end with : would make it a simple regex task later. Though I have never used regex, so maybe not? It might be simpler to send each direction in a separate topic, then I could just send numbers, and that would be a super easy reconstruction task in javascript, now that I think about it. And probably easier on the arduino side as well.