• Author: Sebastian-Nicolae Mătușa
• E-mail: sebastian.matusa@stud.acs.upb.ro
• Master: MPI

Heltec WiFi LoRa32(V2)-F

• Master Chip: ESP32(240MHz Tensilica LX6 dual-core+1 ULP, 600 DMIPS)
• LoRa Chipset: SX1276/SX1278
• Frequency: 863~923MHz
• Wi-Fi: 802.11 b/g/n (802.11n up to 150Mbps)
• Interface: MicroUSB x 1; LoRa Antenna interface(IPEX) x 1; 18 x 2.54 pin x 2
• Display Size: 0.96-inch OLED

#include "LoRaWan_APP.h"
#include <WiFi.h>
#include <set> 

The First library provides the functions and definitions needed to work with LoRaWAN, a protocol for low-power, wide-area networks. The second one is used for managing WiFi connections on the device and the last one provides the std::set container, which is used to store unique elements in an ordered manner.

/* OTAA para */
uint8_t devEui[] = { hidden };
uint8_t appEui[] = { hidden };
uint8_t appKey[] = { hidden };

/* LoraWan region, select in Arduino IDE tools */
LoRaMacRegion_t loraWanRegion = ACTIVE_REGION;   //868

/* LoraWan Class, Class A and Class C are supported */
DeviceClass_t loraWanClass = CLASS_A;

These are the parameters for Over-The-Air Activation (OTAA) in LoRaWAN:

• devEui: Device EUI (unique identifier for the device).
• appEui: Application EUI (identifier for the application).
• appKey: Application key used for encryption.

• loraWanRegion: Specifies the LoRaWAN region. This program uses a placeholder ACTIVE_REGION, which should be set in the Arduino IDE tools to match the regional frequency plan (e.g., EU868 for Europe).
• loraWanClass: Specifies the LoRaWAN class. Class A is the most common and energy-efficient class, where the device can only receive messages in short windows after it has transmitted a message.

/* Prepares the payload of the frame */
static void prepareTxFrame(uint8_t port)
{
    appDataSize = 0; // Reset the payload size

    int n = WiFi.scanNetworks();
    Serial.println("scan done");

    if (n == 0) {
        Serial.println("no networks found");
        appData[appDataSize++] = 0x00; // Indicate no networks found
    } else {
        Serial.print(n);
        Serial.println(" networks found");

        std::set<String> ssidSet; // To store unique SSIDs

        for (int i = 0; i < n && ssidSet.size() < 5; ++i) {
            String ssid = WiFi.SSID(i);

            // Add unique SSID to the set
            if (ssidSet.find(ssid) == ssidSet.end()) {
                ssidSet.insert(ssid);
            }
        }

        // Add SSIDs to the payload
        for (const auto& ssid : ssidSet) {
            uint8_t ssidLength = ssid.length();

            // Ensure there is enough space in the buffer for the length byte and the SSID characters
            if (appDataSize + ssidLength + 1 > LORAWAN_APP_DATA_MAX_SIZE) {
                break;
            }

            appData[appDataSize++] = ssidLength; // Store SSID length
            for (int j = 0; j < ssidLength; ++j) {
                appData[appDataSize++] = ssid[j];
            }

            // Log the SSID being added
            Serial.print("SSID: ");
            Serial.print(ssid);
            Serial.print(" Length: ");
            Serial.println(ssidLength);
        }
    }
}

This function prepares the data to be sent over LoRaWAN:

• It resets the payload size (appDataSize).
• Scans for available WiFi networks and prints the number found.
• If no networks are found, it adds a byte to the payload to indicate this.
• If networks are found, it stores up to 5 unique SSIDs in a set.
• For each unique SSID, it adds the length of the SSID and the SSID itself to the payload, ensuring it does not exceed the maximum data size.

void setup() {
  Serial.begin(115200);
  Mcu.begin(HELTEC_BOARD, SLOW_CLK_TPYE);
  WiFi.mode(WIFI_STA);
  WiFi.disconnect();

  if (firstrun) {
    LoRaWAN.displayMcuInit();
    firstrun = false;
  }
}

The setup function initializes the device:

• Initializes serial communication for debugging with a baud rate of 115200.
• Initializes the microcontroller (MCU) with specific board settings.
• Sets the WiFi mode to station mode (WIFI_STA) and disconnects from any previous WiFi connections.
• Displays MCU initialization information on the first run using the LoRaWAN.displayMcuInit function.

