Appendix G.3
G.3 Report Library - Incorporation of Reports
- Tables
- D_DOCUMENT
- D_LOCATION
- D_LOCATION_QA
- Views
- V_SYS_DOC_REPLIB_ENTRY
- Estimated Recurrence Time: As necessary
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:
- Add each of the LOC_COORD_EASTING and LOC_COORD_NORTHING and copy the values (performing a conversion as necessary) from LOC_COORD_EATING_OUOM and LOC_COORD_NORTHING_OUOM.
- Change DOC_FOLDER_ID to LOC_NAME.
- Add a SITE_ID column and populate with a value of ‘1’.
- All text fields should be ANSI rather than UNICODE.
- Add a LOC_TYPE_CODE column and populate it with a value of ‘25’ (i.e. ‘Document’).
- All cells that will, eventually, be NULL are assigned the value ‘-9999’ to distinguish them from valid data.
- Each column is examined for invalid or empty cells. Each column should be sorted in ascending and descending order to make note, as necessary, that the column contains ‘some’ valid data.
For D_LOCATION_QA:
- Add the QA_COORD_CONFIDENCE_CODE field. By default, unless a spatial uncertainty value is stated within the report, a value of ‘5’ (i.e. ‘Margin of Error: 100m-300m’) is assigned to QA_COORD_CONFIDENCE_CODE. Those locations without coordinates are assigned a value of ‘117’ (i.e. ‘Invalid ?’) instead.
For D_DOCUMENT:
- It is assumed that for each ‘_CODE’ field, all necessary ‘codes’ are available (or have been entered) into the appropriate ‘R_DOC_’ table.
- All cells that will, eventually, be NULL are assigned the value ‘-9999’ to distinguish them from valid data.
- Each column is examined for invalid or empty cells. Each column should be sorted in ascending and descending order to make note, as necessary, that the column contains ‘some’ valid data.
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.