Queries

Campaigns, Missions, and Recordings

List all recordings with their campaign, mission, platform, and sensor:


SELECT
    campaign.name       AS campaign,
    mission.name        AS mission,
    recording.name      AS recording,
    recording.start_date,
    recording.end_date,
    platform.name       AS platform,
    sensor.name         AS sensor,
    sensor.type         AS sensor_type
FROM sensordb.campaign
JOIN sensordb.mission   ON mission.campaign_id   = campaign.id
JOIN sensordb.recording ON recording.mission_id  = mission.id
LEFT JOIN sensordb.platform ON recording.platform_id = platform.id
LEFT JOIN sensordb.sensor   ON recording.sensor_id   = sensor.id
ORDER BY campaign.name, mission.name, recording.name;

Trajectories

List all trajectories with their sensor and platform, and reconstruct each as a line geometry:


SELECT
    trajectory.id,
    sensor.name    AS sensor_name,
    platform.name  AS platform_name,
    ST_MakeLine(trajectory_pose.position ORDER BY timestamp_sec ASC, timestamp_nanosec ASC) AS trajectory_line
FROM sensordb.trajectory
JOIN sensordb.trajectory_pose ON trajectory.id        = trajectory_pose.trajectory_id
JOIN sensordb.recording       ON trajectory.recording_id = recording.id
LEFT JOIN sensordb.sensor   ON recording.sensor_id   = sensor.id
LEFT JOIN sensordb.platform ON recording.platform_id = platform.id
GROUP BY trajectory.id, sensor.name, platform.id;

Point Clouds Summary

List all point clouds with their total cell count and point count:


SELECT
    campaign.name    AS campaign,
    mission.name     AS mission,
    recording.name   AS recording,
    point_cloud.name AS point_cloud,
    COUNT(point_cloud_cell.id)          AS cell_count,
    SUM(point_cloud_cell.point_count)   AS total_points
FROM sensordb.point_cloud
JOIN sensordb.recording     ON point_cloud.recording_id  = recording.id
JOIN sensordb.mission       ON recording.mission_id      = mission.id
JOIN sensordb.campaign      ON mission.campaign_id       = campaign.id
LEFT JOIN sensordb.point_cloud_cell ON point_cloud_cell.point_cloud_id = point_cloud.id
GROUP BY campaign.name, mission.name, recording.name, point_cloud.id, point_cloud.name
ORDER BY campaign.name, mission.name, recording.name;

Point Cloud Cells by Bounding Box

Retrieve all point cloud cells that intersect a 2D bounding box (replace coordinates and SRID as needed):


SELECT
    point_cloud_cell.id,
    point_cloud_cell.level,
    point_cloud_cell.point_count,
    point_cloud_cell.cell_envelope
FROM sensordb.point_cloud_cell
WHERE point_cloud_cell.cell_envelope && ST_MakeEnvelope(690000, 5334000, 691000, 5335000, 25832)
ORDER BY point_cloud_cell.level;

Point Cloud Attributes

List all attributes stored in a point cloud (replace 1 with the target point_cloud.id):


SELECT * FROM sensordb_pkg.get_point_cloud_attributes(1);

Association Coverage

Count how many cells per point cloud have association data:


SELECT
    recording.name   AS recording,
    point_cloud.name AS point_cloud,
    COUNT(DISTINCT point_cloud_cell.id)  AS total_cells,
    COUNT(DISTINCT pcd.point_cloud_cell_id) AS associated_cells
FROM sensordb.point_cloud
JOIN sensordb.recording ON point_cloud.recording_id = recording.id
JOIN sensordb.point_cloud_cell ON point_cloud_cell.point_cloud_id = point_cloud.id
LEFT JOIN sensordb.point_cloud_cell_data AS pcd
    ON pcd.point_cloud_cell_id = point_cloud_cell.id
    AND pcd.name = 'FeatureGeometryId'
GROUP BY recording.name, point_cloud.id, point_cloud.name
ORDER BY recording.name;