Data Field Mapping

[nathankota]

sites table

App Field Name	App DB Field	UIC DB Table.Field	Notes
Site Name	name	UICFacility.FacilityName
Site ID	site_id	UICFacility.FacilityID
Land ownership at site	ownership	UICAuthorization.OwnerSectorType	ownership is a two letter string
6-digit NAICS code	naics_primary	UICFacility.NAICSPrimary	6 digit integer
Corresponding NAICS title	naics_title	UICFacility.NAICSSecondary	string
Street Address	address	UICFacility.FacilityAddress	address field needs to be split
City or Zip code	N/A	UICFacility.Facility City	City and zip code are independent fields within the UIC GDB
City or Zip code	N/A	UICFacility.FacilityZip	City and zip code are independent fields within the UIC GDB
Site Polygon	geometry	UICFacility.Shape

contacts table

App Field Name	App DB Field	UIC DB Table.Field	Notes
First name	first_name	UICContact.ContactName	Contact first and last name within a single field (i.e., ContactName) within the UIC GDB
Last name	last_name	UICContact.ContactName	Contact first and last name within a single field (i.e., ContactName) within the UIC GDB
Email address	email	UICContact.ContactEmail
Phone number	phone	UICContact.ContactPhone	format for phone number: 801.555.5555. we are collecting in the +12223334444 format
Organization	organization	UICContact.ContactOrganization
Contact type	contact_type	UICContact.ContactType	this is a lowercase string
Street address	mailing_address	UICContact.ContactMailAddress
City	city	UICContact.ContactMailCity
State	state	UICContact.ContactMailState
ZIP	zip_code	UICContact.ZipCode5

wells table

App Field Name	App DB Field	UIC DB Table.Field	Notes
Well Subclass	sub_class	UICWell.WellSubClass	4 digit integer. also collected on inventory but that is generalized
Well Construction/Name	well_name	UICWell.WellName
Status	status	UICWellOperatingStatus.OperatingStatusType	two character string
Remediation project id	remediation_project_id	UICWell.RemediationProjectID
Remediation type	remediation_type	UICWell.RemediationProjectType
Quantity	quantity	UICWell.Comments	If well quantity is >1, the quantity count is added to the "Comments" field of the UICWell feature class. For example, in the well quantity was 4, there would be a comment like: "Well represents 4 injection points."
Well location	geometry	UICWell.Shape

unused well fields

1. description
2. remediation_description
3. injectate_characterization
4. hydrogeologic_characterization

inventories table

App Field Name	App DB Field	UIC DB Table.Field	Notes
Inventory order number	order_number	UICAuthorization.OrderNumber
Effective date of submission:	submitted_on	UICWellOperatingStatus.OperatingStatusDate	Date the form was signed/submitted
Effective date of submission:	submitted_on	UICAuthorization.StartDate	Date the form was signed/submitted
Ground Water	surface_water_protection	UICWell.WellSWPZ	Y, S, N, depending on well intersect w/GWZ and ARDA
Edocs #	edocs	UICAuthorization.EDocsNumber

unused inventory fields

1. created_date
2. sub_class
3. signature

water_system_contacts table

App Field Name	App DB Field	UIC DB Table.Field	Notes
Water System Information Name	system	UICWellSWPZ.SystemName
Water System Information Contact	name	UICWellSWPZ.ContactName
Water System Information Email	email	UICWellSWPZ.ContactEmail

No Table

always insert these values

App Field Name	App DB Field	UIC DB Table.Field	Notes
		UICWell.WellClass	always Class V from 'UICWellClassDomain'
		UICWell.HighPriority	always "No" from UICYesNoUnknownDomain
		UICAuthorization.AuthorizationType	always Authorization by Rule, RA from UICAuthoriationTypeDomain
		UICAuthorizationAction.AuthorizationActionType	always Permit not Required, NR from UICAuthorizationAction

[nathankota]

