agrc / uic-etl Goto Github PK

cli tool to munge uic data into exchange network xml

C# 99.30% Python 0.70%

uic-etl's Introduction

Underground Injection Control ETL To EPA XML Tool

This tool is run on an ArcSDE database containing facilities and wells. It generates an XML document that can be submitted to the EPA.

Usage

Open a command window in a folder containing uic-etl.exe.
Execute uic-etl.exe -h or uic-etl.exe --help from the command line to see the options and help text.
Execute uic-etl.exe -c c:\path\to\db.sde
Visit the Submission Log to see warnings and errors for the current run.

The current run will be in a tab with the current date and time.

Options

λ uic-etl.exe -h
Usage: uicetl [OPTIONS]+

Options:
  -c, --connection=VALUE     REQUIRED. the path to the .sde connection file for
                               the UTEQ database. eg: c:\udeq.sde
  -o, --output=VALUE         the location to save the output of
                               this tool. eg: c:\. Defaults to current
                               working directory.
  -v                         increase the debug message verbosity.
  -h, --help                 show this message and exit

Example

uic-etl.exe -c C:\sde\UDEQ.sde -o c:\udeq.xml -v

Reference Items

Submission Steps

Run the tool
Veryify the warnings in the Submission Log
Compress the xml file to zip
Login to the cdxnode
Click on Exchange Network Service
Select Send Information
Search for UIC
Click Send Info
Attach and Upload File
Fill out metadata
Add contact email
Submit

uic-etl's People

Contributors

Watchers

uic-etl's Issues

XML Mapping

