Convert Module – Water Pollutant Loading Tool Methodology

The Convert Module is the first processing step in the EPA’s Water Pollutant Loading Tool. It prepares DMR discharge data for pollutant load calculations by standardizing data formats, correcting flow values and other identified errors, and organizing data into usable tables. The module generates intermediate tables that include corrected flow values, standardized pollutant measurements, and facility and monitoring metadata. The Convert Module ensures that the data input into the pollutant load calculations are consistent and within reasonable value ranges.

On this page:

• Convert Module Function Steps
  • Create CONVERT_DMR Table
  • Correct Flows
  • Calculate Average Wastewater pH and Temperature
  • Correct DMR data
• Data Elements by Tables

Convert Module Functions 

1. Create CONVERT_DMR Table

The Loading Tool creates the CONVERT_DMR table using data extracted from ICIS-NPDES via the following approach:

Extract ICIS Data

The Loading Tool extracts permit, compliance, and DMR data from these ICIS-NPDES tables:

ICIS_ACTIVITY, ICIS_FACILITY_INTEREST, XREF_FACILITY_INTEREST_SIC, XREF_FACILITY_INTEREST_NAICS, ICIS_PERMIT, ICIS_PERMIT_FEATURE, ICIS_LIMIT_SET, ICIS_LIMIT_SET_SCHEDULE, ICIS_LIMIT, ICIS_LIMIT_VALUE, ICIS_DMR, ICIS_DMR_EVENT, ICIS_DMR_FORM, ICIS_DMR_FORM_PARAMETER, ICIS_DMR_VALUE, REF_PARAMETER, REF_POLLUTANT

Store ICIS Data in Interim Loading Tool Database Tables

The data from the 17 tables is put into five interim tables and three reference tables:

Interim Tables:

• DMR_LOADINGS_FACILITIES: Contains information from ICIS_FACILITY_INTEREST, XREF_FACILITY_INTEREST_SIC, XREF_FACILITY_INTEREST_NAICS, and ICIS_PERMIT. Also contains information from the NPDES_SITE table from FRS by unique NPDES permit number.
• DMR_LOADINGS_PERM_FEATURES: Contains information from ICIS_PERM_FEATURE and ICIS_PERMIT by unique permitted feature ID.
• DMR_LOADINGS_LIMITS: Contains information from ICIS_LIMIT_SET, ICIS_LIMIT_SET_SCHEDULE, ICIS_LIMIT, and ICIS_LIMIT_VALUE by unique LIMIT_VALUE_ID.
• DMR_LOADINGS_DMRS: Contains information from ICIS_DMR, ICIS_DMR_EVENT, ICIS_DMR_FORM, ICIS_DMR_FORM_PARAMETER, and ICIS_DMR_VALUE by unique DMR_VALUE_ID.
• DMR_LOADINGS_REF_PARAMETER: Contains information from REF_PARAMETER and REF_POLLUTNAT tables.

Reference Tables:

• REF_UNITS_CODES: Provides conversion factors for unit codes to convert concentrations into units of milligrams per liter (mg/L), loads into kilograms per day (kg/day), and flows into millions of gallons per day (MGD).
• REF_STAT5: Assigns approximately 160 statistical base codes from the ICIS_LIMIT table to one of five categories:
  • 0 = Statistical Base Code is Null
  • 1 = Average
  • 2 = Total
  • 3 = Maximum
  • 4 = Minimum
• REF_FLOW_PARAM: Identifies the wastewater flow parameter codes from the REF_PARAMETER table and assigns priorities that the Convert Module uses to match one flow per outfall and monitoring period for load calculations.

Convert Measurements and Units into Standard Units

The measurements and permit limits are converted into standard units in three steps:

• Identify Units of Measure: Unit codes are provided in the ICIS-NPDES tables ICIS_DMR and ICIS_LIMIT. The ICIS data contains already converted measurements as well as data in the original units. The converted measurements are verified in step 3.
• Convert ICIS Discharges into Standard Units: The measurements are converted into the standard units of milligrams per liter (mg/L), kilograms per day (kg/day) and millions of gallons per day (MGD) by linking the UNIT_CODE to REF_UNIT_CODES table and multiplying the measurement by the associated conversion factor.
• Verify ICIS Unit Conversions: EPA compared the converted ICIS measurements to the measurements stored in the DMR_VALUE_STD_UNITS field and corrected the conversions that did not match EPA's conversions.

Identify Measurement Statistical Basis