@rsparker-utah use the "Data Map" tables throughout the Miro as a reference to complete the table in the initial comment of this issue.

[rsparker-utah]

@nathankota I had started to put together a "UIC Inventory Web App Fields To UIC GDB Fields Crosswalk" spreadsheet (located in the "DEQ-Water Quality and UGRC" shared drive) to help with brainstorming the field mapping between the web application and the UIC geodatabase. If this spreadsheet is too busy, I'd be happy to condense it into the format of the table in the initial comment of this issue.

[steveoh]

My suggestion would be to migrate that here and I can then edit it to match the database names and not only the labels in the website.

[rsparker-utah]

I migrated the fields from my "UIC Inventory Web App Fields To UIC GDB Fields Crosswalk" spreadsheet to match the format of the table in the initial comment. A few notes about this table:

• I omitted UIC GDB fields from my spreadsheet that do not have a corresponding web app field (e.g., GlobalID, GUID, foreign keys, etc.).
• I tried to organize the table based on when the fields are encountered within the web app.
• My understanding on how the shape type fields from the app behave is limited, so I made a few assumptions that the shape fields (e.g., UICFacility.Shape, UICFacility.Shape.STArea, and UICFacility.Shape.STLength, UICWell.Shape) could be derived from the Interactive Map on the Site Location page within the app.

App Page	App Field Name	App DB Field	UIC DB Table.Field	Notes
Site Details	Site Name		UICFacility.FacilityName
Site Details	Land ownership at site		UICAuthorization.OwnerSectorType
Site Details	6-digit NAICS code		UICFacility.NAICSPrimary
Site Details	Corresponding NAICS title		UICFacility.NAICSSecondary
Site Contacts	First name		UICContact.ContactName	Contact first and last name within a single field (i.e., ContactName) within the UIC GDB
Site Contacts	Last name		UICContact.ContactName	Contact first and last name within a single field (i.e., ContactName) within the UIC GDB
Site Contacts	Email address		UICContact.ContactEmail
Site Contacts	Phone number		UICContact.ContactPhone	format for phone number: 801.555.5555
Site Contacts	Organization		UICContact.ContactOrganization
Site Contacts	Contact type		UICContact.ContactType
Site Contacts	Street address		UICContact.ContactMailAddress
Site Contacts	City		UICContact.ContactMailCity
Site Contacts	State		UICContact.ContactMailState
Site Contacts	ZIP		UICContact.ZipCode5
Site Location	Street Address		UICFacility.FacilityAddress
Site Location	City or Zip code		UICFacility.Facility City	City and zip code are independent fields within the UIC GDB
Site Location	City or Zip code		UICFacility.FacilityZip	City and zip code are independent fields within the UIC GDB
Site Location	Interactive Map within App		UICFacility.Shape
Site Location	Interactive Map within App		UICFacility.Shape.STArea{)	Specified using interactive map within web app
Site Location	Interactive Map within App		UICFacility.Shape.STLength{)	Specified using interactive map within web app
Site Location or Site Details Page (tbd)	Site ID		UICFacility.FacilityID	Site ID field in web app TBD, see: https://github.com/uic-utah/uic-inventory/issues/259
Well Inventory	Well Subclass		UICWell.WellSubClass
Well Inventory	UIC inventory form order number		UICAuthorization.OrderNumber
Well Location	Well Construction/Name		UICWell.WellName
Well Location	Status		UICWellOperatingStatus.OperatingStatusType
Well Location	Remediation project id		UICWell.RemediationProjectID
Well Location	Remediation type		UICWell.RemediationProjectType
Well Location	Quantity		UICWell.Comments	If well quantity is >1, the quantity count is added to the "Comments" field of the UICWell feature class. For example, in the well quantity was 4, there would be a comment like: "Well represents 4 injection points."
Well Location	Well location		UICWell.Shape
Add Well Details	Construction details		N/A	Not captured by UIC GDB
Add Well Details	Injectate characterization		N/A	Not captured by UIC GDB
Add Well Details	Hydrogeologic characterization		N/A	Not captured by UIC GDB
Sign and Submit Inventory	Effective date of submission:		UICWellOperatingStatus.OperatingStatusDate	Date the form was signed/submitted
Sign and Submit Inventory	Effective date of submission:		UICAuthorization.StartDate	Date the form was signed/submitted
Inventory Review	Ground Water		UICWell.WellSWPZ
Inventory Review	Edocs #		UICAuthorization.EDocsNumber
Inventory Review	Water System Information Name		UICWellSWPZ.SystemName
Inventory Review	Water System Information Contact		UICWellSWPZ.ContactName
Inventory Review	Water System Information Email		UICWellSWPZ.ContactEmail

