View on GitHub

Oak Ridges Moraine Groundwater Program - Database Manual

ORMGP Database Manual

G.22 Incorporation of the MOE Permit-To-Take-Water database

Refer to Section 2.3.8 for details concerning the format and mapping of the PTTW data source format to that of the ORMGP database.

Here, we describe the process by which this information is incorporated. Note that almost all processing is accomplished through a separate relational database system outside of the MSSQL environment (except in certain cases, which will be noted).

G.22.0 Determine Those Records to be Examined (for the ORMGP Study Area)

Existing records (00a)

Extract all existing PTTW records that have previously been imported. These are written to a temporary database then incorporated in the PTTW project.

select
dloc.loc_id
,dp.pttw_permit_number
,dp.pttw_issueddate
into temphold.dbo.pttw_all_20210604
from 
oak_20160831_master.dbo.d_location as dloc
inner join oak_20160831_master.dbo.d_pttw as dp
on dloc.loc_id=dp.loc_id
where 
loc_type_code=22

Query: 00a_PTTW_All_src_code

Nullify fields in source database (00b)

Empty fields should be converted to a NULL (or nodata) value. There will be many fields affected, only a single example is provided.

update [Database_20210607]
set
[Expired_by]=null
where 
[Expired_by]=' '

Query: 00b_Nullify_Source

PTTW records within the ORMGP study area (00c)

Create geometries for reach record in the source data using the latitude and longitude fields (so as to avoid any UTM zone issues). Use these to delimit these records to the ORMGP study area (with a buffer). A spatial exercise, the code for doing so is not shown here.

Query: 00c_Coordinates_Study_Area

PTTW client names - Optional (00d)

In the case of the 20210607 PTTW database, some processing had been done with the 20210401 PTTW database. The client names and permit numbers in the latter were used to updated the former. This will generally not be a usual step in regular processing.

Query: 00d_PTTW_Clientname

G.22.1 Determine Those Records to be Incorporated

Current data (01a)

Create a query to pull the PTTW_PERMIT_NUMBER and PTTW_ISSUEDDATE fields from the existing data.

select 
[PTTW_PERMIT_NUMBER] as [NAME]
,[PTTW_ISSUEDDATE] as [PERMIT_ISSUED_DATE]
from 
[pttw_all_20210604]
group by 
[PTTW_PERMIT_NUMBER],[PTTW_ISSUEDDATE]

Query: 01a_QCMP_DB

New data (01b)

Create a query to pull the PTTW_PERMIT_NUMBER and PTTW_ISSUEDDATE fields for the new data.

select 
[PERMITNO]
,[ISSUEDDATE]
,1 as [present]
from 
[Database_20210607_ORMGP]
group by 
[PERMITNO],[ISSUEDDATE]

Query: 01b_QCMB_New

Comparison table (01c)

Create a new table that relates the new versus the current permit records using the queries as a source. Add a key field to this table.

select 
[01b_QCMB_New].*
into TCMP_DB_and_New_20210607
from 
[01b_QCMB_New]
inner join [01a_QCMP_DB]
on [01b_QCMB_New].[PERMITNO]=[01a_QCMP_DB].[NAME] and [01b_QCMB_New].[ISSUEDDATE]=[01a_QCMP_DB].[PERMIT_ISSUED_DATE]

Query:: 01c_QCMB_DB_and_New

Presently loaded (01d)

Add a present field to the new dataset and mark those (with a value of 1) that are already present in the current database.

update 
(
select
d.rkey
,d.present
,t.present as present_upd
from 
[Database_20210607_ORMGP] as d
inner join 
(
select 
[Database_20210607_ORMGP].[rkey]
,[TCMP_DB_and_New_20210607].[present]
from 
[Database_20210607_ORMGP]
inner join [TCMP_DB_and_New_20210607]
on [Database_20210607_ORMGP].[PERMITNO]=[TCMP_DB_and_New_20210607].[PERMITNO] 
and [Database_20210607_ORMGP].[ISSUEDDATE]=[TCMP_DB_and_New_20210607].[ISSUEDDATE]
) as t
on d.rkey=t.rkey
) as t2
set
present=present_upd

Query: 01d_QCMP_Present

Check if the ISSUEDDATE is null and compare those permit numbers to those currently in the database. Include them as appropriate. (This code is not shown.)

Query: 01e_QCMP_Null

G.22.2 Check for Duplicates

Find duplicates (02a)

Add a duplicate field to the new data table. Create a query to pull those records that can be considered duplicates based upon the examined fields.

select 
t.[PERMITNO]
,t.[rcount]
,t.rkey_min
,t.rkey_max
from 
(
select 
[PERMITNO]
,min([rkey]) as rkey_min
,max([rkey]) as rkey_max
,count(*) as rcount
from 
[Database_20210607_ORMGP]
where 
[present] is null
and [duplicate] is null
group by 
[PERMITNO]
,[CLIENTNAME]
,[PURPOSECAT]
,[SPURPOSE]
,[EXPIRYDATE]
,[ISSUEDDATE]
,[RENEWDATE]
,[OLDCTYTWN]
,[P_LOT]
,[P_CON]
,[P_MUNICIP]
,[P_UPPERT]
,[P_LOWERT]
,[SURFGRND]
,[SOURCEID]
,[EASTING]
,[NORTHING]
,[UTMZONE]
,[MAXL_DAY]
,[DAYS_YEAR]
,[HRS_DAYMAX]
,[L_MINUTE]
,[AMENDED_BY]
,[EXPIRED_BY]
,[ACTIVE]
,[PERMIT_END]
) as t
where 
t.rcount>1

Query: 02a_QDuplicates_Check

Mark duplicates (02b)

Mark those records (with the largest key value) as the duplicate. This may need to be run-though more than once. These records will not be included in the import process.

This will create a temporary table:

select
d.rkey
,q.rkey_max
into dup_to_mark
from 
[Database_20200519] as d
inner join [02a_QDuplicates_Check] as q
on d.rkey=q.rkey_max

Followed by the actual update:

update (
select
d.rkey
,d.permitno
,d.duplicate
from 
[Database_20200519] as d
inner join [dup_to_mark] as m
on d.rkey=m.rkey
) as t
set
duplicate=1

Query: 02b_QDuplicates_Mark

Subset new data (02c)

Create a subset of the new dataset that will be processed and imported into the current database. This includes those that:

Query: 02c_QSubset

G.22.3 Processing of New Data

G.22.3a Owner/Client Information

Copy D_OWNER (03aa)

Create a local copy of D_OWNER for ease of processing.

select
[OWN_ID], [OWN_NAME], [OWN_TYPE_CODE]
into d_owner_20210607
from 
[CL2K16-OAK_20160831_MASTER_mdsql]::[dbo.D_OWNER]

Query: 03aa_Get_D_OWNER

Match owner to client (03ab)

Check the owner information versus the client information in the new dataset. Record the results in a new table (add a new key and index the clientname to this new table).

select
d.rkey
,o.own_id
,o.own_name
into TMAT_match_own_id
from 
[Database_20210607_ORMGP_subset] as d
inner join [d_owner_20210607] as o
on d.[ClientName]=o.[OWN_NAME]

Query: 03ab_Get_OWN_ID

Check duplicate owners (03ac)

Check for any duplicates in the owners table (as we’re going to use this as a lookup table).

select
tmat.*
--into TMAT_match_own_id_dup
from 
[TMAT_match_own_id] as tmat
inner join 
(
select
t.rkey
from 
( select rkey, count(*) as rcount from [TMAT_match_own_id] group by rkey ) as t
where 
t.rcount>1
) as t2
on tmat.rkey=t2.rkey

Remove these duplicates.

delete from [TMAT_match_own_id]
where
mfd_id in
(
select
max( mfd_id ) as mfd_id
from 
[TMAT_match_own_id_dup] as dt
group by
rkey,own_id
)

Query: 03ac_Remove_OWN_ID