ICIS data contain statistical base codes to describe the statistical bases of the DMR measurements (e.g., 30-day geometric mean or rolling average). These codes are stored in the ICIS_LIMIT and Parameter Limits tables. The Convert Module uses the REF_STAT5 table to assign each statistical base code. Then the module creates a STAT5 code consisting of 5 characters in which each character corresponds to one measurement value field. See Load Calculator Module Functions (hyperlink coming soon) for more information about the STAT5 codes.

Create Measurement and Limit Crosstabs

The DMR_LOADINGS_LIMITS and DMR_LOADINGS_DMRS tables store limit values and DMR values by unique LIMIT_VALUE_NMBR and DMR_VALUE_NMBR, respectively. As a result, limits and DMR measurements for each measurement type are stored as separate records. The Convert Module creates two crosstab tables (DMR_LOADINGS_XTAB_LIMITS and DMR_LOADINGS_XTAB_DMRS) to display the limit values and DMR values for all five measurement fields in one row, unique by EXTERNAL_PERMIT_NMBR, PERM_FEATURE_NMBR, MONITORING_LOCATION_CODE, PATAMETER_CODE, and MONITORING_PERIOD_END_DATE.

Create DMR_LOADINGS_CONVERT_DMR Table

The Convert Module creates the DMR_LOADINGS_CONVERT_DMR table by combining information stored in DMR_LOADINGS_XTAB_LIMITS and DMR_LOADINGS_XTAB_DMRS tables. The new table presents the permit limits and DMR measurements for each unique EXTERNAL_PERMIT_NMBR, PERM_FEATURE_NMBR, PATAMETER_CODE, MONITORING_LOCATION_CODE, LIMIT_SET_DESIGNATOR, and LIMIT_SEASON_ID.

The Loading Tool only selects records for effluent monitoring locations, indicated in the created table in the MONITORING_LOCATION_CODE field by these five codes:

• 1 = Effluent gross discharge
• 2 = Effluent net discharge
• A = After disinfection
• B = Before disinfection
• SC = See Comment

The Convert Module searches the MONITORING_LOCATION_CODE field to identify effluent data and eliminates data for internal monitoring locations. When more than one type of effluent data is present for an outfall, the module selects only one monitoring location using the following priorities:

• Priority 1: 2
• Priority 2: 1
• Priority 3: A
• Priority 4: B
• Priority 5: SC

Calculate the Actual Number of Days per Monitoring Period

The Convert Module identifies the number of days per monitoring period using the NMBR_OF_REPORT field and the MONITORING_PERIOD_END_DATE from the DMR_LOADINGS_CONVERT_DMR table. NMBR_OF_REPORT field indicates the number of months of discharges represented on each DMR. The values allowed for this field are:

• 1 = Monthly Report
• 2 = Bi-Monthly Report
• 3 = Quarterly Report
• 4 = Triannual Report
• 6 = Semi-annual Report
• 12 = Annual Report

Outside of the above values, the Convert Module rounds up invalid NMBR_OF_REPORT values to the next valid value. NMBR_OF_REPORT values that are greater than 12 are converted to 12.

The Convert Module uses the table below to assign the actual number of days for the monitoring period in the NMBR_OF_DAYS field.

 *The Loading tool does not account for the number of days in February during leap years.

If a monitoring period has multiple reported measurements, the Convert Module divides the NMBR_OF_REPORT and the NMBR_OF_DAYS by the number of DMRs submitted per monitoring period.

Create DMR_LOADINGS_FLOWS Table

The DMR_LOADINGS_CONVERT_DMR table contains parameter codes that define the monitored pollutant. The Convert Module identifies parameter codes that define wastewater flow measurements in REF_FLOW_PRAM and creates a new table that is unique by EXTERNAL_PERMIT_NMBR, PERM_FEATURE_NMBR, MONITORING_LOCATION_CODE, LIMIT_SET_DESIGNATOR, LIMIT_SEASON_ID, and MONITORING_PERIOD_END_DATE. There are 24 parameter codes in ICIS-NPDES that represent wastewater flows. If a facility reports more than one type of flow parameter code for the same outfall, then the module selects the parameter code with the highest priority in the look-up table below.

Parameter Codes for Wastewater Flow

2. Correct Flows 

The Loading Tool identifies wastewater flows that were likely stored using incorrect units of measure and corrects the wastewater flows to represent millions of gallons per day. EPA developed three correction types to address flows that fall outside reasonable ranges:

1. Type 1: Month-to-Month Variability

   1. The tool detects extreme fluctuations in reported flaws across monitoring periods, and if the flow magnitudes differ by 3 or more orders of magnitude (e.g., 1 vs 1,000) the module applies the following correction factor:

      Corrected Flow (MGD) = Reported Flow (MGD) × (Minimum Flow Magnitude ≥ 1,000/Maximum Flow Magnitude < 1,000)

   2. This is applied to flows that are between 1,000 and 5,000 MGD or greater with ≥ 1 order of magnitude difference.
2. Type 2: Comparison to Design or Actual Average Flow

   1. If the reported flows are 3 or more orders of magnitude higher than the design/actual flows, the module adjusts by using the following equation:

      Corrected Flow (MGD) = Actual Measured Flow (MGD) × (Actual or Design Flow Magnitude / Actual Measured Flow Magnitude)

   2. This is applied to flows that are between 1,000 and 5,000 MGD or greater with ≥ 1 order of magnitude difference.
3. Type 3: Flows Exceeding 5,000 MGD
   1. If the flow exceeds 5,000 MGD and is not flagged by Types 1 or 2, then it is assumed to be misreported in gallons per day (GPD), and the module corrects by dividing the flow by 1,000,000.

3. Calculate Average Wastewater pH and Temperature 

1. The convert module scans the DMR_LOADINGS_CONVERT_DMR table for parameters that represent pH (REF_PH_PRAM) and temperature (REF_TEMP_PRAM). These reference tables define which parameter codes are valid for averaging.
2. The data are then grouped by external permit number, permit feature number (outfall), monitoring location, and monitoring period end date.
3. The module calculates the average pH and average temperature for each grouping.
4. Results are stored in a dedicated output table: DMR_LOADINGS_AVG_PH_TEMP which includes monitoring data, averaged pH and temperature values, and the applicable parameter codes.

4. Correct DMR Data 

1. Error Identification
   1. The Convert module scans the DMR_LOADINGS_CONVERT_DMR table for common reporting issues including invalid or missing values, misreported units, duplicate entries, and inconsistent parameter codes.
2. Reference-Based Validation
   1. EPA reference tables such as REF_PARAMETER, REF_UNITS, REF_LIMIT_SET are used to validate reported data to ensure parameter codes match expected formats and units.
3. Automated Corrections
   1. Rule-based logic is applied to fix unit conversion errors, duplicate monitoring records, misclassified parameters, invalid or missing values and metadata corrections.
4. Flagging and Documentation
   1. All corrections are logged and flagged for traceability. Metadata is also added to indicate the type and source of correction.

These table outputs are passed to the Load Calculator Module which calculates monitoring period loads, aggregated nitrogen and phosphorus loads, and sums the monitoring period loads to calculate annual loadings.

Data Elements by Tables 

Data Structure of Convert Module Output

DMR_LOADINGS_FACILITIES

This table contains information by unique external permit number (NPDES permit ID) including facility name, location, facility type, latitude/longitude, and primary industrial activity (SIC and NAICS codes). EPA supplemented the facility information from ICIS-NPDES with facility information from FRS to improve the data completeness for several fields, such as city, county, latitude, longitude, and congressional district. In addition, the

FRS data provide a link between NPDES permit numbers and Toxics Release Inventory (TRI) IDs.

DMR_LOADINGS_CONVERT_DMR

The CONVERT_DMR table contains effluent DMR data in standard units that have been matched permit limits, temperature, and pH. The CONVERT_DMR table also identifies the number of days per monitoring period (NMBR_OF_DAYS). In creating the CONVERT_DMR table, the Convert Module applied a monitoring location selection hierarchy to select only effluent measurements.

DMR_LOADINGS_FLOWS

The DMR_LOADINGS_FLOWS table contains effluent wastewater flow data in standard units that correspond to DMR measurements in the DMR_LOADINGS_CONVERT_DMR table.

DMR_LOADINGS_PERM_FEATURES

This table stores information by unique permitted feature ID (NPDES Outfall) including outfall number, location, and latitude/longitude.

DMR_LOADINGS_REF_PARAMETER

This table links parameter codes to Chemical Abstract Service (CAS) numbers, toxic weighting factors (TWFs), and Substance Registry Service (SRS) IDs. In addition, the table stores information that the Loading Tool uses to prioritize pollutant parameters for grouping and identifies parameters that are deleted from the Loading Tool output.