Database Manual

Oak Ridges Moraine Groundwater Program

Appendix E

Appendix E - YPDT-CAMC (ORMGP) Database Timeline

Database Release 1 (First Released 2003)

20040629 (‘YPDT Database Meeting - June 29, 2004’ Memo)

Comments

July Database Release

Water Level Discussion

Water Level Structure

Database Release 2 (First Released 2004)

20050117 (‘YPDT Database Meeting’ Memo)

Comments

20050322 (‘YPDT Database Meeting’ memo)

Comments

20060303 through 20060426 (‘New Database Notes’ Memo)

Comments

20060627 (‘Database Meeting - June 27, 2006’ Memo)

Comments

Database Release 3 (First Released 2006, July)

20060830 (‘Database Meeting Notes’ Memo)

Comments upon ‘2006 Database Release’

20060830 (‘Database Release Notes’ Memo)

Comments

20061106 (‘Database Meeting - November 6, 2006’ Memo)

Comments

20061129 (‘2006 Database Synchronization - Summary of Activities’ Memo)

The 2006 database synchronization involved several key additions and changes to the database. Broad outlines of the changes made to the database between the 2004 and 2006 versions include

In addition, LOC_QA codes (for location and elevation) were reviewed; in general, the following applies:

20061222+

Incorporation of additional MOE boreholes/locations, primarily from the Crowe Region CA (refer to Appendix G for details).

20080110 (‘Notes For New 200[8] Database Release’ Memo)

The following should be examined prior to the next release of the database (in 2008)

Database Release 4 (Unreleased)

20080717 (‘Management of Temporal Data’ Memo)

The following direction or additions to the YPDT-CAMC database should be implemented

20081202 (‘Agenda Items for Upcoming Database Meeting’ Memo)

The 2006 database synchronization involved several key additions and changes to the database. The following broadly outlines the changes that were made to the database between the 2004 and the 2006 versions. (See also the information under the date ‘20061129’, above).

Comments

Comments Regarding Elevations

There is a concern that we haven’t been keeping a good track on how the elevation is assigned to a well and how it might change over time as the database is updated and changed. The problem is of concern from two different perspectives:

The current three Elevation Fields in the D_BOREHOLE Table must be used more rigorously and effectively. Depending on capabilities of selecting the ‘Best’ Elevation on the fly we might add another field called BH_BEST_GND_ELEV (or to keep the ‘Best’ as BH_GND_ELEV).

For MOE Wells:

For other wells:

For consistency I would like these three ELEV fields to be named in a consistent fashion

If we keep the ‘BEST’ field - then it could become BH_GND_ELEV (this then becomes the best elevation to use. This is what Sitefx uses to calculate geology, screens, etc)

All reference to elevations (and coordinates) should be removed from the surface water and climate tables.

The fields QA_ELEV_SOURCE, QA_ELEV_METHOD and QA_ELEV_COMMENT need to be reviewed and corrected.

Elevation Reliability Coding - ELEV_OUOM

Currently the MOE elevation reliability code has been overwritten in most cases and is lost. We need to go back and repopulate the MOE reliability code into the D_LOCATION_QA table. We should rename the field from QA_ELEV_CONFIDENCE_CODE to QA_ELEV_ORIG_CONFIDENCE_CODE - this field should then be repopulated with the original MOE value. In conjunction with this, the R_QA_ELEV_CONFIDENCE_CODE table should be re-named to R_QA_ELEV_ORIG_CONFIDENCE_CODE and expanded to account for the ability now for folks to estimate “original” elevations off of digital maps etc.

Given a an elevation confidence code of ‘0’, the following could be added

We can add whatever else we come up against - the main thing is that this field addresses the accuracy of the ORIGINAL Elevation estimate - so [it contains] MOE plus extra stuff. Although typically we do not need to make “original” elevation estimates anymore since the DEM populated elevation would be the general default.

Elevation Reliability Coding - SURF_ELEV

Given that we are now going to have the Second elevation field reflect the Surveyed Elevations (BH_GND_ELEV_SURV) we must have another field in the D_LOCATION_QA table called - QA_ELEV_SURV_CONFIDENCE_CODE - this will link to a new R_ table which we can call R_QA_ELEV_SURV_CONFIDENCE_CODE

The codes would then be

This could be generalized or specific but this table would only relate to the Surveyed Elevations. This table will capture much of what is currently found in two fields in the D_LOCATION_QA Table, namely the QA_COORD_SOURCE and the QA_COORD_METHOD fields. Both of these fields are largely tied to various consultant surveys that we know about - so they would vanish from the D_LOCATION_QA Table.

Elevation Reliability Coding - DEM_ELEV

We also will do the same thing with the BH_GND_ELEV_DEM field. There will be a new field in the D_LOCATION_QA Table that will be called QA_ELEV_DEM_CONFIDENCE_CODE. This field will link to a new R table which we can call R_QA_ELEV_DEM_CONFIDENCE_CODE.

The codes would then look like (each time the elevations are replaced by a new DEM, a new code would be introduced)

Comments Regarding Geology

The geological descriptions (including both formation and comments fields in various tables) in the database have become confused and the purpose of some of the existing tables remains unclear. One issue we should resolve is the issue of Mat1/2/3/4 codes - we had decided early on to stick with the MOE’s protocol of simple geological coding (Mat 1; Mat 2: Mat 3 - adding Mat 4). More complicated geological descriptions were to be entered into the “Full Description” memo field. The City of Toronto wells were granted an exception since they were imported in from a database - but these should be fixed now. Another problem is that similar information is being entered into different fields.

Proposed

Comments Regarding Screens

In D_FORMATION_SCREEN_ASSIGNMENT, do we want to keep the screen assignment (GEOL_UNIT_CODE) within this table or move to an interval table. If moved, then this becomes the D_FORMATION_TOP table only holding the geology from the gridded surfaces. Fields should be added to reflect surface version. Fields should be included for updated geological units.

Why are there differences (in counts) between D_INTERVAL and D_INTERVAL_MONITOR (checked - OK).

For D_INTERVAL_MONITOR

Comments - Miscellaneous Tables

20081209 (‘Minutes & Actions from Dec 9 Meeting - December 11, 2008’ Memo)

Comments

Database Release 5 (First Released 2012-06-15)

200908+

Initial evaluation of conversion between MS Access and MSSQL2008.

200910+

Expansion of the D_DOCUMENT table and addition of supporting table to expand the capability of the database with regard to the report library. Inclusion of REG library.

200911+

Testing of Viewlog with initial MSSQL2008 version. Correction and simplification of geologic material types.

200912+

Re-evaluation of RD_NAME_CODE table begins (including updates). CLOCA interval data incorporated. Correction of pumptest data. Incorporation of interval data in MSSQL2008 database (i.e. un-blobbed).

201001+

Incorporation of MTO wells started.

201002+

Beginning of process of conversion from MS Access to MSSQL2008. Testing begins of replication process (over VPN) with between Downsview and EarthFX. Bedrock wells - evaluation and correction. Screen lengths check and correction.

201004+

Setup and evaluation of replication process begins. Interval and group types evaluated.

201005+

Replication testing begins. Timeout problems due to size of interval-temporal tables.

201006+

Use of Groups and LOC_STUDY formalized. Initial problems with ‘identity’ fields using distributed replication. Incorporation of ‘Scarborough Report’ data. Re-evaluation of D_DOCUMENT (and supporting) tables.

20100610,23,30 (‘Database Meeting with Kelsy - June 10, 23 and 30’ Memo)

Comments

201007+

Delivery of initial MSSQL2008 version(s) and testing begins. Querytimeout parameters modified to correct issues with interval-temporal tables. Trusted versus untrusted connection problems with SiteFX/Viewlog. Corrected with new versions (of each). Replication through FTP is enabled.

20100728 (‘Database Meeting with Kelsy - June 10, 23 and 30’ Memo)

Comments

20100826 (‘Database Meeting - August 26, 2010’ Memo)

Discussed and approved database backup procedure using ‘Simple’ database model. York reading name codes found to not (necessarily) be equivalent to YPDT-CAMC codes (SiteFX adds new codes whenever reading name not present). Now corrected. Proposed adding of last modified user and date to all tables (for tracking purposes). ‘Identity’ field problems with primary keys (with regard to replication). Decision made to keep.

