Published March 3, 2019 © GPL3+

Beehive Monitoring with Theft Prevention (GPS)

Beehive monitor and datalogger based on Sony Spresense with Sigfox communication and theft alert function.

IntermediateFull instructions provided16 hours2,162

Beehive Monitoring with Theft Prevention (GPS)

Things used in this project

Hardware components

Sony Spresense boards (main & extension)

SparkFun Atmospheric Sensor Breakout - BME280

Adafruit Microphone Amplifier Breakout

LPWAN SigFox Node UART Modem 868MHz

Jumper wires (generic)

Software apps and online services

Arduino IDE

Sigfox

Story

Motto: If All The Bees In The World Die, Humans Will Not Survive

My goal was to address the remote monitoring of a beehive (nevertheless the solution could be used for any kind of remote monitoring and data logging). Beehive monitoring is quite unique as there are certain limitations which must be taken into account as beehives are usually located in nature which means that:

There is no electricity source - the power consumption matters
There is no connectivity and even GSM signal is often not available
Beehives are a subject of robbery (eighter because of the honey inside or because of the bees and beehive itself)

This is why I decided to use the combination of Sony Spresense board and Sigfox network as the reliable data carrier to build a solution which fulfills all the basic requirements on remote beehive monitoring.

So how does it look like?

The prototype consists of the Sony Spresense board responsible for all the computing and detailed data logging to SD card, BME280 sensor responsible for temperature, humidity and atmospherical pressure measuring, GY MAX4466 microphone module with amplifier responsible for noise level monitoring and finally Sigfox modem responsible for the data transfer.

Beehive monitoring with theft prevention (GPS)

Sensors are recursively requested to provide the data and the result is stored to SD card each one minute. Each 15 minutes is the data sent over Sigfox too.

1 / 2 • Sony Spresense, BME280, GY MAX4466 (focused)

Let's have a closer look at the data now.

Log file

After the boot checkes the sketch for the existency of log file. It is created if it is not found and the header row is prepared. New log is appended each minute. The file is in a simple CSV format.

CSV log file taken directly from SD card and opened in Notepad

It can be easily converted into Libre Office Calc (or MS Excell, Google tables,...) spreadsheet so it can be easily analysed.

Log file opened in Lebre Office Calc for analysis

Device monitors the level of noise and each minute records the minimal, maximal and average values of the noise level detected. This could help the beekeeper to predict some events like swarming.

Sound level analysis from log file

Also the weather related values - temperature, humidity and atmospherical pressure - ale logged each minute.

Temperature, humidity and atmospherical pressure analysis from log file

Well, such logs are nice for analysis but are accessible only after the monitor is taken out from a beehive and data from SD card are transferred to PC. To solve this I engaged Sigfox modem. Sigfox network allows to transfer up to 120 messages daily (= one each 15 minutes with some contingency we need for theft alert messages) each of the size up to 12 Bytes. Plenty of bandwidth right? :-)

To save the space I am sending integer values only (and a bit coded to fit in 1 Byte each). The data message has following format:

a1::uint:8 t1::uint:8 v1::uint:8 p1::uint:8 s1::uint:8

a1 - 1 for Data message, 2 for Theft Alert message
t1 - temperature (actual temperature in degrees of Celsius +50. I just hope there wont be less than minus 50 °C ;-).
v1 - relative humidity in % - as it varies from 0 to 100 % there is no need to extra code it.
p1 - atmospherical pressure (actual pressure in hPa diminished by 885 so the average (normal) pressure of 1013 hPa is transferred as 128 giving enough space to higher and lower values).
s1 - sound level

There is also a special Theft Alert message which is sent each time the GPS position of the beehive changes significantly (the treshold can be set by a variable). It has a bit different format:

a1::uint:8 la::float:32 lo::float:32

a1 - 1 for Data message, 2 for Theft Alert message
la - last known Latitude
lo - last known Longitude

These messages are transferred over the Sigfox network and captured in Sigfox backend.

Messages as received in Sigfox backend

Sigfox backend allows to define custom callbacks - simply the data are extracted from the received message and sent to desired location in desired format. I am using REST API to receive the data on the server so the data are encoded as GET in URL form (just to be easily readable - it could be changes to POST easily).

