Database Manual

Oak Ridges Moraine Groundwater Program

Section 2.3.8

Section 2.3.8 Permit To Take Water (PTTW)

The PTTW data (refer to Ministry of the Environment, Conservation and Parks (2022)) is provided as a single spreadsheet that requires manipulation before incorporation into the master database. With an absence of an overriding key (the PERMITNO provided can be present across multiple rows in the source data) multiple fields are examined and grouped in order to distinguish between disparate rows in the spreadsheet (refer to Appendix G.22 for details). Each of these, then, are given a specific location (i.e. a new LOC_ID) and a LOC_TYPE_CODE of ‘22’ (‘Permit To Take Water’).

The existing tables

are used to capture information on these locations. In addition, the tables

have been added to the master database (replacing the original D_PTTW* tables, re-evaluated as of 2014-10-03) to capture the PTTW information as well as make specific the relationships between the individual permits.

Refer to Appendix G.22 for details concerning the processing of the PTTW dataset for import into ORMGP database.

The individual fields within the source file are handled in the following manner:

PERMITNO (Permit number for water taking)

Mapped to D_PTTW.PTTW_PERMIT_NUMBER. Also stored in D_LOCATION.LOC_NAME.

CLIENTNAME (Name of client requesting a permit)

These names are included (or matched) in D_OWNER. The numeric code is then stored in D_PTTW.OWNER_ID.

PURPOSECAT (Major taking category)

These purposes are matched against those available in R_PURPOSE_PRIMARY_CODE (and added as necessary). The resulting value is stored in D_LOCATION_PURPOSE.PURPOSE_PRIMARY_CODE.

SPURPOSE (Specific purpose)

These purposes are matched against those available in R_PURPOSE_SECONDARY_CODE (and added as necessary). The resulting value is stored in D_LOCATION_PURPOSE.PURPOSE_SECONDARY_CODE.

In the case of ‘Municipal’, check PURPOSECAT to differentiate between ‘Exploration’, ‘Monitoring’ and ‘Supply’ (they will most likely be ‘Municipal Supply’).

EXPIRYDATE (Expiry date of permit)

Mapped to D_LOCATION.LOC_END_DATE as well as D_PTTW.PTTW_EXPIRYDATE. Any invalid dates (usually found as blank fields in the original source) are assigned the tag ‘1867-07-01’. Any invalid years (usually greater than ‘2050’) are assigned the year ‘1867’.

ISSUEDDATE (Date the permit was issued)

Mapped to D_LOCATION_LOC_START_DATE and D_PTTW.PTTW_ISSUED_DATE. Refer to EXPIRYDATE (above) regarding invalid dates.

RENEWDATE (Date that a legacy permit as renewed)

Not that legacy permits are those that were issued before 2005.

Mapped to D_PTTW.PTTW_RENEWDATE. In the source data, this may be present as a numeric rather than a datetime field. This should be converted before the field should be used.

This value seems to match P_MUNICIP (if declared) from the source data and is ignored.

P_LOT (Lot where permit is located)

This column contains information in a ‘free-form’ format; it can include LOT numbers, CONCESSION numbers and general addresses. As such, a straight numeric (or readily identified numeric) maps to D_LOCATION.LOC_LOT. The contents are also mapped to D_LOCATION.LOC_ADDRESS_INFO1 (unless a ‘NA’ value is present, which is ignored).

P_CON (Concession where permit is located)

The information contained here is similar to that found in P_LOT and is handled in a similar manner. A numeric will map to D_LOCATION.LOC_CON while the contents will be copied to D_LOCATION.LOC_ADDRESS_INFO2.

P_MUNICIP (Current municipality where permit is located)

This maps to D_LOCATION_LOC_TOWNSHIP_CODE (based upon the relationship in R_LOC_TOWNSHIP_CODE). This is also used to populate D_LOCATION.LOC_COUNTY_CODE.

P_UPPERT (Upper tier where permit is located)

This relates to county, district or regional municipality. This value is not stored.

P_LOWERT (Lower tier where permit is located)

Similar to P_UPPERT. This value is not stored.

SURFGRND (Origin of water taking)

This indicates the source of the water, one of surface, groundwater or both. This has been captured as a reference code (as found in R_PTTW_WATER_SOURCE_CODE) and mapped to D_PTTW.PTTW_WATER_SOURCE_CODE.

SOURCEID (Description of exact location of water taking)

These have been captured as a reference code, found in R_PTTW_SOURCEID_CODE, and mapped to D_PTTW.PTTW_SOURCEID_CODE. The original value is stored in D_PTTW.PTTW_SOURCEID_OUOM.

EASTING (Easting coordinates in UTM)

These values are used if LATITUDE and LONGITUDE is not defined. The assumed datum is NAD83. Coordinates are translated to longitude. If blank, a QA_COORD_CONFIDENCE_CODE of ‘117’ will be applied. When used, this is mapped to D_LOCATION.LOC_COORD_EASTING_OUOM.

NORTHING (Northing coordinates in UTM)

Refer to EASTING (above) for use. Coordinates are translated to latitude. Mapped (when used) to D_LOCATION.LOC_COORD_NORTHING_OUOM.

UTMZONE (UTM Zone - one of 15, 16, 17 or 18)

Only used if LATITUDE or LONGITUDE is not defined. If blank, zone 17 is assumed. Used in conjunction with EASTING and NORTHING to assign a latitude and longitude.

MAXL_DAY (Maximum litres allowed per day)

Mapped to D_PTTW.PTTW_MAX_L_DAY.

DAYS_YEAR (Number of days that water taking is allowed)

Mapped to D_PTTW.PTTW_MAX_DAYS_YEAR.

HRS_DAYMAX (Maximum water taking in hours per day)

Mapped to D_PTTW.PTTW_MAX_HRS_DAY.

L_MINUTE (Allowable taking per minute, in litres)

Mapped to D_PTTW.PTTW_MAX_L_MINUTE.

AMENDED_BY (Previous permit number)

Mapped to D_PTTW.PTTW_AMENDED_BY.

EXPIRED_BY (Previous permit number)

Mapped to D_PTTW.PTTW_EXPIRED_BY.

PERMIT_END (Date at which the water permit ends)

Refer to EXPIRYDATE (above) regarding invalid dates.

Mapped to D_PTTW.PTTW_EXPIRYDATE.

ACTIVE

This indicates whether the permit is active or expired (yes/no) based on PERMIT_END and the date that data was extracted from the source database.

Mapped to D_LOCATION.LOC_ATIVE (a 0/1 field).

Note that an automated process modifies this tag as necessary (refer to Appendix G.32 for details).

LATITUDE (The latitude coordinate)

Mapped to D_LOCATION.LOC_COORD_NORTHING_OUOM. Where this value is not defined, NORTHING is used instead. A NAD83 datum is assumed.

LONGITUDE (The longitude coordinate)

Mapped to D_LOCATION.LOC_COORD_EASTING_OUOM. Where this value is not defined, EASTING is used instead. A NAD83 datum is assumed.