Quick SQL Queries

SensorThings - Queries

This page collects practical examples to query data directly from the database (PostgreSQL / TimescaleDB) for the following tables:

• timeseries
• profiles
• detections

The general pattern is always the same:

1. Identify the datastream_id from the "DATASTREAMS" table using:
   • station ("THINGS"."NAME")
   • sensor ("SENSORS"."NAME")
   • observed property ("OBS_PROPERTIES"."NAME")
   • (optional) filters on "DATASTREAMS"."PROPERTIES" (e.g. averagePeriod, dataType, etc.)
2. Query the target table (timeseries, profiles, or detections) filtering by datastream_id and by time range.

Which table should I query?

The table to query depends on the type of data stored in the datastream:

• timeseries
  Regular time series data (one value per timestamp), such as CTD or environmental sensors.

• profiles
  Vertical profiles or multi-value observations per timestamp (e.g. ADCP/AWAC profiles), where each timestamp contains multiple depth–value pairs.

• detections
  Full detection results from cameras or AI pipelines (e.g. object/species detections), stored as one row per detection.

Note
In this system, full data are stored in timeseries and profiles, while aggregated (averaged) data from time series and profiles are stored in the OBSERVATIONS table.

See: https://github.com/obsea-upc/mmapi?tab=readme-ov-file#metadata-api-data-types

What you need before writing a query

To build a query you need to know:

• Station (Thing): "THINGS"."NAME" (e.g. OBSEA, Lander_AC0D)
• Sensor: "SENSORS"."NAME" (e.g. SBE37SMP-SN3727115, AWAC-SN5931, IPC608_8BC7_166)
• Observed property (variable / species / class): "OBS_PROPERTIES"."NAME" (e.g. PRES, CSPD, species_X)
• (Optional) If more than one equivalent datastream exists (e.g. full vs 30min), you must filter by:
  • "DATASTREAMS"."NAME", or
  • "DATASTREAMS"."PROPERTIES" (JSON)

Important concept: Datastreams define the data

All queries must start by identifying the correct datastream.

• The datastream defines what the data represent (sensor, property, aggregation, pipeline).
• Attributes such as average period, full vs aggregated data, or data type are defined at the datastream level, not in the data tables.

Filtering on the data tables without selecting the correct datastream may return incorrect or incomplete results.

Common errors (and how to avoid them)

1) More than one row returned by a subquery used as an expression

This happens when you use:

• datastream_id = (SELECT "ID" FROM "DATASTREAMS" ...) and the subquery returns more than one ID.

Solutions:

• Use IN (...) instead of = (...), or
• Add an extra filter to select a single datastream (e.g. averagePeriod = 30min), or
• Use ORDER BY ... LIMIT 1 (only if you are sure which one you want).

2) Columns with uppercase names (quoted identifiers)

In this schema, many core tables use quoted uppercase column names:

• "DATASTREAMS"."PROPERTIES" (not "properties")
• "DATASTREAMS"."ID", etc.

In hypertables (timeseries, profiles, detections) columns are usually lowercase:

• datastream_id, timestamp, etc.

Query Example 1 — Timeseries (timeseries table)

Typical query for a time series (e.g. pressure PRES from a sensor at a station):

SELECT *
FROM timeseries
WHERE
  datastream_id = (
    SELECT "ID"
    FROM "DATASTREAMS"
    WHERE
      "SENSOR_ID" = (SELECT "ID" FROM "SENSORS" WHERE "NAME" = 'SBE37SMP-SN3727115')
      AND "THING_ID" = (SELECT "ID" FROM "THINGS" WHERE "NAME" = 'Lander_AC0D')
      AND "OBS_PROPERTY_ID" = (SELECT "ID" FROM "OBS_PROPERTIES" WHERE "NAME" = 'PRES')
  )
  AND "timestamp" BETWEEN '2024-12-01T09:08:11.346Z' AND '2026-01-07T09:08:11.347Z'
ORDER BY 1;

Query Example 2 — Aggregated profiles (OBSERVATIONS table)

In profiles there is no period column.
The distinction between full and 30min is done by selecting the correct datastream in "DATASTREAMS".

Example: CSPD profiles from AWAC-SN5931 at OBSEA, using the datastream with averagePeriod = 30min:

SELECT *
  FROM "OBSERVATIONS" 
WHERE
   "DATASTREAM_ID" = (
      SELECT "ID" FROM "DATASTREAMS" WHERE 
      "SENSOR_ID" = (SELECT "ID" FROM "SENSORS" WHERE "NAME" = 'AWAC-SN5931') AND
      "THING_ID" = (SELECT "ID" FROM "THINGS" WHERE "NAME" = 'OBSEA') AND
      "OBS_PROPERTY_ID" = (SELECT "ID" FROM "OBS_PROPERTIES" WHERE "NAME" = 'CSPD') AND
      "PROPERTIES" ->> 'averagePeriod' = '30min') AND
     "PHENOMENON_TIME_START" BETWEEN '2024-12-01T09:08:11.346Z' AND '2026-01-07T09:08:11.347Z'

ORDER BY 1;

NOTES

• Aggregated profile data are stored in the OBSERVATIONS table.
• Time information is provided through PHENOMENON_TIME_START and PHENOMENON_TIME_END.

Query Example 3 — Profiles (profiles table)

SELECT *
FROM profiles
WHERE
  datastream_id IN (
    SELECT "ID"
    FROM "DATASTREAMS"
    WHERE
      "SENSOR_ID" = (SELECT "ID" FROM "SENSORS" WHERE "NAME" = 'AWAC-SN5931')
      AND "THING_ID" = (SELECT "ID" FROM "THINGS" WHERE "NAME" = 'OBSEA')
      AND "OBS_PROPERTY_ID" = (SELECT "ID" FROM "OBS_PROPERTIES" WHERE "NAME" = 'CSPD')
  )
  AND "timestamp" BETWEEN '2024-12-01T09:08:11.346Z'
                        AND '2026-01-07T09:08:11.347Z'
ORDER BY 1;

Query Example 4 — Detections (detections table)

Query detections for a camera sensor over a time range. Because multiple datastreams may exist (different classes/species/pipelines), IN is usually required.

SELECT *
FROM detections
WHERE
  datastream_id IN (
    SELECT "ID"
    FROM "DATASTREAMS"
    WHERE
      "SENSOR_ID" = (SELECT "ID" FROM "SENSORS" WHERE "NAME" = 'IPC608_8BC7_166')
  )
  AND "timestamp" BETWEEN '2023-12-01T09:08:11.346Z'
                        AND '2024-01-07T09:08:11.347Z'
ORDER BY 1;

Query Example 5 — Images (OBSERVATIONS table)

SELECT *
  FROM "OBSERVATIONS" 
WHERE
   "DATASTREAM_ID" IN (
      SELECT "ID" FROM "DATASTREAMS" WHERE 
      "SENSOR_ID" = (SELECT "ID" FROM "SENSORS" WHERE "NAME" = 'AIPC608UW_10_167')
   )AND
     "PHENOMENON_TIME_START" BETWEEN '2024-06-26T12:06:41Z' AND '2024-06-26T12:10:01Z'

Quick tips

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

Performance and safety notes

• Always restrict queries by time range.
• Avoid SELECT * on large time ranges unless strictly necessary.
• When exploring data, start with a small time window and increase gradually.