<?xml version="1.0" encoding="UTF-8"?>
<Document xmlns="http://www.exchangenetwork.net/schema/v1.0/ExchangeNetworkDocument.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" Id="6a43a7c5-0510-41ff-a15d-39657d55153d" xsi:schemaLocation="xmlns http://www.exchangenetwork.net/schema/v1.0/ExchangeNetworkDocument.xsd">
  <Header>
    <Author>static: CANDACE CADY</Author>
    <Organization>static: UDEQ -- UTAH DEPARTMENT OF ENVIRONMENTAL QUALITY</Organization>
    <Title>user input</Title>
    <CreationTime>dynamic: generated when tool is run</CreationTime>
    <Comment>user input</Comment>
    <DataService>static: UIC</DataService>
    <ContactInfo>static: CANDACE CADY 195 NORTH 1950 WEST, SALT LAKE CITY UT 84114, (801) 536-4352</ContactInfo>
    <Notification>static: [email protected]</Notification>
    <Sensitivity>static: UNCLASSIFIED</Sensitivity>
  </Header>
  <Payload Operation="Delete - Insert">
    <UIC xmlns="http://www.exchangenetwork.net/schema/uic/2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
      <PrimacyAgencyCode>static: UDEQ</PrimacyAgencyCode>
      <FacilityList>
        <FacilityDetail xmlns="http://www.exchangenetwork.net/schema/uic/2">
          <FacilityIdentifier>auto increment int</FacilityIdentifier>
          <LocalityName>[UICFacility].FacilityCity</LocalityName>
          <FacilitySiteName>[UICFacility].FacilityName</FacilitySiteName>
          <FacilityPetitionStatusCode>[UICFacility].NoMigrationPetStatus</FacilityPetitionStatusCode>
          <LocationAddressStateCode>[UICFacility].FacilityState</LocationAddressStateCode>
          <FacilityStateIdentifier>[UICFacility].FacilityID</FacilityStateIdentifier>
          <LocationAddressText>[UICFacility].FacilityAddress</LocationAddressText>
          <FacilitySiteTypeCode>[UICFacility].FacilityType</FacilitySiteTypeCode>
          <LocationAddressPostalCode>[UICFacility].FacilityZIP</LocationAddressPostalCode>
          <FacilityViolationDetail>
            <ViolationIdentifier>auto increment int</ViolationIdentifier>
            <ViolationContaminationCode>[UICViolation].USDWContamination</ViolationContaminationCode>
            <ViolationEndangeringCode>[UICViolation].ENDANGER</ViolationEndangeringCode>
            <ViolationReturnComplianceDate>[UICViolation].ReturnToComplianceDate: 20020104</ViolationReturnComplianceDate>
            <ViolationSignificantCode>[UICViolation].SignificantNonCompliance</ViolationSignificantCode>
            <ViolationDeterminedDate>[UICViolation].ViolationDate: 20020101</ViolationDeterminedDate>
            <ViolationTypeCode>[UICViolation].ViolationType</ViolationTypeCode>
            <ViolationFacilityIdentifier>[UICFacility].Guid</ViolationFacilityIdentifier>
            <FacilityResponseDetail>
              <ResponseEnforcementIdentifier>auto increment int</ResponseEnforcementIdentifier>
              <ResponseViolationIdentifier>[UICViolation].Guid</ResponseViolationIdentifier>
            </FacilityResponseDetail>
          </FacilityViolationDetail>
        </FacilityDetail>
        <WellDetail xmlns="http://www.exchangenetwork.net/schema/uic/2">
          <WellIdentifier>auto increment int</WellIdentifier>
          <WellAquiferExemptionInjectionCode>[UICWell].InjectionAquiferExempt</WellAquiferExemptionInjectionCode>
          <WellTotalDepthNumeric>[UICVerticalWellEvent].EventType</WellTotalDepthNumeric>
          <WellHighPriorityDesignationCode>[UICWell].HighPriority</WellHighPriorityDesignationCode>
          <WellContactIdentifier>[Contact].Foreign Key</WellContactIdentifier>
          <WellFacilityIdentifier>[UICWell].Facility_FK</WellFacilityIdentifier>
          <WellGeologyIdentifier>[VerticalWellEvent].Foreign Key</WellGeologyIdentifier>
          <WellSiteAreaNameText>12345678901234567890123456789012345678901234567890</WellSiteAreaNameText>
          <WellPermitIdentifier>[UICWell].Authorization_FK</WellPermitIdentifier>
          <WellStateIdentifier>[UICWell].GUID</WellStateIdentifier>
          <WellStateTribalCode>UT</WellStateTribalCode>
          <WellInSourceWaterAreaLocationText>[UICWell].SWPZ</WellInSourceWaterAreaLocationText>
          <WellName>[UICWell].WellName</WellName>
          <WellStatusDetail>
            <WellStatusIdentifier>auto incrementing int</WellStatusIdentifier>
            <WellStatusDate>[UICWellOperatingStatus].OperatingStatusDate</WellStatusDate>
            <WellStatusOperatingStatusCode>[UICWellOperatingStatus].OperatingStatusType</WellStatusOperatingStatusCode>
            <WellStatusWellIdentifier>[UICWellOPeratingStatus].Well_FK</WellStatusWellIdentifier>
          </WellStatusDetail>
          <WellTypeDetail>
            <WellTypeIdentifier>auto incrementing int</WellTypeIdentifier>
            <WellTypeCode>[Well].WellSubClass</WellTypeCode>
            <WellTypeDate>20020102</WellTypeDate>
            <WellTypeWellIdentifier>MEEP00W01</WellTypeWellIdentifier>
          </WellTypeDetail>
          <LocationDetail>
            <LocationIdentifier>MEEP00L01</LocationIdentifier>
            <LocationAddressCounty>[Facility].CountyFIPS</LocationAddressCounty>
            <LocationAccuracyValueMeasure>[Well].LocationAccuracy</LocationAccuracyValueMeasure>
            <GeographicReferencePointCode>[TemporaryGeographicOutput].DescriptionCategory</GeographicReferencePointCode>
            <HorizontalCoordinateReferenceSystemDatumCode>[TemporaryGeographicOutput].HorizontalDatum</HorizontalCoordinateReferenceSystemDatumCode>
            <HorizontalCollectionMethodCode>[Well].LocationMethod</HorizontalCollectionMethodCode>
            <LocationPointLineAreaCode>[TemporaryGeographicOutput]PointLineArea</LocationPointLineAreaCode>
            <SourceMapScaleNumeric>[[Well].LocationAccuracy</SourceMapScaleNumeric>
            <LocationWellIdentifier>MEEP00W01</LocationWellIdentifier>
            <LatitudeMeasure>[TemporaryGeographicOutput].Latitude</LatitudeMeasure>
            <LongitudeMeasure>[TemporaryGeographicOutput].Longitude</LongitudeMeasure>
          </LocationDetail>
          <WellViolationDetail>
            <ViolationIdentifier>MEEP0WV01</ViolationIdentifier>
            <ViolationContaminationCode>[Violation].USDWContaimination</ViolationContaminationCode>
            <ViolationEndangeringCode>[Violation].ENDANGER</ViolationEndangeringCode>
            <ViolationReturnComplianceDate>[Violation].ReturnToComplianceDate</ViolationReturnComplianceDate>
            <ViolationSignificantCode>[Violation].SignificantNonCompliance</ViolationSignificantCode>
            <ViolationDeterminedDate>[Violation].ViolationDate</ViolationDeterminedDate>
            <ViolationTypeCode>[Violation].ViolationType</ViolationTypeCode>
            <ViolationWellIdentifier>MEEP00W01</ViolationWellIdentifier>
            <WellResponseDetail>
              <ResponseEnforcementIdentifier>MEEP00E02</ResponseEnforcementIdentifier>
              <ResponseViolationIdentifier>MEEP0WV01</ResponseViolationIdentifier>
            </WellResponseDetail>
          </WellViolationDetail>
         <WellInspectionDetail>
            <InspectionIdentifier>auto increment int</InspectionIdentifier>
            <InspectionAssistanceCode>[UICInspection].InspectionAssistance | UICComplianceAssistanceDomain</InspectionAssistanceCode>
            <InspectionDeficiencyCode>[UICInspection].InspectionDeficiency | UICDeficiencyDomain</InspectionDeficiencyCode>
            <InspectionActionDate>[UICInspection].InspectionDate | yyyMMdd</InspectionActionDate>
            <InspectionICISComplianceMonitoringReasonCode>[UICInspection].ICISCompMonActReason | UICICISCompMonActReasonDomain</InspectionICISComplianceMonitoringReasonCode>
            <InspectionICISComplianceMonitoringTypeCode>[UICInspection].ICISCompMonType | UICICISMonitoringTypeDomain</InspectionICISComplianceMonitoringTypeCode>
            <InspectionICISComplianceActivityTypeCode>[UICInspection].ICISCompActType | UICICISCompActTypeDomain</InspectionICISComplianceActivityTypeCode>
            <InspectionICISMOAName>[UICInspection].ICISMOAPriority</InspectionICISMOAName>
            <InspectionICISRegionalPriorityName>[UICInspection].ICISRegionalPriority</InspectionICISRegionalPriorityName>
            <InspectionTypeActionCode>[UICInspection].InspectionType | UICInspectionTypeDomain</InspectionTypeActionCode>
            <InspectionWellIdentifier>[UICInspection].Well_FK</InspectionWellIdentifier>
            <CorrectionDetail>
              <CorrectionIdentifier>auto incrementing int</CorrectionIdentifier>
              <CorrectionActionTypeCode>[UICCorrection].CorrectiveAction | UICCorrectiveActionDomain</CorrectionActionTypeCode>
              <CorrectionCommentText>[UICCorrection].CorrectionCommentText</CorrectionCommentText>
              <CorrectionInspectionIdentifier>[UICCorrection].GUID</CorrectionInspectionIdentifier>
            </CorrectionDetail>
          </WellInspectionDetail>
          <MITestDetail>
            <MechanicalIntegrityTestIdentifier>auto incrementing int</MechanicalIntegrityTestIdentifier>
            <MechanicalIntegrityTestCompletedDate>[UICMIT].MITDate | yyyMMdd</MechanicalIntegrityTestCompletedDate>
            <MechanicalIntegrityTestResultCode>[UICMIT].MITResult | UICMITResultDomain</MechanicalIntegrityTestResultCode>
            <MechanicalIntegrityTestTypeCode>[UICMIT].MITType | UICMITTypeDomain</MechanicalIntegrityTestTypeCode>
            <MechanicalIntegrityTestRemedialActionDate>[UICMIT].MITRemActDate | yyyMMdd</MechanicalIntegrityTestRemedialActionDate>
            <MechanicalIntegrityTestRemedialActionTypeCode>[UICMIT].MITRemediationAction | MITRemediationActionDomain</MechanicalIntegrityTestRemedialActionTypeCode>
            <MechanicalIntegrityTestWellIdentifier>[UICMIT].Well_FK</MechanicalIntegrityTestWellIdentifier>
          </MITestDetail>
          <EngineeringDetail>
            <EngineeringIdentifier>auto incrementing int</EngineeringIdentifier>
            <EngineeringMaximumFlowRateNumeric>[WellOperation].MaxInjectionRate</EngineeringMaximumFlowRateNumeric>
            <EngineeringPermittedOnsiteInjectionVolumeNumeric>[WellOperation].OnSiteVolume</EngineeringPermittedOnsiteInjectionVolumeNumeric>
            <EngineeringPermittedOffsiteInjectionVolumeNumeric>[WellOperation].OffSiteVolume</EngineeringPermittedOffsiteInjectionVolumeNumeric>
            <EngineeringWellIdentifier>[WellOperation].Well_FK</EngineeringWellIdentifier>
          </EngineeringDetail>
          <WasteDetail>
             <WasteIdentifier>auto incrementing int</WasteIdentifier>
            <WasteCode>[WasteClassI].WasteCode</WasteCode>
            <WasteStreamClassificationCode>[WasteClassI].WasteStream</WasteStreamClassificationCode>
            <WasteWellIdentifier>[WasteClassI].Well_Fk</WasteWellIdentifier>
             <ConstituentIdentifier>auto incrementing int</ConstituentIdentifier>
              <MeasureValue>[ConstituentClassI].Concentration</MeasureValue>
              <MeasureUnitCode>[ConstituentClassI].Unit | EventsUnitsDomain</MeasureUnitCode>
              <ConstituentNameText>[ConstituentClassI].ConstituentCode</ConstituentNameText>
              <ConstituentWasteIdentifier>[ConstituentClassI].ClassIWaste_FK</ConstituentWasteIdentifier>
            </ConstituentDetail>
          </WasteDetail>
        </WellDetail>
      </FacilityList>
      <ContactDetail xmlns="http://www.exchangenetwork.net/schema/uic/2">
        <ContactIdentifier>MEEP00C01</ContactIdentifier>
        <TelephoneNumberText>[UICContact].ContactPhone</TelephoneNumberText>
        <IndividualFullName>[UICContact].ContactName</IndividualFullName>
        <ContactCityName>[UICContact].ContactMailCity</ContactCityName>
        <ContactAddressStateCode>[UICContact].ContactMailState</ContactAddressStateCode>
        <ContactAddressText>[UICContact].ContactMailAddress</ContactAddressText>
        <ContactAddressPostalCode>[UICContact].ZipCode5 + [UICContact].ZipCode4</ContactAddressPostalCode>
      </ContactDetail>
      <PermitDetail xmlns="http://www.exchangenetwork.net/schema/uic/2">
        <PermitIdentifier>MEEP00P02</PermitIdentifier>
        <PermitAORWellNumberNumeric>[AreaOfReview].sum of CA_Abandon,CA_Repair, CA_Replug, and CA_Other</PermitAORWellNumberNumeric>
        <PermitAuthorizedStatusCode>[Authorize].AuthorizeType</PermitAuthorizedStatusCode>
        <PermitOwnershipTypeCode>[Authorize].OwnerSectorType</PermitOwnershipTypeCode>
        <PermitAuthorizedIdentifier>[Authorize].AuthorizeNumber</PermitAuthorizedIdentifier>
        <PermitActivityDetail>
          <PermitActivityIdentifier>MEEP0PA04</PermitActivityIdentifier>
          <PermitActivityActionTypeCode>[AuthorizeAction].AuthorizeActionType</PermitActivityActionTypeCode>
          <PermitActivityDate>[AuthorizeAction]AuthorizeActionDate</PermitActivityDate>
          <PermitActivityPermitIdentifier>MEEP00P02</PermitActivityPermitIdentifier>
        </PermitActivityDetail>
      </PermitDetail>
      <GeologyDetail xmlns="http://www.exchangenetwork.net/schema/uic/2">
        <GeologyIdentifier>MEEP00G02</GeologyIdentifier>
        <GeologyConfiningZoneName>[VerticalWellEvent].VerticalWellEventID</GeologyConfiningZoneName>
        <GeologyConfiningZoneTopNumeric>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyConfiningZoneTopNumeric>
        <GeologyConfiningZoneBottomNumeric>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyConfiningZoneBottomNumeric>
        <GeologyLithologicConfiningZoneText>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyLithologicConfiningZoneText>
        <GeologyInjectionZoneFormationName>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyInjectionZoneFormationName>
        <GeologyBottomInjectionZoneNumeric>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyBottomInjectionZoneNumeric>
        <GeologyLithologicInjectionZoneText>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyLithologicInjectionZoneText>
        <GeologyTopInjectionIntervalNumeric>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyTopInjectionIntervalNumeric>
        <GeologyBottomInjectionIntervalNumeric>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyBottomInjectionIntervalNumeric>
        <GeologyInjectioneZonePermeabilityRateNumeric>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyInjectioneZonePermeabilityRateNumeric>
        <GeologyInjectionZonePorosityPercentNumeric>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyInjectionZonePorosityPercentNumeric>
        <GeologyUSDWDepthNumeric>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyUSDWDepthNumeric>
      </GeologyDetail>
      <EnforcementDetail xmlns="http://www.exchangenetwork.net/schema/uic/2">
        <EnforcementIdentifier>MEEP00E04</EnforcementIdentifier>
        <EnforcementActionDate>[EnforcementAction].EnforcementActionDate</EnforcementActionDate>
        <EnforcementActionType>[EnforcementAction].EnforcementActionType</EnforcementActionType>
      </EnforcementDetail>
    </UIC>
  </Payload>
</Document>

Do you think you could go through and fill in the other items as I have started to and correct any issues that I may have made?

UTEQ8BFFBC860B8BF4F0 - Mitest

<WellDetail xmlns="http://www.exchangenetwork.net/schema/uic/2">
  <WellIdentifier>UTEQ8BFFBC860B8BF4F0</WellIdentifier>
  <WellAquiferExemptionInjectionCode>N</WellAquiferExemptionInjectionCode>
  <WellHighPriorityDesignationCode>N</WellHighPriorityDesignationCode>
  <WellContactIdentifier>UTEQB96428EB4372309C</WellContactIdentifier>
  <WellFacilityIdentifier>UTEQA19B45B3C207F236</WellFacilityIdentifier>
  <WellSiteAreaNameText>Intrepid Potash</WellSiteAreaNameText>
  <WellPermitIdentifier>UTEQ9B1FED53F1C3C2E8</WellPermitIdentifier>
  <WellStateIdentifier>{A3EB7687-82C0-4ABB-8BFF-BC860B8BF4F0}</WellStateIdentifier>
  <WellStateTribalCode>UT</WellStateTribalCode>
  <WellInSourceWaterAreaLocationText>N</WellInSourceWaterAreaLocationText>
  <WellName>IP-37H</WellName>
  <WellStatusDetail>
    <WellStatusIdentifier>UTEQ9AF6BE2F15103E91</WellStatusIdentifier>
    <WellStatusDate>20120711</WellStatusDate>
    <WellStatusOperatingStatusCode>AC</WellStatusOperatingStatusCode>
    <WellStatusWellIdentifier>UTEQ8BFFBC860B8BF4F0</WellStatusWellIdentifier>
  </WellStatusDetail>
  <WellTypeDetail>
    <WellTypeIdentifier>UTEQ8BFFBC860B8BF4F0</WellTypeIdentifier>
    <WellTypeCode>3A</WellTypeCode>
    <WellTypeDate>20120111</WellTypeDate>
    <WellTypeWellIdentifier>UTEQ8BFFBC860B8BF4F0</WellTypeWellIdentifier>
  </WellTypeDetail>
  <LocationDetail>
    <LocationIdentifier>UTEQ8BFFBC860B8BF4F0</LocationIdentifier>
    <LocationAddressCounty>49019</LocationAddressCounty>
    <LocationAccuracyValueMeasure>40.0</LocationAccuracyValueMeasure>
    <GeographicReferencePointCode>026</GeographicReferencePointCode>
 <HorizontalCoordinateReferenceSystemDatumCode>002</HorizontalCoordinateReferenceSystemDatumCode>
    <HorizontalCollectionMethodCode>028</HorizontalCollectionMethodCode>
    <LocationPointLineAreaCode>001</LocationPointLineAreaCode>
    <SourceMapScaleNumeric>1</SourceMapScaleNumeric>
    <LocationWellIdentifier>UTEQ8BFFBC860B8BF4F0</LocationWellIdentifier>
    <LatitudeMeasure>38.515133</LatitudeMeasure>
    <LongitudeMeasure>-109.672412</LongitudeMeasure>
  </LocationDetail>
  <MITestDetail>
 <MechanicalIntegrityTestIdentifier>UTEQ83C847C5FDE59F25</MechanicalIntegrityTestIdentifier>
    <MechanicalIntegrityTestCompletedDate>20120214</MechanicalIntegrityTestCompletedDate>
    <MechanicalIntegrityTestResultCode>PS</MechanicalIntegrityTestResultCode>
    <MechanicalIntegrityTestTypeCode>CB</MechanicalIntegrityTestTypeCode>
 <MechanicalIntegrityTestWellIdentifier>UTEQ8BFFBC860B8BF4F0</MechanicalIntegrityTestWellIdentifier>
  </MITestDetail>
  <MITestDetail>
 <MechanicalIntegrityTestIdentifier>UTEQ90B5AA99D34427A5</MechanicalIntegrityTestIdentifier>
    <MechanicalIntegrityTestCompletedDate>20120422</MechanicalIntegrityTestCompletedDate>
    <MechanicalIntegrityTestResultCode>PS</MechanicalIntegrityTestResultCode>
    <MechanicalIntegrityTestTypeCode>CT</MechanicalIntegrityTestTypeCode>
 <MechanicalIntegrityTestWellIdentifier>UTEQ8BFFBC860B8BF4F0</MechanicalIntegrityTestWellIdentifier>
  </MITestDetail>
  <MITestDetail>
 <MechanicalIntegrityTestIdentifier>UTEQA00A634E7DEEF929</MechanicalIntegrityTestIdentifier>
    <MechanicalIntegrityTestCompletedDate>20120202</MechanicalIntegrityTestCompletedDate>
    <MechanicalIntegrityTestResultCode>PS</MechanicalIntegrityTestResultCode>
    <MechanicalIntegrityTestTypeCode>CT</MechanicalIntegrityTestTypeCode>
 <MechanicalIntegrityTestWellIdentifier>UTEQ8BFFBC860B8BF4F0</MechanicalIntegrityTestWellIdentifier>
  </MITestDetail>
</WellDetail>

UTEQ82B8DD3673CD1C8E - Well Type Detail

5J is wrong see #11

<WellDetail xmlns="http://www.exchangenetwork.net/schema/uic/2">
  <WellIdentifier>UTEQ82B8DD3673CD1C8E</WellIdentifier>
  <WellAquiferExemptionInjectionCode>N</WellAquiferExemptionInjectionCode>
  <WellHighPriorityDesignationCode>N</WellHighPriorityDesignationCode>
  <WellContactIdentifier>UTEQ8F4EB2118DA6AB39</WellContactIdentifier>
  <WellFacilityIdentifier>UTEQAC58BE61A61AD294</WellFacilityIdentifier>
  <WellSiteAreaNameText>LS Carson Mink Ranch</WellSiteAreaNameText>
  <WellPermitIdentifier>UTEQ8CA63D0B7AE5189B</WellPermitIdentifier>
  <WellStateIdentifier>{208E6D37-0238-42EB-82B8-DD3673CD1C8E}</WellStateIdentifier>
  <WellStateTribalCode>UT</WellStateTribalCode>
  <WellInSourceWaterAreaLocationText>N</WellInSourceWaterAreaLocationText>
  <WellName>Annual Wash Down Drain</WellName>
  <WellStatusDetail>
    <WellStatusIdentifier>UTEQAA2270677EF579B2</WellStatusIdentifier>
    <WellStatusDate>20090903</WellStatusDate>
    <WellStatusOperatingStatusCode>UC</WellStatusOperatingStatusCode>
    <WellStatusWellIdentifier>UTEQ82B8DD3673CD1C8E</WellStatusWellIdentifier>
  </WellStatusDetail>
  <WellTypeDetail>
    <WellTypeIdentifier>UTEQ82B8DD3673CD1C8E</WellTypeIdentifier>
    <WellTypeCode>5J</WellTypeCode>
    <WellTypeDate>20090903</WellTypeDate>
    <WellTypeWellIdentifier>UTEQ82B8DD3673CD1C8E</WellTypeWellIdentifier>
  </WellTypeDetail>
  <LocationDetail>
    <LocationIdentifier>UTEQ82B8DD3673CD1C8E</LocationIdentifier>
    <LocationAddressCounty>49049</LocationAddressCounty>
    <LocationAccuracyValueMeasure>200.0</LocationAccuracyValueMeasure>
    <GeographicReferencePointCode>026</GeographicReferencePointCode>
    <HorizontalCoordinateReferenceSystemDatumCode>002</HorizontalCoordinateReferenceSystemDatumCode>
    <HorizontalCollectionMethodCode>007</HorizontalCollectionMethodCode>
    <LocationPointLineAreaCode>001</LocationPointLineAreaCode>
    <SourceMapScaleNumeric>2</SourceMapScaleNumeric>
    <LocationWellIdentifier>UTEQ82B8DD3673CD1C8E</LocationWellIdentifier>
    <LatitudeMeasure>40.068796</LatitudeMeasure>
    <LongitudeMeasure>-111.792392</LongitudeMeasure>
  </LocationDetail>
</WellDetail>

export out wells for the following facilities

Intrepid Potash
Jordan Valley Water Conservancy District
Brigham City Corporation

There should be no errors, only warnings during the submission

PermitActivityActionTypeCode

<PermitActivityActionTypeCode>[AuthorizeAction].AuthorizeActionType</PermitActivityActionTypeCode>

This field is a domain that does not align with the exchange node values. The python does some secondary modifications but they are not good enough.

Most of the wells are permit not required or NR. The python script put these as empty which isn't acceptable. All of the information I have found does not give us an option for no permit. What should we replace these values with?

114 EngineeringDetail

At least one Engineering record (EngineeringDetail) must exist as a child of the Well record (WellDetail) for the reported well type.

Unused Contacts

Steps to reproduce

Add all contacts to epa submission

Expected behaviour

Pass

Actual behaviour

The Contact ID (ContactIdentifier) in the Contact record (ContactDetail) must match the Well Contact ID (WellContactIdentifier) of at least one associated Well record (WellDetail).

Fails as the contacts are not linked to a well

Only add contacts that are linked to a well.

56b WellInspectionDetail

The ICIS Compliance Monitoring Action Reason (InspectionICISComplianceMonitoringReasonCode) in the Well Inspection record (WellInspectionDetail) must match an allowed value. The data will be removed.

52b WellInspectionDetail

The Inspection Assistance Code (InspectionAssistanceCode) in the Well Inspection record (WellInspectionDetail) must match an allowed value. The data will be removed.

FacilityResponseDetail

The sample XML has an element called

<FacilityResponseDetail />

The Table Field mapping has a section called Table name: Response.

I can't find anything in the UIC SDE.

What's the story?

Multiple same level contacts

When selecting a well contact, the highest quality contact is selected. This is based off of the lowest domain index value. When there are more than one with the same value, is there a way to determine which to choose or does it matter if they are equivalent types?

WellTypeDetail

There is a section on wells called well type details that is not mentioned in the UIC mapping. Do we skip this block of items?

<WellTypeDetail>
            <WellTypeIdentifier>auto incrementing int</WellTypeIdentifier>
            <WellTypeCode>1H</WellTypeCode>
            <WellTypeDate>20020102</WellTypeDate>
            <WellTypeWellIdentifier>MEEP00W01</WellTypeWellIdentifier>
 </WellTypeDetail>

Add global Enforcements

Enforcements are in the global space. Add them after PermitDetails.

Empty Response Detail id

Steps to reproduce

ResponseDetail with the id:
R1
Response Violation Identifier' should not be empty. is not valid.

Multiple WellStatusDetails

Well #UTEQ9B46DA19A4D560B8 has 2 status details. The EPA has only one. Do I need to submit only the most recent?

epa

<WellStatusDetail>
    <WellStatusIdentifier>UTEQ9DD2DAE94F0B1B25</WellStatusIdentifier>
    <WellStatusDate>20080415</WellStatusDate>
    <WellStatusOperatingStatusCode>PA</WellStatusOperatingStatusCode>
    <WellStatusWellIdentifier>UTEQ9B46DA19A4D560B8</WellStatusWellIdentifier>
  </WellStatusDetail>

uic-etl

<WellStatusDetail>
    <WellStatusIdentifier>UTEQ9DD2DAE94F0B1B25</WellStatusIdentifier>
    <WellStatusDate>20080415</WellStatusDate>
    <WellStatusOperatingStatusCode>PA</WellStatusOperatingStatusCode>
    <WellStatusWellIdentifier>UTEQ9B46DA19A4D560B8</WellStatusWellIdentifier>
  </WellStatusDetail>
  <WellStatusDetail>
    <WellStatusIdentifier>UTEQ8E76782DEBEC88C4</WellStatusIdentifier>
    <WellStatusDate>20050623</WellStatusDate>
    <WellStatusOperatingStatusCode>AC</WellStatusOperatingStatusCode>
    <WellStatusWellIdentifier>UTEQ9B46DA19A4D560B8</WellStatusWellIdentifier>
  </WellStatusDetail>

other offenders

UTEQ8D4B4446E4265B7A
UTEQ85938DE1F670C1E3
UTEQAE1948F376F1CBA6

GeologyDetail

Is it valid to have all of the same values for the geology details section?

 <GeologyDetail xmlns="http://www.exchangenetwork.net/schema/uic/2">
        <GeologyIdentifier>MEEP00G02</GeologyIdentifier>
        <GeologyConfiningZoneName>[VerticalWellEvent].VerticalWellEventID</GeologyConfiningZoneName>
        <GeologyConfiningZoneTopNumeric>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyConfiningZoneTopNumeric>
        <GeologyConfiningZoneBottomNumeric>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyConfiningZoneBottomNumeric>
        <GeologyLithologicConfiningZoneText>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyLithologicConfiningZoneText>
        <GeologyInjectionZoneFormationName>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyInjectionZoneFormationName>
        <GeologyBottomInjectionZoneNumeric>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyBottomInjectionZoneNumeric>
        <GeologyLithologicInjectionZoneText>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyLithologicInjectionZoneText>
        <GeologyTopInjectionIntervalNumeric>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyTopInjectionIntervalNumeric>
        <GeologyBottomInjectionIntervalNumeric>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyBottomInjectionIntervalNumeric>
        <GeologyInjectioneZonePermeabilityRateNumeric>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyInjectioneZonePermeabilityRateNumeric>
        <GeologyInjectionZonePorosityPercentNumeric>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyInjectionZonePorosityPercentNumeric>
        <GeologyUSDWDepthNumeric>[VerticalWellEvent].EventType | UICEventTypeDomain</GeologyUSDWDepthNumeric>
      </GeologyDetail>

Permit issues with field mapping

The UIC Table Field Mappings document has AuthorizeType and AuthorizeNumber which do not exist in the Authorization table. There is an AuthorizationType which seems like a likely fix but the only thing close to AuthorizeNumber is AuthorizationID and I don't know if that is correct. Can you please verify.

      <PermitDetail xmlns="http://www.exchangenetwork.net/schema/uic/2">
        <PermitIdentifier>MEEP00P02</PermitIdentifier>
        <PermitAORWellNumberNumeric>[AreaOfReview].sum of CA_Abandon,CA_Repair, CA_Replug, and CA_Other</PermitAORWellNumberNumeric>
        <PermitAuthorizedStatusCode>[Authorize].AuthorizeType</PermitAuthorizedStatusCode>
        <PermitOwnershipTypeCode>[Authorize].OwnerSectorType</PermitOwnershipTypeCode>
        <PermitAuthorizedIdentifier>[Authorize].AuthorizeNumber</PermitAuthorizedIdentifier>
        <PermitActivityDetail>
          <PermitActivityIdentifier>MEEP0PA04</PermitActivityIdentifier>
          <PermitActivityActionTypeCode>[AuthorizeAction].AuthorizeActionType</PermitActivityActionTypeCode>
          <PermitActivityDate>[AuthorizeAction]AuthorizeActionDate</PermitActivityDate>
          <PermitActivityPermitIdentifier>MEEP00P02</PermitActivityPermitIdentifier>
        </PermitActivityDetail>
      </PermitDetail>

61b

61b. An inspection of a facility which ended up not revealing a UIC well, must have an inspection type of Inspection, No Well (NW).

Phone numbers with extensions

Steps to reproduce

'Telephone Number Text' must be between 0 and 15 characters. You entered 16 characters. 801.266.7100x226 is not valid.

What should we do with extensions or characters over 15?

57b WellInspectionDetail

The ICIS Compliance Activity Type (InspectionICISComplianceActivityTypeCode) in the Well Inspection record (WellInspectionDetail) does not match an allowed value. The data will be removed.

55b WellInspectionDetail

The ICIS Compliance Monitoring Action Reason (InspectionICISComplianceMonitoringReasonCode) in the Well Inspection record (WellInspectionDetail) must match an allowed value. The data will be removed.

WellTypeCode 5J

Steps to reproduce

WellTypeDetail with the id: UTEQ82B8DD3673CD1C8E                    
    R1              
        The specified condition was not met for 'Well Type Code'. 5J is not valid.

Expected behaviour

5J should be a valid well type code or map to a valid well type code

Actual behaviour

5J is not in the appendix for well types and fails validation so it doesn't get added to the well detail. Then it fails the epa submission.

Well Missing Violation

Facility: {1FE34009-B413-419F-83DE-8673D6D5205B}
Well: {F98B128E-65C3-419C-8055-1285E5E8FB74}

Missing Violation: {82931E3D-62EA-4A58-BE5A-5A210EAC5026}

XML submittal from last submission, needs reviewing

SubmitUICData5d6acf8c-7c46-48b7-8034-db3421ddf900.zip

Python Extraction Clues

Enforcements

this doesn't seem important since we don't use this field

line 1373

elif srcRow.EnforcementType == 'INF':
    tarRow.ENF_TYPE = 'OTR'

Authorizations

implemented

line 358 has some stuff related to authorizations.ownersectortype

if srcRow.OwnerSectorType == "SG":
    tarRow.OWNERSHIP_TYPE = "OS"
elif srcRow.OwnerSectorType == "LG":
    tarRow.OWNERSHIP_TYPE = "OS"
else:
    tarRow.OWNERSHIP_TYPE = handleNulls(tarTbl, "OWNERSHIP_TYPE", srcRow.OwnerSectorType)

Wells

this seems unnecessary also because it is used to set the geologyIdentifier which we are not using

line 466 has some well code stuff

if srcRow.WellClass == "1" or srcRow.WellClass == "3" or srcRow.WellClass == "4" or srcRow.WellClass == "5" or \
   getWellCode(srcRow.WellClass, srcRow.WellSubClass) == "3A" or \
   getWellCode(srcRow.WellClass, srcRow.WellSubClass) == "3G" or \
   getWellCode(srcRow.WellClass, srcRow.WellSubClass) == "5B3" or \
   getWellCode(srcRow.WellClass, srcRow.WellSubClass) == "5B4" or \
   getWellCode(srcRow.WellClass, srcRow.WellSubClass) == "5C1" or \
   getWellCode(srcRow.WellClass, srcRow.WellSubClass) == "5C3" or \
   getWellCode(srcRow.WellClass, srcRow.WellSubClass) == "5C4" or \
   getWellCode(srcRow.WellClass, srcRow.WellSubClass) == "5C5" or \
   getWellCode(srcRow.WellClass, srcRow.WellSubClass) == "5G1" or \
   getWellCode(srcRow.WellClass, srcRow.WellSubClass) == "5G2" or \
   getWellCode(srcRow.WellClass, srcRow.WellSubClass) == "5L2" or \
   getWellCode(srcRow.WellClass, srcRow.WellSubClass) == "5A22" or \
   getWellCode(srcRow.WellClass, srcRow.WellSubClass) == "5A23":

def getWellCode(inClass, inSubClass):
    uicWellCode = ""
    if inSubClass == 1002:
        uicWellCode = "1M"
    elif inSubClass == 1001:
        uicWellCode = "1H"
    elif inSubClass == 1000:
        uicWellCode = "1I"
    elif inSubClass == 1003:
        uicWellCode = "1R"
    elif inSubClass == 1999:
        uicWellCode = "1X"
    elif inSubClass == 3002:
        uicWellCode = "3A"
    elif inSubClass == 3004:
        uicWellCode = "3U"
    elif inSubClass == 3003:
        uicWellCode = "3S"
    elif inSubClass == 3000:
        uicWellCode = "3C"
    elif inSubClass == 3001:
        uicWellCode = "3N"
    elif inSubClass == 3999:
        uicWellCode = "3X"
    elif inSubClass == 4001:
        uicWellCode = "4A"
    elif inSubClass == 4002:
        uicWellCode = "4A"
    elif inSubClass == 4000:
        uicWellCode = "4P"
    elif inSubClass == 5001:
        uicWellCode = "5B1"
    elif inSubClass == 5040:
        uicWellCode = "5B2"
    elif inSubClass == 5048:
        uicWellCode = "5B3"
    elif inSubClass == 5050:
        uicWellCode = "5B4"
    elif inSubClass == 5002:
        uicWellCode = "5B6"
    elif inSubClass == 5046:
        uicWellCode = "5C1"
    elif inSubClass == 5008:
        uicWellCode = "5C2"
    elif inSubClass == 5003:
        uicWellCode = "5C3"
    elif inSubClass == 5005:
        uicWellCode = "5C4"
    elif inSubClass == 5007:
        uicWellCode = "5C5"
    elif inSubClass == 5004:
        uicWellCode = "5D"
    #elif inSubClass == 5049:
    #    uicWellCode = "5E"
    elif inSubClass == 5041:
        uicWellCode = "5F"
    elif inSubClass == 5042:
        uicWellCode = "5F"
    elif inSubClass == 5043:
        uicWellCode = "5F"
    elif inSubClass == 5006:
        uicWellCode = "5G1"
    elif inSubClass == 5047:
        uicWellCode = "5H1"
    elif inSubClass == 5000:
        uicWellCode = "5H2"
    elif inSubClass == 5037:
        uicWellCode = "5I"
    elif inSubClass == 5011: #??
        uicWellCode = "5J"
    elif inSubClass == 5044:
        uicWellCode = "5L1"
    elif inSubClass == 5035:
        if inClass == 3:
            uicWellCode = "3G" #Subclass of Class 3 wells
        elif inClass == 5:
            uicWellCode = "5L2"
    elif inSubClass == 5038:
        uicWellCode = "5K"
    elif inSubClass == 5010:
        uicWellCode = "5A"
    elif inSubClass == 5012:
        uicWellCode = "5A1"
    elif inSubClass == 5013:
        uicWellCode = "5A2"
    elif inSubClass == 5014:
        uicWellCode = "5A3"
    elif inSubClass == 5015:
        uicWellCode = "5A4"
    elif inSubClass == 5016:
        uicWellCode = "5A5"
    elif inSubClass == 5017:
        uicWellCode = "5A6"
    elif inSubClass == 5018:
        uicWellCode = "5A7"
    elif inSubClass == 5019:
        uicWellCode = "5A8"
    elif inSubClass == 5020:
        uicWellCode = "5A9"
    elif inSubClass == 5021:
        uicWellCode = "5A10"
    elif inSubClass == 5022:
        uicWellCode = "5A11"
    elif inSubClass == 5023:
        uicWellCode = "5A12"
    elif inSubClass == 5024:
        uicWellCode = "5A13"
    elif inSubClass == 5025:
        uicWellCode = "5A14"
    elif inSubClass == 5026:
        uicWellCode = "5A15"
    elif inSubClass == 5027:
        uicWellCode = "5A16"
    elif inSubClass == 5028:
        uicWellCode = "5A17"
    elif inSubClass == 5029:
        uicWellCode = "5A18"
    elif inSubClass == 5030:
        uicWellCode = "5A19"
    elif inSubClass == 5031:
        uicWellCode = "5A20"

    elif inSubClass == 5032:
        uicWellCode = "5A21"
    elif inSubClass == 5033:
        if inClass == 1:
            uicWellCode = "1C"
        elif inClass == 5:
            uicWellCode = "5A22"
    elif inSubClass == 5034:
        if inClass == 1:
            uicWellCode = "1W"
        elif inClass == 5:
            uicWellCode = "5A23"
    elif inSubClass == 5999:
        uicWellCode = "5X"
    elif inSubClass == 5045:
        uicWellCode = "5H3"
    elif inSubClass == 5101:
        uicWellCode = "5F"

Well Status Records

only OT and PR got their value changed to UC. Implemented.

line 724

if srcRow.OperatingStatusType == 'AC':
    tarRow.OPER_STATUS = 'AC'
    tarRow.STATUS_ID = handleNulls(tarTbl, 'STATUS_ID', "UTEQ" + srcRow.GUID.replace("}","").replace("-","")[-16:])
    tarRow.STATUS_DATE = handleNulls(tarTbl, 'STATUS_DATE', srcRow.OperatingStatusDate)
    if srcRow.Well_FK != None:
        tarRow.WELL_ID = handleNulls(tarTbl, 'WELL_ID', "UTEQ" + srcRow.Well_FK.replace("}","").replace("-","")[-16:])
    else:
        tarRow.WELL_ID = ''
elif srcRow.OperatingStatusType == 'UC':
    tarRow.OPER_STATUS = 'UC'
    tarRow.STATUS_ID = handleNulls(tarTbl, 'STATUS_ID', "UTEQ" + srcRow.GUID.replace("}","").replace("-","")[-16:])
    tarRow.STATUS_DATE = handleNulls(tarTbl, 'STATUS_DATE', srcRow.OperatingStatusDate)
    tarRow.WELL_ID = handleNulls(tarTbl, 'WELL_ID', "UTEQ" + srcRow.Well_FK.replace("}","").replace("-","")[-16:])
elif srcRow.OperatingStatusType == 'TA':
    tarRow.OPER_STATUS = 'TA'
    tarRow.STATUS_ID = handleNulls(tarTbl, 'STATUS_ID', "UTEQ" + srcRow.GUID.replace("}","").replace("-","")[-16:])
    tarRow.STATUS_DATE = handleNulls(tarTbl, 'STATUS_DATE', srcRow.OperatingStatusDate)
    tarRow.WELL_ID = handleNulls(tarTbl, 'WELL_ID', "UTEQ" + srcRow.Well_FK.replace("}","").replace("-","")[-16:])
elif srcRow.OperatingStatusType == 'PA':
    tarRow.OPER_STATUS = 'PA'
    tarRow.STATUS_ID = handleNulls(tarTbl, 'STATUS_ID', "UTEQ" + srcRow.GUID.replace("}","").replace("-","")[-16:])
    tarRow.STATUS_DATE = handleNulls(tarTbl, 'STATUS_DATE', srcRow.OperatingStatusDate)
    tarRow.WELL_ID = handleNulls(tarTbl, 'WELL_ID', "UTEQ" + srcRow.Well_FK.replace("}","").replace("-","")[-16:])
elif srcRow.OperatingStatusType == 'AN':
    tarRow.OPER_STATUS = 'AN'
    tarRow.STATUS_ID = handleNulls(tarTbl, 'STATUS_ID', "UTEQ" + srcRow.GUID.replace("}","").replace("-","")[-16:])
    tarRow.STATUS_DATE = handleNulls(tarTbl, 'STATUS_DATE', srcRow.OperatingStatusDate)
    tarRow.WELL_ID = handleNulls(tarTbl, 'WELL_ID', "UTEQ" + srcRow.Well_FK.replace("}","").replace("-","")[-16:])
elif srcRow.OperatingStatusType == 'PW':
    tarRow.OPER_STATUS = 'PW'
    tarRow.STATUS_ID = handleNulls(tarTbl, 'STATUS_ID', "UTEQ" + srcRow.GUID.replace("}","").replace("-","")[-16:])
    tarRow.STATUS_DATE = handleNulls(tarTbl, 'STATUS_DATE', srcRow.OperatingStatusDate)
    tarRow.WELL_ID = handleNulls(tarTbl, 'WELL_ID', "UTEQ" + srcRow.Well_FK.replace("}","").replace("-","")[-16:])
elif srcRow.OperatingStatusType == 'OT':
    tarRow.OPER_STATUS = 'UC'
    tarRow.STATUS_ID = handleNulls(tarTbl, 'STATUS_ID', "UTEQ" + srcRow.GUID.replace("}","").replace("-","")[-16:])
    tarRow.STATUS_DATE = handleNulls(tarTbl, 'STATUS_DATE', srcRow.OperatingStatusDate)
    tarRow.WELL_ID = handleNulls(tarTbl, 'WELL_ID', "UTEQ" + srcRow.Well_FK.replace("}","").replace("-","")[-16:])
elif srcRow.OperatingStatusType == 'PR':
    tarRow.OPER_STATUS = 'UC'
    tarRow.STATUS_ID = handleNulls(tarTbl, 'STATUS_ID', "UTEQ" + srcRow.GUID.replace("}","").replace("-","")[-16:])
    tarRow.STATUS_DATE = handleNulls(tarTbl, 'STATUS_DATE', srcRow.OperatingStatusDate)
    tarRow.WELL_ID = handleNulls(tarTbl, 'WELL_ID', "UTEQ" + srcRow.Well_FK.replace("}","").replace("-","")[-16:])

Permit Activity Records

I don't think this is used.

line 605

if srcRow.AUTHORIZATIONACTIONTYPE == 'NR': # NR - Permit Not Required
    #print 'NR not reported'
    pass
if srcRow.AUTHORIZATIONACTIONTYPE == None:
    print '    Empty value not reported'

if srcRow.AUTHORIZATIONACTIONTYPE == 'AI' or srcRow.AUTHORIZATIONACTIONTYPE == 'AM' or \
   srcRow.AUTHORIZATIONACTIONTYPE == 'AR' or srcRow.AUTHORIZATIONACTIONTYPE == 'PI' or \
   srcRow.AUTHORIZATIONACTIONTYPE == 'PD' or srcRow.AUTHORIZATIONACTIONTYPE == 'AW' or \
   srcRow.AUTHORIZATIONACTIONTYPE == 'PT' or srcRow.AUTHORIZATIONACTIONTYPE == 'TP' or \
   srcRow.AUTHORIZATIONACTIONTYPE == 'PM' or srcRow.AUTHORIZATIONACTIONTYPE == 'FR':

    if srcRow.AUTHORIZATIONACTIONTYPE == 'AI':
        tarRow.ACT_TYPE = 'AI'

    if srcRow.AUTHORIZATIONACTIONTYPE == 'AM':
        tarRow.ACT_TYPE = 'AM'

    if srcRow.AUTHORIZATIONACTIONTYPE == 'AR':
        tarRow.ACT_TYPE = 'AM'

    if srcRow.AUTHORIZATIONACTIONTYPE == 'PI':
        tarRow.ACT_TYPE = 'PI'

    if srcRow.AUTHORIZATIONACTIONTYPE == 'PD':
        tarRow.ACT_TYPE = 'PD'

    if srcRow.AUTHORIZATIONACTIONTYPE == 'AW':
        tarRow.ACT_TYPE = 'PD'

    if srcRow.AUTHORIZATIONACTIONTYPE == 'PT':
        tarRow.ACT_TYPE = 'PN'

    if srcRow.AUTHORIZATIONACTIONTYPE == 'TP':
        tarRow.ACT_TYPE = 'PN'

    if srcRow.AUTHORIZATIONACTIONTYPE == 'PM':
        tarRow.ACT_TYPE = 'PM'

    if srcRow.AUTHORIZATIONACTIONTYPE == 'FR':
        tarRow.ACT_TYPE = 'FR'

    if srcRow.GUID != None:
        tarRow.ACTIVITY_ID = "UTEQ" + srcRow.GUID.replace("}","").replace("-","")[-16:]
    else:
        tarRow.ACTIVITY_ID = ''

    if srcRow.AuthorizationActionDate != None:
        tarRow.ACT_DATE = srcRow.AuthorizationActionDate
    else:
        tarRow.ACT_DATE = None

    if srcRow.Authorization_FK != None:
        tarRow.PERMIT_ID = "UTEQ" + srcRow.Authorization_FK.replace("}","").replace("-","")[-16:]
    else:
        tarRow.PERMIT_ID = ''

Well Total Depth 20d

The Well Depth (WellTotalDepthNumeric) in the Well record (WellDetail) must be an integer in the range 0 < x < 100,000. The data will be removed.

I've been defaulting to 0. Should we up that to 1?

53 b WellInspectionDetail

The Inspection Deficiency Code (InspectionDeficiencyCode) in the Well Inspection record (WellInspectionDetail) must match an allowed value. The data will be removed.

Incomplete well

Well #UTEQA7029FF38E5E1933 is missing it's wellstatus detail and well type detail record.

Investigate why. The child records are probably not valid. Look at why.

make the tool do the zipping

It is required for the submittal and simple to do in the app.

Linked Permits

The Permit ID (PermitIdentifier) in the Permit record (PermitDetail) must match the Well Permit ID (WellPermitIdentifier) of at least one associated Well record (WellDetail).

Only add linked permits

Lat and Long 39c and 40c

The Well Latitude (LatitudeMeasure) in the Location record (LocationDetail) must be in the range -90 <= x <= 90 and formatted as (-)DD.dddddd (no plus (+) sign for a positive value). Leading and/or trailing 0s are not required. The data will be removed.

Round to 6 decimal places.

Table Field Mapping

This google doc has the mapping between the UDEQ.UICADMIN.UICFacility table and the xml etc. I am noticing that the Field Names are not identical to what is in the UICFacility table. Were they recently changed? Am I looking at the wrong table?

PermitActivityIdentifier

The Permit Activity Permit ID (PermitActivityPermitIdentifier) in the Permit Activity record (PermitActivityDetail) must match the Permit ID (PermitIdentifier) of the parent Permit record (PermitDetail).

Possibly linked to #16 or the permit activity id's are not lined up properly.

GeologyDetail

Steps to reproduce

Run tool
submit xml as zip file
view summary report

Expected behaviour

Successful submission

Actual behaviour

Failed

The Well Geology ID (WellGeologyIdentifier) in the Well record (WellDetail) must map to the Geology ID (GeologyIdentifier) of at least one associated Geology record (GeologyDetail).

Every well has an empty guid for the geology detail because we are not reporting geology details. How should we get around this.

update tests to passing

someone got lazy with the recent changes and didn't keep up on the tests. update the tests so they pass with all the current changes.

Well operating status

Steps to reproduce

Identify Well with guid {A248EBE1-F3C0-4FC9-BD2E-8E689C554A18}
Expand the well operating status relationship
View the dates of the records

Expected behavior

The operating status with the newest operating status date should be used in the xml

Actual behavior

The 2016/07/13 well was selected over the 2016/8/12 record

Is this because of when the data was submitted? I thought it was long after August that the submission was sent. Is there some other priority on the operating status that I am not aware of other than date? The python script does not provide any clues. It is adding all of the operating status values. The submission xml only has one record. Could this be a bug caused by sending all of the operating status values and not only the newest?

Validation Errors

These three wells did not have associated well status records or well type records:
WellDetail Without Matching WellStatusDetail
WellIdentifier
WellStateIdentifier
WellName
UTEQ8C3613540B6DBF04
{2271CA70-13DB-402B-8C36-13540B6DBF04}
EX 7
UTEQAADCA27E861B1A73
{3BA6F592-4ED9-4285-AADC-A27E861B1A73}
StormTech
UTEQA6A4A795DCE1D8F9
{4DFF6D45-F110-4206-A6A4-A795DCE1D8F9}
Storm Water Drainage Well

WellDetail Without Matching WellTypeDetail
WellIdentifier
WellStateIdentifier
WellName
UTEQ8C3613540B6DBF04
{2271CA70-13DB-402B-8C36-13540B6DBF04}
EX 7
UTEQAADCA27E861B1A73
{3BA6F592-4ED9-4285-AADC-A27E861B1A73}
StormTech
UTEQA6A4A795DCE1D8F9
{4DFF6D45-F110-4206-A6A4-A795DCE1D8F9}
Storm Water Drainage Well

These three wells were not included in the data set submitted the 2nd time. The data set in the submission that failed had 3713 well records; the data set in the second submission had 3676 well records. If you would like to me identify the well records that were in the 1st data set but not in the 2nd data set, let me know. I can do that, but maybe not by COB today.

This permit record did not have an ownership type code included:
PermitDetail
PermitIdentifier
PermitAORWellNumberNumeric
PermitAuthorizedStatusCode
PermitOwnershipTypeCode
PermitAuthorizedIdentifier
UTEQ8EFD6E2B9A405A85
0.0
RA
UTU49RAA405A85
This permit was not included in the second data set. The first data set had 631 permit records; the second data set had 625 permit records. It looks like it is a rule authorization for the Midtown 360 stormwater wells.

There were two enforcement records with the same unique identifier in the data set that failed. Only 1 record with this unique identifier was included in the second data set
Find duplicates for EnforcementDetail
EnforcementIdentifier Field
NumberOfDups
UTEQAD51F0679A55A157
2

WellContactIdentifier

 <WellDetail xmlns="http://www.exchangenetwork.net/schema/uic/2">    
    <WellContactIdentifier>MEEP00C02</WellContactIdentifier>

The doc says that well contact identifier is the [Foreign Key]. There is no relationship between a well and a contact. I was able to find a relationship between a facility and a contact but that is a many to many relationship.

How do we put a many to many relationship into a single field?
or
What is the real value to put into WellContactIdentifier?

NAICS Code Rule 7b

Either a NAICS code (NAICSCode), not exceeding 6 characters in length, or a SICS code (SICSCode), not exceeding 4 characters in length, must be present in the Facility record (FacilityDetail).

From the sample schema

<FacilityDetail xmlns="http://www.exchangenetwork.net/schema/uic/2">
<FacilityIdentifier>MEEP000F2</FacilityIdentifier>
<LocalityName>a</LocalityName>
<FacilitySiteName>a</FacilitySiteName>
<FacilityPetitionStatusCode>AP</FacilityPetitionStatusCode>
<LocationAddressStateCode>DC</LocationAddressStateCode>
<FacilityStateIdentifier>bx</FacilityStateIdentifier>
<LocationAddressText>a</LocationAddressText>
<FacilitySiteTypeCode>C</FacilitySiteTypeCode>
<NAICSCode>a</NAICSCode>
<SICCode>a</SICCode>
<LocationAddressPostalCode>aaaaa</LocationAddressPostalCode>

Do we need one?

WellGeologyIdentifier

 <WellDetail xmlns="http://www.exchangenetwork.net/schema/uic/2">    
    <WellGeologydentifier></WellGeologydentifier>

The doc says that well geology identifier is the [Foreign Key]. There is no relationship between a well and a geology. I was not able to find a geology table.

What goes in this value?

uic-etl output option

I cannot get the -o option for the uic-etl tool to work.

uic-etl.exe –c c:\path\to\db.sde –o c:\path\to\output.xml -v

I have tried this a few times but the output .xml still shows up in the Release directory. I want it to be written to: c:\GIS\ExchangeNetwork\2017_05_09\UDEQ-5-9-2017.xml Writing the output to this directory rather than the Release directory will prevent any accidental mucking around of the other files in Release. Has this option worked for you?

EPA omissions in FacilityInspectionDetail

<FacilityDetail xmlns="http://www.exchangenetwork.net/schema/uic/2">
  <FacilityIdentifier>UTEQ85F522C551BD0E18</FacilityIdentifier>
  <LocalityName>Murray</LocalityName>
  <FacilitySiteName>Wagstaff Crane Service</FacilitySiteName>
  <FacilityPetitionStatusCode>NA</FacilityPetitionStatusCode>
  <LocationAddressStateCode>UT</LocationAddressStateCode>
  <FacilityStateIdentifier>UTU35F51BD0E18</FacilityStateIdentifier>
  <LocationAddressText>487 West Jensen </LocationAddressText>
  <NAICSCode>238990</NAICSCode>
  <LocationAddressPostalCode>84123</LocationAddressPostalCode>
  <FacilityInspectionDetail>
    <InspectionIdentifier>UTEQBDDF72302EE8627C</InspectionIdentifier>
    <InspectionAssistanceCode>BH</InspectionAssistanceCode>
    <InspectionActionDate>20120312</InspectionActionDate>
    <InspectionTypeActionCode>NW</InspectionTypeActionCode>
    <InspectionFacilityIdentifier>UTEQ85F522C551BD0E18</InspectionFacilityIdentifier>
  </FacilityInspectionDetail>
</FacilityDetail>

<FacilityDetail xmlns="http://www.exchangenetwork.net/schema/uic/2">
  <FacilityIdentifier>UTEQ86EEFEB2B1EE178B</FacilityIdentifier>
  <LocalityName>St. George</LocalityName>
  <FacilitySiteName>Somerville Auto &amp; Marine</FacilitySiteName>
  <FacilityPetitionStatusCode>NA</FacilityPetitionStatusCode>
  <LocationAddressStateCode>UT</LocationAddressStateCode>
  <FacilityStateIdentifier>UTU53FB1EE178B</FacilityStateIdentifier>
  <LocationAddressText>2375 East Middleton Drive</LocationAddressText>
  <NAICSCode>811111</NAICSCode>
  <LocationAddressPostalCode>84770</LocationAddressPostalCode>
  <FacilityInspectionDetail>
    <InspectionIdentifier>UTEQ8B0FBBA3446C8CBD</InspectionIdentifier>
    <InspectionAssistanceCode>GN</InspectionAssistanceCode>
    <InspectionDeficiencyCode>NO</InspectionDeficiencyCode>
    <InspectionActionDate>20110317</InspectionActionDate>
    <InspectionTypeActionCode>NW</InspectionTypeActionCode>
    <InspectionFacilityIdentifier>UTEQ86EEFEB2B1EE178B</InspectionFacilityIdentifier>
  </FacilityInspectionDetail>
</FacilityDetail>

Should we omit InspectionAssistanceCode and InspectionDeficiencyCode for NoWell inspections?

Facility missing inpection

Figure out why

epa xml

<FacilityDetail xmlns="http://www.exchangenetwork.net/schema/uic/2">
  <FacilityIdentifier>UTEQ8209B976DF463884</FacilityIdentifier>
  <LocalityName>Cedar City</LocalityName>
  <FacilitySiteName>Rolling Rubber</FacilitySiteName>
  <FacilityPetitionStatusCode>NA</FacilityPetitionStatusCode>
  <LocationAddressStateCode>UT</LocationAddressStateCode>
  <FacilityStateIdentifier>UTU21FDF463884</FacilityStateIdentifier>
  <LocationAddressText>256 West 200 North</LocationAddressText>
  <NAICSCode>811111</NAICSCode>
  <LocationAddressPostalCode>84721</LocationAddressPostalCode>
  <FacilityInspectionDetail>
    <InspectionIdentifier>UTEQ9CBC3DC7E8531BA0</InspectionIdentifier>
    <InspectionActionDate>20110317</InspectionActionDate>
    <InspectionTypeActionCode>NW</InspectionTypeActionCode>
    <InspectionFacilityIdentifier>UTEQ8209B976DF463884</InspectionFacilityIdentifier>
  </FacilityInspectionDetail>
</FacilityDetail>

UTEQ8378313933F60C3E

Empty Facility Address should get Milepost value

There should be some code that checks to see if Utah UIC Facility Address is null. If it is, then FacilityMilePost should be substituted for Facility Address and written to EPA LocationAddressText. Unfortunately, we should have left Facility Address blank in the UIC gdb for Intrepid Potash so this would work. Instead we put ''See Facility Mile Post''.

agrc / uic-etl Goto Github PK

uic-etl's Introduction

Underground Injection Control ETL To EPA XML Tool

Usage

Options

Example

Reference Items

Submission Steps

uic-etl's People

Contributors

Watchers

uic-etl's Issues

UTEQ8BFFBC860B8BF4F0 - Mitest

UTEQ82B8DD3673CD1C8E - Well Type Detail

export out wells for the following facilities

Steps to reproduce

Expected behaviour

Actual behaviour

Steps to reproduce

epa

uic-etl

other offenders

Steps to reproduce

Steps to reproduce

Expected behaviour

Actual behaviour

Enforcements

Authorizations

Wells

Well Status Records

Permit Activity Records

Steps to reproduce

Expected behaviour

Actual behaviour

Steps to reproduce

Expected behavior

Actual behavior

epa xml

Recommend Projects

Recommend Topics

Recommend Org