Example of custom callback from Sigfox backend

Sigfox backend allows to define more than one callback so I am using extra callback for data message and extra one for Theft Alert message.

Two different custom callbacks from Sigfox backend

Data are currently stored at iTRUBEC (iDRONE) server and visualised on this page:

http://itrubec.cz/cs/2-itrubec-cz/28-sony-spresense-sigfox-based-beehive-monitoring

In case REST API receives Theft Alert message it immedialelly alerts the owner via SMS sent via SMS over e-mail service:

Theft alert SMS (screenshot from mobile)

In parallel it sends and e-mail with the last known position of the device...

Theft alert e-mail with last known GPS position

...so it could be easily located on the map:

GPS position from the above Theft alert visualised on map

Issues

As well as my colleagues in the discussion related to the contest Make it better with Sony I experienced that the GNSS become to be unusable when there is SD card inserted to the slot. With SD card in it does not fix the position even outdoors. It takes several minutes to get the correct time - oscillating between 0 and two satelites.

This means that the user must decide if to collect the detailed log or omit logging and use the Theft alert & location function instead.

I hope this "feature" will be fixed in next release of the board as is really lowers the value of it.

What next?

As my work on the field of beehive monitors is never to be finished I would like to extend this prototype with a bunch of additional sensors, collect the data from a number of beehives during the year and analyse them using IBM Watson analytical capabilities.

Code

long lastJob1s = 0, lastJob5s = 0, lastJob10s = 0, lastJob30s = 0, lastJob1min = 0, lastJob5min = 0, lastJob15min = 0;
float myTemp, myPres, myHumi, myLon, myLat, myLastLon, myLastLat, myLonChange, myLatChange; //my variables
char zprava[12]; //SigFox message
char myDate[15], myTime[15]; // Date and Time
int myYear = 1980; // Year - used just to determine if the actual time has been retrieved from GNSS
int t1, v1, p1, s1; //SigFox variables
int myZvuk = 0, myZvukSum = 0,  myZvukCount = 0, myZvukAVG = 0, myZvukMin = 1024, myZvukMax = 0;
const int sampleWindow = 50; // Sample window width in mS (50 mS = 20Hz)
unsigned int sample;

// libraries
#include <Wire.h>
#include <SPI.h>
#include <Adafruit_Sensor.h>
#include <Adafruit_BME280.h>
#include <SDHCI.h>
#include <GNSS.h>

// BME280 sensor address setup
#define BME280_ADDRESS (0x76)
// initialization of BME280 sensor
Adafruit_BME280 bme;

//SD card
SDClass SD;
File myLogFile;

const float myPosChangeTreshold = 0.00001; //If position changes more, alert is triggered

#define STRING_BUFFER_SIZE  128       /**< %Buffer size */
#define RESTART_CYCLE       (60 * 5)  /**< positioning test term */
static SpGnss Gnss;

enum ParamSat {
  eSatGps,            /**< GPS                     World wide coverage  */
  eSatGlonass,        /**< GLONASS                 World wide coverage  */
  eSatGpsSbas,        /**< GPS+SBAS                North America        */
  eSatGpsGlonass,     /**< GPS+Glonass             World wide coverage  */
  eSatGpsQz1c,        /**< GPS+QZSS_L1CA           East Asia & Oceania  */
  eSatGpsGlonassQz1c, /**< GPS+Glonass+QZSS_L1CA   East Asia & Oceania  */
  eSatGpsQz1cQz1S,    /**< GPS+QZSS_L1CA+QZSS_L1S  Japan                */
};

/* Set this parameter depending on your current region. */
static enum ParamSat satType =  eSatGpsGlonass;
//static enum ParamSat satType =  eSatGps;

static void Led_isActive(void)
{
  static int state = 1;
  if (state == 1)
  {
    ledOn(PIN_LED0);
    state = 0;
  }
  else
  {
    ledOff(PIN_LED0);
    state = 1;
  }
}

static void Led_isPosfix(bool state)
{
  if (state)
  {
    ledOn(PIN_LED1);
  }
  else
  {
    ledOff(PIN_LED1);
  }
}