[steveoh]

here's my view of that.

sites table

App Field Name	App DB Field	UIC DB Table.Field	Notes
Site Name	name	UICFacility.FacilityName
Site ID	site_id	UICFacility.FacilityID
Land ownership at site	ownership	UICAuthorization.OwnerSectorType	ownership is a two letter string
6-digit NAICS code	naics_primary	UICFacility.NAICSPrimary	6 digit integer
Corresponding NAICS title	naics_title	UICFacility.NAICSSecondary	string
Street Address	address	UICFacility.FacilityAddress	address field needs to be split
City or Zip code	N/A	UICFacility.Facility City	City and zip code are independent fields within the UIC GDB
City or Zip code	N/A	UICFacility.FacilityZip	City and zip code are independent fields within the UIC GDB
Site Polygon	geometry	UICFacility.Shape

contacts table

App Field Name	App DB Field	UIC DB Table.Field	Notes
First name	first_name	UICContact.ContactName	Contact first and last name within a single field (i.e., ContactName) within the UIC GDB
Last name	last_name	UICContact.ContactName	Contact first and last name within a single field (i.e., ContactName) within the UIC GDB
Email address	email	UICContact.ContactEmail
Phone number	phone	UICContact.ContactPhone	format for phone number: 801.555.5555. we are collecting in the +12223334444 format
Organization	organization	UICContact.ContactOrganization
Contact type	contact_type	UICContact.ContactType	this is a lowercase string
Street address	mailing_address	UICContact.ContactMailAddress
City	city	UICContact.ContactMailCity
State	state	UICContact.ContactMailState
ZIP	zip_code	UICContact.ZipCode5

wells table

App Field Name	App DB Field	UIC DB Table.Field	Notes
Well Subclass	sub_class	UICWell.WellSubClass	4 digit integer. also collected on inventory but that is generalized
Well Construction/Name	well_name	UICWell.WellName
Status	status	UICWellOperatingStatus.OperatingStatusType	two character string
Remediation project id	remediation_project_id	UICWell.RemediationProjectID
Remediation type	remediation_type	UICWell.RemediationProjectType
Quantity	quantity	UICWell.Comments	If well quantity is >1, the quantity count is added to the "Comments" field of the UICWell feature class. For example, in the well quantity was 4, there would be a comment like: "Well represents 4 injection points."
Well location	geometry	UICWell.Shape

unused well fields

1. description
2. remediation_description
3. injectate_characterization
4. hydrogeologic_characterization
5. surface_water_protection

inventories table

App Field Name	App DB Field	UIC DB Table.Field	Notes
Inventory order number	order_number	UICAuthorization.OrderNumber
Effective date of submission:	submitted_on	UICWellOperatingStatus.OperatingStatusDate	Date the form was signed/submitted
Effective date of submission:	submitted_on	UICAuthorization.StartDate	Date the form was signed/submitted
Ground Water	?	UICWell.WellSWPZ	what is this?
Edocs #	edocs	UICAuthorization.EDocsNumber

unused inventory fields

1. created_date
2. sub_class
3. signature

water_system_contacts table

