This view contains details of the sensors and devices that are installed on the Spacewell platform, including their version history.
It can be used in combination with raw sensor data to obtain details about sensor devices and where they are located.
Concepts
→ Check this chapter for understanding relationships between locations, devices and sensors.
When using raw sensor data, timestamped values are recorded by sensors that belong to devices.
For reporting, you want to combine sensor data with sensors and devices and the locations where the are used.
Spacewell provides two views for this purpose:
- sensor_devices_v1
- locations_v1
Data structure of sensor_devices_v1
| Column | Meaning | Comment |
|---|---|---|
| sensor_history_id | primary key | |
| device_id | unique identifier of a device | |
| device_external_id | external identifier of a device | |
| device_type | type of device | |
| device_name | name of device | |
| sensor_id | unique identifier of a device sensor | |
| source | source of information | |
| sensor_type | what data a sensor monitors (PRESENCE / HEADCOUNT / CO2 etc) | |
| version_number | incremental version number (1, 2, 3 etc) | see historization |
| is_current | flag that determines if the version is current or not | see historization |
| start_timestamp | start timestamp of the version (in UTC) | see historization |
| end_timestamp | end timestamp of the version (in UTC) | see historization |
| location_id | unique identifier of the location where the device is installed (foreign key) | see locations_v1 |
| linked_location_ids | list of monitored locations (indirect monitoring) | see indirect monitoring |
| installed_timestamp | timestamp when the device was installed (in UTC) | |
| is_disabled | flag if the sensor is enabled or disabled | |
| disabled_timestamp | timestamp when the sensor was disabled | |
| remark | remark |
Illustration:
This example helps understanding the data structure.
In Studio, let’s look at this device:
The data for this device can be retrieved via this example query:
SELECT
sensor_history_id, device_id, device_name,
sensor_id, sensor_type,
version_number, is_current,
location_id
FROM `prod-sw-bit-spacewell.exposed_views.sensor_devices_v1`
WHERE device_id = 'E8-E1-E1-00-01-0E-54-8A'Output:
This example illustrates that
- A sensor device may contain 1 or more sensors. In this example, we see 2 sensor_types: PRESENCE and BATTERY
- The table contains a version history of the sensor devices:
- version number = 1, 2, 3 etc (incremental numbers are generated automatically)
- for more details, see Historization
Historization (version numbers)
Table “sensor_devices_v1” contains the current situation and also the past situations (= history) of the devices, when changes are happening over time such as:
- renaming a device
- activating/deactivating sensors on the device
- moving a device from one location to another
- etc
If you are NOT interested in this history for your reporting needs, use “ WHERE is_current = true” in your queries.
Example:
SELECT
sensor_history_id, device_id, device_name,
sensor_id, sensor_type,
version_number, is_current,
location_id
FROM `prod-sw-bit-spacewell.exposed_views.sensor_devices_v1`
WHERE device_id = 'E8-E1-E1-00-01-0E-54-8A'
AND is_current = trueOutput: