View on GitHub

Oak Ridges Moraine Groundwater Program - Database Manual

ORMGP Database Manual

Section 2.3.7 Report Library Information (Example)

As of version 20100915, documents in the ORMGP database are now stored as locations (LOC_TYPE_CODE of 25 - Documents) and can be treated/accessed just as any other location type. As such, document information is stored in both the D_LOCATION and D_DOCUMENT tables. Using DOC_ID 154 as an example (entitled ‘30M13-118 Highway 407 and Highway 50 Underpass’), information extracted from the document is found across a number of pages.

Figure 2.3.7.1 Example document - cover pageFigure 2.3.7.1 Example document - cover page

A new location would be created in D_LOCATION and the following tables populated

In addition, using a combination of the intersection name provided (i.e. Hwy 407 and Hwy 50 Underpass) as well as the scanned location map (below) the coordinates of the location can be determined.

Figure 2.3.7.2 Example document - study area Figure 2.3.7.2 Example document - study area

Note that these coordinates are approximate, only, and are generally associated with the center point of the area described by the report. This is also indicated in the D_LOCATION_QA table

Note that a value of 5 or less (commonly specified as ‘<6’) is the default worst-case cut-off value used when examining locations - any value greater than six is not used. The choice of a ‘5’ value may then indicate that this location should be included in these queries.

Information for populating the D_DOCUMENT table include

In addition, the report contains data that could be loaded into the database. A tag is used to both indicate its presence and whether this has been done.

Figure 2.3.7.3 Example document - location map Figure 2.3.7.3 Example document - location map

Figure 2.3.7.4 Example document - cross section Figure 2.3.7.4 Example document - cross section

Figure 2.3.7.5 Example document - borehole log Figure 2.3.7.5 Example document - borehole log

Note that in most reports, only that ‘YN’ and ‘ENTERED’ information pertinent to the particular report would be included and given a value of ‘0’ or ‘-1’ (i.e. false or true, respectively), the remainder would have a NULL value (i.e. no-value associated with the field).

Finally, a series of keyword fields (DOC_KEYWORD through DOC_KEYWORD10) can be used to add further information. These are also free-form text fields.

While under review, it was noted that this report contains grain size analysis as well as various Atterberg Limit Tests that would be done in a lab. As such, the DOC_YN_CHEMISTRY field should be updated (to a ‘-1’ or true value) to reflect this (refer to Section 2.3.3 concerning the use of the D_INTERVAL_TEMPORAL_1A/1B tables).

Figure 2.3.7.6 Example document - grainsize analysis Figure 2.3.7.6 Example document - grainsize analysis