Data source and DATA_ID issues

Twelve baseflow methods (calculations) now included in the D_INTERVAL_TEMPORAL_2 table. All bad pumping test data have been removed. Target for first version SQL Server version of ‘Master’ database slated for 20100903.

201009+

First test of the replication process with CLOCA (over VPN). Inclusion of SYS_TEMP* fields in every table.

20100914

First working (Master) version within MSSQL2008 resulting from conversion of the original Microsoft Access database. All subsequent changes are to this version only.

201010+

Problems with D_GEOPHYSICAL* tables (blobbed data; no primary keys). Temporal data incorporated into two interval-temporal tables. Methodology for and assignment of geologic unit to screen interval begins. Calculation of ‘mbgs’ for all intervals incorporated.

201011+

RD_NAME_CODE (along with RD_NAME_ALIAS) re-evaluations continue. Assessment of required tables for SiteFX and streamlining of table information begins. Inclusion of surficial geologic unit. Bedrock depth calculation moved to a view. Additional fields included in interval-temporal 1A/B for method tracking and uncertainty.

20101112 (‘Database Meeting - November 12, 2010’ Memo)

Comments

20101119

Comparison between YPDT-CAMC database and MOE WWDB, for wells where there is only one screen reported there is some discrepancy between OUOM fields; to be overwritten with MOE data

201012+

First phase of table removal and data transfer (in general to D_INTERVAL_TEMPORAL_* tables). Evaluation of necessary ‘Views’ begins. Versioning by date recommended and approved. Added ‘last modified’ tracking columns for both date and user to most tables. Addition of SYS_LAST_MODIFED and SYS_LAST_MODIFIED_BY. Correction of some MOE wells, changing ‘Early Warning Well’ to ‘Supply Well’ - re-examining records for reported screens. Removal of duplicates and transfer of temporal data for various Ballantrae, Nobleton and Alton wells.

201101+

R_RD_NAME_CODE, R_RD_TYPE_CODE and R_READING_GROUP_CODE tables modified. Evaluate usage of D_BOREHOLE_SAMPLE and D_INTERVAL_SOIL. Sample data included from ‘Scarborough Report’ (Eyles and Doughty, 1996).

20110112 (‘Database Meeting Notes - January 12, 2011’ Memo)

Comments

201102+

Presentation of replication methodology (including distribution of partner databases from the ‘Master’) at Peel. Incorporation of DEM information (multiple sources). Proposed replication testing at Peel. Review diameter in borehole table and correct any problems. Check and removal of duplicate interval-temporal data.

201103+

Second phase of table removal and data transfer. Evaluation of fields within tables required by SiteFX. Review of requirements necessary for evaluation of replication methodology at Peel - overview documentation prepared and sent. Geologic screen assignments revisited. Duplicates in geology-layers checked and removed. Review dates and fix errors.

20110302 (‘Database Meeting Notes - March 2, 2011’ Memo)

Comments

201104+

Removal of D_BOREHOLE_SAMPLE and incorporation of data into D_INTERVAL, D_INTERVAL_SOIL and the D_INTERVAL_TEMPORAL_* tables. Evaluation of QA confidence codes and inclusion of out-of-area codes. Duplicate intervals and geology removed. Example consultant view prepared. Top and bottom depths of intervals calculated and included in interval-monitor table.

201105+

Geologic unit codes updated and assigned. Include model geologic unit assignments to intervals. Example databases for each agency prepared and distributed (for testing only). Halton and York database checks and comparison with master database. Check and remove invalid DATA_ID’s. Checked PGMN locations.

201106+

Addition of manual formation assignments to formation assignment table. Trust relationship problems between workstations and primary server (MSSQL2008). Incorporated old MOE water level data. Changed structure of D_LOCATION_ELEV table.

201107+

Start incorporation of historical chemistry data. Water level imports. Halton and York intitial database fixes (before import). Various reading units reviewed (e.g. water levels) and modified.

201108+

Addition of reading name aliases to include all interval data. Methodology for incorporation of logger data developed. Geologic and ‘consultant’ views added. Corrected problems with cascaded deletes (from within SiteFX). Start of Halton temporal data import. Correction of document table errors.

201109+

Initial review of to-date views. Amalgamation/correction of ‘flows’ reading name codes. Addition/update of reference elevations. Added views for use with Viewlog.

201110+

Corrections to isotope data. Removal of bedrock fields from D_BOREHOLE table (now calculated on-the-fly using V_General_BHs_Bedrock).

201111+

Addition of new tables to match latest SiteFX version(s). Initial training session (at Peel) using the MSSQL2008 database. Invalid coordinates ‘nulled’ and assigned appropriate QA code. Correction of D_BOREHOLE table due to missing BH_ID’s. Removal of invalid PICKS information. Initial setup of replication-over-web begins. Corrected pump information with regard to conversion between liters and gallons. Comprehensive review of required views. Moved specific capacity information to D_INTERVAL_TEMPORAL_2.

201112+

Amalgamation (and correction) of all PICKS information.

201201+

Simplification of all water level codes. Database manual preparation started. Correction of interval-temporal lab data.

201202+

Simplification of all pumping codes. General View’s (i.e. non-expert) setup. Evaluation of coordinate QA’s versus elevation QA’s. Removal of lab info from the interval-temporal tables with no associated data. Removal of duplicate records in D_INTERVAL_TEMPORAL_2.

20120224

Version of database standardized to this date (unreleased).

201203+

D_AGENCY abandoned and functionality moved to D_LOCATION_AGENCY (with change in structure). Use of type codes for tracking logger (and other) manufacturers implemented. Removed duplicates found imported interval-temporal tables. Applied type code methodology to interval-temporal tables. Corrected depths using D_BOREHOLE_CONSTRUCTION and D_GEOLOGY_LAYER.

201204+

Views for pumping and chemistry updated (returning additional fields). Bibliographic view implemented. Further correction of chemistry data, interval-temporal 1A/B tables (invalid number of SAM_ID’s). Re-evaluation of chemistry and water level R_RD_NAME_CODES. Unit codes modified to correct for conversion problems. Standardized to ‘mbref’ for all depth units. Populated all ‘null’ OUOM fields in the temporal tables.

201205+

Replication-over-web setup and trouble-shooting (for all partners). ‘Identity’ field problems return.

20120528

Addition of views to OAK_SUP for listing technical information on all tables in the master database.

201206+

Replication-over-web setup issues continue.

20120612

Check re-seeding of ident fields in various tables.

20120615

Version of database standardized to this date.

20120703+

Initial evaluation of D_INT_FORM_ASSIGN for duplicate intervals (i.e. duplicate top- and bottom-depths for a single INT_ID).

20120727+

Correction of D_GEOL_LAYER for Peel Region (spatial extent). Evaluation of methodology (in SQL) for determining shallow water table surface.

20120808

Initial evaluation of determining sand and gravel thicknesses as an indicator of aquifer availability.

20120809

Addition of V_WL_Average_lt_20m to OAK_SUP (for determination of shallow water table surface).

20120815

Correction of TRCA PGMN water levels (where no water level is recorded).

20120816

Initial evaluation of master-partner database checking (in SQL).

20120821

Corrected top- and bottom-depths of geologic layers in D_GEOL_LAYER.

20120823

Moved incorrectly located static water levels from D_INT_TEMP_1A/1B to D_INT_TEMP_2. Creation of LOC_ID QA/QC checks by EV (first draft).

20120827

Updated/corrected location elevations in D_LOC_ELEV and D_BOREHOLE.

20120907

Updated/corrected D_INT_REF_ELEV.

201210+

Updated OAK_SUP with V_SG_* views (for determining sand and gravel thicknesses for less-than and greater-than-or-equal-too 20m depths). Updated OAK_CHECKS with V_D_INT_FM_ASSIGN_* for checking whether an existing model has been applied against an interval and well as evaluating the top- and bottom-depths of screens (for correctness/conversion). Added V_D_DOCUMENT_…, V_D_INTERVAL_… and V_D_LOCATION_… to be used for checking (when importing) existing _ID’s.

20121016+

Reviewed D_INT_MON. A number of records are present (i.e. multiple monitor tops and bottoms) tied to a single INT_ID - this needs to be corrected. Likely tied back to MOE WWDB nested well configurations (checked against two intervals - this turned out to be the case). These should be changed to multiple locations each with a single interval.