Include OWN_ID in new data table (03ad)

Add the OWN_ID into the new data table and populate it from the (just created) look-up table.

update (
select
d.rkey
,m.own_id as own_id_new
,d.own_id
from 
[Database_20210607_ORMGP_subset] as d
inner join [TMAT_match_own_id] as m
on d.rkey=m.rkey
) as t
set
own_id=own_id_new

Query: 03ad_Update_Match

Unmatched Owners/Clients (03ae)

Get the unmatched owners/clients and add these to a separate table.

select
--count(*)
[mfd_id]
,[rkey]
,cast( null as int32 ) as own_id_upd
,[ClientName]
into TDIST_own_id_20210607
from 
[Database_20210607_ORMGP_subset]
where 
own_id is null
and clientname is not null

Review these to see if any of these should be manually assigned.
Compile a list of the new owners to include in D_OWNER.

select
distinct([ClientName]) as own_name
,cast( null as int32 ) as own_id
into [TDIST_own_id_add_20210607]
from 
[TDIST_own_id_20210607]
where 
[own_id_upd] is null

Assemble those who are to be added to the D_OWNER table.

update TDIST_own_id_add_20210607
set
own_id=t2.own_id
from 
TDIST_own_id_add_20210607 as d
inner join
(
select
t.own_id
,row_number() over (order by t.own_id) as rkey
from 
(
select
top 150
v.new_id as own_id
from 
oak_20160831_master.dbo.v_sys_random_id_bulk_001 as v
where 
v.new_id not in
( select own_id from oak_20160831_master.dbo.d_owner )
) as t
) as t2
on d.mfd_id=t2.rkey

We’ll use a new DATA_Id for this PTTW import. Use this same DATA_ID to mark the new owners that are being added. Insert these into D_OWNER.

insert into oak_20160831_master.dbo.d_owner
( own_id, own_name, data_id )
select
own_id
,own_name
,240095125 as data_id
from 
TDIST_own_id_add_20210607

Get a new copy of the D_OWNER table to use for reference.

select
[OWN_ID], [OWN_NAME], [OWN_TYPE_CODE]
into d_owner_20210607b
from 
[CL2K16-OAK_20160831_MASTER_mdsql]::[dbo.D_OWNER]

Then assemble the OWN_ID values to update the new PTTW dataset.

select
t.mfd_id
,t.rkey
,t.own_id_upd
,t.clientname
,d.own_id
,d.own_name
into TDIST_own_id_upd_20210607
from 
[TDIST_own_id_20210607] as t
inner join [d_owner_20210607b] as d
on t.clientname= d.own_name
where 
t.own_id_upd is null

And, finally, update the OWN_ID field in the temporary table in the new dataset.

update
(
select
t1.mfd_id
,t1.own_id_upd
,t2.own_id
from 
[TDIST_own_id_20210607] as t1
inner join [TDIST_own_id_upd_20210607] as t2
on t1.mfd_id=t2.mfd_id
) as t
set
t.own_id_upd=t.own_id

Check especially those OWN_IDs not updated due to field truncation (in the text fields).

Query: 03ae_Unmatched_Owner

Update OWN_ID (03af)

Finally, update the OWN_ID in the table containing the subset of information to be loaded into the current database.

update (
select
d.mfd_id
,t.mfd_id
,d.own_id
,t.own_id_upd
from 
[Database_20210607_ORMGP_subset] as d
inner join [TDIST_own_id_20210607] as t
on d.[rkey]=t.[rkey]
where
d.own_id is null
) as t
set
own_id=own_id_upd

Query: 03af_Update_OWN_ID

G.22.3b Primary and Secondary Purposes

Get the primary purpose (03ba)

Determine the primary purpose codes for the records in the new dataset. Look for null values that need to be corrected.

select
t.pname
,rppc.[PURPOSE_PRIMARY_CODE]
,rppc.[PURPOSE_PRIMARY_DESCRIPTION]
from 
(
select
distinct([PURPOSECAT]) as pname
from 
[Database_20210607_ORMGP_subset]
) as t
left outer join [R_PURPOSE_PRIMARY_CODE] as rppc
on t.pname=rppc.[PURPOSE_PRIMARY_DESCRIPTION]

