module models - d2r-api Documentation

class app.models.ATM41DataRaw(**kwargs)[source]¶

air_temperature: Mapped[Decimal]¶: air temperature in °C

atmospheric_pressure: Mapped[Decimal]¶: atmospheric pressure in kPa

battery_voltage: Mapped[Decimal]¶: The battery voltage of the sensor in Volts

lightning_average_distance: Mapped[Decimal]¶: distance of lightning strikes in km

lightning_strike_count: Mapped[Decimal]¶: number of lightning strikes

maximum_wind_speed: Mapped[Decimal]¶: maximum wind speed in m/s (gusts)

measured_at: Mapped[datetime]¶: The exact time the value was measured in UTC

precipitation_sum: Mapped[Decimal]¶: precipitation sum in mm

protocol_version: Mapped[int]¶: The protocol version the data was sent with

relative_humidity: Mapped[Decimal]¶: relative humidity in %

sensor: Mapped[Sensor]¶: The sensor the data was measured with

sensor_id: Mapped[str]¶: id of the sensor e.g. DEC1234

sensor_temperature_internal: Mapped[Decimal]¶: internal temperature of the sensor in °C

solar_radiation: Mapped[Decimal]¶: solar radiation in W/m2

u_wind: Mapped[Decimal]¶: u wind component in m/s

v_wind: Mapped[Decimal]¶: v wind component in m/s

vapor_pressure: Mapped[Decimal]¶: vapor pressure in kPa

wind_direction: Mapped[Decimal]¶: wind direction in °

wind_speed: Mapped[Decimal]¶: wind speed in m/s

x_orientation_angle: Mapped[Decimal]¶: x-tilt angle of the sensor in °

y_orientation_angle: Mapped[Decimal]¶: y-tilt angle of the sensor in °

class app.models.BLGDataRaw(**kwargs)[source]¶

battery_voltage: Mapped[Decimal]¶: The battery voltage of the sensor in Volts

black_globe_temperature: Mapped[Decimal]¶: black globe temperature in °C

measured_at: Mapped[datetime]¶: The exact time the value was measured in UTC

protocol_version: Mapped[int]¶: The protocol version the data was sent with

sensor: Mapped[Sensor]¶: The sensor the data was measured with

sensor_id: Mapped[str]¶: id of the sensor e.g. DEC1234

thermistor_resistance: Mapped[Decimal]¶: thermistor resistance in Ohms

voltage_ratio: Mapped[Decimal]¶: voltage ratio of the sensor

class app.models.BiometData(**kwargs)[source]¶

absolute_humidity: Mapped[Decimal]¶: absolute humidity in g/m3 calculated using thermal_comfort.absolute_humidity()

air_temperature: Mapped[Decimal]¶: air temperature in °C

air_temperature_qc_persistence_check: Mapped[bool]¶

air_temperature_qc_range_check: Mapped[bool]¶

air_temperature_qc_spike_dip_check: Mapped[bool]¶

atmospheric_pressure: Mapped[Decimal]¶: atmospheric pressure in kPa

atmospheric_pressure_qc_persistence_check: Mapped[bool]¶

atmospheric_pressure_qc_range_check: Mapped[bool]¶

atmospheric_pressure_qc_spike_dip_check: Mapped[bool]¶

atmospheric_pressure_reduced: Mapped[Decimal]¶: atmospheric pressure reduced to sea level in hPa calculated using app.tasks.reduce_pressure()

battery_voltage: Mapped[Decimal]¶: The battery voltage of the sensor in Volts

black_globe_temperature: Mapped[Decimal]¶: black globe temperature in °C

black_globe_temperature_qc_persistence_check: Mapped[bool]¶

black_globe_temperature_qc_range_check: Mapped[bool]¶

black_globe_temperature_qc_spike_dip_check: Mapped[bool]¶

blg_battery_voltage: Mapped[Decimal]¶: battery voltage of the black globe sensor in Volts

blg_sensor: Mapped[Sensor | None]¶: The black globe sensor the data was measured with

blg_sensor_id: Mapped[str | None]¶: id of the BLG sensor these measurements were taken with

blg_time_offset: Mapped[Decimal]¶: time offset of the Blackglobe sensor to the corresponding ATM41 sensor in seconds

deployments: Mapped[list[SensorDeployment]]¶: list of deployments that were involved in the measurement of this data

dew_point: Mapped[Decimal]¶: dew point temperature in °C calculated using thermal_comfort.dew_point()

heat_index: Mapped[Decimal]¶: heat index in °C calculated using thermal_comfort.heat_index_extended()

lightning_average_distance: Mapped[Decimal]¶: distance of lightning strikes in km

lightning_average_distance_qc_persistence_check: Mapped[bool]¶

lightning_average_distance_qc_range_check: Mapped[bool]¶

lightning_strike_count: Mapped[Decimal]¶: number of lightning strikes

lightning_strike_count_qc_persistence_check: Mapped[bool]¶

lightning_strike_count_qc_range_check: Mapped[bool]¶

maximum_wind_speed: Mapped[Decimal]¶: maximum wind speed in m/s (gusts)

maximum_wind_speed_qc_persistence_check: Mapped[bool]¶

maximum_wind_speed_qc_range_check: Mapped[bool]¶

measured_at: Mapped[datetime]¶: The exact time the value was measured in UTC

mrt: Mapped[Decimal]¶: mean radiant temperature in °C calculated using thermal_comfort.mean_radiant_temp()

pet: Mapped[Decimal]¶: physiological equivalent temperature in °C calculated using thermal_comfort.pet_static()

pet_category: Mapped[HeatStressCategories]¶: physiological equivalent temperature category derived from PET_STRESS_CATEGORIES and applied using app.tasks.category_mapping()

precipitation_sum: Mapped[Decimal]¶: precipitation sum in mm

precipitation_sum_qc_persistence_check: Mapped[bool]¶

precipitation_sum_qc_range_check: Mapped[bool]¶

precipitation_sum_qc_spike_dip_check: Mapped[bool]¶

protocol_version: Mapped[int]¶: The protocol version the data was sent with

qc_flagged: Mapped[bool]¶

relative_humidity: Mapped[Decimal]¶: relative humidity in %

relative_humidity_qc_persistence_check: Mapped[bool]¶

relative_humidity_qc_range_check: Mapped[bool]¶

relative_humidity_qc_spike_dip_check: Mapped[bool]¶

sensor: Mapped[Sensor]¶: The sensor the data was measured with

sensor_id: Mapped[str]¶: id of the ATM41 sensor these measurements were taken with

sensor_temperature_internal: Mapped[Decimal]¶: internal temperature of the sensor in °C

solar_radiation: Mapped[Decimal]¶: solar radiation in W/m2

solar_radiation_qc_persistence_check: Mapped[bool]¶

solar_radiation_qc_range_check: Mapped[bool]¶

solar_radiation_qc_spike_dip_check: Mapped[bool]¶

specific_humidity: Mapped[Decimal]¶: specific humidity in g/kg calculated using thermal_comfort.specific_humidity()

station: Mapped[Station]¶: The station the data was measured at

station_id: Mapped[str]¶: id of the station these measurements were taken at

thermistor_resistance: Mapped[Decimal]¶: thermistor resistance in Ohms

u_wind: Mapped[Decimal]¶: u wind component in m/s

u_wind_qc_persistence_check: Mapped[bool]¶

u_wind_qc_range_check: Mapped[bool]¶

u_wind_qc_spike_dip_check: Mapped[bool]¶

utci: Mapped[Decimal]¶: universal thermal climate index in °C calculated using thermal_comfort.utci_approx()

utci_category: Mapped[HeatStressCategories]¶: universal thermal climate index category derived from UTCI_STRESS_CATEGORIES and applied using app.tasks.category_mapping()

v_wind: Mapped[Decimal]¶: v wind component in m/s

v_wind_qc_persistence_check: Mapped[bool]¶

v_wind_qc_range_check: Mapped[bool]¶

v_wind_qc_spike_dip_check: Mapped[bool]¶

vapor_pressure: Mapped[Decimal]¶: vapor pressure in kPa

voltage_ratio: Mapped[Decimal]¶: voltage ratio of the sensor

wet_bulb_temperature: Mapped[Decimal]¶: wet bulb temperature in °C calculated using thermal_comfort.wet_bulb_temp()

wind_direction: Mapped[Decimal]¶: wind direction in °

wind_direction_qc_persistence_check: Mapped[bool]¶

wind_direction_qc_range_check: Mapped[bool]¶

wind_speed: Mapped[Decimal]¶: wind speed in m/s

wind_speed_qc_persistence_check: Mapped[bool]¶

wind_speed_qc_range_check: Mapped[bool]¶

wind_speed_qc_spike_dip_check: Mapped[bool]¶

x_orientation_angle: Mapped[Decimal]¶: x-tilt angle of the sensor in °

x_orientation_angle_qc_range_check: Mapped[bool]¶

x_orientation_angle_qc_spike_dip_check: Mapped[bool]¶

y_orientation_angle: Mapped[Decimal]¶: y-tilt angle of the sensor in °

y_orientation_angle_qc_range_check: Mapped[bool]¶

y_orientation_angle_qc_spike_dip_check: Mapped[bool]¶

class app.models.BiometDataDaily(**kwargs)[source]¶

This is not an actual table, but a materialized view. We simply trick sqlalchemy into thinking this was a table. Querying a materialized view does not differ from querying a proper table.

absolute_humidity: Mapped[Decimal]¶: absolute humidity in g/m3 calculated using thermal_comfort.absolute_humidity()

absolute_humidity_max: Mapped[Decimal]¶: maximum of absolute humidity in g/m3 calculated using thermal_comfort.absolute_humidity()

absolute_humidity_min: Mapped[Decimal]¶: minimum of absolute humidity in g/m3 calculated using thermal_comfort.absolute_humidity()

air_temperature: Mapped[Decimal]¶: air temperature in °C

air_temperature_max: Mapped[Decimal]¶: maximum of air temperature in °C

air_temperature_min: Mapped[Decimal]¶: minimum of air temperature in °C

atmospheric_pressure: Mapped[Decimal]¶: atmospheric pressure in kPa

atmospheric_pressure_max: Mapped[Decimal]¶: maximum of atmospheric pressure in kPa

atmospheric_pressure_min: Mapped[Decimal]¶: minimum of atmospheric pressure in kPa

atmospheric_pressure_reduced: Mapped[Decimal]¶: atmospheric pressure reduced to sea level in hPa calculated using app.tasks.reduce_pressure()

atmospheric_pressure_reduced_max: Mapped[Decimal]¶: maximum of atmospheric pressure reduced to sea level in hPa calculated using app.tasks.reduce_pressure()

atmospheric_pressure_reduced_min: Mapped[Decimal]¶: minimum of atmospheric pressure reduced to sea level in hPa calculated using app.tasks.reduce_pressure()

battery_voltage: Mapped[Decimal]¶: The battery voltage of the sensor in Volts

battery_voltage_max: Mapped[Decimal]¶: maximum of The battery voltage of the sensor in Volts

battery_voltage_min: Mapped[Decimal]¶: minimum of The battery voltage of the sensor in Volts

black_globe_temperature: Mapped[Decimal]¶: black globe temperature in °C

black_globe_temperature_max: Mapped[Decimal]¶: maximum of black globe temperature in °C

black_globe_temperature_min: Mapped[Decimal]¶: minimum of black globe temperature in °C

blg_battery_voltage: Mapped[Decimal]¶: battery voltage of the black globe sensor in Volts

blg_battery_voltage_max: Mapped[Decimal]¶: maximum of battery voltage of the black globe sensor in Volts

blg_battery_voltage_min: Mapped[Decimal]¶: minimum of battery voltage of the black globe sensor in Volts

blg_time_offset: Mapped[Decimal]¶: time offset of the Blackglobe sensor to the corresponding ATM41 sensor in seconds

blg_time_offset_max: Mapped[Decimal]¶: maximum of time offset of the Blackglobe sensor to the corresponding ATM41 sensor in seconds

blg_time_offset_min: Mapped[Decimal]¶: minimum of time offset of the Blackglobe sensor to the corresponding ATM41 sensor in seconds

creation_sql: str = " WITH data_bounds AS (\n SELECT\n station_id,\n MIN(measured_at) AS start_time,\n MAX(measured_at) AS end_time\n FROM biomet_data\n WHERE measured_at BETWEEN :window_start AND :window_end\n GROUP BY station_id\n ), filling_time_series AS (\n SELECT generate_series(\n DATE_TRUNC('hour', (\n SELECT MIN(measured_at) FROM biomet_data\n WHERE measured_at BETWEEN :window_start AND :window_end)\n ),\n DATE_TRUNC('hour', (\n SELECT MAX(measured_at) FROM biomet_data\n WHERE measured_at BETWEEN :window_start AND :window_end) + '1 hour'::INTERVAL\n ),\n '1 hour'::INTERVAL\n ) AS measured_at\n ),\n stations_subset AS (\n -- TODO: this could be faster if check the station table by station_type\n SELECT DISTINCT station_id FROM biomet_data\n ),\n time_station_combinations AS (\n SELECT\n measured_at,\n stations_subset.station_id,\n start_time,\n end_time\n FROM filling_time_series\n CROSS JOIN stations_subset\n JOIN data_bounds\n ON data_bounds.station_id = stations_subset.station_id\n WHERE filling_time_series.measured_at >= data_bounds.start_time\n AND filling_time_series.measured_at <= data_bounds.end_time\n ), all_data AS(\n (\n SELECT\n measured_at AS ma,\n station_id,\n NULL AS absolute_humidity,\n NULL AS air_temperature,\n NULL AS atmospheric_pressure,\n NULL AS atmospheric_pressure_reduced,\n NULL AS battery_voltage,\n NULL AS black_globe_temperature,\n NULL AS blg_battery_voltage,\n NULL AS blg_time_offset,\n NULL AS dew_point,\n NULL AS heat_index,\n NULL AS lightning_average_distance,\n NULL AS lightning_strike_count,\n NULL AS maximum_wind_speed,\n NULL AS mrt,\n NULL AS pet,\n NULL AS pet_category,\n NULL AS precipitation_sum,\n NULL AS protocol_version,\n NULL AS relative_humidity,\n NULL AS sensor_temperature_internal,\n NULL AS solar_radiation,\n NULL AS specific_humidity,\n NULL AS thermistor_resistance,\n NULL AS u_wind,\n NULL AS utci,\n NULL AS utci_category,\n NULL AS v_wind,\n NULL AS vapor_pressure,\n NULL AS voltage_ratio,\n NULL AS wet_bulb_temperature,\n NULL AS wind_direction,\n NULL AS wind_speed,\n NULL AS x_orientation_angle,\n NULL AS y_orientation_angle\n FROM time_station_combinations\n )\n UNION ALL\n (\n SELECT\n measured_at AS ma,\n station_id,\n absolute_humidity,\n air_temperature,\n atmospheric_pressure,\n atmospheric_pressure_reduced,\n battery_voltage,\n black_globe_temperature,\n blg_battery_voltage,\n blg_time_offset,\n dew_point,\n heat_index,\n lightning_average_distance,\n lightning_strike_count,\n maximum_wind_speed,\n mrt,\n pet,\n pet_category,\n precipitation_sum,\n protocol_version,\n relative_humidity,\n sensor_temperature_internal,\n solar_radiation,\n specific_humidity,\n thermistor_resistance,\n u_wind,\n utci,\n utci_category,\n v_wind,\n vapor_pressure,\n voltage_ratio,\n wet_bulb_temperature,\n wind_direction,\n wind_speed,\n x_orientation_angle,\n y_orientation_angle\n FROM biomet_data\n WHERE measured_at BETWEEN :window_start AND :window_end\n )\n ) SELECT\n (time_bucket('1day', ma, 'CET') + '1 hour'::INTERVAL)::DATE AS measured_at,\n station_id,\n CASE\n WHEN (count(*) FILTER (\n WHERE absolute_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(absolute_humidity)\n ELSE NULL\n END AS absolute_humidity,\n CASE\n WHEN (count(*) FILTER (\n WHERE absolute_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN max(absolute_humidity)\n ELSE NULL\n END AS absolute_humidity_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE absolute_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN min(absolute_humidity)\n ELSE NULL\n END AS absolute_humidity_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE air_temperature IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(air_temperature)\n ELSE NULL\n END AS air_temperature,\n CASE\n WHEN (count(*) FILTER (\n WHERE air_temperature IS NOT NULL) / 288.0\n ) > 0.7 THEN max(air_temperature)\n ELSE NULL\n END AS air_temperature_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE air_temperature IS NOT NULL) / 288.0\n ) > 0.7 THEN min(air_temperature)\n ELSE NULL\n END AS air_temperature_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE atmospheric_pressure IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(atmospheric_pressure)\n ELSE NULL\n END AS atmospheric_pressure,\n CASE\n WHEN (count(*) FILTER (\n WHERE atmospheric_pressure IS NOT NULL) / 288.0\n ) > 0.7 THEN max(atmospheric_pressure)\n ELSE NULL\n END AS atmospheric_pressure_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE atmospheric_pressure IS NOT NULL) / 288.0\n ) > 0.7 THEN min(atmospheric_pressure)\n ELSE NULL\n END AS atmospheric_pressure_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE atmospheric_pressure_reduced IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(atmospheric_pressure_reduced)\n ELSE NULL\n END AS atmospheric_pressure_reduced,\n CASE\n WHEN (count(*) FILTER (\n WHERE atmospheric_pressure_reduced IS NOT NULL) / 288.0\n ) > 0.7 THEN max(atmospheric_pressure_reduced)\n ELSE NULL\n END AS atmospheric_pressure_reduced_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE atmospheric_pressure_reduced IS NOT NULL) / 288.0\n ) > 0.7 THEN min(atmospheric_pressure_reduced)\n ELSE NULL\n END AS atmospheric_pressure_reduced_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE battery_voltage IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(battery_voltage)\n ELSE NULL\n END AS battery_voltage,\n CASE\n WHEN (count(*) FILTER (\n WHERE battery_voltage IS NOT NULL) / 288.0\n ) > 0.7 THEN max(battery_voltage)\n ELSE NULL\n END AS battery_voltage_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE battery_voltage IS NOT NULL) / 288.0\n ) > 0.7 THEN min(battery_voltage)\n ELSE NULL\n END AS battery_voltage_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE black_globe_temperature IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(black_globe_temperature)\n ELSE NULL\n END AS black_globe_temperature,\n CASE\n WHEN (count(*) FILTER (\n WHERE black_globe_temperature IS NOT NULL) / 288.0\n ) > 0.7 THEN max(black_globe_temperature)\n ELSE NULL\n END AS black_globe_temperature_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE black_globe_temperature IS NOT NULL) / 288.0\n ) > 0.7 THEN min(black_globe_temperature)\n ELSE NULL\n END AS black_globe_temperature_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE blg_battery_voltage IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(blg_battery_voltage)\n ELSE NULL\n END AS blg_battery_voltage,\n CASE\n WHEN (count(*) FILTER (\n WHERE blg_battery_voltage IS NOT NULL) / 288.0\n ) > 0.7 THEN max(blg_battery_voltage)\n ELSE NULL\n END AS blg_battery_voltage_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE blg_battery_voltage IS NOT NULL) / 288.0\n ) > 0.7 THEN min(blg_battery_voltage)\n ELSE NULL\n END AS blg_battery_voltage_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE blg_time_offset IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(blg_time_offset)\n ELSE NULL\n END AS blg_time_offset,\n CASE\n WHEN (count(*) FILTER (\n WHERE blg_time_offset IS NOT NULL) / 288.0\n ) > 0.7 THEN max(blg_time_offset)\n ELSE NULL\n END AS blg_time_offset_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE blg_time_offset IS NOT NULL) / 288.0\n ) > 0.7 THEN min(blg_time_offset)\n ELSE NULL\n END AS blg_time_offset_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE dew_point IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(dew_point)\n ELSE NULL\n END AS dew_point,\n CASE\n WHEN (count(*) FILTER (\n WHERE dew_point IS NOT NULL) / 288.0\n ) > 0.7 THEN max(dew_point)\n ELSE NULL\n END AS dew_point_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE dew_point IS NOT NULL) / 288.0\n ) > 0.7 THEN min(dew_point)\n ELSE NULL\n END AS dew_point_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE heat_index IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(heat_index)\n ELSE NULL\n END AS heat_index,\n CASE\n WHEN (count(*) FILTER (\n WHERE heat_index IS NOT NULL) / 288.0\n ) > 0.7 THEN max(heat_index)\n ELSE NULL\n END AS heat_index_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE heat_index IS NOT NULL) / 288.0\n ) > 0.7 THEN min(heat_index)\n ELSE NULL\n END AS heat_index_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE lightning_average_distance IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(lightning_average_distance) FILTER (WHERE lightning_average_distance > 0.0)\n ELSE NULL\n END AS lightning_average_distance,\n CASE\n WHEN (count(*) FILTER (\n WHERE lightning_average_distance IS NOT NULL) / 288.0\n ) > 0.7 THEN max(lightning_average_distance) FILTER (WHERE lightning_average_distance > 0.0)\n ELSE NULL\n END AS lightning_average_distance_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE lightning_average_distance IS NOT NULL) / 288.0\n ) > 0.7 THEN min(lightning_average_distance) FILTER (WHERE lightning_average_distance > 0.0)\n ELSE NULL\n END AS lightning_average_distance_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE lightning_strike_count IS NOT NULL) / 288.0\n ) > 0.7 THEN sum(lightning_strike_count)\n ELSE NULL\n END AS lightning_strike_count,\n CASE\n WHEN (count(*) FILTER (\n WHERE maximum_wind_speed IS NOT NULL) / 288.0\n ) > 0.7 THEN max(maximum_wind_speed)\n ELSE NULL\n END AS maximum_wind_speed,\n CASE\n WHEN (count(*) FILTER (\n WHERE mrt IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(mrt)\n ELSE NULL\n END AS mrt,\n CASE\n WHEN (count(*) FILTER (\n WHERE mrt IS NOT NULL) / 288.0\n ) > 0.7 THEN max(mrt)\n ELSE NULL\n END AS mrt_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE mrt IS NOT NULL) / 288.0\n ) > 0.7 THEN min(mrt)\n ELSE NULL\n END AS mrt_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE pet IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(pet)\n ELSE NULL\n END AS pet,\n CASE\n WHEN (count(*) FILTER (\n WHERE pet_category IS NOT NULL) / 288.0\n ) > 0.7 THEN mode() WITHIN GROUP (ORDER BY pet_category ASC)\n ELSE NULL\n END AS pet_category,\n CASE\n WHEN (count(*) FILTER (\n WHERE pet IS NOT NULL) / 288.0\n ) > 0.7 THEN max(pet)\n ELSE NULL\n END AS pet_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE pet IS NOT NULL) / 288.0\n ) > 0.7 THEN min(pet)\n ELSE NULL\n END AS pet_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE precipitation_sum IS NOT NULL) / 288.0\n ) > 0.7 THEN sum(precipitation_sum)\n ELSE NULL\n END AS precipitation_sum,\n CASE\n WHEN (count(*) FILTER (\n WHERE protocol_version IS NOT NULL) / 288.0\n ) > 0.7 THEN mode() WITHIN GROUP (ORDER BY protocol_version ASC)\n ELSE NULL\n END AS protocol_version,\n CASE\n WHEN (count(*) FILTER (\n WHERE relative_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(relative_humidity)\n ELSE NULL\n END AS relative_humidity,\n CASE\n WHEN (count(*) FILTER (\n WHERE relative_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN max(relative_humidity)\n ELSE NULL\n END AS relative_humidity_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE relative_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN min(relative_humidity)\n ELSE NULL\n END AS relative_humidity_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE sensor_temperature_internal IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(sensor_temperature_internal)\n ELSE NULL\n END AS sensor_temperature_internal,\n CASE\n WHEN (count(*) FILTER (\n WHERE sensor_temperature_internal IS NOT NULL) / 288.0\n ) > 0.7 THEN max(sensor_temperature_internal)\n ELSE NULL\n END AS sensor_temperature_internal_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE sensor_temperature_internal IS NOT NULL) / 288.0\n ) > 0.7 THEN min(sensor_temperature_internal)\n ELSE NULL\n END AS sensor_temperature_internal_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE solar_radiation IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(solar_radiation)\n ELSE NULL\n END AS solar_radiation,\n CASE\n WHEN (count(*) FILTER (\n WHERE solar_radiation IS NOT NULL) / 288.0\n ) > 0.7 THEN max(solar_radiation)\n ELSE NULL\n END AS solar_radiation_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE solar_radiation IS NOT NULL) / 288.0\n ) > 0.7 THEN min(solar_radiation)\n ELSE NULL\n END AS solar_radiation_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE specific_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(specific_humidity)\n ELSE NULL\n END AS specific_humidity,\n CASE\n WHEN (count(*) FILTER (\n WHERE specific_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN max(specific_humidity)\n ELSE NULL\n END AS specific_humidity_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE specific_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN min(specific_humidity)\n ELSE NULL\n END AS specific_humidity_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE thermistor_resistance IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(thermistor_resistance)\n ELSE NULL\n END AS thermistor_resistance,\n CASE\n WHEN (count(*) FILTER (\n WHERE thermistor_resistance IS NOT NULL) / 288.0\n ) > 0.7 THEN max(thermistor_resistance)\n ELSE NULL\n END AS thermistor_resistance_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE thermistor_resistance IS NOT NULL) / 288.0\n ) > 0.7 THEN min(thermistor_resistance)\n ELSE NULL\n END AS thermistor_resistance_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE u_wind IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(u_wind)\n ELSE NULL\n END AS u_wind,\n CASE\n WHEN (count(*) FILTER (\n WHERE u_wind IS NOT NULL) / 288.0\n ) > 0.7 THEN max(u_wind)\n ELSE NULL\n END AS u_wind_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE u_wind IS NOT NULL) / 288.0\n ) > 0.7 THEN min(u_wind)\n ELSE NULL\n END AS u_wind_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE utci IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(utci)\n ELSE NULL\n END AS utci,\n CASE\n WHEN (count(*) FILTER (\n WHERE utci_category IS NOT NULL) / 288.0\n ) > 0.7 THEN mode() WITHIN GROUP (ORDER BY utci_category ASC)\n ELSE NULL\n END AS utci_category,\n CASE\n WHEN (count(*) FILTER (\n WHERE utci IS NOT NULL) / 288.0\n ) > 0.7 THEN max(utci)\n ELSE NULL\n END AS utci_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE utci IS NOT NULL) / 288.0\n ) > 0.7 THEN min(utci)\n ELSE NULL\n END AS utci_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE v_wind IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(v_wind)\n ELSE NULL\n END AS v_wind,\n CASE\n WHEN (count(*) FILTER (\n WHERE v_wind IS NOT NULL) / 288.0\n ) > 0.7 THEN max(v_wind)\n ELSE NULL\n END AS v_wind_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE v_wind IS NOT NULL) / 288.0\n ) > 0.7 THEN min(v_wind)\n ELSE NULL\n END AS v_wind_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE vapor_pressure IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(vapor_pressure)\n ELSE NULL\n END AS vapor_pressure,\n CASE\n WHEN (count(*) FILTER (\n WHERE vapor_pressure IS NOT NULL) / 288.0\n ) > 0.7 THEN max(vapor_pressure)\n ELSE NULL\n END AS vapor_pressure_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE vapor_pressure IS NOT NULL) / 288.0\n ) > 0.7 THEN min(vapor_pressure)\n ELSE NULL\n END AS vapor_pressure_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE voltage_ratio IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(voltage_ratio)\n ELSE NULL\n END AS voltage_ratio,\n CASE\n WHEN (count(*) FILTER (\n WHERE voltage_ratio IS NOT NULL) / 288.0\n ) > 0.7 THEN max(voltage_ratio)\n ELSE NULL\n END AS voltage_ratio_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE voltage_ratio IS NOT NULL) / 288.0\n ) > 0.7 THEN min(voltage_ratio)\n ELSE NULL\n END AS voltage_ratio_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE wet_bulb_temperature IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(wet_bulb_temperature)\n ELSE NULL\n END AS wet_bulb_temperature,\n CASE\n WHEN (count(*) FILTER (\n WHERE wet_bulb_temperature IS NOT NULL) / 288.0\n ) > 0.7 THEN max(wet_bulb_temperature)\n ELSE NULL\n END AS wet_bulb_temperature_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE wet_bulb_temperature IS NOT NULL) / 288.0\n ) > 0.7 THEN min(wet_bulb_temperature)\n ELSE NULL\n END AS wet_bulb_temperature_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE wind_direction IS NOT NULL) / 288.0\n ) > 0.7 THEN avg_angle(wind_direction)\n ELSE NULL\n END AS wind_direction,\n CASE\n WHEN (count(*) FILTER (\n WHERE wind_speed IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(wind_speed)\n ELSE NULL\n END AS wind_speed,\n CASE\n WHEN (count(*) FILTER (\n WHERE wind_speed IS NOT NULL) / 288.0\n ) > 0.7 THEN max(wind_speed)\n ELSE NULL\n END AS wind_speed_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE wind_speed IS NOT NULL) / 288.0\n ) > 0.7 THEN min(wind_speed)\n ELSE NULL\n END AS wind_speed_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE x_orientation_angle IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(x_orientation_angle)\n ELSE NULL\n END AS x_orientation_angle,\n CASE\n WHEN (count(*) FILTER (\n WHERE x_orientation_angle IS NOT NULL) / 288.0\n ) > 0.7 THEN max(x_orientation_angle)\n ELSE NULL\n END AS x_orientation_angle_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE x_orientation_angle IS NOT NULL) / 288.0\n ) > 0.7 THEN min(x_orientation_angle)\n ELSE NULL\n END AS x_orientation_angle_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE y_orientation_angle IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(y_orientation_angle)\n ELSE NULL\n END AS y_orientation_angle,\n CASE\n WHEN (count(*) FILTER (\n WHERE y_orientation_angle IS NOT NULL) / 288.0\n ) > 0.7 THEN max(y_orientation_angle)\n ELSE NULL\n END AS y_orientation_angle_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE y_orientation_angle IS NOT NULL) / 288.0\n ) > 0.7 THEN min(y_orientation_angle)\n ELSE NULL\n END AS y_orientation_angle_min\n FROM all_data\n GROUP BY measured_at, station_id\n ORDER BY measured_at, station_id\n "¶

dew_point: Mapped[Decimal]¶: dew point temperature in °C calculated using thermal_comfort.dew_point()

dew_point_max: Mapped[Decimal]¶: maximum of dew point temperature in °C calculated using thermal_comfort.dew_point()

dew_point_min: Mapped[Decimal]¶: minimum of dew point temperature in °C calculated using thermal_comfort.dew_point()

heat_index: Mapped[Decimal]¶: heat index in °C calculated using thermal_comfort.heat_index_extended()

heat_index_max: Mapped[Decimal]¶: maximum of heat index in °C calculated using thermal_comfort.heat_index_extended()

heat_index_min: Mapped[Decimal]¶: minimum of heat index in °C calculated using thermal_comfort.heat_index_extended()

lightning_average_distance: Mapped[Decimal]¶: distance of lightning strikes in km

lightning_average_distance_max: Mapped[Decimal]¶: maximum of distance of lightning strikes in km

lightning_average_distance_min: Mapped[Decimal]¶: minimum of distance of lightning strikes in km

lightning_strike_count: Mapped[Decimal]¶: number of lightning strikes

maximum_wind_speed: Mapped[Decimal]¶: maximum wind speed in m/s (gusts)

measured_at: Mapped[datetime]¶: The exact time the value was measured in UTC

mrt: Mapped[Decimal]¶: mean radiant temperature in °C calculated using thermal_comfort.mean_radiant_temp()

mrt_max: Mapped[Decimal]¶: maximum of mean radiant temperature in °C calculated using thermal_comfort.mean_radiant_temp()

mrt_min: Mapped[Decimal]¶: minimum of mean radiant temperature in °C calculated using thermal_comfort.mean_radiant_temp()

pet: Mapped[Decimal]¶: physiological equivalent temperature in °C calculated using thermal_comfort.pet_static()

pet_category: Mapped[HeatStressCategories]¶: physiological equivalent temperature category derived from PET_STRESS_CATEGORIES and applied using app.tasks.category_mapping()

pet_max: Mapped[Decimal]¶: maximum of physiological equivalent temperature in °C calculated using thermal_comfort.pet_static()

pet_min: Mapped[Decimal]¶: minimum of physiological equivalent temperature in °C calculated using thermal_comfort.pet_static()

precipitation_sum: Mapped[Decimal]¶: precipitation sum in mm

protocol_version: Mapped[int]¶: The protocol version the data was sent with

relative_humidity: Mapped[Decimal]¶: relative humidity in %

relative_humidity_max: Mapped[Decimal]¶: maximum of relative humidity in %

relative_humidity_min: Mapped[Decimal]¶: minimum of relative humidity in %

sensor_temperature_internal: Mapped[Decimal]¶: internal temperature of the sensor in °C

sensor_temperature_internal_max: Mapped[Decimal]¶: maximum of internal temperature of the sensor in °C

sensor_temperature_internal_min: Mapped[Decimal]¶: minimum of internal temperature of the sensor in °C

solar_radiation: Mapped[Decimal]¶: solar radiation in W/m2

solar_radiation_max: Mapped[Decimal]¶: maximum of solar radiation in W/m2

solar_radiation_min: Mapped[Decimal]¶: minimum of solar radiation in W/m2

specific_humidity: Mapped[Decimal]¶: specific humidity in g/kg calculated using thermal_comfort.specific_humidity()

specific_humidity_max: Mapped[Decimal]¶: maximum of specific humidity in g/kg calculated using thermal_comfort.specific_humidity()

specific_humidity_min: Mapped[Decimal]¶: minimum of specific humidity in g/kg calculated using thermal_comfort.specific_humidity()

station: Mapped[Station]¶: The station the data was measured at

station_id: Mapped[str]¶: id of the station these measurements were taken at

thermistor_resistance: Mapped[Decimal]¶: thermistor resistance in Ohms

thermistor_resistance_max: Mapped[Decimal]¶: maximum of thermistor resistance in Ohms

thermistor_resistance_min: Mapped[Decimal]¶: minimum of thermistor resistance in Ohms

u_wind: Mapped[Decimal]¶: u wind component in m/s

u_wind_max: Mapped[Decimal]¶: maximum of u wind component in m/s

u_wind_min: Mapped[Decimal]¶: minimum of u wind component in m/s

utci: Mapped[Decimal]¶: universal thermal climate index in °C calculated using thermal_comfort.utci_approx()

utci_category: Mapped[HeatStressCategories]¶: universal thermal climate index category derived from UTCI_STRESS_CATEGORIES and applied using app.tasks.category_mapping()

utci_max: Mapped[Decimal]¶: maximum of universal thermal climate index in °C calculated using thermal_comfort.utci_approx()

utci_min: Mapped[Decimal]¶: minimum of universal thermal climate index in °C calculated using thermal_comfort.utci_approx()

v_wind: Mapped[Decimal]¶: v wind component in m/s

v_wind_max: Mapped[Decimal]¶: maximum of v wind component in m/s

v_wind_min: Mapped[Decimal]¶: minimum of v wind component in m/s

vapor_pressure: Mapped[Decimal]¶: vapor pressure in kPa

vapor_pressure_max: Mapped[Decimal]¶: maximum of vapor pressure in kPa

vapor_pressure_min: Mapped[Decimal]¶: minimum of vapor pressure in kPa

voltage_ratio: Mapped[Decimal]¶: voltage ratio of the sensor

voltage_ratio_max: Mapped[Decimal]¶: maximum of voltage ratio of the sensor

voltage_ratio_min: Mapped[Decimal]¶: minimum of voltage ratio of the sensor

wet_bulb_temperature: Mapped[Decimal]¶: wet bulb temperature in °C calculated using thermal_comfort.wet_bulb_temp()

wet_bulb_temperature_max: Mapped[Decimal]¶: maximum of wet bulb temperature in °C calculated using thermal_comfort.wet_bulb_temp()

wet_bulb_temperature_min: Mapped[Decimal]¶: minimum of wet bulb temperature in °C calculated using thermal_comfort.wet_bulb_temp()

wind_direction: Mapped[Decimal]¶: wind direction in °

wind_speed: Mapped[Decimal]¶: wind speed in m/s

wind_speed_max: Mapped[Decimal]¶: maximum of wind speed in m/s

wind_speed_min: Mapped[Decimal]¶: minimum of wind speed in m/s

x_orientation_angle: Mapped[Decimal]¶: x-tilt angle of the sensor in °

x_orientation_angle_max: Mapped[Decimal]¶: maximum of x-tilt angle of the sensor in °

x_orientation_angle_min: Mapped[Decimal]¶: minimum of x-tilt angle of the sensor in °

y_orientation_angle: Mapped[Decimal]¶: y-tilt angle of the sensor in °

y_orientation_angle_max: Mapped[Decimal]¶: maximum of y-tilt angle of the sensor in °

y_orientation_angle_min: Mapped[Decimal]¶: minimum of y-tilt angle of the sensor in °

class app.models.BiometDataHourly(**kwargs)[source]¶

This is not an actual table, but a materialized view. We simply trick sqlalchemy into thinking this was a table. Querying a materialized view does not differ from querying a proper table.

absolute_humidity: Mapped[Decimal]¶: absolute humidity in g/m3 calculated using thermal_comfort.absolute_humidity()

absolute_humidity_max: Mapped[Decimal]¶: maximum of absolute humidity in g/m3 calculated using thermal_comfort.absolute_humidity()

absolute_humidity_min: Mapped[Decimal]¶: minimum of absolute humidity in g/m3 calculated using thermal_comfort.absolute_humidity()

air_temperature: Mapped[Decimal]¶: air temperature in °C

air_temperature_max: Mapped[Decimal]¶: maximum of air temperature in °C

air_temperature_min: Mapped[Decimal]¶: minimum of air temperature in °C

atmospheric_pressure: Mapped[Decimal]¶: atmospheric pressure in kPa

atmospheric_pressure_max: Mapped[Decimal]¶: maximum of atmospheric pressure in kPa

atmospheric_pressure_min: Mapped[Decimal]¶: minimum of atmospheric pressure in kPa

atmospheric_pressure_reduced: Mapped[Decimal]¶: atmospheric pressure reduced to sea level in hPa calculated using app.tasks.reduce_pressure()

atmospheric_pressure_reduced_max: Mapped[Decimal]¶: maximum of atmospheric pressure reduced to sea level in hPa calculated using app.tasks.reduce_pressure()

atmospheric_pressure_reduced_min: Mapped[Decimal]¶: minimum of atmospheric pressure reduced to sea level in hPa calculated using app.tasks.reduce_pressure()

battery_voltage: Mapped[Decimal]¶: The battery voltage of the sensor in Volts

battery_voltage_max: Mapped[Decimal]¶: maximum of The battery voltage of the sensor in Volts

battery_voltage_min: Mapped[Decimal]¶: minimum of The battery voltage of the sensor in Volts

black_globe_temperature: Mapped[Decimal]¶: black globe temperature in °C

black_globe_temperature_max: Mapped[Decimal]¶: maximum of black globe temperature in °C

black_globe_temperature_min: Mapped[Decimal]¶: minimum of black globe temperature in °C

blg_battery_voltage: Mapped[Decimal]¶: battery voltage of the black globe sensor in Volts

blg_battery_voltage_max: Mapped[Decimal]¶: maximum of battery voltage of the black globe sensor in Volts

blg_battery_voltage_min: Mapped[Decimal]¶: minimum of battery voltage of the black globe sensor in Volts

blg_time_offset: Mapped[Decimal]¶: time offset of the Blackglobe sensor to the corresponding ATM41 sensor in seconds

blg_time_offset_max: Mapped[Decimal]¶: maximum of time offset of the Blackglobe sensor to the corresponding ATM41 sensor in seconds

blg_time_offset_min: Mapped[Decimal]¶: minimum of time offset of the Blackglobe sensor to the corresponding ATM41 sensor in seconds

creation_sql: str = " WITH data_bounds AS (\n SELECT\n station_id,\n MIN(measured_at) AS start_time,\n MAX(measured_at) AS end_time\n FROM biomet_data\n WHERE measured_at BETWEEN :window_start AND :window_end\n GROUP BY station_id\n ), filling_time_series AS (\n SELECT generate_series(\n DATE_TRUNC('hour', (\n SELECT MIN(measured_at) FROM biomet_data\n WHERE measured_at BETWEEN :window_start AND :window_end)\n ),\n DATE_TRUNC('hour', (\n SELECT MAX(measured_at) FROM biomet_data\n WHERE measured_at BETWEEN :window_start AND :window_end) + '1 hour'::INTERVAL\n ),\n '1 hour'::INTERVAL\n ) AS measured_at\n ),\n stations_subset AS (\n -- TODO: this could be faster if check the station table by station_type\n SELECT DISTINCT station_id FROM biomet_data\n ),\n time_station_combinations AS (\n SELECT\n measured_at,\n stations_subset.station_id,\n start_time,\n end_time\n FROM filling_time_series\n CROSS JOIN stations_subset\n JOIN data_bounds\n ON data_bounds.station_id = stations_subset.station_id\n WHERE filling_time_series.measured_at >= data_bounds.start_time\n AND filling_time_series.measured_at <= data_bounds.end_time\n ), all_data AS(\n (\n SELECT\n measured_at AS ma,\n station_id,\n NULL AS absolute_humidity,\n NULL AS air_temperature,\n NULL AS atmospheric_pressure,\n NULL AS atmospheric_pressure_reduced,\n NULL AS battery_voltage,\n NULL AS black_globe_temperature,\n NULL AS blg_battery_voltage,\n NULL AS blg_time_offset,\n NULL AS dew_point,\n NULL AS heat_index,\n NULL AS lightning_average_distance,\n NULL AS lightning_strike_count,\n NULL AS maximum_wind_speed,\n NULL AS mrt,\n NULL AS pet,\n NULL AS pet_category,\n NULL AS precipitation_sum,\n NULL AS protocol_version,\n NULL AS relative_humidity,\n NULL AS sensor_temperature_internal,\n NULL AS solar_radiation,\n NULL AS specific_humidity,\n NULL AS thermistor_resistance,\n NULL AS u_wind,\n NULL AS utci,\n NULL AS utci_category,\n NULL AS v_wind,\n NULL AS vapor_pressure,\n NULL AS voltage_ratio,\n NULL AS wet_bulb_temperature,\n NULL AS wind_direction,\n NULL AS wind_speed,\n NULL AS x_orientation_angle,\n NULL AS y_orientation_angle\n FROM time_station_combinations\n )\n UNION ALL\n (\n SELECT\n measured_at AS ma,\n station_id,\n absolute_humidity,\n air_temperature,\n atmospheric_pressure,\n atmospheric_pressure_reduced,\n battery_voltage,\n black_globe_temperature,\n blg_battery_voltage,\n blg_time_offset,\n dew_point,\n heat_index,\n lightning_average_distance,\n lightning_strike_count,\n maximum_wind_speed,\n mrt,\n pet,\n pet_category,\n precipitation_sum,\n protocol_version,\n relative_humidity,\n sensor_temperature_internal,\n solar_radiation,\n specific_humidity,\n thermistor_resistance,\n u_wind,\n utci,\n utci_category,\n v_wind,\n vapor_pressure,\n voltage_ratio,\n wet_bulb_temperature,\n wind_direction,\n wind_speed,\n x_orientation_angle,\n y_orientation_angle\n FROM biomet_data\n WHERE measured_at BETWEEN :window_start AND :window_end\n )\n ) SELECT\n time_bucket('1 hour', ma) + '1 hour'::INTERVAL AS measured_at,\n station_id,\n avg(absolute_humidity) AS absolute_humidity,\n max(absolute_humidity) AS absolute_humidity_max,\n min(absolute_humidity) AS absolute_humidity_min,\n avg(air_temperature) AS air_temperature,\n max(air_temperature) AS air_temperature_max,\n min(air_temperature) AS air_temperature_min,\n avg(atmospheric_pressure) AS atmospheric_pressure,\n max(atmospheric_pressure) AS atmospheric_pressure_max,\n min(atmospheric_pressure) AS atmospheric_pressure_min,\n avg(atmospheric_pressure_reduced) AS atmospheric_pressure_reduced,\n max(atmospheric_pressure_reduced) AS atmospheric_pressure_reduced_max,\n min(atmospheric_pressure_reduced) AS atmospheric_pressure_reduced_min,\n avg(battery_voltage) AS battery_voltage,\n max(battery_voltage) AS battery_voltage_max,\n min(battery_voltage) AS battery_voltage_min,\n avg(black_globe_temperature) AS black_globe_temperature,\n max(black_globe_temperature) AS black_globe_temperature_max,\n min(black_globe_temperature) AS black_globe_temperature_min,\n avg(blg_battery_voltage) AS blg_battery_voltage,\n max(blg_battery_voltage) AS blg_battery_voltage_max,\n min(blg_battery_voltage) AS blg_battery_voltage_min,\n avg(blg_time_offset) AS blg_time_offset,\n max(blg_time_offset) AS blg_time_offset_max,\n min(blg_time_offset) AS blg_time_offset_min,\n avg(dew_point) AS dew_point,\n max(dew_point) AS dew_point_max,\n min(dew_point) AS dew_point_min,\n avg(heat_index) AS heat_index,\n max(heat_index) AS heat_index_max,\n min(heat_index) AS heat_index_min,\n avg(lightning_average_distance) FILTER (WHERE lightning_average_distance > 0.0) AS lightning_average_distance,\n max(lightning_average_distance) FILTER (WHERE lightning_average_distance > 0.0) AS lightning_average_distance_max,\n min(lightning_average_distance) FILTER (WHERE lightning_average_distance > 0.0) AS lightning_average_distance_min,\n sum(lightning_strike_count) AS lightning_strike_count,\n max(maximum_wind_speed) AS maximum_wind_speed,\n avg(mrt) AS mrt,\n max(mrt) AS mrt_max,\n min(mrt) AS mrt_min,\n avg(pet) AS pet,\n mode() WITHIN GROUP (ORDER BY pet_category ASC) AS pet_category,\n max(pet) AS pet_max,\n min(pet) AS pet_min,\n sum(precipitation_sum) AS precipitation_sum,\n mode() WITHIN GROUP (ORDER BY protocol_version ASC) AS protocol_version,\n avg(relative_humidity) AS relative_humidity,\n max(relative_humidity) AS relative_humidity_max,\n min(relative_humidity) AS relative_humidity_min,\n avg(sensor_temperature_internal) AS sensor_temperature_internal,\n max(sensor_temperature_internal) AS sensor_temperature_internal_max,\n min(sensor_temperature_internal) AS sensor_temperature_internal_min,\n avg(solar_radiation) AS solar_radiation,\n max(solar_radiation) AS solar_radiation_max,\n min(solar_radiation) AS solar_radiation_min,\n avg(specific_humidity) AS specific_humidity,\n max(specific_humidity) AS specific_humidity_max,\n min(specific_humidity) AS specific_humidity_min,\n avg(thermistor_resistance) AS thermistor_resistance,\n max(thermistor_resistance) AS thermistor_resistance_max,\n min(thermistor_resistance) AS thermistor_resistance_min,\n avg(u_wind) AS u_wind,\n max(u_wind) AS u_wind_max,\n min(u_wind) AS u_wind_min,\n avg(utci) AS utci,\n mode() WITHIN GROUP (ORDER BY utci_category ASC) AS utci_category,\n max(utci) AS utci_max,\n min(utci) AS utci_min,\n avg(v_wind) AS v_wind,\n max(v_wind) AS v_wind_max,\n min(v_wind) AS v_wind_min,\n avg(vapor_pressure) AS vapor_pressure,\n max(vapor_pressure) AS vapor_pressure_max,\n min(vapor_pressure) AS vapor_pressure_min,\n avg(voltage_ratio) AS voltage_ratio,\n max(voltage_ratio) AS voltage_ratio_max,\n min(voltage_ratio) AS voltage_ratio_min,\n avg(wet_bulb_temperature) AS wet_bulb_temperature,\n max(wet_bulb_temperature) AS wet_bulb_temperature_max,\n min(wet_bulb_temperature) AS wet_bulb_temperature_min,\n avg_angle(wind_direction) AS wind_direction,\n avg(wind_speed) AS wind_speed,\n max(wind_speed) AS wind_speed_max,\n min(wind_speed) AS wind_speed_min,\n avg(x_orientation_angle) AS x_orientation_angle,\n max(x_orientation_angle) AS x_orientation_angle_max,\n min(x_orientation_angle) AS x_orientation_angle_min,\n avg(y_orientation_angle) AS y_orientation_angle,\n max(y_orientation_angle) AS y_orientation_angle_max,\n min(y_orientation_angle) AS y_orientation_angle_min\n FROM all_data\n GROUP BY measured_at, station_id\n ORDER BY measured_at, station_id\n "¶

dew_point: Mapped[Decimal]¶: dew point temperature in °C calculated using thermal_comfort.dew_point()

dew_point_max: Mapped[Decimal]¶: maximum of dew point temperature in °C calculated using thermal_comfort.dew_point()

dew_point_min: Mapped[Decimal]¶: minimum of dew point temperature in °C calculated using thermal_comfort.dew_point()

heat_index: Mapped[Decimal]¶: heat index in °C calculated using thermal_comfort.heat_index_extended()

heat_index_max: Mapped[Decimal]¶: maximum of heat index in °C calculated using thermal_comfort.heat_index_extended()