static void Led_isError(bool state)
{
  if (state)
  {
    ledOn(PIN_LED3);
  }
  else
  {
    ledOff(PIN_LED3);
  }
}

int Correct(int nr) {
  //pripadne orezan hodnoty na 0 az 255
  if (nr < 0) nr = 0;
  if (nr > 255) nr = 255;
  return nr;
}

// SETUP-SETUP-SETUP-SETUP-SETUP-SETUP-SETUP-SETUP-SETUP-SETUP-SETUP-SETUP-SETUP-SETUP-SETUP-SETUP-SETUP-SETUP-SETUP-
void setup() {
  pinMode(LED0, OUTPUT); //BME280 in operation
  pinMode(LED1, OUTPUT); //SD card in operation
  pinMode(LED2, OUTPUT);
  pinMode(LED3, OUTPUT); //Time synced

  int error_flag = 0;

  Serial.begin(115200);
  Serial2.begin(9600);

  /* Wait HW initialization done. */
  sleep(3);

  Serial.println("Setup");
  // POSITIONING SYSTEM INI BEGIN
  /* Set Debug mode to Info */
  Gnss.setDebugMode(PrintInfo);

  int result;

  /* Activate GNSS device */
  result = Gnss.begin();

  if (result != 0)
  {
    Serial.println("Gnss begin error!!");
    error_flag = 1;
  }
  else
  {
    switch (satType)
    {
      case eSatGps:
        Gnss.select(GPS);
        break;

      case eSatGpsSbas:
        Gnss.select(GPS);
        Gnss.select(SBAS);
        break;

      case eSatGlonass:
        Gnss.select(GLONASS);
        break;

      case eSatGpsGlonass:
        Gnss.select(GPS);
        Gnss.select(GLONASS);
        break;

      case eSatGpsQz1c:
        Gnss.select(GPS);
        Gnss.select(QZ_L1CA);
        break;

      case eSatGpsQz1cQz1S:
        Gnss.select(GPS);
        Gnss.select(QZ_L1CA);
        Gnss.select(QZ_L1S);
        break;

      case eSatGpsGlonassQz1c:
      default:
        Gnss.select(GPS);
        Gnss.select(GLONASS);
        Gnss.select(QZ_L1CA);
        break;
    }

    /* Start positioning */
    result = Gnss.start(COLD_START);
    if (result != 0)
    {
      Serial.println("Gnss start error!!");
      error_flag = 1;
    }
    else
    {
      Serial.println("Gnss setup OK");
    }
  }

  /* Turn off all LED:Setup done. */
  ledOff(PIN_LED0);
  ledOff(PIN_LED1);
  ledOff(PIN_LED2);
  ledOff(PIN_LED3);

  /* Set error LED. */
  if (error_flag == 1)
  {
    Led_isError(true);
    exit(0);
  }
  // POSITIONING SYSTEM INI END

  // initialization of communication with BME280 sensor
  if (!bme.begin(BME280_ADDRESS)) {
    Serial.println("BME280 sensor not found! Check wiring!");
    while (1);
  }

  // Check if Log file exists (initialize if does not exist yet)
  digitalWrite(LED1, HIGH);
  myLogFile = SD.open("iTrubecLog.csv");
  if (myLogFile) {
    Serial.println("Log file iTrubecLog.csv exists already.");
    myLogFile.close();
    digitalWrite(LED1, LOW);
  } else {
    // Log file does not exist - will be created and header row will be written into it
    myLogFile.close();
    Serial.println("Initializing log file iTrubecLog.csv.");
    myLogFile = SD.open("iTrubecLog.csv", FILE_WRITE);
    if (myLogFile) {
      Serial.print("Writing header row to log file iTrubecLog.csv on SD card...");
      myLogFile.println("Date;Time;DateTime;Temperature;Humidity;Atm.Pressure;Longitude;Latitude;AVG Sound level;MIN Sound level;MAX Sound level");
      myLogFile.close();
      Serial.println("...done.");
    } else {
      /* If the file didn't open, print an error */
      Serial.println("error opening log file iTrubecLog.csv");
    }
  } digitalWrite(LED1, LOW);

} // setup konec