Query: 03ba_QDistinct_Purp_Prim

Get the secondary purpose codes for the records in the new dataset. Look for null values that need to be corrected.

select
t2.sname
,case
when purpose_secondary_code_1 is not null then purpose_secondary_code_1
else purpose_secondary_code_2
end as PURPOSE_SECONDARY_CODE
,case 
when purpose_secondary_code_1 is not null then purpose_secondary_description
else purpose_secondary_alt_code
end as PURPOSE_SECONDARY_DESCRIPTION
from 
(
select
t.sname
,rpsc1.[PURPOSE_SECONDARY_CODE] as PURPOSE_SECONDARY_CODE_1
,rpsc1.[PURPOSE_SECONDARY_DESCRIPTION]
,rpsc2.[PURPOSE_SECONDARY_CODE] as PURPOSE_SECONDARY_CODE_2
,rpsc2.[PURPOSE_SECONDARY_ALT_CODE]
from 
(
select
distinct([SPURPOSE]) as sname
from 
[Database_20210607_ORMGP_subset] as s
) as t
left outer join [R_PURPOSE_SECONDARY_CODE] as rpsc1
on t.sname=rpsc1.[PURPOSE_SECONDARY_DESCRIPTION]
left outer join [R_PURPOSE_SECONDARY_CODE] as rpsc2
on t.sname=rpsc2.[PURPOSE_SECONDARY_ALT_CODE]
) as t2

Query: 03bb_QDistinct_Purp_Sec

G.22.3c Water Source Code (Groundwater, etc…)

Get the water source code (03ca)

select
[mfd_id]
,case
when [SURFGRND] = 'Surface Water' then 2
when [SURFGRND] = 'Ground Water' then 1
when [SURFGRND] = 'Pumping Test' then 2
when [SURFGRND] = 'Surface and Ground Water' then 3
else null
end as PTTW_WATER_SOURCE_CODE
from 
[Database_20210607_ORMGP_subset]

Query: 03ca_QDistinct_Get_Source_Code

G.22.3d Water Source Identifer Code (Actual Source)

Get the actual source (e.g. well, brook, etc…) (03da)

Determine the actual source of the water by examination of the SOURCEID text field (not shown). Look for null values for correction.

Query: 03da_QDist_SourceID_Code_All

Unassigned sources (03db)

Examine the unassigned records and include in the 03da code for correction.

select
*
from 
[03da_QDist_SourceID_Code_All]
where
[PTTW_SOURCEID_CODE]=51
order by
[PTTW_SOURCEID_OUOM]

Query: 03db_QDist_SourceID_Code_Test

G.22.3e Municipality

Determine the municipality of the record (03ea)

Determine the municpality for each record. Note that this query calls another (which in turn calls a third).

select
q.[MUNIC]
,q.[P_MUNICIP]
,r.[LOC_TOWNSHIP_ABBR]
,r.[LOC_TOWNSHIP_CODE]
,r.[LOC_TOWNSHIP_DESCRIPTION]
,r.[LOC_COUNTY_CODE]
into TDIST_P_MUNICIP_Match_20210607
from 
[03ea_SRC_QDist_P_MUNICIP_Single] as q
inner join [R_LOC_TOWNSHIP_CODE-ALT] as r
on StringToLowerCase(q.[MUNIC])=StringToLowerCase(r.[LOC_TOWNSHIP_ABBR])
where 
[LOC_TOWNSHIP_ABBR] <> '-9999'

Query: 03ea_QDist_P_MUNICIP_Match

This finds the single municipality in the text field.