void loop()
{
    switch (deviceState)
    {
    case DEVICE_STATE_INIT:
    {
#if (LORAWAN_DEVEUI_AUTO)
        LoRaWAN.generateDeveuiByChipID();
#endif
        LoRaWAN.init(loraWanClass, loraWanRegion);
        // both set join DR and DR when ADR off
        LoRaWAN.setDefaultDR(3);
        break;
    }
    case DEVICE_STATE_JOIN:
    {
        LoRaWAN.displayJoining();
        LoRaWAN.join();
        break;
    }
    case DEVICE_STATE_SEND:
    {
        LoRaWAN.displaySending();
        prepareTxFrame(appPort);
        LoRaWAN.send();
        deviceState = DEVICE_STATE_CYCLE;
        break;
    }
    case DEVICE_STATE_CYCLE:
    {
        // Schedule next packet transmission
        txDutyCycleTime = appTxDutyCycle + randr(-APP_TX_DUTYCYCLE_RND, APP_TX_DUTYCYCLE_RND);
        LoRaWAN.cycle(txDutyCycleTime);
        deviceState = DEVICE_STATE_SLEEP;
        break;
    }
    case DEVICE_STATE_SLEEP:
    {
        LoRaWAN.displayAck();
        LoRaWAN.sleep(loraWanClass);
        break;
    }
    default:
    {
        deviceState = DEVICE_STATE_INIT;
        break;
    }
    }
}

The loop function handles the main state machine for the device:

• DEVICE_STATE_INIT: Initializes LoRaWAN settings and the device.
• DEVICE_STATE_JOIN: Attempts to join the LoRaWAN network and displays the joining process.
• DEVICE_STATE_SEND: Prepares the transmission frame using prepareTxFrame, sends the data, and then moves to the cycle state.
• DEVICE_STATE_CYCLE: Schedules the next packet transmission by calculating a random delay and sets the device to sleep.
• DEVICE_STATE_SLEEP: Displays acknowledgment status and puts the device to sleep.
• default: Resets the state to initialization if an unknown state is encountered.

// Decode an uplink message from a buffer
// port: the port number
// bytes: the payload as a byte array
function decodeUplink(input) {
  var bytes = input.bytes;
  var port = input.fPort;
  var ssids = [];
  var i = 0;

  while (i < bytes.length) {
    var ssidLength = bytes[i];
    i += 1;

    if (ssidLength === 0 || i + ssidLength > bytes.length) {
      break;
    }

    var ssid = "";
    for (var j = 0; j < ssidLength; j++) {
      ssid += String.fromCharCode(bytes[i + j]);
    }
    ssids.push(ssid);
    i += ssidLength;
  }

  // Return the decoded values in an object
  return {
    data: {
      ssids: ssids
    }
  };
}

The purpose of this TTN (The Things Network) decoder function is to interpret the payload received from the device. This payload contains WiFi SSIDs detected by the device. Here’s a breakdown of what the function does:

Inputs: The function takes an input object containing two properties:

• bytes: A byte array representing the payload.
• fPort: The port number (not used in this function).

It initializes an empty array ssids to store the SSIDs and a variable i to keep track of the current position in the byte array.

The function enters a while loop that iterates through the bytes array:

• It reads the first byte to determine the length of the next SSID (ssidLength).
• If ssidLength is zero or if there aren’t enough bytes left in the array to read the entire SSID, it breaks the loop.
• It then constructs the SSID string by reading the next ssidLength bytes and converting them from character codes to a string.
• The SSID is added to the ssids array, and the position index i is updated to move past the read bytes.
• Return: The function returns an object with a data property containing the ssids array, which holds all the decoded SSIDs.

var SHEET_NAME = "Sheet1";

var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

function doGet(e) {
  return handleResponse(e);
}

function doPost(e) {
  return handleResponse(e);
}