static void get_date_time(SpNavData *pNavData)
{
  /* get year */
  myYear = pNavData->time.year;
  if (myYear > 1980){ledOn(PIN_LED3);}else{ledOff(PIN_LED3);}
  /* get date */
  snprintf(myDate, 15, "%04d.%02d.%02d", pNavData->time.year, pNavData->time.month, pNavData->time.day);
  /* get time */
  snprintf(myTime, 15, "%02d:%02d:%02d", pNavData->time.hour, pNavData->time.minute, pNavData->time.sec);
}

/**
   @brief %Print position information.
*/
static void print_pos(SpNavData *pNavData)
{
  char StringBuffer[STRING_BUFFER_SIZE];

  /* print time */
  snprintf(StringBuffer, STRING_BUFFER_SIZE, "%04d/%02d/%02d ", pNavData->time.year, pNavData->time.month, pNavData->time.day);
  Serial.print(StringBuffer);

  snprintf(StringBuffer, STRING_BUFFER_SIZE, "%02d:%02d:%02d.%06d, ", pNavData->time.hour, pNavData->time.minute, pNavData->time.sec, pNavData->time.usec);
  Serial.print(StringBuffer);

  /* print satellites count */
  snprintf(StringBuffer, STRING_BUFFER_SIZE, "numSat:%2d, ", pNavData->numSatellites);
  Serial.print(StringBuffer);

  /* print position data */
  if (pNavData->posFixMode == FixInvalid)
  {
    Serial.print("No-Fix, ");
  }
  else
  {
    Serial.print("Fix, ");
  }
  if (pNavData->posDataExist == 0)
  {
    Serial.print("No Position");
    myLat = 0;
    myLon = 0;
    myLastLat = 0;
    myLastLon = 0;
  }
  else
  {
    Serial.print("Lat=");
    myLat = (pNavData->latitude, 6);
    Serial.print(myLat);
    Serial.print(", Lon=");
    myLon = (pNavData->longitude, 6);
    Serial.print(myLon);
  }

  Serial.println("");
}

/**
   @brief %Print satellite condition.
*/
static void print_condition(SpNavData *pNavData)
{
  char StringBuffer[STRING_BUFFER_SIZE];
  unsigned long cnt;

  /* Print satellite count. */
  snprintf(StringBuffer, STRING_BUFFER_SIZE, "numSatellites:%2d\n", pNavData->numSatellites);
  Serial.print(StringBuffer);

  for (cnt = 0; cnt < pNavData->numSatellites; cnt++)
  {
    const char *pType = "---";
    SpSatelliteType sattype = pNavData->getSatelliteType(cnt);

    /* Get satellite type. */
    /* Keep it to three letters. */
    switch (sattype)
    {
      case GPS:
        pType = "GPS";
        break;

      case GLONASS:
        pType = "GLN";
        break;

      case QZ_L1CA:
        pType = "QCA";
        break;

      case SBAS:
        pType = "SBA";
        break;

      case QZ_L1S:
        pType = "Q1S";
        break;

      default:
        pType = "UKN";
        break;
    }

    /* Get print conditions. */
    unsigned long Id  = pNavData->getSatelliteId(cnt);
    unsigned long Elv = pNavData->getSatelliteElevation(cnt);
    unsigned long Azm = pNavData->getSatelliteAzimuth(cnt);
    float sigLevel = pNavData->getSatelliteSignalLevel(cnt);

    /* Print satellite condition. */
    snprintf(StringBuffer, STRING_BUFFER_SIZE, "[%2d] Type:%s, Id:%2d, Elv:%2d, Azm:%3d, CN0:", cnt, pType, Id, Elv, Azm );
    Serial.print(StringBuffer);
    Serial.println(sigLevel, 6);
  }
}