select
case
when StringRegexpFind([P_MUNICIP],'~','i')>-1 then StringSubstringLen([P_MUNICIP],0,StringRegexpFind([P_MUNICIP],'~','i'))
when StringRegexpFind([P_MUNICIP],',','i')>-1 then StringSubstringLen([P_MUNICIP],0,StringRegexpFind([P_MUNICIP],',','i'))
--when StringRegexpFind([P_MUNICIP],'\s+\d','i')>-1 then StringSubstringLen([P_MUNICIP],0,StringRegexpFind([P_MUNICIP],'\s+\d','i'))
else [P_MUNICIP]
end as MUNIC
,[P_MUNICIP]
from 
[03ea_SRC_QDist_P_MUNICIP_Distinct]

Query: 03ea_SRC_QDist_P_MUNICIP_Single

While this finds the distinct records based upon the text field.

select
distinct([P_MUNICIP]) as P_MUNICIP
from 
[Database_20210607_ORMGP_subset]

Query: 03ea_SRC_QDist_P_MUNICIP_Distinct

G.22.4 Permits Ammended-By and Expired-By

Note that this is a query only. Appendix G.22.99b uses this as a source.

Assemble the related (and inverse related) permits (04a)

This query calls source queries to determine those permits that are related through their amended-by or expired-by fields. Note that the full new dataset is used for this comparison as previously loaded permit records may be affected.

select
mfd_id
,mfd_id_orig
,permitno
,permitno_related
,cast( null as int32 ) as inverse_related
from 
[04a_SRC_QRelated_Amended_Expired]
union
select
mfd_id
,mfd_id_orig
,permitno
,permitno_related
,cast( 1 as int32 ) as inverse_related
from 
[04a_SRC_QRelated_Amended_Expired_Inv]

Query: 04a_QReleated_All

This is the source query for amended and expired records (for the main 04a query).

select
t.*
from 
(
select
mfd_id
,mfd_id_orig
,[PERMITNO]
,[AMENDED_BY] as PERMITNO_RELATED
from 
[04a_SRC_QRelated_Amended_By_Inv]
union
select
mfd_id
,mfd_id_orig
,[PERMITNO]
,[EXPIRED_BY] as PERMITNO_RELATED
from 
[04a_SRC_QRelated_Expired_By_Inv]
) as t
group by
t.mfd_id,t.mfd_id_orig,t.[PERMITNO],t.PERMITNO_RELATED

Query: 04a_SRC_QRelated_Amended_Expired

This calls

select
q.mfd_id
,d.mfd_id as mfd_id_orig
,q.[PERMITNO]
,q.[AMENDED_BY]
from 
[04a_SRC_QRelated_Amended_By] as q
inner join [Database_20210607_ORMGP_subset] as d
on q.[AMENDED_BY]=d.[PERMITNO]

Query: 04a_SRC_QRelated_Amended_By_Inv

and

select
q.mfd_id
,d.mfd_id as mfd_id_orig
,q.[PERMITNO]
,q.[EXPIRED_BY]
from 
[04a_SRC_QRelated_Expired_By] as q
inner join [Database_20210607_ORMGP_subset] as d
on q.[EXPIRED_BY]=d.[PERMITNO]

Query: 04a_SRC_QRelated_Expired_By_Inv

This is the inverse related amended and expired records (for the main 04a query).

select
q.mfd_id_orig as mfd_id
,q.mfd_id as mfd_id_orig
,q.permitno_related as PERMITNO
,q.[PERMITNO] as PERMITNO_RELATED
from 
[04a_SRC_QRelated_Amended_Expired] as q
group by
q.mfd_id_orig,q.mfd_id,q.permitno_related,q.[PERMITNO]

Query: 04a_SRC_QRelated_Amended_Expired_Inv

This calls

select
t.*
from 
(
select
mfd_id
,mfd_id_orig
,[PERMITNO]
,[AMENDED_BY] as PERMITNO_RELATED
from 
[04a_SRC_QRelated_Amended_By_Inv]
union
select
mfd_id
,mfd_id_orig
,[PERMITNO]
,[EXPIRED_BY] as PERMITNO_RELATED
from 
[04a_SRC_QRelated_Expired_By_Inv]
) as t
group by
t.mfd_id,t.mfd_id_orig,t.[PERMITNO],t.PERMITNO_RELATED