20121024

Updated all valid intervals with the geologic models in D_INT_FM_ASSIGN - added the interpreted information for the Durham Model (2007). Re-calculated the ASSIGNED_UNIT field (to accommodate the additional model in the assignment logic; see Section 2.4.1).

20121118

Updated descriptions to all tables (D_ and R_) in database.

20121120

Added missing locations and elevations to D_LOC_ELEV.

20121128

Correction of ground elevations in D_BOREHOLE. Examination and initial correction of D_INT_REF_ELEV reference elevations. Added descriptions to all views in database.

20121130 (Database Meeting)

20121211+

Correction of chemistry data and sample information in D_INT_TMP_1A and D_INT_TMP_1B.

20121212

Updated D_INTERVAL setting all INT_TYPE_CODEs of 118 to 29 (‘Soil’ to ‘Sample’). Changed ‘Sample’ to ‘Soil or Rock’ (29) and dropped ‘Soil’ (118). Added values to ‘R_REC_STATUS_CODE’ to tag information that is not to be used in certain instances. Anything less than 100 is kept. Started work on D_INT_REF_ELEV removing all intervals whose INT_TYPE_CODEs are not related to having a reference elevation. Added missing intervals whose INT_TYPE_CODEs do relate (these would include type codes: 18,19,20,21,22,27,28,101,102,112). Removal of duplicate chemistry readings.

20121214

After discussion, decided to (in this version of the database) use INT_TYPE_ALT_CODE in R_INT_TYPE_CODE as a field for grouping similar interval ‘types’ together, for use in simplifying queries. As this is a text field, these groups must be specified as text strings. Currently only the grouping ‘Screen’ has been applied. The usage of this field can be applied to other reference tables as well (as needed). In subsequent version of the database, this may be replaced with a function similar to the R_RD_NAME_CODE and R_READING_GROUP_CODE tables.

20121217

Finished working on updating D_INT_REF_ELEV; updated all possible NULL REF_ELEVs with ‘ASSIGNED_ELEV + 0.75m’ values (~100000 updated; ~20000 remaining NULL values with either no ASSIGNED_ELEV or no BH_DRILL_END_DATE). Removal of duplicate intervals (i.e. intervals formed from having multiple screens within a single borehole).

20130109

Corrected D_BOREHOLE null bottom elevations (when available, assigned either the minimum bottom elevation of geology - from D_GEOLOGY_LAYER - for borehole OR minimum bottom elevation of construction element - from D_BOREHOLE_CONSTRUCTION - for borehole).

20130110+

Correcting D_GEOLOGY_LAYER for null GEOL_BOT_OUOM values begins (mainly a problem for Oil & Gas wells).

20130122

Updated D_BOREHOLE, modifying the BH_BOTTOM_ELEV, BH_BOTTOM_DEPTH, BH_BOTTOM_OUOM and BH_BOTTOM_UNIT_OUOM fields. As many of the boreholes present in this table (~22000) had NULL or invalid bottom elevations (or depths), these were corrected (previously) based upon construction or geology details (D_BOREHOLE_CONSTRUCTION and D_GEOLOGY_LAYER, respectively). For the remainder, any screen present in the D_INTERVAL_MONITOR table was used to extract a bottom depth (plus 0.3m) then converted to an elevation. If the borehole did not have a screen, the BH_BOTTOM_ELEV was given the same value as the BH_GND_ELEV and the remainder of the _BOTTOM fields assigned NULL values.

20130214+

Updated D_LOCATION_QA; all those locations with a NULL QA_COORD_CONFIDENCE_CODE have had a ‘9’ (i.e. ‘Unknown’) assigned. York has started comparing records of their municipal wells - some problems that need to be corrected.

20130218

Addition of ‘V_D_LOCATION_Geometry’ to OAK_SUP (for spatial objects in the ‘Master’ database). Addition of the BH_BEDROCK_ELEV column to D_BOREHOLE - this is to be used instead of the dynamic-search implemented in ‘V_General_BHs_Bedrock’ (for speed considerations).

20130306

Addition of the field MON_WL_ELEV_AVG to D_INTERVAL_MONITOR. Contains the average water-level at the interval across the whole temporal record available.

20130311+

Initial examination of monthly water levels as compared to the DEM show invalid water level elevations (far above surface). Start of the correction of elevation and water level values for locations.

20130313+

Many borehole depths (seemingly) invalid (not including 20130122, above). Start of re-examination and population of borehole depths (in D_BOREHOLE).

20130314

Added ‘Alternate’ (‘6’; Class Code ‘Lithology’, ‘1’) to R_GEOL_SUBCLASS_CODE. This is used to differentiate between geologic interpretations for the same location/borehole.

20130319

Corrected stratigraphic top-and-bottom problems for various MOE bedrock wells (previously missing top- or bottom- depth/elevations). Updated values copied into D_BOREHOLE (BH_BEDROCK_ELEV).

20130320

Added ‘Original (Invalid)’ (‘7’; Class Code ‘Lithology’, ‘1’) to R_GEOL_SUBCLASS_CODE. This is used to tag invalid geologic interpretations (usually problem units from the MOE, mainly lack of elevation/depth data). The ‘Original’ subclass description is now changed to ‘Original (or Corrected)’.

20130401+

Start of process for addition of tagged locations for each partner agency into D_LOCATION_AGENCY. Methodology now incorporates data from the OAK_SPATIAL accessory database.

20130405

Added INT_NAME_MAP to D_INTERVAL. This is to be used for plotting of intervals in plan (i.e. overhead) view and matches LOC_NAME_MAP in D_LOCATION. These names for intervals/locations can be no longer than ten characters in length.

20130407

Added soil intervals - those with tops and bottoms - to the D_INTERVAL_FORMATION_ASSIGNMENT table and applied the CORE Model (2004) interpretation. These are to be incorporated along with the screen intervals in future updates.

20130408+

Started correcting intervals in both D_INT_MON and D_INT_SOIL creating top- and/or bottom-depths and elevations as appropriate (or available). These are subsequently to be used in populating D_INT_FM_ASSIGN.

20130419

Alternate PICKS2 created with indexes and keys setup using the method described in Appendix H (dated 20130419b). This is to be used for the foreseeable future to avoid the pick-delay problems with the original PICKS table. This is to be fixed in the next version of the database.

20130501

Dropped RD_NAME_CODE of ‘630’ (‘Water Level - MOE Well Record’) from R_RD_NAME_CODE and R_READING_NAME_ALIAS. Use the appropriate R_RD_TYPE_CODE to access MOE water levels.

Updated R_GEOL_UNIT_CODE, standardizing the YPDT-CAMC model layer names. Added an AQUIFER tag (a NULL, ‘0’ or ‘1’ value; ‘1’ indicating an aquifer unit) to be used when assigning formations in D_INTERVAL_FORMATION_ASSIGNMENT (allowing the ‘aquifer’ layer to be assigned preferentially if the top and bottom of the screen do not match).

20130506

Added ~130000 rows to D_LOCATION_ALIAS - these are BORE_HOLE_IDs (from the MOE WWDB) that were otherwise missing. As WELL_ID is not a primary key, we used WELL_ID, EAST83, NORTH83 and ELEVATION to match against LOC_IDs in D_LOCATION. This has been included as a comment if any checking is required. Note that there still a number of BORE_HOLE_IDs not matched to LOC_IDs in the database as these four fields were not matched exactly. This still needs to be corrected.

ELEV_ORIGINAL is now populated in D_LOCATION_ELEV based on these new aliases (i.e. linked back to the MOE WWDB 201304 cut).

20130510

Corrected formations - mainly related to bedrock elevations - for another set of boreholes (mostly MOE). Update D_BOREHOLE with new elevations.

20130604

Updated D_LOCATION_GEOM; start of updating D_LOCATION_AGENCY based upon temporary CHK_* views (as tables). Re-initialized York and LSRCA databases.

20130607

Completed updating D_LOCATION_AGENCY.

201307

Start work on the next version of the database (tentatively referenced as OAK_20130701MASTER or v20130701). This includes: complete SQL scripts for creation of all tables and their associated keys, indexes and triggers; evaluate methods (including functions and stored procedures) to implement partner-specific ranges of values without resort to identity fields (this involves the user of the bigint type; problems using this are to be examined); re-evaluate the replication method including the presence of SYS_TEMP* fields (which must be included for access by SiteFX but should not be replicated) and SiteFX specific S* tables.

