View on GitHub

Oak Ridges Moraine Groundwater Program - Database Manual

ORMGP Database Manual

Section 2.3.2 Consultant Report Data

Borehole Records

From a standard borehole reporting sheet, a number of tables are affected when recording and transcribing information into the database. These have been grouped into the following sections:

Note that the example borehole sheet has been pulled from a geotechnical foundation report.

Figure 2.3.2.1 Borehole Record - example with sections indicated Figure 2.3.2.1 Borehole Record - example with sections indicated

A - General Information

Affected tables (and their populated fields; REF indicates a look-up table) include:

These tables are related through LOC_ID, a randomly assigned integer value, found in the D_LOCATION table (in the D_LOCATION table no duplicated LOC_ID’s would be found, i.e. it’s a primary key). The D_DOCUMENT_ASSOCATION table, different from the others, presupposes (in this case) that the document/report from which we are extracting the borehole information has already been added to the database (see Section 2.3.7). Here we are linking, explicitly, that this location/borehole has been extracted from a particular report (through the association of the LOC_ID with a DOC_ID, the latter found in the D_DOCUMENT table). Note that we’re preferentially making use of the depth values versus the elevation values - the reasons for this are described in Section 2.4.2.

Additionally, multiple look-up (i.e. R_*) tables are accessed for populating some of these fields (those marked with REF above):

B - Geologic Information

The primary table and fields for dealing with geologic information, and the look-up tables referenced, include:

Note that, in addition (though not used in this example), the D_GEOLOGY_FEATURES can be used for making specific observations (provided with a depth) about the units encountered.

There will be one recorded row per unit found within the borehole - in this case, there are six units total resulting in six rows of information to be added into the database. All geologic materials are converted from text descriptions to geologic codes as found in R_GEOL_MAT1_CODE through R_GEOL_MAT4_CODE. Each of these material tables contain equivalent information and are only present to remove some internal complexity in the database. Both the top and bottom of each unit are specified as well as the colour (if known).

C - Soil Intervals (Blow Count Data)

This type of information is actually related to a (field-collected) soil sample with a specific top and bottom. In some cases (two here) these samples are further analyzed in a laboratory for additional parameters that would be stored elsewhere (for example, grain size; see ‘F - Lab Analysis’ and Section 2.3.3). The primary table and fields for dealing with the soil sample and blow count values, as well as their associated look-up/reference tables include:

The D_INTERVAL table contains two keys - a LOC_ID and an INT_ID (this is also an randomly assigned integer serving as a primary key). All subsequent interval tables are related based upon this INT_ID (as found in, for this example, D_INTERVAL_SOIL).

D - Water Levels

Water levels are also tied to intervals, as was the case with the ‘Soil Intervals’ discussed above, but, they are associated with ‘screen’ intervals. The primary table and fields for dealing with water levels, as well as the look-up tables referenced, include:

Any information measured in the field is to be stored in the D_INTERVAL_TEMPORAL_2 table. In this case, if more water levels were taken at this location, such information would be added to the temporal table tied back to the same interval as tracked by the INT_ID.

E - Laboratory Analysis - Water Content

Information or data produced or determined in a laboratory is stored in the D_INTERVAL_TEMPORAL_1A/1B tables. As was the case for blow counts and water levels, the Water Content information is also tied back to an interval, in this case a “Soil” Interval (described in ‘C - Soil Intervals’, above). The primary table and fields for dealing with sample water content (not including the interval, as previously described), as well as the look-up tables referenced, include:

The INT_ID for the particular reading ties the D_INTERVAL and D_INTERVAL_TEMPORAL_1A tables together. At this point, though, a new identifier (SAM_ID) relates the two temporal tables (1A & 1B). In this way a particular sample (stored in 1A) can have a number of parameters (stored in 1B). Note that each of the different soil samples would have a water content value associated with it.

F - Laboratory Analysis - Grain Size

Again, as this information is a laboratory determined measurement, it is also held in the D_INTERVAL_TEMPORAL_1A/1B tables. This information is also tied back to an interval, in this case a ‘soil’ interval (described in ‘C - Soil Intervals’, above). Refer also to Section 2.3.3. The primary table and fields for dealing with sample grain size distribution (not including the interval, as previously described), as well as the look-up tables referenced, include:

The grain size analysis only applies to the sixth and seventh soil sample. Within the D_INTERVAL_TEMPORAL_1B table, each sample would have four rows associated with it based on the clay, silt, sand and gravel breakdown (eight rows in total). These would be converted (in this case, through a straight copy procedure, from the OUOM fields to the actual ‘value’ fields during a SiteFX conversion of ‘new’ information). Also note that if the moisture content and the grain size analysis were undertaken on the same sample at the same laboratory, then they could possibly share a sample identifier (i.e. SAM_ID) in the D_INTERVAL_TEMPORAL_1A table. Since no lab sheets were provided in this report, the data would be split into two different samples.