heat_index_min: Mapped[Decimal]¶: minimum of heat index in °C calculated using thermal_comfort.heat_index_extended()

lightning_average_distance: Mapped[Decimal]¶: distance of lightning strikes in km

lightning_average_distance_max: Mapped[Decimal]¶: maximum of distance of lightning strikes in km

lightning_average_distance_min: Mapped[Decimal]¶: minimum of distance of lightning strikes in km

lightning_strike_count: Mapped[Decimal]¶: number of lightning strikes

maximum_wind_speed: Mapped[Decimal]¶: maximum wind speed in m/s (gusts)

measured_at: Mapped[datetime]¶: The exact time the value was measured in UTC

mrt: Mapped[Decimal]¶: mean radiant temperature in °C calculated using thermal_comfort.mean_radiant_temp()

mrt_max: Mapped[Decimal]¶: maximum of mean radiant temperature in °C calculated using thermal_comfort.mean_radiant_temp()

mrt_min: Mapped[Decimal]¶: minimum of mean radiant temperature in °C calculated using thermal_comfort.mean_radiant_temp()

pet: Mapped[Decimal]¶: physiological equivalent temperature in °C calculated using thermal_comfort.pet_static()

pet_category: Mapped[HeatStressCategories]¶: physiological equivalent temperature category derived from PET_STRESS_CATEGORIES and applied using app.tasks.category_mapping()

pet_max: Mapped[Decimal]¶: maximum of physiological equivalent temperature in °C calculated using thermal_comfort.pet_static()

pet_min: Mapped[Decimal]¶: minimum of physiological equivalent temperature in °C calculated using thermal_comfort.pet_static()

precipitation_sum: Mapped[Decimal]¶: precipitation sum in mm

protocol_version: Mapped[int]¶: The protocol version the data was sent with

relative_humidity: Mapped[Decimal]¶: relative humidity in %

relative_humidity_max: Mapped[Decimal]¶: maximum of relative humidity in %

relative_humidity_min: Mapped[Decimal]¶: minimum of relative humidity in %

sensor_temperature_internal: Mapped[Decimal]¶: internal temperature of the sensor in °C

sensor_temperature_internal_max: Mapped[Decimal]¶: maximum of internal temperature of the sensor in °C

sensor_temperature_internal_min: Mapped[Decimal]¶: minimum of internal temperature of the sensor in °C

solar_radiation: Mapped[Decimal]¶: solar radiation in W/m2

solar_radiation_max: Mapped[Decimal]¶: maximum of solar radiation in W/m2

solar_radiation_min: Mapped[Decimal]¶: minimum of solar radiation in W/m2

specific_humidity: Mapped[Decimal]¶: specific humidity in g/kg calculated using thermal_comfort.specific_humidity()

specific_humidity_max: Mapped[Decimal]¶: maximum of specific humidity in g/kg calculated using thermal_comfort.specific_humidity()

specific_humidity_min: Mapped[Decimal]¶: minimum of specific humidity in g/kg calculated using thermal_comfort.specific_humidity()

station: Mapped[Station]¶: The station the data was measured at

station_id: Mapped[str]¶: id of the station these measurements were taken at

thermistor_resistance: Mapped[Decimal]¶: thermistor resistance in Ohms

thermistor_resistance_max: Mapped[Decimal]¶: maximum of thermistor resistance in Ohms

thermistor_resistance_min: Mapped[Decimal]¶: minimum of thermistor resistance in Ohms

u_wind: Mapped[Decimal]¶: u wind component in m/s

u_wind_max: Mapped[Decimal]¶: maximum of u wind component in m/s

u_wind_min: Mapped[Decimal]¶: minimum of u wind component in m/s

utci: Mapped[Decimal]¶: universal thermal climate index in °C calculated using thermal_comfort.utci_approx()

utci_category: Mapped[HeatStressCategories]¶: universal thermal climate index category derived from UTCI_STRESS_CATEGORIES and applied using app.tasks.category_mapping()

utci_max: Mapped[Decimal]¶: maximum of universal thermal climate index in °C calculated using thermal_comfort.utci_approx()

utci_min: Mapped[Decimal]¶: minimum of universal thermal climate index in °C calculated using thermal_comfort.utci_approx()

v_wind: Mapped[Decimal]¶: v wind component in m/s

v_wind_max: Mapped[Decimal]¶: maximum of v wind component in m/s

v_wind_min: Mapped[Decimal]¶: minimum of v wind component in m/s

vapor_pressure: Mapped[Decimal]¶: vapor pressure in kPa

vapor_pressure_max: Mapped[Decimal]¶: maximum of vapor pressure in kPa

vapor_pressure_min: Mapped[Decimal]¶: minimum of vapor pressure in kPa

voltage_ratio: Mapped[Decimal]¶: voltage ratio of the sensor

voltage_ratio_max: Mapped[Decimal]¶: maximum of voltage ratio of the sensor

voltage_ratio_min: Mapped[Decimal]¶: minimum of voltage ratio of the sensor

wet_bulb_temperature: Mapped[Decimal]¶: wet bulb temperature in °C calculated using thermal_comfort.wet_bulb_temp()

wet_bulb_temperature_max: Mapped[Decimal]¶: maximum of wet bulb temperature in °C calculated using thermal_comfort.wet_bulb_temp()

wet_bulb_temperature_min: Mapped[Decimal]¶: minimum of wet bulb temperature in °C calculated using thermal_comfort.wet_bulb_temp()

wind_direction: Mapped[Decimal]¶: wind direction in °

wind_speed: Mapped[Decimal]¶: wind speed in m/s

wind_speed_max: Mapped[Decimal]¶: maximum of wind speed in m/s

wind_speed_min: Mapped[Decimal]¶: minimum of wind speed in m/s

x_orientation_angle: Mapped[Decimal]¶: x-tilt angle of the sensor in °

x_orientation_angle_max: Mapped[Decimal]¶: maximum of x-tilt angle of the sensor in °

x_orientation_angle_min: Mapped[Decimal]¶: minimum of x-tilt angle of the sensor in °

y_orientation_angle: Mapped[Decimal]¶: y-tilt angle of the sensor in °

y_orientation_angle_max: Mapped[Decimal]¶: maximum of y-tilt angle of the sensor in °

y_orientation_angle_min: Mapped[Decimal]¶: minimum of y-tilt angle of the sensor in °

class app.models.BuddyCheckQc(**kwargs)[source]¶

The quality control flags returned by the buddy check for a station.

air_temperature_qc_buddy_check: Mapped[bool]¶: quality control for the air temperature using a buddy check

air_temperature_qc_isolated_check: Mapped[bool]¶: quality control for the air temperature using an isolation check

atmospheric_pressure_qc_buddy_check: Mapped[bool]¶: quality control for the atmospheric pressure using a buddy check

atmospheric_pressure_qc_isolated_check: Mapped[bool]¶: quality control for the atmospheric pressure using an isolation check

measured_at: Mapped[datetime]¶: The exact time the value was measured in UTC

qc_score: Mapped[Decimal]¶: Quality control score of the data. This is calculated by weighting the different QC checks according to their severity

relative_humidity_qc_buddy_check: Mapped[bool]¶: quality control for the relative humidity using a buddy check

relative_humidity_qc_isolated_check: Mapped[bool]¶: quality control for the relative humidity using an isolation check

station_id: Mapped[str]¶: id of the station these measurements were taken at

class app.models.HeatStressCategories(value, names=<not given>, *values, module=None, qualname=None, type=None, start=1, boundary=None)[source]¶

Enum for the different heat stress categories as defined by PET or UTCI.

extreme_cold_stress = 'extreme cold stress'¶

extreme_heat_stress = 'extreme heat stress'¶

moderate_cold_stress = 'moderate cold stress'¶

moderate_heat_stress = 'moderate heat stress'¶

no_thermal_stress = 'no thermal stress'¶

slight_cold_stress = 'slight cold stress'¶

slight_heat_stress = 'slight heat stress'¶

strong_cold_stress = 'strong cold stress'¶

strong_heat_stress = 'strong heat stress'¶

unknown = 'unknown'¶

very_strong_cold_stress = 'very strong cold stress'¶

very_strong_heat_stress = 'very strong heat stress'¶

class app.models.LatestData(**kwargs)[source]¶

This is not an actual table, but a materialized view. We simply trick sqlalchemy into thinking this was a table. Querying a materialized view does not differ from querying a proper table.

The query for creating this materialized view is saved above.

absolute_humidity: Mapped[Decimal]¶: absolute humidity in g/m3 calculated using thermal_comfort.absolute_humidity()

air_temperature: Mapped[Decimal]¶: air temperature in °C

air_temperature_qc_buddy_check: Mapped[bool]¶: quality control for the air temperature using a buddy check

air_temperature_qc_isolated_check: Mapped[bool]¶: quality control for the air temperature using an isolation check

air_temperature_qc_persistence_check: Mapped[bool]¶

air_temperature_qc_range_check: Mapped[bool]¶

air_temperature_qc_spike_dip_check: Mapped[bool]¶

altitude: Mapped[float]¶: altitude of the station in m a.s.l.

atmospheric_pressure: Mapped[Decimal]¶: atmospheric pressure in kPa

atmospheric_pressure_qc_buddy_check: Mapped[bool]¶: quality control for the atmospheric pressure using a buddy check

atmospheric_pressure_qc_isolated_check: Mapped[bool]¶: quality control for the atmospheric pressure using an isolation check

atmospheric_pressure_qc_persistence_check: Mapped[bool]¶

atmospheric_pressure_qc_range_check: Mapped[bool]¶

atmospheric_pressure_qc_spike_dip_check: Mapped[bool]¶

atmospheric_pressure_reduced: Mapped[Decimal]¶: atmospheric pressure reduced to sea level in hPa calculated using app.tasks.reduce_pressure()

battery_voltage: Mapped[Decimal]¶: The battery voltage of the sensor in Volts

black_globe_temperature: Mapped[Decimal]¶: black globe temperature in °C

black_globe_temperature_qc_persistence_check: Mapped[bool]¶

black_globe_temperature_qc_range_check: Mapped[bool]¶

black_globe_temperature_qc_spike_dip_check: Mapped[bool]¶

