Cover and Table of Contents

Table of Contents

Forward

Section 1 - Introduction

• 1.1 Overview
• 1.2 Background
• 1.3 Database Versions

Section 2 - Understanding the ORMGP Database

• 2.1 Tables and Views

  • 2.1.1 Data Tables (D_*)
  • 2.1.2 Reference Tables (R_*)
  • 2.1.3 Other Tables
  • 2.1.4 Views Outline
  • 2.1.5 Main Views (V_*)
  • 2.1.6 System Views (V_SYS_*)

• 2.2 General Overview of Relationships

• 2.3 Primary Data Relationships

  • 2.3.1 MOE Records
  • 2.3.2 Consultant Report Data
  • 2.3.3 Grain Size Data
  • 2.3.4 Chemistry Data
  • 2.3.5 Water Level Data
  • 2.3.6 Climate Station Data
  • 2.3.7 Report Library Information
  • 2.3.8 Permit To Take Water (PTTW)
  • 2.3.9 HYDAT and Spotflow Data

• 2.4 Secondary (or Derived) Data Relationships)

  • 2.4.1 Formation Assignment
  • 2.4.2 Elevations
  • 2.4.3 Geologic Picks
  • 2.4.4 Baseflow Estimation
  • 2.4.5 Specific Capacity, Transmissivity and Hydraulic Conductivity

• 2.5 Locations, Groups and Study Areas

• 2.6 Report Library

  • 2.6.1 Document Association
  • 2.6.2 Directory and Files, Naming and Structure
  • 2.6.3 Report Entry

• 2.7 Multi-Screen Installations

• 2.8 Multi-Borehole Records

Section 3 - Practical Applications

• 3.1 Working with the Database

  • 3.1.1 General Database Access
  • 3.1.2 Microsoft Access
  • 3.1.3 Microsoft SQL Management Studio
  • 3.1.4 Microsoft Excel
  • 3.1.5 SiteFX
  • 3.1.6 Viewlog

• 3.2 Training Exercises

• 3.3 Guidelines and FAQ

  • 3.3.1 Naming Conventions
  • 3.3.2 Quality Assurance (QA) and Quality Control (QC)
  • 3.3.3 Data Source Tracking
  • 3.3.4 Frequently Asked Questions (FAQ)

• 3.4 Adding New Data

  • 3.4.1 Locations - Mandatory Fields
  • 3.4.2 Geologic Information
  • 3.4.3 Hydraulic Properties
  • 3.4.4 Geophysical Logging
  • 3.4.5 Temporal Data
  • 3.4.6 Data Validation and Conversion
  • 3.4.7 Adding New Data - Methods
  • 3.4.8 Data Selection

• 3.5 Changes to Existing Data

Section 4 - Technical

• 4.1 Detailed Overview of Relationships

• 4.2 Table Details (D_*, R_* and Other)

• 4.3 View Details (V_*)

• 4.4 Database Distribution

• 4.5 Background Processing and Updates

References

Appendices

• A - Basic Outline and Use of Structured Query Language (SQL)

• B - Soil Classification Systems and Translation of Geologic Layers

• C - Baseflow Estimation

• D - External Data Sources

  • D.1 Datasets Summary Page - Locations
  • D.2 Specific Sources of Datasets - Locations
  • D.3 Datasets Summary Page - Chemistry Records
  • D.4 Datasets Summary Page - Climate Records
  • D.5 Datasets Summary Page - Field Records
  • D.6 Datasets Summary Page - Surface Water Records
  • D.7 Datasets Summary Page - Pick Records

• E - YPDT-CAMC/ORMGP Database Timeline

• F - Accessory ORMGP Databases

• G - Procedures

  • G.1 Formation Assignment and Associated Calculations (Automated)
  • G.2 Update of D_LOCATION_GEOM (Automated)
  • G.3 Report Library - Addition
  • G.4 Ground Elevation Assignment
  • G.5 Correction of Bedrock Wells
  • G.6 Addition to/Population of D_LOCATION_AGENCY (REPLACED)
  • G.7 Update of Bedrock Elevation (Automated)
  • G.8 Assignment of MOE Elevations as Original Elevations
  • G.9 Correction of Datalogger Information (DESCRIPTION NEEDED)
  • G.10 Import of MOE Water Well Database
  • G.11 Correction of D_GEOLOGY_LAYER - Missing Depths and Units
  • G.12 Creation of the TRAINING database (REPLACED)
  • G.13 Synchronizing non-replicating databases (REPLACED)
  • G.14 Population of coordinates (REVIEW)
  • G.15 Synchronize elevations between D_BOREHOLE and D_LOCATION_ELEV (REVIEW)
  • G.16 Check D_INTERVAL_FORMATION_ASSIGNMENT for Invalid (Null) Rows
  • G.17 Correction of elevations (D_BOREHOLE and D_LOCATION_ELEV) (REVIEW)
  • G.18 Extracting LOC_IDs for the Training database
  • G.19 Addition of INT_ID to D_INTERVAL_FORMATION_ASSIGNMENT (REPLACED)
  • G.20 Calculate and Incorporate Specific Capacity (REPLACED)
  • G.21 Perform QA/QC Check Against OAK_20120615_MASTER Backup (REPLACED)
  • G.22 Incorporation of the MOE Permit-To-Take-Water database (REVIEW)
  • G.23 Population of D_INTERVAL_MONITOR (Top and Bottom)
  • G.24 Update D_INTERVAL_MONITOR Depths (REPLACED)
  • G.25 Correction of Water Levels and Associated Data (REVIEW)
  • G.26 Correction or Update of Borehole Coordinates (MOE WWDB)
  • G.27 York Database - Incorporation of Temporal Data
  • G.28 Updating Elevations in D_* tables
  • G.29 Update MOE BORE_HOLE_ID (D_LOCATION_ALIAS)
  • G.30 Update Locations from MOE WWDB
  • G.31 Incorporate D_LOCATION_COORD_HIST and D_LOCATION_ELEV_HIST Records in D_LOCATION_SPATIAL_HIST
  • G.32 Automated Scripts (Listing and Calling Order)
  • G.33 Update of D_AREA_GEOM
  • G.34 Incorporation of Bedrock Elevations as Bedrock Picks
  • G.35 Handling Temporal Data Duplicates

• H - Current Problems (to be Corrected)

• I - Training Setup and Access

  • I.1 Citrix XenDesktop
  • I.2 Geocortex

• J - Training Exercises

  • J.1 Training Exercises - Easy
  • J.2 Training Exercises - Moderate
  • J.3 Training Exercises - Difficult

• K - Alternate Software Instructions

• L - Database Reports

• M - ORMGP Analyses, Metadata and Other Links

Last modified: 2022-10-18