View on GitHub

Oak Ridges Moraine Groundwater Program - Database Manual

ORMGP Database Manual

Section 2.3.4 Chemistry Data (Example)

In general, because of simplicity and so as to ensure consistency, it is presumed here that all chemistry information will be imported into the database using the SiteFX import routines (refer to Section 3.2.2). However, there should be enough detailed descriptions provided with the data to enable users to apply other methods of data import.

Historical Data (Spreadsheet Format - column centric)

Historical data, in this case, applies both to hard-copy reports and spreadsheets (both usually in some tabular format). The following example dataset shows information in a common spreadsheet-type form where each column contains information on a particular laboratory-analyzed parameter for multiple rows of samples - many older (i.e. historical) datasets are commonly found in this form. This format is built around a table-type visualization familiar to users for comparison between samples and is referred to here as column-centric. As can be seen in the example table, the results for one sample are spread out over three sheets with multiple parameters per row. Data received in this column-centric format cannot be used directly to store information within the ORMGP database. The ORMGP database uses a row-centric table format where all information for a particular parameter and a parameter is stored as a single row. In the ORMGP database, multiple rows are necessary to store additional parameters for the same sample.

To incorporate chemistry data into the database the following information is required for each sample (and its corresponding parameter values):

Figure Chemistry Data - spreadsheet format Figure Chemistry Data - spreadsheet format

The above data has been re-formatted and is presented below in a (row-centric) form that can now be used as a direct import into the database. As an example, the description of the method by which the first sample is captured is described (following the screen-capture):

Figure Chemistry Data - import format Figure Chemistry Data - import format

Once imported into the database, the chemistry data is stored in two tables, D_INTERVAL_TEMPORAL_1A and D_INTERVAL_TEMPORAL_1B. The former contains information concerning the sample (including the sample name, sample date, etc …) while the latter contains the actual parameter information (including, most importantly ,the parameter name, value and units). The tables are tied together using a random SAM_ID created upon data import.

Upon import, the following tables/fields are populated:

Figure Chemistry Data - sample record Figure Chemistry Data - sample record

The SAM_ANALYSIS_DATE, SAM_LAB_SAMPLE_ID, SAM_LAB_JOB_NUMBER and SAM_TYPE_CODE (or SAM_SAMPLE_NAME_OUOM) are optional fields that may also be useful to include as part of the import file.

The RD_TYPE_CODE, RD_MDL_OUOM, RD_RDL_OUOM and RD_UNCERTAINTY_OUOM are optional fields that may also be useful to include as part of the import file.

Figure Chemistry Data - parameter records Figure Chemistry Data - parameter records

In general, for each sample D_INTERVAL_TEMPORAL_1B will generally have multiple records/rows, one for each parameter analyzed (as shown in the above table). D_INTERVAL_TEMPORAL_1A, alternately, should only have a single record/row (for that sample).

Current Laboratory File (Database Format - row centric)

Digital/electronic files submitted directly from a laboratory tend to be in a format directly accessible to import into the ORMGP database (being row-centric; examine the sample import file created in the Historical Data section, above, for details). Prior to import, some details still need to be found and added to these files (e.g. assign the INT_ID the sample is associated with, check the names used for the parameters analyzed against the name codes - or aliases - available in the database, etc…).