201308

Initial evaluation of the CLOCA partner database for import of differences into the master db. Problems encountered between conflicting primary keys - partner db’s and the master db can end up with the same primary keys in some tables. One table uses incremental values (an S_* table) which guarantees this. The development of the official QA/QC procedures/methods is necessary.

20130826

In D_LOCATION, where LOC_COORD_OUOM_CODE is null, copied the LOC_COORD_EASTING and LOC_COORD_NORTHING values (if non-null) into their respective *_OUOM fields and assigned a value of ‘4’ (i.e. UTMz17 NAD83) to the LOC_COORD_OUOM_CODE field. Various coordinate corrections (and QA_COORD_CONFIDENCE_CODE corrections) during this process.

20130927

Corrected layer info in D_GEOLOGY_LAYER (examining units and NULL values) and updated D_BOREHOLE as required.

20130930

Corrected layer info in D_GEOLOGY_LAYER (examining units and NULL values) and updated D_BOREHOLE as required.

20131004

Corrected chemistry parameters in D_INTERVAL_TEMP_1B and added aliases (as necessary and appropriate) into R_READING_NAME_ALIAS.

20131007

Focussed look at the CLOCA partner database for inclusion within the master db. Note that this is to be used as a test case for the QA/QC methods/procedures (and is the continuation of an earlier test).

20131011

CLOCA partner database information has been added to the master db.

20131018a

Correction of the location of ‘Conductivity’ (temporal) values, moving them from D_INTERVAL_TEMPORAL_1A/1B to D_INTERVAL_TEMPORAL_2 (note that it is possible that values can be present in the 1A/1B tables - the analysis would have to be lab based only).

20131018b

Addition of ‘Active (Monitoring)’ and ‘Inactive (Monitoring)’ codes to the R_LOC_STATUS_CODE table (to differentiate from the MOE WWDB understanding of ‘Active’ and ‘Inactive’).

20131022 and 23

Updated D_LOCATION coordinates (translated as necessary) prior to examining elevations. QA code ‘117’ is beginning to be used to tag those locations outside of the YPDT-CAMC buffered area.

Re-examined elevations in D_BOREHOLE and D_LOCATION_ELEV. Each of BH_GND_ELEV, BH_GND_ELEV_OUOM and BH_DEM_GND_ELEV should now match (for those locations with a valid QA, i.e. not ‘117’) that of the ASSIGNED_ELEV in D_LOCATION_ELEV. The BH_GND_ELEV_OUOM values have been copied/converted to ELEV_ORIGINAL (if not already present). The BH_GND_ELEV_UNIT_OUOM has been standardized to ‘masl’ (the BH_GND_ELEV_OUOM value was converted as necessary).

20131202

Evaluated possible problem with ‘V_General_Consultant_Hydrogeology’ - turns out that multiple instances of an INT_ID in D_INTERVAL_FORMATION_ASSIGNMENT results in the doubling (or other multiplier) of the number of water levels associated with the interval. Note that this is not an error with a view but with the population of the the formation assignment table - there should only be one row/tuple for each INT_ID. The supplementary check database view ‘CHK_D_INT_FM_ASSIGN_Multiple_INT_IDs’ should be run periodically to correct this. Note also that these ‘blank’ rows (i.e. the additional INT_IDs) have been added in many cases by the ‘NT Authority’ or ‘YKREGION\SQLAgentAdmin’ user - this is not mapped to an actual user (so where are these runs coming from? Is this a SiteFX issue? - supposedly not).

20131209

Fixed various data constraint issues (i.e. information currently in the database not correctly matching ‘new’ constraints) when developing methodology and testing conversion/translation of data between database and spreadsheet formats. This included (but were not necessarily limited to) the tables D_BOREHOLE, D_DOCUMENT, D_GEOLOGY_LAYER, D_GROUP_LOCATION, D_INTERVAL_REF_ELEV, D_LOCATION_ALIAS and D_LOCATION_PURPOSE.

20131211

Updated D_INTERVAL_TEMPORAL_2; all rows with no values, no OUOM values and no comments were removed. This should be consistently applied in the future.

20140115

Deleted ~1100 records from D_INTERVAL_FORMATION_ASSIGNMENT that contained repeating blank/empty rows for various INT_IDs. There are multiple SYS_LAST_MODIFIED_BY users (no SYS_USER_STAMP) from both Y-C and the partner agencies - is there some automatic processing running? Monitor these occurrences.

20140117

Modified V_Random_ID_Creator to randomize ID’s between specified values (actually a lower value and range). A view needs to be created for each user/partner (according to the uniqueIDrange as found in S_USER for SiteFX). Currently V_Random_ID_Creator_MD and V_Random_ID_Creator_REG has been created.

20140206

Started in 20130923, the methodology for the MOE WWDB updates to be included as part of the YPDT-CAMC database are near-completion. The final steps concerning the inclusion of boreholes with no geology remain. Notes regarding possible issues and corrections applied (as needed) are shown here.

Notes on 2013 MOE Well Import File

20140605-06

Started in 20130923, the methodology for the MOE WWDB updates to be included as part of the YPDT-CAMC database are complete (refer to Section G.10 for methodology). This new information has now been added to the database. Notes regarding possible issues and corrections applied (as needed) are shown here.

Notes on MOE 2013 Well Import D-Location - 16525 Wells coming into DB Min LocID = 240000035 Max LocID = 241680698

D_BOREHOLE TABLE

D_BOREHOLE_CONSTRUCTION TABLE

D_GEOLOGY_FEATURE_CODE TABLE

D_GEOL_LAYER TABLE

D_INTERVAL TABLE

D_INTERVAL_MONITOR TABLE

D_INTERVAL_REF_ELEV TABLE

D_LOCATION

D_LOCATION_ELEV

D_LOCATION_QA

20140606

As part of the MOE WWDB import (201304 version), various corrections for other locations were applied using standard procedures/methodology as developed for the water well database.

D_INTERVAL_MONITOR rows were found with no bottom elevations/depths; these were found to correspond to zero thickness bedrock wells. In these cases, ‘0.3m’ screen intervals were imposed above the bottom of the well.

201406

Started assembling information for creating a new version of the database; both this document and Appendix H (‘Current Problems’) will be examined for necessary changes. The schema created by SiteFX will be reviewed and both additions and modifications will be included as necessary.

20140716

An SSMA_Timestamp should be incorporated into each table - they help avoid errors when Access updates records in tables linked to SQL Server. Review ‘Supporting Concurrency Checks’ from Microsoft documents. In particular, ‘The timestamp column increases automatically every time a new value is assigned to any column in the table. Access automatically detects when a table contains this type of column and uses it in the WHERE clause of all UPDATE and DELETE statements affecting that table’ (Stack Overflow, 2012).

20140723

At the moment, ranges of values include NULL, ‘-1’ through ‘2’. The ‘-1’ values will be changed to ‘1’ and ‘0’ will be changed to NULL. What is ‘2’ indicating?

201408

Started implementing the new version of the database. Corrections will be made on-the-fly with regard to errors while copying information between the old and the new versions.

20140908

Updated R_LOC_WATERSHED1_CODE removing multiple ‘Rouge River’ indicators.

20140909

D_INTERVAL_REF_ELEV has NULL values for REF_ELEV_START_DATE - this has been corrected (assigned the ‘holder’ value ‘1867-07-01’). A note should be made to populate this field upon data entry (this should be a constraint in the new version of the database).

In D_LOCATION_QA, what is QA_COORD_CONFIDENCE_CODE_UPDATE (a ‘bit’ field) being used for? Should this be removed (likely, yes).

For D_LOCATION_VULNERABILITY: Should this table be re-evaluated? ‘AVI_Feb2003_Final 3 tier’ column name should be changed. ‘Water Table Sept 04 from model’ column name should be changed.

Added R_GEOL_LAYERTYPE_CODE into OAK_20120615_MASTER (non-replicating) and populated manually from the original Microsoft Access database. The ‘0’ value was not included (no description was tagged to GEOL_LAYERTYPE_CODE ‘0’). This value (i.e. ‘0’) was removed (assigned a NULL value) from D_GEOLOGY_LAYER (the GEOL_LAYERTYPE_CODE field).