creation_sql: str = " CREATE MATERIALIZED VIEW IF NOT EXISTS latest_data AS\n (\n SELECT DISTINCT ON (station_id)\n biomet_data.station_id,\n long_name,\n latitude,\n longitude,\n altitude,\n district,\n lcz,\n station_type,\n biomet_data.measured_at,\n air_temperature,\n relative_humidity,\n dew_point,\n absolute_humidity,\n specific_humidity,\n heat_index,\n wet_bulb_temperature,\n atmospheric_pressure,\n atmospheric_pressure_reduced,\n lightning_average_distance,\n lightning_strike_count,\n mrt,\n pet,\n pet_category,\n precipitation_sum,\n solar_radiation,\n utci,\n utci_category,\n vapor_pressure,\n wind_direction,\n wind_speed,\n maximum_wind_speed,\n u_wind,\n v_wind,\n sensor_temperature_internal,\n x_orientation_angle,\n y_orientation_angle,\n black_globe_temperature,\n thermistor_resistance,\n voltage_ratio,\n air_temperature_qc_range_check,\n air_temperature_qc_persistence_check,\n air_temperature_qc_spike_dip_check,\n relative_humidity_qc_range_check,\n relative_humidity_qc_persistence_check,\n relative_humidity_qc_spike_dip_check,\n atmospheric_pressure_qc_range_check,\n atmospheric_pressure_qc_persistence_check,\n atmospheric_pressure_qc_spike_dip_check,\n wind_speed_qc_range_check,\n wind_speed_qc_persistence_check,\n wind_speed_qc_spike_dip_check,\n wind_direction_qc_range_check,\n wind_direction_qc_persistence_check,\n u_wind_qc_range_check,\n u_wind_qc_persistence_check,\n u_wind_qc_spike_dip_check,\n v_wind_qc_range_check,\n v_wind_qc_persistence_check,\n v_wind_qc_spike_dip_check,\n maximum_wind_speed_qc_range_check,\n maximum_wind_speed_qc_persistence_check,\n precipitation_sum_qc_range_check,\n precipitation_sum_qc_persistence_check,\n precipitation_sum_qc_spike_dip_check,\n solar_radiation_qc_range_check,\n solar_radiation_qc_persistence_check,\n solar_radiation_qc_spike_dip_check,\n lightning_average_distance_qc_range_check,\n lightning_average_distance_qc_persistence_check,\n lightning_strike_count_qc_range_check,\n lightning_strike_count_qc_persistence_check,\n x_orientation_angle_qc_range_check,\n x_orientation_angle_qc_spike_dip_check,\n y_orientation_angle_qc_range_check,\n y_orientation_angle_qc_spike_dip_check,\n black_globe_temperature_qc_range_check,\n black_globe_temperature_qc_persistence_check,\n black_globe_temperature_qc_spike_dip_check,\n qc_flagged,\n air_temperature_qc_isolated_check,\n air_temperature_qc_buddy_check,\n relative_humidity_qc_isolated_check,\n relative_humidity_qc_buddy_check,\n atmospheric_pressure_qc_isolated_check,\n atmospheric_pressure_qc_buddy_check,\n qc_score,\n battery_voltage,\n protocol_version\n FROM biomet_data\n INNER JOIN station ON biomet_data.station_id = station.station_id\n LEFT OUTER JOIN buddy_check_qc ON (\n biomet_data.station_id = buddy_check_qc.station_id AND\n biomet_data.measured_at = buddy_check_qc.measured_at\n )\n ORDER BY biomet_data.station_id, biomet_data.measured_at DESC\n )\n UNION ALL\n (\n SELECT DISTINCT ON (station_id)\n temp_rh_data.station_id,\n long_name,\n latitude,\n longitude,\n altitude,\n district,\n lcz,\n station_type,\n temp_rh_data.measured_at,\n air_temperature,\n relative_humidity,\n dew_point,\n absolute_humidity,\n specific_humidity,\n heat_index,\n wet_bulb_temperature,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n air_temperature_qc_range_check,\n air_temperature_qc_persistence_check,\n air_temperature_qc_spike_dip_check,\n relative_humidity_qc_range_check,\n relative_humidity_qc_persistence_check,\n relative_humidity_qc_spike_dip_check,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n NULL,\n qc_flagged,\n air_temperature_qc_isolated_check,\n air_temperature_qc_buddy_check,\n relative_humidity_qc_isolated_check,\n relative_humidity_qc_buddy_check,\n NULL,\n NULL,\n qc_score,\n battery_voltage,\n protocol_version\n FROM temp_rh_data\n INNER JOIN station ON temp_rh_data.station_id = station.station_id\n LEFT OUTER JOIN buddy_check_qc ON (\n temp_rh_data.station_id = buddy_check_qc.station_id AND\n temp_rh_data.measured_at = buddy_check_qc.measured_at\n )\n WHERE station.station_type <> 'double'\n ORDER BY temp_rh_data.station_id, temp_rh_data.measured_at DESC\n )\n "¶

dew_point: Mapped[Decimal]¶: dew point temperature in °C calculated using thermal_comfort.dew_point()

district: Mapped[str]¶: name of the district the station is located in

heat_index: Mapped[Decimal]¶: heat index in °C calculated using thermal_comfort.heat_index_extended()

latitude: Mapped[float]¶: latitude of the station in decimal degrees

lcz: Mapped[str]¶: local climate zone of the station

lightning_average_distance: Mapped[Decimal]¶: distance of lightning strikes in km

lightning_average_distance_qc_persistence_check: Mapped[bool]¶

lightning_average_distance_qc_range_check: Mapped[bool]¶

lightning_strike_count: Mapped[Decimal]¶: number of lightning strikes

lightning_strike_count_qc_persistence_check: Mapped[bool]¶

lightning_strike_count_qc_range_check: Mapped[bool]¶

long_name: Mapped[str]¶: long name of the station e.g. Nordmarkt

longitude: Mapped[float]¶: longitude of the station in decimal degrees

maximum_wind_speed: Mapped[Decimal]¶: maximum wind speed in m/s (gusts)

maximum_wind_speed_qc_persistence_check: Mapped[bool]¶

maximum_wind_speed_qc_range_check: Mapped[bool]¶

measured_at: Mapped[datetime]¶: The exact time the value was measured in UTC

mrt: Mapped[Decimal]¶: mean radiant temperature in °C calculated using thermal_comfort.mean_radiant_temp()

pet: Mapped[Decimal]¶: physiological equivalent temperature in °C calculated using thermal_comfort.pet_static()

pet_category: Mapped[HeatStressCategories]¶: physiological equivalent temperature category derived from PET_STRESS_CATEGORIES and applied using app.tasks.category_mapping()

precipitation_sum: Mapped[Decimal]¶: precipitation sum in mm

precipitation_sum_qc_persistence_check: Mapped[bool]¶

precipitation_sum_qc_range_check: Mapped[bool]¶

precipitation_sum_qc_spike_dip_check: Mapped[bool]¶

protocol_version: Mapped[int]¶: The protocol version the data was sent with

qc_flagged: Mapped[bool]¶

qc_score: Mapped[Decimal]¶: Quality control score of the data. This is calculated by weighting the different QC checks according to their severity

async classmethod refresh(*, concurrently=True, **kwargs) → None[source]¶

override the base refresh since this is actually a materialized view.

Return type:: None

relative_humidity: Mapped[Decimal]¶: relative humidity in %

relative_humidity_qc_buddy_check: Mapped[bool]¶: quality control for the relative humidity using a buddy check

relative_humidity_qc_isolated_check: Mapped[bool]¶: quality control for the relative humidity using an isolation check

relative_humidity_qc_persistence_check: Mapped[bool]¶

relative_humidity_qc_range_check: Mapped[bool]¶

relative_humidity_qc_spike_dip_check: Mapped[bool]¶

sensor_temperature_internal: Mapped[Decimal]¶: internal temperature of the sensor in °C

solar_radiation: Mapped[Decimal]¶: solar radiation in W/m2

solar_radiation_qc_persistence_check: Mapped[bool]¶

solar_radiation_qc_range_check: Mapped[bool]¶

solar_radiation_qc_spike_dip_check: Mapped[bool]¶

specific_humidity: Mapped[Decimal]¶: specific humidity in g/kg calculated using thermal_comfort.specific_humidity()

station: Mapped[Station]¶: The station the data was measured at

station_id: Mapped[str]¶: id of the station e.g. DOBNOM

station_type: Mapped[StationType]¶: type of the station e.g. temprh

thermistor_resistance: Mapped[Decimal]¶: thermistor resistance in Ohms

u_wind: Mapped[Decimal]¶: u wind component in m/s

u_wind_qc_persistence_check: Mapped[bool]¶

u_wind_qc_range_check: Mapped[bool]¶

u_wind_qc_spike_dip_check: Mapped[bool]¶

utci: Mapped[Decimal]¶: universal thermal climate index in °C calculated using thermal_comfort.utci_approx()

utci_category: Mapped[HeatStressCategories]¶: universal thermal climate index category derived from UTCI_STRESS_CATEGORIES and applied using app.tasks.category_mapping()

v_wind: Mapped[Decimal]¶: v wind component in m/s

v_wind_qc_persistence_check: Mapped[bool]¶

v_wind_qc_range_check: Mapped[bool]¶

v_wind_qc_spike_dip_check: Mapped[bool]¶

vapor_pressure: Mapped[Decimal]¶: vapor pressure in kPa

voltage_ratio: Mapped[Decimal]¶: voltage ratio of the sensor

wet_bulb_temperature: Mapped[Decimal]¶: wet bulb temperature in °C calculated using thermal_comfort.wet_bulb_temp()

wind_direction: Mapped[Decimal]¶: wind direction in °

wind_direction_qc_persistence_check: Mapped[bool]¶

wind_direction_qc_range_check: Mapped[bool]¶

wind_speed: Mapped[Decimal]¶: wind speed in m/s

wind_speed_qc_persistence_check: Mapped[bool]¶

wind_speed_qc_range_check: Mapped[bool]¶

wind_speed_qc_spike_dip_check: Mapped[bool]¶

x_orientation_angle: Mapped[Decimal]¶: x-tilt angle of the sensor in °

x_orientation_angle_qc_range_check: Mapped[bool]¶

x_orientation_angle_qc_spike_dip_check: Mapped[bool]¶

y_orientation_angle: Mapped[Decimal]¶: y-tilt angle of the sensor in °

y_orientation_angle_qc_range_check: Mapped[bool]¶

y_orientation_angle_qc_spike_dip_check: Mapped[bool]¶

class app.models.MaterializedView(**kwargs) → None[source]¶

Baseclass for a materialized view. The view is faked as a table, so that sqlalchemy can handle it like a table and so we can incrementally refresh it.

creation_sql: str¶

async classmethod get_view_state() → datetime | None[source]¶

Return type:: datetime | None

is_continuous_aggregate = False¶

async classmethod refresh(*, concurrently=True, window_start=None, window_end=None) → None[source]¶

Refresh the materialized view.

This takes into account whether the view is a continuous aggregate or a vanilla postgres materialized view. This needs to be done outside of a transaction.

Parameters:

concurrently (bool) – Whether to refresh the view concurrently. Refreshing concurrently is slower, however no exclusive lock is acquired. This way reads to the view can still be performed. This only applies to vanilla postgres materialized views. (default: True)
window_start (datetime | None) – The start of the window that will be refreshed. This only applies to continuous aggregates. This is not inclusive. (default: None)
window_end (datetime | None) – The end of the window that will be refreshed. This only applies to continuous aggregates. This is inclusive. (default: None)

Return type:

None

station_id: Mapped[str] = <sqlalchemy.orm.properties.MappedColumn object>¶

class app.models.SHT35DataRaw(**kwargs)[source]¶

air_temperature: Mapped[Decimal]¶: air temperature in °C

battery_voltage: Mapped[Decimal]¶: The battery voltage of the sensor in Volts

measured_at: Mapped[datetime]¶: The exact time the value was measured in UTC

protocol_version: Mapped[int]¶: The protocol version the data was sent with

relative_humidity: Mapped[Decimal]¶: relative humidity in %

sensor: Mapped[Sensor]¶: The sensor the data was measured with

sensor_id: Mapped[str]¶: id of the sensor e.g. DEC1234

class app.models.Sensor(**kwargs)[source]¶

Pool of sensors that can be installed at a station

current_station: Mapped[Station | None]¶: the station the sensor is currently deployed at

deployments: Mapped[list[SensorDeployment]]¶: list of all deployments of the sensor

device_id: Mapped[int]¶: device id of the sensor e.g. 1234567890

former_stations: Mapped[list[Station]]¶: list of stations the sensor was previously deployed at

relhum_calib_offset: Mapped[Decimal]¶: relative humidity calibration offset in %. This is the offset that is applied to the relative humidity value before it is stored in the database

sensor_id: Mapped[str]¶: id of the sensor e.g. DEC1234

sensor_type: Mapped[SensorType]¶: type of the sensor e.g. biomet

temp_calib_offset: Mapped[Decimal]¶: temperature calibration offset in °C. This is the offset that is applied to the temperature value before it is stored in the database

class app.models.SensorDeployment(**kwargs)[source]¶

Deployment of a sensor at a station

deployment_id: Mapped[int]¶

sensor: Mapped[Sensor]¶

sensor_id: Mapped[str]¶

setup_date: Mapped[datetime]¶

station: Mapped[Station]¶

station_id: Mapped[str]¶

teardown_date: Mapped[datetime | None]¶

class app.models.SensorType(value, names=<not given>, *values, module=None, qualname=None, type=None, start=1, boundary=None)[source]¶

Enum differentiating between the different types of sensors:

atm41: ATM41 sensor with many parameters
blg: BLG sensor with black globe temperature
sht35: SHT35 sensor with temperature and relative humidity

atm41 = 'atm41'¶

blg = 'blg'¶

sht35 = 'sht35'¶

class app.models.Station(**kwargs)[source]¶

Representation of a station which has a physical location and sensor(s) attached to it.

active_deployments: Mapped[list[SensorDeployment]]¶: list of deployments that are currently active at the station

active_sensors: Mapped[list[Sensor]]¶: list of sensors that are currently deployed at the station

altitude: Mapped[float]¶: altitude of the station in m a.s.l.

artificial_heat_sources: Mapped[str]¶: artificial heat sources at the station

blg_sensor_distance_from_mounting_structure: Mapped[Decimal | None]¶: the distance of the black globe sensor of the station from the mounting structure

blg_sensor_height_agl: Mapped[Decimal | None]¶: the mounting height of the black globe sensor of the station

blg_sensor_orientation: Mapped[Decimal | None]¶: the orientation (-angle) of the arm of the black globe sensor of the station from the mounting structure

city: Mapped[str]¶: name of the city the station is located in

comment: Mapped[str | None]¶: a text describing the station

country: Mapped[str]¶: name of the country the station is located in

deployments: Mapped[list[SensorDeployment]]¶: list of all deployments at the station