void SampleSound() {
  unsigned long startMillis = millis(); // Start of sample window
  unsigned int peakToPeak = 0;   // peak-to-peak level

  unsigned int signalMax = 0;
  unsigned int signalMin = 1024;

  // collect data for 50 ms
  while (millis() - startMillis < sampleWindow)
  {
    sample = analogRead(A0);
    if (sample < 1024)  // toss out spurious readings
    {
      if (sample > signalMax)
      {
        signalMax = sample;  // save just the max levels
      }
      else if (sample < signalMin)
      {
        signalMin = sample;  // save just the min levels
      }
    }
  }
  peakToPeak = signalMax - signalMin;  // max - min = peak-peak amplitude
  myZvuk = peakToPeak;
}

// LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-LOOP-
void loop() {

  // LOOP-BLOCK-5s-LOOP-BLOCK-5s-LOOP-BLOCK-5s-LOOP-BLOCK-5s-LOOP-BLOCK-5s-LOOP-BLOCK-5s-LOOP-BLOCK-5s-LOOP-BLOCK-5s-LOOP-BLOCK-5s-LOOP-BLOCK-5s-LOOP-BLOCK-5s-LOOP-BLOCK-5s-
  if (millis() > (5000 + lastJob5s))
  {
    // runs every 5 seconds (5000 ms)
    static int LoopCount = 0;
    static int LastPrintMin = 0;

    /* Blink LED. */
    Led_isActive();

    /* Check update. */
    if (Gnss.waitUpdate(-1))
    {
      /* Get NaviData. */
      SpNavData NavData;
      Gnss.getNavData(&NavData);

      /* Set posfix LED. */
      bool LedSet = (NavData.posDataExist && (NavData.posFixMode != FixInvalid));
      Led_isPosfix(LedSet);

      /* Print satellite information every minute. */
      if (NavData.time.minute != LastPrintMin)
      {
        print_condition(&NavData);
        LastPrintMin = NavData.time.minute;
      }

      /* Print position information. */
      print_pos(&NavData);
      //update stored date and time
      get_date_time(&NavData);
    }
    else
    {
      /* Not update. */
      Serial.println("data not updated");
    }

    /* Check loop count. */
    LoopCount++;
    if (LoopCount >= RESTART_CYCLE)
    {
      int error_flag = 0;

      /* Turn off LED0 */
      ledOff(PIN_LED0);

      /* Set posfix LED. */
      Led_isPosfix(false);

      /* Restart GNSS. */
      if (Gnss.stop() != 0)
      {
        Serial.println("Gnss stop error!!");
        error_flag = 1;
      }
      else if (Gnss.end() != 0)
      {
        Serial.println("Gnss end error!!");
        error_flag = 1;
      }
      else
      {
        Serial.println("Gnss stop OK.");
      }

      if (Gnss.begin() != 0)
      {
        Serial.println("Gnss begin error!!");
        error_flag = 1;
      }
      else if (Gnss.start(HOT_START) != 0)
      {
        Serial.println("Gnss start error!!");
        error_flag = 1;
      }
      else
      {
        Serial.println("Gnss restart OK.");
      }

      LoopCount = 0;

      /* Set error LED. */
      if (error_flag == 1)
      {
        Led_isError(true);
        exit(0);
      }
    }

    SampleSound();
    Serial.print("Sound level: ");
    Serial.println(myZvuk);
    myZvukSum = myZvukSum + myZvuk;
    myZvukCount = myZvukCount + 1;
    if (myZvuk < myZvukMin) {
      myZvukMin = myZvuk;
      if (myZvukMin < 0) { //time to time is negative value returned - this is to artificially fix it as it is a nonsence
        myZvukMin = 0;
      }
    }
    if (myZvuk > myZvukMax) {
      myZvukMax = myZvuk;
    }

    lastJob5s = millis();
  } // 5s end

  // LOOP-BLOCK-1min-LOOP-BLOCK-1min-LOOP-BLOCK-1min-LOOP-BLOCK-1min-LOOP-BLOCK-1min-LOOP-BLOCK-1min-LOOP-BLOCK-1min-LOOP-BLOCK-1min-LOOP-BLOCK-1min-LOOP-BLOCK-1min
  if (millis() > (60000 + lastJob1min))
  {
    // runs every minute (60000 ms)
    //Serial.println("1min");
    //Average level of sound
    myZvukAVG = myZvukSum / myZvukCount;
    myZvukSum = 0;
    myZvukCount = 0;
    // Read all data from BME280
    Serial.println();
    digitalWrite(LED0, HIGH);
    // Temperature
    Serial.print("Temperature  :   ");
    myTemp = bme.readTemperature();
    Serial.print(myTemp);
    Serial.println(" degrees of Celsius");
    // Humidity
    Serial.print("Rel. humidity:   ");
    myHumi = bme.readHumidity();
    Serial.print(myHumi);
    Serial.println(" %");
    // Atmospherical pressure
    Serial.print("Pressure     : ");
    myPres = bme.readPressure() / 100.0F + 26; //26 is correction for concrete sensor
    Serial.print(myPres);
    Serial.println(" hPa");
    Serial.print("Sound lvl - AVG: ");
    Serial.print(myZvukAVG);
    Serial.print(", MIN: ");
    Serial.print(myZvukMin);
    Serial.print(", MAX: ");
    Serial.println(myZvukMax);
    Serial.println();
    digitalWrite(LED0, LOW);

    // initialization of SD card and writing the log
    digitalWrite(LED1, HIGH);
    if (myYear > 1980) { // GNSS time retrieved
      myLogFile = SD.open("iTrubecLog.csv", FILE_WRITE);
      if (myLogFile) {
        Serial.print("Writing log to SD card - iTrubecLog.csv...");
        myLogFile.print(myDate);
        myLogFile.print(";");
        myLogFile.print(myTime);
        myLogFile.print(";");
        myLogFile.print(myDate);
        myLogFile.print(" ");
        myLogFile.print(myTime);
        myLogFile.print(";");
        myLogFile.print(myTemp);
        myLogFile.print(";");
        myLogFile.print(myHumi);
        myLogFile.print(";");
        myLogFile.print(myPres);
        myLogFile.print(";");
        myLogFile.print(myLon);
        myLogFile.print(";");
        myLogFile.print(myLat);
        myLogFile.print(";");
        myLogFile.print(myZvukAVG);
        myLogFile.print(";");
        myLogFile.print(myZvukMin);
        myZvukMin = 1024;
        myLogFile.print(";");
        myLogFile.print(myZvukMax);
        myZvukMax = 0;
        myLogFile.println("");
        myLogFile.close();
        Serial.println("...done.");
      } else {
        /* If the file didn't open, print an error */
        Serial.println("error opening log file iTrubecLog.csv");
      }
    } else {
      Serial.println("Actual time not retrieved -> no log recorded (waiting for sync).");
    }
    digitalWrite(LED1, LOW);
    Serial.println();

    //position change check
    myLonChange = abs(myLastLon - myLon);
    myLatChange = abs(myLastLat - myLat);
    myLastLat = myLat;
    myLastLon = myLon;
    //Debug print
    Serial.println();
    Serial.print("Lat: ");
    Serial.print(myLat);
    Serial.print(" ==>Absolute change: ");
    Serial.println(myLatChange);
    Serial.print("Lon: ");
    Serial.print(myLon);
    Serial.print(" ==>Absolute change: ");
    Serial.println(myLonChange);
    Serial.println();
    //Check if position was changed
    if ((myPosChangeTreshold < myLonChange) || (myPosChangeTreshold < myLatChange)) {
      //POSITION CHANGE ALERT!
      Serial.println();
      Serial.println("POSITION CHANGED");
      //alert message
      Serial.println("SigFox modem link init - ALERT mode");
      //prepare sigfox alert message
      sprintf(zprava, "%02X%04X%04X", 2, myLat, myLon);
      Serial.print("Sigfox Alert message: ");
      Serial.println(zprava);
      //Sending SigFox message
      Serial2.print("AT$SF=");
      Serial2.println(zprava);
      Serial.println("Alert message sent");
      Serial.println("--------------------------------------");
      delay(1000);
      Serial.println("SigFox Alert communication is over");
      Serial.println();
    }

    lastJob1min = millis();
  } // 1min end

  // LOOP-BLOCK-15min-LOOP-BLOCK-15min-LOOP-BLOCK-15min-LOOP-BLOCK-15min-LOOP-BLOCK-15min-LOOP-BLOCK-15min-LOOP-BLOCK-15min-LOOP-BLOCK-15min-LOOP-BLOCK-15min-LOOP-BLOCK-15min-
  if (millis() > (900000 + lastJob15min))
  {
    // runs each 15th minute (900000 ms)
    Serial.println("SigFox modem link init - Data mode");
    // SigFox message
    // Temp rounded to integer plus 50 (50 => 0 degrees of celsius)
    t1 = int(round(myTemp) + 50);
    t1 = Correct(t1);
    // humidity rounded to integer
    v1 = int(round(myHumi));
    v1 = Correct(v1);
    // pressure rounded to integer and diminished by 885 (1013-128) => 128 is 1013 hPa
    p1 = int(round(myPres) - 885);
    p1 = Correct(p1);
    // sound rounded to integer
    s1 = int(round(myZvukAVG));
    s1 = Correct(s1);

    //prepare sigfox data message
    sprintf(zprava, "%02X%02X%02X%02X%02X", 1, t1, v1, p1, s1);
    Serial.print("Sigfox data message: ");
    Serial.println(zprava);
    //Sending SigFox message
    Serial2.print("AT$SF=");
    Serial2.println(zprava);
    Serial.println("Data message sent");
    Serial.println("--------------------------------------");
    delay(1000);
    Serial.println("SigFox Data communication is over");
    Serial.println();

    lastJob15min = millis();
  } // 15min end

  // LOOP-BLOCK-LOOP-BLOCK-LOOP-BLOCK-LOOP-BLOCK-LOOP-BLOCK-LOOP-BLOCK-LOOP-BLOCK-LOOP-BLOCK-LOOP-BLOCK-LOOP-BLOCK-LOOP-BLOCK-LOOP-BLOCK-LOOP-BLOCK-LOOP-BLOCK-LOOP-BLOCK-
} // loop end