20140910

For D_INTERVAL_FORMATION_ASSIGNMENT: SYS_RECORD_ID has been removed as both a key and a field - there should be only a single row per INT_ID. Refer to Section 2.4.1 for the reasoning behind this. Do we want to re-think the setup of this table? Instead of adding additional columns for each model, should we simplify the table and add a look-up table for model codes? An example would be:

INT_ID,ASSIGN_CODE,ASSIGN_DATE,GEOL_UNIT_CODE,MODEL_CODE, VDIST

Where ASSIGN_CODE (a look-up table) would consist of (for example):

And MODEL_CODE (a look-up table) would consist of (for example):

Note that VDIST would only be populated for ‘Next ?’ and ‘Previous ?’ records.

There are various problems with this table (invalid GEOL_UNIT_CODE; invalid INT_ID) that should be addressed.

20141017

D_INTERVAL_TEMPORAL_1B and D_INTERVAL_TEMPORAL_2 have had their SYS_RECORD_ID’s substituted for ranges of values that have been assigned to PEEL, CLOCA, YORK and NVCA. This is to, temporarily, avoid errors when incorporating temporal data in the database where the primary keys conflict. This arises from each of the partner agencies having a subset of the total database - keys can exist in the master that do not exist in their copy (and will conflict during replication). Note that some sort of fix should be applied to all tables with identifiers.

20141022

D_INTERVAL_MONITOR is missing screened intervals (as specified in D_INTERVAL); this includes INT_TYPE_CODE’s of: 18, 19, 21, 22 and 27. Each is assigned tops and bottoms as appropriate. See write-up in Section G.23.

20141110

D_LOCATION_PURPOSE is setup in a manner such that multiple purposes can be assigned to a particular location - this can take the form of multiple ‘rows’ of information. Upon examination, it is found that there has been locations with more than two purposes assigned them (i.e. other than a single row with PURPOSE_PRIMARY_CODE and PURPOSE_SECONDARY_CODE). There is an end date indicator (PURPOSE_DATE_END) but this is not being used. In this case the multiples are repeating purposes (i.e. duplicates) for that particular location - these duplicates have now been removed. However, more than two purposes can be assigned to any particular location, still (see the PTTW dataset). If this is to continue, an indicator field - PRIMACY - is suggested which will tag the rows in the order that they should be considered.

20141111

Corrected location issue with regard to the MOE WWDB 201304 import - geocoding issues placed a subset of wells (~900) in the likely incorrect location. This should continually be checked (using D_LOCATION_QA) when working with these locations. QA_COORD_SOURCE and QA_COORD_COMMENT contain the relative details regarding the coordinate assignment.

20141112

Re-populated the contents of D_LOCATION_GEOM so as to incorporate changes in the D_LOCATION table (including the update of the MOE 201304 imported boreholes).

Examined resulting table (through plotting). Corrected locations with invalid coordinates or assigned the invalid tag of ‘117’ in D_LOCATION_QA.

It was noticed, during these coordinate corrections, that at times a 6-digit number actually conforms to a lat/long designation in ‘ddmmss’ format. This is primarily found for Environment Canada climate stations and surface water sites. This was then checked against other locations in D_LOCATION with coordinates modified as appropriate. If addresses were found, an attempt at geocoding was performed.

20141201

Values within the temporal tables are found to have RD_NAME_CODEs that do not have a READING_GROUP_CODE assigned. These have been updated as appropriate (either by modifying the RD_NAME_CODE and RD_NAME_OUOM or by applying a READING_GROUP_CODE to the RD_NAME_CODE). Note that in the case of temperatures, some seem to exceed that which would be appropriate to groundwater but are associated with a ‘reported screen’.

Users are assigning parameters to invalid temporal tables (e.g. herbicides in DIT2; water levels in DIT1B). This is likely due to the default SiteFX setup. RD_NAME_CODEs have been evaluated and re-assigned as necessary.

20141215

Reset invalid ‘Temperature’ records, reassigning them to the specific RD_NAME_CODE for their interval type (either a barometric logger or water level/temperature logger).

Corrected invalid DIT2 and DIT1B rows to appropriate table.

201502+

Start if review of ‘new’ database format (views and tables).

20150224

D_BOREHOLE and D_LOCATION_ELEV elevations are once again out-of-sync. Some of these are tagged as ‘surveyed’ but still have their elevations changed - once this tag is in place, no elevation changes should ever again be made. Corrected all of the changes (based on the QA_COORD_CONFIDENCE_CODE).

Updated all elevations (BH_GND_ELEV, BH_GND_ELEV_OUOM and BH_DEM_GND_ELEV) setting them to equivalent values (namely the ASSIGNED_ELEV); assigned BH_GND_ELEV_UNIT_OUOM to ‘masl’. Where the location did not have an ELEV_ORIGINAL, the BH_GND_ELEV_OUOM was used to populate the field (converting to ‘masl’ as appropriate).

Updated/corrected all BH_BEDROCK_ELEV where the elevation has changed (subsequent to the correction of elevations, above).

Removed ‘soil’ intervals (1) and ‘surface water spot stage elevation’ intervals (27) from D_INTERVAL_MONITOR. Removed duplicates from D_INTERVAL_MONITOR. Starting correcting of top- and bottom-screen intervals based upon various assumptions (e.g. ‘0.3’ metres above bottom of hole for INT_TYPE_CODE ‘19’). Started re-examining incorporation of various screens into D_INTERVAL_MONITOR based upon various assumptions (e.g. as in the preceding sentence).

20150225

Incorporated ‘new’ intervals into D_INTERVAL_MONITOR with INT_TYPE_CODE 21 where:

INT_TYPE_CODE 21 - Assumed Open Hole (Bot. of Casing to Bot. of Hole) these are assumed bedrock wells (i.e. they have casing that extends to approx top of bedrock)

Boreholes with a difference between the bottom of the hole and the bottom of casing greater than 0.1m were incorporated as is. The bottom-of-casing is considered to be the top-of-bedrock (and an update was made in D_BOREHOLE).

20150227

Finished updating/modifying INT_TYPE_CODE ‘21’; reassigned those (to ‘123’) that were not appropriate. Approximately 4000 do not have elevations due to invalid coordinates. Update required some evaluation of BH_BEDROCK_ELEV and changes in D_GEOLOGY_LAYER; for the latter, a review of ‘fbgs’ OUOMs (which tend to be integer values) and ‘mbgs’ OUOMs (which tend to be real values) should be evaluatated. It appears that some current ‘mbgs’ should be re-tagged as ‘fbgs’.

20150303

Updating INT_TYPE_CODE ‘28’ (Screen Information Omitted). UGAIS wells (DATA_ID ‘2076806159’, i.e. DATA_DESCRIPTION ‘MNDM - Ontario Geological Survey (UGAIS Wells)’) are assigned INT_TYPE_CODE ‘22’ (Assumed open hole, top of bedrock to bottom of hole) where the BH_BEDROCK_ELEV is greater than 0.3m above the BH_BOTTOM_ELEV. Note that only a subset of these records contain actual water level readings.

20150305

Updating INT_TYPE_CODE ‘28’ (Screen Information Omitted). Changed to INT_TYPE_CODE ‘22’ or ‘19’ based upon the presence of a BH_BEDROCK_ELEV. Deleted those records with no valid bottom depth of any kind (the remainder were from the MOE WWDB 201304 import). Twenty-four of these remain. There are >8000 INT_TYPE_CODEs ‘28’ remaining.

20150306

Update D_BOREHOLE_CONSTRUCTION; corrected CON_TOP_OUOM and CON_BOT_OUOM based upon casing information. Units are converted from ‘inch’ and ‘cm’ to ‘mbgs’. Where CON_UNIT_OUOM is NULL, numeric ‘integer’ depths are assigned ‘fbgs’; numeric ‘real’ (i.e. with a decimal) depths are assigned ‘mbgs’. Corrected (added) depths based upon construction details.

20150313

Summary review of ‘new’ database views (and related tables).

DB REVIEW - March 13, 2015