Query: 04a_SRC_QRelated_Amended_Expired

which in turn calls

select
q.mfd_id
,d.mfd_id as mfd_id_orig
,q.[PERMITNO]
,q.[AMENDED_BY]
from 
[04a_SRC_QRelated_Amended_By] as q
inner join [Database_20210607_ORMGP_subset] as d
on q.[AMENDED_BY]=d.[PERMITNO]

Query: 04a_SRC_QRelated_Amended_By_Inv

and

select
q.mfd_id
,d.mfd_id as mfd_id_orig
,q.[PERMITNO]
,q.[EXPIRED_BY]
from 
[04a_SRC_QRelated_Expired_By] as q
inner join [Database_20210607_ORMGP_subset] as d
on q.[EXPIRED_BY]=d.[PERMITNO]

Query: 04a_SRC_QRelated_Expired_By_Inv

Note that the following queries are used as sources as well.

select
d.mfd_id
,d.[PERMITNO]
,d.[AMENDED_BY]
from 
[Database_20210607_ORMGP_subset] as d
where 
StringLength(d.[AMENDED_BY])>0

Query: 04a_SRC_QRelated_Amended_By

select
[mfd_id]
,[PERMITNO]
,[EXPIRED_BY]
from 
[Database_20210607_ORMGP_subset]
where 
StringLength([EXPIRED_BY])>0

Query: 04a_SRC_QRelated_Expired_By

G.22.99a Creation of Import Tables

This section numeric has been assigned 99 as it is the last series of steps to be undertaken. Other possible processing can be fitted in before this section without having to re-number subsequent sections.

Create D_LOCATION (99aa)

We’ll need to create the X and Y coordinates (projection UTM Zone 17, NAD83) and include them in the source table (not shown). In addition, a DATA_ID needs to be added to the D_DATA_SOURCE table and included for each new location record (as well as the additions to the D_OWNER table and PTTW related tables).

An example is shown.

insert into d_data_source
( data_id, data_type, data_description, data_filename )
values 
( 508, 'PTTW', 'Permit To Take Water - Update 20210607', 'PermitsToTakeWater.xls (20210607); pttw_manipulation_m9_20210607.map' )

The new PTTW locations can then be created.

select 
cast(null as int32) as LOC_ID
,d.mfd_id
,cast([PERMITNO] as varchar) as LOC_NAME
,cast(22 as INT32) as LOC_TYPE_CODE
,d.x as LOC_COORD_EASTING
,d.y as LOC_COORD_NORTHING
,cast([P_LOT] as varchar) as LOC_CON
,cast([P_CON] as varchar) as LOC_LOT
,qmun.[LOC_COUNTY_CODE]
,qmun.[LOC_TOWNSHIP_CODE]
,[ACTIVE] as LOC_ACTIVE
,case
when [ISSUEDDATE] = cast('1867-07-01' as datetime) then NULL
else [ISSUEDDATE] 
end as LOC_START_DATE
,case
when [EXPIRYDATE] = cast('1867-07-01' as datetime) then NULL
else [EXPIRYDATE] 
end as LOC_END_DATE
,d.OWN_ID
,cast(508 as INT32) as DATA_ID
,cast(d.[P_MUNICIP] as varchar) as LOC_ADDRESS_INFO2
,cast(d.[P_UPPERT] as VARCHAR) as LOC_ADDRESS_INFO3
,cast(d.[P_LOWERT] as varchar) as LOC_ADDRESS_INFO4
into PTTW_20210607_D_LOCATION
from 
[Database_20210607_ORMGP_subset] as d
--***** This introduces duplicates; erase them
left outer join [TDist_P_MUNICIP_Match] as qmun
on d.[P_MUNICIP]=qmun.[P_MUNICIP]

Query: 99a_QCreate_D_LOCATION

Create D_LOCATION_PURPOSE (99ab)

The primary and secondary purposes can be assembled into D_LOCATION_PURPOSE.