district: Mapped[str]¶: name of the district the station is located in

dominant_land_use: Mapped[str | None]¶: dominant land use at the station

former_deployments: Mapped[list[SensorDeployment]]¶: list of deployments that were previously active at the station

former_sensors: Mapped[list[Sensor]]¶: list of sensors that were previously deployed at the station

property full_address: str¶: Returns the full address of the station as a string.

latitude: Mapped[float]¶: latitude of the station in decimal degrees

lcz: Mapped[str | None]¶: local climate zone of the station

leuchtennummer: Mapped[int]¶: the number of the streetlight the sensor is mounted to

long_name: Mapped[str]¶: long name of the station e.g. Nordmarkt

longitude: Mapped[float]¶: longitude of the station in decimal degrees

mounting_structure_diameter: Mapped[Decimal | None]¶: the diameter of the mounting structure at the mounting height

mounting_structure_height_agl: Mapped[Decimal | None]¶: the total height of the mounting structure above ground level

mounting_structure_light_extension_offset: Mapped[Decimal | None]¶: when mounted to a lantern post, the overhang of the lantern

mounting_structure_material: Mapped[str | None]¶: The material the structure the sensor is mounted to is made of e.g. metal, wood, …

mounting_type: Mapped[str | None]¶: the structure the sensor is mounted to e.g. black mast, building, …

number: Mapped[str | None]¶: if possible, the number of the closest building

orographic_setting: Mapped[str | None]¶: orographic setting of the station e.g. flat, hilly, …

plz: Mapped[int]¶: postal code of the station

proximity_to_building: Mapped[Decimal | None]¶: the distance to the closest building in m

proximity_to_parking: Mapped[Decimal | None]¶: the distance to the closest parking lot in m

proximity_to_tree: Mapped[Decimal | None]¶: the distance to the closest tree in m

sensor_distance_from_mounting_structure: Mapped[Decimal | None]¶: the distance of the main component of the station (ATM41 or SHT35) from the mounting structure

sensor_height_agl: Mapped[Decimal | None]¶: the mounting height of the main component of the station (ATM41 or SHT35)

sensor_orientation: Mapped[Decimal | None]¶: the orientation (-angle) of the arm of the main component of the station (ATM41 or SHT35) from the mounting structure

station_id: Mapped[str]¶: id of the station e.g. DOBNOM

station_type: Mapped[StationType]¶: type of the station e.g. temprh

street: Mapped[str]¶: name of the street the station is located at

surrounding_land_cover_description: Mapped[str | None]¶: a text describing the surrounding land cover

svf: Mapped[Decimal]¶

urban_atlas_class_name: Mapped[str | None]¶: urban atlas class name of the station

urban_atlas_class_nr: Mapped[int | None]¶: urban atlas class number of the station

class app.models.StationType(value, names=<not given>, *values, module=None, qualname=None, type=None, start=1, boundary=None)[source]¶

Enum differentiating between the different types of stations:

temprh: station with a SHT35 sensor
biomet: station with an ATM41 and a BLG sensor
double: station with an ATM41, SHT35, and a BLG sensor

biomet = 'biomet'¶

double = 'double'¶

temprh = 'temprh'¶

class app.models.TempRHData(**kwargs)[source]¶

absolute_humidity: Mapped[Decimal]¶: absolute humidity in g/m3 calculated using thermal_comfort.absolute_humidity()

air_temperature: Mapped[Decimal]¶: air temperature in °C

air_temperature_qc_persistence_check: Mapped[bool]¶

air_temperature_qc_range_check: Mapped[bool]¶

air_temperature_qc_spike_dip_check: Mapped[bool]¶

air_temperature_raw: Mapped[Decimal]¶: raw air temperature in °C with no calibration applied

battery_voltage: Mapped[Decimal]¶: The battery voltage of the sensor in Volts

deployment: Mapped[SensorDeployment]¶: the deployment that made the measurement of this data

dew_point: Mapped[Decimal]¶: dew point temperature in °C calculated using thermal_comfort.dew_point()

heat_index: Mapped[Decimal]¶: heat index in °C calculated using thermal_comfort.heat_index_extended()

measured_at: Mapped[datetime]¶: The exact time the value was measured in UTC

protocol_version: Mapped[int]¶: The protocol version the data was sent with

qc_flagged: Mapped[bool]¶

relative_humidity: Mapped[Decimal]¶: relative humidity in %

relative_humidity_qc_persistence_check: Mapped[bool]¶

relative_humidity_qc_range_check: Mapped[bool]¶

relative_humidity_qc_spike_dip_check: Mapped[bool]¶

relative_humidity_raw: Mapped[Decimal]¶: raw relative humidity in % with no calibration applied

sensor: Mapped[Sensor]¶: The sensor the data was measured with

sensor_id: Mapped[str]¶: id of the SHT35 sensor these measurements were taken with

specific_humidity: Mapped[Decimal]¶: specific humidity in g/kg calculated using thermal_comfort.specific_humidity()

station: Mapped[Station]¶: The station the data was measured at

station_id: Mapped[str]¶: id of the station these measurements were taken at

wet_bulb_temperature: Mapped[Decimal]¶: wet bulb temperature in °C calculated using thermal_comfort.wet_bulb_temp()

class app.models.TempRHDataDaily(**kwargs)[source]¶

This is not an actual table, but a materialized view. We simply trick sqlalchemy into thinking this was a table. Querying a materialized view does not differ from querying a proper table.

absolute_humidity: Mapped[Decimal]¶: absolute humidity in g/m3 calculated using thermal_comfort.absolute_humidity()

absolute_humidity_max: Mapped[Decimal]¶: maximum of absolute humidity in g/m3 calculated using thermal_comfort.absolute_humidity()

absolute_humidity_min: Mapped[Decimal]¶: minimum of absolute humidity in g/m3 calculated using thermal_comfort.absolute_humidity()

air_temperature: Mapped[Decimal]¶: air temperature in °C

air_temperature_max: Mapped[Decimal]¶: maximum of air temperature in °C

air_temperature_min: Mapped[Decimal]¶: minimum of air temperature in °C

air_temperature_raw: Mapped[Decimal]¶: raw air temperature in °C with no calibration applied

air_temperature_raw_max: Mapped[Decimal]¶: maximum of raw air temperature in °C with no calibration applied

air_temperature_raw_min: Mapped[Decimal]¶: minimum of raw air temperature in °C with no calibration applied

battery_voltage: Mapped[Decimal]¶: The battery voltage of the sensor in Volts

battery_voltage_max: Mapped[Decimal]¶: maximum of The battery voltage of the sensor in Volts

battery_voltage_min: Mapped[Decimal]¶: minimum of The battery voltage of the sensor in Volts

creation_sql: str = " WITH data_bounds AS (\n SELECT\n station_id,\n MIN(measured_at) AS start_time,\n MAX(measured_at) AS end_time\n FROM temp_rh_data\n WHERE measured_at BETWEEN :window_start AND :window_end\n GROUP BY station_id\n ), filling_time_series AS (\n SELECT generate_series(\n DATE_TRUNC('hour', (\n SELECT MIN(measured_at) FROM temp_rh_data\n WHERE measured_at BETWEEN :window_start AND :window_end)\n ),\n DATE_TRUNC('hour', (\n SELECT MAX(measured_at) FROM temp_rh_data\n WHERE measured_at BETWEEN :window_start AND :window_end) + '1 hour'::INTERVAL\n ),\n '1 hour'::INTERVAL\n ) AS measured_at\n ),\n stations_subset AS (\n -- TODO: this could be faster if check the station table by station_type\n SELECT DISTINCT station_id FROM temp_rh_data\n ),\n time_station_combinations AS (\n SELECT\n measured_at,\n stations_subset.station_id,\n start_time,\n end_time\n FROM filling_time_series\n CROSS JOIN stations_subset\n JOIN data_bounds\n ON data_bounds.station_id = stations_subset.station_id\n WHERE filling_time_series.measured_at >= data_bounds.start_time\n AND filling_time_series.measured_at <= data_bounds.end_time\n ), all_data AS(\n (\n SELECT\n measured_at AS ma,\n station_id,\n NULL AS absolute_humidity,\n NULL AS air_temperature,\n NULL AS air_temperature_raw,\n NULL AS battery_voltage,\n NULL AS dew_point,\n NULL AS heat_index,\n NULL AS protocol_version,\n NULL AS relative_humidity,\n NULL AS relative_humidity_raw,\n NULL AS specific_humidity,\n NULL AS wet_bulb_temperature\n FROM time_station_combinations\n )\n UNION ALL\n (\n SELECT\n measured_at AS ma,\n station_id,\n absolute_humidity,\n air_temperature,\n air_temperature_raw,\n battery_voltage,\n dew_point,\n heat_index,\n protocol_version,\n relative_humidity,\n relative_humidity_raw,\n specific_humidity,\n wet_bulb_temperature\n FROM temp_rh_data\n WHERE measured_at BETWEEN :window_start AND :window_end\n )\n ) SELECT\n (time_bucket('1day', ma, 'CET') + '1 hour'::INTERVAL)::DATE AS measured_at,\n station_id,\n CASE\n WHEN (count(*) FILTER (\n WHERE absolute_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(absolute_humidity)\n ELSE NULL\n END AS absolute_humidity,\n CASE\n WHEN (count(*) FILTER (\n WHERE absolute_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN max(absolute_humidity)\n ELSE NULL\n END AS absolute_humidity_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE absolute_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN min(absolute_humidity)\n ELSE NULL\n END AS absolute_humidity_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE air_temperature IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(air_temperature)\n ELSE NULL\n END AS air_temperature,\n CASE\n WHEN (count(*) FILTER (\n WHERE air_temperature IS NOT NULL) / 288.0\n ) > 0.7 THEN max(air_temperature)\n ELSE NULL\n END AS air_temperature_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE air_temperature IS NOT NULL) / 288.0\n ) > 0.7 THEN min(air_temperature)\n ELSE NULL\n END AS air_temperature_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE air_temperature_raw IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(air_temperature_raw)\n ELSE NULL\n END AS air_temperature_raw,\n CASE\n WHEN (count(*) FILTER (\n WHERE air_temperature_raw IS NOT NULL) / 288.0\n ) > 0.7 THEN max(air_temperature_raw)\n ELSE NULL\n END AS air_temperature_raw_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE air_temperature_raw IS NOT NULL) / 288.0\n ) > 0.7 THEN min(air_temperature_raw)\n ELSE NULL\n END AS air_temperature_raw_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE battery_voltage IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(battery_voltage)\n ELSE NULL\n END AS battery_voltage,\n CASE\n WHEN (count(*) FILTER (\n WHERE battery_voltage IS NOT NULL) / 288.0\n ) > 0.7 THEN max(battery_voltage)\n ELSE NULL\n END AS battery_voltage_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE battery_voltage IS NOT NULL) / 288.0\n ) > 0.7 THEN min(battery_voltage)\n ELSE NULL\n END AS battery_voltage_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE dew_point IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(dew_point)\n ELSE NULL\n END AS dew_point,\n CASE\n WHEN (count(*) FILTER (\n WHERE dew_point IS NOT NULL) / 288.0\n ) > 0.7 THEN max(dew_point)\n ELSE NULL\n END AS dew_point_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE dew_point IS NOT NULL) / 288.0\n ) > 0.7 THEN min(dew_point)\n ELSE NULL\n END AS dew_point_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE heat_index IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(heat_index)\n ELSE NULL\n END AS heat_index,\n CASE\n WHEN (count(*) FILTER (\n WHERE heat_index IS NOT NULL) / 288.0\n ) > 0.7 THEN max(heat_index)\n ELSE NULL\n END AS heat_index_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE heat_index IS NOT NULL) / 288.0\n ) > 0.7 THEN min(heat_index)\n ELSE NULL\n END AS heat_index_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE protocol_version IS NOT NULL) / 288.0\n ) > 0.7 THEN mode() WITHIN GROUP (ORDER BY protocol_version ASC)\n ELSE NULL\n END AS protocol_version,\n CASE\n WHEN (count(*) FILTER (\n WHERE relative_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(relative_humidity)\n ELSE NULL\n END AS relative_humidity,\n CASE\n WHEN (count(*) FILTER (\n WHERE relative_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN max(relative_humidity)\n ELSE NULL\n END AS relative_humidity_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE relative_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN min(relative_humidity)\n ELSE NULL\n END AS relative_humidity_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE relative_humidity_raw IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(relative_humidity_raw)\n ELSE NULL\n END AS relative_humidity_raw,\n CASE\n WHEN (count(*) FILTER (\n WHERE relative_humidity_raw IS NOT NULL) / 288.0\n ) > 0.7 THEN max(relative_humidity_raw)\n ELSE NULL\n END AS relative_humidity_raw_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE relative_humidity_raw IS NOT NULL) / 288.0\n ) > 0.7 THEN min(relative_humidity_raw)\n ELSE NULL\n END AS relative_humidity_raw_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE specific_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(specific_humidity)\n ELSE NULL\n END AS specific_humidity,\n CASE\n WHEN (count(*) FILTER (\n WHERE specific_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN max(specific_humidity)\n ELSE NULL\n END AS specific_humidity_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE specific_humidity IS NOT NULL) / 288.0\n ) > 0.7 THEN min(specific_humidity)\n ELSE NULL\n END AS specific_humidity_min,\n CASE\n WHEN (count(*) FILTER (\n WHERE wet_bulb_temperature IS NOT NULL) / 288.0\n ) > 0.7 THEN avg(wet_bulb_temperature)\n ELSE NULL\n END AS wet_bulb_temperature,\n CASE\n WHEN (count(*) FILTER (\n WHERE wet_bulb_temperature IS NOT NULL) / 288.0\n ) > 0.7 THEN max(wet_bulb_temperature)\n ELSE NULL\n END AS wet_bulb_temperature_max,\n CASE\n WHEN (count(*) FILTER (\n WHERE wet_bulb_temperature IS NOT NULL) / 288.0\n ) > 0.7 THEN min(wet_bulb_temperature)\n ELSE NULL\n END AS wet_bulb_temperature_min\n FROM all_data\n GROUP BY measured_at, station_id\n ORDER BY measured_at, station_id\n "¶

