Database Manual

Oak Ridges Moraine Groundwater Program

Section 2.3.5

Section 2.3.5 Water Level Data

Water level measurements, considered field data, are stored in a single table D_INTERVAL_TEMPORAL_2 (along with all other field-type information).

Manual Measurements

Manually derived water level measurements are typically hand-written/recorded as opposed to being automatically recorded (through some sort of datalogger), and generally record the water level depth measured from a particular reference point (typically the top of a pipe when recorded for a well/borehole) and the date-time at which the measurement was taken. These are to be linked to the particular interval (i.e. a screen) that is tied to the location. Example water levels, shown below, have been converted from hard-copy field sheets to a row-centric (digital) file appropriate for importing into the database (similar to that described for importing of chemistry data, described in Section 2.3.4, where each row contains a single piece of information).

Figure Manual Water Levels - source data Figure Manual Water Levels - source data

Note that, in this case, each water level has already been identified with a location and the interval at that location (through manual_name and int_id). Prior to import, certain information is required:

This dataset (as shown below) can now be imported into the database.

Figure Manual Water Levels - import format Figure Manual Water Levels - import format

Once imported (and any conversion has been performed), the data will be stored in the database format shown below.

Figure Manual Water Levels - database records Figure Manual Water Levels - database records

A few points regarding these fields

Also, the DATA_ID field is populated with a randomly assigned integer (when importing the data through SiteFX; note that each row shown has the same DATA_ID value), linked to a new entry in the D_DATA_SOURCE table. This is created as a reference to any imported data set. It is strongly encouraged that the user modify the DATA_DESCRIPTION field found in this table to better reflect the imported information (as shown below, the DATA_DESCRIPTION was changed from the SiteFX default of User Import to CAMC-YPDT Manual WL, 20120928 which is more descriptive)

Figure Manual Water Levels - Data source information Figure Manual Water Levels - Data source information

Note also that the DATA_FILENAME references the original source file(s) - this information may be useful when tracking possible sources of error (at some time in the future).

Datalogger Water Level Measurements

Logger files come in a variety of formats dependent upon the manufacturer. Important features of different loggers (when importing their data files) include, for example, whether the logger is vented and the types of readings generated. In most cases information will consist of a date-time column (or columns if handled separately), a water depth reading (uncorrected if non-vented) and (perhaps) a temperature reading. If directly importing the datalogger files without any SiteFX logger corrections/adjustments then, before import, non-vented logger water levels need to be corrected for barometric pressure from a (preferably on-site) dedicated baro-logger. In addition, the depth below a reference point for these recorded water levels needs to be calculated using the recorded height of water above logger (determined by the logger itself).

Note that the SiteFX User Guide (EarthFX, 2012) has an extensive section on importing logger files into the database. Users are referred to this manual for any additional details not included here.

The example provided shows the readings from a vented logger, an Insitu Troll; in this case no barometric compensation is necessary. The table, below, includes a number of source fields, including (for each row of information):

Figure Logger Water Levels - source data Figure Logger Water Levels - source data

Of these, the WL_mbtp will be stored as the RD_VALUE_OUOM value in the database (this would have been calculated from the depth_m logger measurement where the depth of the logger itself, below the top of the pipe/casing, is known). Note that this information is already row-centric and does not need transforming.

The following database fields, mostly from the D_INTERVAL_TEMPORAL_2 table, are used to incorporate the imported logger data:

Note that, in this case, the original units of measure fields were used instead of the finalized value fields (e.g. RD_VALUE, UNIT_CODE, etc…). This allows for the raw data to be stored within the database and also provides another distinct advantage. Consider the case where calculated water levels in masl were determined in the above table (i.e. outside of the database) and then imported directly into the database. In this scenario, if the elevation of the top of pipe/casing (i.e. the reference elevation) were re-surveyed or found to be in error then the data would have to be removed from the database and re-imported to correctly have it updated (or a correction applied to the data within the database). By having the OUOM values in mbtp units, this allows SiteFX to automatically update the water levels in masl units should any changes in the reference elevation (i.e. REF_ELEV) occur. Upon import, or shortly thereafter, the RD_VALUE and the UNIT_CODE fields will be populated by SiteFX.

Figure Logger Water Levels - import format Figure Logger Water Levels - import format

The process is repeated for the logger temperature data. In this case the RD_TYPE_CODE remains the same but the values, units and original name are modified to reflect the temperature (rather than the water level) data (see table below).

Figure Logger Water Temperature - import format Figure Logger Water Temperature - import format

Upon import and final conversion, the final water level results are shown below. Note the following:

Figure Logger Water Levels - database records Figure Logger Water Levels - database records

The final temperature results upon import (no conversion necessary as all results are in degrees C) are shown below. The differences, as compared to the previous water level data, include:

Figure Logger Water Temperature - database records Figure Logger Water Temperature - database records

Water Level Related Reading Name and Reading Type Codes

With respect to water levels, the following Reading Name Descriptions (i.e. RD_NAME_DESCRIPTION, found in the R_RD_NAME_CODE table) are available for use when adding water level data into the database (the actual code, i.e. the RD_NAME_CODE, is listed in brackets):

Note that all of these have a READING_GROUP_CODE of 23 (i.e. Water Level). The following are disparaged (and are to be removed).

A variety of Reading Type Descriptions (i.e. RD_TYPE_DESCRIPTION, found in R_RD_TYPE_CODE) are available to provide additional information regarding each water level measurement in the database (whether by a logger or manual). These include, for example (the actual code, i.e. the RD_TYPE_CODE, is listed in brackets):

Refer to R_RD_TYPE_CODE for a complete listing of available descriptions.

Water Levels from WWIS (MOE)

Static water levels obtained from the MOEs WWIS would be found by querying for a RD_NAME_CODE of 628 and an RD_TYPE_CODE of 0. For water levels measured during pumping or recovery, a search using an RD_NAME_CODE of 70899 and an RD_TYPE_CODE of 64 or 65 (as appropriate) would yield the desired water level data.

Water Levels from Loggers

Water level data obtained from loggers generally involve large files with many thousands of water levels. Because of the large size it is important to prioritize the data that is imported into the database. In general, logger data (to be imported into the database) should be compensated for barometric pressure and should be correct in terms of the elevation of the logger below the reference elevation (this equates to an RD_NAME_CODE of 629, i.e. Water Level - Logger (Compensated & Corrected)). A READING_TYPE_CODE can also be specified, identifying the type or make of logger that was used to record the data.

If Water Level - Logger (Compensated & Corrected) data is available, there is no need to also import any other water-level logger data covering the same time period (e.g. the raw, uncompensated and/or uncorrected data). Note that the database includes some of these RD_NAME_CODES as, historically, some data of this nature was already incorporated in the database. These are now disparaged.

Water Levels from Municipal Pumping Wells (SCADA)

Water levels from municipal pumping wells (e.g. pumping wells from York, Durham, etc…) are generally collected using a SCADA logging system. Manual water levels (i.e. taken with a water level tape) will usually not be available or be rare. Instead, manual readings from the real-time output display of the SCADA system are read and recorded (once per day on average). These values will then be stored in the database with a RD_NAME_CODE of 70899 - the pumping status is usually not known when the reading was recorded so no RD_TYPE_CODE will be specified (if known, a pump-on or pump-off RD_TYPE_CODE will be added). Logger data will be stored with the usual 629 RD_NAME_CODE but will have a RD_TYPE_CODE of 85 (i.e. Logger - SCADA) noted.

Correction of Datalogger Water Level Measurements

Refer to Appendix G (Procedures; G.9) for details regarding the correction of water levels recorded by dataloggers.