This view does not exist. But it might someday!
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.[294]
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.164. 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.79. 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.Canonical | 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.Canonical | 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.166. 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.80. 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.Canonical | 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.Canonical | 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.
[294] 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.