select
cast(null as int32) as LOC_ID
,mfd_id
,[PURPOSE_PRIMARY_CODE]
,[PURPOSE_SECONDARY_CODE]
into PTTW_20210607_D_LOCATION_PURPOSE
from 
[Database_20210607_ORMGP_subset] as d
left outer join [03ba_QDistinct_Purp_Prim] as pp
on d.[PURPOSECAT]=[pname]
left outer join [03bb_QDistinct_Purp_Sec] as ps
on d.[SPURPOSE]=[sname]

Query: 99b_QCreate_D_LOCATION_PURPOSE

Create D_LOCATION_QA (99ac)

Make sure that each location has valid coordinates. Assign a 5 if so, a 117 if not.

select 
cast(null as int32) as [LOC_ID]
,mfd_id
,case
when d.x is null or d.y is null then 117
else 5
end as [QA_COORD_CONFIDENCE_CODE]
into PTTW_20210607_D_LOCATION_QA
from
[Database_20210607_ORMGP_subset] as d

Query: 99c_QCreate_D_LOCATION_QA

Create D_PTTW (99ad)

This is the master table for the information particular to a PTTW location. Note that in some cases, the RenewDate and Permit_End date columns are numeric rather than datetime values. This needs to be corrected before import.

select
cast(null as int32) as LOC_ID
,d.mfd_id
,cast([PERMITNO] as varchar) as PTTW_PERMIT_NUMBER
,[PTTW_SOURCEID_CODE]
,cast( [PTTW_SOURCEID_OUOM] as varchar ) as PTTW_SOURCEID_OUOM
,qsc.[PTTW_WATER_SOURCE_CODE]
,cast( [AMENDED_BY] as varchar ) as PTTW_AMENDED_BY
,cast( [EXPIRED_BY] as varchar ) as PTTW_EXPIRED_BY
,d.[OWN_ID]
,[ISSUEDDATE] as PTTW_ISSUEDDATE
,case
when [RENEWDATE_upd] is not null then NULL
else [PERMIT_END_upd] 
end as PTTW_PERMIT_END
,[EXPIRYDATE] as PTTW_EXPIRYDATE
,[RENEWDATE_upd] as PTTW_RENEWDATE
,[MAXL_DAY] as PTTW_MAX_L_DAY
,[DAYS_YEAR] as PTTW_MAX_DAYS_YEAR
,[HRS_DAYMAX] as PTTW_MAX_HRS_DAY
,[L_MINUTE] as PTTW_MAX_L_MINUTE
into PTTW_20210607_D_PTTW
from 
[Database_20210607_ORMGP_subset] as d
left outer join [03da_QDist_SourceID_Code_All] as tsrcid
on d.[mfd_id]=tsrcid.[mfd_id]
left outer join [03ca_QDistinct_Get_Source_Code] as qsc
on d.[mfd_id]=qsc.[mfd_id]

Query: 99d_QCreate_D_PTTW

Create D_PTTW_RELATED (99ae)

This creates the base PTTW related table using the keys from the source instead of the actual permit numbers. Note that this is will not be the relation between source locations (e.g. a well) and a PTTW record.

select
q.mfd_id as mfd_id_main
,q.mfd_id_orig
,cast(null as int32) as LOC_ID
,cast(null as int32) as LOC_ID_RELATED
,q.INVERSE_RELATED
,cast(null as int32) as SYS_RECORD_ID
into TREL_assemble_20210607
from 
[04a_QRelated_All] as q

Query: 99e_QCreate_D_PTTW_RELATED

G.22.99b Creation of PTTW Relation Tables

Create temporary relations (99ba)

This assembles the related records into a temporary table to be used for comparison.

select
p.mfd_id
,dm.permitno
,dm.amended_by
,dm.expired_by
,dr.permitno as permitno_dr
,dr.amended_by as amended_by_dr
,dr.expired_by as expired_by_dr
into TREL_cmp_20210607
from 
[TREL_assemble_20210607] as p
inner join [Database_20210607_ORMGP_subset] as dm
on p.mfd_id_main=dm.mfd_id
inner join [Database_20210607_ORMGP_subset] as dr
on p.mfd_id_orig=dr.mfd_id