<?php
/*
REST API for iTRUBEC SigFox monitor based on SONY Spresense

Message Types:
DATA:  $a1 == 1  --> temperature, humidity, atmospherical pressure, sound level
ALERT: $a1 == 2  --> position (latitude & longitude)

SigFox DATA message format
a1::uint:8 t1::uint:8 v1::uint:8 p1::uint:8 s1::uint:8
example: 014B316A65

SigFox DATA Callback Format
http://itrubec.cz/monitor/REST_API.php?key=xxxxxxx&dev={device}&t1={customData#t1}&v1={customData#v1}&p1={customData#p1}&s1={customData#s1}&a1={customData#a1}&rssi={rssi}&seqn={seqNumber}&lat={lat}&lng={lng}&snr={snr}&avgsnr={avgSnr}

GET DATA callback example
http://itrubec.cz/monitor/REST_API.php?key=xxxxxx&dev=xxxxxx&t1=75&v1=49&p1=106&s1=86&a1=1&rssi=-102.00&seqn=4&lat=49.0&lng=17.0&snr=25.83&avgsnr=23.77

SigFox ALERT message format
a1::uint:8 la::float:32 lo::float:32
example:

SigFox ALERT Callback Format
http://itrubec.cz/monitor/REST_API.php?key=xxxxxxx&dev={device}&la={customData#la}&lo={customData#lo}&a1={customData#a1}&rssi={rssi}&seqn={seqNumber}&lat={lat}&lng={lng}&snr={snr}&avgsnr={avgSnr}

GET ALERT callback example
http://itrubec.cz/monitor/REST_API.php?key=xxxxxxx&dev=xxxxxxxx&la=49.21095&lo=16.5760356&a1=2&rssi=-102.00&seqn=4&lat=49.0&lng=17.0&snr=25.83&avgsnr=23.77
*/