function handleResponse(e) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var data = JSON.parse(e.postData.contents);

  if (sheet.getLastRow() === 0) {
    sheet.appendRow([
      'Device ID',
      'Application ID',
      'Device EUI',
      'Device Address',
      'Received At',
      'Port',
      'Payload',
      'SSIDs',
      'Gateway ID',
      'EUI',
      'RSSI',
      'SNR',
      'Latitude',
      'Longitude',
      'Altitude'
    ]);
  }

  var rowData = [];

  rowData.push(data.end_device_ids.device_id);
  rowData.push(data.end_device_ids.application_ids.application_id);
  rowData.push(data.end_device_ids.dev_eui);
  rowData.push(data.end_device_ids.dev_addr);
  rowData.push(data.received_at);
  rowData.push(data.uplink_message.f_port);
  rowData.push(data.uplink_message.frm_payload);

  // Push SSIDs into a single cell
  var decodedPayload = data.uplink_message.decoded_payload;
  var ssidList = decodedPayload.ssids.join(", ");

  // Check if SSID list already exists for the same device and timestamp
  var lastRow = sheet.getLastRow();
  var existingSSID = false;

  for (var i = 1; i <= lastRow; i++) {
    var deviceId = sheet.getRange(i, 1).getValue();
    var receivedAt = sheet.getRange(i, 5).getValue();
    var ssids = sheet.getRange(i, 8).getValue();
    if (deviceId === data.end_device_ids.device_id && receivedAt === data.received_at && ssids === ssidList) {
      existingSSID = true;
      break;
    }
  }

  if (!existingSSID) {
    rowData.push(ssidList);

    // Append the SSID data once
    sheet.appendRow(rowData);
  }

  // Append the gateway data for each gateway
  for (var i = 0; i < data.uplink_message.rx_metadata.length; i++) {
    var gateway = data.uplink_message.rx_metadata[i];
    var gatewayRowData = rowData.slice(0, 7);  // Make a copy of the base rowData without the SSID list

    gatewayRowData.push(''); // Empty cell for SSIDs
    gatewayRowData.push(gateway.gateway_ids.gateway_id);
    gatewayRowData.push(gateway.gateway_ids.eui);
    gatewayRowData.push(gateway.rssi);
    gatewayRowData.push(gateway.snr);
    gatewayRowData.push(gateway.location.latitude);
    gatewayRowData.push(gateway.location.longitude);
    gatewayRowData.push(gateway.location.altitude);

    sheet.appendRow(gatewayRowData);  // Write the new row of data
  }

  return ContentService.createTextOutput(JSON.stringify({ "status": "success" })).setMimeType(ContentService.MimeType.JSON);
}

function setup() {
  var doc = SpreadsheetApp.getActiveSpreadsheet();
  SCRIPT_PROP.setProperty("key", doc.getId());
}

This Google Sheets script is designed to handle HTTP GET and POST requests to log and process data from LoRaWAN devices into a Google Sheets spreadsheet.

Script Properties and Initialization:

• SCRIPT_PROP uses the PropertiesService to store script properties.
• The setup function sets the spreadsheet ID in the script properties for easy access later.

HTTP Request Handling:

• doGet and doPost functions both call handleResponse to handle incoming HTTP GET and POST requests.

Response Handling:

• handleResponse function processes the incoming request, extracts the data, and logs it into the active sheet. The function parses the JSON data from the request body.

Sheet Initialization:

• If the sheet is empty (i.e., no rows), it adds a header row with columns for various data fields such as Device ID, Application ID, Device EUI, Received At, Port, Payload, SSIDs, Gateway ID, RSSI, SNR, Latitude, Longitude, and Altitude.

Data Logging:

• The script extracts key data fields from the incoming JSON payload, including device identifiers, timestamps, and payload data.
• SSIDs from the decoded payload are combined into a single string and checked to see if they already exist for the same device and timestamp. If they do not exist, they are logged into a new row.

Gateway Metadata:

• For each gateway metadata entry in the uplink message, it logs the relevant gateway information such as Gateway ID, EUI, RSSI, SNR, and location (latitude, longitude, altitude).

Response Generation:

• After logging the data, the function responds with a JSON object indicating success.

The primary purpose of this script is to collect and organize data from LoRaWAN devices into a structured format within a Google Sheets spreadsheet. It handles both the data from the devices themselves (including SSIDs detected by the devices) and metadata from the gateways that received the uplink messages, ensuring all relevant information is logged efficiently.

%% Calculate geoid coordinates of the esp32 board

%% fixed constants
% attenuation of signal - can be modified, depending on the local area
n = 3.6667;
% RSSI of the unit distance (1 meter) - need to be measured in advanced
A = -29;

