SensorThings

Data – SensorThings

---

This section describes the process of ingesting observational data into SensorThings once the metadata have been properly defined and exported from MMAPI.

It documents the operational steps required to load data into the system, without covering theoretical aspects of the SensorThings standard.

Overview of the SensorThings data model

The diagram above shows the data model used in SensorThings for storing observations and its relationship with the different elements of the system.

In this model:

• sensors and observed properties are grouped into datastreams,
• datastreams contain observations,
• each observation is associated with a timestamp and a Feature of Interest
• observations are ultimately stored in the database (TimescaleDB).

The purpose of this diagram is to provide a global overview of how data are structured once they are ingested, not to describe each entity of the SensorThings standard in detail.

From this point onwards, the documentation focuses on data ingestion processes and on the scripts used to insert observations and files into the system.

Prerequisites

Before loading any data, all of the following requirements must be met:

1. Metadata have been defined in MMAPI.

2. Metadata have been loaded using:

   ./metadata_manager.py --put

3. Metadata have been exported to SensorThings using:

   ./metadata_to_sensorthings.py

4. System services are running:

odi up

If any of these steps have not been completed, data ingestion will fail or lead to inconsistencies.

Supported data types

The ingestion system supports several data types, which are selected using specific command-line flags in bulk_load_data.py:

• Timeseries data (-t, --timeseries)
  Numeric data associated with a sensor, typically representing measurements taken over time at a fixed location (for example CTD time series or environmental sensors).

• Profile data (-p, --profiles)
  Numeric data organized along a vertical or parametric dimension, commonly used for AWAC profile measurements.

• Detections data (-d, --detections)
  Event-based or detection-type data, typically generated by automatic processing or detection algorithms.

• JSON-like data (-j, --json)
  Structured JSON data, such as outputs from AI inference or other automated processing pipelines.

• Files data (-f, --files)
  File-based data, where file paths and associated metadata are registered in the system.

Each data type requires specifying the appropriate flag when running the ingestion script.

Time series ingestion (bulk_load_data)

Time series data are ingested using:

bulk_load_data.py

This script inserts observations into SensorThings from CSV files, associating them with the corresponding datastreams.

File preparation

Before running the ingestion:

• Data must be provided in CSV format.
• The file must contain a timestamp column.
• Data columns must match the variables defined in the metadata.
• Any additional header or metadata rows must be removed.

Command execution

The general way to run the script is:

./bulk_load_data.py [options]

It is recommended to consult the script help:

./bulk_load_data.py --help

Example: timeseries ingestion:

./bulk_load_data.py --foi OBSEA /ruta/a/los/datos SENSOR_NAME -t --station OBSEA

In this command:

• --foi specifies the Feature of Interest associated with the data.
• /path/to/data is the path where the CSV files are located.
• SENSOR_NAME identifies the sensor associated with the data.
• -t (--timeseries) indicates the destination table type.
• --station specifies the station to which the sensor belongs.

Post-ingestion checks

After running the command:

• Verify that observations appear in SensorThings.
• Check that data are visible in visualization tools (for example Grafana).
• Review logs in case of errors.

##Image and file ingestion (bulk_load_files) Images and other files are ingested using:

bulk_load_files.py

This script copies files to the file server and registers the corresponding references in SensorThings.

File preparation

Before ingestion:

• Files must be organized according to the structure expected by the script.
• File names must allow identifying the sensor and the acquisition date.
• Source and destination paths must be accessible from the system.

Command execution

./bulk_load_files.py [options]

To list all available options:

./bulk_load_files.py --help

Example:

./bulk_load_files.py --foi OBSEA /path/to/images SENSOR_NAME /opt/files/SENSOR_NAME --station OBSEA

In this command:

• --foi specifies the Feature of Interest.
• /path/to/imagesis the local path containing the files.
• SENSOR_NAME dentifies the sensor.
• /opt/files/SENSOR_NAME is the destination path on the file server.
• --station specifies the associated station.

Manual deletion of data in SensorThings (exceptional use)

In certain situations, it may be necessary to manually delete data directly from the database. This procedure should be considered an exceptional action and should only be used when an error has occurred during data ingestion and there is no alternative through the ingestion scripts.

Examples of such situations include:

• data ingested into an incorrect station
• incorrect association of sensors or datastreams
• tests performed on the production environment
• errors detected after data ingestion (for example AXIS cameras or CTD sensors)

ATTENTION: These operations are irreversible and must be performed with extreme caution.

Deleting observations associated with a datastream

Before deleting a datastream, all observations associated with that datastream must be deleted first. Otherwise, the deletion will fail due to foreign key constraints.

1) Identify the datastream ID

First, identify the datastream_id associated with a given sensor (and optionally filter later by station or observed property):

SELECT *
FROM "DATASTREAMS"
WHERE
  "SENSOR_ID" = (
    SELECT "ID"
    FROM "SENSORS"
    WHERE "NAME" = 'SENSOR_NAME'
  );

Use this query to identify the correct ID associated with the datastream name

2) Delete observations linked to the datastream

Once the datastream_id is known, delete the observations associated with that datastream.

ATTENTION

• These operations are irreversible.
• Always verify the datastream_id and time range before executing a DELETE.

Example: deleting aggregated observations from the OBSERVATIONS table for a specific datastream and time range:

DELETE FROM "OBSERVATIONS"
WHERE
  "DATASTREAM_ID" IN (
      SELECT "ID" FROM "DATASTREAMS" WHERE 
      "SENSOR_ID" = (SELECT "ID" FROM "SENSORS" WHERE "NAME" = 'AXIS_P1346')
  AND "PHENOMENON_TIME_START"
      BETWEEN '2024-01-12T00:00:00Z' AND '2024-01-16T04:36:45Z';

The value 456 corresponds to the identifier of the affected datastream and must be verified beforehand.

Deleting datastreams

Once all associated observations have been deleted, the corresponding datastream can be safely removed.

Example: deleting a single datastream:

DELETE FROM "DATASTREAMS"
WHERE "ID" = 501;

Deleting a datastream without removing its observations first will fail due to database constraints.

Common errors and troubleshooting

During data ingestion, several common errors may occur.

Write permission errors

This error usually occurs when directories have been created by another user (for example root) and the current user does not have write permissions.

A common solution is to change the ownership of the affected directory, for example:

sudo chown  user:user /path/to/directory

Exemple:

sudo chown luna:luna /tmp/sta_db_copy/data

After fixing permissions, the ingestion command must be executed again.

Duplicate data ingestion

If ingestion scripts are executed multiple times on the same files or over the same time range, duplicate observations may be created in SensorThings.

Before repeating an ingestion, it is recommended to:

• verify whether the data already exist in SensorThings,
• check the time range of the files to be ingested,
• avoid running the script on directories that have already been processed.

Duplicate data removal is not automatic and may require manual intervention.

Services not running If system services are not running, ingestion scripts will not work.

Always ensure that odi up has been executed beforehand.

odi up

Inconsistent metadata

Ingestion errors may be caused by:

• non-existing sensors,
• duplicated datastreams,
• incorrectly defined variables. In such cases, review metadata in MMAPI and repeat the metadata loading and export process before ingesting data again.

Key points

• Data ingestion depends entirely on correctly defined metadata.
• In case of errors, always review MMAPI before modifying data.
• Ingesting data with incorrect metadata can create inconsistencies that are difficult to fix.
• Whenever metadata are modified, the process must be repeated before ingesting data.