View on GitHub

Oak Ridges Moraine Groundwater Program - Database Manual

ORMGP Database Manual

G.3 Report Library - Incorporation of Reports

A form (through Microsoft Access) is available so as to standardize the entry of new reports into a format consistent with the database. This information is stored, with appropriate fields, as a single table in an Access database; modification of this data is necessary before import is possible.

A temporary key (e.g. ‘rkey’) must be assigned to the table; this will be eventually replaced by a LOC_ID and DOC_ID during import; these fields may need to be added as well. DOC_FOLDER_ID should be checked for duplication. As fields within this single table can be found in each of D_DOCUMENT, D_LOCATION and D_LOCATION_QA it is duplicated and the unnecessary fields (from each table) are deleted.

For D_LOCATION:

For D_LOCATION_QA:

For D_DOCUMENT:

Each table is then imported into a temporary database in Microsoft SQL (e.g. ‘temphold’). All cells assigned a ‘-9999’ placeholder value are converted to NULL values.

Populate the LOC_ID field in D_LOCATION using the (example) script:

update [temphold].[dbo].[D_LOCATION_20220203]
set
LOC_ID=t3.LOC_ID
from 
[temphold].[dbo].[D_LOCATION_20220203] as t1
inner join
(
select
t2.LOC_ID
,ROW_NUMBER() over (order by t2.LOC_ID) as rkey
from
(
select
top 1000 
v.NEW_ID as LOC_ID
from 
[OAK_20160831_MASTER].dbo.V_SYS_RANDOM_ID_BULK_001 as v
where 
v.NEW_ID not in
(select LOC_ID from [OAK_20160831_MASTER].dbo.d_location)
) as t2
) as t3
on t1.rkey=t3.rkey

Populate the LOC_ID field in D_LOCATION_QA using the (example) script:

update [temphold].[dbo].[D_LOCATION_QA_20220203]
set
LOC_ID=dloc.LOC_ID
FROM [temphold].[dbo].[D_LOCATION_QA_20220203] as dlqa
inner join [temphold].dbo.[d_location_20220203] as dloc
on dlqa.rkey=dloc.rkey

Populate the LOC_ID field in D_DOCUMENT using the (example) script:

update [temphold].[dbo].[D_DOCUMENT_20220203]
set
LOC_ID=dloc.LOC_ID
FROM
[temphold].[dbo].[D_DOCUMENT_20220203] as ddoc
inner join [temphold].dbo.[D_LOCATION_20220203] as dloc
on ddoc.rkey=dloc.rkey

Populate the DOC_ID field in D_DOCUMENT using the (example) script:

update [temphold].[dbo].[D_DOCUMENT_20220203]
set
DOC_ID=t3.DOC_ID
from 
[temphold].[dbo].[D_DOCUMENT_20220203] as t1
inner join
(
select
t2.DOC_ID
,ROW_NUMBER() over (order by t2.DOC_ID) as rkey
from
(
select
top 1000 
v.NEW_ID as DOC_ID
from 
[OAK_20160831_MASTER].dbo.V_SYS_RANDOM_ID_BULK_001 as v
where 
v.NEW_ID not in
(select DOC_ID from [OAK_20160831_MASTER].dbo.d_document)
) as t2
) as t3
on t1.rkey=t3.rkey

Note the in each of the ‘update’ example scripts, the ‘top 1000’ should be modified to a value greater than the number of locations/documents being entered (in this case, ‘764’ documents were being added). This controls the number of random identifiers to return from ‘V_Random_ID_Creater_MD’.

Insert the completed (example) D_LOCATION_20220203 table into D_LOCATION (note that ‘rkey’ is not being inserted):