Views

  1. V_Consultant_Document - OK
  2. V_Consultant_General
    • Well Depth is missing
    • Change Surface_Elev to Ground Elev
    • Change Interval_Monitor_Num - to Number_Of_Screens
    • Change BH_Diameter_M to BH_Diameter_cm - (or whatever the correct unit is - it is not metres)
    • Given that the Number/StartDate/EndDate of the Water Levels and Water Quality info is already in the “V_Consultant_Hydrogeology” View and the intent is to provide all of these together - I think we can remove these fields from this table.
    • Wonder about the Several “Water_Found” fields at the end of the table - with no depth provided I don’t know if this is all that useful to include in the view
  3. V_Consultant_Geology - OK
  4. V_Consultant_Hydrogeology
    • We should add “Screened Formation” to this view
    • What is the difference between Pumptest_Start and Pump_Start_Date (and Pumptest_End vs Pump_End_Date
    • Similarly - doesn’t Pumptest_Date_MDY relect the Pumptest_Start - they look the same - except maybe the time?
    • Does the Field “Pump_Readings_Num” record the number of water levels during the pump test? Or a change in pumping rate? Maybe change to “Test_WL_Readings_Num”?
    • How come the “Pumptest_Rate_IGPM” is in whole numbers and the “Pumptest_Rec_Rate_IGPM” has decimal places - check the numbers to make sure that we didn’t convert Gal to L or vice versa - make decimal places consistent between two fields. No decimal places unless we converted from L to gal - then maybe 2 decimals.
    • Wonder if we should add “Daily_Pumping_Volume_Num” (and start/end dates) to provide indication of the data availability for the main pumping wells.
  5. V_Consultant_Pick
    • This View doesn’t return any records.
  6. V_General
    • Purpose_Primary and Purpose_Secondary (Fix spelling) - do not appear to pull the data correctly - they are all null - should pull description from the correct R tables;
    • Move “QA_Coord_Code” - so that is right beside “Coord_Confidence” field;
    • Move “Status_Code” so that it is located beside the “Status” field;
    • Move “Type_Code” so that it is located right beside the “Type” field - also change name from “Type” to “Location_Type”
    • What does “Access_Code” refer to? Do we need it here?
    • I see some of the 2006 PTTW records in here - did you create this DB before we deleted and replaced them?
  7. V_General_Borehole
    • Since all records in View are from the D_Borehole - we can probably remove the “Type” field (are there any outcrops in this view? Maybe we remove them from here.
    • Change “Surface_Elev” to “Ground_Elev”
    • Change “Bottom_Depth” to “Bottom_Depth_m”
    • Change “Data_MDY” to “Date_Drilled_MDY”
    • Change “Interval_Monitor_Num” - to “Number_Of_Screens”
    • Change “Screen_Geol_Unit” to “Screened_Geol_Unit”
    • What does “Screen_Geol_Unit_Elev” point to? Top/Middle/bottom of geol Unit? Top of screen? Bottom of Screen?
    • I don’t think we would need “Status_Code”, “Access_Code” or “Type_Code” in this View
  8. V_General_Borehole_Bedrock
    • Do we need this View anymore? It’s the same as previous except the Bedrock Field is never null. If we keep it - then we might want to change name to “Bedrock_Reached” _ notice there are lots of wells in the table where the screen is in an overburden unit and not in the bedrock.
  9. V_General_Borehole_Outcrop
    • Change name to V_General_Outcrop
    • I think the Alternative_Name and the Study fields are both Null for all Outcrops - maybe we can remove them
    • Remove “Type” field?
    • Change name of “Bottom_Depth” to Bottom_Depth_m)
    • Remove “Borehole_Diameter_cm” - not applicable
    • Do any of the outcrops have a date associated with them? If not then remove - if yes then let’s change the name from “Date_MDY” to “Date_Logged_MDY”
    • Remove fields “Interval_Monitor_Num”; “Interval_Soil_Num”; “Screen_Geol_Unit”; “Screen_Geol_Unit_Elev”; “Status_Code”; “Access_Code” and “Type_Code” all are not applicable and/or blank/consistent.
    • Maybe we could add a Field “Num_of_Geol_Layers” - and record the number of records for each outcrop in the D_Geol_Layer table.
  10. V_General_Chemistry
    • What is in this view? Does it include everything from all of the following Chemistry Views? If yes - should we change name to “V_General_Chemistry_All”
    • Change “Name” to “Loc_Name”
    • What does “Formation” refer to? Change to “Screened_Formation”.
    • I don’t think anyone would care about “Bedrock_Elevation” in here - delete field.
    • Change “Group_Code” to the description - or add description and leave the Code.
    • What does Int_Access_Code refer to? Remove?
    • What does Int_Active_Code - refer to? Is it updated/used? Remove?
    • This view also returns all of the soil grain size analyses and other lab data that pertains to soils - which I guess is why we called it “Lab” before. Don’t know if we should change name or change what the view returns (Int_Type <> “Soil or Rock”) - we should discuss.
  11. V_General_Chemistry_Bacteriologicals
    • Same points as for V_General_Chemistry
  12. V_General_Chemistry_Extractables
    • Same points as for V_General_Chemistry
    • Maybe we should add the “Qualifier” Field so that all of the “<” signs pop up
  13. V_General_Chemistry_Herbicides_Pesticides
    • Same points as for V_General_Chemistry
    • Maybe we should add the “Qualifier” Field so that all of the “<” signs pop up
  14. V_General_Chemistry_Ions
    • Same points as for V_General_Chemistry
  15. V_General_Chemistry_Isotopes
    • Same points as for V_General_Chemistry
  16. V_General_Chemistry_Metals
    • Same points as for V_General_Chemistry
    • Maybe we should add the “Qualifier” Field so that all of the “<” signs pop up
  17. V_General_Chemistry_Organics
    • Same points as for V_General_Chemistry
    • Maybe we should add the “Qualifier” Field so that all of the “<” signs pop up
  18. V_General_Chemistry_SVOCs
    • Same points as for V_General_Chemistry
    • Maybe we should add the “Qualifier” Field so that all of the “<” signs pop up
  19. V_General_Chemistry_VOCs
    • Same points as for V_General_Chemistry
    • Maybe we should add the “Qualifier” Field so that all of the “<” signs pop up
  20. V_General_Document
    • Remove “Study” field - not used for documents
  21. V_General_Document_Bibliography - OK
  22. V_General_Field
    • What does this View Return? All Field Data? Change Name to “General_Field_All”?
    • Change “Name” to “Loc_Name”
    • Change “Parameter_Type” to “Measurement_Descriptor”
    • Remove “Bedrock_Elev” - not relevant to this View
    • Remove “Int_Access_Code” and “Int_Active_Code”
  23. V_General_Field_Metereological
    • Change “Name” to “Loc__Alternative_Name” - the AltName field has the Environment Canada Name - so its easier for folks to quickly know the Climate Station location
    • Maybe remove the “Int_Type” and “Int_Type_Code” - since all of these should be climate station intervals?
    • What about adding RD_NAME_CODE to this View - someone might want to query out snowpack or something and the code might be handy (rather than text);
    • Change “Parameter_Type” to “Measurement_Descriptor”
    • Isn’t the “Group_Code” all 22 for the metereological data? If it doesn’t change then maybe we can delete this field.
    • Remove “Int_Access_Code” and “Int_Active_Code”
  24. V_General_Field_Stream_Flow
    • I didn’t check all - but if the Int_Alt_Name always has the Env Canada Long Name - then its fine - however if it doesn’t then maybe we have to add Loc_Alternative_Name to show this.
    • Maybe remove the “Int_Type” and “Int_Type_Code” - since all of these should be surface water stations?
    • Isn’t the “Group_Code” all 25 for the stream flow data? If it doesn’t change then maybe we can delete this field.
    • Change “Parameter_Type” to “Measurement_Descriptor”
    • Remove “Int_Access_Code” and “Int_Active_Code”
  25. V_General_Field_Summary
    • So does this summarize the Count and start/end dates for each Parameter for each Interval from Int 2? Looks like Climate and Streamflow Ints are omitted. Maybe change name to reflect this - “V_General_Field_Summary_BHs”
    • Remove “Bedrock_Elev” - not relevant to this View
    • Wonder if a “Value_Avg” would be a useful addition to this table?
    • Remove “Int_Access_Code” and “Int_Active_Code”
  26. V_General_Geology
    • Change “Name” to “Loc_Name”
    • Maybe add “Layer_Thickness”?
    • Don’t know if we need UTMs and QA code in here - don’t think that plotting would occur very often from this View
    • Remove Type_Code; Access_Code; and Status_Code - not needed
    • For those wells that have it - it might be nice to add in the “GSC_Code”
  27. V_General_Geology_Deepest_Nonrock
    • Don’t know if we need UTMs and QA code in here - don’t think that plotting would occur very often from this View
    • Remove Type_Code; Access_Code; and Status_Code - not needed
  28. V_General_Geology_Outcrop
    • If we separate Outcrops - then should we rename V_General_Geology - to “V_General_Geology_BHs”?
    • Same points as above
  29. V_General_Hydrogeology
    • This view only pulls BHs right? Therefore the Type and Type_Code are likely not needed
    • Access_Code not needed
    • Same as for “V_Consultant_Hydrogeology” - Rectify difference between Pump_Readings and Pumping_Test - is one showing the daily pumping rates? If yes - let’s try find a better name - how about “Daily Pumped Volume_Num” (too long?)
    • We should add “Screened Formation” to this view
    • Is “Name” - the “Int Name” or the “Loc_Name”? - clarify
    • We need to check the field “PUMPTEST_REC_RATE_IGPM” - the values do not appear to be whole numbers - even when the pumping rate is a whole number - did we do a conversion here that we didn’t mean to?
  30. V_General_MOE_Report
    • What is the field “WellNumbers? - mostly contains ND(ND)ND?
    • Does “Water” indicate “Water_Found”? Maybe change name?
  31. V_General_MOE_Well
    • We need to add a field for MOE_Well_ID (Loc Original Name)
    • I don’t think that Primary and Secondary Use are being populated correctly - they are all null for the first 1000 records
    • “Access Code” and “Type Code” and “Type” are probably not needed
  32. V_General_Pick
    • View doesn’t return any records
  33. V_General_Station_Climate
    • How about changing name to “V_General_Field_Summary_Meteorological” - similar to #25
    • We should add Int_ID and Maybe IntNAme - so if someone wants to quickly find the info the Int_ID is right here.
    • Type; Type_Code; and Access_Code are likely not needed
    • There are quite a few stations that have no Precip or Temp readings - is that correct? No data for them - wonder if we should delete them?
  34. V_General_Station_SurfaceWater
    • How about changing name to “V_General_Field_Summary_SurfaceWater” - similar to #25
    • What about adding in the Max and Min Water Level so that the Staff Gauges get summarized
    • Any chance that for the staff gauges we could have the min/max/avg/start date/end date and number of WL readings - I guess this might mean we need two sets of readings here (one for WLev and one for streamflow)
    • I think the status code should also be in text (i.e. “Active” vs “Not Active”)
  35. V_General_Water_Level_Best
    • I don’t like the name of this view - cannot tell what is returned?
    • If we keep this View - how about changing WL_Source to WL_Type (i.e. static vs logger vs other?)
  36. “General”
    • What do you think of removing “General” from all of the Views - I don’t think it provides any info that is useful. If you/Rick want to go for only “pure” views that have codes only or no change in field names - then why not preface those with a “YPDT” - it looks to me that most of your stuff/Rick’s stuff is tucked away in your linked DBs anyway. Let’s discuss again.
  37. “Names”
    • In general you tend to use Name - in many of the views - and this is tied to LocName - I think the Loc_Name_Alt1 is a much more informative name in general - for spot flows and MOE wells - the AltName1 is better. Just thinking about what I have said below here - that we should combine “Consultant Views” with the General Vviews” to provide one - we are not supposed to give out “Owner” - so wondering if Loc_Name_Alt1” is different enough from “owner” that I can plead it wasn’t intentional or if we should have one separate view for consultants - where we drop the Alt_1 name. We should discuss when you read this.
  38. Sitefx_SearchLoc_YPDT_Custom_view
    • Provides the info for one template format that we need to keep - there might be others - so this view should be transferred to the new DB
  39. Overburden Wells
    • To get overburden wells - we would filter General_BHs for “Bedrock_Elev is null” - correct? [Which way are we approaching this - users that know exactly what they’re doing OR general users?]
  40. Consultant PTTW
    • We formerly had a View Consultant PTTW - we should likely make a new one with our revised PTTW tables. [Revised tables have not, thus far, been approved.]
  41. V_Consultant_TemporalData
    • We also had a V_Consultant_TemporalData - the intent of this view was to pull info from Interval_Temporal 2 - where a flag in D_Location (SysTemp1 or SysTemp2) was inserted. The consultants were to be given the first views and based on the info - largely in Consultant_Hydrogeology - they would be able to see if they needed any of the temporal data.
  42. Climate station views
    • The Views for the Climate Stations are missing - We should restore V_General_Climate_Annual_Prec_Summary - this provides the number of readings in any given year as well as the Avg/Max/Min for the year - it looks like to me the Avg is only calculated when the number of readings in any given year reaches close to 365 (maybe above 360) - but I don’t know if the annual avg precip is all that valueable - we can discuss - but maybe we dump the Avg and just keep the max and min? I also see some stations where the number of readings in a year exceeds 365 - can we check to see why that is? (See point below)
  43. V_General_Climate_Annual_Temp_Summary
    • Restore - this view is similar to the above - except with Temp. Again some years have more than 365 readings (e.g. Toronto City - 2004 has 397 readings - I suppose that if they took more than one measurement per day this could happen - don’t know how it would affect the Avg calculation though - if it is an issue - then maybe we can dump the Avg. (See point below)
  44. General_Climate_Data_Range
    • The current view takes too long to run (>11 minutes) - but I thought you were going to rejig things to have some of the key info temporarily stored in Summary Tables? Off-hand I don’t see any tables that house summarized climate data - but I do see the View YPDT_Sys_Summary_Temp_Air that basically appears to do the same as the old view - but only for Temp. I see YPDT_Sys_Summary_Precip does the same for precip. These views are infinitely faster - so they must be relying on a built table somewhere. Wonder if we want to have them in the same view and pull it out of the “Sys_View” pile and put it up in general?
  45. The View “General Climate Stations” View (repeat for Streamflow stations)
    • Can be restored - its pretty basic - but at least should provide the status for all of the climate stations (active vs non-active). - ok I see it below with new name.
    • How about for Climate Stations/Meteorological - what if we have the following views:
    • V_General_Station_Climate_All_Data (currently named “Field_Meteorological”
    • V_General_Station_Climate_Summary (currently named “Station_Climate”)
    • V_General_Station_Climate_Annual_Temp_Summary
    • V_General_Station_Climate_Annual_Precip_Summary
    • V_General_Station_Streamflow_All_Data (currently named “Field_Stream_Flow”)
    • V_General_Station_Streamflow_Summary (currently named “Station_Surface_Water”)
    • V_General_Station_Streamflow_Annual_Flow_Summary
    • We should discuss whether “Station” comes before or after “Streamflow” and “Climate”
  46. V_General_Surface_Water_Station_Spotflow
    • I think we should bring this back so that folks remember we have data and that they should add info to DB
  47. V_General_Documents
    • Similar to the Consultant View but has slightly more info - maybe we can discuss and fold into only one View - V_General_Documents_(Consultant)?
  48. V_General_Field
    • Does this view only contain data from the BHs (i.e. not metereological stations nor surface water stations?) - if yes maybe we change name to V_General_Field_Borehole? Then we would have V_General_Field_ 1) BH; 2) Meteorological; and 3) Stream Flow as well as the V_General_Field_Summary - which includes all three of above.
  49. V_General_Geology
    • We can probably look at adding a couple of fields to V_Consultant_Geology (maybe i) Ground Elev and ii) Bottom Elev (of the well - not the layer) and iii) Formation (Interpreted)) and merging the two views V_General_Geology_(Consultant) - Don’t know how many layers would have a populated Material 4 - but we may want to include it in the view so that partners etc. would know that it is available;
  50. V_General_Hydrogeology vs V_Consultant_Hydrogeology
    • Maybe combine to one view - there are a few fields in the General one that aren’t in the Consultant one that I would think we should add (i) Interpreted Formation (screen top); ii) Interpreted Formation (screen bottom); iii) First Measured WL (mASL); iv) Most recent measured WL (mASL); v) Drawdown During Pumptest (m); v) Completion date; vi) screen type)
  51. V_General_Outcrops
    • Maybe bring back- just so folks are reminded that they are in the DB - just found it with new name - don’t know if I like calling it Borehole?
  52. V_General_Locations
    • Similar to above - maybe we combine this with the “Consultant_General” View - re-name to V_General_Locations_(Consultant)
  53. V_General_Permits
    • We should probably develop a new View that works with the new PTTW table(s) AND COMBINE WITH Consultant view - re-name to V_General_PTTW_(Consultant)
  54. V_General_Pick
    • Let’s add the “s” back - “V_General_Picks”
  55. LOC_TYPE
    • Wonder if we should add the Loc_Type to the chemistry views - so that folks can readily screen out SW vs GW
  56. General_Chemistry
    • Maybe change the name of General_Chemistry to General_Chemistry_All
  57. Locs/Ints with Chemistry
    • How about a view (a group by) that pulls out the Locations/Intervals that have chemistry data associated with them? Maybe called V_Chemistry_Present? - I don’t think we need to necessarily pull the chemistry values - but maybe the number of “dates” that appear in Int1a/b - and the first/last date.
  58. Temporal_BHs_Discharge_Production
    • I would like to bring back the Temporal_BHs_Discharge_Production” view - should focus on the “Production - Pumped Volume (Total Daily)” Rd_Name Code.
  59. Viewlog views
    • For the Viewlog views (VL) - I think I would want to keep the names the same (especially for VL_Log_Header) since everyone’s Viewlog project would be looking for this view to show wells in cross section. We can talk specifically about these - I think the ones you omitted are ok to leave out for now.
  60. General_Temporal_Chemistry_General_Water_Soil_Rock
    • We might want to bring this one back - Waterfront Toronto is thinking of asking us to put in 15,000 BHs into the DB - all having soil chemistry results.

