View on GitHub

Oak Ridges Moraine Groundwater Program - Database Manual

ORMGP Database Manual

Section 2.3.6 Climate Station Data

Climate station data, considered field data, are stored in a single table - D_INTERVAL_TEMPORAL_2 (along with all other field -type information). Climate stations, besides having an assigned location identifier (as found in D_LOCATION; they are also referenced in D_CLIMATE - refer to Section 2.1 for details), are considered to consist of a single interval against which temporal data can be tagged (or linked). As such, each climate station also has an interval identifier (INT_ID; as found in D_INTERVAL). This INT_ID allows the user to insert or access information from the D_INTERVAL_TEMPORAL_2 table.

The example given is for the Toronto Lester B. Pearson International Airport climate station, daily averages, and is the standard format for climate information from Environment Canada. A single month is shown (January 1-31) for 1971; the data is split across two windows.

Figure 2.3.6.1 Example Climate Station - source data Figure 2.3.6.1 Example Climate Station - source data

Note that the format is column-centric as is standard for spreadsheet distributed information. This information needs to be transformed to a row-centric format required for database data entry. Each row of input information will consist of a single parameter (e.g. Mean Temp) tagged with the interval identifier for the Toronto Lester B. Pearson … location and include the date and the parameter units. Thus for each day of the dataset, there will one row for each parameter. Details are provided as follows:

The example input format is shown below

Figure 2.3.6.2 Example Climate Station - data input format Figure 2.3.6.2 Example Climate Station - data input format

Upon import of the information, into the database, a conversion routine is run against the new information (currently through SiteFX) which populates the other fields, as appropriate, in the D_INTERVAL_TEMPORAL_2 table

Figure 2.3.6.3 Example Climate Station - imported data Figure 2.3.6.3 Example Climate Station - imported data

Note that the primary data fields (i.e. RD_NAME_CODE, RD_VALUE and UNIT_CODE) have now been populated with their converted values from the original _OUOM fields (as found in the R_UNIT_CODE and R_UNIT_CONV tables). In this case, though, no conversion of the RD_VALUE_OUOM field was required as they original units of measure were already system units - as such, the values are only copied. In addition, the DATA_ID field has been populated with an integer value linking each row with information concerning (for example) the date it was included, the source of the data and by whom it was input - this information is found in the D_DATA_SOURCE table.