Data analysis - green-ecolution/backend GitHub Wiki

Once our backend successfully decodes and validates the incoming MQTT payload from The Things Stack Cloud (TTN), the structured sensor data is reliably stored and managed within our internal system for analysis and evaluation. This data later enables us to determine the watering status of individual trees and tree clusters.

The validated sensor data is first saved into the database. Each sensor has a record in the sensors table. If the sensor does not already exist, we create a new record and save it in the database:

log.Info("A new sensor has joined the system! Creating sensor record", "sensor_id", payload.Device, "sensor_latitude", payload.Latitude, "sensor_longitude", payload.Longitude)
createdSensor, err := s.sensorRepo.Create(ctx, func(s *domain.Sensor, _ storage.SensorRepository) (bool, error) {
    s.ID = payload.Device
    s.Latitude = payload.Latitude
    s.Longitude = payload.Longitude
    s.Status = domain.SensorStatusOnline
    return true, nil
})

If the sensor already exists, we update its coordinates and ensure its status is set to active:

if sensor.Latitude != payload.Latitude || sensor.Longitude != payload.Longitude || sensor.Status != domain.SensorStatusOnline {
    updatedSensor, err := s.sensorRepo.Update(ctx, sensor.ID, func(s *domain.Sensor, _ storage.SensorRepository) (bool, error) {
        s.Latitude = payload.Latitude
        s.Longitude = payload.Longitude
        s.Status = domain.SensorStatusOnline
        return true, nil
    })
    if err != nil {
        return nil, err
    }
    log.Info("Coordinates and status of sensor updated successfully", "sensor_id", updatedSensor.ID)
    return updatedSensor, err
}

The received data, such as humidity, temperature, and battery voltage, is then stored in the sensor_data table and linked to the corresponding sensor through a foreign key (sensor_id). If a sensor’s details change, such as its location, the sensors table is updated accordingly, ensuring data consistency.

This database design allows us to:

• Track how sensor readings change over time.
• Combine location data from sensors with current or historical readings in sensor_data.

The actual insertion of sensor data into the database is performed with the following code:

data := domain.SensorData{
    Data: payload,
}
err = s.sensorRepo.InsertSensorData(ctx, &data, sensor.ID)
if err != nil {
    log.Error("Failed to insert sensor data", "sensor_id", sensor.ID, "error", err)
    return nil, err
}

Once the data is stored, our system sends out events to notify other components, such as dashboards, analytics tools, or notification services, ensuring real-time updates and deeper long-term analysis. This helps in making informed decisions and monitoring sensor activity efficiently.

sensorData, err := s.sensorRepo.GetLatestSensorDataBySensorID(ctx, sensor.ID)
if err != nil {
    return nil, err
}

s.publishNewSensorDataEvent(ctx, sensorData)

This process ensures that sensor data is accurately stored, updated when necessary, and made available for analysis and real-time decision-making.

For a complete overview of the implementation logic, you can explore the internal/service/domain/sensor/mqtt.go file.