App Field Name	App DB Field	UIC DB Table.Field	Notes
Water System Information Name	system	UICWellSWPZ.SystemName
Water System Information Contact	name	UICWellSWPZ.ContactName
Water System Information Email	email	UICWellSWPZ.ContactEmail

[nathankota]

@steveoh 's table view moved up as the initial comment for this issue

[nathankota]

@rsparker-utah, before we close this issue as complete, please confirm that the following fields will not be transferred to the UIC database. I thought you were going to create a new table for the well description, remediation, injectate, etc.??

unused well fields

1. description
2. remediation_description
3. injectate_characterization
4. hydrogeologic_characterization
5. surface_water_protection

unused inventory fields

1. created_date
2. sub_class
3. signature

[rsparker-utah]

@nathankota Here is my feedback for the following:

Ground Water Field Question

In regards to the question about the Ground Water field in the inventories table above: This is referring to the Ground Water field characterizations (i.e., GWZ and/or ARDA) under the "Location Details" section on the Inventory Review page:

The corresponding UIC geodatabase values are then derived from the aforementioned GWZ and/or ARDA Ground Water field characterizations based on domain values from the UICGWProtectionDomain that are to be added to the UICWell.WellSWPZ field in the UICWell table in accordance with the following:

• If GWZ = Yes and ARDA = Yes, the UICWell.WellSWPZ field is "Within GW SWPZ", which corresponds with the UICGWProtectionDomain domain code of "Y".
• If GWZ = Yes and ARDA = No, the UICWell.WellSWPZ field is still "Within GW SWPZ", which corresponds with the UICGWProtectionDomain domain code of "Y".
• If GWZ = No and ARDA = Yes, the UICWell.WellSWPZ field is "Outside GW SWPZ; Inside SGWA", which corresponds with the UICGWProtectionDomain domain code of "S".
• If GWZ = No and ARDA = No, the UICWell.WellSWPZ field is "Outside GW SWPZ and SGWA", which corresponds with the UICGWProtectionDomain domain code of "N".
• There is also a domain option for "Unknown" (UICGWProtectionDomain domain code of "U") that can be used if the GWZ/ARDA is unknown, although I'm not sure if this will be needed with the app since the app automatically calculates GWZ and ARDA.

Unused Fields

Here is my feedback regarding the listed unused fields:

unused well fields

• description - Correct, this field is not transferred.
• remediation_description - Correct, this is not captured within the database and is not transferred.
• injectate_characterization - Correct, this is not captured within the database and is not transferred.
• hydrogeologic_characterization - Correct, this is not captured within the database and is not transferred.
• surface_water_protection - Is this field related to the GWZ/ARDA mentioned above? If so, then the above explanation will apply to this field (please let me know if this field is related to something completely different).

unused inventory fields

• created_date - I think it is ok not to transfer this field. The one caveat is that there are fields for "CreatedOn" for the different entity types (e.g., facility, wells, authorizations, etc.), so the assumption is that these fields will be populated when the app writes the data to the database.
• sub_class - Assuming this is the same field, this is accounted for in the wells table above:
• signature - Correct, this is not captured within the database and is not transferred.

[nathankota]

I made changes to the tables based on the comments above. The final question I have is related to @rsparker-utah's comment about

unused inventory fields

• created_date - I think it is ok not to transfer this field. The one caveat is that there are fields for "CreatedOn" for the different entity types (e.g., facility, wells, authorizations, etc.), so the assumption is that these fields will be populated when the app writes the data to the database.

I don't think that assumption is correct. If you're thinking attribute rules will insert those, the only rule I see that would affect a date is https://github.com/uic-utah/uic-attribute-rules/blob/fc6dd1a0ae428b4c92d672ed8f13fc3c94a3a06e/src/rules/authorization_action.py#L30C1-L31C83.

Before I close this issue, please help me understand how and when the attribute rules are executed in your database entry process so we can determine whether or not the AuthorizationActionDate will be affected.

[nathankota]

This is validated