20150317-19

Reassigned INT_TYPE_CODEs ‘27’ (i.e. ‘Reported Open Hole - derived from bh construction’) to one of ‘21’ and ‘22’ based upon the presence of casing and a bedrock elevation. Started corrections of D_INTERVAL_MONITOR with invalid or duplicate rows for particular INT_IDs. Started corrections of D_GEOLOGY_LAYER with possibly invalid units.

20150320

Updated D_LOCATION_ELEV for new locations (~800).

20150407-08

With regard to the new database version: Reworked D_INTERVAL_FORMATION_ASSIGNMENT view(s); added the number of geologic layers to the summary tables; incorporated the SITEFX_SEARCHLOC view; added the V_GENERAL_WATER_LEVEL_OTHER view; added the V_GENERAL_HYDROGEOLOGY_BEDROCK view; added the V_GENERAL_WATER_FOUND view; updated various other views to reflect underlying changes.

20150410

Exchanged LOC_NAME and LOC_NAME_ALT1 for the climate stations. Added the spot flow view(s) to the new database version.

20150413

Modified V_CONSULTANT_HYDROGEOLOGY in new version of database. Add V_SUMMARY_CHEMISTRY_SAMPLES and V_GENERAL_FIELD_PUMPING_PRODUCTION. Examined duplicate INT_IDs and combined data to single INT_ID.

