This view does not currently exist. But it might someday!
This view unifies the temperature minima and maxima from both the manually and electronically collected weather data into a single place. For the manually collected data, this view contains one row for every WREADINGS row with a curated minimum and/or maximum temperature in TEMPMINS and TEMPMAXS. The electronically collected data in DIGITAL_WEATHER are first aggregated into 24-hour periods beginning and ending at 06:00 AM each day, in which the lowest curated" AirTemp_Min and highest curated AirTemp_Max are retained. This view contains one row for each of these 24-hour periods.
Ideally, none of the rows in this view should overlap in time but this is not explicitly forbidden. The system will return a warning when this occurs.
Tip
Data managers can prevent overlaps in time from happening by using the various curated columns to prune overlapping rows.
Figure 6.164. Query Defining the CURATED_TEMPS View
WITH manual AS (SELECT wreadings.wstation
, (SELECT last.wrdaytime
FROM wreadings AS last
WHERE last.wstation = wreadings.wstation
AND last.wrdaytime < wreadings.wrdaytime
AND (EXISTS(SELECT 1 FROM tempmins tm WHERE tm.wrid = wreadings.wrid)
OR
EXISTS(SELECT 1 FROM tempmaxs tm WHERE tm.wrid = wreadings.wrid))
ORDER BY last.wrdaytime DESC
LIMIT 1) AS start
, wreadings.wrdaytime AS stop
, tempmins.tempmin
, tempmaxs.tempmax
FROM wreadings
LEFT JOIN tempmins
ON tempmins.wrid = wreadings.wrid
AND tempmins.curated
LEFT JOIN tempmaxs
ON tempmaxs.wrid = wreadings.wrid
AND tempmaxs.curated
WHERE COALESCE(tempmins.tempmin, tempmaxs.tempmax) IS NOT NULL)
, hourly AS (SELECT digital_weather.wstation
, date_trunc('DAY', timestamp - '7 hours'::interval) AS effective_date
, digital_weather.timestamp
, CASE
WHEN digital_weather.airtemp_min_curated
THEN digital_weather.airtemp_min
ELSE NULL
END AS airtemp_min
, CASE
WHEN digital_weather.airtemp_max_curated
THEN digital_weather.airtemp_max
ELSE NULL
END AS airtemp_max
FROM digital_weather
WHERE digital_weather.airtemp_min_curated
OR digital_weather.airtemp_max_curated
)
, digital AS (SELECT hourly.wstation
, (MIN(hourly.timestamp) - '1 hour'::interval) AS start
, MAX(hourly.timestamp) AS stop
, MIN(hourly.airtemp_min) AS tempmin
, MAX(hourly.airtemp_max) AS tempmax
FROM hourly
GROUP BY hourly.wstation, hourly.effective_date
)
, temps AS (SELECT * FROM manual
UNION
SELECT * FROM digital)
SELECT wstation AS wstation
, start AS start
, stop AS stop
, tempmin AS tempmin
, tempmax AS tempmax
, to_hhmmss(stop - start) AS timespan
, to_hhmmss(start - (LAG(stop) OVER (ORDER BY stop))) AS time_missed
FROM temps;
Figure 6.165. Entity Relationship Diagram of the portion of the CURATED_TEMPS View involving electronically-collected weather data
|
Figure 6.166. Entity Relationship Diagram of the portion of the CURATED_TEMPS View involving manually-collected weather data
|
Table 6.79. Columns in the CURATED_TEMPS View
| Column | From | Description |
|---|---|---|
| WStation | WREADINGS.Wstation or DIGITAL_WEATHER.WStation | The weather station from which this row's data were collected. |
| Start | WREADINGS.WRdaytime or DIGITAL_WEATHER.TimeStamp | The WRdaytime of
the previous manually collected weather reading
having any curated
temperatures (or NULL, if there is no such reading),
or the earliest (TimeStamp - 1
hour) of all the DIGITAL_WEATHER rows that were
aggregated into the row. |
| Stop | WREADINGS.WRdaytime or DIGITAL_WEATHER.TimeStamp | The WRdaytime of the manually-collected weather reading, or the latest TimeStamp of all the DIGITAL_WEATHER rows that were aggregated into the row. |
| Tempmin | TEMPMINS.Tempmin or DIGITAL_WEATHER.AirTemp_Min | The curated Tempmin (if any) for the manual reading, or the lowest curated AirTemp_Min (if any) of all the DIGITAL_WEATHER rows that were aggregated into the row. |
| Tempmax | TEMPMAXS.Tempmax or DIGITAL_WEATHER.AirTemp_Max | The curated Tempmax (if any) for the manual reading, or the highest curated AirTemp_Max (if any) of all the DIGITAL_WEATHER rows that were aggregated into the row. |
| Timespan | WREADINGS.WRdaytime or DIGITAL_WEATHER.TimeStamp | The time elapsed between this row's Start and Stop, as HH:MM:SS. |
| Time_Missed | WREADINGS.WRdaytime or DIGITAL_WEATHER.TimeStamp | The time elapsed between the chronologically previous row's Stop and this row's Start, as HH:MM:SS. |
Contains one row for every row in WREADINGS. Each row contains the WREADINGS data and the related TEMPMINS, TEMPMAXS, and RAINGAUGES rows. In those cases where there is a
WREADINGS row but not a row from a related
table the columns from the related table are NULL.
This view is useful for the analysis of the manually collected weather data.[292]
Warning
The UNadjusted maximum temperature is not shown in this view. That is, when the original maximum temperature was determined to be spurious and has been adjusted in some way, this view does not provide a way to identify which Tempmax values are and are not adjusted. When this information is important to retain, users should use the TEMPMAXS table.
For more information, see TEMPMAXS and its Historical Note.
Caution
The WRdaytime column indicates when the reading was
recorded, not when the recorded weather
events (e.g. Tempmax, Rain) occurred. The weather data shown
in each row are the weather conditions since the previous
reading — usually covering the day before the WRdaytime.
For example, a Tempmax value in a row with WRdaytime =
2015-07-04 06:00 will usually reflect the
high temperature for 2015-07-03; however, if the previous
WRdaytime was more than 24 hours before the current one, you
will not be able to determine which particular date
experienced that high temperature. The only thing you know is
that that temperature occurred sometime between the current
WRdaytime and the previous one.
It is important to think of each row as the weather conditions since the last reading, not yesterday's weather. Occasionally, one or more days are skipped and thus a single row may represent weather conditions spanning multiple days. Always interpret the values as covering the interval between this reading and the last, not the calendar day shown in WRdaytime.
Tip
If you need a table that converts the WRdaytimes into estimates of rainfall on a daily basis, ask a data manager for help.
Figure 6.167. Query Defining the MIN_MAXS View
SELECT wreadings.wrid AS wrid
, wreadings.wstation AS wstation
, wreadings.wrdaytime AS wrdaytime
, wreadings.estdaytime AS estdaytime
, wreadings.wrperson AS wrperson
, wreadings.wrnotes AS wrnotes
, tempmins.tempmin AS tempmin
, tempmins.curated AS tempmin_curated
, tempmaxs.tempmax AS tempmax
, tempmaxs.curated AS tempmax_curated
, raingauges.rgspan AS rgspan
, raingauges.estrgspan AS estrgspan
, raingauges.rain AS rain
FROM wreadings
LEFT OUTER JOIN tempmins
ON wreadings.wrid = tempmins.wrid
LEFT OUTER JOIN tempmaxs
ON wreadings.wrid = tempmaxs.wrid
LEFT OUTER JOIN raingauges
ON wreadings.wrid = raingauges.wrid;
Table 6.80. Columns in the MIN_MAXS View
| Column | From | Description |
|---|---|---|
| WRid | WREADINGS.WRid | Identifier of the manual weather reading. |
| Wstation | WREADINGS.Wstation | Identifier of the weather station where the reading was taken. |
| WRdaytime | WREADINGS.WRdaytime | Date and time of the weather reading. |
| Estdaytime | WREADINGS.Estdaytime | Whether the WREADINGS.WRdaytime is estimated.
TRUE if the date/time is estimated, FALSE if the
reading was taken at a known date and time. |
| WRperson | WREADINGS.WRperson | The OBSERVERS.Initials of the person who took the reading. |
| WRnotes | WREADINGS.WRnotes | Textual notes on the weather reading. |
| Tempmin | TEMPMINS.Tempmin | The minimum temperature reading, if any, since the last minimum temperature reading at the weather station. |
| Tempmin_Curated | TEMPMINS.Curated | Whether or not this minimum temperature has been curated. |
| Tempmax | TEMPMAXS.Tempmax | The maximum temperature reading, if any, since the last maximum temperature reading at the weather station. |
| Tempmax_Curated | TEMPMAXS.Curated | Whether or not this maximum temperature has been curated. |
| RGspan | RAINGAUGES.RGspan | The time elapsed since the rain gauge was last emptied. |
| EstRGspan | RAINGAUGES.EstRGspan | Weather or not the time elapsed since the rain
gauge was last emptied is an estimate. TRUE when
the elapsed time is based on one or more estimated
times, FALSE when the elapsed time is computed from
known endpoints. |
| Rain | RAINGAUGES.Rain | The amount of rain accumulation in millimeters. |
Inserting a row into MIN_MAXS inserts a row into
WREADINGS and rows into TEMPMINS, TEMPMAXS, and
RAINGAUGES as expected. Rows are only
inserted into TEMPMINS, TEMPMAXS, and RAINGAUGES
when the relevant columns are present and contain
non-NULL values.
Warning
Attempts to specify the WRid column on insert are
silently ignored. When inserting a new weather reading
the WRid column should be unspecified (the column
omitted or the data values specified as NULL). Babase
automatically computes a WRid and uses it appropriately in
the new rows.
Warning
The value of the RGspan and EstRGspan columns are ignored and
automatically computed values are used in their place.
It is best to omit these columns from the inserted data
(or specify them as NULL).
Warning
The PostgreSQL nextval()
function cannot be part of an INSERT
expression which assigns a value to this view's Wrid
column.
Deleting a row from MIN_MAXS deletes a row from WREADINGS and rows from TEMPMINS, TEMPMAXS, and RAINGAUGES as expected.
Contains one row for every row in WREADINGS. This view is the MIN_MAXS view sorted for ease of maintenance.
This view is less efficient than MIN_MAXS like view.
Figure 6.169. Query Defining the MIN_MAXS_SORTED View
SELECT wreadings.wrid AS wrid
, wreadings.wstation AS wstation
, wreadings.wrdaytime AS wrdaytime
, wreadings.estdaytime AS estdaytime
, wreadings.wrperson AS wrperson
, wreadings.wrnotes AS wrnotes
, tempmins.tempmin AS tempmin
, tempmins.curated AS tempmin_curated
, tempmaxs.tempmax AS tempmax
, tempmaxs.curated AS tempmax_curated
, raingauges.rgspan AS rgspan
, raingauges.estrgspan AS estrgspan
, raingauges.rain AS rain
FROM wreadings
LEFT OUTER JOIN tempmins
ON wreadings.wrid = tempmins.wrid
LEFT OUTER JOIN tempmaxs
ON wreadings.wrid = tempmaxs.wrid
LEFT OUTER JOIN raingauges
ON wreadings.wrid = raingauges.wrid
ORDER BY wreadings.wrdaytime, wreadings.wstation;;
Table 6.81. Columns in the MIN_MAXS_SORTED View
| Column | From | Description |
|---|---|---|
| WRid | WREADINGS.WRid | Identifier of the manual weather reading. |
| Wstation | WREADINGS.Wstation | Identifier of the weather station where the reading was taken. |
| WRdaytime | WREADINGS.WRdaytime | Date and time of the weather reading. |
| Estdaytime | WREADINGS.Estdaytime | Whether the WREADINGS.WRdaytime is estimated.
TRUE if the date/time is estimated, FALSE if the
reading was taken at a known date and time. |
| WRperson | WREADINGS.WRperson | The OBSERVERS.Initials of the person who took the reading. |
| WRnotes | WREADINGS.WRnotes | Textual notes on the weather reading. |
| Tempmin | TEMPMINS.Tempmin | The minimum temperature reading, if any, since the last minimum temperature reading at the weather station. |
| Tempmin_Curated | TEMPMINS.Curated | Whether or not this minimum temperature has been curated. |
| Tempmax | TEMPMAXS.Tempmax | The maximum temperature reading, if any, since the last maximum temperature reading at the weather station. |
| Tempmax_Curated | TEMPMAXS.Curated | Whether or not this maximum temperature has been curated. |
| RGspan | RAINGAUGES.RGspan | The time elapsed since the rain gauge was last emptied. |
| EstRGspan | RAINGAUGES.EstRGspan | Weather or not the time elapsed since the rain
gauge was last emptied is an estimate. TRUE when
the elapsed time is based on one or more estimated
times, FALSE when the elapsed time is computed from
known endpoints. |
| Rain | RAINGAUGES.Rain | The amount of rain accumulation in millimeters. |
The operations allowed are as described in the MIN_MAXS view.
[292] Because the MIN_MAXS view always returns a row regardless of whether data exists in TEMPMINS, TEMPMAXS, and RAINGAUGES the view may sometimes be less useful than, say, a query which returns only those rows where there is both a minimum and a maximum temperature reading. In other words, as usual, it's always prudent to know what you're doing when querying Babase.