$_GET   = filter_input_array(INPUT_GET, FILTER_SANITIZE_STRING);

$servername = "xxx";   //jméno serveru
$username = "xxx";       //DB login
$password = "xxx";//DB psswd
$dbname = "xxx";       //DB name

// Create connection
$mysqli =  new mysqli($servername, $username, $password, $dbname);

/* check connection */
if ($mysqli->connect_errno) {
    printf("Connect failed: %s\n", $mysqli->connect_error);
    exit();
}

$key = $_GET["key"];
$dev = $_GET["dev"];
$t1 = $_GET["t1"];
$v1 = $_GET["v1"];
$p1 = $_GET["p1"];
$s1 = $_GET["s1"];
$a1 = $_GET["a1"];
$rssi = $_GET["rssi"];
$seqn = $_GET["seqn"];
$lat = $_GET["lat"];
$lng = $_GET["lng"];
$la = $_GET["la"];
$lo = $_GET["lo"];
$snr = $_GET["snr"];
$avgsnr = $_GET["avgsnr"];

$t1 = $t1 - 50;
$p1 = $p1 + 885;

//SELECT ID FROM `monitor_devices` WHERE devkey like "xxxxxx" AND device like "xxxxx"
$sql = "SELECT ID FROM `monitor_devices` WHERE devkey like \"".$key."\" AND device like \"".$dev."\"";
if ($result = $mysqli->query($sql)) {
    printf("%d record found.\n", $result->num_rows);
    $row=mysqli_fetch_row($result);
    $devid = $row[0];
    $result->close();
    $sql = "UPDATE `monitor_devices` SET `lastactivity`=now() WHERE `id`=".$devid;
    echo("<br>");
    echo($sql);
    echo("<br>");
    if ($mysqli->query($sql) === TRUE) {
      printf("Last seen timestamp updated.<br>\n");
    }
}else{
   die("Bad key or bad device!");
}