Query: 99ba_QRelated_CMP_Create

Assemble D_PTTW_RELATED format (99bb)

This temporary table is then used to create the format of the D_PTTW_RELATED table using this query.

-- permitno and amended_by
select
t.permitno as permit
,t.amended_by as permit_rel
,cast(null as int32) as inv
from 
[TREL_cmp_20210607] as t
where 
t.amended_by is not null
union
-- permitno and amended_by inv
select
t.amended_by as permit
,t.permitno as permit_rel
,cast(1 as int32) as inv
from 
[TREL_cmp_20210607] as t
where
t.amended_by is not null
union
-- permitno and expired_by
select
t.permitno as permit
,t.expired_by as permit_rel
,cast(null as int32) as inv
from 
[TREL_cmp_20210607] as t
where 
t.expired_by is not null
union
-- permitno and expired_by inv
select
t.expired_by as permit
,t.permitno as permit_rel
,cast(1 as int32) as inv
from 
[TREL_cmp_20210607] as t
where 
t.expired_by is not null
union
-- permitno and permitno_dr
select
t.permitno as permit
,t.permitno_dr as permit_rel
,cast(null as int32) as inv
from 
[TREL_cmp_20210607] as t
where 
t.permitno_dr is not null
union
-- permitno and permitno_dr inv
select
t.permitno_dr as permit
,t.permitno as permit_rel
,cast(1 as int32) as inv
from 
[TREL_cmp_20210607] as t
where 
t.permitno_dr is not null
union 
-- permitno and amended_by_dr
select
t.permitno as permit
,t.amended_by_dr as permit_rel
,cast(null as int32) as inv
from 
[TREL_cmp_20210607] as t
where 
t.amended_by_dr is not null
union
-- permitno and amended_by_dr inv
select
t.amended_by_dr as permit
,t.permitno as permit_rel
,cast(1 as int32) as inv
from 
[TREL_cmp_20210607] as t
where 
t.amended_by_dr is not null
union
-- permitno and expired_by_dr
select
t.permitno as permit
,t.expired_by_dr as permit_rel
,cast(null as int32) as inv
from 
[TREL_cmp_20210607] as t
where 
t.expired_by_dr is not null
union
-- permitno and expired_by_dr inv
select
t.expired_by_dr as permit
,t.permitno as permit_rel
,cast(1 as int32) as inv
from 
[TREL_cmp_20210607] as t
where 
t.expired_by_dr is not null

Query: 99bb_QRelated_CMP

Create the D_PTTW_RELATED table (99bc)

Using 99bb as source, creates the D_PTTW_RELATED table.

select
*
into PTTW_20210607_D_PTTW_RELATED_all
from 
[99bb_QRelated_CMP]

Query: 99bc_QRelated_CMP_Out

G.22.99c Incorporate in Master Database

Import tables (99ca)

Incorporate the tables into the master db; note that we’re assigned the new loc_id to the loc_master_loc_id for now (not shown). This includes population of all requisite keys in comparison with the particular master db tables.

Query: 99ca_QImport

Link to well locations (99cb)

The PTTW_SOURCE_ID is examined for WWR or A-tag numbers that can be used to link the PTTW record to a MOE borehole record (not shown). The LOC_MASTER_LOC_ID field of the PTTW location is updated to point to this source location. This is manually intensive.

Query: 99cb_QLink_Source_ID

Link using a buffer (99cc)

The spatial location of the PTTW is used (with a specified buffer) in comparison with point locations in the master database (not shown). These, after examination, can be used as a link. The LOC_MASTER_LOC_ID for the PTTW record is updated for this purpose.

Query: 99cc_QLink_Buffer

This new table was implemented to capture the link between PTTW locations and their source locations to supplement (and eventually replace) the use of the LOC_MASTER_LOC_ID for the PTTW record (not shown). In some cases, a single permit can apply to multiple locations - this table allows us to capture these records.