20150415+

Corrected D_GEOLOGY_LAYER based on duplicate ‘0’ depth values (and other errors).

20150420

Worked on PICKS table (trying to decrease access/update speed).

20150507-12

Incorporated UGAIS grainsize (with corrections) data previously included as part of the GEOL_DESCRIPTION within D_GEOLOGY_LAYER. Additional soil intervals were created (in some cases) in some instances(where they previously did not exist).

20150515+

Start of the water level (DIT2) corrections as part of a shallow water table calculation across the entire area. The first phase is the examination of those values that greatly exceed ‘20m’ depth (at which depth the shallow wells are arbitrarily defined). Various modifications are made including (for example): depths such as ‘203m’ for boreholes that are only ‘4m’ deep are assigned a value of ‘2.03m’ instead; units are converted from (some combination of) ‘ft’ to ‘m’ (or vice-versa) as appropriate based upon the depth of the well or depth of the screen.

Note that these modifications have/may affect borehole details especially with regard to depths and including modification of REF_ELEV from D_INTERVAL_REF_ELEV as appropriate.

There is a real issue with regard to multiple instances of elevations throughout the various tables (e.g. D_BOREHOLE, D_LOCATION_ELEV, D_INTERVAL_REF_ELEV, D_INTERVAL_MONITOR, and etc?). Mismatched elevations occur between these tables - an effort should be made to reduce these to single value with all others changed to depths (instead). This is an issue with SiteFX, however.

In addition, correction of the water levels themselves (as found in D_INTERVAL_TEMPORAL_2) has-been/is-being performed. This includes adjustment of ‘masl’ units to depth units (based upon, usually, the original elevation) - a similar problem, as described above, was found.

20150529

Evaluated R_BH_DRILLER_CODE to swap BH_DRILLER_DESCRIPTION and BH_DRILLER_DESCRIPTION_LONG; not possible as there are duplicates in the latter.

201506+

Worked with TRCA on Portlands data (extraction and addition of). Evaluated the presence of boreholes in the lake for this area (UGAIS wells; they were drilled through in the lake itself and is not an locational error). Determed that Toronto uses a modified NAD27 datum - modified Portlands locations based upon this.

20150623

Reset all Y/N fields in D_DOCUMENT; ‘N’ is changed to a NULL value and ‘Y’ changed to a ‘1’ value for consistency.

20150814

Change BH_BEDROCK_ELEV values to four decimal places. This should be standardized across all relevant (i.e. REAL types) columns (e.g. elevations and depths).

201509+

Major push to finish database structure (including views) for the updated master database release. Note that if replication is to be discontinued, the auto-increment (i.e. IDENT) columns can be reinstated (from the programming - maximum determination - currently imposed). Remember, IDENT fields are very finicky with regard to multiple users and replication.

20150904

R_GEOL_MA\T*_CODE out of sync (mainly MAT3 and MAT4). Corrected these and D_GEOLOGY_LAYER.

20150908

Applied a GEOL_SUBCLASS_CODE of ‘7’ (‘Original (Invalid)’) to those records in D_GEOL_LAYER where the GEOL_TOP_OUOM and GEOL_BOT_OUOM have NULL values. This is code (i.e. ‘7’) should be used to weed out invalid geology layers in the various views (as required; e.g. V_GENERAL_MOE_REPORT).

20150923

Corrected D_GEOLOGY_LAYER for those wells in the ‘Yonge St. Aquifer’ study. Mainly, these have zeroed elevation layers; in some cases, geology for a single layer is spread over multiple rows.

20151005

Incorporation of Permite-to-take-Water database (from 20141003) using the newly developed structure.

20151103

Changed water level data in D_INTERVAL_TEMPORAL_2 from 628/629 to 70899 when the RD_VALUE is NULL due to: frozen; dry; other … The RD_TYPE_CODE is adjusted to match as well.

201606-08

A tracking mechanism is implemented for both coordinates (D_LOCATION_COORD_HIST) and elevations (D_LOCATION_ELEV_HIST).

20160831

A new version of the database is created - this incorporates all changes as originally implemented in the OAK_20120615_VIEWS database. All views and tables are now found entirely in a single database.

20170115

Secondary version number change (now 20160831.20170115). This is tied to the update of the master database to match the current SiteFX schema base. Comment added to D_VERSION_CURRENT was ‘Matched to subset of SiteFX db v16082201’.

20170524

Secondary version number change (now 20160831.20170524). All meteorological data (i.e. from climate stations) have now been moved into the D_INTERVAL_TEMPORAL_3 table; updates to the various views are ongoing. Comment added to D_VERSION_CURRENT was ‘Climate data moved to DIT3’.

20210901

Secondary version number change (now 20160831.20210901). Includes the updates of D_INTERVAL_FORM_ASSIGN (i.e. T, K and specific capacity calculations), final switch to the use of D_LOCATION_SPATIAL and D_LOCATION_SPATIAL_HIST (bringing together D_LOCATION_COORD_HIST and D_LOCATION_ELEV_HIST which are now disparaged) and inclusion of the quality check tables (D_LOCAION_QC, D_LOGGER_QC and D_INTERVAL_QC_DATA).