dew_point: Mapped[Decimal]¶: dew point temperature in °C calculated using thermal_comfort.dew_point()

dew_point_max: Mapped[Decimal]¶: maximum of dew point temperature in °C calculated using thermal_comfort.dew_point()

dew_point_min: Mapped[Decimal]¶: minimum of dew point temperature in °C calculated using thermal_comfort.dew_point()

heat_index: Mapped[Decimal]¶: heat index in °C calculated using thermal_comfort.heat_index_extended()

heat_index_max: Mapped[Decimal]¶: maximum of heat index in °C calculated using thermal_comfort.heat_index_extended()

heat_index_min: Mapped[Decimal]¶: minimum of heat index in °C calculated using thermal_comfort.heat_index_extended()

measured_at: Mapped[datetime]¶: The exact time the value was measured in UTC

protocol_version: Mapped[int]¶: The protocol version the data was sent with

relative_humidity: Mapped[Decimal]¶: relative humidity in %

relative_humidity_max: Mapped[Decimal]¶: maximum of relative humidity in %

relative_humidity_min: Mapped[Decimal]¶: minimum of relative humidity in %

relative_humidity_raw: Mapped[Decimal]¶: raw relative humidity in % with no calibration applied

relative_humidity_raw_max: Mapped[Decimal]¶: maximum of raw relative humidity in % with no calibration applied

relative_humidity_raw_min: Mapped[Decimal]¶: minimum of raw relative humidity in % with no calibration applied

specific_humidity: Mapped[Decimal]¶: specific humidity in g/kg calculated using thermal_comfort.specific_humidity()

specific_humidity_max: Mapped[Decimal]¶: maximum of specific humidity in g/kg calculated using thermal_comfort.specific_humidity()

specific_humidity_min: Mapped[Decimal]¶: minimum of specific humidity in g/kg calculated using thermal_comfort.specific_humidity()

station: Mapped[Station]¶: The station the data was measured at

station_id: Mapped[str]¶: id of the station these measurements were taken at

wet_bulb_temperature: Mapped[Decimal]¶: wet bulb temperature in °C calculated using thermal_comfort.wet_bulb_temp()

wet_bulb_temperature_max: Mapped[Decimal]¶: maximum of wet bulb temperature in °C calculated using thermal_comfort.wet_bulb_temp()

wet_bulb_temperature_min: Mapped[Decimal]¶: minimum of wet bulb temperature in °C calculated using thermal_comfort.wet_bulb_temp()

class app.models.TempRHDataHourly(**kwargs)[source]¶

This is not an actual table, but a materialized view. We simply trick sqlalchemy into thinking this was a table. Querying a materialized view does not differ from querying a proper table.

absolute_humidity: Mapped[Decimal]¶: absolute humidity in g/m3 calculated using thermal_comfort.absolute_humidity()

absolute_humidity_max: Mapped[Decimal]¶: maximum of absolute humidity in g/m3 calculated using thermal_comfort.absolute_humidity()

absolute_humidity_min: Mapped[Decimal]¶: minimum of absolute humidity in g/m3 calculated using thermal_comfort.absolute_humidity()

air_temperature: Mapped[Decimal]¶: air temperature in °C

air_temperature_max: Mapped[Decimal]¶: maximum of air temperature in °C

air_temperature_min: Mapped[Decimal]¶: minimum of air temperature in °C

air_temperature_raw: Mapped[Decimal]¶: raw air temperature in °C with no calibration applied

air_temperature_raw_max: Mapped[Decimal]¶: maximum of raw air temperature in °C with no calibration applied

air_temperature_raw_min: Mapped[Decimal]¶: minimum of raw air temperature in °C with no calibration applied

battery_voltage: Mapped[Decimal]¶: The battery voltage of the sensor in Volts

battery_voltage_max: Mapped[Decimal]¶: maximum of The battery voltage of the sensor in Volts

battery_voltage_min: Mapped[Decimal]¶: minimum of The battery voltage of the sensor in Volts

creation_sql: str = " WITH data_bounds AS (\n SELECT\n station_id,\n MIN(measured_at) AS start_time,\n MAX(measured_at) AS end_time\n FROM temp_rh_data\n WHERE measured_at BETWEEN :window_start AND :window_end\n GROUP BY station_id\n ), filling_time_series AS (\n SELECT generate_series(\n DATE_TRUNC('hour', (\n SELECT MIN(measured_at) FROM temp_rh_data\n WHERE measured_at BETWEEN :window_start AND :window_end)\n ),\n DATE_TRUNC('hour', (\n SELECT MAX(measured_at) FROM temp_rh_data\n WHERE measured_at BETWEEN :window_start AND :window_end) + '1 hour'::INTERVAL\n ),\n '1 hour'::INTERVAL\n ) AS measured_at\n ),\n stations_subset AS (\n -- TODO: this could be faster if check the station table by station_type\n SELECT DISTINCT station_id FROM temp_rh_data\n ),\n time_station_combinations AS (\n SELECT\n measured_at,\n stations_subset.station_id,\n start_time,\n end_time\n FROM filling_time_series\n CROSS JOIN stations_subset\n JOIN data_bounds\n ON data_bounds.station_id = stations_subset.station_id\n WHERE filling_time_series.measured_at >= data_bounds.start_time\n AND filling_time_series.measured_at <= data_bounds.end_time\n ), all_data AS(\n (\n SELECT\n measured_at AS ma,\n station_id,\n NULL AS absolute_humidity,\n NULL AS air_temperature,\n NULL AS air_temperature_raw,\n NULL AS battery_voltage,\n NULL AS dew_point,\n NULL AS heat_index,\n NULL AS protocol_version,\n NULL AS relative_humidity,\n NULL AS relative_humidity_raw,\n NULL AS specific_humidity,\n NULL AS wet_bulb_temperature\n FROM time_station_combinations\n )\n UNION ALL\n (\n SELECT\n measured_at AS ma,\n station_id,\n absolute_humidity,\n air_temperature,\n air_temperature_raw,\n battery_voltage,\n dew_point,\n heat_index,\n protocol_version,\n relative_humidity,\n relative_humidity_raw,\n specific_humidity,\n wet_bulb_temperature\n FROM temp_rh_data\n WHERE measured_at BETWEEN :window_start AND :window_end\n )\n ) SELECT\n time_bucket('1 hour', ma) + '1 hour'::INTERVAL AS measured_at,\n station_id,\n avg(absolute_humidity) AS absolute_humidity,\n max(absolute_humidity) AS absolute_humidity_max,\n min(absolute_humidity) AS absolute_humidity_min,\n avg(air_temperature) AS air_temperature,\n max(air_temperature) AS air_temperature_max,\n min(air_temperature) AS air_temperature_min,\n avg(air_temperature_raw) AS air_temperature_raw,\n max(air_temperature_raw) AS air_temperature_raw_max,\n min(air_temperature_raw) AS air_temperature_raw_min,\n avg(battery_voltage) AS battery_voltage,\n max(battery_voltage) AS battery_voltage_max,\n min(battery_voltage) AS battery_voltage_min,\n avg(dew_point) AS dew_point,\n max(dew_point) AS dew_point_max,\n min(dew_point) AS dew_point_min,\n avg(heat_index) AS heat_index,\n max(heat_index) AS heat_index_max,\n min(heat_index) AS heat_index_min,\n mode() WITHIN GROUP (ORDER BY protocol_version ASC) AS protocol_version,\n avg(relative_humidity) AS relative_humidity,\n max(relative_humidity) AS relative_humidity_max,\n min(relative_humidity) AS relative_humidity_min,\n avg(relative_humidity_raw) AS relative_humidity_raw,\n max(relative_humidity_raw) AS relative_humidity_raw_max,\n min(relative_humidity_raw) AS relative_humidity_raw_min,\n avg(specific_humidity) AS specific_humidity,\n max(specific_humidity) AS specific_humidity_max,\n min(specific_humidity) AS specific_humidity_min,\n avg(wet_bulb_temperature) AS wet_bulb_temperature,\n max(wet_bulb_temperature) AS wet_bulb_temperature_max,\n min(wet_bulb_temperature) AS wet_bulb_temperature_min\n FROM all_data\n GROUP BY measured_at, station_id\n ORDER BY measured_at, station_id\n "¶

dew_point: Mapped[Decimal]¶: dew point temperature in °C calculated using thermal_comfort.dew_point()

dew_point_max: Mapped[Decimal]¶: maximum of dew point temperature in °C calculated using thermal_comfort.dew_point()

dew_point_min: Mapped[Decimal]¶: minimum of dew point temperature in °C calculated using thermal_comfort.dew_point()

heat_index: Mapped[Decimal]¶: heat index in °C calculated using thermal_comfort.heat_index_extended()

heat_index_max: Mapped[Decimal]¶: maximum of heat index in °C calculated using thermal_comfort.heat_index_extended()

heat_index_min: Mapped[Decimal]¶: minimum of heat index in °C calculated using thermal_comfort.heat_index_extended()

measured_at: Mapped[datetime]¶: The exact time the value was measured in UTC

protocol_version: Mapped[int]¶: The protocol version the data was sent with

relative_humidity: Mapped[Decimal]¶: relative humidity in %

relative_humidity_max: Mapped[Decimal]¶: maximum of relative humidity in %

relative_humidity_min: Mapped[Decimal]¶: minimum of relative humidity in %

relative_humidity_raw: Mapped[Decimal]¶: raw relative humidity in % with no calibration applied

relative_humidity_raw_max: Mapped[Decimal]¶: maximum of raw relative humidity in % with no calibration applied

relative_humidity_raw_min: Mapped[Decimal]¶: minimum of raw relative humidity in % with no calibration applied

specific_humidity: Mapped[Decimal]¶: specific humidity in g/kg calculated using thermal_comfort.specific_humidity()

specific_humidity_max: Mapped[Decimal]¶: maximum of specific humidity in g/kg calculated using thermal_comfort.specific_humidity()

specific_humidity_min: Mapped[Decimal]¶: minimum of specific humidity in g/kg calculated using thermal_comfort.specific_humidity()

station: Mapped[Station]¶: The station the data was measured at

station_id: Mapped[str]¶: id of the station these measurements were taken at

wet_bulb_temperature: Mapped[Decimal]¶: wet bulb temperature in °C calculated using thermal_comfort.wet_bulb_temp()

wet_bulb_temperature_max: Mapped[Decimal]¶: maximum of wet bulb temperature in °C calculated using thermal_comfort.wet_bulb_temp()

wet_bulb_temperature_min: Mapped[Decimal]¶: minimum of wet bulb temperature in °C calculated using thermal_comfort.wet_bulb_temp()

app.models.create_hypertable(target, connection, **kwargs) → None[source]¶

Create a timescaledb hypertable for the given table if it doesn’t exist.

Parameters:

target (Table) – The table to create a hypertable for
connection (Connection) – The database connection to use
kwargs (Any) – Additional keyword arguments (which are ignored)

Return type:

None

module models¶

module `models`¶