insert into [OAK_20160831_MASTER].dbo.D_LOCATION
(
[LOC_NAME]
,[LOC_NAME_ALT1]
,[LOC_COORD_EASTING_OUOM]
,[LOC_COORD_NORTHING_OUOM]
,[LOC_COORD_OUOM_CODE]
,[OWN_ID]
,[LOC_ID]
,[LOC_COORD_EASTING]
,[LOC_COORD_NORTHING]
,[SITE_ID]
,[LOC_STUDY]
)
SELECT 
[LOC_NAME]
,[LOC_NAME_ALT1]
,[LOC_COORD_EASTING_OUOM]
,[LOC_COORD_NORTHING_OUOM]
,[LOC_COORD_OUOM_CODE]
,[OWN_ID]
,[LOC_ID]
,[LOC_COORD_EASTING]
,[LOC_COORD_NORTHING]
,[SITE_ID]
,[LOC_STUDY]
FROM 
[temphold].[dbo].[D_LOCATION_20220203]

Insert the completed (example) D_LOCATION_QA_20220203 table into D_LOCATION_QA (note that the ‘rkey’ is not being inserted):

insert into [OAK_20160831_MASTER].dbo.D_LOCATION_QA
(
[LOC_ID]
,[QA_COORD_CONFIDENCE_CODE]
)
SELECT 
[LOC_ID]
,[QA_COORD_CONFIDENCE_CODE]
FROM 
[temphold].[dbo].[D_LOCATION_QA_20220203]

Insert the completed (example) D_DOCUMENT_20220203 table into D_DOCUMENT (note that the ‘rkey’ is not being inserted):

insert into [OAK_20160831_MASTER].dbo.D_DOCUMENT 
(
[DOC_FOLDER_ID]
,[DOC_AUTHOR]
,[DOC_YEAR]
,[DOC_MONTH]
,[DOC_DAY]
,[DOC_PAGE_RANGE]
,[DOC_AUTHOR_AGENCY_CODE]
,[DOC_TYPE_CODE]
,[DOC_FORMAT_CODE]
,[DOC_LOCATION_CODE]
,[DOC_DESCRIPTION]
,[DOC_TOPIC_CODE]
,[DOC_CLIENT_AGENCY_CODE]
,[DOC_YN_LOCATION_MAP]
,[DOC_YN_CROSS_SECTION]
,[DOC_YN_BH_LOG]
,[DOC_YN_GEOPHYSICS]
,[DOC_YN_PUMP_TEST]
,[DOC_YN_MODELLING]
,[DOC_YN_CHEMISTRY]
,[DOC_YN_WATERLEVEL]
,[DOC_YN_DIGITAL_DATA]
,[DOC_VOLUME_OTHER]
,[DOC_YN_DRAFT]
,[DOC_YN_PARTIAL]
,[LOC_ID]
,[DOC_ID]
)
SELECT 
[DOC_FOLDER_ID]
,[DOC_AUTHOR]
,[DOC_YEAR]
,[DOC_MONTH]
,[DOC_DAY]
,[DOC_PAGE_RANGE]
,[DOC_AUTHOR_AGENCY_CODE]
,[DOC_TYPE_CODE]
,[DOC_FORMAT_CODE]
,[DOC_LOCATION_CODE]
,[DOC_DESCRIPTION]
,[DOC_TOPIC_CODE]
,[DOC_CLIENT_AGENCY_CODE]
,[DOC_YN_LOCATION_MAP]
,[DOC_YN_CROSS_SECTION]
,[DOC_YN_BH_LOG]
,[DOC_YN_GEOPHYSICS]
,[DOC_YN_PUMP_TEST]
,[DOC_YN_MODELLING]
,[DOC_YN_CHEMISTRY]
,[DOC_YN_WATERLEVEL]
,[DOC_YN_DIGITAL_DATA]
,[DOC_VOLUME_OTHER]
,[DOC_YN_DRAFT]
,[DOC_YN_PARTIAL]
,[LOC_ID]
,[DOC_ID]
FROM 
[temphold].[dbo].[D_DOCUMENT_20220203]

These locations will automatically be added to D_LOCATION_GEOM on a weekly basis (refer to Appendix G.2 and G.32 for details).

The document records currently found in the ORMGP database (i.e. in D_DOCUMENT and associated) can be added to the Microsoft Access database (for reference by any Partner Agency) through V_SYS_DOC_REPLIB_ENTRY. This view assembles the requisite fields that match the format found in the report entry database.