if ($a1 == 1){  //data call
  $sql = "INSERT INTO `monitor_data`(`devid`, `timestamp`, `temp1`, `humidity1`, `pressure1`, `rssi`, `seqn`, `lat`, `lng`, `snr`, `avgsnr`, `TheftAlert`, `Sound`) VALUES ($devid, now(),$t1,$v1,$p1,$rssi,$seqn,$lat,$lng,$snr,$avgsnr,0,$s1)";
  echo("<br>");
  echo($sql);
  echo("<br>");
  if ($result = $mysqli->query($sql)) {
      echo "OK<br>";
  } else {
      echo "Error" . $sql;
      echo("<br>");
  }
}

if ($a1 == 2){  //Theft Alert call
  //SMS alert!
   $to = "+420xxxxxxxxxxx@sms.cz.o2.com";
   $subject = "Theft Alert";
   $txt = "Motion detected at beehive #".$dev."\nLat: ".$la."\nLon: ".$lo;
   $headers = 'From: info@itrubec.cz' . "\r\n" .
    'Reply-To: info@itrubec.cz' . "\r\n" .
    'Cc: info@itrubec.cz' . "\r\n" .
    'X-Mailer: PHP/' . phpversion();
   mail($to,$subject,$txt,$headers);

   $sql = "INSERT INTO `monitor_data`(`devid`, `timestamp`, `rssi`, `seqn`, `lat`, `lng`, `snr`, `avgsnr`, `TheftAlert`) VALUES ($devid, now(),$rssi,$seqn,$la,$lo,$snr,$avgsnr,1)";
   echo("<br>");
   echo($sql);
   echo("<br>");
  if ($result = $mysqli->query($sql)) {
      echo "OK<br>";
  } else {
      echo "Error" . $sql;
      echo("<br>");
  }
}

$mysqli->close();

?>

Credits

Vilém Kužel

7 projects • 22 followers

https://www.linkedin.com/in/vilemkuzel/

Beehive Monitoring with Theft Prevention (GPS)

Things used in this project

Hardware components

Software apps and online services

Story

So how does it look like?

Log file

Issues

What next?

Schematics

Wiring diagram

Code

SONY-Spresense-Beehive-Monitor.ino

REST_API.php

Credits

Vilém Kužel

Comments

Embed the widget on your own site

Beehive Monitoring with Theft Prevention (GPS)

Beehive Monitoring with Theft Prevention (GPS)

Things used in this project

Hardware components

Software apps and online services

Story

So how does it look like?

Log file

Issues

What next?

Schematics

Wiring diagram

Code

SONY-Spresense-Beehive-Monitor.ino

REST_API.php

Credits

Vilém Kužel

Comments

Related channels and tags