%% Reference ellipsoid for World Geodetic System 1984
wgs84 = wgs84Ellipsoid('meter');

%% First end-device coordinates 

p(1).lat = 
p(1).long = 
p(1).altitude = 
rssi1 = 

% convert the coordinates from World Geodetic System to Earth-centered, Earth-fixed coordinate system
[p(1).x,p(1).y,p(1).z] = geodetic2ecef(wgs84,p(1).lat,p(1).long,p(1).altitude);

%calculate the distance between the first end-node and the gateway based on RSSI
dist1 = 10.^((A-rssi1)/(10*n));

%% Second end-device coordinates 
p(2).lat = 
p(2).long = 
p(2).altitude = 
rssi2 = 

% convert the coordinates from World Geodetic System to Earth-centered, Earth-fixed coordinate system
[p(2).x,p(2).y,p(2).z] = geodetic2ecef(wgs84,p(2).lat,p(2).long,p(2).altitude);

% calculate the distance between second end-device and the gateway based on the RSSI
dist2 = 10.^((A-rssi2)/(10*n));

%% Third end-device coordinates 
p(3).lat = 
p(3).long = 
p(3).altitude = 
rssi3 = 

% convert the coordinates from World Geodetic System to Earth-centered, Earth-fixed coordinate system
[p(3).x,p(3).y,p(3).z] = geodetic2ecef(wgs84,p(3).lat,p(3).long,p(3).altitude);

% calculate the distance between the second end-device and the gateway based on the RSSI
dist3 = 10.^((A-rssi3)/(10*n));

%% Fourth end-device coordinates 
p(4).lat = 
p(4).long = 
p(4).altitude = 
rssi4 = -

% convert the coordinates from World Geodetic System to Earth-centered, Earth-fixed coordinate system
[p(4).x,p(4).y,p(4).z] = geodetic2ecef(wgs84,p(4).lat,p(4).long,p(4).altitude);

% calculate the distance between the fourth end-device and the gateway based on the RSSI
dist4 = 10.^((A-rssi4)/(10*n));

%% Finding gateway coordinates

% calculate the initial Guess for GPS equations
x0 = (p(1).x + p(2).x + p(3).x + p(4).x) / 4;
y0 = (p(1).y + p(2).y + p(3).y + p(4).y) / 4;
z0 = (p(1).z + p(2).z + p(3).z + p(4).z) / 4;
Guess = [x0,y0,z0];

% matrix form - the coordinates of the four end-devices and distances btw them and gw
A = [p(1).x , p(2).x , p(3).x, p(4).x];
B = [p(1).y , p(2).y , p(3).y, p(4).y];
C = [p(1).z , p(2).z , p(3).z, p(4).z];
D = [dist1 , dist2 , dist3, dist4];

% vector A length
LA = length(A);

% solving GPS equations
% Result vector contains the ECEF coordinates of the gateway
[Result,~,~] = MVNewtonsInputs(Guess,A,B,C,D,LA);

% Convert the gateway coordinates from Earth-centered, Earth-fixed
% coordinate system to World Geodetic System 1984
[esp_lat,esp_lon,esp_alt] = ecef2geodetic(wgs84,Result(1),Result(2),Result(3))

The main MATLAB script calculates the geoid coordinates of an ESP32 board using RSSI values from four end-devices. It first defines constants for signal attenuation (`n`) and the RSSI at a unit distance (`A`). It uses the World Geodetic System 1984 (`wgs84`) as the reference ellipsoid.

For each end-device, the script assigns latitude, longitude, altitude, and RSSI values, then converts these geodetic coordinates to Earth-centered, Earth-fixed (ECEF) coordinates. Using the RSSI values, it calculates the distance between each end-device and the gateway.

An initial guess for the gateway’s coordinates is determined by averaging the ECEF coordinates of the four end-devices. The coordinates and distances are then arranged in matrix form, and the `MVNewtonsInputs` function is used to solve for the gateway’s ECEF coordinates. Finally, these ECEF coordinates are converted back to geodetic coordinates (latitude, longitude, altitude) using the `ecef2geodetic` function, giving the geoid coordinates of the ESP32 board.

1. https://docs.heltec.org/en/node/esp32/wifi_lora_32/index.html
2. https://www.thethingsnetwork.org/docs/lorawan/end-device-activation/
3. https://github.com/HelTecAutomation/Heltec_ESP32