# Technical Users Background Document for the Discharge Monitoring Report (DMR) Pollutant Loading Tool

Version 1.0

January 2012DRAFT Table of Contents



### i TABLE OF CONTENTS

Page

1. I NTRODUCTION 

### ................................................................................................................. 1-1 1.1 Purpose of Loading Tool ..................................................................................... 1-1 1.2 Overview of Loading Tool Architecture .............................................................. 1-2 1.3 Development Status and Deployment Schedule .................................................. 1-4

2. R EQUIREMENTS 

### A NALYSIS 

### S UMMARY 

### .............................................................................. 2-1 2.1 Loading Tool EPA Work Group Interviews ........................................................ 2-4 2.1.1 Office of Water/Office of Wastewater Management/Water Permits Division (WPD) ....................................................................................... 2-4 2.1.2 Office of Water/Office of Wastewater Management/Municipal Support Division (MSD) .......................................................................... 2-5 2.1.3 Office of Water/Management and Operations Staff/Program Management Office (PMO) ..................................................................... 2-6 2.1.4 Office of Enforcement Targeting and Data Division/Office of Compliance/ Enforcement Targeting and Data Division (ETDD) ........... 2-7 2.1.5 Office of Water/Office of Science and Technology/Standards and Health Protection Division (SHPD) ......................................................... 2-8 2.1.6 Requirements Summary ........................................................................... 2-9 2.1.7 Requirements Selection and Loading Tool Options Assessment .......... 2-12 2.1.8 Requirements for TRI Searches ............................................................. 2-15 2.2 Data Extraction Procedures (ICIS, PCS, and TRI) ............................................ 2-18 2.3 Repository Selection .......................................................................................... 2-21

3. C ALCULATOR 

### M ODULE 

### D EVELOPMENT 

### ............................................................................ 3-1 3.1 Convert Module Functions .................................................................................. 3-1 3.1.1 Convert Module Major Step 1 – Create CONVERT_DMR Table .......... 3-3 3.1.2 Major Step 2 – Correct Wastewater Flows ............................................ 3-14 3.1.3 Major Step 3 – Calculate Average pH and Temperature ....................... 3-17 3.1.4 Convert Module Major Step 4 – Correct DMR Errors .......................... 3-18 3.2 Loa d Calculator Module Functions ................................................................... 3-20 3.2.1 Select Measurement Values and Flows for Loadings Calculations ....... 3-20 3.2.2 Calculate Moni toring Period Loads ....................................................... 3-21 3.2.3 Aggregate Nutrient Loads ...................................................................... 3-29 3.2.4 Calculating the Estimation Factor .......................................................... 3-32 3.2.5 Calculate Annual Loads ......................................................................... 3-33 3.3 EZ Search Database Development ..................................................................... 3-35 EZ Search Po llutant Category Loadings Development ..................................... 3-38 3.3.1 General Methodology for Grouping Pollutants into Categories ............ 3-39 3.3.2 References .............................................................................................. 3-49 3.4 TRI Search Database Development ................................................................... 3-49 References .......................................................................................................... 3-51

4. I NTERFACE 

### D EVELOPMENT 

### ............................................................................................... 4-1 4.1 EZ Search (DMR) ................................................................................................ 4-1DRAFT Table of Contents



### (Continued)

Page

ii 4.1.1 Search Statistics Table ............................................................................. 4-6 4.1.2 Top Pollutants Tables .............................................................................. 4-6 4.1.3 Top SIC Codes Tables ............................................................................. 4-7 4.1.4 Top Watershed Tables ............................................................................. 4-9 4.1.5 Top Facilities Tables .............................................................................. 4-10 4.2 TRI Search ......................................................................................................... 4-13 4.2.1 Top Pollutants Tables ............................................................................ 4-17 4.2.2 Top NAICS Codes Tables ..................................................................... 4-18 4.2.3 Top Watershed Tables ........................................................................... 4-20 4.2.4 Top Facilities Tables .............................................................................. 4-22 4.3 Compare DMR and TRI Data ............................................................................ 4-24 4.4 Facility Search ................................................................................................... 4-26 4.5 Advanced Search ............................................................................................... 4-31 4.5.1 Level of Detail ....................................................................................... 4-32 4.5.2 Filter Criteria .......................................................................................... 4-32 4.5.3 Loading Calculation Options ................................................................. 4-36 4.5.4 Advanced Search Output ....................................................................... 4-37

5. Q UALITY 

### A SSURANCE 

### R ESULTS 

### ........................................................................................ 5-1 5.1 DMR Data Review ............................................................................................... 5-1 5.2 TRI Data Review ................................................................................................. 5-5 5.3 304m Screening- Level Analysis Outlier Review ................................................ 5-6 5.3.1 DMR Data Corrections ............................................................................ 5-6 5.3.2 TRI Data Correctio ns .............................................................................. 5-7 5.4 Calculation Module Testing ................................................................................. 5-8 5.5 Interface Testing .................................................................................................. 5-9 5.6 Error Reporting .................................................................................................... 5-9 5.7 References .......................................................................................................... 5-12

Appendix A: TABLES FOR CONVERT AND LOAD CALCULATOR MODULE DEVELOPMENT

Appendix B: TABLES FOR EZ SEARCH DATABASE DEVELOPMENT

Appendix C: TABLES FOR TRI SEARCH DATABASE DEVELOPMENT

Appendix D: TABLES AND FIGURES FOR INTERFACE DEVELOPMENT

Appendix E: TABLES FOR QUALITY ASSURANCE RESULTS

Appendix F: DATA ELEMENT DICTIONARY

Appendix G: CALCULATOR MODULE SO URCE COD EDRAFT List of Tables



### iii LIST OF TABLES

Page

Table 2 -1. EPA Loading Tool Work Group Members ................................................................ 2-1

Table 2 -2. Summary of Options for Loading Tool Considered in 2008 ...................................... 2-3

Table 2 -3. Scope of Data to Include in Loading Tool ................................................................. 2-9

Table 2

- 4. Loading Tool Calculation Methodology and Assumptions ...................................... 2-10

Table 2 -5. Loading Tool Output File ......................................................................................... 2-11

Table 2

- 6. User Querying Capabilities ...................................................................................... 2-12

Table 2 -7. Comparison of Loading Tool Options and User Requirements ............................... 2-13

Table 3

- 1. PCS Data Import to DMR_LOADINGS_FACILITIES Table .................................. 3-6

Table 3

- 2. PCS Data Import to DMR_LOADINGS_PERM_FEATURES Table ....................... 3-7

Table 3

- 3. PCS Data Import to DMR_LOADINGS_LIMITS Table .......................................... 3-8

Table 3

- 4. PCS Data Import to DMR_LOADINGS_DMRS Table ............................................ 3-9

Table 3 -5. Actual Number of Days per Monitoring Period ....................................................... 3-13

Table 3 -6. Example Type 1 Flow Correction ............................................................................ 3-15

Table 3 -7. Example Type 2 Flow Correction ............................................................................ 3-17

Table 3

- 8. Measurement Value Selection Priorities and Calculations ...................................... 3-25

Table 3

- 9. Flow Value Selection Priorities ................................................................................ 3-27

Table 3 -10. Priorities for Nitrogen Aggregation ........................................................................ 3-30

Table 3 -11. Priorities for Phosphorus Aggregation ................................................................... 3-32

Table 3 -12. Example Reco rds from DMR_LOADINGS_PARM_CAT_XWALK .................. 3-40

Table 3 -13. Aluminum and Copper Groups Assigned Priorities a 

### ............................................. 3-41

Table 3 -14. Group an d Category Priorities for Parameters in an Example Metal Group .......... 3-42

Table 3 -15. Lead and Benzene Groups Assigned Priorities a 

### .................................................... 3-42

Table 3 -16. Example Priority Pollutant Parameter Groups and Category Priorities ................. 3-43

Table 3 -17. Suspended Solids Parameter Pollutant Groups and Assigned Priorities ................ 3-44DRAFT List of Tables



### (Continued)

Page

iv Table 3 -18. Organic Enrichment Parameter Pollutant Groups and Assigned Priorities ............ 3-45

Table 3 -19. Pathogen Indicator Parameter Pollutant Groups an d Assigned Priorities .............. 3-47

Table 4 -1. EZ Search Results Tables ........................................................................................... 4-5

Table 4

- 2. TRI Search Results Tables ....................................................................................... 4-17

Table 4 -3. Advanced Search Fields ........................................................................................... 4-34

Table 4 -4. Advanced Search Results for Monitoring Period Loads .......................................... 4-37

Table 4 -5. Advanced Search Results for Annual Loads ............................................................ 4-39

Table 4 -6. Advanced Search Results for Facility Loads ............................................................ 4-42

Table 5 -1. Summary of Facility Counts ....................................................................................... 5-2

Table 5 -2. Summary of Parameter Counts ................................................................................... 5-3

Table 5 -3. Automatic Flow Corrections ...................................................................................... 5-4

Table 5 -4. Automatic Mercury and Dioxin Corrections .............................................................. 5-4

Table 5 -5. Summary of TRI Facility Counts and Discharges ...................................................... 5-5

Table 5

- 6. Manual Corrections to Data in DMR_LOADINGS_CONVERT_DMR ................... 5-7

Table 5 -7. Manual Corrections to Data in DMR_LOADINGS_TRI_ RELEASES ..................... 5-8DRAFT List of Figures



### v LIST O F FIGURES

Page

Figure 1 -1. DMR Pollutant Loading Tool Data Flow Chart ........................................................ 1-1

Figure 2 -1. ICIS Data Table Relationships ................................................................................ 2-19

Figure 2 -2. PCS Data Table Relationships ................................................................................ 2-20

Figure 2 -3. TRI Data Table Relationships ................................................................................. 2-21

Figure 3 -1. Relationship Diagram for Convert Module Output .................................................. 3-2

Figu re 3 -2. Convert Module Calculation Steps ........................................................................... 3-3

Figure 3 -3. Relationship Diagram for Interim ICIS Tables and Reference Tables ..................... 3-6

Figure 3 -4. Example STAT5 Code in CONVERT_DMR Table ............................................... 3-11

Figure 3 -5. Convert Module Crosstab Tables ............................................................................ 3-11

Figure 3 -6. Load Calculator Module Output Tables and Relationships .................................... 3-35

Figure 3

- 7. EZ Search Back

- End Database

............................................................................... 3-36

Figure 3

- 8. Database Table Relationships for Pollutant Categories

.......................................... 3-39

Figure 3

- 9. TRI Search Back

- End Database

.............................................................................. 3-50

Figure 4

- 1. EZ Search Start Page

................................................................................................. 4-2

Figure 4

- 2. Example EZ Search Results – Search Statistics Table

.............................................. 4-6

Figure 4

- 3. Example EZ Search Results – Top Pollutants Tables ............................................... 4-7

Figure 4

- 4. Example EZ Search Results – Top SIC Code Discharges Tables (Pollutant not Specified by User)

............................................................................................................ 4-8

Figure 4

- 5. Example EZ Search Results – Top SIC Code Discharges Table (Pollutant is Specified by User)

............................................................................................................ 4-9

Figure 4

- 6. Example EZ Search Results – Top Watershed Discharges Tables (Pollutant not Specified by User)

................................................................................................... 4-11

Figure 4

- 7. Example EZ Search Results – Top Receiving Watersheds Table (Pollutant is Specified by User)

.......................................................................................................... 4-12

Figure 4

- 8. E xample EZ Search Results – Top Facility Discharges Tables (Pollutant not Specified by User)

.......................................................................................................... 4-12DRAFT List of Figures



### (Continued)

Page

vi Figure 4

- 9. Example EZ Search Results – Top Facility Discharges Table (Pollutant is Specified by User)

.......................................................................................................... 4-13

Figure 4

- 10. TRI Search Page

.................................................................................................... 4-14

Figure 4 -11. Example TRI Search Results – Top Chemicals Tables

........................................ 4-18

Figure 4 -12. Example TRI Search Results – Top NAICS Code Discharges Tables (Pollutant not Specified by User)

................................................................................... 4-19

Figure 4 -13. Example TRI Search Results – Top NAICS Discharges Table (Pollutant is Specified by User)

.......................................................................................................... 4-19

Figure 4 -14. Example TRI Search Results – Top Watershed Discharges Tables (Pollutant not Specified by User)

................................................................................................... 4-21

Figure 4 -15. Example TRI Search Results – Top Receiving Watersheds Table (Pollutant is Specified by User)

...................................................................................................... 4-21

Figure 4 -16. Example TRI Search Results – Top Facility Discharges Tables (Pollutant not Specified by User)

................................................................................................... 4-23

Figure 4 -17. Example TRI Search Results – Top Facility Discharges Table (Pollutant is Specified by User)

.......................................................................................................... 4-23

Figure 4-18. Loading Tool Database Design for Linking DMR and TRI Data ......................... 4-24

Figure 4 -19. Example Compare to TRI Page – EZ Search Top Facility Results for Single Facility

........................................................................................................................... 4-25

Figure 4 -20. Example Compar e to DMR Page – TRI Search Top Facility Discharges Table 4-25

Figure 4 -21. Facility Search Page .............................................................................................. 4-27

Figure 4

- 22. Example Facility Search Results for Multiple Facility Matches

.......................... 4-27

Figure 4

- 23. Example Detailed Facility Report (DMR)

............................................................ 4-30

Figure 4

- 24. Example Detailed Facility Report (TRI)

............................................................... 4-31

Figure 4 -25. Advanced Search Page .......................................................................................... 4-33

Figure 5 -1. Flow Chart of the Review Steps................................................................................ 5-1

Figure 5

- 2. “Effluent Charts” Link on Facility Specific Page

..................................................... 5-9DRAFT List of Figures



### (Continued)

P age

vii Figure 5

- 3. “Effluent Charts” Link on EZ Search Results Page (see purple “E” graphic)

........ 5-10

Figure 5 -4. Submit an Error Button on Effluent Charts ............................................................. 5-10

Figure 5 -5. Discharge Monitoring Data Error Reporting Form ................................................. 5-11

Figure 5

- 6. EPA’s Data Error Corre ction Process

..................................................................... 5-12DRAFT List of Acronyms



### viii LIST O F ACRONYMS

AAA Anytime Anywhere Access API Application Programming Interface BASINS Better Assessment Science Integrating point and Non -point Sources BDL Below Detection Limit BOD Biochemical Oxygen Demand BOD 5 

### 5- Day Biochemical Oxygen Demand CAS Chemical Abstract Service CBOD Carbonaceous Biochemical Oxygen Demand CG Construction Grants COD Chemical Oxygen Demand CSO Combined Sewer Overflow CSV Comma Separated Value CWA ISA Clean Water Act Indian Set Aside CWNS ID Clean Watersheds Needs Survey ID DL Detection Limit DMR Discharge Monitoring Report EAD Office of Water/Office of Science and Technology/Environmental Accountability Division ECHO Enforcement and Compliance History Online ED S Effluent Data Statistics ELGs Effluent Limitations Guidelines and Standards EPA Environmental Protection Agency ERG Eastern Research Group EST Estimation Function ETDD Office of Enforcement and Compliance Assurance/Office of Compliance/Enforcement Tar geting and Data Division FC1 Flow Concentration 1 FC2 Flow Concentration 2 FC3 Flow Concentration 3 FQ1 Flow Quantity 1 FQ2 Flow Quantity 2 FRS Federal Registry Service FRS ID Facility Registry System ID GIS Geographic Information System GPD Gallon s Per Day GPRA Government Performance and Results Act HAWQS Hydrologic and Water Quality Systems HUC Hydrologic Unit Code HUC -12 12-digit Hydrologic Unit Code ICIS Integrated Compliance Information System IDEA Integrated Data and Enforcement IT Informa tion Technology LC Concentration Limit LOL Load Over Limit LQ Quantity Limit MC1 Concentration 1DRAFT List of Acronyms



### (C ontinued)

ix MC2 Concentration 2 MC3 Concentration 3 MG Million Gallons MGD Millions of Gallons per Day MP&M Metal Products and Machinery MQ1 Quantity 1 MQ2 Quanti ty 2 N Nitrogen NAAS Network Authentication and Authorization Services NAICS North American Industry Classification System NBOD Nitrogenous Biochemical Oxygen Demand ND Nondetects NHD National Hydrography Database NODI No Data Indicator NPDES National Pollutant Discharge Elimination System O&G Oil and Grease OCPSF Organic Chemicals, Plastics, and Synthetic Fibers OECA Office of Enforcement and Compliance Assurance OW Office of Water P Phosphorus PART Performance Assessment Rating Tool PCS Permit Compliance System PMO Office of Water/ Management and Operations Staff/Program Management Office POTW Publicly Owned Treatment Works PSC Point Source Category QA Quality Assurance QC Quality Control SHPD Office of Water/Office of Science and Technology/ Standards and Health Protection Division SIC Standard Industrial Classification SPARROW Spatial Referenced Regressions on Watershed Attributes SRS System Registry Service SSO Separate Sewer Overflow TKN Total Kjeldahl Nitrogen TMDL Total Maximum Dail y Load TRI ID Toxics Release Inventory TRI ID TSS Total Suspended Solids TWF Toxic Weighting Factor TWPE Toxic -Weighted Pound Equivalent USGS United States Geological Survey WATERS Watershed Assessment, Tracking, and Environmental Results WebCMS Web C ontent Management System WPD Office of Water/Office of Wastewater Management/Water Permits Division MSD Office of Water/Office of Waste Management/Municipal Support Division XML Extensible Markup LanguageDRAFT Section 1 - Introduction



### 1-1 1. I NTRODUCTION



### EPA’s Office of Water Engineering and Analysis Division (EAD) required a tool to calculate wastewater pollutant discharges to use in its annual reviews of existing effluent guidelines and to support publication of its biennial Clean Water Act Section 304(m) Plan. Facilities report discharg es on Discharge Monitoring Reports (DMRs), which their permitting authorities record into EPA databases. From 2003 to 2009 EAD used pollutant discharge data from the Permit Compliance System (PCS) and PCS pollutant loading tools (i.e., the Effluent Data St atistics (EDS) System and the PCS Load Calculator) as part of its annual reviews. However, EPA is currently upgrading PCS to a new national database of record called the Integrated Compliance Information System- National Pollutant Discharge Elimination Syst em (ICIS

- NPDES). ICIS

- NPDES data structures are significantly different from the legacy PCS data structures. Consequently, the ICIS-NPDES database is not compatible with the current PCS loading tools and EAD required a new tool.

Although PCS is expected to be fully phased out in the next few years, approximately two dozen NPDES permitting authorities are still using PCS to record compliance data, including DMRs. Meanwhile, other states and regions phased into using ICIS-NPDES in 2006. The migration from PC S to ICIS -NPDES included current and historical DMR data. As a result, EPA’s nationwide “view” of effluent discharges is currently stored in two databases: PCS and ICIS -NPDES. EPA developed the DMR Pollutant Loading Tool to calculate loadings using DMR dat a stored in both PCS and ICIS-NPDES to provide users access to a nationwide view of wastewater pollutant loadings.

In addition to EPA’s obligations to review its effluent guidelines EPA recently announced through its “Clean Water Act Action Plan” a new ap proach for collecting DMR data from all NPDES permitted facilities. 1 

### This new tool is part of this effort to increase the availability and utility of DMR data.

The purpose of this report is to document the DMR Pollutant Loading Tool development by describi ng the objectives and requirements, development procedure, calculation methodologies, user querying capabilities, and quality assurance steps.

1.1 Purpose of Loading Tool

The DMR Pollutant Loading Tool (referred to as the “Loading Tool” throughout this documen t) calculates annual pollutant loads using a similar methodology to EPA’s EDS System for PCS data. EPA updated the EDS methodology to be compatible with ICIS- NPDES data structures. To incorporate PCS data into the tool, EPA developed a PCS conversion modul e to make the PCS data structures compatible with ICIS -NPDES. As a result, the Loading Tool includes pollutant discharge data from all states. EPA designed the tool to:

• 

### Calculate annual pollutant loads from DMR data in the PCS and ICIS- NPDES databases at t he facility and industry category level;

• 

### Use calculation methodologies that are consistent with methodologies used by EPA’s current PCS pollutant loading tool;

1 See: http://www.epa.gov/oecaerth/civil/cwa/cwaenfplan.htm l.DRAFT Section 1 - Introduction



### 1-2 • 

### Create a module that converts PCS data into formats consistent with the ICIS

NPDES data to allo w processing of PCS DMR data through the ICIS- NPDES loading tool;

• 

### Produce a flat output file (e.g., comma delimited text file) that contains nation

wide, annual pollutant loadings usable by EPA analysts in other programs;

• 

### Provide a web interface for publ ic access to DMR data allowing for better transparency and reproducibility of EPA’s pollutant discharge estimates and its annual reviews;

• 

### Provide a web interface for public access to TRI data allowing for comparison to DMR loadings; and

• 

### Provide the public with better linkages between DMR data and other data supporting the Clean Water Act (e.g., Clean Watershed Needs Survey).

1.2 Overview of Loading Tool Architecture

Figure 1 -1 presents a flow chart for the Loading Tool and user interfaces. The Loading Tool consists of four calculation modules, a backend Oracle 10G database, and a web interface. The following is a description of the Loading Tool components:

• 

### Annual ICIS Extract Module. This module extracts the necessary data fields from approximately 20 ICIS d atabase tables and 4 PCS database tables and stores the data in five denormalized tables

.

• 

### PCS and ICIS-NPDES Extract Database Tables in text database (tbd) form (input to Convert Module). at

— Interim database tables containing ICIS

- NPDES data required for loading tool;

— Reference tables developed by EPA; and

— PCS Extrac t Database Tables in tbd format.

• 

### Convert Module. The Convert Module converts the ICIS- NPDES and PCS DMR data into standard units of milligrams per liter, kilograms per day, and millions of gal lons per day; and creates the DMR_LOADINGS_CONVERT_DMR table.

• 

### Load Calculator Module. The Load Calculator Module selects pollutant measurements and wastewater flows from the DMR_LOADINGS_CONVERT_DMR table, calculates monitoring period pollutant loads.

• 

### EZ Search Load Module. The EZ Search Load Module aggregates the pollutant loadings in DMR_LOADINGS_ANNUAL by NPDES ID and pollutant. This module also incorporates calculations to replicate EPA’s 304m Annual Review process and generate point source category rankings.

TRI Search Load Module. T • 

### he TRI Search Load Module converts the TRI data into standard units of pounds per year and incorporates calculations to replicateDRAFT Section 1 - Introduction



### 1-3 EPA’s 304m Annual Review process and generate point source category rankings.

• 

### Loading Tool Database Tables in tbd Database. This Oracle database stores the
out put tables from the Convert Module, Load Calculator Module, and EZ Search Load Module:

— DM R_LOADINGS_FACILITIES

. This table stores information by unique NPDES permit ID including facilit y name, location, facility type, latitude/longitude, and primary industrial activity (SIC and NAICS codes).

— 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 pollutant codes, CAS numbers, and Substance Registry Service (SRS) IDs.

— DMR_LOADINGS_CONVERT_DMR. This table stores the combined DMR data from PCS and ICIS-NPDES, includin g pollutant measurements and permit limits.

— DMR_LOADINGS_FLOWS. This table stores the wastewater flow measurements that correspond to the pollutant measurements in the DMR_LOADINGS_CONVERT_DMR table.

— DMR_LOADINGS_WORK. This table stores the pollutant lo adings per monitoring period calculated by the Load Calculator Module.

— DMR_LOADINGS_ANNUAL. This table stores the annual pollutant calculated by the Load Calculator Module.

— DMR_LOADINGS_EZ_SRCH_CALCS. This table stores the pollutant loadings calculated b y the EZ Search Load Module.

— DMR_LOADINGS_EZ_SRCH_FLOWS. This table stores the annual wastewater flows calculated by the EZ Search Load Module.

— DMR_LOADINGS_TRI_RELEASES. This table stores the annual TRI releases calculated by the TRI Search Load Module.

• 

### EZ Search Interface

. The EZ S earch provides users with a simple interface to query pollutant loads by location, watershed, industry, and pollutant. Based on the

user

- entered search criteria, the EZ Search filters the loads in DMR_LOADINGS_EZ_SRCH_CALCS and displays the results in an HTML view.DRAFT Section 1 - Introduction



### 1-4 • 

### Advanced Search Interface. The Advanced Search allows users to conduct a customized query and alter the loading calculation methodology. Based on the user’s criteria, the Advanced Search initiates the Load Calcula tor Module, and provides the results to the user as a downloadable Comma Separated Value (CSV) file.

• 

### TRI Search Interface. The TRI Search provides users with a simple interface to
query TRI releases by location, watershed, industry, and pollutant. Based on the

user

- entered search criteria, the EZ Search filters the loads in DMR_LOADINGS_ TRI_RELEASES and displays the results in an HTML view.

1.3 Development Status and Deployment Schedule

Starting in August 2008, EPA’s Office of Water (OW) worked with EPA’s Of fice of Enforcement and Compliance Assurance (OECA) and EPA’s Office of Environmental Information (OEI) to design a pollutant load calculation tool using both PCS and ICIS- NPDES data. OW completed the development of the load calculations in September 2009, in support of the preliminary 2010 Effluent Guidelines Program Plan . After successful development of the load calculations, OW and its partners developed a web-based version of the tool and its components. The web-based version of the tool was created to provide greater transparency and utility of DMR data in PCS and ICIS- NPDES. EPA initially released the Loading Tool as a beta version on 2 December 2010 with 2007 data to incorporate early public input on the development of the new tool. EPA received 38 comments during the public comment period. EPA reviewed these comments and incorporated suggested enhancements into the tool. See EPA’s Comment Response Document for the December 2010 Beta Release of the Discharge Monitoring Report (DMR) Pollutant Loading Tool for the public comments and EPA responses to these comments. EPA continues to solicit input and suggestions on the tool, which can be sent
to : waterloadings@epa.gov.DRAFT Section 1 - Introduction



### 1-1 ICIS

- NPDES PCS

Convert Program

DMR_LOADINGS_WORK Table Load Calculator Program EZ Search Load Program

DMR_LOADINGS_EZ_SRCH_CALCS Table

User EZ Search Advanced Search DMR data extraction, Convert Program, Load Calculator Program, and EZ Search Load Program are run once annually

Loading Tool Database Tables

Online Search Options

EZ Search Steps:

• User enters search criteria into EZ Search.

• Loadings Tool filters the DMR_LOADINGS_EZ_SRCH_CALCS table based on user’s criteria.

• EZ Search displays results in HTML.

• User may download results as a csv file. Advanced Search Steps:

• User enters calculation criteria in Advanced Search.

• If the user queries monitoring period loads, then the Advanced Search extracts loads from DMR_LOADINGS_WORK that meet the user’s search criteria

• If the user queries annual loads, then the Advanced Search extracts loads from DMR_LOADINGS_ANNUAL that meet the user’s search criteria.

• The Advanced Search prompts user to download the results as a csv file. CSV File Download DMR_LOADINGS_CONVERT_DMR Table DMR_LOADINGS_ANNUAL Table DMR_LOADINGS_TRI_RELEASES Table TRI Search Load Program

TRI

TRI Search TRI data extraction and TRI Search Load

. Program are run once annually

TRI Search Steps:

• User enters search criteria into TRI Search

.

• Loadings Tool filters the DMR_LOADINGS_TRI_RELEASES table based on user’s criteria.

• TRI Search displays results in HTML.

• User may download results as a csv file. CSV File Download



### F igure 1 -1. DMR Pollutant Loading Tool Data Flow ChartDRAFT Section 2 - Requirements Analysis Summary



### 2-1 2. R EQUIREMENTS 

### A NALYSIS 

### S UMMARY



### The requirements analysis was a key step for ensuring efficient use of resources during the Loading Tool development phases. ERG’s development team contacted EPA Loading Tool work group members listed in Table 2-1 to discuss requirements that EPA considered including in the Loading Tool development. The requirements discussed during these contacts are described below.

Table 2

- 1. EPA Loading Tool Work Group Members

Name Organization Carey Johnston, Project Manager EAD
Andrew Schulman ETDD
Steven Ru bin ETDD
Nasrin Lescure ETDD
David Wells ETDD
Pravin Rana PMO
Andrew Yuen PMO
Karen Metchis WPD
Bob Bastian WPD
Jim Carleton SHPD



### EPA considered four options for the Loading Tool, which are summarized in Table 2-2. Option 1 is the most complex and flexible version of the loading tool, Options 2 and 3 are less complex and less flexible than Option 1, and Option 4 is the simplest (but least flexible) version.

EPA’s Loading Tool development plan consisted of t hree phases for implementation:

• 

### Phase 1 a ddressed EAD’s immediate need for a pollutant load calculator to support the 2009 Annual Review. The 2009 Annual Review used calendar year2007 DMR data, some of which are stored in PCS while others are stored in ICISNPDES. EAD’s contractor, ERG, developed and tested the Loading Tool, stored the file on their computer network, and delivered these products to EPA after completing software testing (see Section 5.3).

• 

### Phase 2 involved developing web interface tools to improve the transparency of EPA’s pollutant discharge estimates and its annual reviews. This phase had an additional benefit of making pollutant loadings data available to the public and other EPA programs. EPA released Phase 2 of the Loading Tool as beta on 2 December 2010.

• 

### Phase 3 involved addressing public comments on the beta version of the tool and incorporating new data (e.g., TRI water releases) and new search interfaces to improve public access to the data.DRAFT Section 2 - Requirements Analysis Summary



### 2-2 The Phase I Loading Tool includes the components and user abilities described for Option 4. Although Option 4 is the simplest version of the Loading Tool, it is intended to be a starting point for developing more complex and interactive versions of the Loading Tool. It was important to involve all interested parties early during the requirements analysis so that all necessary calculations could be built into the Loading Tool during Phase I. The requirements analysis for Phase I addressed the following:

• 

### Method of data extraction;

• 

### Calculation methodology and assumptions;

• 

### Information to include in the output file;

• 

### User querying capabilities;

• 

### File posting format (XML, CSV, or both); and

• 

### Location of data repository.DRAFT Section 2 - Requirements Analysis Summary



### 2-3 Table 2

- 2. Summary of Options for Loading Tool Considered in 2008

Option Components User Abilities Pros Cons Option 4 • One

- tim e annual extraction of DMR data

• One

- time annual run of Convert and Load Calculator Modules and storage of annual load data in an output file

• Annual posting of a link to annual loads file on EPA Web site for downloading • Download annual loads database to PC for analysis and use • Least expensive

• Allows for possible quality review of Load Calculator Module output prior to public

access • Minimal features for

users

• Data might be “stale” and out of date because they are extracted once annually from ICIS

NPDES and PC S

Option 3 • One

- time annual extraction of DMR data

• One

- time annual run of Convert and Load Calculator Modules and storage of annual load data in an Oracle relational database

• Integration of annual loads database with Web interface • Query annual loads output by SIC/NAICS, NPDES ID, state, pollutant, receiving stream, etc. • Provides flexibility to users to download only desired annual loads data

• Allows for possible quality review of Load Calculator Module output prior to public

access • Data might be “stale” and out of date because they are extracted once annually from ICIS

NPDES and PCS

• User cannot select calculation options for Load Calculator Module Option 2 • One

- time annual extraction of DMR data

• One

- time annual run of Convert Module and storage of converted D MR data in an Oracle relational database

• Integration of Load Calculator Module with Web interface • Select converted DMR data for input to Load Calculator Module based on SIC/NAICS, NPDES ID, state, pollutant, receiving stream, etc.

• Specify calculation optio ns for Load Calculator Module

• Query annual load output • Provides a robust and feature

rich solution

• Allows for possible public access to the loading tool

• Allows for possible quality review of Convert Module output prior to public access • Data might be “stale ” and out of date because they are extracted once annually from ICIS

NPDES and PCS

Option 1 • Real

- time extraction of DMR data

• Temporary file storage on IDEA, Envirofacts, or new Web interface

• Integration of Convert and Load Calculator Modules with Web inte rface • Select DMR data for input to Convert and Load Calculator Modules based on SIC/NAICS, NPDES ID, state, pollutant, receiving stream, etc.

• Specify calculation options for Load Calculator Module

• Query annual load output • Provides a robust and feature

rich solution

• Provides for most up

- to

- date DMR data

• Takes advantage of the Exchange Network

• Allows for possible public access of the loading tool • Most expensive

• Because public has direct access to ICIS

NPDES data, EPA would need to rely on the quality of the d ata in ICIS

- NPDES

• Public cannot have real time access to PCS dataDRAFT Section 2 - Requirements Analysis Summary



### 2-4 The Phase I tool requirements were to meet, at a minimum, EAD’s requirements for the 304m Effluent Guidelines Planning Process, which included:

• 

### Annual loads that are calculated from DMR data for calendar year 2007.

• 

### Calculation methodologies that are consistent with methodologies used by EPA’s 2009 version of the PCS pollutant loading tool.

• 

### A comma

- delimited output file that can be incorporated into EAD’s Microsoft AccessTM Annual Review Databases.

For Phase 2, EPA selected Option 3 after consideration of user requirements summarized in Section 2.1 and the Loading Tool project budget and schedule. EPA selected the Loading Tool deployment environment following option development and work group interviews.

2.1 L oading Tool EPA Work Group Interviews

Previously, EAD had established its requirements for the Loading Tool as indicated in the second column of the tables. Because users outside EAD have different needs for the loading tool, additional requirements were identified during meetings with WPD, MSD, PMO and ETDD. The requirements discussed in these meetings are presented below and tabulated in the third column of the tables.

2.1.1 Office of Water/Office of Wastewater Management/Water Permits Division ( WPD)

WPD provides national program direction to the NPDES permit, pretreatment, and sewage sludge management programs. WPD develops regulations, policy, guidance, and national strategies and provides implementation management, compliance assurance, and ove rview of regional and state operations. WPD may use the Loading Tool to support the following activities:

• 

### Annual Government Performance and Results Act (GPRA) reporting, which includes the Performance Assessment Rating Tool (PART). The PART measures program success by calculating the loads of pollutants reduced divided by the dollars invested into the program.

• 

### Estimating the effectiveness of the NPDES program by examining the relationship between the number of permits issued and the change in pollutant dis charges.

• 

### Evaluating discharges to impaired water bodies to help EPA’s Office of Wetlands, Oceans, and Watersheds identify impaired waters that can be removed from the Total Maximum Daily Load (TMDL) list.

Currently, WPD uses its own computer models to calculate the PART and conduct various other assessments of program effectiveness. These models rely on many assumptions, which introduce uncertainties into the models and, therefore, the PART. Accordingly, WPD would like to bolster the current models with more specific information from the Loading Tool.

Scope of Data (Table 2-3)

• 

### Facility information: WPD’s analyses include schedules from current and expired NPDES permits; therefore information about both current and expired permits is necessary for their an alysis. Terminated permit schedules, however, are not usedDRAFT Section 2 - Requirements Analysis Summary



### 2-5 in WPD’s analysis and are not included. Inactive facilities do not need to be included; if they are included they should be labeled as inactive.

• 

### Discharge information: WPD requires permit feature (outfall) latitude and longitude so that discharges can be linked to receiving streams. Other WPD requirements include data for pretreaters discharging to POTWs and combined sewer overflow (CSO), separate sewer overflow (SSO), and stormwater event reporting information.

• 

### Pollutants: With increased focus on nationwide nutrient monitoring, WPD requested the inclusion of aggregate nitrogen and phosphorus loads in the tool output.

Calculation Methodology and Output File (Tables 2-4 and 2-5)

• 

### In addition to the total annual load, WPD requested that the loading tool calculate the annual average concentration and maximum daily load.

• 

### WPD indicated that, for some pollutants such as mercury, it may be useful to calculate the pollutant load assuming nondetects (ND) are equal to the detection limit (DL) concentration. Therefore, WPD requested that ND=DL be added to the detection limit options in the calculator.

• 

### WPD did not specify an output file format.

Querying Capabilities (Table 2 -6)

• 

### WPD requested the ability to query on permit feature latitude and longitude, since this is the primary method of identifying receiving streams and watersheds.

• 

### WPD requested that the interface be designed to allow users to query on multiple NPDES permit IDs in one query.

2.1.2 Office of Water/O ffice of Wastewater Management/Municipal Support Division (MSD)

MSD manages the construction grants (CG), CWA Indian Set Aside (CWA ISA), special appropriations acts, and Clean Water State Revolving Fund programs, and the ongoing oversight of these assista nce programs.
MSD plans to use the Loading Tool to analyze nationwide discharge data for publicly owned treatment works (POTW). The purpose of these analyses is to compare discharge trends over time and evaluate the long

- term effects of permit limits. Ther efore, in addition to loads for calendar year 2007, loads for previous years would be useful. A previous MSD study reviewed PCS data for wastewater treatment plants and water quality monitoring data in EPA’s STORET database. This earlier study focused on b iochemical oxygen demand (BOD) and reviewed discharges from the mid -1960s to mid-1990s. MSD would like to continue to monitor POTW discharges on a national scale using loads calculated from DMR data. One key aspect of MSD’s analyses is connecting pollutant parameters in POTW discharges to water quality issues (e.g., BOD and dissolved oxygen). MSD stated that the Loading Tool should be available to collaborators outside EPA, such as contractors, academics, and other research organizations.DRAFT Section 2 - Requirements Analysis Summary



### 2-6 Scope of Data (Tab le 2 -3)

• 

### Reporting year: Because MSD analyzes trends in POTW discharges, MSD requested that, in addition to calendar year 2007 loads, the loading tool calculate loads for years prior to 2007.

• 

### Facility information: In addition to direct discharges, MSD requi res monitoring data for pretreated discharge to POTWs.

• 

### Permit information: MSD requested that NPDES permit limits be included in the output file.

• 

### Discharge information: In addition to pollutant loads, MSD requested that the output include average discharge stream conditions (temperature and pH). MSD also suggested including measurements of acute and chronic toxicity as a means of evaluating the toxicity of discharges (alternative to toxic -weighted pounds).

• 

### Pollutants: Like WPD, MSD expressed much interest in aggregate nutrient loads. Since MSD promotes the beneficial use of biosolids and oversees biosolids management, MSD requested that EAD consider calculating loads for pollutant concentrations measured in biosolids. MSD also indicated an interest in CSO/SS O and stormwater event information. However, MSD stated that analyses of these events are not routine.

Calculation Methodology and Output File (Tables 2-4 and 2-5)

• 

### In addition to the total annual load, MSD requires that the loading tool calculate
average daily flow, average daily pollutant load, and monthly and annual average pollutant concentration.

• 

### MSD requested that the output include average discharge stream conditions (temperature and pH).

• 

### MSD did not specify an output file format.

Querying Capabil ities (Table 2 -6)

• 

### MSD requested the ability to specify the measurement value hierarchy imbedded in the algorithm. For example, the user could prioritize maximum loads over average loads.

• 

### MSD requested the ability to query loads by watershed (using the Hydrologic Basin Unit Code [HUC]) and calculate aggregate basin loads. In addition to annual loads, MSD requested the ability to access loads and concentrations on a monthly basis to account for seasonal changes.

2.1.3 Office of Water/Management and Operations Staff/Program Management Office (PMO)

OW’s Project Management Office (PMO) is a service organization created to support OW information management programs and investments. PMO collaborates with OW programmatic offices in managing software development, complying with Agency standards, exploring opportunities to reduce costs, and helping to expand OW’s knowledge of effective information management solutions for its projects. For the requirements interview, PMODRAFT Section 2 - Requirements Analysis Summary



### 2-7 discussed how the Loading Tool output can be used in combination with the Hydrologic and Water Quality Systems (HAWQS) modeling program.

The HAWQS program models discharges for a variety of scenarios, including surface runoff. The program extracts data from USGS’s National Hydrography Database (NHD/NHDplus ) and integrates the data with USGS’s Spatial Referenced Regressions on Watershed Attributes (SPARROW), a surface water quality model. PMO may use the output from the Loading Tool as a means of determining point source contributions to waterbody pollutant loads. At the time of the interview, PMO stated that the HAWQS model was under development and that a prototype tool would be released in 2009.

Scope of Data (Table 2-3)

• 

### Reporting year: To observe trends over time, PMO requested that the loading tool calcu late loads for years prior to 2007.

• 

### Permit information: PMO requires NPDES permit limits.

• 

### Discharge information: To determine the point source contributions to receiving streams and to integrate the Loading Tool output with HAWQS, PMO requires information to identify the receiving stream (i.e., HUC, REACH, or stream name). If available and sufficiently populated in ICIS

- NPDES and PCS, PMO requires information on discharge duration, CSO/SSO, and stormwater event report data.

Calculation Methodology and Out put File (Tables 2 -4 and 2-5)

• 

### In addition to the total annual pollutant loads and wastewater flows, PMO requires the Loading Tool to calculate average pollutant concentration, average daily pollutant load, and average daily wastewater flow and include thes e calculations in the output file.

• 

### Facility information: PMO requires Federal Registry Service (FRS) ID numbers for facilities to identify facilities with multiple permits.

Querying Capabilities (Table 2 -6)

• 

### In addition to annual loads, PMO requires the a bility to access calculated monthly loads and concentrations to account for seasonal changes.

2.1.4 Office of Enforcement Targeting and Data Division/Office of Compliance/ Enforcement Targeting and Data Division (ETDD)

ETDD supports national enforcement and compliance information and reporting needs.ETDD is responsible for maintaining enforcement and compliance data systems (including ICISNPDES and PCS), overseeing multimedia data integration systems, reporting results, maintaining online targeting tools for enforcement and compliance, and operating Enforcement and Compliance History Online (ECHO), a public Web tool with historical enforcement and compliance information.DRAFT Section 2 - Requirements Analysis Summary



### 2-8 ETDD plans to use the Loading Tool mainly for inspection targeting. ETDD noted that it plans to input a flat file of the calculated loadings into its Integrated Data and Enforcement (IDEA) system. Therefore, ETDD does not require a user interface.

ETDD not ed that the Web hosting environment that is ultimately selected for the loading tool could limit the tool’s functionality. For example, the Loading Tool may not be able to perform real

- time extractions from ICIS

- NPDES.

Scope of Data (Table 2-3)

• 

### Permit information: ETDD requires that NPDES permit limits be included in the scope of the Loading Tool input.

• 

### Discharge information: CSO/SSO and stormwater event report information would be very useful to ETDD. However, as discussed in meetings with other EPA stake holders, the extent to which this information is populated in ICIS- NPDES and PCS is unknown. ICIS-NPDES supports, but does not require, the entry of CSO/SSO DMR data.

Calculation Methodology and Output File (Tables 2-4 and 2-5)

• 

### ETDD stated that it supports the use of EAD’s current Loading Tool methodology because this methodology is based on OECA’s Effluent Data Statistics System.

• 

### ETDD requested that the Loading Tool compare loads to permit limits by calculating a load over limit. This calculation is the sum of the monthly differences between the average load and the permit limit for each pollutant.

• 

### ETDD requested that the Loading Tool track whether loads are calculated using a mass quantity or flow and concentration data, and to provide this information in the output file.

• 

### ETDD stated that, for its intended use, it requires an annual update file similar to the file described for Option 4.

Querying Capabilities (Table 2 -6)

• 

### ETDD stated that it does not require and would not use an interface to access load s from the tool.

2.1.5 Office of Water/Office of Science and Technology/Standards and Health Protection Division (SHPD)

The Standards and Health Protection Division (SHPD) directs the national program for adoption of state and tribal water quality standards. It develops policies and guidance and assists EPA regional offices and states in adopting appropriate uses, water quality criteria, and antidegradation protection for specific water bodies. SHPD also helps states and EPA Regions develop total maximum daily l oads (TMDLs) to meet water quality standards.

SHPD may use the loading tool for integration of DMR data into BASINS (Better Assessment Science Integrating point and Non-point Sources), a Decision Support System that integrates geographic information system (GIS), hydrologic, and water quality data, with watershed and water quality models. The information in the output file of the Loading Tool mayDRAFT Section 2 - Requirements Analysis Summary



### 2-9 be used as point source data for input into these models. BASINS provides access to NHD Plus data with its “adde d attributes” and currently operates within an open- source GIS platform that is non-proprietary and free for anyone to download.

At the time of the meeting, SHPD was uncertain as to the specific requirements of the Loading Tool for integration with BASINS, or whether the loads will be useful for the BASINS user community. However, SHPD did identify the following as likely requirements:

• 

### Loads calculated on the highest possible level of temporal detail (monthly averages), for consistency with requirements of watershed and water quality models.

• 

### Effluent flow information at the same level of detail.

• 

### REACH number.

• 

### Permit feature latitude/longitude.

• 

### Distinction of quantitation limits and detection limits in the calculation methodology.

SHPD was unsure whether a l oadings tool was needed for integrating BASINS with DMR data, and did not provide detailed requirements.

2.1.6 Requirements Summary

Tables 2 -3 through 2- 6 tabulate the results of the requirements analysis interviews.

Table 2

- 3. Scope of Data to Include in Load ing Tool

Data Element/Scope Included in EAD Requirements Additional Requirements
Year 2007 data X Data prior to 2007 MSD, PMO Facilities (majors, minors, and POTWs) X Pretreaters (discharges to POTWs) a WPD, MSD Facility information (name, state , zip code, latitude, longitude, SIC/NAICS) X

Receiving stream identifier (HUC, REACH, receiving stream name) MSD, PMO, SHPD NPDES permit limits PMO, ETDD NPDES permit schedule WPD, ETDD Effluent monitoring data X Duration of discharge X PMO

Po llutant parameters (nutrients, toxics, conventionals, nonconventionals) X

Bioassays for acute and chronic toxicity MSD Pollutant parameters indicating wastewater conditions (temperature, pH) MSD

Stormwater event report information WPD, PMO, ETDD

C SO event report information WPD, PMO, ETDDDRAFT Section 2 - Requirements Analysis Summary



### 2-10 Table 2

- 3. Scope of Data to Include in Load ing Tool

Data Element/Scope Included in EAD Requirements Additional Requirements SSO event report information WPD, PMO Biosolids monitoring MSD



### Table 2

- 4. Loading Tool Calculation Methodology and Assumptions

Calculation Method/Assumption Included in EAD Requirements Additional Requir ements Select only effluent measurements X Prioritize average values over maxima X Calculate monthly load assuming discharge occurs for 30 days per month X

Calculate load options for measurements that were below the detection limit X

Option 1: non detects = 0 X Option 2: nondetects = 1⁄2 detection limit concentration X Option 3: nondetects = detection limit concentration WPD Estimate discharges for months where DMR data are missing (use “no data” indicator to distinguish between missing data and periods of no discharge) X

Calculate average concentration on annual basis MSD, PMO, WPD Calculate average daily load on annual basis MSD, PMO, WPD Calculate average daily flow on annual basis MSD, PMO, WPD Calculate average concentration on month ly basis MSD, PMO Calculate average daily load on monthly basis MSD, PMO Calculate average daily flow on monthly basis MSD, PMO Calculate aggregate nitrogen and phosphorus loads MSD, WPD Compare loads to NPDES permit limits (load over limit calcula tion) ETDD Calculate pollutant loads for biosolids a MSD Calculate pollutant loads for stormwater events, CSOs, and SSOs a WPD, PMO, ETDD

a – ERG has not yet developed a methodology to calculate annual loads for biosolids, stormwater events, CSOs, or SSOs.DRAFT Section 2 - Requirements Analysis Summary



### 2-11 Table 2

- 5. Loading Tool Output File

Data Field Included in EAD Requirements Additional Requirements

Year X NPDES permit ID X
FRS ID number PMO
SIC/NAICS code X
State X EPA Region County Major/minor status indicator for facility X Facility latitude/longitude X Receiving stream, REACH number MSD Permit feature ID (outfall/pipe number) X Permit feature latitude/longitude WPD, SHPD Monitoring location code X Parameter code (pollutant identifier) X Limit set designator Annual loads calculated by setting nondetects to zero, to 1⁄2 the detection limit, and equal to the detection limit (kg/yr). X

Annual loads calculated with and without estimating discharges for months missing data (kg/yr) X

Annual flow (millions of gal lons per year) X Average annual stream conditions: temperature, pH MSD Monthly stream conditions: temperature, pH MSD Annual averages: pollutant concentration, load, and wastewater flow MSD, PMO, WPD Monthly averages: pollutant concentration, load, and wastewater flow MSD, PMO Aggregate nitrogen and phosphorus loads MSD, WPD Load over limit comparison ETDD Annual pollutant loads for biosolids MSD Annual pollutant loads for stormwater events, CSOs, and SSOs WPD, PMO, ETDD Data flags/indicat ors: BDL flags, estimator flags, measurement values used for loading calculation X ETDDDRAFT Section 2 - Requirements Analysis Summar y



### 2-12 Table 2

- 6. User Querying Capabilities

Querying Options/User Specifications Included in EAD Requirements Additional Requirements

Year X NPDES permit ID X
SIC/NA ICS code X
State X EPA region County Select majors only, minors only, or both majors and minors X Receiving stream/watershed (HUC, REACH, receiving stream name) MSD Permit feature ID (outfall/pipe number) X Permit feature latitude/longitude WPD, SHPD Monitoring location code X Parameter code (pollutant identifier) X Option to select loads calculated by setting nondetects to zero or 1⁄2 the detection limit, or both X

Option to turn estimator for missing DMR data on or off X Ability to specify measurement value hierarchy MSD Ability to query on multiple permit IDs WPD Ability to access calculated monthly loads/concentrations in one
query MSD, PMO



### 2.1.7 Requirements Selection and Loading Tool Options Assessment

Table 2

- 7 lists the Loadi ng Tool requirements specified by the workgroup that EPA selected for the scope of the Loading Tool development. The table also indicates which Loading Tool options address each requirement. As shown in the table, Option 3 meets the majority of the

user re quirements. Option 2 provides the additional function of allowing users access to monthly loads, concentrations, and flows. EPA determined that Option 1 does not provide any additional functionality over Option 2 for meeting user requirements. EPA selected Option 3 for the Loading Tool Development to meet performance needs for the web interface. However, because work group members expressed the importance of having access to monitoring period loads to assess seasonal variability, EPA altered Option 3 to provide users with access to the monthly loads.DRAFT Section 2 - Requirements Analysis Summary



### 2-13 Table 2

- 7. Comparison of Loading Tool Options and User Requirements

Requirement Option 4 Option 3 Option 2 Option 1 Scope of Data Input to Loading Tool
Year 2007 data X X X X Data prior to 2007 X X X X
Faci lities (majors, minors, and POTWs) X X X X Pretreaters (discharges to POTWs) X X X X Facility information (name, state, zip code, latitude, longitude, SIC/NAICS) X X X X

Receiving stream identifier (HUC, REACH, receiving stream name) X X X X NPDES per mit limits X X X X NPDES permit schedule X X X X Effluent monitoring data (no internal monitoring points) X X X X Pollutant parameters (nutrients, toxics, conventionals, nonconventionals) X X X X

Pollutant parameters indicating wastewater conditions (t emperature, pH) X X X X

Calculation Methodology and Assumptions Select only effluent measurements X X X X Prioritize average values over maxima X X X X Calculate monthly load assuming discharge occurs for 30 days per month X X X X

Calculate load optio ns for measurements that were below the detection limit X X X X

Option 1: nondetects = 0 X X X X Option 2: nondetects = 1⁄2 Detection Limit Concentration X X X X Estimate discharges for months where DMR data are missing (use “no data” indicator to disting uish between missing data and periods of no discharge) X X X X

Calculate average concentration on annual basis X X X X Calculate average daily load on annual basis X X X X Calculate average daily flow on annual basis X X X X Calculate average concentra tion on monthly basis X X Calculate average daily load on monthly basis X X Calculate average daily flow on monthly basis X X Calculate aggregate nitrogen and phosphorus loads X X X X Compare loads to NPDES permit limits (load over limit calculat ion) X X X X Elements for Output File

Year X X X X NPDES permit ID X X X X
FRS ID X X X X Facility information (name, state, zip code, latitude, longitude, SIC/NAICS) X X X X

Major/minor status indicator for facility X X X XDRAFT Section 2 - Requirements Analysis Summary



### 2-14 Table 2

- 7. Comparison of Loading Tool Options and User Requirements

Requirement Option 4 Option 3 Option 2 Option 1 Receiving stream identif ier (HUC, REACH, receiving stream name) X X X X Permit feature ID (outfall/pipe number) X X X X Permit feature latitude/longitude X X X X Monitoring location code X X X X Parameter code (pollutant identifier) X X X X Limit set designator X X X X

Annu al loads calculated by setting nondetects to zero, to 1⁄2 the detection limit, and equal to the detection limit (kg/yr) X X X X

Annual loads calculated with and without estimating discharges for months missing data (kg/yr) X X X X

Annual flow (millions of gallons per year) X X X X Average annual stream conditions: temperature, pH X X X X Monthly stream conditions: temperature, pH a X X Annual averages: pollutant concentration, load, and wastewater flow X X X X Monthly averages: pollutant concentration , load, and wastewater flow a X X

Aggregate nitrogen and phosphorus loads X X X X Load over limit comparison X X X X User querying capabilities

Year X X X NPDES permit ID X X X
SIC/NAICS code X X X
State X X X Select majors only, minors only, or both majors and minors X X X Receiving stream/watershed (HUC, REACH, receiving stream name) X X X Permit feature ID (outfall/pipe number) X X X Permit feature latitude/longitude X X X Monitoring location code X X X Parameter code (pollutant identifier) X X X Ability to access annual pollutant loads X X X Ability to access monthly pollutant loads X X Option to select loads calculated by setting nondetects to zero or 1⁄2 the detection limit, or both X X X

Option to turn estimator for mi ssing DMR data on or off X X X

a – EPA altered Option 3 to include these requirements.



### Due to budget and schedule limitations, EPA did not include the following tool features, suggested by EPA ICIS -NPDES Work Group members, in this project’s scope:

• 

### Stormwater event report information;

• 

### CSO event report information;

• 

### SSO event report information;DRAFT Section 2 - Requirements Analysis Summary



### 2-15 • 

### Analysis of toxicity measurements and bioassays;

• 

### Biosolids monitoring; and

• 

### Ability to specify measurement value selection hierarchy for loadings calculations.

Al though the Loading Tool does not include the above- listed information, it includes the necessary information in its output to allow users to link to the program report tables (see Figure 2-1). If the Loading Tool undergoes further development in the future, then future developers will have the necessary links to expand the capabilities of the tool to include stormwater, CSO, SSO, and biosolids program reports.

2.1.8 Requirements for TRI Searches

As part of the Version 1.0 release (Phase 3 of development) , EPA in vestigated options for displaying TRI data alongside DMR data in the Loading Tool. Displaying TRI data in the Loading Tool can help address some of the DMR data limitations. For example, DMR data only include information about discharges of pollutants that a facility is required by permit to monitor. In addition, DMR data do not include information for facilities that discharge to sewage treatment plants or some smaller (“minor”) discharges to surface waters. Conversely, the TRI program collects information on toxic chemical releases regardless of permit requirements, and also collects wastewater discharge data on facilities that discharge to sewage treatment plants.

Similarly, the TRI data have some limitations that may be addressed by displaying side

by

- side with DMR data. For example, TRI does not collect wastewater discharge data on all industrial sectors or all pollutants that degrade water quality, such as nutrients and pathogens. TRI also does not collect information on sewage treatment plant discha rges.

Given the differences between the DMR and TRI datasets, the following lists examples of potential applications to users for displaying TRI wastewater releases side

- by

- side with DMR loadings:

• Quality Assurance/Data Verification: Users can compare the discharge quantities from DMR to TRI. The quantities will not match exactly; however, a difference of several orders of magnitude would indicate a data error in either the DMR or TRI databases.

• Permit Writing: Permit writers can compare the TRI polluta nt universe to the DMR pollutant universe for a particular facility to determine if the facility requires additional permit limits or monitoring requirements.

• Compliance Assurance: Users can compare the DMR pollutant universe to the TRI pollutant universe for a particular facility to determine whether the facility is under-reporting to TRI.

In 2011, EPA interviewed additional stakeholders , listed in Table 2 -8, to determine user requirements for incorporating TRI water release data into the Loading Tool.DRAFT Section 2 - Requirements Analysis Summary



### 2-16 Table 2

- 8. EPA and NGO Stakeholders for TRI Requirements Interviews

Name Organization Carey Johnston, Project Manager EPA/OST/ EAD Wayne Davis EPA HQ
Kara Koehrn EPA HQ
Steve Witkin EPA HQ
Velu Senthil EPA HQ Cory Wagner EPA HQ Timothy Antisdel EP A HQ Nora Lopez EPA Region 2
Mark Tedesco EPA Region 2 Gregory Allen EPA Region 3, Chesapeake Bay Program
Thelma Codina EPA Region 5
Linda McKenzie EPA Region 7
John Dunn EPA Region 7
Gabriela Carvalho EPA Region 10 Tony Dutzig Frontier Group
Shau n Livermore N ational T ribal W ater C ouncil (NTWC)
Kevin Masterson Oregon D epartment of E nvironmental Q uality (DEQ) Jim Billings Oregon DEQ



### The requirements analysis for the addition of TRI data addressed the following:

• 

### Main Mission of the Organization

• 

### Primary Deliverables

• 

### Familiarity with DMR/TRI Data

• 

### Frustrations with Current TRI Data and Ideas for Improvement

• 

### Tools used in Querying and Displaying Data

• 

### Methods of Displaying Trends

• 

### Data Quality Needs

• 

### Ideas for Displaying TRI and DMR Data Together

• 

### Recomm endations for Improving the Current DMR Loading Tool.

The following subsections summarize the results of each topic addressed during the interviews.

Main Mission of Organization

. The stakeholders interviewed had a variety of focuses. These included na tional analysis of discharge data, establishing relationships between TRI data and other data sets, analyzing data for outliers/potential errors, tracking trends, programming, and enforcement of non reporters. One stakeholder also stated that he uses the d ata sources toDRAFT Section 2 - Requirements Analysis Summary



### 2-17 advance general knowledge of the pathways of toxic chemicals. Stakeholders provided the following example studies:

• Long Island Sound (LIS) Study: Reviews discharges of nutrients, toxic chemicals, and pathogens, and discusses impacts to aqua tic life in the LIS. Presents information on each topic in a one-pager with visual data displays including bar charts and maps.

• Wasting our Waterways Report: Nationwide study of toxic releases to U.S. waters. Links discharge information to public health co ncerns. Presents nationwide data using shaded maps to indicate magnitude of toxic chemical discharges for each state.

• TRI National Analysis: Presents nationwide TRI releases to air, water, land, underground injection, and offsite disposal. Provides links for nationwide summaries, as well as summaries by state, urban communities, large aquatic ecosystems, and Indian country. 2



### P

rimary Deliverables . Stakeholders provided a wide range of example deliverables. These included public reports, action plans, briefings to management, and internal tracking and calculation spreadsheets. Most deliverables identified the top facilities and chemicals discharged either in a specific region of the country or nationwide, and most are designed to communicate data to the ge neral public.

Familiarity with DMR/TRI Data

. The stakeholders interviewed ranged from being experts to beginners in DMR and TRI data. Most were not equally familiar with both DMR and TRI, but were at least semi -familiar with one of the data sources.

Ideas for Improving Current Access to TRI Data

. EPA could enhance the usability of the TRI data by showing the facilities that are underreporting or not reporting, integrating a geographic focus, trends and summary statistics, and organizing the data so it is easy to understand. Stakeholders also suggested having watershed pollution totals broken down by state, adding air data by region, and using the TRI database to show how chemicals affect public health. Stakeholders also commented about data constancy among different websites. For example, it was mentioned that the names of many rivers, creeks, POTW’s and chemicals don’t match up throughout the TRI database which makes searches cumbersome.

Tools Used in Querying and Displaying Data. The primary tool u sed for displaying data is spreadsheets. Some stakeholders also use modeling and progress assessment tools.

Methods of Displaying Trends. Mapping displays along with bar and line graphs are the primary methods used to display trends in the data.

Dat a Quality Needs

. The primary data quality need is to have accurate and current data. Sometimes there are errors between what the facility reports and what is shown in the DMR and TRI data which makes the data difficult to work with. Additionally, the data needs to be current so it matches data presented in other websites.

2 http://www.epa.gov/tri/tridata/tri09/nationalanalysis/index.htmDRAFT Section 2 - Requirements Analysis Summary



### 2-18 Ideas for Displaying TRI and DMR Data Together. Th e stakeholders interviewed suggested many ideas for displaying TRI and DMR data together. Overall, the data need to be understandable for the general public and also detailed enough to be useful for advanced users. Several stakeholders suggested map displays as a way to communicate results to the general public. For more advanced users, stakeholders suggested providing a way to easily drill down into the details of the data. One stakeholder also suggested linking chemical hazard information to the pollutants so that the general public can easily see the effects of certain chemicals.

There was a discrepancy between whether the DMR and T RI data should be displayed side by side or on separate pages. Some stakeholders thought that it would be easier to see the data displayed together but with clear notes on why the data may be different. Others thought it would be easier to see the data sep arately. All stakeholders agreed that it is extremely important to highlight the limitations and differences between data sets so that the data is not used for incorrect applications.

Other suggestions included a link to standard reports where users can select a data set and have it displayed in a general template, a frequently asked questions page that highlights the differences between TRI and DMR data, tutorial videos explaining TRI and DMR data (such as the TRI Explorer tutorials found here: http://www.epa.gov/tri/triexplorertutorial/tri_explorer_training.htm ), and linking the data to economic and business research.

Recommendations for Improving the Current DMR Loading Tool . The most prominent suggestion for improving the current Loading Tool was t o add the permit range or total allowable discharge next to the actual discharge by the facility. Other suggestions included adding land and air discharges to the water discharges and adding a color button under each facility saying that they are also TRI reporters.

Requirements Selection. Based on stakeholder input, EPA decided to incorporate TRI data into the Loading Tool in two ways:

1. Provide direct access to TRI data through a simple search with a similar design to the DMR EZ Search; and 2. Provide abilit y to compare DMR EZ Search results to TRI Search results and display the DMR loads side- by -side with TRI data.

2.2 Data Extraction Procedures (ICIS, PCS , and TRI)

To extract ICIS -NPDES DMR data, EPA’s contractor, ERG, obtained Anytime Anywhere Access (AAA) from EPA and restored the data to secure ERG servers. The restored data include the following ICIS tables:

• 

### ICIS_FACILITY_INTEREST;

• 

### XREF_FACILITY_INTEREST_SIC;

• 

### XREF_FACILITY_INTEREST_NAICS;

• 

### ICIS_ACTIVITY;DRAFT Section 2 - Requirements Analysis Summary



### 2-19 • 

### ICIS_PERMIT;

• 

### ICIS_PERMIT_FEATURE;

• 

### ICIS_LIMIT;

• 

### ICIS_LIM IT_SET;

• 

### ICIS_LIMIT_SET_SCHEDULE;

• 

### ICIS_LIMIT_VALUE;

• 

### ICIS_DMR;

• 

### ICIS_DMR_PARAMETER;

• 

### ICIS_DMR_VALUE;

• 

### ICIS_DMR_EVENT;

• 

### ICIS_DMR_FORM; and

• 

### ICIS_DMR_FORM_PARAMETER.

Figure 2 -1 shows the relationships between the ICIS data tables and how these tables can be linked to tables that are out of this project’s scope.

ICIS_FACILITY_INTEREST

ICIS_FACILITY_INTEREST_ID PGM_SYS_ACRNM PRG_SYS_ID FACILITY_UIN FACILITY_TYPE_CODE FACILITY_NAME LOCATION_ADDRESS CITY CITY_CODE COUNTY_CODE STATE_CODE ZIP HUC_CODE EPA_REGION_CODE ICIS_PERMIT

ACTIVITY_ID EXTERNAL_PERMIT_NMBR PERMIT_TYPE_CODE MAJOR_MINOR_STATUS_FLAG

ICIS_ACTIVITY

ACTIVITY_ID ACTIVITY_NAME STATE_CODE FISCAL_YEAR ACTIVITY_STATUS_CODE ACTIVITY_TYPE_CODE ACTUAL_BEGIN_DATE ACTUAL_END_DATE DURATION TOTAL_HOURS ICIS_LIMIT_SET

LIMIT_SET_ID LIMIT_SET_TYPE_CODE LIMIT_SET_DESIGNATOR LIMIT_SET_NAME PERM_FEATURE_ID

ICIS_LIMIT

LIMIT_ID MONITORING_LOCATION_CODE LIMIT_SEASON_ID PARAMETER_CODE LIMIT_SET_ID

REF_POLLUTANT

POLLUTANT_CODE POLLUTANT_DESC CHEMICAL_ABSTRACT_SERVICE_NMBR ICIS_DMR_FORM_PARAMETER

DMR_FORM_PARAMETER_ID DMR_FORM_ID LIMIT_ID PARAMETER_CODE ICIS_DMR_FORM

DMR_FORM_ID DMR_EVENT_ID XREF_FACILITY_INTEREST_SIC

ICIS_FACILITY_INTEREST_ID SIC_CODE PRIMARY_INDICATOR_FLAG

XREF_FACILITY_INTEREST_NAICS

ICIS_FACILITY_INTEREST_ID NAICS_CODE PRIMARY_INDICATOR_FLAG ICIS_LIMIT_VALUE

LIMIT_VALUE_ID LIMIT_ID VALUE_TYPE_CODE UNIT_CODE STATISTICAL_BASE_CODE LIMIT_VALUE_NMBR LIMIT_VALUE_STANDARD_UNITS

ICIS_LIMIT_SET_SCHEDULE

LIMIT_SET_SCHEDULE_ID LIMIT_SET_ID NMBR_OF_REPORT NMBR_OF_SUBMISSION

ICIS_DMR_EVENT

DMR_EVENT_ID LIMIT_SET_SCHEDULE_ID LIMIT_SET_ID DMR_DUE_DATE MONITORING_PERIOD_END_DATE ICIS_PERM_FEATURE

PERM_FEATURE_ID ACTIVITY_ID PERM_FEATURE_NMBR DESIGN_FLOW_NMBR ACTUAL_AVERAGE_FLOW_NMBR WATER_BODY_NAME REACH_ID

ICIS_DMR_FORM_VALUE

DMR_FORM_VALUE_ID DMR_FORM_PARAMETER_ID VALUE_TYPE_CODE LIMIT_VALUE_ID

REF_PARAMETER

PARAMETER_CODE PARAMETER_DESC POLLUTANT_CODE ICIS_DMR_VALUE

DMR_FORM_VALUE_ID DMR_VALUE_ID DMR_PARAMETER_ID NODI_CODE DMR_VALUE_NMBR VALUE_QUALIFIER_CODE UNIT_CODE DMR_VALUE_STANDARD_UNITS ICIS_DMR_VALUECol1

ICIS_PROG_RPT

PROG_RPT_ID ACTIVITY_ID ICIS_PROG_RPT_BIOSOLID

PROG_RPT_ID ANNUAL_DRY_SLUDGE EQ_PRODUCT_AMT LAND_APPLIED_AMT INCINERATED_AMT CO_DISPOSED_AMT SURFACE_DISPOSAL_AMT OS_SOURCE_AMT OTHER_METHOD_AMT TRANSFERRED_AMT ICIS_PROG_RPT_SW

PROG_RPT_ID EVENT_SAMPLED_DATE EVENT_SAMPLED_NMBR DISCHARGE_VOLUME EVENT_SAMPLED_DURATION PERM_FEATURE_ID MONITORING_BASIS_CODE ICIS_PROG_RPT_CSO

PROG_RPT_ID WEATHER_DRY_WET_FLAG OVERFLOW_LOCATION OVERFLOW_DURATION DISCHARGE_VOLUME_TREATED DISCHARGE_VOLUME_UNTREATED PRECIPITATION PERM_FEATURE_ID LATITUDE_MEASURE LONGITUDE_MEASURE ICIS_PROG_RPT_SSO

PROG_RPT_ID OVERFLOW_CAUSE OVERFLOW_LOCATION OVERFLOW_DURATION OVERFLOW_VOLUME RECEIVING_WATER LATITUDE_MEASURE LONGITUDE_MEASURE PERM_FEATURE_ID

ICIS_PROG_RPT_FREQUENCY_SSO

PROG_RPT_ID SSO_EVENT_MONTH SSO_EVENT_YEAR SSO_EVENT_NMBR SSO_EVENT_VOLUME Out of Scope



### Figure 2 -1. ICIS Data Table Relationships

To extract PCS data, ERG downloaded year 2007 DMR data through EPA’s Mainframe. ERG extracted PCS data from the Permit Facility, Pipe Schedule, Parameter Limits, and Measurements/Violations data types. Figure 2 -2 shows the PCS data tables and relationships.DRAFT Section 2 - Requirements Analysis Summary



### 2-20 ERG obtained a Mainframe ID and password and was granted PCS access by EPA. ERG downloaded the PCS files as comma- delimited text files, and imported the tables into the Loading Tool Oracle database. Section 3.1.1 describes EPA’s methodology for merging the PCS files with the ICIS -NPDES data in the Loading Tool.

REPORTED QUANTITY UNIT RUNT REPORTED CONCENTRATION UNIT RCUN QUANTITY MAXIMUM MQMX QUANTITY AVERAGE MQAV NO DATA INDICATOR NODI MONITORING PERIOD END DATE MVDT MEASUREMENT/VIOLATI ON SEASON NUMBER VSEA MEASUREMENT/VIOLATI ON REPORT DESIGNATOR VDRD MEASUREMENT/VIOLATI ON PIPE SET QUALIFIER VIPQ MEASUREMENT/VIOLATI ON PARAMETER CODE VPRM MEASUREMENT/VIOLATI ON MONITORING LOCATION VMLO MEASUREMENT/VIOLATI ON MODIFICATION NUMBER VMOD MEASUREMENT/VIOLATI ON DISCHARGE NUMBER/REPORT DESIGNATOR VDSG MEASUREMENT/VIOLATI ON DISCHARGE NUMBER VDCS MEASUREMENT/VIOLATI ON CODE MVIO CONCENTRATION MINIMUM MCMN CONCENTRATION MAXIMUM MCMX CONCENTRATION AVERAGE MCAV NPDES NUMBER NPID MEASUREMENT VIOLATION

REPORTED QUANTITY UNIT RUNT REPORTED CONCENTRATION UNIT RCUN QUANTITY MAXIMUM MQMX QUANTITY AVERAGE MQAV NO DATA INDICATOR NODI MONITORING PERIOD END DATE MVDT MEASUREMENT/VIOLATI ON SEASON NUMBER VSEA MEASUREMENT/VIOLATI ON REPORT DESIGNATOR VDRD MEASUREMENT/VIOLATI ON PIPE SET QUALIFIER VIPQ MEASUREMENT/VIOLATI ON PARAMETER CODE VPRM MEASUREMENT/VIOLATI ON MONITORING LOCATION VMLO MEASUREMENT/VIOLATI ON MODIFICATION NUMBER VMOD MEASUREMENT/VIOLATI ON DISCHARGE NUMBER/REPORT DESIGNATOR VDSG MEASUREMENT/VIOLATI ON DISCHARGE NUMBER VDCS MEASUREMENT/VIOLATI ON CODE MVIO CONCENTRATION MINIMUM MCMN CONCENTRATION MAXIMUM MCMX CONCENTRATION AVERAGE MCAV NPDES NUMBER NPID MEASUREMENT VIOLATION

STAT

- LIM QUAN MAX BASE CD LQXS STAT

- LIM QUAN AVG BASE CD LQAS STAT

- LIM CONC MIN BASE CD LCMS STAT

- LIM CONC MAX BASE CD LCXS STAT

- LIM CONC AVG BASE CD LCAS SEASON NUMBER SEAN QUANTITY UNIT CODE STANDARD LQSC QUANTITY UNIT CODE LQUC QUANTITY MAXIMUM LIMIT STANDARD LQSX QUANTITY MAXIMUM LIMIT LQMX QUANTITY AVERAGE LIMIT STANDARD LQSA QUANTITY AVERAGE LIMIT LQAV PARAMETER CODE PRAM MONITORING LOCATION MLOC MODIFICATION NUMBER MODN MINIMUM CONCENTRATION LIMIT

- STD UNITS LCSM MINIMUM CONCENTRATION LIMIT LCMN MAXIMUM CONCENTRATION LIMIT

- STD UNITS LCSX MAXIMUM CONCENTRATION LIMIT LCMX LIMIT TYPE (NUMERIC) LNTP LIMIT TYPE (ALPHABETIC) LTYP LIMIT REPORT DESIGNATOR PLRD LIMIT PIPE SET QUALIFIER LIPQ LIMIT DISCHARGE NUMBER PLDS CONCENTRATION UNIT CODE

- STD UNITS LCSC CONCENTRATION UNIT CODE LCUC AVERAGE CONCENTRATION LIMIT

- STD UNITS LCSA AVERAGE CONCENTRATION LIMIT LCAV NPDES NUMBER NPID PARAMETER LIMITS

STAT

- LIM QUAN MAX BASE CD LQXS STAT

- LIM QUAN AVG BASE CD LQAS STAT

- LIM CONC MIN BASE CD LCMS STAT

- LIM CONC MAX BASE CD LCXS STAT

- LIM CONC AVG BASE CD LCAS SEASON NUMBER SEAN QUANTITY UNIT CODE STANDARD LQSC QUANTITY UNIT CODE LQUC QUANTITY MAXIMUM LIMIT STANDARD LQSX QUANTITY MAXIMUM LIMIT LQMX QUANTITY AVERAGE LIMIT STANDARD LQSA QUANTITY AVERAGE LIMIT LQAV PARAMETER CODE PRAM MONITORING LOCATION MLOC MODIFICATION NUMBER MODN MINIMUM CONCENTRATION LIMIT

- STD UNITS LCSM MINIMUM CONCENTRATION LIMIT LCMN MAXIMUM CONCENTRATION LIMIT

- STD UNITS LCSX MAXIMUM CONCENTRATION LIMIT LCMX LIMIT TYPE (NUMERIC) LNTP LIMIT TYPE (ALPHABETIC) LTYP LIMIT REPORT DESIGNATOR PLRD LIMIT PIPE SET QUALIFIER LIPQ LIMIT DISCHARGE NUMBER PLDS CONCENTRATION UNIT CODE

- STD UNITS LCSC CONCENTRATION UNIT CODE LCUC AVERAGE CONCENTRATION LIMIT

- STD UNITS LCSA AVERAGE CONCENTRATION LIMIT LCAV NPDES NUMBER NPID PARAMETER LIMITS

REPORT DESIGNATOR DRID PIPE SET QUALIFIER PIPQ PIPE SCHEDULE USGS HYDROLOGIC BASIN CODE PHBC PIPE SCHEDULE STREAM SEGMENT PSEG PIPE LONGITUDE PLON PIPE LATITUDE/LONGITUDE CODE OF ACCURACY PLLC PIPE LATITUDE PLAT NUMBER OF UNITS IN REPORT NRPU LIMIT DISCHARGE NUMBER/REPORT DESIGNATOR PDSG DISCHARGE NUMBER DSCH NPDES NUMBER NPID PIPE SCHEDULE

REPORT DESIGNATOR DRID PIPE SET QUALIFIER PIPQ PIPE SCHEDULE USGS HYDROLOGIC BASIN CODE PHBC PIPE SCHEDULE STREAM SEGMENT PSEG PIPE LONGITUDE PLON PIPE LATITUDE/LONGITUDE CODE OF ACCURACY PLLC PIPE LATITUDE PLAT NUMBER OF UNITS IN REPORT NRPU LIMIT DISCHARGE NUMBER/REPORT DESIGNATOR PDSG DISCHARGE NUMBER DSCH NPDES NUMBER NPID PIPE SCHEDULE

TYPE OF OWNERSHIP TYPO STATE CODE STTE SIC CODE SIC2 RIVER BASIN CODE (SEGMENT) RSEG RIVER BASIN CODE (MAJOR/MINOR) BAS4 RIVER BASIN BAS6 REGION CODE REGN RECEIVING WATERS RWAT PERMIT TYPE INDICATOR PTYP MAJOR DISCHARGE INDICATOR MADI FEDERAL FACILITY IDENTIFICATION NUMBER FFID FACILITY USGS HUC FHBC FACILITY TYPE INDICATOR FTYP FACILITY NAME FNML FACILITY LONGITUDE FLON FACILITY LOCATION ZIP CODE RZIP FACILITY LATITUDE/LONGITUDE CODE OF ACCURACY FLLC FACILITY LATITUDE FLAT FACILITY INACTIVE CODE IACC COUNTY NAME CNTN COUNTY CODE CNTY CONSOLIDATED SYSTEM FACILITY IDENTIFIER CSDN CITY NAME CYNM CITY CODE CITY AVERAGE DESIGN FLOW FLOW AVERAGE DESIGN FLOW FLOW NPDES NUMBER NPID PERMIT FACILITY

TYPE OF OWNERSHIP TYPO STATE CODE STTE SIC CODE SIC2 RIVER BASIN CODE (SEGMENT) RSEG RIVER BASIN CODE (MAJOR/MINOR) BAS4 RIVER BASIN BAS6 REGION CODE REGN RECEIVING WATERS RWAT PERMIT TYPE INDICATOR PTYP MAJOR DISCHARGE INDICATOR MADI FEDERAL FACILITY IDENTIFICATION NUMBER FFID FACILITY USGS HUC FHBC FACILITY TYPE INDICATOR FTYP FACILITY NAME FNML FACILITY LONGITUDE FLON FACILITY LOCATION ZIP CODE RZIP FACILITY LATITUDE/LONGITUDE CODE OF ACCURACY FLLC FACILITY LATITUDE FLAT FACILITY INACTIVE CODE IACC COUNTY NAME CNTN COUNTY CODE CNTY CONSOLIDATED SYSTEM FACILITY IDENTIFIER CSDN CITY NAME CYNM CITY CODE CITY AVERAGE DESIGN FLOW FLOW AVERAGE DESIGN FLOW FLOW NPDES NUMBER NPID PERMIT FACILITY



### Figure 2 -2. PCS Data Table Relationships

For TRI data, EPA obtains a customized Microsoft Excel file containing the water releases in the data fields shown in Figure 2 -3 , and restores the data to the Loading Tool Oracle database . EPA’s TRI staff constructs the file using summary tables in a copy of the Envirofacts database. Howvever, these tables are not directly available through EPA’s production database for Envirofacts in this format

.DRAFT Section 2 - Requirements Analysis Summary



### 2-21 DMR_LOADINGS_TRI_FACILITIES TRI Facility ID
UIN FACILITY_NAME CITY_NAME COUNTY_NAME STATE_COUNTY_FIPS_CODE STATE_ABBR ZIP_CODE
REGION FAC_LATITUDE FAC_LONGITUDE SIC_CODE NAICS_CODE DMR_LOADINGS_WATER_RELEASES
Year TRI Facility ID Doc_Ctrl_Num Chemical CAS #/Compound ID Congener No. Congener CAS# Congener Unit of Measure
5.3.1 Stream 1 Name 5.3.1 Stream 1 Release
5.3.2 Stream 2 Name 5.3.2 Stream 2 Release
5.3.3 Stream 3 Name 5.3.3 Stream 3 Release
5.3.4 Stream 4 Name 5.3.4 Stream 4 Release
5.3.5 Stream 5 Name 5.3.5 Stream 5 Release
5.3.6 Stream 6 Name 5.3.6 Stream 6 Release
5.3.7 Stream 7 Name 5.3.7 Stream 7 Release
5.3.8 Stream 8 Name 5.3.8 Stream 8 Release
5.3.9 Stream 9 Name 5.3.9 Stream 9 Release
5.3.10 Stream 10 Name 5.3.10 Stream 10 Release 5.3 Total Water Release 6.1 POTW Transfer Amount
6.1 POTW A NAME 6.1 POTW A ADDRESS 6.1 POTW A CITY 6.1 POTW A COUNTY 6.1 POTW A STATE 6.1 POTW A ZIP 6.1 POTW B NAME 6.1 POTW B ADDRESS 6.1 POTW B CITY 6.1 POTW B COUNTY 6.1 POTW B STATE 6.1 POTW B ZIP



### Figure 2 -3. TRI Data Table Relationships

2.3 Repository Selection

EPA ultimately plans to deploy the Loading Tool to the Integrated Data for Enforcement Analysis (IDEA) system

. The IDEA system serves as the back -end database for two EPA web interfaces:

• 

### Online Targeting Information System (OTIS) (available only to EPA, federal, and state users); and

• 

### Enforcement and Compliance History Online (ECHO) (available to public use rs).DRAFT Section 2 - Requirements Analysis Summary



### 2-22 At this time, the IDEA system is not compatible with the Loading Tool. However, EPA is in the process of modernizing IDEA to be compatible with Oracle- based databases

. As a temporary hosting solution, EPA coordinated with OW/PMO, and selected EPA’s W atershed Assessment, Tracking, and Environmental Results (WATERS) database as the temporary data repository for the ICIS-NPDES Pollutant Loading Tool. 3 

### The WATERS database resides in EPA’s CommonSpot Web Content Management System (WebCMS). EPA developed the user interface of the Loading Tool using the following specifications to ensure that the Loading Tool was compatible with CommonSpot:

• 

### ColdFusion 8.1;

• 

### Oracle Database Server 10G; and

• 

### EPA Template 3.2.

3 http://www.epa.gov/waters/about/index.html

.DRAFT Section 3 - Calculator Module Development



### 3-1 3. C ALCULATOR 

### M ODULE 

### D EVELOPMENT



### The Loading Tool cons ists of four calculation modules, a backend Oracle 10G database, and a web interface

. See Figure 1 -1 for a diagram of the Loading Tool components. This section describes the Loading Tool calculation modules and database tables in the following subsections:

• 

### Section 3.1 describes the Convert Module functions and output tables;

• 

### Section 3.2 describes the Load Calculator Module functions;

• 

### Section 3.3 describes the EZ Search Load Module and database tables for the EZ Search; and

• 

### Section 3.4 describes the TRI Search Load Module.

3.1 Convert Module Functions

EPA developed the Convert Module to mimic the functions of the Convert program and EDS System that OECA developed for PCS. The Convert Module creates five database tables that the Load Calculator Module uses as t he input for annual loadings calculations:

• 

### This table contains information by unique external permit number (NPDES permit ID) including facility name, location, facility type, latitude/longitude, and primary industrial activity (SI C and NAICS codes). EPA supplemented the facility information from PCS and 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

e ffluent 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

. T h e 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 uni que permitted feature ID (NPDES Outfall) including outfall number, location, and latitude/longitude.

• 

### LOADINGS_REF_PARAMETER

. This table links parameter codes to Chemical A bstract Service (CAS) numbers, toxic weighting factors (TWFs), and Substance Registry Service (SRS) IDs

. In additi on, 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. DMR_LOADINGS_FACILITIES

.DRAFT Section 3 - Calculator Module Development



### 3-2 Figure 3 -1 presents the database tables and relationships for the Convert Module Output.

DATA_SOURCE

DMR_YEAR

LT_DMRS_XTB_ID

LT_FLOW_ID

AVG_PH AVG_TEMP NMBR_OF_DAYS LOADINGS_ID MC3 MC2 MC1 MQ2 MQ1 C3_QUAL C2_QUAL C1_QUAL Q2_QUAL Q1_QUAL NODI_CODE PARAMETER_CODE MONITORING_LOCATION_CODE NMBR_OF_REPORT MONITORING_PERIOD_END_DATE STAT5 LC3 LC2 LC1 LQ2 LQ1 LIMIT_SEASON_ID LIMIT_SET_DESIGNATOR PERM_FEATURE_NMBR EXTERNAL_PERMIT_NMBR DMR_LOADINGS_CONVERT_DMR

DATA_SOURCE

DMR_YEAR

LT_DMRS_XTB_ID

LT_FLOW_ID

AVG_PH AVG_TEMP NMBR_OF_DAYS LOADINGS_ID MC3 MC2 MC1 MQ2 MQ1 C3_QUAL C2_QUAL C1_QUAL Q2_QUAL Q1_QUAL NODI_CODE PARAMETER_CODE MONITORING_LOCATION_CODE NMBR_OF_REPORT MONITORING_PERIOD_END_DATE STAT5 LC3 LC2 LC1 LQ2 LQ1 LIMIT_SEASON_ID LIMIT_SET_DESIGNATOR PERM_FEATURE_NMBR EXTERNAL_PERMIT_NMBR DMR_LOADINGS_CONVERT_DMR

DATA_SOURCE STATE_WATER_BODY STATE_WATER_BODY_NAME

SOURCE_FEATURE_ID HUC12 HUC_CODE ACTUAL_AVERAGE_FLOW_NMBR

TOTAL_DESIGN_FLOW_NMBR NAICS_CODE

SICCODE SIC_CODE_3 SIC_CODE_2 SIC_CODE_1 FACILITY_TYPE_INDICATOR

PERMIT_TYPE_CODE GEOCODE_LONGITUDE GEOCODE_LATITUDE CONGRESSIONAL_DIST_NUM EPA_REGION_CODE ZIP STATE_CODE COUNTY_NAME COUNTY_CODE CITY FACILITY_NAME MAJOR_MINOR_STATUS_FLAG UIN EXTERNAL_PERMIT_NMBR DMR_LOADINGS_FACILITIES

DATA_SOURCE STATE_WATER_BODY STATE_WATER_BODY_NAME

SOURCE_FEATURE_ID HUC12 HUC_CODE ACTUAL_AVERAGE_FLOW_NMBR

TOTAL_DESIGN_FLOW_NMBR NAICS_CODE

SICCODE SIC_CODE_3 SIC_CODE_2 SIC_CODE_1 FACILITY_TYPE_INDICATOR

PERMIT_TYPE_CODE GEOCODE_LONGITUDE GEOCODE_LATITUDE CONGRESSIONAL_DIST_NUM EPA_REGION_CODE ZIP STATE_CODE COUNTY_NAME COUNTY_CODE CITY FACILITY_NAME MAJOR_MINOR_STATUS_FLAG UIN EXTERNAL_PERMIT_NMBR DMR_LOADINGS_FACILITIES

DATA_SOURCE LONGITUDE_MEASURE LATITUDE_MEASURE STATE_WATER_BODY_NAME REACH_ID WATER_BODY_NAME ACTUAL_AVERAGE_FLOW_NMBR DESIGN_FLOW_NMBR PERM_FEATURE_ID PERM_FEATURE_NMBR EXTERNAL_PERMIT_NMBR DMR_LOADINGS_PERM_FEATURES

DATA_SOURCE LONGITUDE_MEASURE LATITUDE_MEASURE STATE_WATER_BODY_NAME REACH_ID WATER_BODY_NAME ACTUAL_AVERAGE_FLOW_NMBR DESIGN_FLOW_NMBR PERM_FEATURE_ID PERM_FEATURE_NMBR EXTERNAL_PERMIT_NMBR DMR_LOADINGS_PERM_FEATURES

SRS_ID TWF CHEMICAL_ABSTRACT_SERVICE_NMBR PCS_PARAMETER_CODE DELETE_FROM_LOADINGS NUTRIENT_AGG_N_CVT NUTRIENT_AGG_NITRO NUTRIENT_AGG_PHOSP PRIORITY GROUP_CODE POLLUTANT_DESC POLLUTANT_CODE PARAMETER_DESC PARAMETER_CODE DMR_LOADINGS_REF_PARAMETER

SRS_ID TWF CHEMICAL_ABSTRACT_SERVICE_NMBR PCS_PARAMETER_CODE DELETE_FROM_LOADINGS NUTRIENT_AGG_N_CVT NUTRIENT_AGG_NITRO NUTRIENT_AGG_PHOSP PRIORITY GROUP_CODE POLLUTANT_DESC POLLUTANT_CODE PARAMETER_DESC PARAMETER_CODE DMR_LOADINGS_REF_PARAMETER

NODI_CODE

DUP LT_FLOW_ID LIMIT_SET_DESIGNATOR

LIMIT_SEASON_ID

PARAMETER_CODE

NMBR_OF_REPORT

NMBR_OF_DAYS

DATA_SOURCE

FSTAT5

FC3 FC2 FC1 FQ2 FQ1 MONITORING_PERIOD_END_DATE MONITORING_LOCATION_CODE

PERMIT_FEATURE_NMBR EXTERNAL_PERMIT_NMBR DMR_LOADINGS_FLOWS

NODI_CODE

DUP LT_FLOW_ID LIMIT_SET_DESIGNATOR

LIMIT_SEASON_ID

PARAMETER_CODE

NMBR_OF_REPORT

NMBR_OF_DAYS

DATA_SOURCE

FSTAT5

FC3 FC2 FC1 FQ2 FQ1 MONITORING_PERIOD_END_DATE MONITORING_LOCATION_CODE

PERMIT_FEATURE_NMBR EXTERNAL_PERMIT_NMBR DMR_LOADINGS_FLOWS



### Figure 3 -1. Relationship Diagram for Convert Module Output

The Convert Module functions are divided into four major steps, which are presented in Figure 3 -2 and described in more detail in the following subsections:

• 

### Convert Module Major Step 1 – Create CONVERT_DMR Table. The Loading To ol creates the CONVERT_DMR table using DMR data extracted from ICIS

NPDES and PCS.

• 

### Convert Module Step 2 – Correct Flows

. The Loading Tool identifies wastewater fl ows that were likely stored in PCS and ICIS-NPDES using incorrect units of measure and corrects the wastewater flows to represent millions of gallons per day.

• 

### Convert Module Major Step 3 – Calculate Average Wastewater pH and Temperature

. The Loading Tool creates two new columns in the CONVE RT_DMR t able, calculates the average wastewater temperature and pH, and displays the average temperature and pH in the new columns.DRAFT Section 3 - Calculator Module Development



### 3-3 • 

### Convert Module Major Step 4 – Correct DMR data. The Loading Tool corrects the data in the CONVERT_DMR table using corrections identified through public comment and EAD’s Annual Reviews of DMR data for EPA’s Effluent Guidelines Planning Process.

ICIS

- NPDES PCS

Data Extraction Through EPA Firewall Data Extraction from EPA Mainframe

DMR_LOADINGS_CONVERT_DMR Table Loadings Tool Convert Module Convert Module Step 1: Extract data from ICIS and PCS tables and store in interim table s Convert DMR measurements into standard units Indentify the statistical basis of permit limits Match DMR measurements with wastewater flows and permit limits

Convert Module Step 2: Automated Wastewater Flow QC and Corrections

Convert Module Step 3: Identify and select wastewater temperature and pH measurements Match Temperature and pH with Pollutant Measurements Adjust Invalid Number of Report Values Calculate the Number of Days per Monitoring Period

Convert Module Step 4: Make Database Corrections identified through 304 Screening

Level Analysis and public comment



### Figure 3 -2. Convert Module Calculation Steps

3.1.1 Convert Module Major Step 1 – Create CONVERT_DMR Table

In this step, the Loading Tool creates the CONVERT_DMR table using DMR data extracted from ICIS -NPDES using the following steps:

1. Extract data from 17 ICIS tables; 2. Store the extracted data in five interim Loading Tool database tables;DRAFT Section 3 - Calculator Module Development



### 3-4 3. Import PCS data into the five interim Loading Tool database tables; 4. Convert DMR measurements into standardized units of measure; 5. Identify the statistical basis of permit limits; 6. Create measure ment and limits crosstab tables; 7. Match DMR measurements permit limits; 8. Calculate the number of days per monitoring period; and 9. Identify measurements for wastewater flow.

3.1.1.1 Extract ICIS Data (Step 1)

To extract ICIS -NPDES DMR data, EPA’s contractor, ERG, obtained Anytime Anywhere Access (AAA) from EPA and restored the data to secure ERG servers. The restored data include the fo llowing 17 ICIS 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 IC IS_DMR_FORM_PARAMETER ICIS_DMR_VALUE REF_PARAMETER REF_POLLUTANT

3.1.1.2 Store ICIS Data in Interim Loading Tool Database Tables (Step 2)

• 

### E PA created five interim tables to store the extracted ICIS data:

DMR_LOADINGS_FACILITIES

. This table contains selected in formation from the ICIS_FACILITY_INTEREST, XREF_FACILITY_INTEREST_SIC, XREF_FACILITY_INTEREST_NAICS, and ICIS_PERMIT tables extracted from ICIS -NPDES; and the NPDES_SITE table extracted from FRS by unique NPDES permit number.

• 

### DMR_LOADINGS_PERM_FEATURES

. T his table contains selected information from the extracted ICIS_PERM_FEATURE and ICIS_PERMIT tables by unique permitted feature ID.

• 

### DMR_LOADINGS_LIMITS

. This table contains selected information from the extracted ICIS_LIMIT_SET, ICIS_LIMIT_SET_SCHEDULE, I CIS_LIMIT, and ICIS_LIMIT_VALUE tables by unique LIMIT_VALUE_ID.

• 

### DMR_LOADINGS_DMRS

. This table contains select information from the extracted ICIS_DMR, ICIS_DMR_EVENT, ICIS_DMR_FORM, ICIS_DMR_FORM_PARAMETER, and ICIS_DMR_VALUE tables by unique DMR_VALUE_ ID

.DRAFT Section 3 - Calculator Module Development



### 3-5 • 

### DMR_LOADINGS_REF_PARAMETER

. This table contains select information f rom the extracted REF_PARAMETER and REF_POLLUTANT tables . This table links pollutant parameters to pollutant codes and CAS numbers. In addition, EPA added descriptor fields to this table to assign parameter codes to pollutant groups, priorities for grouping parameter loads, toxic weighting factors (TWFs), and Substance Registry Service (SRS) IDs.

In addition, EPA created three reference tables to perform Convert Module functions:

• 

### REF_ UNIT_CODES

. This table provides conversion factors for unit codes to

c onvert concentrations into units of milligrams per liter (mg/L), loads into kilograms per day (kg/day), and flows into millions of gallon s per day (MGD).

• 

### REF_STAT5

. This table assigns a p proximately 160 statistical base codes from the ICIS_LIMIT table to one of four categories:

— 1 = Average ;

— 2 = Total ;

— 3 = Maximum; and

— 4 = Minimum.

• 

### REF_FLOW_PRAM

. This table identifies 24 parameter codes from the R EF_PARAMETER table that represent wastewat er flow and assigns priorities that the Convert Module uses to match one flow per outfall and monitoring period for load calculations.

Fi gure 3

- 3 presents the relationships between fields from the interim ICIS tables and reference tables.

3.1.1.3 Import PCS Data into Interim Loading Tool Database Tables (Step 3)

EPA extracts PCS DMR data from four PCS data types through EPA’s Mainframe: Permit Facility, Pipe Schedule, Parameter Limits, and Measurement Violation. In this major step, the Convert Module imports DMR d ata from PCS into DMR_LOADINGS_FACILITIES, DMR_LOADINGS_PERM_FEATURES, DMR_LOADINGS_LIMITS, and DMR_LOADINGS_DMRS. During import, the Convert Module converts the PCS data into formats consistent with the ICIS

- NPDES DMR data. Table 3

- 1 lists the PCS fields that the Convert Module imports into DMR_LOADINGS_FACILITIES, Table 3- 2 lists the PCS fields that the Convert Module imports into the DMR_LOADINGS_PERM_FEATURES tables, Table 3- 3 lists the PCS fields that the Convert Module imports into the DMR_LOADINGS_LI MITS table, and Table 3

- 4 lists the PCS fields that the Convert Module imports into the DMR_LOADINGS_DMRS table. In addition, each table notes any conversions that the tool makes to the PCS data. As the Loading Tool appends records from the PCS data into t he Loading Tool database tables, it only selects records for effluent monitoring locations using the same hierarchy described for ICIS -NPDES DMR data in Section 3.1.1.6. In addition, the Loading Tool does not append PCS records if the same record already e xists in the ICIS

- NPDES data.DRAFT Section 3 - Calculator Module Development



### 3-6 SRS_ID TWF CHEMICAL_ABSTRACT_SERVICE_NMBR PCS_PARAMETER_CODE DELETE_FROM_LOADINGS NUTRIENT_AGG_N_CVT NUTRIENT_AGG_NITRO NUTRIENT_AGG_PHOSP PRIORITY GROUP_CODE POLLUTANT_DESC POLLUTANT_CODE PARAMETER_DESC PARAMETER_CODE DMR_LOADINGS_REF_PARAMETER

SRS_ID TWF CHEMICAL_ABSTRACT_SERVICE_NMBR PCS_PARAMETER_CODE DELETE_FROM_LOADINGS NUTRIENT_AGG_N_CVT NUTRIENT_AGG_NITRO NUTRIENT_AGG_PHOSP PRIORITY GROUP_CODE POLLUTANT_DESC POLLUTANT_CODE PARAMETER_DESC PARAMETER_CODE DMR_LOADINGS_REF_PARAMETER MULTIPLIER STD_UNIT MEAS_TYPE DESCRIPTION UNIT_CODE REF_UNIT_CODES

MULTIPLIER STD_UNIT MEAS_TYPE DESCRIPTION UNIT_CODE REF_UNIT_CODES

STAT5 DESCRIPTION STATISTICAL_BASE_CODE REF_STAT5 RANK PARAMETER_DESC PARAMETER_CODE REF_FLOW_PRAM

DATA_SOURCE STATE_WATER_BODY STATE_WATER_BODY_NAME

SOURCE_FEATURE_ID HUC12 HUC_CODE ACTUAL_AVERAGE_FLOW_NMBR

TOTAL_DESIGN_FLOW_NMBR NAICS_CODE

SICCODE SIC_CODE_3 SIC_CODE_2 SIC_CODE_1 FACILITY_TYPE_INDICATOR

PERMIT_TYPE_CODE GEOCODE_LONGITUDE GEOCODE_LATITUDE CONGRESSIONAL_DIST_NUM EPA_REGION_CODE ZIP STATE_CODE COUNTY_NAME COUNTY_CODE CITY FACILITY_NAME MAJOR_MINOR_STATUS_FLAG UIN EXTERNAL_PERMIT_NMBR DMR_LOADINGS_FACILITIES

DATA_SOURCE STATE_WATER_BODY STATE_WATER_BODY_NAME

SOURCE_FEATURE_ID HUC12 HUC_CODE ACTUAL_AVERAGE_FLOW_NMBR

TOTAL_DESIGN_FLOW_NMBR NAICS_CODE

SICCODE SIC_CODE_3 SIC_CODE_2 SIC_CODE_1 FACILITY_TYPE_INDICATOR

PERMIT_TYPE_CODE GEOCODE_LONGITUDE GEOCODE_LATITUDE CONGRESSIONAL_DIST_NUM EPA_REGION_CODE ZIP STATE_CODE COUNTY_NAME COUNTY_CODE CITY FACILITY_NAME MAJOR_MINOR_STATUS_FLAG UIN EXTERNAL_PERMIT_NMBR DMR_LOADINGS_FACILITIES

DATA_SOURCE LONGITUDE_MEASURE LATITUDE_MEASURE STATE_WATER_BODY_NAME REACH_ID WATER_BODY_NAME ACTUAL_AVERAGE_FLOW_NMBR DESIGN_FLOW_NMBR PERM_FEATURE_ID PERM_FEATURE_NMBR EXTERNAL_PERMIT_NMBR DMR_LOADINGS_PERM_FEATURES

DATA_SOURCE LONGITUDE_MEASURE LATITUDE_MEASURE STATE_WATER_BODY_NAME REACH_ID WATER_BODY_NAME ACTUAL_AVERAGE_FLOW_NMBR DESIGN_FLOW_NMBR PERM_FEATURE_ID PERM_FEATURE_NMBR EXTERNAL_PERMIT_NMBR DMR_LOADINGS_PERM_FEATURES

LT_LIMITS_ID DMR_YEAR LIMIT_SEASON_ID LIMIT_SET_DESIGNATOR

DATA_SOURCE LT_DMRS_ID DMR_VALUE_STANDARD_UNITS UNIT_CODE VALUE_QUALIFIER_CODE

DMR_VALUE_NMBR

NODI_CODE NMBR_OF_REPORT VALUE_TYPE_CODE PARAMETER_CODE MONITORING_PERIOD_END_DATE MONITORING_LOCATION_CODE

PERM_FEATURE_NMBR EXTERNAL_PERMIT_NMBR DMR_LOADINGS_DMRS

LT_LIMITS_ID DMR_YEAR LIMIT_SEASON_ID LIMIT_SET_DESIGNATOR

DATA_SOURCE LT_DMRS_ID DMR_VALUE_STANDARD_UNITS UNIT_CODE VALUE_QUALIFIER_CODE

DMR_VALUE_NMBR

NODI_CODE NMBR_OF_REPORT VALUE_TYPE_CODE PARAMETER_CODE MONITORING_PERIOD_END_DATE MONITORING_LOCATION_CODE

PERM_FEATURE_NMBR EXTERNAL_PERMIT_NMBR DMR_LOADINGS_DMRS

DATA_SOURCE LT_LIMITS_ID

STAT LIMIT_VALUE_STANDARD_UNITS LIMIT_VALUE_NMBR STATISTICAL_BASE_CODE UNIT_CODE VALUE_TYPE_CODE PARAMETER_CODE LIMIT_SEASON_ID LIMIT_SET_DESIGNATOR MONITORING_LOCATION_CODE

PERM_FEATURE_NMBR EXTERNAL_PERMIT_NMBR DMR_LOADINGS_LIMITS

DATA_SOURCE LT_LIMITS_ID

STAT LIMIT_VALUE_STANDARD_UNITS LIMIT_VALUE_NMBR STATISTICAL_BASE_CODE UNIT_CODE VALUE_TYPE_CODE PARAMETER_CODE LIMIT_SEASON_ID LIMIT_SET_DESIGNATOR MONITORING_LOCATION_CODE

PERM_FEATURE_NMBR EXTERNAL_PERMIT_NMBR DMR_LOADINGS_LIMITS



### Figure 3

- 3. Relationship Diagram for Interim ICIS Tables and Reference Tables

Table 3

- 1. PCS Data Import to DMR_LOADINGS_FACILITIES Table

DMR_LOADINGS_FACILITIES Field Name PCS Convert File Field Name Conversions for Compa tibility with Loading Tool ICIS_FACILITY_INTEREST_ID N/A DATA_SOURCE Autotext “PCS”

– Flagged data source as PCS EXTERNAL_PERMIT_NMBR NPID FACILITY_UIN N/A MAJOR_MINOR_STATUS_FLAG MADI ACTIVITY_CODE IACC FACILITY_NAME NAME
CITY CYNM STATE_C ODE STTE COUNTY_CODE CNTY

ZIP N/A HUC_CODE [BAS4] & [RSEG]DRAFT Section 3 - Calculator Module Development



### 3-7 Table 3

- 1. PCS Data Import to DMR_LOADINGS_FACILITIES Table

DMR_LOADINGS_FACILITIES Field Name PCS Convert File Field Name Conversions for Compa tibility with Loading Tool EPA_REGION_CODE N/A STATE_WATER_BODY_NAME RWAT TOTAL_DESIGN_FLOW_NMBR N/A ACTUAL_AVERAGE_FLOW_NMBR N/A PERMIT_TYPE_CODE Autotext All permits in the PCS Convert file are individual
p ermits, therefore Permit Type = “NPD”. FACILITY_TYPE_ INDICATOR FTYP Updated PCS fields to spell out facility type: FED = FEDERAL; PRI = NON

- POTW ; STA = STATE If PUB and SIC Code is 4952 and facility type is municipal = POTW If PUB and SIC Code is not 495 2 or facility type is not municipal = NON

- POTW NAICS_CODE N/A SIC_CODE SIC2 GEOCODE_LATITUDE FLAT Multiplied by 0.00001 GEOCODE_LONGITUDE FLON Multiplied by 0.00001

N/A – “Not Applicable” – Field does not exist in PCS Convert file output.



### Table 3

- 2 . PCS Data Import to DMR_LOADINGS_PERM_FEATURES Table

DMR_LOADINGS_FACILITIES Field Name PCS Convert File Field Name Conversions for Compatibility with Loading Tool EXTERNAL_PERMIT_NMBR NPID PERM_FEATURE_NMBR DSCH DATA_SOURCE Autotext “PCS”

– Flagge d data source as PCS DESIGN_FLOW_NMBR N/A ACTUAL_AVERAGE_FLOW_NMBR N/A WATER_BODY_NAME RWAT REACH_ID REAC STATE_WATER_BODY_NAME RWAT LATITUDE_MEASURE PLAT Multiplied by 0.00001 LONGITUDE_MEASURE PLON Multiplied by 0.00001

N/A – “Not Applicable ” – Field does not exist in PCS Convert file output.DRAFT Section 3 - Calculator Module Development



### 3-8 Table 3

- 3. PCS Data Import to DMR_LOADINGS_LIMITS Table

DMR_LOADINGS_FACILITIES Field Name PCS CNVRT Field Name Conversions for Compatibility with Loading Tool EXTERNAL_PERMIT_NMBR NPID PERM_FEATUR E_NMBR DSCH MONITORING_LOCATION_CODE MLOC Convert “#” to “SC” LIMIT_SET_DESIGNATOR DRID LIMIT_SEASON_ID SEAN PARAMETER_CODE a PRAM Updated PCS Parameter Codes to New ICIS Parameter Codes 1
VALUE_TYPE_CODE Autotext Set to “LQ1” when importing records for LQAV Set to “LQ2” when importing records for LQMX Set to “LC1” when importing records for LCMN Set to “LC2” when importing records for LCAV Set to “LC3” when importing records for LCMX UNIT_CODE LQUC, LCUC Select LQUC when importing records for LQAV and LQMX Select LCUC when importing records for LCMN, LCAV, and LCMX STATISTICAL_BASE_CODE LQAS, LQXS, LCMS, LCAS, LCXS Select LQAS when importing records for LQAV Select LQXS when importing records for LQMX Select LCMS when importing records for LCMN Sel ect LCAS when importing records for LCAV Select LCXS when importing records for LCMX LIMIT_VALUE_NMBR LQAV, LQMX, LCMN, LCAV, LCMX Remove “ADDMON”, “OPTMON”, and “DELMON” and convert to number.

LIMIT_VALUE_STANDARD_UNITS N/A Populated by Loading Tool dur ing unit conversions.
STAT N/A Populated by linking the STATISTICAL_BASE_CODE to REF_STAT5 table. LT_LIMITS_ID Autotext Unique identifier assigned to each record in the DMR_LOADINGS_LIMITS table DATA_SOURCE Autotext “PCS”

– Flag data source as PCS

a – Table A

- 4 of Appendix A presents the crosswalk from legacy parameter codes to new ICIS parameter codes.DRAFT Section 3 - Calculator Module Development



### 3-9 Table 3

- 4. PCS Data Import to DMR_LOADINGS_DMRS Table

DMR_LOADINGS_FACILITIES Field Name PCS CNVRT Field Name Conversions for Compatibility with Load ing Tool EXTERNAL_PERMIT_NMBR NPID PERM_FEATURE_NMBR DSCH MONITORING_LOCATION_CODE MLOC Convert “#” to “SC” LIMIT_SET_DESIGNATOR DRID LIMIT_SEASON_ID SEAN MONITORING_PERIOD_END_DATE MVDT Converted Date in PCS from text MMDDYY to ICIS Date Format: MM/DD/YYYY. PARAMETER_CODE a PRAM Updated PCS Parameter Codes to New ICIS Parameter Codes 1
VALUE_TYPE_CODE Autotext Set to “MQ1” when importing records for MQAV Set to “MQ2” when importing records for MQMX Set to “MC1” when importing records for MCMN Se t to “MC2” when importing records for MCAV Set to “MC3” when importing records for MCMX NMBR_OF_REPORT NRPU NODI_CODE NODI DMR_VALUE_NMBR MQAV, MQMX, MCMN, MCAV, MCMX Remove data qualifiers (e.g., “<”) and convert to number.

VALUE_QUALIFIER_CODE MQAV , MQMX, MCMN, MCAV, MCMX Select first character of measurement field.

UNIT_CODE RCUN, RUNT Select RUNT for MQAV and MQMX Select RCUN for MCMN, MCAV, and MCMX DMR_VALUE_STANDARD_UNITS N/A Populated by Loading Tool during unit conversions. DMR_YEAR MVDT E xtract DATE from MVDT LT_DMRS_ID Autotext Unique identifier assigned to each record in the DMR_LOADINGS_DMRS table LT_LIMITS_ID Autotext Unique identifier assigned to each record in the DMR_LOADINGS_LIMITS table

a – Table A

- 4 of Appendix A presents the crosswalk from legacy parameter codes to new ICIS parameter codes.



### 3.1.1.4 Convert Measurements and Units into Standard Units (Step 4)

DMR data and permit limits are stored in ICIS

- NPDES and PCS in the measurement units specified by facilities’ NPDES permits. The ICIS database also converts the DMR measurements and limits into standard units. EPA included both the original value and the standard units value in its ICIS data extract. EPA’s PCS data import only included the original DMR and limit values. EPA developed the Convert Module to identify the unit of measure for each discharge, convert ICIS and PCS discharges into standard units, and verify the ICIS standard units conversion.DRAFT Section 3 - Calculator Module Development



### 3-10 • 

### Identify Units of Measure

. Unit codes are provided in ICIS- NDPES in the ICIS_DMR a nd ICIS_LIMIT tables. Similarly, PCS contains unit codes in both the Measurement Violations and Parameter Limits data types. However, EPA’s review of the data found that the unit code fields in ICIS_DMR and PCS_Measurement_Violations are blank for most records. Therefore, EPA developed the Convert Module to pull unit codes from ICIS_LIMIT and PCS_Parameter_Limit if the ICIS_DMR and PCS_Measurement_Violations unit code fields are blank.

• 

### Convert ICIS and PCS Discharges into Standard Units. EPA converted

mea surements in ICIS and PCS into standard units of milligrams per liter, kilograms per day, and millions of gallons per day by linking the UNIT_CODE to the REF_UNIT_CODES table (provided in Table A-1, Appendix A) and multiplying the measurement by the associ ated conversion factor.

• 

### Verify ICIS Unit Conversio ns

. EPA compared the converted ICIS measurements from the previous step to the measurements stored in the DMR_VALUE_STD_UNITS field. EPA corrected the ICIS conversions that did not match EPA’s conversions. The Convert Module corrected approximately 0.3 percent of the DMR records from ICIS.

3.1.1.5 Identify Measurement Statistical Basis (Step 5)

ICIS and PCS data contain approximately 160 statistical base codes to describe the statistical basis of the DMR measureme nts (e.g., 30- day geometric mean or rolling average). These codes are stored in the ICIS_LIMIT and PCS_Parameter_Limits tables. The Convert Module uses the REF_STAT5 table (provided in Table A-2, Appendix A) to assign each statistical base code to one of f ive categories:

• 

### 0 = Statistical Base Code is Null ;

• 

### 1 = Average ;

• 

### 2 = Total ;

• 

### 3 = Maximum; and

• 

### 4 = Minimum.

Although specific information regarding the statistical basis of the measurement is lost during this step, the simplification is necessary for efficie nt calculation of loads. The Convert Module creates a STAT5 code consisting of five characters. Each character of the STAT5 code corresponds to one measurement value field as shown in Figure 3-4. Section 3.2.1 describes how the Convert Module uses the STAT5 code for loadings calculations.DRAFT Section 3 - Calculator Module Development



### 3-11 

# 1 3 0 1 1

Quantity 1

(MQ1) Quantity 2 (MQ2) Concentration 1 (MC1) Concentration 2 (MC2) Concentration 3 (MC3)



### Figure 3

- 4. Example STAT5 Code in CONVERT_DMR Table

3.1.1.6 Create Measurement and Limit Crosstabs (Step 6)

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 (Quantity 1, Quantity 2, Concentration 1, Concentration 2, and Concentration 3) are stored as separate records

. The Convert Module creates two crosstab tables (D MR_LOADINGS_XTAB_LIMITS and DMR_LOADINGS_XTAB_DMRS) to display limit values and DMR values for all five measurement fields in one row, unique by external permit number, permit feature number (outfall), monitoring location, parameter code, and monitoring period end date. Figure 3-5 presents the measurement and limits crosstab tables.

DATA_SOURCE STAT5 EXTERNAL_PERMIT_NMBR

LT_LIMITS_XTB_ID LSTAT5 LSTAT4 LSTAT3 LSTAT2 LSTAT1

LC3 LC2 LC1 LQ2 LQ1 PARAMETER_CODE LIMIT_SEASON_ID LIMIT_SET_DESIGNATOR MONITORING_LOCATION_CODE PERM_FEATURE_NMBR DMR_LOADINGS_XTAB_LIMITS

DATA_SOURCE STAT5 EXTERNAL_PERMIT_NMBR

LT_LIMITS_XTB_ID LSTAT5 LSTAT4 LSTAT3 LSTAT2 LSTAT1

LC3 LC2 LC1 LQ2 LQ1 PARAMETER_CODE LIMIT_SEASON_ID LIMIT_SET_DESIGNATOR MONITORING_LOCATION_CODE PERM_FEATURE_NMBR DMR_LOADINGS_XTAB_LIMITS

DATA_SOURCE

LT_LIMITS_XTB_ID

LT_DMRS_XTB_ID PARAMETER_CODE LIMIT_SET_DESIGNATOR

LIMIT_SEASON_ID

LT_FLOW_ID DMR_YEAR C3_QUAL C2_QUAL C1_QUAL Q2_QUAL Q1_QUAL

MC3 MC2 MC1 MQ2 MQ1 NODI_CODE NMBR_OF_REPORT MONITORING_PERIOD_END_DATE MONITORING_LOCATION_CODE PERM_FEATURE_NMBR EXTERNAL_PERMIT_NMBR DMR_LOADINGS_DMRS_XTAB

DATA_SOURCE

LT_LIMITS_XTB_ID

LT_DMRS_XTB_ID PARAMETER_CODE LIMIT_SET_DESIGNATOR

LIMIT_SEASON_ID

LT_FLOW_ID DMR_YEAR C3_QUAL C2_QUAL C1_QUAL Q2_QUAL Q1_QUAL

MC3 MC2 MC1 MQ2 MQ1 NODI_CODE NMBR_OF_REPORT MONITORING_PERIOD_END_DATE MONITORING_LOCATION_CODE PERM_FEATURE_NMBR EXTERNAL_PERMIT_NMBR DMR_LOADINGS_DMRS_XTAB



### Figure 3 -5. Convert Module Crosstab TablesDRAFT Section 3 - Calculator Module Development



### 3-12 3.1.1.7 Create DMR_LOADINGS_CONVERT_DMR Table (Step 7)

The Convert Module creates the DMR_LOADINGS_CONVERT_DMR table by combining informatio n stored in DMR_LOADINGS_XTAB_LIMITS and DMR_LOADINGS_XTAB_DMRS tables

. The DMR_LOADINGS_CONVERT_DMR table presents the permit limits and DMR measurements for each unique NPDES permit, permitted feature, parameter code, monitoring location, limit set desig nator, and limit season ID.

The Loading Tool only selects records for effluent monitoring locations. The monitoring location is indicated in the DMR_LOADINGS_CONVERT_DMR table in the MONITORING_LOCATION_CODE field. Five monitoring location codes in ICIS-N PDES can represent effluent discharges:

• 

### 1 = Effluent gross discharge;

• 

### 2 = Effluent net discharge;

• 

### A = After disinfection;

• 

### B = Before disinfection; and

• 

### SC = See Comment.

Some facilities may report discharges for A, B, and SC to represent final effluent eve n though the actual monitoring location is not located at the final outfall. For example, some facilities are required to monitor upstream of their final effluent for pollutants that may be present at the outfall, but are present at concentrations too smal l to detect. Therefore, the facility’s NPDES permit requires them to monitor at a location where the pollutant is more concentrated. Because both flow and concentration are measured at this upstream location, the mass discharge is representative of what th e facility discharges at the final outfall

. In these circumstances, facilities may use “SC” to label their monitoring location.

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 Convert Module selects only one monitoring location using the following priorities:

• 

### Priority 1: MONITORING_LOCATION_CODE 2;

• 

### Priority 2: MONITORING_LOCATION_CODE 1;

• 

### Pri ority 3: MONITORING_LOCATION_CODE A;

• 

### Priority 4: MONITORING_LOCATION_CODE B; and

• 

### Priority 5: MONITORING_LOCATION_CODE SC.

3.1.1.8 Calculate the Actual Number of Days per Monitoring Period (Step 8)

The Convert Modules 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. The NMBR_OF_REPORT field indicates the number of months of discharges represented on each DMR. For example, a NMBR_OF_REPORT of 1 indicates a monthly report and a NMBR_OF_REPORT of 3 is a quarterly report (i.e., three months are in a quarter). EPA reviewed the ICIS- NPDES data and identified the following valid NMBR_OF_REPORT values:DRAFT Section 3 - Calculator Module Development



### 3-13 • 

### 1 = Monthly Report;

• 

### 2 = Bi -monthly Report;

• 

### 3 = Quarterly Report;

• 

### 4 = Triannual Report (typically for April, August, and December);

• 

### 6 = Semi -annual Report; and

• 

### 12 = Annual Report.

However, due to a data-entry rule in ICIS, some DMR records have invalid NMBR_OF_REPORT values, such as 5, 7, 8, 9, 10, 11 or greater than 12. Because ICIS does not allow users to enter a monitoring period start date that is earlier than the permit effective date, facilities whose permits are renewed part -way through the year cannot enter valid NMBR_OF_REPORT values. For example, if a facility submitted a semi -annual DMR in June, which covered discharges from January to June, but their NPDES permit was renewed in February, then ICIS will not allow the facility to enter a monitoring period start date earlier than the effective date of the permit (February). As a result the NMBR_OF_REPORT in ICIS is five instead of six. The Convert Module addresses this issue by rounding up invalid NMBR_OF_REPORT values to the next valid value. In addition, NMBR_OF_REPORT values that are greater than 12 are conver ted to 12.

Once all NMBR_OF_REPORT values have been converted to valid values, the Convert Module uses Table 3-5 to assign the actual number of days for the monitoring period in the NMBR_OF_DAYS field in DMR_LOADINGS_CONVERT_DMR. Table 3 -6 presents the

ac tual number of days for all possible MONITORING_PERIOD_END_DATE and NMBR_OF_REPORT combinations.

Table 3

- 5. Actual Number of Days per Monitoring Period

MONITORING_PERIOD_END_DATE (Month) NUMBER_OF_ REPORT
1 2 3 4 6 12 January 31 62 92 123 184 365
Febru ary a 28 59 90 120 181 365
March 31 59 90 121 182 365 April 30 61 89 120 181 365 May 31 61 92 120 182 365

June 30 61 91 122 181 365 July 31 61 92 122 181 365 August 31 62 92 123 184 365 September 30 61 92 122 183 365
October 31 61 92 123 184 365

N ovember 30 61 91 122 183 365
December 31 61 92 122 184 365

a – Table 3

- 4 does not account for the number of days in February during leap years.



### As a final step for assigning the number of days per monitoring period, the Convert Module identifies and corrects monitoring periods with multiple reported measurements. For example, if a facility’s NPDES permit requires them to report wastewater selenium discharges on both January 15 and January 30, the Loading Tool would overestimate the annual selenium loadDRAFT Section 3 - Calculator Module Development



### 3-14 because it would multiply both the January 15 and January 31 discharges by 31 days per month according to Table 3 -5. To eliminate this overestimation, the Convert Module divides the NMBR_OF_REPORT and the NMBR_OF_DAYS by the number of DMRs submitted per monitoring period. So, using the above example, the Convert Module would calculate the NMBR_OF_REPORT and NMBR_OF_DAYS for the January DMRs as follows:

NMBR_OF_REPORT = NMBR_OF_REPORT (1) / 2 Reports per month = 0.5 NMBR_OF_DAYS = NMBR_OF_DAYS (31) / 2 Reports per month = 15.5

3.1.1.9 Create DMR_LOADINGS_FLOWS Table (Step 9)

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_FL OW_PRAM, and creates a new table (DMR_LOADINGS_FLOWS) that is unique by external permit number, permit feature number (outfall), monitoring location, limit set designator, limit season id, and monitoring period end date. EPA identified 24 parameter codes i n ICIS

- NPDES that represent wastewater flows. EPA assigned priorities to the parameter codes in REF_FLOW_PRAM (provided in Table A-3, Appendix A). If a facility reports more than one type of flow parameter code for the same outfall, then the Convert Module selects the PRAM code with the highest priority in the look -up table. As a result, the Convert Module selects only one flow for each permit, outfall, monitoring location, limit set designator, limit season id, and monitoring period end date.

3.1.2 Major Step 2 – Correct Wastewater Flows

EPA’s initial ICIS and PCS DMR data quality review (see Section 5) identified several wastewater flows that exceeded the reasonable range. EPA reviewed these flows and developed the following flow correction function for the Convert Module. The goal of this function is to identify data entry errors for flows greater than 1,000 MGD. OECA’s code for the PCS EDS System assumed any flow greater than 1,300 MGD was incorrectly reported in units of gallons per day (GPD) and divided the flow by 1,000,000. Because facility discharges have increased, this 1,300 MGD flow cutoff is now outdated. The maximum design flow in the DMR_LOADINGS_FACILITIES table is 5,000 MGD. Therefore, EPA updated the maximum flow rate to 5,000 MGD. In addition, the flow correction function uses other information available in the DMR_LOADINGS_CONVERT_DMR and DMR_LOADINGS_FACIL I TIES tables to verify flows and identify corrections.

The Convert Module corrects all flows exceeding 5,000 MGD. For flows ranging from 1,000 to 5,000 MGD, EPA developed a new methodology that uses three types of erroneous flow indicators to correct flows:

Type 1: Month

- to

- Month Variability

In this step, the Convert Module compares flows reported for each monitoring period and identifies vari ations greater than three orders of magnitude using the following procedure:DRAFT Section 3 - Calculator Module Development



### 3-15 a) Create a field that identifies the magnitude of each flow (e.g., 62,800 MGD has a magnitude of 10,000).

b) Group flow magnitudes by unique external permit number, permit feature numb er (outfall), monitoring location, and limit set designator.

c) Find the mini mum flow magnitude that is ≥ 1,000.

d) Find the maximum flow magnitude that is <1,000.

e) If the Convert Module does not identify flows that meet the criteria in steps c and d, then it makes no correction. If it identifies flows meeting the criteria in both steps c and d, then it calculates a flow correction fa ctor by dividing step c by step d.

f) If the reported flow is between 1,000 and 5,000 MGD, and the correction factor indicates a difference of three orders of magnitude or more, then correct the flow as follows:

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

g) I f the reported flow is ≥ 5,000 MGD, and the correction factor indicates a difference of one order of magnitude or more, then correct the flow using the equation in Step f.

Table 3 -6 presents an example of a Type 1 flow correction that the Convert Module identified. As shown in the Table, the September 2007 flow is between 1,000 and 5,000 MGD and the correction factor is three orders of magnitude. Therefore, the Convert Module divided the Septe mber flow by 1,000.

Table 3

- 6. Example Type 1 Flow Correction

EXTERNAL_

PERMIT_

NMBR PERM_ FEATURE_ NMBR MONITORING_ PERIOD_

END_ DATE Original FQ1 Flow Magnitude New FQ1 Correction Applied?
GA0037648 0B0 31

- Mar

- 07 0.74 0.1 0.74 No
GA0037648 0B0 30

- Apr

- 07 0.54 0.1 0.54 No
GA0037648 0B0 31

- May

- 07 0.67 0.1 0.67 No
GA0037648 0B0 30

- Jun

- 07 1.31 1 1.31 No
GA0037648 0B0 31

- Jul

- 07 1.02 1 1.02 No
GA0037648 0B0 31

- Aug

- 07 1.06 1 1.06 No
GA0037648 0B0 30

- Sep

- 07 2,554.00 1 , 000 2.55 Yes
GA0037648 0B0 31

- Oct

- 07 1.24 1 1.24 No
GA0037648 0B0 31

- Dec

- 07 1.29 1 1.29 No
GA0037648 0B0 28

- Feb

- 07 0.96 0.1 0.96 NoDRAFT Section 3 - Calculator Module Development



### 3-16 Table 3

- 6. Example Type 1 Flow Correction

EXTERNAL_

PERMIT_

NMBR PERM_ FEATURE_ NMBR MONITORING_ PERIOD_

END_ DATE Original FQ1 Flow Magnitude New FQ1 Correction Applied?
GA0037648 0B0 31

- Jan

- 07 1.02 1 1.02 No
GA0037648 0B0 30

- Nov

- 07 0.85 0.1 0.85 No Maximum Flow Magnitude < 1,000 1 Minimum Flow Magn itude ≥ 1,000 1

, 000
Correction Factor 1

, 000



### Type 2: Variations from Design Flows and Actual Average Flo ws in DMR_LOADINGS_FACILITIES

T he DMR_LOADINGS_FACILITIES table contains information for facility design flow and actual average flow in millions of gallons per day. These fields are not required and therefore are not populated for all records. However, when populated, these fields can be used to help evaluate the reasonableness of the flows reported in the DMR data. The Convert Module compares the de sign flow or actual average flow in FACILITIES to the reported flows in CONVERT_DMR using the following procedure:

a) Use ACTUAL_AVG_FLOW if reported. If ACTUAL_AVG_FLOW is not reported, then use DESIGN_FLOW.

b) Use same procedure as Step a in Type 1 correctio n to calculate the magnitude of the reported flows and the actual/design flow magnitudes

c) Divide the actual/design flow magnitude by the reported flow (e.g., FQ1) magnitude to calculate the correction factor

d) If the correction factor indicates that the reported flow is at least three orders of magnitude higher than the actual/design flow (correction factor ≤ 0.001), and the reported flow is between 1,000 and 5,000 MGD, then correct the flow as follows:

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

e) I f the correction factor indicates a di fference of one order of magnitude or more, and the reported flow is ≥ 5,000 MGD, then correct the flow using the equation in Step d.

Table 3 -7 presents an example of a Type 2 flow correction that the Convert Module identified. As shown in the Table, the reported flows (FQ1) were six orders of magnitude higher than the facility design flow. Therefore, the Convert Module divided all flows by 1,000,000.DRAFT Section 3 - Ca lculator Module Development



### 3-17 Table 3

- 7. Example Type 2 Flow Correction

External Permit Number Permitted Feature Number Monitoring Period End Date FQ1 Actual Measured Flow Magnitude Design Flow Design Flow Magnitude Correction Factor New FQ1
NH0100692 001 31

- Jan

- 07 250,038 100,000 0.5 0.1 1,000,000 0.25
NH0100692 001 28

- Feb

- 07 131,243 100,000 0.5 0.1 1,000,000 0.13
NH0100692 001 31

- Mar

- 0 7 203,087 100,000 0.5 0.1 1,000,000 0.20
NH0100692 001 30

- Apr

- 07 308,359 100,000 0.5 0.1 1,000,000 0.31
NH0100692 001 31

- May

- 07 382,444 100,000 0.5 0.1 1,000,000 0.38
NH0100692 001 30

- Jun

- 07 460,524 100,000 0.5 0.1 1,000,000 0.46
NH0100692 001 31

- Jul

- 0 7 308,488 100,000 0.5 0.1 1,000,000 0.31
NH0100692 001 31

- Aug

- 07 154,491 100,000 0.5 0.1 1,000,000 0.15
NH0100692 001 30

- Sep

- 07 161,996 100,000 0.5 0.1 1,000,000 0.16
NH0100692 001 31

- Oct

- 07 158,444 100,000 0.5 0.1 1,000,000 0.16
NH0100692 001 30

- Nov

- 0 7 183,168 100,000 0.5 0.1 1,000,000 0.18
NH0100692 001 31

- Dec

- 07 190,775 100,000 0.5 0.1 1,000,000 0.19



### T ype 3: Flows Exceeding the 5,000 MGD Cap

If a reported flow exceeds 5,000 MGD and is not identified for the Type 1 or Type 2 corrections, then the Convert Module assumes that the flow was incorrectly entered in units of GPD and divides the flow by 1,000,000.

3.1.3 Major Step 3 – Calculate Average pH and Temperature

The Convert Module creates two new fields in the CONVERT_DMR table for
wastewater stream te mperature and pH. ICIS contains two parameter codes for temperature and one parameter code for pH:

• 

### Temperature Degrees C = PRAM 00010;

• 

### Temperature Degrees F = PRAM 00011; and

• 

### pH = PRAM 00400.

The Convert Module uses a measurement value selection hierarchy , based on the STAT5 codes created in Step 4, to select one pH and one temperature for each permitted feature, monitoring location, and monitoring period end date. The selection hierarchy uses the following procedure:

1. Scan STAT5 from left to right and sele ct the first measurement corresponding to a STAT5=1 (average); 2. If none of the STAT5 digits equals 1, then scan STAT5 from left to right and select first measurement corresponding to a STAT5=2 (total); 3. If none of the STAT5 digits equals 2, then scan STAT5 f rom left to right and select first measurement corresponding to a STAT5=3 (maximum); and 4. If none of the STAT5 digits equals 3, then scan STAT5 from left to right and select first measurement corresponding to a STAT5=4 (minimum).DRAFT Section 3 - Calculator Module Development



### 3-18 The Convert Module output p resents all temperatures in degrees Fahrenheit.

3.1.4 Convert Module Major Step 4 – Correct DMR Errors

To support its Effluent Guidelines Planning Process required by Section 304(m) of the Clean Water Act, EPA annually reviews DMR data from PCS and ICIS

- NPDES fo r data quality

. EPA transferred the DMR corrections that the 304(m) review process identified into the Oracle database

. The Convert Module corrects the data that are stored in the DMR_LOADINGS_CONVERT_DMR table.

EPA reviewed the DMR data corrections identi fied during the 2009 annual review and created the UPDT_DMR_CORRECTIONS_IMPRT table for importing into the Oracle database

. The UPDT_DMR_CORRECTIONS_IMPRT contains corrections to database fields that the tool uses to calculate loads

. These fields include:

• 

### Concentration Measurements. PCS and ICIS-NPDES include three fields, for f

acilities to report concentration measurements (MC1, MC2, and MC3). Facilities may report concentration measurements to one or all three of the measurement fields depending on the requirements of their NPDES permit. For example, a facility may report minimum concentrations in MC1, 30- day average concentrations in MC2, and daily maximum concentrations in MC3. EPA updated measurement fields to correct data -entry errors and incorrect uni ts of measure identified during the quality review.

• 

### Quantity Measurements. PCS and ICIS

- N PDES include two fields, for facilities to report quantity measurements (MQ1 and MQ2). Facilities may report quantities to one or both of the measurement fields depending on the requirements of their NPDES permit. For example, a facility may report 30 -day average quantities in MQ1 and daily maximum quantities in MQ2. EPA updated measurement fields to correct data -entry errors and incorrect units of measure identified d uring the quality review.

• 

### Measurement Field Qualifiers. DMR data extracted from PCS and ICIS

- N DPES contain qualifier fields for each of the five measurement value fields (MQ1_QUAL, MQ2_QUAL, MC1_QUAL, MC2_QUAL, and MC3_QUAL). These fields contain qualifie rs that indicate whether a pollutant was measured nondetect (“<”). If a nondetect qualifier is missing from the DMR data, then the loadings tool will overestimate pollutant loads. EPA added nondetect qualifiers (“<”) to these fields for measurements that w ere determined to be nondetect during the quality review.

• 

### Wastewater Flow. PCS and ICIS- N PDES include five potential measurement fields for facilities to report wastewater flows (FQ1, FQ2, FC1, FC2, and FC3 4 

### ). Facilities may report flows to one or more me asurement fields depending on the

4 FQ fields are the flow quantity fields while FC fields are th e flow concentration fields. A “flow concentration” is a flow measurement that was reported to a concentration measurement field. Facilities may report flows in any of the five measurement value fields. However, all flows are reported in units of MGD wheth er they are reported in a quantity or a concentration field.DRAFT Section 3 - Calculator Module Development



### 3-19 requirements of their NPDES permit. For example, a facility may report the 30

- day average flow in FQ1 and daily maximum flow in FQ2. EPA updated measurement fields to correct data -entry errors and incorrect units of measur e identified during the quality review.

• 

### Limit Set Designators

. P CS and ICIS-NPDES data include one- character codes to differentiate measurements reported for different limit sets. For example, a facility may report quarterly DMRs for one limit set (“Q”) a nd monthly DMRs for another limit set (“M”). Limit set designator codes are not standard across the NPDES program and vary from facility- to -facility. EPA’s quality review found that, in some cases, pollutant loadings reported for multiple limit sets may be double-counted by the Loading Tool. For these instances, EPA either selected one limit set to represent the pollutant discharges for the year, or combined the two limit sets to avoid double counting.

• 

### No Data Indicator (NODI) Code

. Facilities report a NOD I code for DMRs where no measurements are reported. The NODI code corresponds to a NODI description, which explains why no data are reported (e.g., no discharge, not quantifiable, conditional monitoring). The type of NODI code reported affects the annual load estimation performed by the Loading Tool. For NODI codes that indicate no discharge, the Loading Tool assumes a pollutant discharge of zero. However, for NODI codes that indicate a discharge may have occurred for that monitoring period, the Loading Too l estimates a discharge based on discharges reported for the other monitoring periods in that reporting year. As a result, incorrectly reported NODI codes can result in over or underestimating pollutant loads. EPA changed NODI codes to ensure the correct e stimation for annual loads calculations.

• 

### Number of Report. Number of report is a numeric code that indicates the required monitoring frequency for each pollutant. The number corresponds to the number of months included in each monitoring period (e.g., 1=monthly, 3=quarterly, 12=annually). The Loading Tool uses this number to calculate annual loads and also to determine whether ICIS

- NPDES and PCS contain a complete set of DMRs for the year. Therefore, incorrect number of reports can result in significant ov er or underestimating pollutant loads. EPA changed number of report records to ensure that the Loading Tool was estimating the correct number of months per DMR.

• 

### Number of Days. Number of days is a field that is created by the Loading Tool based on the number of report and the monitoring period end date, as described in Section 3.1.4. The Loading Tool assumes that discharges occur continuously for the duration of the monitoring period, and therefore overestimates loads for facilities that discharge intermittently. EPA corrected loads for intermittent dischargers by adjusting the Number of Days in the Loading Tool to reflect the actual number of discharge days per monitoring period.

I n addition, the UPDT_DMR_CORRECTIONS_IMPRT table includes records that EPA determined should be excluded from the annual load calculation (e.g., internal monitoringDRAFT Section 3 - Calculator Module Development



### 3-20 locations). The Loading Tool only calculates loads for pollutant measurements from samples potentially collected at the treatment system effluent . However, it is pos sible for a NPDES permit to require monitoring at two effluent points where one point (e.g. final outfall 001) is downstream of the other monitoring point (internal outfall 101). If EPA’s review determined that pollutant discharges were being double-counted at both internal and final outfalls in the Loading Tool, then EPA corrected this error by deleting the data for the internal outfall from the DMR_LOADINGS_CONVERT_DMR table. Records that EPA has identified for deletion from the loadings are marked as “DE L” in the FIELD column of the UPDT_DMR_CORRECTIONS_IMPRT table

.

3.2 Load Calculator Module Functions

The input to the Load Calculator module is the output from the Convert Module, shown in Figure 3 -1. The Load Calculator Module selects the appropriate DMR mea surements and wastewater flows from the DMR_LOADINGS_CONVERT_DMR table, calculates monitoring period loads, calculates aggregated nitrogen and aggregated phosphorus loads, and sums the monitoring period loads to calculate annual loads and aggregated annual nitrogen and phosphorus loads.

The Load Calculator Module creates the following tables:

• 

### DMR_LOADINGS _WORK. This database table stores the calculated pollutant loadings per monitoring period and the aggregated total nitrogen and total phosphorus loadings.

• 

### DMR_LOADINGS_ANNUAL

. This database table stores the calculated annual pollutant loads and the aggregated total nitrogen and total phosphorus loadings.

3.2.1 Select Measurement Values and Flows for Loadings Calculations

ICIS

- NPDES contains five measurement valu e fields for storing DMR measurements. These include:

• 

### Quantity 1 (MQ1);

• 

### Quantity 2 (MQ2);

• 

### Concentration 1 (MC1);

• 

### Concentration 2 (MC2); and

• 

### Concentration 3 (MC3).

The Load Calculator Module selects one of these five measurements for load calculation. EAD ’s goal for calculating annual loads is to characterize the average annual pollutant mass discharges to receiving streams. Therefore, the Load Calculator Module uses a selection hierarchy that prioritizes average values. However, average measurements may be reported in ICIS -NPDES as a quantity (kg/day) or a concentration (mg/L). Calculating annual loads from concentrations requires adding wastewater flow to the equation, which increases the uncertainty of the calculated annual load. Because using values alr eady reported as quantities in ICISNPDES requires fewer variables to calculate annual loads, the measurement selection hierarchy prioritizes average quantities over average concentrations.DRAFT Section 3 - Calculator Module Development



### 3-21 3.2.2 Calculate Monitoring Period Loads

After completing the measureme nt value selection, the Load Calculator Module has condensed the DMR data to one quantity or concentration and flow rate per record (unique by external permit number, permit feature number (outfall), limit set designator, monitoring period, monitoring location, and pollutant parameter). In this step, the Load Calculator Module performs a series of calculations to calculate average and total loads, concentrations, and flows for each monitoring period. To calculate the total monitoring period load and flow, t he Load Calculator Module uses the NMBR_OF_DAYS from the DMR_LOADINGS_CONVERT_DMR table and assumes that the discharge occurs continuously for the duration of the monitoring period. In addition, the Load Calculator Module calculates the Load

- Over

- Limit for the monitoring period by subtracting the DMR measurement value from the corresponding permit limit value. The list of calculations performed for this step includes:

• 

### Average Daily Load (kg/day);

• 

### Average Concentration (mg/L);

• 

### Average Wastewater Flow (MGD);

• 

### Monitoring Period Load (kg/monitoring period);

• 

### Monitoring Period Wastewater Flow (MG/monitoring period); and

• 

### Monitoring Period Load- Over

- Limit.

As stated in the previous section, the DMR_LOADINGS_CONVERT_DMR table stores effluent DMR data extracted from ICIS

- NPDES in five measurement value fields

. These include:

• 

### Quantity 1 (MQ1);

• 

### Quantity 2 (MQ2);

• 

### Concentration 1 (MC1);

• 

### Concentration 2 (MC2); and

• 

### Concentration 3 (MC3).

These five measurement value fields contain data from the five DMR fields that facil ities use to enter quantity and concentration data: Average Quantity, Maximum Quantity; Minimum Concentration; Average Concentration; and Maximum Concentration. However, unlike PCS, the measurement value fields in ICIS -NPDES do not correspond to the statis tical basis of the
measurement

. For example, Maximum Quantities may be entered into either MQ1 or MQ2 fields . Therefore, the Load Calculator Module requires information about the statistical basis of the measurement to determine which measurements are aver age, maximum, or minimum values.

Facilities may use a variety of measurements to populate the above five measurement value fields

. For example, a facility can use a monthly average, daily average, 30 day geometric average, etc. to represent the average qu antity

. The DMR_LOADINGS_CONVERT_DMR table contains a statistical base code field (STAT5), in which each of the five digits corresponds to one of the five measurement value fields (see Figure 3 -2). The following codes are used for the types of measurements that may be reported in ICIS:

• 

### 0 = No value reported;

• 

### 1 = Average value;

• 

### 2 = Total value;DRAFT Section 3 - Calculator Module Development



### 3-22 • 

### 3 = Maximum value; and

• 

### 4 = Minimum value.

The Load Calculator Module selects measurements for loadings calculations using a hierarchy that prioritizes average values and quantities. As a first step, the Load Calculator Module selects measurements based on permit limits data. Because the tool compares measurements to permit limits, EPA first selects measurements that are the same measurement type as the permit limit , b ut prioritizes average measurements over maximum measurements as follows:

• 

### If there is an average quantity limit (STAT = 1) , then the Load Calculator Module selects the average quantity measurement (STAT = 1) ;

• 

### If there is a total quantity limit (STAT = 2), then the Load Calculator Module selects the total quantity measurement (STAT = 2);

• 

### If there is a maximum quantity limit (STAT = 3) , then:

 If the average quantity measurement is available , then the Load Calculator Module selects the average quantity measurement (STAT = 1); and

 If the average quantity measurement is not reported, then the Load Calculator Module selects the maximum quantity measurement (STAT = 3);

• 

### If there is an average concentration limit (STAT = 1) , then the Load Calculator Module selects the average concentration measurement (STAT = 1) ;

• 

### If there is a maximum concentration limit (STAT = 3) , then:

 If t he average concentration measurement is available , then the Load Calculator Module selects the average concentration measurement (STAT = 1); and

 If the average concentration measurement is not reported, then the Load Calculator Module selects the maximum concentration measurement (STAT = 3) ; and

• 

### If there is a minimum concentration limit (STAT = 4) , then the Load Calculator Module selects the minim um concentration measurement (STAT = 4)

.
Some facilities’ NPDES permits require monitoring and reporting for a particular pollutant, but do not include a numeric limit for the pollutant discharge. If no numeric limits are provided in the permit data, the n the Load Calculator Module uses a second hierarchy to select measurement values for loading calculations. First , the Load Calculator Module attempts to identify an average value (STAT=1) by searching the STAT5 digits from left to right

. By scanning left to right, the Load Calculator Module searches the STAT5 digits that correspond to measurement fields in the following sequence:

• 

### Quantity 1 (MQ1);

• 

### Quantity 2 (MQ2);

• 

### Concentration 1 (MC1);

• 

### Concentration 2 (MC2); and

• 

### Concentration 3 (MC3).

If the Load Calcu lator Module finds a 1, then it selects the corresponding measurement for load calculation and performs the following calculations:DRAFT Section 3 - Calculator Module Development



### 3-23 • 

### If the selected measurement is a quantity (MQ1 or MQ2):

— Average daily load (kg/day) = MQ ;

— Average concentration (mg/L) = MQ/ (Flow × 3.785);

— Monitoring Period Load (kg/monitoring period) = MQ × NMBR_OF_DAYS ; and

— Monitoring Period Load Over Limit (LOL) = (MQ – LQ (Quantity Limit))

× NMBR_OF_DAYS

.

• 

### If the selected measurement is a concentration (MC1, MC2, or MC3):

— Average daily load (kg/day) = MC × Flow × 3.785;

— Average concentration (mg/L) = MC;

— Monitoring Period Load (kg/monitoring period) = MC × Flow × 3.785 × NMBR_OF_DAYS ; and

— Monitoring Period LOL = (MC – LC (Concentration Limit)) × Flow × 3.785 × NMBR_OF_DAYS

.
If the Load Calculator Module does not find an average value (STAT=1), then it scans STAT5 from left to right for a total value (STAT=2). “Total” values only apply to quantity measurements, and because these measurements represent the total mass discharge for the monito ring period, the Load Calculator Module cannot use the same calculations used for average, maximum, and minimum values. If the Load Calculator Module identifies a total value, it selects the value and performs the following calculations:

• 

### Average Daily Load (kg/day) = MQ / NMBR_OF_DAYS ;

• 

### Average Concentration (mg/L) = MQ/(Flow × NMBR_OF_DAYS × 3.785);

• 

### Monitoring Period Load (kg/monitoring period) = MQ ; and

• 

### Monitoring Period LOL = MQ – LQ

.
If the Load Calculator Module does not find an average value (STAT=1) or a total value (STAT=2), then it scans STAT5 from left to right for a maximum value (STAT=3). If the Load Calculator Module identifies a maximum value, then it selects that value and performs the same calculations used for the average values (STAT=1).

If the Load Calculator Module does not find an average value (STAT=1), total value (STAT=2), or maximum value (STAT=3), then it scans STAT5 from left to right for a minimum value (STAT=4) . If the Load Calculator Module identifies a minimum value, then it se lects that value and performs the same calculations used for the average values (STAT=1). Table 3 -8 presents the measurement value selection priorities and calculations.

Wastewater flow measurements are stored in ICIS in the same five measurement value fie lds as other pollutant measurements (MQ1, MQ2, MC1, MC2, and MC3). The Load Calculator Module of the ICIS-NPDES Pollutant Loading Tool pulls flow measurements and their corresponding STAT5 code from the ICIS DMR table and stores the measurements in a
separ ate table, DMR_LOADINGS_FLOWS (FQ1, FQ2, FC1, FC2, FC3, and FSTAT5). 5 

### The Load Calculator Module uses a hierarchy similar to the measurement value selection hierarchy to

5 Although wastewater flows may be stored in concentration fields, all wastewater flows are reported in quantity units (e.g., MGD).DRAFT Section 3 - Calculator Module Development



### 3-24 select flow rates using the FSTAT5 code. The FSTAT5 code applies the same concept as the STAT5 code, and provides information about the statistical basis of wastewater flow values. Similar to the measurement value selection hierarchy, the flow selection hierarchy prioritizes average flows.

First, the Load Calculator Module attempts to find an average flow (FSTAT=1) by scanning the FSTAT5 code from left to right. By scanning from left to right, the Load Calculator Module searches the FSTAT5 digits corresponding to the flow values in the following sequence:

• 

### Flow Quantity 1 (FQ1);

• 

### Flow Quantity 2 (FQ2);

• 

### Flow Concentration 1 (FC1);

• 

### Flow Concentration 2 (FC2); and

• 

### Flow Concentration 3 (FC3).

If the Load Calculator Module finds a 1, then it selects the corresponding flow for load calculation and performs the following calculations:

• 

### Average Daily Flow (MGD) = Flow; and

• 

### Monitoring Period Flow (MG/monitoring period) = Flow × NMBR_OF_DAYS.

If the Load Calculator Module does not find an average flow (FSTAT=1), then it scans FSTAT5 from left to right for a total flow (FSTAT=2). Because “total” flows represent the total wastewater discharge for the monitoring period, the Load Calculator Module cannot use the same calculations used for average, maximum, and minimum flows. If the Load Calculator Module identifies a total flow, it selects the value and performs the following calculations:

• 

### Average Daily Flow (MGD) = Flow/N M BR_OF_DAYS ; and

• 

### Monitoring Period Flow (MG/monitoring period) =Flow.DRAFT Section 3 - Calculator Module Development



### 3-25 Table 3 -8. Measurement Value Selection Priorities and Calculations

Priority Value Type STAT Code Average Daily Load (k g/day) Average Concentration (mg/L) Monitoring Period DMR Load (kg/monitoring period) Monitoring Period Load over Limit (kg/monitoring period) Monitoring Period Allowable Load (kg/monitoring period)
1 MQ1 1 MQ1 MQ1/(Flow×3.785) MQ1 × NMBR_OF_DAYS (MQ1

– LQ1) × NMBR_OF_DAYS L Q1 × NMBR_OF_DAYS

2 MQ2 1 MQ2 MQ2/(Flow×3.785) MQ2 × NMBR_OF_DAYS (MQ2

– LQ2) × NMBR_OF_DAYS L Q2 × NMBR_OF_DAYS

3 MC1 1 MC1 × Flow × 3.785 MC1 MC1 × Flow× 3.785 × NMBR_OF_DAYS (MC1

– LC1) × Flow × 3.785 × NMBR_OF_DAYS L C1 × Flow× 3. 785 × NMBR_OF_DAYS

4 MC2 1 MC2 × Flow × 3.785 MC2 MC2 × Flow× 3.785 × NMBR_OF_DAYS (MC2

– LC2) × Flow × 3.785 × NMBR_OF_DAYS L C2 × Flow× 3.785 × NMBR_OF_DAYS
5 MC3 1 MC3 × Flow × 3.785 MC3 MC3 × Flow× 3.785 × NMBR_OF_DAYS (MC3

– LC3) × Flow × 3.785 × NMB R_OF_DAYS L C3 × Flow× 3.785 × NMBR_OF_DAYS

6 MQ1 2 MQ1 / NMBR_OF_DA YS MQ1/(Flow × NMBR_OF_DAYS × 3.785) MQ1 MQ1

– LQ1 L Q1

7 MQ2 2 MQ2 / NMBR_OF_DA YS MQ2/(Flow × NMBR_OF_DAYS × 3.785) MQ2 MQ2

– LQ2 L Q2

8 MQ1 3 MQ1 MQ1/(Flow × 3.785) MQ1 × NMBR_OF_DAYS (M Q1

– LQ1) × NMBR_OF_DAYS L Q1 × NMBR_OF_DAYS

9 MQ2 3 MQ2 MQ2/(Flow × 3.785) MQ2 × NMBR_OF_DAYS (MQ2

– LQ2) × NMBR_OF_DAYS L Q2 × NMBR_OF_DAYS

10 MC1 3 MC1 × Flow × 3.785 MC1 MC1 × Flow× 3.785 × NMBR_OF_DAYS (MC1

– LC1) × Flow × 3.785 × NMBR_OF_DAYS L C1 × F low× 3.785 × NMBR_OF_DAYS

11 MC2 3 MC2 × Flow × 3.785 MC2 MC2 × Flow× 3.785 × NMBR_OF_DAYS (MC2

– LC2) × Flow × 3.785 × NMBR_OF_DAYS L C2 × Flow× 3.785 × NMBR_OF_DAYS

12 MC3 3 MC3 × Flow × 3.785 MC3 MC3 × Flow× 3.785 × NMBR_OF_DAYS (MC3

– LC3) × Flow × 3. 785 × NMBR_OF_DAYS L C3 × Flow× 3.785 × NMBR_OF_DAYS
13 MQ1 4 MQ1 MQ1/(Flow × 3.785) MQ1 × NMBR_OF_DAYS (MQ1

– LQ1) × NMBR_OF_DAYS L Q1 × NMBR_OF_DAYS

14 MQ2 4 MQ2 MQ2/(Flow × 3.785) MQ2 × NMBR_OF_DAYS (MQ2

– LQ2) × NMBR_OF_DAYS L Q2 × NMBR_OF_DAYSDRAFT Section 3 - Calculator Module Development



### 3-26 Priority Value Type STAT Code Average Daily Load (k g/day) Average Concentration (mg/L) Monitoring Period DMR Load (kg/monitoring period) Monitoring Period Load over Limit (kg/monitoring period) Monitoring Period Allowable Load (kg/monitoring period)
15 MC1 4 MC1 × Flow × 3.785 MC1 MC1 × Flow× 3.785 × NMBR_OF_DAYS (MC1

– LC1) × Flow × 3.785 × NMBR_OF_DAYS L C1 × Flow× 3.785 × NMBR_OF_DAYS
16 MC2 4 MC2 × Flow × 3.785 MC2 MC2 × Flow× 3.785 × NMBR_OF_DAYS (MC2

– LC2) × Flow × 3.785 × NMBR_OF_DAYS L C2 × Flow× 3.7 85 × NMBR_OF_DAYS
17 MC3 4 MC3 × Flow × 3.785 MC3 MC3 × Flow× 3.785 × NMBR_OF_DAYS (MC3

– LC3) × Flow × 3.785 × NMBR_OF_DAYS L C3 × Flow× 3.785 × NMBR_OF_DAYS
18 No Data (NODI is not null) Any NULL NULL NULL NULL NULLDRAFT Section 3 - Calculator Module Development



### 3-27 If the Load Calculator Module does not find an average flow (FSTAT=1) or a total flow (FSTAT=2), then it scans FSTAT5 from left to right for a maximum flow (FSTAT=3). If the Load Calculator Module identifies a maximum flow, then it selects that flow and performs the same calculations used f or the average flows (FSTAT=1).

If the Load Calculator Module does not find an average flow (FSTAT=1), total flow (FSTAT=2), or maximum value (FSTAT=3), then it scans FSTAT5 from left to right for a minimum flow (FSTAT=4) . If the Load Calculator Module id entifies a minimum flow, then it selects that flow and performs the same calculations used for the average flows (FSTAT=1). Table 3

- 9 presents the measurement value selection priorities and calculations.

Table 3

- 9. Flow Value Selection Priorities

Priority Value Type FSTAT5 Average Daily Flow (MGD) Monitoring Period Flow (millions of gallons/monitoring period)

1 FQ1 1 FQ1 FQ1 × NMBR_OF_DAYS

2 FQ2 1 FQ2 FQ2 × NMBR_OF_DAYS
3 FC1 1 FC1 FC1 × NMBR_OF_DAYS

4 FC2 1 FC2 FC2 × NMBR_OF_DAYS
5 FC3 1 FC3 FC3 × NMB R_OF_DAYS

6 FQ1 2 FQ1 / NMBR_OF_DAYS FQ1

7 FQ2 2 FQ2 / NMBR_OF_DAYS FQ2
8 FQ1 3 FQ1 FQ1 × NMBR_OF_DAYS
9 FQ2 3 FQ2 FQ2 × NMBR_OF_DAYS
10 FC1 3 FC1 FC1 × NMBR_OF_DAYS

11 FC2 3 FC2 FC2 × NMBR_OF_DAYS

12 FC3 3 FC3 FC3 × NMBR_OF_DAYS
13 FQ1 4 FQ1 FQ1 × NMBR_OF_DAYS

14 FQ2 4 FQ2 FQ2 × NMBR_OF_DAYS
15 FC1 4 FC1 FC1 × NMBR_OF_DAYS
16 FC2 4 FC2 FC2 × NMBR_OF_DAYS
17 FC3 4 FC3 FC3 × NMBR_OF_DAYS



### Not all pollutants reported in ICIS- NPDES and PCS are in units of measure that can be converted into a load. For example, parameters that are reported in units of number of occurrences, degrees, pH standard units, etc. are excluded from loading calculations. Table A-5 in Appendix A provides a list of pollutant parameters excluded from loadings.

Detection Limit O ptions

When pollutants are reported “not detected”, their concentrations are presumed to be below their detection limit (BDL). Permittees may report the detection limit with a “less

- than” sign (<) to indicate that the pollutant was BDL. The DMR_LOADINGS_C ONVERT_DMR table stores the “less -than” signs for nondetects in the data qualifier field that corresponds to the measurement value (i.e., MQ1_Qual, MQ2_Qual, MC1_Qual, MC2_Qual, or MC3_Qual). If a pollutant is BDL, the pollutant concentration may be between zero and the detection limit. TheDRAFT Section 3 - Calculator Module Deve lopment



### 3-28 Load Calculator Module calculates three versions of each monitoring period load and concentration using each of the following assumptions:

• 

### BDL equals zero;

• 

### BDL equals the detection limit; or

• 

### BDL equals one- half the detec tion limit.

Load- Over

- Limit (LOL) Options

T he Load Calculator Module compares the monitoring period loads to the NPDES permit limits on a mass basis

. Table 3 -8 shows the load-over- limit equations for each measurement value field and statistical basis . The Load Calculator creates two options for analyzing the monitoring period load- over

- limit:

• 

### LOL1 – If the Monitoring Period LOL is negative, then set the LOL to zero; and

• 

### LOL2 – If the Monitoring Period LOL is negative, then retain the calculated negative value.

For example, if the DMR Monitoring Period Load is 200 and the Limit Monitoring Period Load is 205, then the result for LOL1 would be 0 and the result for LOL2 would be -5.

DMR_LOADINGS_WORK Table

The Load Calculator Module applies the DL options and LOL options, to create the following output fields for DMR_LOADINGS_WORK:

• 

### Average Daily Loads (kg/day):

— AVG_DAILY_LOAD_BDL0 ;

— AVG_DAILY_LOAD_BDLH (BDL = 1⁄2 DL) ; and

— AVG_DAILY_LOAD_BDL1 (BDL = DL)

.

• 

### Average Concentrations (mg/L):

— AVG_CONC_BDL0 ;

— AVG_CONC_BD LH (BDL = 1⁄2 DL) ; and

— AVG_CONC_BDL1 (BDL = DL)

.

• 

### Average Daily Wastewater Flows (MGD):

— AVG_DAILY_FLOW_BDL0 ;

— AVG_DAILY_FLOW_BDLH (BDL = 1⁄2 DL) ; and

— AVG_DAILY_FLOW_BDL1 (BDL = DL)

.

• 

### Total Monitoring Period DMR Loads (kg/monitoring period):

— DMR_LOAD_BDL0 ;

— DMR_L OAD_BDLH (BDL = 1⁄2 DL) ;

— DMR_LOAD_BDL1 (BDL = DL) ; and

— ALLOWABLE_LOAD

.DRAFT Section 3 - Calculator Module Development



### 3-29 • 

### Total Monitoring Period Flow (millions of gallons/ monitoring period)

— MONPER_FLOW_BDL0 ;

— MONPER_FLOW_BDLH (BDL = 1⁄2 DL) ; and

— MONPER_FLOW_BDL1 (BDL = DL)

.

• 

### Monitoring Period Load- Over

- Limit Option 1 Calculations (using zero if load is less than limit):

— MP_LOL1_BDL0 ;

— MP_LOL1_BDLH (BDL = 1⁄2 DL) ; and

— MP_LOL1_BDL1 (BDL = DL)

.

• 

### Monitoring Period Load- Over -Limit Option 2 Calculations (using negative value if load is less than limit):

— MP_LOL2_BDL0 ;

— M P_LOL2_BDLH (BDL = 1⁄2 DL) ; and

— MP_LOL2_BDL1 (BDL = DL)

.

3.2.3 Aggregate Nutrient Loads

The Loading Tool provides the user with the option of calculating aggregated loads for nitrogen-containing parameters and phosphorus-containing parameters. Nutrient aggregation approximates a total nitrogen and total phosphorus load by combining loads for various nitrogen and phosphorus species. Total nitrogen consists of organic nitrogen, ammonia, nitrate, and nitrite; or total Kjeldahl nitrogen (TKN), nitrate, and nitrite . Typically, organic nitrogen and TKN account for the majority of the total nitrogen load. As a result, if a facility is not required to report total nitrogen, TKN, or organic nitrogen; the loadings tool calculation will underestimate the aggregated nitrogen load . Phosphorus compounds may be reported as phosphorus or phosphate in ICIS- NPDES and PCS . This subsection describes the nutrient aggregation methodology.

For nitrogen and phosphorus parameters, the Loading Tool calculates an aggregate nitrogen load and aggregate phosphorus load for each outfall and monitoring location at each facility using the following logic:

• 

### For Total Nitrogen (as N):

— If Total Nitrogen is reported, use that load for total nitrogen;

— If Total Nitrog en is not reported and TKN is >0, then Total Nitrogen = TKN + Nitrate + Nitrite;

— If TKN is not rep orted and Organic Nitrogen is > 0, then Total Nitrogen = Organic Nitrogen + Ammonia + Nitrate + Nitrite; and

— If Organic Nitrogen is not reported, then Total Nitrogen = Ammonia + Nitrate + Nitrite. 6

6 Note: The nitrogen aggregation calculations do not require th at all pollutants in the equation have nonzero values. To use the TKN equation, only TKN must be nonzero. To use the Organic Nitrogen equation, only Organic Nitrogen must be nonzero. The fourth equation, which sums ammonia, nitrate, and nitrite, simply sums any nonzero values reported for any of the three parameters.DRAFT Section 3 - Calculator Module Development



### 3-30 • 

### For Total Phosphorus (as P):

— If Total Phosphorus is reported, then use that load for total phosphorus; and

— If Total Phosphorus is not reported, then use the Phosphate load and convert to kilograms of phosphorus.

Table 3

- 10 presents the priorities that the Load Calculator Module uses to select nutrient parameters for inclusion in the total nitrogen calculation. Using these priorities, the Load Calculator Module selects one parameter to represent Total Nitrogen, TKN, Nitrate, Nitrite, Organic Nitrogen, an d Ammonia. The Load Calculator Module stores the aggregated nitrogen loads in the DMR_LOADINGS_WORK table, and assigns a nutrient type flag (TYPE_NUTRIENT) of “N” and a nutrient aggregation flag (NUTAGGFLAG) of “A”.

Table 3

- 11 presents the priorities that the Load Calculator Module uses to select nutrient parameters for inclusion in the total phosphorus calculation. The Load Calculator Module stores the aggregated phosphorus loads in the DMR_LOADINGS_WORK table, and assigns a nutrient type flag (TYPE_NUTRIE NT) of “P” and a nutrient aggregation flag (NUTAGGFLAG) of “A”.

Table 3

- 10. Priorities for Nitrogen Aggregation

Calculation

Priority PRAM CODE PRAMDESC Conversion Factor Calculation Parameter PRAM Selection Priority
1 Equation 1: Total Nitrogen = TOTAL N
00600 Nitrogen, total (as N) 1 TOTAL N 1
51445 Nitrogen, Total 1 TOTAL N 2
51084 Nitrogen, total available (water) 1 TOTAL N 3
2 Equation 2: Total Nitrogen = TKN + NITRATE + NITRITE
00625 Nitrogen, Kjeldahl, total (as N) 1 TKN 1
51449 Nitrogen, Kjeldahl Total 1 TKN 2
51087 Nitrogen, Kjeldahl, total (TKN) (water) 1 TKN 3
82539 Nitrogen, Kjeldahl 1 TKN 4
49579 Nitrogen, total Kjeldahl 1 TKN 5
81639 Nitrogen Kjelda h l , total (TKN) 1 TKN 6
00623 Nitrogen, Kjeldahl, dissolved (as N) 1 TKN 7
00620 NITROGEN, NITRATE TOTAL (AS N) 1 NITRATE 1
71850 Nitrogen, nitrate total (as NO 3 ) 0.225806452 NITRATE 2
51100 Nitrogen, total, as NO 3 , (water) 0.225806452 NITRATE 3
51086 Nitrogen, nitrate (NO 3 ), (water) 0.225806452 NITRATE 4
00615 Nitro gen, nitrite total (as N) 1 NITRITE 1
51447 Nitrogen, Nitrite Total 1 NITRITE 2
00613 Nitrite nitrogen, dissolved (as N) 1 NITRITE 3
71855 NITROGEN, NITRITE TOTAL (AS NO2) 0.304347826 NITRITE 4DRAFT Section 3 - Calculator Module Development



### 3-31 Table 3

- 10. Priorities for Nitrogen Aggregation

Calculation

Priority PRAM CODE PRAMDESC Conversion Factor Calculation Parameter PRAM Selection Priority
3 Equation 3: Total Nitrogen = ORG N + AMMON+ NITRAT E + NITRITE
00605 Nitrogen, organic total (as N) 1 ORG N 1
00607 Nitrogen, organic, dissolved (as N) 1 ORG N 2
00610 Nitrogen, ammonia total (as N) 1 AMMON 1
51446 Nitrogen, Ammonia Total 1 AMMON 2
00609 Ammonia nitrogen, total, (as N) 30 day 1 AMMON 3
00612 Nitrogen, ammonia, tot unionized (as N) 1 AMMON 4
34726 Nitrogen, ammonia, total (as NH 3 ) 0.823529412 AMMON 5
51085 Nitrogen, ammonia (NH 3 - N), (water) 0.823529412 AMMON 6
61574 Ammonia (as N) + unionized ammonia 0.823529412 AMMON 7
00619 Ammonia, unionized 0.823529412 AMMON 8
00608 Nitrogen, ammonia dissolved 0.823529412 AMMON 9
82230 Total Ammonia & Ammonium 0.776409915 AMMON 10
71845 Nitrogen, Ammonia Total (as NH 4 ) 0.776409915 AMMON 11
00620 NITROGEN, NITRATE TOTAL (AS N) 1 NITRATE 1
71850 Nitrogen, nitrate total (as NO 3 ) 0.225806452 NITRATE 2
51100 Nitrogen, total, as NO 3 , (water) 0.225806452 NITRATE 3
51086 Nitrogen, nitrate (NO 3 ), (water) 0.225806452 NITRATE 4
00615 Nitrogen, nitrite total (as N) 1 NITRITE 1
51447 Nitrogen, Nitrite Total 1 NITRITE 2
00613 Nitrite nitrogen, dissolved (as N) 1 NITRITE 3
71855 NITROGEN, NITRITE TOTAL (AS NO 2) 0.304347826 NITRITE 4
4 Equation 4: Total Nitrogen = AMMON+ NITRATE + NITRITE
00610 Nitrogen, ammonia total (as N) 1 AMMON 1
51446 Nitrogen, Ammonia Total 1 AMMON 2
00609 Ammonia nitrogen, total, (as N) 30 day 1 AMMON 3
00612 Nitrogen, ammonia, tot unionized (as N) 1 AMMON 4
34726 Nitrogen, ammonia, total (as NH 3 ) 0.823529412 AMMON 5
51085 Nitrogen, ammo nia (NH 3 - N), (water) 0.823529412 AMMON 6
61574 Ammonia (as N) + unionized ammonia 0.823529412 AMMON 7
00619 Ammonia, unionized 0.823529412 AMMON 8
00608 Nitrogen, ammonia dissolved 0.823529412 AMMON 9
82230 Total Ammonia & Ammonium 0.776409915 AMM ON 10
71845 Nitrogen, Ammonia Total (as NH 4 ) 0.776409915 AMMON 11
00620 NITROGEN, NITRATE TOTAL (AS N) 1 NITRATE 1
71850 Nitrogen, nitrate total (as NO 3 ) 0.225806452 NITRATE 2
51100 Nitrogen, total, as NO 3 , (water) 0.225806452 NITRATE 3
51086 N itrogen, nitrate (NO 3 ), (water) 0.225806452 NITRATE 4
00615 Nitrogen, nitrite total (as N) 1 NITRITE 1
51447 Nitrogen, Nitrite Total 1 NITRITE 2
00613 Nitrite nitrogen, dissolved (as N) 1 NITRITE 3
71855 NITROGEN, NITRITE TOTAL (AS NO 2 ) 0.304347 826 NITRITE 4DRAFT Section 3 - Calcul ator Module Development



### 3-32 Table 3

- 11. Priorities for Phosphorus Aggregation

Calculation

Priority PRAM CODE PRAM DESC Conversion Factor Calculation Parameter PRAM Selection Priority
1

– Total Phosphorus = Phosphorus (PHOSP)

1 00665 Phosphorus, total (as P) 1 PHOSP 1
51451 Phosphorus, Total 1 PHOSP 2
70507 Phosphorous, in total orthophosphate 1 PHOSP 3
00670 Phosphorous, total organic (as P) 1 PHOSP 4
00662 Phosphorous, total recoverable 1 PHOSP 5
00666 Phosphorus, dissolved 1 PHOSP 6 IF NO TOTAL PHOSP, t hen 2

– Total Phosphorus = Phosphate (PO 4 )

2 00650 Phosphate, total (as PO 4 ) 0.326315789 PO 4 1
00660 Phosphate, ortho (as PO 4 ) 0.326315789 PO 4 2
00655 Phosphate, poly (as PO 4 ) 0.326315789 PO 4 3
71888 Phosphorus, total soluble (as PO 4 ) 0.326315789 PO 4 4
70505 Phosphate, total, color method (as P) 0.326315789 PO 4 5



### 3.2.4 Calculating the Estimation Factor

To avoid underestimating pollutant loads, the Loading Tool Advanced Search (discussed in Section 4.5) provides users with the option to estimate discharges for monitoring periods where no pollutant quantities or concentrations are reported. To correctly estimate discharges, the Loading Tool must account for variations in monitoring frequencies for pollutants and periods of no discharge at a facility’s ou tfall. The Load Calculator Module calculates an estimation factor that is stored in the DMR_LOADINGS_ANNUAL table and is used by the Advanced Search to perform the estimation function when queried by the user. This section describes the logic that the Load Calculator Module uses to calculate the estimation factor

. Section 4.5 describes how the Advanced Search applies the factor to perform the estimation.

For monitoring periods where no pollutant quantities or concentrations are reported, facilities indicate the reason that no measurements are reported in the No Data Indicator (NODI_Code) field. In some cases, it is reasonable to assume that a discharge occurred during the monitoring period. For example, NODI = G means that monitoring was required for that

mo nitoring period but not reported due to a sampling equipment failure. In other cases, it is not reasonable to assume a discharge occurred. For example, NODI = C means that no discharge occurred for that monitoring period. The Load Calculator Module assumes no discharge for the following NODI codes:

• 

### 2: Operations shutdown;

• 

### 4: Discharge to Lagoon/Groundwater;

• 

### 7: No Influent;

• 

### 9: Conditional Monitoring;

• 

### B: Below Detection Limit;

• 

### C: No discharge;DRAFT Section 3 - Calculator Module Development



### 3-33 • 

### I: Land Applied;

• 

### J: Recycled – Water

- Closed System;

• 

### Q: Not Quanti fiable; and

• 

### W: Dry Lysimeter/Well.

The NMBR_OF_REPORT field in the DMR_LOADINGS_WORK table provides information about the number of months represented in each monitoring period. For example, if a facility is required to report quarterly, the NMBR_OF_REPORT assigned to each quarterly report is 3. The monitoring period NMBR_OF_REPORT values for a calendar year sum up to 12. The Load Calculator Module accounts for monitoring periods where DMR data are not reported by setting the NMBR_OF_REPORT field to zero if the NODI code for that monitoring period does not match the above list. As a result, the Loading Tool can identify annual datasets that are missing discharges for some monitoring periods by determining whether the annual sum of the NMBR_OF_REPORT values equals 12. For example:

• 

### If a facility reported quarterly concentrations for three of the four quarters and entered NODI = C for the fourth quarter, then the sum of NMBR_OF_REPORT values will be 12 (3+3+3+3).

• 

### In contrast, if the facility reported concentrat ions for three of four quarters and entered NODI=G for the fourth quarter, the sum of the NMBR_OF_REPORT values will be 9 (3+3+3)

.
The Load Calculator Module Calculates the estimation factor as 12 divided by the annual sum of the NMBR_OF_REPORT values. Using the above example, if the sum of NMBR_OF_REPORT values is 12, then the estimation factor is 1 (12/12). If the sum of NMBR_OF_REPORT values is 9, then the estimation factor is 1.33 (12/9).

3.2.5 Calculate Annual Loads

The Load Calculator Module calculates t he following annual averages and totals by averaging and summing the monitoring period discharges from DMR_LOADINGS_WORK, and stores the annual loads and concentrations in the DMR_LOADINGS_ANNUAL table:

• 

### Annual Pollutant Loads (kg/yr):

— TOTAL_YEAR_POLLUTANT_ LOAD_BDL0 ;

— TOTAL_YEAR_POLLUTANT_LOAD_BDLH (BDL = 1⁄2 DL) ;

— TOTAL_YEAR_POLLUTANT_LOAD_BDL1 (BDL = DL) ; and

— TOTAL_ALLOWABLE_LOAD;

• 

### Annual Average Pollutant Loads (kg/day):

— YEAR_AVG_POLLUTANT_LOAD_BDL0 ;

— YEAR_AVG_POLLUTANT_LOAD_BDLH (BDL = 1⁄2 DL) ; and

— YEAR_AVG_PO LLUTANT_LOAD_BDL1 (BDL = DL)

.

• 

### Annual Wastewater Flows (millions of gallons/yr):

— YEAR_WASTEWATER_FLOW_BDL0 ;

— YEAR_WASTEWATER_FLOW_BDLH (BDL = 1⁄2 DL) ; and

— YEAR_WASTEWATER_FLOW_BDL1 (BDL = DL)

.DRAFT Section 3 - Calculator Module Development



### 3-34 • 

### Annual Average Wastewater Flows (MGD):

— YEAR_AVG_WASTEWATER_FLOW_B DL0 ;

— YEAR_AVG_WASTEWATER_FLOW_BDLH (BDL = 1⁄2 DL) ; and

— YEAR_AVG_WASTEWATER_FLOW_BDL1 (BDL = DL)

.

• 

### Annual Average Concentrations (mg/L):

— YEAR_AVG_CONCENTRATION_BDL0 ;

— YEAR_AVG_CONCENTRATION_BDLH (BDL = 1⁄2 DL) ; and

— YEAR_AVG_CONCENTRATION_BDL1 (BDL = DL)

.

• 

### Annual Load

- Over

- Limit Option 1 Calculations (using zero if load is less than limit):

— TOTAL_LOAD_OVER_LIMIT_1_BDL0 ;

— TOTAL_LOAD_OVER_LIMIT_1_BDLH (BDL = 1⁄2 DL) ; and

— TOTAL_LOAD_OVER_LIMIT_1_BDL1 (BDL = DL)

.

• 

### Annual Load

- Over -Limit Option 2 Calculations (using negat ive value if load is less than limit):

— TOTAL_LOAD_OVER_LIMIT_2_BDL0 ;

— TOTAL_LOAD_OVER_LIMIT_2_BDLH (BDL = 1⁄2 DL) ; and

— TOTAL_LOAD_OVER_LIMIT_2_BDL1 (BDL = DL)

.

• 

### Annual Average Wastewater Temperature ( °F) and pH:

— AVG_TEMP ; and

— AVG_PH

.
Figure 3 -6 presents the output from the Load Calculator Module. These database tables support the Advanced Search user interface queries described in Section 4.5.DRAFT Section 3 - Calculator Module Development



### 3-35 DMR_LOADINGS_WORK

LOADINGS_ID

NMBR_OF_REPORTS

SELECTED_STAT

SELECTED_TYPE

AVG_DAILY_LOAD_BDL0

AVG_CONC_BDL0

AVG_DAILY_FLOW_BDL0

DMR_LOAD_BDL0

MONPER_FLOW_BDL0

MP_LOL1_BDL0

MP_LOL2_BDL0

AVG_DAILY_LOAD_BDLH

AVG_CONC_BDLH

AVG_DAILY_FLOW_BDLH

DMR_LOAD_BDLH

MONPER_FLOW_BDLH

MP_LOL1_BDLH

MP_LOL2_BDLH

AVG_DAILY_LOAD_BDL1

AVG_CONC_BDL1

AVG_DAILY_FLOW_BDL1

DMR_LOAD_BDL1

MONPER_FLOW_BDL1

MP_LOL1_BDL1

MP_LOL2_BDL1

ALLOWABLE_LOAD

SELECTED_FSTAT

SELECTED_FTYPE

NUTRIENT_FACTOR

TYPE_NUTRIENT

NUTAGGFLAG

DMR_YEAR DMR_LOADINGS_ANNUAL

YEAR

EXTERNAL_PERMIT_NMBR

PERM_FEATURE_NMBR

LIMIT_SET_DESIGNATOR

PARAMETER_CODE

POLLUTANT_CODE

GROUP_CODE

MONITORING_LOCATION_CODE

TOTAL_YEAR_NMBR_OF_REPORT

TOTAL_YEAR_POLLUTANT_LOAD_BDL0

YEAR_WASTEWATER_FLOW_BDL0

TOTAL_LOAD_OVER_LIMIT_1_BDL0

TOTAL_LOAD_OVER_LIMIT_2_BDL0

TOTAL_YEAR_POLLUTANT_LOAD_BDLH

YEAR_WASTEWATER_FLOW_BDLH

TOTAL_LOAD_OVER_LIMIT_1_BDLH

TOTAL_LOAD_OVER_LIMIT_2_BDLH

TOTAL_YEAR_POLLUTANT_LOAD_BDL1

YEAR_WASTEWATER_FLOW_BDL1

TOTAL_LOAD_OVER_LIMIT_1_BDL1

TOTAL_LOAD_OVER_LIMIT_2_BDL1

YEAR_AVG_POLLUTANT_LOAD_BDL0

YEAR_AVG_CONCENTRATION_BDL0

YEAR_AVG_WASTEWATER_FLOW_BDL0

YEAR_AVG_POLLUTANT_LOAD_BDLH

YEAR_AVG_CONCENTRATION_BDLH

YEAR_AVG_WASTEWATER_FLOW_BDLH

YEAR_AVG_POLLUTANT_LOAD_BDL1

YEAR_AVG_CONCENTRATION_BDL1

YEAR_AVG_WASTEWATER_FLOW_BDL1

TOTAL_ALLOWABLE_LOAD

AVG_TEMP

AVG_PH

TYPE_NUTRIENT

NUTAGGFLAG DMR_LOADINGS_CONVERT_DMR

EXTERNAL_PERMIT_NMBR

PERM_FEATURE_NMBR

LIMIT_SET_DESIGNATOR

LIMIT_SEASON_ID

LQ1

LQ2

LC1

LC2

LC3

STAT5

MONITORING_PERIOD_END_DATE

NMBR_OF_REPORT

MONITORING_LOCATION_CODE

PARAMETER_CODE

NODI_CODE

Q1_QUAL

Q2_QUAL

C1_QUAL

C2_QUAL

C3_QUAL

MQ1

MQ2

MC1

MC2

MC3

LOADINGS_ID

NMBR_OF_DAYS

AVG_TEMP

AVG_PH

DATA_SOURCE

DMR_YEAR

LT_DMRS_XTB_ID

LT_FLOW_ID DMR_LOADINGS_FLOWS

EXTERNAL_PERMIT_NMBR

PERMIT_FEATURE_NMBR

MONITORING_LOCATION_CODE

MONITORING_PERIOD_END_DATE

LIMIT_SET_DESIGNATOR

LIMIT_SEASON_ID

PARAMETER_CODE

NMBR_OF_REPORT

NMBR_OF_DAYS

FQ1

FQ2

FC1

FC2

FC3

FSTAT5

DATA_SOURCE

LT_FLOW_ID

NODI_CODE

DUP



### F igure 3 -6 . Load Calculator Module Output Tables and Relationships

3.3 EZ Search Database Development

In addition to providing users access to annual pollutant loadings, EPA developed the EZ Search interface to allow uses to perform analyses on the loadings. These analyses include calculating TWPE; ranking discharges by industry, watershed, and pollutant; and calculating loadings for pollutant categories. In addition, the EZ Search interface provides users access to the point source category rankings developed by EAD’s screening- level analysis . Including these features in the Loading Tool required additional backend databas e development

. Therefore, EPA developed a separate loadings module and database tables in the Loading Tool to provide the input to the EZ Search queries. Figure 3 -7 presents the EZ Search back

- end database.DRAFT Section 3 - Calculator Module Development



### 3-36 DMR_LOADINGS_PSC_304M_XWALK

EXTERNAL_PERMIT_NMBR

PARAMETER_CODE

PSC_CODE_304_M

DMR_LOADINGS_PSC_STD_XWALK

EXTERNAL_PERMIT_NMBR

PARAMETER_CODE

PSC_CODE_STD DMR_LOADINGS_EZ_SRCH_CALCS

EXTERNAL_PERMIT_NMBR

PARAMETER_CODE

AVG_CONC

MAX_CONC

LBY

TWPE

AVG_DAILY_FLOW_BDLH

AVG_TEMP

MAX_TEMP

RUNYEAR

NUTAGGFLAG

LIMITS_INFO_FLAG

DMR_INFO_FLAG

ALLOWABLE_LOAD

ANNUAL_FLOW DMR_LOADINGS_REF_PARAMETER

PARAMETER_CODE

PARAMETER_DESC

POLLUTANT_CODE

POLLUTANT_DESC

GROUP_CODE

PRIORITY

NUTRIENT_AGG_PHOSP

NUTRIENT_AGG_NITRO

NUTRIENT_AGG_N_CVT

DELETE_FROM_LOADINGS

PCS_PARAMETER_CODE

CHEMICAL_ABSTRACT_SERVICE_NMBR

TWF

SRS_ID DMR_LOADINGS_PARM_CAT_XWALK

PARAMETER_CODE

CATEGORY

CAT_PRIORITY REF_PSC

PSC_CODE

PSC_DESC

TRI_TWPE

RCNT_REV

DMR_LOADINGS_FACILITIES_IDS

EXTERNAL_PERMIT_NMBR

UIN

TRI_ID_1

TRI_ID_2

TRI_ID_3

TRI_ID_4

TRI_ID_5

TRI_ID_6

TRI_ID_7

CWNS_ID_1

CWNS_ID_2

CWNS_ID_3

CWNS_ID_4

CWNS_ID_5



### Figure 3 -7 . EZ Search Back -End Database

The DM R_LOADINGS_EZ_SRCH_CALCS table, contains annual pollutant loadings for each unique NPDES permit and parameter group. The EZ Search Load Module creates the DMR_LOADINGS_EZ_SRCH_CALCS table using the annual loadings in the DMR_LOADINGS_ANNUAL table and the f ollowing calculation methodologies:

• 

### Assign Pollutant Parameters to Parameter Groups. The EZ Search Load Module

a ssigns parameters to groups to avoid double counting loads for parameters that represent the same pollutant (e.g., total lead and dissolved lead). The DMR_LOADINGS_REF_PARAMETER table includes a field for the parameter group assignment and ranks the parameters in each group to give priority to pollutant parameters that represent a total discharge. The EZ Search Load Module selects one load per par ameter group and NPDES ID by selecting the loads for the parameters from the DMR_LOADINGS_ANNUAL table that have the lowest priority number in DMR_LOADINGS_REF_PARAMETER.DRAFT Section 3 - Calculator Module Development



### 3-37 • 

### Apply Hybrid Method for Nondetects. As described in Section 3.2.2, the Load Calculat or Module calculates annual loads for three detection limit options: 1) set nondetects equal to zero, 2) set nondetects equal to one half the detection limit, and 3) set nondetects equal to the detection limit. To simplify the loadings data input to the EZ Search, the DMR_LOADINGS_EZ_SRCH_CALCS table contains only one set of loads. The EZ Search Load Module uses a hybrid of detection limit options 1 and 2 to calculate the annual pollutant loadings:

— If the pollutant was measured nondetect for all monitoring periods in a reporting year, then the EZ Search Load Module sets the annual pollutant load to zero; and

— If the pollutant was detected for at least one monitoring period in a reporting year, then the EZ Search Load Module calculates the annual pollutant load by setting the nondetects equal to one half the detection limit.

• 

### Apply Estimation Function. This function determines whether the annual loads in DMR_LOADINGS_ANNUAL include measurements for all months of the reporting year. If measurements were not repo rted for a particular monitoring period, then the EZ Search Load Module normalizes the calculated annual load to 12 months per year.

• 

### Toxic-Weighted Pound Equivalent (TWPE) Calculation. EPA calculates TWPE for the screening

- level analysis to rank the human health and environmental hazard potential of pollutant loadings. TWPE is the mass of a pollutant or chemical discharged that accounts for its relative toxicity. To convert pollutant loadings into TWPE, EPA multiplies the pollutant mass (in pounds) by its toxic weighting factor (TWF). EAD developed TWFs for use in its effluent limitations guidelines and standards (ELGs) development program to allow comparison of pollutants with varying toxicities. 7 

### The EZ Search Load Module converts pollutant loadings in the DMR_LOADINGS_ANNUAL table from kilograms to pounds and from pounds to TWPE. The calculated annual pound and TWPE are stored in the DMR_LOADINGS_EZ_SRCH_CALCS table. If a pollutant does not have a TWF (e.g., BOD 5 

### ) then the EZ Search Load Module sets the TWPE to NULL.

T he EZ Search uses the following reference tables to execute queries:

• 

### DMR_LOADINGS_PSC_STD_XWALK

. T his table links unique NPDES IDs and parameter codes to point source categories. The standard point source category field (PSC_CODE_STD) assi gns records to existing point source categories. The EZ Search uses these category assignments for all EZ Search queries (except in the View EPA’s Industrial Category Rankings link).

7 See Section 5 of Technical Support Document for the Annual Review of Existing Effluent Guidelines and Identification of Potential New Point Source Categories (2009 SLA Report) for more inform ation about TWFs (U.S. EPA, 2009).DRAFT Section 3 - Calculator Module Development



### 3-38 • 

### DMR_LOADINGS_PSC_304M_XWALK. This table links unique NPDES IDs and param eter codes to point source categories. The 304m point source category field (PCS_CODE_304m) assigns records to existing point source categories, potential new categories, and categories currently under consideration for revision. The EZ Search uses these a ssignments only for creating EPA’s current industrial category rankings.

• 

### REF_PSC

. This table contains the descriptions for the point source category codes in REF_PSC_XWALK. In addition, this table provides the TWPE the EPA calculated from Toxics Release Inventory (TRI) data for each point source category during the screening -level analysis. This table also flags point source categories that have recently undergone EPA review.

• 

### DMR_LOADINGS_PARM_CAT_XWALK

. This table assigns parameter groups to larger poll utant categories (e.g., metals, priority pollutants).

• 

### DMR_LOADINGS_FACILITIES_IDS

. This table links NPDES permit ID numbers from PCS and ICIS to FRS IDs, Clean Watershed Needs Survey (CWNS) IDs, and TRI IDs.

E Z Search Pollutant Category Loadings Developm ent

This section describes the DMR_LOADINGS_PARM_CAT_XWALK table development, which the EZ Search uses to calculate pollutant category loads.

The Loading Tool calculates mass loadings for individual pollutants (i.e., chemical compounds with a unique Chemi cal Abstract Services (CAS) Number) . To enhance querying capabilities for the general public in the EZ Search, EPA developed a methodology to calculate pollutant loadings and TWPE for broad pollutant categories. EPA selected the following pollutant categories for inclusion in the Loading Tool because they include chemicals of interest for most water programs and, in many cases, correspond to common causes of impairments in receiving water bodies:

• 

### Metals;

• 

### Priority Pollutants;

• 

### Suspended Solids;

• 

### Organic Enri chment;

• 

### Nutrients

- Nitrogen;

• 

### Nutrients -Phosphorus;

• 

### Pathogen Indicators;DRAFT Section 3 - Calculator Module Development



### 3-39 • 

### Temperature. 8 

### ; and

• 

### Wastewater Flow

3.3.1 General Methodology for Grouping Pollutants into Categories

The EZ Search calculates pollutant category loads by assigning pollutant categories to pollutant parameters and aggregating the loads based on specified priorities. EPA developed the DMR_LOADINGS_PARM_CAT_XWALK table to assign relevant parameters to pollutant categories and assign priorities for aggregating the category loads

. Figure 3 -8 pr esents the database relationship between the DMR_LOADINGS_PARM_CAT_XWALK table and the DMR_LOADINGS_EZ_SRCH_CALCS table. Table 3 -12 provides an example of the entries related to copper in the DMR_LOADINGS_PARM_CAT_XWALK table.

ANNUAL_FLOW DMR_INFO_FLAG LIMITS_INFO_FLAG

NUTAGGFLAG

RUNYEAR MAX_TEMP AVG_TEMP AVG_DAILY_FLOW_BDLH TWPE

LBY MAX_CONC AVG_CONC PARAMETER_CODE EXTERNAL_PERMIT_NMBR DMR_LOADINGS_EZ_SRCH_CALCS

ANNUAL_FLOW DMR_INFO_FLAG LIMITS_INFO_FLAG

NUTAGGFLAG

RUNYEAR MAX_TEMP AVG_TEMP AVG_DAILY_FLOW_BDLH TWPE

LBY MAX_CONC AVG_CONC PARAMETER_CODE EXTERNAL_PERMIT_NMBR DMR_LOADINGS_EZ_SRCH_CALCS

CAT_PRIORITY CATEGORY PARAMETER_CODE DMR_LOADINGS_PARM_CAT_XWALK



### Figure 3 -8. Database Table Relationships for Pollutant Categories

8 Although this section uses the term pollutant category “loads”, EPA notes that temperature and pathogen indicators do not use standard units that can be converted into a load (i.e., kg/day or mg/L). The standard units f or temperature are degrees Fahrenheit and the units for pathogen indicators are counts/100mL. The EZ Search displays results for these two categories using the average and max values in their standard units. Results for all other categories are presented a s concentrations (mg/L) and loads (lb/yr). E PA excluded any parameters that are reported in units of measure that could not be converted into the standard units for the pollutant category (e.g., percent, visual observation, severity)

.DRAFT Section 3 - Calculator Module Development



### 3-40 Table 3

- 12. Example Records from DMR_LOADINGS_PARM_CAT_XWALK

PRAM Code Parameter Description Parameter Group Group Priority Pollutant Category Category Priority
01042 Copper, total (as Cu) CU 1 MET, PP 1
01256 Cop per CU 2 MET, PP 1
00159 Copper, total per batch CU 3 MET, PP 1
01119 Copper, total recoverable CU 4 MET, PP 1
46394 Copper, dry weight CU 5 MET, PP 1
01306 Copper, potentially dissolved CU 6 MET, PP 1
01041 Copper, suspended (as Cu) CU 7 MET, PP 1
0 1040 Copper, dissolved (as Cu) CU 7 MET, PP 1



### The Loading Tool uses two levels of aggregation to calculate category loads. The first level of aggregation groups pollutant parameters that represent the same pollutant. For example, the Loading Tool groups all copper parameters (i.e. dissolved copper, total copper, total recoverable copper, etc.) together into the group labeled CU as shown in the Parameter Group column in Table 3-12. The second level of aggregation categorizes all pollutant groups for a part icular pollutant category. For instance, the copper group (CU) is included in both the metal (MET) and priority pollutant (PP) categories as shown in the Pollutant Category column in Table 3-12. The Loading Tool uses the following steps to perform the cate gory aggregation:

• 

### Step 1 – Identify Parameters Included in Category. The Loading Tool links the DMR_LOADINGS_EZ_SRCH_CALCS table with the DMR_LOADINGS_PARM_CAT_XWALK table by parameter code

. The Loading Tool then searches the DMR_LOADINGS_PARM_CAT_XWALK ta ble for parameter codes that are assigned to the pollutant category queried by the user

. For example, if the user queries for metals loads, the Loading Tool searches the table for parameter codes that are flagged as metals (“MET”) in the Pollutant Category column.

• 

### Step 2 – Aggregate Loads by Parameter Group. For each facility in the query output, the Loading Tool selects one parameter from each parameter group by searching the Group Priority column for the parameter code with the minimum priority value. For example, if a facility reports two copper parameters: one with a priority 2 (“Copper”) and one with a priority 4 (“Copper, total recoverable”), the Loading Tool will select the parameter with the priority of 2 (“Copper”) to represent the copper load for that facility

. As a general rule for parameter groupings, the Loading Tool gives preference to pollutant parameters that express total discharges (e.g., total copper) over parameters that express partial discharges (e.g., total recoverable copper).

• 

### Ste p 3 – Aggregate Category Loads by Facility. The Loading Tool then aggregates the loads for the parameter groups using the same principal as the parameter grouping step

. It searches the Category Priority column for parameter groups with the minimum category priority, and then sums the loads for the selected parameter groups

. For metals, all parameter groups are assigned aDRAFT Section 3 - Calculator Module Development



### 3-41 category priority of 1; therefore, the Loading Tool sums all loads for all metal parameter groups . For other pollutant categories, such as organic enrichment, the Loading Tool prioritizes certain parameter groups over others and does not simply sum the loads for all groups. These category -specific aggregation methodologies are described in the following subsections.

3.3.1.1 Metals

Table 3 -13 below provides an example of the pollutant group and assigned priorities for aluminum and copper. The following discusses the scope of the metals category, general rules for priorities, and aggregation methodology:

Table 3

- 13. Aluminum and Copper Groups Assigned Priorities a

PRAM Code Parameter Description Pollutant Group Group Priority Category Priority
01105 Aluminum, total (as Al) AL 1 1
01251 Aluminum AL 2 1
82056 Aluminum, total AL 3 1

01104 Aluminum, total recoverable AL 4 1

01308 Aluminum, potentiall y dissolved AL 5 1
85824 Aluminum, acid soluble AL 6 1

01106 Aluminum, dissolved (as Al) AL 7 1

01042 Copper, total (as Cu) CU 1 1
01256 Copper CU 2 1
00159 Copper, total per batch CU 3 1
01119 Copper, total recoverable CU 4 1
46394 Copper, dry weig ht CU 5 1

01306 Copper, potentially dissolved CU 6 1

01041 Copper, suspended (as Cu) CU 7 1

01040 Copper, dissolved (as Cu) CU 7 1

a – All additional parameters can be found in Table B

- 3 in Appendix B.



### Parameters Included in the Metals Category. All m etals in their elemental form are included in this category. Hexav a lent or trivalent metals and metals in ionic form (i.e. hexavalent chromium and aluminum, ion) are included, but specific metallic compounds (i.e. calcium chloride) are not.

Parameter Grou p ing/Category Priorities. A ll parameters for a specific metal are grouped together (e.g. all aluminum related parameters are assigned to the pollutant group labeled AL). EPA assigns each parameter a group priority number using the hierarchy shown in Table 3-14. As shown in Table 3- 14, metal parameters that express total discharges are given a higher priority (lower number) than parameters that express partial discharges. Category priorities for all metal parameters are assigned a ‘1’ because all groups in the metal category are included in the calculation of the total metal load.DRAFT Section 3 - Calculator Module Development



### 3-42 Table 3

- 14

. Group and Category Priorities for Parameters in an Example Metal Group

Parameter description Group Priority Category Priority Metal, total 1 1
Metal 2 1
Metal/batch 3 1
Total recoverable metal 4 1 Metal, dry weight 5 1 Metal, potentially dissolved 6 1 Metal, dissolved 7 1 Metal, suspended 7 1



### Metal Load Aggregation. The pollutant parameter in each metal group with the minimum group priority number is selected to represent each individual metal load. For instance, the Loading Tool will select aluminum, total if available instead of all other aluminum parameters to represent the aluminum load. If two parameters have the same minimum group priority number, the Loading Tool will sum them together. For example, copper, dissolved and copper, suspended are both assigned group priority 7. If a copper parameter with a lower priority number is not available, the Loading Tool will sum the dissolved and suspended copper to compute the total copper load. If only one of the two parameters is reported, then the Loading Tool uses the reported parameter’s load to represent the total copper load. Because all parameters for metals have a category priority of 1, the Loading Tool sums all of the individual metal loads to calculate the total metal load (i.e., one parameter from each metals group represents the load for the group, all metals groups are summed to represent the total metal load).

3.3.1.2 Priority Pollutants

Table 3 -15 below provides an example of the pollutant groups and assigned priorities for lead and benzene. The following discusses the scope of the priority pollutant category, general rules for priorities, and aggregation methodology:

Table 3

- 15. Lead and Benzene Groups Assign ed Priorities a

PRAM

Code Parameter Description Parameter Group Group Priority Pollutant Category Category Priority
01051 Lead, total (as Pb) PB 1 PP 1
01259 Lead PB 2 PP 1
01114 Lead, total recoverable PB 3 PP 1
78468 Lead, dry weight PB 4 PP 1
0105 2 Lead, total dry weight (as Pb) PB 5 PP 1
01318 Lead, potentially dissolved PB 6 PP 1
01049 Lead, dissolved (as Pb) PB 7 PP 1
51491 Lead, Organic, Total PB 8 PP 1
34030 Benzene BENZN 1 PP 1DRAFT Section 3 - Calculator Module Developm ent



### 3-43 Table 3

- 15. Lead and Benzene Groups Assign ed Priorities a

PRAM

Code Parameter Description Parameter Group Group Priority Pollutant Category Category Priority
78124 Benzene (volatile analysis) BENZN 2 PP 1
34235 Benzen e, dissolved BENZN 3 PP 1
34237 Benzene, dry weight BENZN 4 PP 1

a – All additional parameters can be found in Table B

- 4 in Appendix B.



### Parameters Included in the Priority Pollutant Category. EPA defines priority pollutants as those chemical pollutants that are regulated and that can be tested by established analytical methods. EPA included all 126 pollutants that EPA defines as priority pollutants (EPA, 2008) (EPA, 2009b) in this category. Wherever possible, EPA identified synonyms for the priority pollutants and incorporated them into the DMR_LOADINGS_PARM_CAT_XWALK table. EPA only included pollutants that exactly matched the priority pollutant. For example, EPA included phenol, but not phenols or phenolic compounds. The priority pollutant category incl udes metals. EPA used the same methodology to define the metals priorities and hierarchies as described in the previous section.

Parameter Grouping/Category Priorities. As shown in Table 3- 15, all parameters for a specific priority pollutant are grouped to gether. For instance, all parameters related to benzene are assigned to a group labeled BENZN. EPA assigns each parameter in the group a group priority using the hierarchy shown in Table 3-16. Some pollutants contain additional parameters not in the hierar chy and are handled on a case by case basis. For example, the benzene group has an additional parameter, benzene (volatile analysis), which is not in Table 3-15. Category priorities for all priority pollutants are assigned a ‘1’ because all groups in the p riority pollutant category are included in the calculation of the total priority pollutant load.

Table 3

- 16. Example Priority Pollutant Parameter Groups and Category Priorities

Parameter Description Group Priority Category Priority Priority Pollutant 1 1 Priority Pollutant, dissolved 2 1 Priority Pollutant, dry weight 3 1



### Priority Pollutant Load Aggregation. The pollutant parameter in each priority pollutant group with the minimum group priority number is selected to represent each individual priorit y pollutant load. For category aggregation, all priority pollutant loads are summed together to calculate the total priority pollutant load.

3.3.1.3 Suspended Solids

Table 3 -17 below provides the parameter group, group priority, pollutant category, and category pr iority for all 20 parameters in the suspended solids category. The following discusses the scope of the suspended solids category, general rules for priorities, and aggregation methodology:DRAFT Section 3 - Calculator Module Development



### 3-44 Table 3

- 17. Suspended Solids Parameter Pollutant Groups and Assign ed Priorities

PRAM

Code Parameter Description Parameter Group Group Priority Pollutant Category Category Priority
51545 Total Suspended Solids TSS 1 SS 1
00530 Solids, total suspended TSS 2 SS 1
51530 Suspended Solids, Total TSS 3 SS 1
70031 Solids, to tal suspended TSS 4 SS 1
85001 Suspended solids TSS 5 SS 1
79775 Suspended solids, total annual TSS 6 SS 1
00533 Solids, Total Suspended, Net Value TSS 7 SS 1
51194 Dewatering effluent TSS TSS 8 SS 1
61730 Inorganic suspended solids TSS 9 SS 1
49164 Sanitary waste, TSS TSS 10 SS 1
79774 Suspended solids, total discharge TSS 11 SS 1
49163 WWT drill fluids/cuttings, TSS TSS 12 SS 1
00540 Solids, fixed suspended TSS 13 SS 1
00535 Solids, volatile suspended TSS 13 SS 1
00547 Residue, total non

- settle able 00547 SS 2
00546 Residue, settleable 00546 SS 2
00545 Solids, settleable 00545 SS 3
00534 Solids, Settleable, Net 00534 SS 4
70024 Solids, large fraction, suspended 70024 SS 5
70025 Solids, small fraction, suspended 70025 SS 6



### Parameters Included in the Suspended Solids Category. All suspended or settable solids are included in this category, but no pollutant specific solids such as suspended copper are included.

Parameter Grouping/Category Priorities. All parameters for total suspended solids are grouped together under the pollutant group labeled TSS. EPA assigned a group priority to each of the parameters in the TSS group as shown in Table 3- 17. Category priorities for all parameters in the TSS group are assigned a ‘1’. Some solids are not included in the TSS group, so they are not assigned a group priority number. However, these solids are still included in the suspended solids category and are assigned a category priority as shown in Table 3- 17. The category priority hierarchy is establ ished by assigning a higher priority (lower number) to parameters representing the total release of a pollutant rather than the partial release

. Suspended Solids Load Aggregation. The parameter in the TSS group with the lowest priority number is used as the suspended solids category load. If there is no reported TSS pollutant, the parameter with the lowest category priority number is used as the suspended solids category load. If there are two parameters with the same category or group priority number, the Loading Tool, will sum the two values. For instance, residue, total non- settleable and residue settleable are assigned category priority 2. If a priority 1 category parameter is not available (one of the TSS group parameters), and both total non- settleable and settleable residue are reported, then the Loading Tool will sum the two parameters together to calculate the total suspendedDRAFT Section 3 - Calculator Module Development



### 3-45 solids category load. If only one of the two parameters is reported, then the Loading Tool will use that parameter’s load to represent the total suspended solids category load.

3.3.1.4 Organic Enrichment

Table 3 -18 below provides the pollutant group, group priority, and category priority for all 42 parameters in the organic enrichment category. The scope of the organic enrichment catego ry, general rules for priorities, and aggregation methodology are discussed following the table.

Table 3

- 18. Organic Enrichment Parameter Pollutant Groups and Assigned Priorities

PRAM

Code Parameter Description Parameter Group Group Priority Pollutant Cat egory Category Priority
00318 BOD, 5

- day BOD5 1 ORG 1
70026 BOD, 5

- day, total BOD5 2 ORG 1
85002 Biochemical oxygen demand

- 5 BOD5 3 ORG 1
00310 BOD, 5

- day, 20 deg. C BOD5 4 ORG 1
50083 Avg. of 7

- day sum of BOD5 values BOD5 5 ORG 1
03602 Biochemical o xygen demand BOD5 6 ORG 1
00311 BOD, 5

- day, dissolved BOD5 7 ORG 1
00359 BOD, filtered, 5 day, 20 deg. C BOD5 8 ORG 1
50077 5

- Day sum of BOD5 discharged BOD5 10 ORG 1
50078 7

- Day sum of BOD5 discharged BOD5 11 ORG 1
82236 BOD, 5 lb/cu ft process BOD5 12 ORG 1
81017 Chemical Oxygen Demand (COD) 81017 ORG 2
80108 Chemical oxygen demand (COD) 80108 ORG 3
80103 Chemical oxygen demand (COD) 80103 ORG 4
80116 Chemical oxygen demand, soluble 80116 ORG 5
00341 Oxygen demand, chem. (COD), dissolved 003 41 ORG 6
00340 Oxygen demand, chem. (high level) (COD) 00340 ORG 7
00335 Oxygen demand, chem. (low level) (COD) 00335 ORG 8
51544 BOD 51544 ORG 9
00319 BOD, (ult. all stages) 00319 ORG 10
00321 BOD, (ult. 2nd stage) 00321 ORG 11
00320 BOD, (ult . 1st stage) 00320 ORG 12
00327 BOD, 11

- day (20 deg. C) 00327 ORG 13
00324 BOD, 20

- day (20 deg. C) 00324 ORG 14
00352 BOD, 35

- day (20 deg. C) 00352 ORG 15
00314 BOD, nitrogen inhib 5

- day (20 deg. C) 00314 ORG 16
80082 BOD, carbonaceous, 05 day, 2 0 deg. C CBOD5 1 ORG 16
81286 BOD, carbonaceous, 5

- day, 20 deg. C CBOD5 2 ORG 16
80280 Carbonaceous BOD, 5 day, 20 deg. C filtrd CBOD5 3 ORG 16
70027 COD, 25N K2Cr207, tot 70027 ORG 17
50081 WLA BOD

- 5 day value 50081 ORG 18DRAFT Section 3 - Calculator Module Development



### 3-46 Table 3

- 18. Organic Enrichment Parameter Pollutant Groups and Assigned Priorities

PRAM

Code Parameter Description Parameter Group Group Priority Pollutant Cat egory Category Priority
49160 Sanitary waste, BOD , 5

- day 49160 ORG 19
51189 Dewatering effluent COD 51189 ORG 20
49165 WWT drill fluids/cuttings, COD 49165 ORG 21
00192 Oxygen demand, ult. carbonaceous (UCOD) 00192 ORG 22
80087 BOD, carbonaceous, 20

- day (20 deg. C) 80087 ORG 23
80273 BOD, carbo naceous, 25

- day (20 deg. C) 80273 ORG 24
80276 BOD, carbonaceous, 28

- day (20 deg. C) 80276 ORG 25
80126 BOD, carbonaceous, 5 day, 5 deg. C 80126 ORG 26
80278 Non

- nitrogenous BOD 80278 ORG 27
80089 BOD, carbonaceous, 40 day, 20 deg. C 80089 ORG 28
80279 CBOD5/NH3

- N 80279 ORG 29



### Parameters Included in the Organic Enrichment Category. All biochemical oxygen demand (BOD) and chemical oxygen demand (COD) pollutants are selected to represent organic enrichment.

Parameter Grouping/Category Priorities

. BOD 5 

### is the standard method for estimating the mass of oxidizable carbon and nitrogen. The standard testing conditions for BOD 5 

### are to take measurements over a period of 5 days at 20°C. All parameters that relate to BOD 5 

### are assigned to a group labeled B OD 5 

### . EPA assigned group priorities for these parameters as shown in Table 3- 18. All of these parameters are assigned a category priority of 1, since BOD 5 

### is the first choice for calculating organic enrichment

. If no parameters for BOD 5 

### are recorded, the Lo ading Tool uses other parameters . As shown in Table 3- 18, these parameters are assigned category priorities according to the following hierarchy:

1. C OD

. COD measures the total oxidizable content. Because the COD method relies on a chemical oxidant rather tha n bacteria, other compounds such as metals and organics that may be oxidized in addition to carbon and nitrogen. Typically, COD measurements are slightly higher than BOD 5 

### measurements.

2. B OD

. B OD represents methods, which use bacteria, but with nonstandard conditions. Because such variations make BOD parameters inconsistent, BOD parameters are given a higher category priority number than COD parameters.

3. CBO D 5 

### and NBOD 5 

### . Carbonaceous or nitrogenous BOD 5 

### (C or NBOD 5 

### ) estimate only the mass of oxidizable carbon or nitrogen respectively. Because both values are needed to calculate BOD 5 

### accurately, these parameters are given higher category priority numbers than BOD parameters.

4. CBOD

. C arbonaceous BOD parameters are given the highest category priority numbers bec ause NBOD is not an available parameter, and both parameters are necessary to accurately estimate organic enrichment.DRAFT Section 3 - Calculator Module Development



### 3-47 Organic Enrichment Load Aggregation. If available, the parameter in the BOD 5 

### group with the minimum group priority is used as the total o rganic enrichment load. Otherwise, the parameter with the minimum category priority is used as the total organic enrichment load. There is one exception; if no BOD 5 

### or COD parameters are available, the Loading Tool adds the parameter for NBOD 5 

### to the param eter with the minimum group priority in the CBOD 5 

### group to calculate the total organic enrichment load.

3.3.1.5 Nutrients

- Nitrogen

Because a method for calculating the nutrients -nitrogen load has already been developed in the Load Calculator Module of the Loading Tool, the EZ Search did not require a new method for calculating the total nitrogen load. Section 3.2.3 of this document describes the Load Calculator Module methodology for calculating the total aggregated nitrogen loads.

3.3.1.6 Nutrients

- Phosphorus

Because a method for calculating the nutrients-phosphorus load has already been developed in the Load Calculator Module of the Loading Tool, the EZ Search did not require a new method for calculating the total phosphorus load. Section 3.2.3 of this document describe s the Load Calculator Module methodology for calculating the total aggregated nitrogen loads.

3.3.1.7 Pathogen Indicators

Table 3 -19 below provides the pollutant group, group priority, and category priority for all parameters in the pathogen indicators category. T he following discusses the scope of the pathogen indicators category, general rules for priorities, and aggregation methodology:

Table 3

- 19. Pathogen Indicator Parameter Pollutant Groups and Assigned Priorities

PRAM C ode Parameter Description Pollutant Gr oup Group Priority Category Priority
81401 Enteric viruses EV 1 1
74054 Streptococci, fecal general FS 1 1
31671 Streptococci, fecal plate count KF agar FS 2 1
31673 Streptococci, fecal MF, KF agar, 35C, 48hr FS 3 1
31674 Streptococci, fecal 10/mL FS 4 1
31675 Streptococci, fecal MPN, KF broth 35 C FS 5 1
31679 Fecal streptococci, MF m

- enterococcus ag FS 6 1
61211 Enterococci FS 7 1
31639 Enterococci: group D, MF trans, M

- E, EIA FS 8 1
74056 Coliform, total general TC 1 1
74058 Coliform, total co lony forming units TC 2 1
31505 Coliform, tot, MPN, completed, (100 mL) TC 3 1
74057 Coliform, fecal, colony forming units TC 4 1
31504 Coliform, total MF, immed, les endo agar TC 4 1
31501 Coliform, total MF, immed, m

- endo med 35 C TC 5 1
74055 Colif orm, fecal general TC 6 1DRAFT Section 3 - Calculator Module Development



### 3-48 Table 3

- 19. Pathogen Indicator Parameter Pollutant Groups and Assigned Priorities

PRAM C ode Parameter Description Pollutant Gr oup Group Priority Category Priority
31625 Coliform, fecal MF, MFC, 0.7um TC 7 1
31616 Coliform, fecal MF, MFC broth, 44.5 C TC 8 1
31615 Fecal coliform, MPN, EC med, 44.5 C TC 9 1
31613 Coliform, fecal MF, MFC agar, 44.5 C, 24hr TC 9 1
48201 Coliform, fecal MPN + membrane ftl 44.5 C TC 10 1
31612 Coliform, fecal 10/mL TC 11 1
49187 Sanitary waste, fecal coliform TC 12 1
51041 E. coli, colony forming units (CFU) TC 15 1
51040 E. coli TC 16 1
31648 E. coli, MTEC

- MF TC 17 1
31633 E. coli, thermotol, MF, MTEC TC 18 1



### Parameters Included in the Pathogen Indicators Category. All pathogen parameters are included EXCEPT the following:

• 

### Those pathogen parameters that are entered in units which could not be expressed in units of counts per 100 milliliters (e.g. perc ent of fecal coliform samples).

• 

### Sludge related parameters.

Parameter Grouping/Category Priorities. Parameters in the pathogen indicator category are organized into three groups: enteric viruses (EV), fecal streptococci (FS), and total coliform (TC). EPA a ssigns group priorities to each of these parameters as shown in Table 3-19. Since E.coli is a subset of fecal coliform, which is a subset of total coliform, total coliform group priority numbers are assigned using the following hierarchy:

1. Total coliform; 2. F ecal coliform; and 3. E.coli.

Since enterococci is a subset of streptococci, streptococci group priority numbers are assigned using the following hierarchy:

1. Fecal Streptococci; and 2. Enterococci

.
All parameters in the pathogen indicator category are assigned a category priority of 1, because all groups are included in the total pathogen load.

Pathogen Indicators Load Aggregation. The load for each group is calculated by selecting the parameter in each group that has the minimum group priority number. The Loadi ng Tool calculates the total pathogen indicators load by summing the total coliform load, fecal streptococci load, and the enteric viruses load.DRAFT Section 3 - Calculator Module Development



### 3-49 3.3.1.8 Temperature

The Convert Module of the Loading Tool aggregates temperature measurements in DMR data. This sectio n describes the Convert Module methodology for aggregating temperature measurements that are stored in the DMR_LOADINGS_EZ_SRCH_CALCS table.

The Convert Module creates a new column in the CONVERT_DMR table for wastewater stream temperature

. ICIS and PCS co ntain two parameter codes for temperature:

• 

### Temperature Degrees C = PRAM 00010; and

• 

### Temperature Degrees F = PRAM 00011.

The Convert Module selects one temperature measurement (prioritizing degrees F over degrees C) for each outfall, monitoring location, an d date, and converts all temperatures to degrees Fahrenheit

. The EZ Search Load Module aggregates the temperature measurements by facility by calculating the average and the maximum temperature measurements per facility per
year.

3.3.1.9 Wastewater Flow

Because a method for calculating total annual wastewater flow has already been developed in the Convert and Load Calculator Modules, the EZ Search did not require a new method for calculating annual wastewater flow . Section 3.1.1 describes the Convert Module’s logic for selecting wastewater flow parameters, and Section 3.2 describes how the Load Calculator Module calculates an annual average wastewater flow (MGD) and the total annual wastewater flow (millions of gallons per year).

3.3.2 References

U.S. EPA. 2009a. Technical Support Document for the Annual Review of Existing Effluent Guidelines and Identification of Potential New Point Source Categories. EPA-821-R-09- 007. Washington, DC. (TBD). EPA- HQ

- OW -2008-0517 DCN 06557.

U.S. EPA. 2008. Priority Pollutants. http://www.epa.gov/waterscience/methods/pollutants.htm

. October. Downloaded on 20 Aug 2009.

U.S. EPA. 2009b Title 40: Protection of Environment. http://ecfr.gpoaccess.gov/cgi/t/text/text

idx?c=ecfr&sid=1da3c92cb271898c38620a9523264027&rgn=div5&view=text&node=4 0:28.0.1.1.23&idno=40#40:28.0.1.1.23.0.5.9.9 . August. Downloaded on 20 Aug 2009.

U.S. EPA. 2009c Effluent Limitation Guidelines: Frequent Questions. http://www.epa.gov/guide/questions/ . July. Downloaded on 20 Aug 2009.

3.4 TRI Search Database Development

For Phase 3 of the Loading Tool development, EPA developed the TRI Search interface to allow uses to perform analyses on TRI releases. These analyses include calculating TWPE; and ranking discharges by industry, watershed, and pollutant. Including these features in the Loading Tool required importing additional data into the database and additional backendDRAFT Section 3 - Calculator Module Development



### 3-50 database development. Therefore, EPA developed a separate loadings module and database tables in the Loading Tool to p rovide the input to the TRISearch queries. Figure 3 -9 presents the TRI Search back

- end database.

DMR_LOADINGS_TRI_FACILITIES TRI Facility ID
UIN FACILITY_NAME CITY_NAME COUNTY_NAME STATE_COUNTY_FIPS_CODE STATE_ABBR ZIP_CODE
REGION FAC_LATITUDE FAC_LONGITUDE SIC_CODE NAICS_CODE DMR_LOADINGS_TRI_RELEASES REPORTING_YEAR TRI_FACILITY_ID
UIN SIC_CODE NAICS_CODE CHEMICAL_NAME TRI_CHEM_ID UNIT_OF_MEASURE DIRECT_RELEASE INDIRECT_RELEASE
TWF DIRECT_TWPE INDIRECT_TWPE REF_POTW_REMOVALS TRI_CHEM_ID CHEM_NAME POTW_REMOVAL

REF_TRI_CHEM_CAS_XWALK CHEM_NAME TRI_CHEM_ID
CASNU

REF_TRI_CHEM_TWFS CHEM_NAME CAS_NU
TWF REF_TRI_METALS CHEM_NAME TRI_CHEM_ID NEW_CHEM_NAME NEW_CHEM_ID DMR_LOADINGS_PSC_TRI_XWALK TRI_FACILITY_ID TRI_CHEM_ID PSC_CODE



### Figure 3 -9. TRI Search Back -End Database

The DMR_LOADINGS_ TRI_RELEASES table contains annual pollutant releases for each unique TRI Facility ID and TRI chem ical name . The TRI Search Load Module creates the DMR_LOADINGS_ TRI_RELEASES table using the TRI data from the DMR_LOADINGS_ WATER_RELEASES table and the following calculation methodologies:

• 

### POTW Removals. For facilities that reported transfers of chemicals to POTWs (Indirect Releases) , The TRI Search Load Module first adjusted the reported pollutant loads to account for pollutant removal that occurs at the POTW prior to discharge to the receiving stream. EPA estimated the pounds of facilities’ waste released to the surface water after POTW removal using the following equation 9 

### :

I

ndirect Release (lbs/yr) = [Transfer to POTW (lbs/yr)] x [1 - POTW Removal (%)]

T able C-1 in Appendix C lists the POTW Removals and their data sources, in alphabetical order

• 

### TWFs. To identify potential impacts on human health and the environment, EPA
est imates toxic equivalent mass discharge through the use of TWFs. The TRI Search Load Module uses the REF_TRI_CHEM_TWFS table, w hich lists TWFs by CAS number, to calculate TWPE for chemical discharges. If the table does not

9 For example, the PO TW removal efficiency for lithium carbonate is 1.85 percent . That is if 10,000 lbs of lithium carbonate discharged to a POTW, only 9,815 lbs of lithium carbonate will be discharged from the POTW to surface waters as this amount is untreated by th e POTW [9, 815 lbs = 10,000 lbs × (1 - 0.0185)]

.DRAFT Section 3 - Calculator Module Development



### 3-51 list a TWF for a specific parameter, the TRI Search Load Module does not include pollutant discharges for this chemical in its TWPE estimates.

I n some cases, the TRI Search Load Module calculates industry- specific TWFs for certain chemical compound categories reported in TRI. These TWFs are not used to calculate TWPE for chemical discharges in PCS/ICIS

- NPDES. EPA created specific TRI TWFs when it had additional information about the composition of the compound category, as released from specific industries. These chemicals include:

 Dioxin and dioxin-like compounds;

 Polyclcylic aromatic compounds; and

 Creosote.

E PA’s 2009 Technical Support Document for the Annual Review of Existing Effluent Guidelines and Identifica tion of Potential New Point Source Categories (EPA 821-R-09-007 ) describes the development of these TWFs in more detail. Table C -2 in Appendix C presents the chemical- specific TWFs used by the TRI Search Load Module to calculate TWPE.

• Metals. For TRI reporting, facilities report metal compounds on a single reporting form for each parent metal and do not specify the individual compound(s) released. In addition, if the facility is required to report for a metal (e.g., zinc) and its compounds (e.g., zinc compounds), the facility may report both the metal and metal compound on a single form (reported as the metal compound). For metal compound reporting, the release quantities are based on the mass of the parent metal, only. To calculate loads and TWPEs for meta l compounds, the TRI Search Load Module combines the loads for the metal and metal compounds and uses the TWF for the parent metal

.

• Point Source Categories. The DMR_LOADINGS_PSC_TRI_XWALK table links unique TRI Facility IDs and TRI Chemical IDs to point s ource categories. The point source category field assigns records to existing point source categories. The TRI Search uses these category assignments for all TRI Search queries

. References

U.S. EPA. 2009a. Technical Support Document for the Annual Review of Existing Effluent Guidelines and Identification of Potential New Point Source Categories. EPA-821-R-09- 007. Washington, DC. (TBD). EPA- HQ

- OW -2008-0517 DCN 06557.DRAFT Section 4 - Interface Development



### 4-1 4. I NTERFACE 

### D EVELOPMENT



### The Loading Tool has four main search interfaces to provide users access to DMR and TRI pollutant loadings:

• 

### EZ Search Interface. The EZ search provides users with a simple interface to query pollutant loads by location, watershed, industry, and pollutant/pollutant category. Based on the user

- entered search criteria, the EZ Search filters the loads in DMR_LOADINGS_EZ_SRCH_CALCS and displays the results in an HTML view.

• 

### TRI Search Interface. The TRI Search provides users with a simple interface to
query TRI releases by location, watershed, industry, and pollutant. Based on t he

user

- entered search criteria, the EZ Search filters the loads in DMR_LOADINGS_ TRI_RELEASES and displays the results in an HTML view.

• 

### Facility Search Interface. The Facility Search provides direct access to facility

level information, one facility at a time . Based on the user’s specifications, the Facility Search filters the DMR_LOADINGS_FACILITIES table and the DMR_LOADINGS_EZ_SRCH_CALCS table and displays the results in an HTML view

.

• 

### Advanced Search Interface. The Advanced Search allows users to cond uct a customized query and alter the loading calculation methodology. The Advanced Search provides the results to the user as a down- loadable comma

- separated value (csv) file.

The following subsections describe each main interface in detail. Additional se arch options are provided in the Data Explorer and Everyday Searches interfaces of the Loading Tool. More information on these searches can be found in the user guides for the Data Explorer and Everyday Searches, found on the User Guide/Technical Documents tab of the Loading Tool.

4.1 EZ Search (DMR)

Figure 4 -1 presents the search page for the EZ Search, which allows users to query DMR pollutant loadings by specifying location or watershed, industry, and pollutant.DRAFT Section 4 - Interface Development



### 4-2 Figure 4 -1. EZ Search Start Page

The input to the EZ Search is the DMR_LOADINGS_EZ_SRCH_CALCS table. This table contains annual pollutant loadings that are aggregated by NPDES permit ID and parameter code. Section 3.3 of this document describes the development of the database tables that the Loading Tool uses to produce results for the EZ Search.

The EZ Search provides the following search options:

• 

### Location. The EZ Search defaults to nationwide loadings. Users can narrow the search by specifying EPA Region, state, county, city, or zip code. The Loading Tool identifies the scope of facilities that meet the user’s specifications based on location information in the DMR_LOADINGS_FACILITIES table.

• 

### Watershed. As an alternative to searching by location, users can narrow their search by specifying watershed. The EZ Search defines watersheds using the 12

- digit HUC Code (HUC -12) 10 

### . Users have three options for specifying receiving watersheds to include in their query:

10 USGS defines watersheds in the US and Caribbean using unique HUC codes consisting of two to twelve digits based on six levels of classification: Region (first

- level, 2

- digit HUC), Subregion (second

- level, 4

- digit HUC ),DRAFT Section 4 - Interface Development



### 4-3 — E nter HUC -12 code. If known, the user can directly enter the HUC-12 code corresponding to th e watershed of interest for their search. The Loading Tool uses a mapping service (OWRAD/PCS_WMERC) developed by Office of Water, to link facilities to HUC -12’s. The mapping service contains GIS data for facilities in the Watershed Assessment Tracking and Environmental ResultS (WATERS) database and overlays the facility data with HUC -12 polygons from the Natural Resources Conservation Service’s Watershed Boundary Dataset 11



### . The service
returns t he facility that has the greatest overlap with the spatial data for the specified HUC -12. The Loading Tool then uses the list of matching facilities to pull loadings from the DMR_LOADINGS_EZ_SRCH_CALCS table.

— Enter 5 -digit Zip Code. If the user does not know the exact HUC-12 code, they can alternatively enter the 5 -digit Zip Code for the region of interest. The Loading Tool uses a WATERS look-up service (ZCTA5_to_HUC12) which returns one or more HUC-12’s for a specified zip code. The service determines a match between HUC -12 and zip code by overlaying spatial data for HUC -12’s from the Natural Resources Conservation Service’s Watershed Boundary Dataset with spatial data for zip codes from the U.S. Census Bureau’s Zip Code Tabulation Areas (ZCTA). If there is any overlap between the HUC -12 and zip code, then the service matches thezip code and HUC-12. Once the Loading Tool has the matching HUC12’s, it uses the OWRAD/PCS_WMERC service, described above, to obtain a list of matching facilities. The Loading Tool then uses the list of matching facilities to pull loadings from the DMR_LOADINGS_EZ_SRCH_CALCS table.

— Select a Major US Watershed

. The EZ search provides users with a drop down menu of major US Watersheds that the user can select for their query. EPA’s menu of major U.S. watersheds includes the Albemarle and Pamlico Sounds, Columbia River Basin, Delaware Bay, Great Lakes, Long Island Sound, Lower Columbia River Basin, Mississippi

Atchafalaya River Basin, Mobile Bay, Ohio River Basin, Puget Sound, San Francisco Bay, and Tampa Bay. Many of these watersheds comprise several HUC -12 codes. Appendix D lists the HUC-12 codes included in each watershed (Table D

- 1) and presents a map of the watershed boundaries (Figure D-1). The Loading Tool passes the list of HUC-12 codes corresponding to the major U.S. watershed through the OWRAD/PCS_WMERC service, described above, to obtain a list of matching facilities. The Loading Tool then uses the list of matching facilities to pull loadings from the DMR_LOADINGS_EZ_SRCH_CALCS table.

Accounting unit (third

- level, 6

- digit HUC), Cataloguing unit (fourth

- level, 8

- digit HUC), Watershed (fifth

- level, 10

- digit HUC), and Subwatershed (sixth

- level, 12

- digit HUC).

11 For more information about the Watershed Boundary Dataset, go to http://www.n cgc.nrcs.usda.gov/products/datasets/watershed/index.htmlDRAFT Section 4 - Interface Development



### 4-4 • 

### Pollutant. As a default, the EZ Search includes all p ollutants in the query. The user can narrow the search by either specifying a single pollutant of interest or by selecting a pollutant category.

— Specify single pollutant

. To specify a pollutant of interest the user may either enter the pollutant name or the CAS Number. When entering the pollutant name, the user can enter a partial name. Then the Loading Tool searches the DMR_LOADINGS_REF_PARAMETER table returns a list of pollutant names containing the text string entered by the user. The user then selects the pollutant of interest from the list

. The DMR_LOADINGS_REF_PARAMETER table links the specified pollutant to parameter codes . The Loading Tool pulls the data for the matching parameter codes from the DMR_LOADINGS_EZ_SRCH_CALCS table.

— Specify pollutant c ategory. The EZ Search also provides the option to aggregate loads for pollutant categories. The categories that EPA selected for the EZ Search include metals, priority pollutants, suspended solids, organic enrichment, conventional pollutants, nitrogen, phosphorus, pathogen indicators, and temperature. EPA selected these categories because they include pollutants of interest for most water programs, and, in many cases, correspond to common causes of impairment in receiving water bodies. The DMR_LOADINGS_PAR M_CAT_XWALK table links pollutant categories to parameter codes and assigns priorities that the Loading Tool uses to aggregate the category load. The Loading Tool pulls loadings data from the DMR_LOADINGS_EZ_SRCH_CALCS table for the parameters that link to the pollutant category. Section 3.3.2 of this report describes how the Loading Tool aggregates loads for the pollutant parameters that make up each pollutant category.

• 

### Industry. As a default, the EZ Search includes all industries. The user can narrow the search to specify an industry of interest:

— Select POTWs Only

. The EZ Search provides the option to only query pollutant loadings for POTWs. The Loading Tool identifies NPDES IDs for POTWs using the FACILITY_TYPE field in DMR_LOADINGS_FACILITIES. Faciliti es that are classified as POTWs in PCS and ICIS- NPDES are labeled as “POTW” in the FACILITY_TYPE field.

— Select Industrial Point Sources Only

. The EZ Search provides the option to only query pollutant loadings for Industrial Point Sources. This search excl udes loadings for facilities that are labeled as POTWs in the DMR_LOADINGS_FACILITIES table

. Users can further narrow the Industrial Point Source search using one of the following three options:DRAFT Section 4 - Interface Development



### 4-5 ▪ S elect a Point Source Category

. The EZ Search provides a men u of Point Source Categories for EPA’s Effluent Limitations Guidelines and Pretreatment Standards. The Loading Tool matches NPDES IDs in the DMR_LOADINGS_EZ_SRCH_CALCS table to point source categories using the DMR_LOADINGS_STD_PSC_XWALK table. Section 3.3.1 of this report describes the DMR_LOADINGS_STD_PSC_XWALK table development.

▪ S elect SIC grou p. The EZ Search provides a menu of 2- digit SIC codes that the user can select to specify their industry of interest. The Loading Tool filters the DMR_LOADINGS_E Z_SRCH_CALCS records to include NPDES IDs that link to SIC codes that are included in the 2- digit SIC group. The Loading Tool links NPDES IDs to primary SIC codes through the DMR_LOADINGS_FACILITIES table.

▪ E nter a 4

- Digit SIC Code

. Users can directly ente r the 4

- digit SIC code for their industry of interest. The Loading Tool filters the DMR_LOADINGS_EZ_SRCH_CALCS records to include NPDES IDs that link to SIC codes that match the user’s 4

- digit SIC code. The Loading Tool links NPDES IDs to primary SIC codes through the DMR_LOADINGS_FACILITIES table.

▪ S elect NAICS group. The EZ Search provides a menu of 2- digit NAICS codes that the user can select to specify their industry of interest. The Loading Tool filters the DMR_LOADINGS_EZ_SRCH_CALCS records to includ e NPDES IDs that link to SIC codes that are included in the 2- digit NAICS group. The Loading Tool links NPDES IDs to primary NAICS codes through the DMR_LOADINGS_FACILITIES table.

Table 4

- 1 lists the results tables that the EZ Search displays based on th e user’s search specifications

. The checks in the table indicate which tables the Loading Tool will display if the user specifies a location/watershed, a pollutant, or an industry. For example, if the user specifies a pollutant in their search, the Loading Tool only displays four of the twelve tables listed in Table 4 -1. If the user enters search criteria for all three search options (Location/Watershed, Industry, and Pollutant) then the Loading Tool only displays the Search Statistics Table and the Top Fac ility Discharges Table.

Table 4

- 1. EZ Search Results Tables

Table Location/Watershed Pollutant Industry Search Statistics (Figure 4

- 2) X X X Top Pollutants by Pound (Figure 4

- 3) X X Top Pollutants by TWPE (Figure 4

- 3) X X Top SIC Discharges in Pounds (Figure 4

- 4) X Top SIC Discharges in TWPE (Figure 4

- 4) XDRAFT Section 4 - Interface Development



### 4-6 Top SIC Discharges (Figure 4

- 5) X X Top Discharges to Watersheds in Pounds (Figure 4

- 6) X Top Discharges to Watersheds in TWPE (Figure 4

- 6) X Top Receiving Watersheds (Figure 4

- 7) X X Top Facility Discharges in Pounds (Figure 4

- 8) X X Top Facility Discharges in TWPE (Figure 4

- 8) X X Top Facility Discharges (Figure 4

- 9) X X X



### 4.1.1 Search Statistics Table

This table, shown in Figure 4-2, provides information about the total number of NPDES permits that are included in the search results ( total facilities, major facilities, minor facilities, and facilities that do not have permit or DMR data in PCS or ICIS- NPDES ), the number of facilities that have permit data (discharge limits or monit oring requirements), and the number of facilities with DMR data

.

F igure 4

- 2. Example EZ Search Results – Search Statistics Table

4.1.2 Top Pollutants Tables

The EZ Search provides two tables, shown in Figure 4-3, that display pollutant rankings:

• 

### Top pollutants in pounds. This table displays the ten pollutants with the largest discharges meeting the user’s search criteria . The pollutants are ranked in order of decreasing pollutant mass

.

• 

### Top pollutants in TWPE. This table displays the ten pollutants with the highest TWPE meeting the user’s search criteria . The pollutants are ranked in order of decreasing TWPE

.DRAFT Section 4 - Interface Development



### 4-7 F igure 4

- 3. Example EZ Search Results – Top Pollutants Tables

4.1.3 Top SIC Codes Tables

The EZ Search provides three tables that rank SIC code discharges:

• 

### Top SIC code discharges in pounds. This table, shown in Figure 4-4, displays the SIC codes with the ten largest discharges meeting the user’s search criteria

. The SIC Code discharges are ranked in order of decreasing pollutant ma ss . Note the Loading Tool does not display this table if the user specifies a Pollutant or an Industry.

• 

### Top SIC code discharges in TWPE. This table, shown in Figure 4-4, displays the SIC Codes with the ten highest toxic

- weighted discharges meeting the use r’s search criteria

. The pollutants are ranked in order of decreasing TWPE

. Note the Loading Tool does not display this table if the user specifies a Pollutant or an Industry.

• 

### Top SIC discharges. The Loading Tool displays this table if the user specifies a pollutant or pollutant category. This table, shown in Figure 4- 5, lists the ten SIC codes with the largest discharges of the pollutant specified by the user. The table ranks the SIC codes in the following order based on the type of pollutant that was
spe cified:

— P ollutants with Toxic Weighting Factors

. T he table ranks SIC codes by decreasing toxic -weighted pound equivalents.

— P ollutants with no Toxic Weighting Factors (e .g., Biochemical Oxygen Demand). The table ranks SIC codes by decreasing pollutant ma ss

.

— P athogen Indicators. The table ranks SIC codes by decreasing maximum concentration (count per 100 mL).DRAFT Section 4 - Interface Development



### 4-8 — T emperature. The table ranks SIC code by decreasing maximum temperature ( ° F).

— W astewater Flow. The table ranks SIC codes by decreasing annual

was tewater volume (millions of gallons per year).

F igure 4

- 4. Example EZ Search Results – Top SIC Code Discharges Tables (Pollutant not Specified by User)DRAFT Section 4 - Interface Development



### 4-9 Figure 4

- 5. Example EZ Search Results – Top SIC Code Discharges Table (Pollutant is Specified by Us er)

4.1.4 Top Watershed Tables

The EZ Search provides three tables that rank receiving watersheds:

• 

### Top discharges to watersheds in pounds. This table, shown in Figure 4-6, displays the watersheds receiving the ten largest discharges meeting the user’s search cri teria

. The watershed discharges are ranked in order of decreasing pollutant mass . Note the Loading Tool does not display this table if the user specifies a Pollutant or a Location/Watershed.

• 

### Top discharges to watersheds in TWPE. This table, shown in Figur e 4 -6, displays the watersheds receiving the ten highest toxic- weighted discharges meeting the user’s search criteria . The pollutants are ranked in order of decreasing TWPE . Note the Loading Tool does not display this table if the user specifies a Pollutan t or a Location/Watershed.

• 

### Top receiving watersheds

. The Loading Tool displays this table if the user specifies a pollutant or pollutant category. This table, shown in Figure 4- 7, lists the ten watersheds receiving the largest discharges of the pollutant specified by the user. The table ranks the watersheds in the following order based on the type of pollutant that was specified:

— Pollutants with Toxic Weighting Factors. The table ranks watersheds by decreasing toxic -weighted pound equivalents.

— Pollutant s with no Toxic Weighting Factors (e.g., Biochemical Oxygen Demand). The table ranks watersheds by decreasing pollutant mass

.

— Pathogen Indicators. The table ranks watersheds by decreasing maximum concentration (count per 100 mL).DRAFT Section 4 - Interface Development



### 4-10 — Temperature

. The table ranks watersheds by decreasing maximum temperature ( ° F).

— Wastewater Flow. The table ranks watersheds by decreasing annual wastewater volume (millions of gallons per year).

4.1.5 Top Facilities Tables

The EZ Search provides three tables that rank Facilities:

• 

### To p Facility discharges in pounds. This table, shown in Figure 4-8, displays the facilities with the ten largest discharges meeting the user’s search criteria. The facilities discharges are ranked in order of decreasing pollutant mass. Note the Loading Tool does not display this table if the user specifies a Pollutant.

• 

### Top Facility discharges in TWPE. This table, shown in Figure 4-8, displays the facilities with the ten highest toxic

- weighted discharges meeting the user’s search criteria. The pollutants are ranked in order of decreasing TWPE. Note the Loading Tool does not display this table if the user specifies a Pollutant.

• 

### Top Facility discharges. The Loading Tool displays this table if the user specifies a pollutant or pollutant category. This table, shown in Figure 4- 9, lists the ten facilities with the largest discharges of the pollutant specified by the user. The table ranks the facilities in the following order based on the type of pollutant that was specified:

— Pollutants with Toxic Weighting Factor s. The table ranks facilities by decreasing toxic -weighted pound equivalents.

— Pollutants with no Toxic Weighting Factors (e.g., Biochemical Oxygen Demand). The table ranks facilities by decreasing pollutant mass.

— Pathogen Indicators. The table ranks fac ilities by decreasing maximum concentration (count per 100 mL).

— Temperature. The table ranks facilities by decreasing maximum temperature (°F).

— Wastewater Flow. The table ranks facilities by decreasing annual wastewater volume (millions of gallons per ye ar).DRAFT Section 4 - Interface Development



### 4-11 Figure 4

- 6. Example EZ Search Results – Top Watershed Discharges Tables (Pollutant not Specified by User)DRAFT Section 4 - Interface Development



### 4-12 F igure 4

- 7. Example EZ Search Results – Top Receiving Watersheds Table (Pollutant is Specified by User)

F igure 4

- 8. Example EZ Search Resu lts – Top Facility Discharges Tables (Pollutant not Specified by User)DRAFT Section 4 - Interface Development



### 4-13 F igure 4

- 9. Example EZ Search Results – Top Facility Discharges Table (Pollutant is Specified by User)

4.2 TRI Search

Figure 4 -10 presents the search page for the TRI Search, which allows users to query TRI pollutant loadings by specifying location or watershed, industry, and pollutant.DRAFT Section 4 - Interface Development



### 4-14 F igure 4 -10. TRI Search Page

The input to the EZ Search is the DMR_LOADINGS_ TRI_RELEASES table. This table contains annual pollutant loadings that are u nique by TRI Facility ID and chemical . Section 3.4 of this document describes the development of the database tables that the Loading Tool uses to produce results for the TRI Search.

The TRI Search provides the following search options:

• 

### Location. The TRI Search defaults to nationwide loadings. Users can narrow the search by specifying EPA Region, state, county, city, or zip code. The Loading Tool identifies the scope of facilities that meet the user’s specifications based on location information in the DMR _LOADINGS_ TRI_ FACILITIES table.

• 

### Watershed. As an alternative to searching by location, users can narrow their search by specifying watershed. The TRI Search defines watersheds using the 12

- digit HUC Code (HUC -12) 12 

### . Users have three options for specifying receiving watersheds to include in their query:

12 USGS defines watersheds in the US and Caribbean using unique HUC codes consisting of two to twelve digits based on six levels of classification: Region (first

- level, 2

- digit HUC), Subregion (second

- level, 4

- digit HUC), Accounting unit (third

- level, 6

- digit HUC), Cataloguing unit (fourth

- level, 8

- digit HUC), Watershed (fifth

- level, 10

- digit HUC), and Subwatershed (sixth

- level, 12

- digit HUC).DRAFT Section 4 - Interface Development



### 4-15 — E nter HUC -12 code. If known, the user can directly enter the HUC-12 code corresponding to the watershed of interest for their search. The Loading Tool uses a look-up service (ZCTA5_to_HUC12) developed by Offi ce of Water, to link TRI facilities to HUC

- 12’s using their zip code information

. The service determines a match between HUC -12 and zip code by overlaying spatial data for HUC -12’s from the Natural Resources Conservation Service’s Watershed Boundary Datase t with spatial data for zip codes from the U.S. Census Bureau’s Zip Code Tabulation Areas (ZCTA). If there is any overlap between the HUC -12 and zip code, then the service matches the zip code and HUC -12. The Loading Tool then uses the list of facilities with zip codes linking to the specified HUC-12 to pull loadings from the DMR_LOADINGS_ TRI_RELEASES table.

— E nter 5 -digit Zip Code. If the user does not know the exact HUC-12 code, they can alternatively enter the 5 -digit Zip Code for the region of interest. The Loading Tool the same WATERS look

- up service (ZCTA5_to_HUC12) which returns one or more HUC- 12’s for a specified zip code. The Loading Tool then uses the list of matching facilities to pull loadings from the DMR_LOADINGS_ TRI_RELEASES table.

— S elect a Major US Watershed. The TRI S earch provides users with a drop down menu of major US Watersheds that the user can select for their query. EPA’s menu of major U.S. watersheds includes the Albemarle and Pamlico Sounds, Columbia River Basin, Delaware Bay, Grea t Lakes, Long Island Sound, Lower Columbia River Basin, Mississippi

Atchafalaya River Basin, Mobile Bay, Ohio River Basin, Puget Sound, San Francisco Bay, and Tampa Bay. Many of these watersheds comprise several HUC -12 codes. Appendix D lists the HUC-12 codes included in each watershed (Table D

- 1) and presents a map of the watershed boundaries (Figure D- 1). The Loading Tool compares the list of HUC -12 codes corresponding to the major U.S. watershed to the TRI Facility HUC -12 codes (identified using the ZCTA5_to_HUC12 service) to obtain a list of matching facilities. The Loading Tool then uses the list of matching facilities to pull loadings from the DMR_LOADINGS_ TRI_RELEASES table.

• 

### Pollutant. As a default, the TRI Search includes all pollutants in the query . The user can narrow the search by specifying a single pollutant of interest or TRI chemical category of interest

. The user can either enter the pollutant name directly or look up the pollutant name by entering either a partial pollutant name. The Loading Tool pulls the data for the matching TRI chemical codes from the DMR_LOADINGS_ TRI_RELEASES table.

• 

### Industry. As a default, the TRI Search includes all industries. The user can narrow the search to specify an industry of interest:DRAFT Section 4 - Interface Development



### 4-16  S elect a Point Source Category

. The TRI Search provides a menu of Point Source Categories for EPA’s Effluent Limitations Guidelines and Pretreatment Standards. The Loading Tool matches TRI Facility IDs in the DMR_LOADINGS_ TRI_RELEASES table to point source categories using t he DMR_LOADING _PSC_ TRI_ XWALK table. Section 3.4 of this report describes the DMR_LOADINGS_ PCS_TRI _XWALK table development.

 S elect SIC group. The TRI Search provides a menu of 2-digit SIC codes that the user can select to specify their industry of interest. The Loading Tool filters the DMR_LOADINGS_ TRI_RELEASES records to include TRI Facility IDs that link to SIC codes that are included in the 2- digit SIC group. The Loading Tool links TRI Facility IDs to primary SIC codes through the DMR_LOADINGS_ TRI_FACILITI ES table.

 E nter a 4

- Digit SIC Code. Users can directly enter the 4

- digit SIC code for their industry of interest. The Loading Tool filters the DMR_LOADINGS_ TRI_RELEASES records to include TRI Facility IDs that link to SIC codes that match the user’s 4 -digit SIC code. The Loading Tool links TRI Facility IDs to primary SIC codes through the DMR_LOADINGS_ TRI_ FACILITIES table.

 S elect NAICS group. The TRI Search provides a menu of 2- digit NAICS codes that the user can select to specify their industry of inter est. The Loading Tool filters the DMR_LOADINGS_ TRI_RELEASES records to include TRI Facility IDs that link to NAICS codes that are included in the 2- digit NAICS group. The Loading Tool links TRI Facility IDs to primary NAICS codes through the DMR_LOADINGS_ T RI_ FACILITIES table.

 E nter a 6- Digit NAICS Code. Users can directly enter the 6

- digit NAICS code for their industry of interest. The Loading Tool filters the DMR_LOADINGS_ TRI_RELEASES records to include TRI Facility IDs that link to NAICS codes that match the user’s 6- digit NAICS code. The Loading Tool links TRI Facility IDs to primary NAICS codes through the DMR_LOADINGS_ TRI_ FACILITIES table.

Table 4 -2 lists the results tables that the TRI Search displays based on the user’s search specifications. The ch ecks in the table indicate which tables the Loading Tool will display if the user specifies a location/watershed, a pollutant, or an industry. For example, if the user specifies a pollutant in their search, the Loading Tool only displays four of the t welve tables listed in Table 4 -2 . If the user enters search criteria for all three search options (Location/Watershed, Industry, and Pollutant) then the Loading Tool only displays the Search Statistics Table and the Top Facility Discharges Table.DRAFT Section 4 - Interface Development



### 4-17 Table 4-2. TRI Search Results Tables

Table Location/Watershed Pollutant Industry Top Chemicals by Pound (Figure 4

- 11 ) X X Top Chemicals by TWPE (Figure 4

- 11 ) X X Top NAICS Discharges in Pounds (Figure 4

- 12 ) X Top NAICS Discharges in TWPE (Figure 4

- 12 ) X Top N AICS Discharges (Figur e 4

- 13 ) X X Top Discharges to Watersheds in Pounds (Figure 4

- 14 ) X

Top Discharges to Watersheds in TWPE (Figure 4

- 14 ) X Top Receiving Watersheds (Figure 4

- 15 ) X X Top Facility Discharges in Pounds (Figure 4

- 16 ) X X Top Fac ility Discharges in TWPE (Figure 4

- 16 ) X X Top Facility Discharges (Figure 4

- 17 ) X X X



### 4.2.1 Top Pollutants Tables

The TRI Search provides two tables, shown in Fig ure 4 -11, that display pollutant rankings:

• 

### Top chemicals in pounds. This table displays the ten pollutants with the largest discharges meeting the user’s search criteria. Discharges are presented as pounds of chemicals discharged directly to receiving streams (direct discharges) and as pounds of chemicals discharged to POTWs following treatment at t he POTW (i.e., POTW pass through only - indirect discharges). The pollutants are ranked in order of decreasing direct discharge.

• 

### Top chemicals in TWPE. This table displays the ten pollutants with the highest TWPE meeting the user’s search criteria. TWPE are presented as direct discharges and indirect discharges. The pollutants are ranked in order of decreasing direct discharge TWPE.DRAFT Section 4 - Interface Development



### 4-18 Figure 4 -11. Example TRI Search Results – Top Chemicals Tables

4.2.2 Top NAICS Codes Tables

The TRI Search provides three tables that rank NAICS code discharges:

• 

### Top NAICS discharges in pounds. This table, shown in Figure 4-12, displays the NAICS codes with the ten largest discharges meeting the user’s search criteria. Discharges are presented as pounds of chemicals discharged di rectly to receiving streams (direct discharges) and as pounds of chemicals discharged to POTWs following treatment at the POTW (i.e., POTW pass through only - indirect discharges). The NAICS Code discharges are ranked in order of decreasing direct discharg e. Note the Loading Tool does not display this table if the user specifies a Pollutant or an Industry.

• 

### Top NAICS discharges in TWPE. This table, shown in Figure 4-12, displays the NAICS Codes with the ten highest toxic- weighted discharges meeting the user ’s search criteria. TWPE are presented as direct discharges and indirect discharges. The discharges are ranked in order of decreasing direct discharge TWPE. Note the Loading Tool does not display this table if the user specifies a Pollutant or an Industry.

• 

### Top NAICS discharges. The Loading Tool displays this table if the user specifies a TRI Chemical . This table, shown in Figure 4-13 , lists the ten NAICS codes with the largest discharges of the pollutant specified by the user. The table ranks the NAICS codes in order of decreases direct discharge TWPE.DRAFT Section 4 - Interface Development



### 4-19 F igure 4 -12. Example TRI Search Results – Top NAICS Code Discharges Tables ( Pollutant not Specified by User)

F igure 4 -13. Example TRI Search Results – Top NAICS Discharges Table ( Pollutant is Specified by User)DRAFT Section 4 - Interface Development



### 4-20 4.2.3 Top Watershed Tables

The TRI Search provides three tables that rank receiving watersheds:

• 

### Top discharges to watersheds in pounds. This table, shown in Figure 4-14

, displays the watersheds receiving the ten largest discharges meeting the user’s search criteria. Discharges are presented as pounds of chemicals discharged directly to receiving streams (direct discharges) and as pounds of chemicals discharged to POTWs following treatment at the POTW (i.e., POTW pass through only - indirect discharges ). The watershed discharges are ranked in order of decreasing direct discharge . Note the Loading Tool does not display this table if the user specifies a Pollutant or a Location/Watershed.

• 

### Top discharges to watersheds in TWPE. This table, shown in Figure 4-14, displays the watersheds receiving the ten highest toxic- weighted discharges meeting the user’s search criteria. The watersheds are ranked in order of decreasing direct TWPE. Note the Loading Tool does not display this table if the user specifies a Po llutant or a Location/Watershed.

• 

### Top receiving watersheds. The Loading Tool displays this table if the user specifies a TRI Chemical . This table, shown in Figure 4-15 , lists the ten watersheds receiving the largest discharges of the pollutant specified by the user. The table ranks the watersheds in the following order decreasing direct TWPE.DRAFT Section 4 - Interface Development



### 4-21 F igure 4 -14. Example TRI Search Results – Top Watershed Discharges Tables ( Pollutant not Specified by User)

F igure 4 -15. Example TRI Search Results – Top Rece iving Watersheds Table (Pollutant is Specified by User)DRAFT Section 4 - Interface Development



### 4-22 4.2.4 Top Facilities Tables

The TRI Search provides three tables that rank Facilities:

• 

### Top Facility discharges in pounds. This table, shown in Figure 4-16, displays the facilities with the ten largest di scharges meeting the user’s search criteria. Discharges are presented as pounds of chemicals discharged directly to receiving streams (direct discharges) and as pounds of chemicals discharged to POTWs following treatment at the POTW (i.e., POTW pass through only - indirect discharges). The facility discharges are ranked in order of decreasing direct discharge . Note the Loading Tool does not display this table if the user specifies a Pollutant.

• 

### Top Facility discharges in TWPE. This table, shown in Figure 4-16, displays the facilities with the ten highest toxic

- weighted discharges meeting the user’s search criteria. The facility discharges are ranked in order of decreasing direct TWPE. Note the Loading Tool does not display this table if the user specifies a Pollutant.

• 

### Top Facility discharges. The Loading Tool displays this table if the user specifies a pollutant. This table, shown in Figure 4-17 , lists the ten facilities with the largest discharges of the pollutant specified by the user. The table ranks the f acilities in the following order based of descreasing direct TWPE.DRAFT Section 4 - Interface Development



### 4-23 F igure 4 -16. Example TRI Search Results – Top Facility Discharges Tables (Pollutant not Specified by User)

F igure 4 -17. Example TRI Search Results – Top Facility Discharges Table (Pol lutant is Specified by User)DRAFT Section 4 - Interface Development



### 4-24 4.3 Compare DMR and TRI Data

The EZ Search flags records that have a facility and pollutant match in TRI data. Users can compare the results of their EZ Search query to TRI data by clicking the “Compare to TRI” button at the botto m of the results table of interest. Similarly, the TRI Search flags records that have a facility and pollutant match in DMR data.Users can compare the results of their TRI Search query to DMR data by clicking the “Compare to DMR” button at the bottom of th e results table of interest. Figure 4 -18 shows how the Loading Tool database links DMR and TRI discharges. Figure 4

- 19 presents an example Compare to TRI table from the EZ Search results, and Figure 4-20 presents an example Compare to DMR table.

DMR_LOADINGS_TRI_FACILITIES
TRI Facility ID

UIN
FACILITY_NAME

CITY_NAME
COUNTY_NAME
STATE_COUNTY_FIPS_CODE
STATE_ABBR
ZIP_CODE

REGION
FAC_LATITUDE
FAC_LONGITUDE

SIC_CODE
NAICS_CODE

DMR_LOADINGS_TRI_RELEASES
REPORTING_YEAR
TRI_FACILITY_ID

UIN
SIC_CODE
NAICS_CODE
CHEMICAL_NAME

TRI_CHEM_ID
UNIT_OF_MEASURE
DIRECT_RELEASE
INDIRECT_RELEASE

TWF
DIRECT_TWPE
INDIRECT_TWPE REF_TRI_CHEM_CAS_XWALK
CHEM_NAME
TRI_CHEM_ID

CASNU

DMR_LOADINGS_EZ_SRCH_CALCS

EXTERNAL_PERMIT_NMBR

PARAMETER_CODE

AVG_CONC

MAX_CONC

LBY

TWPE

AVG_DAILY_FLOW_BDLH

AVG_TEMP

MAX_TEMP

RUNYEAR

NUTAGGFLAG

LIMITS_INFO_FLAG

DMR_INFO_FLAG

ALLOWABLE_LOAD

ANNUAL_FLOW DMR_LOADINGS_REF_PARAMETER

PARAMETER_CODE

PARAMETER_DESC

POLLUTANT_CODE

POLLUTANT_DESC

GROUP_CODE

PRIORITY

NUTRIENT_AGG_PHOSP

NUTRIENT_AGG_NITRO

NUTRIENT_AGG_N_CVT

DELETE_FROM_LOADINGS

PCS_PARAMETER_CODE

CHEMICAL_ABSTRACT_SERVICE_NMBR

TWF

SRS_ID DMR_LOADINGS_FACILITIES

EXTERNAL_PERMIT_NMBR

UIN

MAJOR_MINOR_STATUS_FLAG

FACILITY_NAME

CITY

COUNTY_CODE

COUNTY_NAME

STATE_CODE

ZIP

EPA_REGION_CODE

CONGRESSIONAL_DIST_NUM

GEOCODE_LATITUDE

GEOCODE_LONGITUDE

PERMIT_TYPE_CODE

FACILITY_TYPE_INDICATOR

SIC_CODE_1

SIC_CODE_2

SIC_CODE_3

SICCODE

NAICS_CODE

TOTAL_DESIGN_FLOW_NMBR

ACTUAL_AVERAGE_FLOW_NMBR

HUC_CODE

HUC12

SOURCE_FEATURE_ID

STATE_WATER_BODY_NAME

STATE_WATER_BODY

DATA_SOURCE



### F igure 4-18. Loading Tool Database Design for Linking DMR and TRI DataDRAFT Section 4 - Interface Development



### 4-25 F igure 4 -19. Example Compare to TRI Page – EZ Search Top Facility Results for Single Facility

F igure 4 -20. Example Compare to DMR Page – TRI Search Top Facility Discharges TableDRAFT Section 4 - Interface Development



### 4-26 4.4 Facility Search

The Facility Search interface allows users to search the DMR data by facility, shown in Figure 4 -21. The interface provides several options for users to identify their facility of interest. Users can identify facilities by entering the NPDES permit ID, Facility Registry System (FRS) ID 13 

### , Clean Watersheds Needs Survey (CWNS) ID 14 

### , or Toxics Release Inventory (TRI) ID 15



### . To support this search feature, EPA developed the DMR_LOADINGS_FACILITIES_IDS table, which matches NPDES permit IDs with FRS IDs, CWNS IDs, and TRI IDs. Note a single NPDES ID can link to multiple FRS IDs, CWNS IDs, or TRI IDs.

• 

### NPDES Permit ID. The NPDES ID is stored in the DMR_LOADINGS_EZ_SRCH_CALCS table. When the user queries by NPDES ID, the Loading Tool filters the DMR_LOADINGS_EZ_SR CH_CALCS table by the specified NPDES ID and displays the facility information and pollutant loadings in the Facility Search Results page.

• 

### FRS ID. The FRS ID is stored in the DMR_LOADINGS_EZ_SRCH_CALCS table in the FACILITY_UIN field obtained from the ICI S_FACILITY_INTEREST table. When the user queries by FRSID, the Loading Tool displays a list of matching NPDES IDs for the user. The user can then select their facility of interest from the list. The Loading Tool filters the DMR_LOADINGS_EZ_SRCH_CALCS table by the selected NPDES ID and displays the facility information and pollutant loadings in the Facility Search Results page.

• 

### CWNS ID. When the user queries by CWNS ID, the Loading Tool uses the DMR_LOADINGS_FACILITIES_IDS table to match the specified CWNS ID to a NPDES ID, then displays a list of the matching NPDES ID(s) for the user. The user can then select their facility of interest from the list. The Loading Tool filters the DMR_LOADINGS_EZ_SRCH_CALCS table by the selected NPDES ID, and displays the fac ility information and pollutant loadings in the Facility Search Results page.

• 

### TRI ID. If the user searches by TRI ID, the Loading Tool uses the DMR_LOADINGS_FACILITIES_IDS table to match TRI IDs to NPDES IDs. The Loading Tool displays a list of matching NPDES IDs for the user. The user can then select their facility of interest from the list. The Loading Tool filters the DMR_LOADINGS_EZ_SRCH_CALCS table by the selected NPDES ID, and displays the facility information and pollutant loadings in the Facility Search Results page.

13 See http://www.epa.gov/enviro/html/fii/index.html for more information about FRS.

14 See http://www.epa.gov/cwns/ for more information about CWNS.

15 See http://www.epa.g ov/TRI/ for more information about TRI.DRAFT Section 4 - Interface Development



### 4-27 F igure 4 -21. Facility Search Page

Users can also search for a facility by facility name and/or state by doing one of the following:

• 

### Enter a full or partial facility name;

• 

### Select a state from the state menu and leave the facility nam e field blank; or

• 

### Enter a full or partial facility name and select a state from the state menu.

The Loading Tool displays a list of facilities that meet the user’s search criteria, shown in Figure 4 -22. The user can then select one facility from the list to view the Facility Search Results
page.

F igure 4 -22 . Example Facility Search Results for Multiple Facility Matches

Figure 4 -23 presents the Detailed Facility Report, which includes the following sections:DRAFT Section 4 - Interface Development



### 4-28 • 

### General Facility Information

. The first sect ion of the report displays facility information including, facility name, location, facility program IDs, facility type (e.g., federal, POTW), permit type (e.g., NPDES Individual Permit, General Permit), facility latitude/longitude, industrial activity (SI C code, NAICS code, point source category). This section also provides a link to the facility’s Enforcement Compliance Report on EPA’s Enforcement and Compliance History Online (ECHO) website. 16 

### This section also provides a link to the TRI version of the Fa cility Detail Page (see Figure 4 -24).

• 

### Top Pollutants. The facility report displays two tables displaying the ten pollutants with the highest pounds and the ten pollutants with the highest TWPE for the facility for the selected reporting year

. Users can use the reporting year menu to toggle back and forth between reporting years. Each table also presents the maximum allowable annual load and TWPE calculated using permit limits, if available., and has a link that allows the user to export the underlying pollutant loadings data (in pounds or TWPE) to a csv file. In addition, the text immediately above the tables provides a link for the user to view the underlying pollutant loading calculations for the facility.

• 

### Receiving Water Information

. This section lists the receiving waterbody name and number (REACH Code), the watershed name and number ( HUC -12 code)

, and provides additional information if the receiving waterbody is impaired, including the impairment class, impairment causes, and a list of pollutant
parame ters monitored at the facility that may contribute to the impairment. To access the waterbody, watershed, and impairments data, the Loading Tool obtains the HUC -12 code for the facility using the WATERS HUC12_to_NPDES lookup service

. Waterbody name and imp airment status are obtained through the WATERS Reachcode_to_GNIS lookup service. 17 

### Information about the impairment class and impairment causes are obtained using an OW look-up service that was custom -designed for the Loading Tool to input an NPDES ID and extract the impairment information from ATTAINS. In addition, EPA developed a crosswalk to link impairment causes to DMR pollutant parameters. The development of this crosswalk is described in Appendix D.

• 

### CWNS Treatment Information (POTWs only). This sec tion lists the treatment technologies in place at POTWs and information about population served according to the 2008 CWNS. To shorten the list of treatment technologies displayed on the screen, and to ensure that the treatment information would be meaning ful to a wide range of users, EPA simplified the CWNS treatment descriptions by assigning unit operations to broad treatment categories. EPA’s treatment category assignments are presented in Table D -2 of Appendix D. EPA also included check boxes to indicat e whether the POTW reported advanced treatment to CWNS.

16 EPA’s ECHO web site http://www.epa

- echo.gov/echo/

17 See http://www. epa.gov/waters/geoservices/docs/lookup_services.html#Reachcode_to_GNIS for more information about the Reachcode to GNIS lookup service.DRAFT Section 4 - Interface Development



### 4-29 • Facility Map. The Facility Detail page plots the facility on a Google map using the latitude and longitude coordinates from FRS. In addition, the Loading Tool uses an OW mapping service (upstream_dow nstream) to identify the closest monitoring stations upstream and downstream of the facility. Information about the water monitoring stations is obtained from STORET.

• 

### NPDES Program Information. This section contains two links to external web sites

. These include the NPDES program web sites for the State and EPA Region in which the facility is located.

Figure 4 -24 presents the Detailed Facility Report using TRI data. Although there is no direct search interface for TRI facilities, users can access the TRI Facility Report through the TRI Search and the Facility Search (DMR) results:

• In the TRI Search results for top facilities users can click on the hyperlinked facility name to view the TRI Facility Report.

• In the Detailed Facility Report (DMR) users can c lick on the hyperlinked TRI Facility ID to view the TRI Facility Report.DRAFT Section 4 - Interface Development



### 4-30 F igure 4 -23 . Example Detailed Facility Report (DMR)DRAFT Section 4 - Interface Development



### 4-31 TRI Search Results



### F igure 4 -2 4. Example Detailed Facility Report ( TRI )

4.5 Advanced Search

Figure 4 -25 presents the interface for the Advanced Search . The input to the Advanced Search is the database tables produced by the Convert and Load Calculator Modules: DMR_LOADINGS_FACILITIES, DMR_LOADINGS_PERM_FEATURES, DMR_LOADINGS_CONVERT_DMR, DMR_LOADINGS_WORK, DMR_LOADINGS_ANNUAL and DMR_LOADINGS_REF_PA RAMETER (see Section 3.1).

The Advanced Search interface allows users to specify the following search criteria, discussed in more detail in the following subsections:

• 

### Level of detail for loadings output. Users can query annual loads, monitoring period loads , or facility

- level loads

.

• 

### Filter criteria

. The user can filter the loadings by specifying criteria for timeframe, industry classification, facility identification, facility location, facility or permit type, receiving watershed, pollutant, or outfall/monitoring location.

• 

### Loading Calculation Options. The user has the option to turn some Loading Tool calculation methods on or off in the advanced search. These include estimating loads for nondetects, estimating loads for monitoring periods with no dischar ge data, grouping loads for parameters that represent the same pollutant, and aggregating nitrogen and phosphorus compound loads to calculate total nitrogen and total phosphorus.DRAFT Section 4 - Interface Development



### 4-32 4.5.1 Level of Detail

Unlike the EZ Search, the Advanced Search does not sum pollutant loads by geographic location, receiving watershed, or industry sector. Instead, the Advanced Search output lists the individual pollutant loads that meet the user’s search criteria. Each of the listed pollutant loads is specific to a particular NPDES permit, facility outfall, and pollutant parameter. As a default, the Advanced Search provides annual loads; however, the interface also allows users to select the loads on a monitoring period basis or at the facility level:

• 

### If the user queries annual load s, then the Loading Tool extracts data from the DMR_LOADINGS_ANNUAL table.

• 

### If the user queries monitoring period loads, then the Loading Tool extracts data from the DMR_LOADINGS_WORK table.

• 

### If the user queries facility loads, then the Loading Tool extrac ts data from the DMR_LOADINGS_ANNUAL table and aggregates the data to the facility level.

4.5.2 Filter Criteria

The Advanced Search interface allows uses to filter the DMR data by timeframe, industry classification, facility identification, facility location, facility or permit type, receiving watershed, pollutant, or outfall/monitoring location. These search fields provide the user with flexibility for conducting broad searches (e.g. all pollutant loads for a particular industry sector) or for conducting more fine- tuned searches by specifying more search criteria (e.g. pollutant loads for a particular facility outfall). Table 4 -3 presents the search criteria that users can specify in the Advanced Search.DRAFT Section 4 - Interface Development



### 4-33 Figure 4 -25. Advanced Search PageDRAFT Section 4 - Interface Development



### 4-34 Table 4

- 3 . Advanced Search Fields

Search Field Description Search Field Description
Timeframe Facility/Permit Type
Year DMR reporting year (e.g., 2007) Facility Type Menu of facility types in ICIS

- NPDES. Treatment Category (POTWs Only) Menu of CWNS treatment categories.

Monitoring Period Range Start Start date for monitoring period range (month and year) Approved Pretreatment Program (POTWs Only) Check to only include POTWs with an approved pretreatment program.

Number of CSO Outfalls (POTWs Only) Enter number of CSO outfalls.

Monitoring Period Range End End date for monitoring period range (month and year) Permit Type Menu of permit types in ICIS

- NPDES. Major/Minor Indicator Selects “majors only” or “minors only” from the

menu. Industry Classification Receiving Watershed
2

- Digit SIC code 2

- digit SIC code for Industry Sector of interest from

menu HUC Region Menu of 2

- digit HUC regions.

4

- Digit SIC code Enter up to 400 4

- digit SIC code s for Industry Sector s of interest (e.g., 2821) HUC Code Enter up to 400 12

- dig it HUC code s (e.g., 020700110302)
NAICS Code Enter up to 400 6

- digit NAICS code s for Industry Sector s of interest (e.g., 325110) Facility Identification Pollutant
FRS ID Enter up to 400 12

- character FRS ID s (e.g., 110001136271) CAS Number Enter up to 400 CAS Number s (without dashes) (e.g., 7439976).
NPDES Permit ID Enter up to 400 9

- character NPDES ID s (e.g., DC0000094) Pollutant Enter up to 400 p ollutant name s or select from pollutant look

- up link. Facility Name Full or partial facility name. The se arch output will include all records with facility names containing the specified text string. Parameter Code Enter up to 400 5

- digit parameter code s (e.g., 50060) or select from the parameter search link.

Facility Location Facility Outfall/Monitoring Loc ation City Full or partial city name. The search output will include all records with cities containing the specified text string. Permit Feature ID 3

- digit code for facility outfall of interest (e.g., 001)

State Menu of U.S. States and Territories/Tribe s Zip Code Enter up to 5

- digit zip code s (e.g., 20004) Permit Feature Latitude Full or partial latitude (e.g., 35.178) County Full or partial county name. The search output willDRAFT Section 4 - Interface Development



### 4-35 include all records with counties containing the specified text string. EPA Region Menu of EPA Regions Permit Feature Longitude Full or partial longitude (e.g.,

- 147.2568)

Facility Latitude Full or partial latitude (e.g., 35.178) Monitoring Location Code 1

- digit code for the monitoring location code of interest (e.g., 2) F acility Longitude Full or partial longitude (e.g.,

- 147.2568) Compliance
Facilities with Exceedances Check to only include facilities with pollutant loadings that exceed permit limits.
Percent Over Limit (%) Enter percent over limit (e.g., 50)

Pou nds Over Limit (lbs) Enter annual pollutant mass over limit in pounds (e.g., 5,000)DRAFT Section 4 - Interface Development



### 4-36 4.5.3 Loading Calculation Options

The Advanced Search allows users to turn same load calculation methods on or off. These methods include parameter grouping, the estimation function, detection limit options, and nutrient aggregation:

• 

### Data for Loading Calculations. Users have the option to calculate loads using D MR data (default), permit limits (allowable loads), or to use allowable loads to fill in for monitoring periods w here no DMR data are available. If the user selects the second or third option, they may also set the loadings that are based on permit limits to a percentage of the total allowable load. For example, a user can calculate annual loads that are 75 percent o f the annual loadings allowed under the facility’s NPDES permit.

• 

### Parameter Grouping. The parameter grouping logic avoids double counting loads f

or parameters that represent the same pollutant

. The DMR_LOADINGS_REF_PARAMETER table includes a field for the parameter group assignment and ranks the parameters in each group to give priority to pollutant parameters that represent a total discharge (See Section 3.3.2 for examples). If the user sets the parameter grouping function to ON then the Loading Tool selects one load per parameter group by selecting the load with the lowest priority number.

• 

### Estimation Function

. To avoid underestimating pollutant loads, the Loading Tool i ncludes a calculation option that estimates discharges for monitoring periods where no pollutant quantities or concentrations were reported. Section 3.2.4 describes how the Load Calculator Module identifies monitoring periods where DMR data were not reported and a discharge likely occurred

. The Load Calculator Module uses the NMBR_OF_REPORT field in DMR_LOADINGS_ANNUAL table to determine whether the load was based on 12 months of DMR data. If the user sets the estimation function to ON, and the NMBR_OF_REPORT is less than 12 for a load, then the Loading Tool performs the following calculation to normalize the load to 12 months per year:

Annual Load (kg/yr) = Sum of Monitoring Period Loads × 12/(Sum of NMBR_OF_REPORT)

For example, if a pollutant is reported quarterly, but only three reported values are present in ICIS, the NMBR_OF_REPORT sum will equal 9. The load calculator multiplies the sum of the three quarterly loads by 12/9. So if the sum of the three quarterly loads is 100 kg/yr, this sum is multiplied by 12/9 and the estimated annual load is 133 kg/yr.

• 

### DL Options

. The Load Calculator M odule provides three sets of monitoring period loads for each record in DMR_LOADINGS_WORK and DMR_LOADINGS_ANNUAL – one load per DL option: setting nondetects equal to zero, setting nondetects equal to one half the detection limit, and setting nondetects equal to the detection limit . When generating the Loading Tool outputDRAFT Section 4 - Interface Development



### 4-37 file, the Load Calculator selects the loads and concentrations only from the columns that match the user’s specified DL option.

• 

### Nutrient Aggregation

. As described in the Section 3.2.3, the Load Calculator Module calculates aggregated nitrogen and phosphorus loads and appends the loads to the DMR_LOADINGS_WORK and DMR_LOADINGS_ANNUAL tables . Aggregated nutrient loads are identified by a nutrient aggregation flag (NUTAGGFLAG = “A”) in the se tables . When the user queries loadings with the nutrient aggregation function on, the Loading Tool extracts the loads from either DMR_LOADINGS_WORK or DMR_LOADINGS_ANNUAL that are flagged as aggregated nutrients.

4.5.4 Advanced Search Output

After the user specifies the criteria for the Advanced Search, the Loading Tool, the user clicks a link to export the loadings to a CSV file

. Table 4 -4 presents the list of fields provided in the CSV file if the user queries monitoring period loads. Table 4-5 presents th e list of fields provided in the CSV file if the user queries annual loads. Table 4 -6 presents the list of fields provided in the CSV file if the user queries facility loads.

Table 4 -4. Advanced Search Results for Monitoring Period Loads

Field Description
PERIOD The end date of the monitoring period for the pollutant discharge.
SICCODE Four

- digit code that describes the primary activity of the facility. NAICS_CODE Six

- digit code that identifies NAICS industries
UIN Unique Identification Number is the 12 character code used to uniquely identify a facility site within the EPA Facility Registry System (FRS) database

. The code is also known as the FRS ID. EXTERNAL_PERMIT_ NMBR A nine

- character code used to uniquely identify a permitted NPDES facility (NP DES ID). FACILITY_NAME The primary name used to identify a facility in ICIS

- NPDES or PCS. FACILITY_TYPE_ INDICATOR Facility ownership classification derived from codes in ICIS

- NPDES and PCS

. Facilities can be classified as publicly

- owned treatment work s (POTW), non

- POTW, federal, or state. PERMIT_TYPE_CODE The permit classification in the DMR Pollutant Loading Tool. These classifications include the following: NPDES Individual Permit (NPD), NPDES Master General Permit (NGP), General Permit Covered Facility (GPC), State Issued Master General Permit (SNN), Individual IU Permit (IUU), Individual State Issued Permit (SIN).
CITY The city name for the facility location. STATE_CODE The two

- character state abbreviation for the facility location.
ZIP The 5

- d igit mail code for the facility address.
COUNTY The county name for the facility location. EPA_REGION_ CODE The EPA

- designated area that U.S. States, territories, and tribes are assigned to.
FACILITY LATITUDE The latitude coordinate for the facility lo cation in units of decimal degrees.
FACILITY LONGITUDE The longitude coordinate for the facility location in units of decimal degrees.DRAFT Section 4 - Interface Development



### 4-38 Table 4

- 4 . Advanced Search Results for Monitoring Period Loads

Field Description
MAJOR/MINOR STATUS A facility classification from the NPDES permitting authorities based on: toxic pollutant potential, ratio of discharge flow/stream flow volume, conventional pollutant loading, public health impact, water quality factors, and proximity to costal waters. Major facilities have a larger impact on receiving waters if not controlled than minor facilities; th erefore, they receive more regulatory attention than minor facilities

. A Major/Minor status code of “M” indicates the facility is a major

. This field is blank for minor facilities.
HUC12 Code assigned by the US Geological Survey used to classify watershed s in the United States and the Caribbean. Code consists of twelve digits which correspond to six levels of classification: Region (first

- level, 2

- digit HUC), Subregion (second

level, 4

- digit HUC), Accounting unit (third

- level, 6

- digit HUC), Cataloguing un it (fourth

- level, 8

- digit HUC), Watershed (fifth

- level, 10

- digit HUC), and Subwatershed (sixth

- level, 12

- digit HUC). TOTAL_DESIGN_ FLOW_ NMBR (MGD) The daily rate of wastewater flow that a facility is designed to discharge.

ACTUAL_AVERAGE_ FLOW_ NMBR (MG D) The daily rate of wastewater flow that a facility actually discharges.

PERM_FEATURE_NMBR A three

- character code in ICIS

- NPDES and PCS that identifies the point of discharge (e.g., outfall) for a facility

. A NPDES permit may have multiple points of disc harge. MONITORING_ LOCATION_CODE A single

- character code in ICIS

- NPDES and PCS that indicates the sampling location for each pollutant measurement

. The Loading Tool only includes locations for effluent sampling points

. These include: 1 – Effluent gross discharge; 2: Efflu ent net discharge; A – After Disinfection; B – Before Disinfection and; SC – See Comments. LIMIT_SET_DESIGNATO R A single

- character code in ICIS

- NPDES and PCS that uniquely identifies a limit set. For example, a facility’s NPDES permit may have annual limi t sets and quarterly limit sets. SEASON_ID A single

- character code in ICIS

- NPDES and PCS that groups permit limits into

seasons. PERMIT_FEATURE_LATI TUDE The latitude coordinate for the facility outfall location in units of decimal degrees.

PERMIT_FEATUR E_LON GITUDE The longitude coordinate for the facility outfall location in units of decimal degrees.

PARAMETER _CODE A five

- character code in ICIS

- NDPES and PCS that identifies the regulated pollutant parameter in a NPDES permit and specifies both the pollu tant name and pollutant form (e.g., dissolved or suspended)

. Multiple parameters can apply to a single pollutant or CAS number

. PARAMETER DESCRIPTION Description/parameter name that corresponds to the five

- digit parameter code.

CAS Chemical Abstract Ser vice Number assigned by the American Chemical Society that uniquely identifies a chemical.
SUBSTANCE REGISTRY SYSTEM ID Unique identification number assigned to substances, such as chemicals, biological organisms, physical properties, and miscellaneous objects by EPA’s Substance Registry Services, to provide a common substance identification method across multiple regulatory programs. PERMIT_LIMITS Five fields (Quantity 1 Limit, Quantity 2 Limit, Concentration 1 Limit, Concentration 2 Limit, Concentration 3 Limit) containing the NPDES permit limits for each monitoring period in standard units of mg/L and kg/day.DRAFT Section 4 - Interface Development



### 4-39 Table 4

- 4 . Advanced Search Results for Monitoring Period Loads

Field Description DMR_MEASUREMENTS Five fields (Quantity 1, Quantity 2, Concentration 1, Concentration 2, Concentration 3) containing the DMR data for each monito ring period in standard units of mg/L and kg/day.
POLLUTANT LOAD (KG/PERIOD) The total mass discharge of a pollutant to a receiving stream for a monitoring period calculated using the methodology described in Section 3.3.
WASTEWATER FLOW ( MGal/PERIOD) The total wastewater volume discharged per monitoring period.

AVG DAILY LOAD (KG/DAY) The average mass pollutant discharge per day for a monitoring period.

AVG CONC (MG/L) The average pollutant concentration for a monitoring period.
AVG DAILY FLOW (MGD) The average daily rate of flow that a facility discharges for a monitoring period.

AVG WASTEWATER TEMP (F) The average wastewater temperature for a monitoring period.

AVG WASTEWATER pH The average wastewater pH for a monitoring period. LOL (OPTION 1) ( KG/PERIOD) The difference between the Mass Discharge and the Mass Limit (“Load

- over

Limit”) calculated using the methodology described in Section 3.1.3. If the difference is positive, the discharge exceeded the permit limit

. If the difference is zero, the discharge is below the limit

. LOL (OPTION 2) (KG/PERIOD) The difference between the Mass Discharge and the Mass Limit (“Load

- over

Limit”) calculated using the methodology described in Section 3.1.3

. If the difference is positive, the discharge exceeded t he permit limit. If the difference is negative, the discharge was below the limit.
NONDETECT INDICATOR Flag indicating whether the reported pollutant concentration for the monitoring period was below the detection limit.
MEASUREMENT TYPE The PCS or ICI S

- NPDES measurement field (e.g., quantity or concentration field) that the Loading Tool selected to calculate the pollutant load. POTENTIAL_OUTLIER Data quality flag assigned by the Loading Tool to measurements that vary by more than three orders of magn itude from other measurements reported for the same reporting year.



### Table 4

- 5 . Advanced Search Results for Annual Loads

Field Description
PERIOD The calendar year for the annual pollutant loadings.
SICCODE Four

- digit code that describes the primary a ctivity of the facility. NAICS_CODE Six

- digit code that identifies NAICS industries.
UIN The Unique Identification Number which is the 12 character code used to uniquely identify a facility site within the EPA Facility Registry System (FRS) database

. Th e code is also known as the FRS ID. EXTERNAL_ PERMIT_ NMBR A nine

- character code used to uniquely identify a permitted NPDES facility (NPDES ID). The NPDES permit program regulates the direct discharge of pollutants into US waters. FACILITY_NAME The prim ary name used to identify a facility in ICIS

- NPDES or PCS. FACILITY_TYPE_ INDICATOR Facility ownership classification derived from codes in ICIS

- NPDES and PCS

. Facilities can be classified as publicly

- owned treatment works (POTW), non

POTW, federal, or s tate.DRAFT Section 4 - Interface Development



### 4-40 Table 4

- 5 . Advanced Search Results for Annual Loads

Field Description PERMIT_TYPE_ CODE The permit classification in the DMR Pollutant Loading Tool. These classifications include the following: NPDES Individual Permit (NPD), NPDES Master General Permit (NGP), General Permit Covered Facility (GPC), State Issued Master General Permit (SNN), Individual IU Permit (IUU), Individual State Issued Permit (SIN).
CITY The city name for the facility location. STATE_CODE The two

- character state abbreviation for the facility location.
ZIP The 5

- digit mail code for the facility address.
COUNTY The county name for the facility location. EPA_REGION_CODE The EPA

- designated area that U.S. States, territories, and tribes are assigned to. The U.S. is divided into 10 EPA Regions. CONGRESSIONAL_ DISTRICT A n electoral constituency that elects a single member of a congress. There are 435 congressional districts in the U.S.
FACILITY LATITUDE The latitude coordinate for the facility location in units of decimal degrees.
FACILITY LONGITUDE The longitude coordinate for the facility locatio n in units of decimal degrees.
MAJOR/MINOR STATUS A facility classification from the NPDES permitting authorities based on: toxic pollutant potential, ratio of discharge flow/stream flow volume, conventional pollutant loading, public health impact, water quality factors, and proximity to costal waters. Major facilities have a larger impact on receiving waters if not controlled than minor facilities; therefore, they receive more regulatory attention than minor facilities

. A Major/Minor status code of “M” indicates the facility is a major

. This field is blank for minor facilities.
HUC12 Code assigned by the US Geological Survey used to classify watersheds in the United States and the Caribbean. Code consists of twelve digits which correspond to six levels of classification: Region (first

- level, 2

- digit HUC), Subregion (second

- level, 4

- digit HUC), Accounting unit (third

- level, 6

- digit HUC), Cataloguing unit (fourth

- level, 8

- digit HUC), Watershed (fifth

- level, 10

- digit HUC), and Subwatershed (sixth

- level, 12

- digit HUC). CSO_COUNT The number of permitted combined sewer overflow outfalls for the facility. This result only applies to POTWs. TOTAL_DESIGN_ FLOW_ NMBR (MGD) The daily rate of wastewater flow that a facility is designed to discharge.

ACTUAL_ AVERAGE_ FLOW_ NMBR (MGD) The daily rate of wastewater flow that a facility actually discharges.

PERM_FEATURE_NMBR A three

- character code in ICIS

- NPDES and PCS that identifies the point of discharge (e.g., outfall) for a facility

. A NPDES permit may have multiple points of discharge. MONITORING_ LOCATION _CODE A single

- character code in ICIS

- NPDES and PCS that indicates the sampling location for each pol lutant measurement

. The Loading Tool only includes locations for effluent sampling points

. These include: 1 – Effluent gross discharge; 2: Effluent net discharge; A – After Disinfection; B – Before Disinfection and; SC – See Comments.
PARAMETER A five

- di git code in ICIS

- NDPES and PCS that identifies the regulated pollutant parameter in a NPDES permit and specifies both the pollutant name and pollutant form (e.g., dissolved or suspended)

. Multiple parameters can apply to a single pollutant or CAS number

. PARAMETER DESCRIPTION Description/parameter name that corresponds to the five

- digit parameter code.

CAS Chemical Abstract Service Number assigned by the American Chemical Society that uniquely identifies a chemical.DRAFT Section 4 - Interface Development



### 4-41 Table 4

- 5 . Advanced Search Results for Annual Loads

Field Description Substance Registry System ID Unique i dentification number assigned to substances, such as chemicals, biological organisms, physical properties, and miscellaneous objects by EPA’s Substance Registry Services, to provide a common substance identification method across multiple regulatory progra ms. PERMIT_LIMITS Five fields (Quantity 1 Limit, Quantity 2 Limit, Concentration 1 Limit, Concentration 2 Limit, Concentration 3 Limit) containing the NPDES permit limits for the reporting year in standard units of mg/L and kg/day. If limits change durin g the reporting year, the Loading Tool returns the limits that are in effect at the end of the reporting year.
POLLUTANT LOAD (KG/YR) Annual mass discharge of a pollutant to a receiving stream calculated using the methodology described in Section 3.3.
WA STEWATER FLOW (MMGal/YR) The total annual wastewater volume discharged.

AVG DAILY LOAD (KG/DAY) The average mass discharge per day for a calendar year calculated as the arithmetic average of the average daily loads for all monitoring periods in the calend ar year. AVG CONC (MG/L) The average pollutant concentration for a calendar year calculated as the arithmetic average of the average pollutant concentrations for all monitoring periods in the calendar year. AVG DAILY FLOW (MGD) The average daily rate of flow that a facility discharges for a calendar year calculated as the arithmetic average of the average daily wastewater flows for all monitoring periods in the calendar year.
AVG WASTEWATER TEMP (F) The arithmetic average of the average temperature for a ll monitoring periods in a calendar year. AVG WASTEWATER pH The arithmetic average of the average pH for all monitoring periods in a calendar
year. LOL (OPTION 1) (KG/YR) The sum of the differences between the Mass Discharges and the Mass Limits for all monitoring periods in a calendar year calculated using the methodology described in Section 3.1.3

. If the difference is positive, the discharge exceeded the permit limit during the calendar year

. If the difference is zero, the discharge was below the limi t for the entire calendar year

. LOL (OPTION 2) (KG/YR) The sum of the differences between the Mass Discharges and the Mass Limits for all monitoring periods in a calendar year calculated using the methodology described in Section 3.1.3

. If the difference is positive, the discharge exceeded the permit limit during the calendar year

. If the difference is negative, then the discharges were below the limit on average for the calendar year.
INCLUDES NONDETECTS Flag to identify annual pollutant loads that inc lude at least one monitoring period where the reported pollutant concentration was below the detection limit.
ESTIMATION FACTOR Weighting factor used when the estimation function is applied to account for periods of missing data for a reporting year. Thi s is calculated by dividing 12 by the sum of the number of months with DMR data and the number of months with no discharge. POTENTIAL_OUTLIER Data quality flag assigned by the Loading Tool to annual loads that are calculated from measurements that vary b y more than three orders of magnitude during the reporting year.DRAFT Section 4 - Interface Development



### 4-42 Table 4

- 6 . Advanced Search Results for Facility Loads

Field Description
PERIOD The calendar year for the pollutant loadings.
SICCODE Four

- digit code that describes the primary activity of the facility. NAICS_CODE Six

- digit code that identifies NAICS industries.
UIN The Unique Identification Number which is the 12 character code used to uniquely identify a facility site within the EPA Facility Registry System (FRS) database. The code i s also known as the FRS ID. EXTERNAL_ PERMIT_ NMBR A nine

- character code used to uniquely identify a permitted NPDES facility (NPDES ID). The NPDES permit program regulates the direct discharge of pollutants into US waters. FACILITY_NAME The primary name used to identify a facility in ICIS

- N PDES or PCS. FACILITY_TYPE_ INDICATOR Facility ownership classification derived from codes in ICIS

- NPDES and PCS. Facilities can be classified as publicly

- owned treatment works (POTW), non

POTW, federal, or state. PERMIT_TYPE_ CODE The permit classifica tion in the DMR Pollutant Loading Tool. These classifications include the following: NPDES Individual Permit (NPD), NPDES Master General Permit (NGP), General Permit Covered Facility (GPC), State Issued Master General Permit (SNN), Individual IU Permit (I UU), Individual State Issued Permit (SIN).
CITY The city name for the facility location. STATE_CODE The two

- character state abbreviation for the facility location.
ZIP The 5

- digit mail code for the facility address.
COUNTY The county name for the facil ity location. EPA_REGION_CODE The EPA

- designated area that U.S. States, territories, and tribes are assigned to. The U.S. is divided into 10 EPA Regions. CONGRESSIONAL_ DISTRICT A n electoral constituency that elects a single member of a congress. There a re 435 congressional districts in the U.S.
FACILITY LATITUDE The latitude coordinate for the facility location in units of decimal degrees.
FACILITY LONGITUDE The longitude coordinate for the facility location in units of decimal degrees.
MAJOR/MINOR ST ATUS A facility classification from the NPDES permitting authorities based on: toxic pollutant potential, ratio of discharge flow/stream flow volume, conventional pollutant loading, public health impact, water quality factors, and proximity to costal wate rs. Major facilities have a larger impact on receiving waters if not controlled than minor facilities; therefore, they receive more regulatory attention than minor facilities. A Major/Minor status code of “M” indicates the facility is a major. This field i s blank for minor facilities.
HUC12 Code assigned by the US Geological Survey used to classify watersheds in the United States and the Caribbean. Code consists of twelve digits which correspond to six levels of classification: Region (first

- level, 2

- digi t HUC), Subregion (second

- level, 4

- digit HUC), Accounting unit (third

- level, 6

- digit HUC), Cataloguing unit (fourth

- level, 8

- digit HUC), Watershed (fifth

- level, 10

- digit HUC), and Subwatershed (sixth

- level, 12

- digit HUC). CSO_COUNT The number of permitted combined sewer overflow outfalls for the facility. This result only applies to POTWs. TOTAL_DESIGN_ FLOW_ NMBR (MGD) The daily rate of wastewater flow that a facility is designed to discharge.

ACTUAL_ AVERAGE_ FLOW_ NMBR (MGD) The daily rate of wastewat er flow that a facility actually discharges.

FACILITY_INFO_ONLY Data flag assigned by the Loading Tool if the facility does not have permit or DMR data in PCS or ICIS

- NPDESDRAFT Section 4 - Interface Development



### 4-43 Table 4

- 6 . Advanced Search Results for Facility Loads

Field Description HAS_PERMIT_LIMITS Data flag assigned by the Loading Tool if the facility has effl uent limits on pollutants meeting user’s search criteria. HAS_LOADINGS_DATA Data flag assigned by the Loading Tool if the facility has DMR data for pollutants meeting user’s search criteria.
TOTAL FACILITY LOAD (KG/YR) Annual mass discharge of a ll pollut ant s from facility to a receiving stream calculated using the methodology described in Section 3.3.
TOTAL FACILITY FLOW ( MGal/YR) The total annual wastewater volume discharged from facility

.

FACILITY LOAD OVER LIMIT (OPTION 1) (KG/YR) The sum of the diff erences between the Mass Discharges and the Mass Limits for all monitoring periods in a calendar year calculated using the methodology described in Section 3.1.3

. If the difference is positive, the discharge exceeded the permit limit during the calendar ye ar. If the difference is zero, the discharge was below the limit for the entire calendar year.
FACILITY LOAD OVER LIMIT (OPTION 2) (KG/YR) The sum of the differences between the Mass Discharges and the Mass Limits for all monitoring periods in a calendar year calculated using the methodology described in Section 3.1.3

. If the difference is positive, the discharge exceeded the permit limit during the calendar year. If the difference is negative, then the discharges were below the limit on average for the c alendar year. POTENTIAL_OUTLIER Data quality flag assigned by the Loading Tool to facility loads that are calculated from measurements that vary by more than three orders of magnitude during the reporting year.DRAFT Section 5 - Quality Assurance Results



### 5-1 5. Q UALITY 

### A SSURANCE 

### R ESULTS



### This section in cludes the following discussion:

• 

### Recaps the quality assurance plans described in the Quality Assurance Plan for Development of the DMR Pollutant Loading Tool (Revision 4) (U.S. EPA. 2011 ).

• 

### Describes quality assurance steps and results.

• 

### States EPA’s conclus ions concerning data quality and usability.

Quality Assurance/Quality Control (QA/QC) procedures for the Loading Tool Development are outlined briefly in Figure 5-1 and include the following:

• 

### DMR data review ;

• 

### TRI data review ;

• 

### 304m screening- level analysis outlier review and error correction;

• 

### Calculation module verification and testing; and

• 

### Interface/final acceptance testing.

ICIS -NPDES Data Extraction PCS Data Extraction

Calculator Module

Development Data Reviews

Unit Testing System Testing Functional Testing

Interface Testing Final Acceptance Testing 304m Screening

Level Analysis Loadings Tool Interface Development

Loadings Tool Deployment User Error Reporting Outlier Review

EPA Data Error Correction Process Data errors identified by user are addressed by OEI in the underlying ICIS

- NPDES and PCS databases. Error corrections appear each annual refresh of Loadings Tool DMR and TRI data corrections identified by 304m screening

- level analysis TRI Data Extraction



### F igure 5 -1. Flow Chart of the Review Steps

5.1 DMR Data Review

EPA reviewed the combined DMR data extracted from PCS and ICIS

- NPDES to evaluate completeness, reasonableness, accuracy, and comparability. EPA conducted an initial quality review of the extracted DMR data to evaluate the completeness and reasonableness of the DMR data. The purpose of the review was to identify any quality issues (e.g., misreported units) that could be corrected by the convert module. During Phase 1 development of the Loading Tool, EPA verified the quality of the 2007 DMR dataset. Therefore, EPA compared the 2007 dataset to subsequent reporting years to asses the quality of the 2008 through 2010 datasets.DRAFT Section 5 - Quality Assurance Results



### 5-2 Completeness

E AD requires the DMR pollutant loading tool output to provide annual loads that can be prioritized and assigned to industrial categories. To meet these requirements, the following information is needed:

• 

### Facility identity;

• 

### Classification of industrial activity at the facility;

• 

### Identity of parameters discharged;

• 

### Discharge monitoring date;

• 

### Identity of monitoring location;

• 

### Mass of pollutants discharged (or pollutant concentration and discharge flow, from which the mass can be calculated);

• 

### Number of reported discharges available in ICIS- NPDES and PCS for the facility for a reporting year and the number of expected reported discharges for the facility; and

• 

### Understanding of how available information represents the discharger population and pollutant population.

The CONVERT_DMR table contains approximately 3 million records per reporting year which have all of the required information listed above. During Phase 1 development of the Loading Tool, EPA verified the completeness of the combined 2007 PCS and ICIS- NPDES dataset . Therefore, EPA compared the 2007 DMR data to subsequent reporting years to evaluate the completeness and reasonableness of the 2008 through 2010 data. Specifically, EPA performed the following tasks to evaluate the completeness of the extracted DMR data:

Number of Facilities

EPA counted the number of facilities (majors and minors) by state reporting to PCS and ICIS -NPDES for 2007, 2008, 2009, and 2010. These counts are presented in Table 5 -1. EPA determined that the total 2007 ICIS/PCS counts are similar to the counts for 2008 through 2010. Because these counts are similar, EPA concluded that in general that the extracted ICIS

- NPDES and PCS DMR data included all relevant facilities. T he detailed state reporting statistics are available on the Everyday Searches tab of the Loading Tool.

Table 5 -1. Summary of Facility Counts

Reporting Year # of Majors in ICIS

NPDES/PCS # of Minors in ICIS

NPDES/PCS # of Majors w/ Pollutant

Loadings # of M inors w/ Pollutant

Loadings % of Majors w/ Pollutant Loadings % of Minors w/ Pollutant Loadings

2007 6,893 58,829 5,619 19,199 82% 33%

2008 6,893 58,829 5,715 20,024 83% 34%

2009 6,893 58,829 5,724 20,368 83% 35%

2010 6,893 58,829 5,726 19,247 83% 33%DRAFT Section 5 - Quality Assurance Results



### 5-3 Universe of SIC Codes

E PA compared the list of SIC codes in 2007 DMR dataset to the list of SI C codes the 2008 through 2010 datasets. EPA expected that the 2007 dataset would comprise most of the SIC codes in the 2008 through 2010 dataset s. EPA’s review determined that all SIC codes in the 2007 dataset are represented in the 2008 through 2010 datas ets

.

Un iverse of Pollutant Parameters

EPA compared the list of the pollutant parameters in the 2007 DMR dataset to the 2008 through 2010 datasets to identify whether there are any new or missing parameters and verify that commonly reported parameters (e.g ., biochemical oxygen demand, ammonia, metals) are present in the 2008 through 2010 DMR data. The 2007 DMR dataset includes 1,047 pollutant parameters. Table 5 -2 presents the counts of pollutant parameter identified for the 2007 through 2010 datasets and the number of parameters from the 2007 dataset that have no match in subsequent reporting years. EPA expected to find differences in the universes of pollutants in the DMR data; however, EPA verified that all commonly reported parameters are present in all reporting years.

Table 5 -2. Summary of Parameter Counts

Reporting Year Count of Parameter Codes Count of 2007 Parameter Codes with no Match for Reporting Year

2007 1

, 047 0

2008 1

, 023 56

2009 1

, 023 74

2010 1

, 010 100



### Reasonableness

R eported pollutant concentrations and loads should reflect the range of concentrations and loads known to exist in the United States. Similarly, reported facility wastewater flows should reflect the range of flows known to exist in the United States. EPA performed the follo wing tasks to identify potentially unreasonable concentrations, loads, and flows in the DMR data:

• 

### Identified flows reported to ICIS- NPDES and PCS that exceeded 1,000 million gallons per day (MGD); 18



### • 

### Determined the reasonable ranges of reported concentration s and loads for pollutant parameter s with high TWFs (i.e., mercury and dioxin) ; and

• 

### Identified reported concentrations and loads that varied more than three orders of magnitude from monitoring period to monitoring period in each reporting year.

18 OECA’s pollutant loading tool for PCS (Effluent Data Statistics System) specifies 1,300 MGD as the upper limit for wa stewater flows. Any flows that exceed 1,300 MGD are assumed to be in units of gallons per day and are divided by 1,000,000.DRAFT Section 5 - Quality Assurance Results



### 5-4 EPA develo ped a flow correction function, described in Section 3.1.3.3. The flow correction function identifies corrections based on month- to -month variability, comparison to design flows, and as a last resort applies a 5,000 MGD cap on wastewater discharges. Additionally EPA assigned caps and performed automated corrections for mercury and dioxin discharges.

During Phase 1 development of the Loading Tool, EPA verified the accuracy of the unit conversions and pollutant loading calculations. Therefore, EPA suspects th at the unreasonable flows and pollutant measurements are the result of data entry errors and are not the result of any errors in the calculator module functions. The following changes were made to prevent data entry errors from impacting future calculation s.

Unreasonable Flow Values

EPA identified several wastewater flows that exceeded the reasonable range. EPA reviewed these flows and developed the proposed flow correction function for the Convert Module see Section 3.1.3.3 for more details. The goal of t his function is to identify data entry errors for flows greater than 1,000 MGD. The Convert Module corrects all flows exceeding 5,000 MGD, and applies more conservative criteria to correct flows from 1,000 to 5,000 MGD. Table 5 -2 presents counts of the corrections to DMR wastewater flows.

Table 5 -3. Automatic Flow Corrections

Correction Type 2007 2008 2009 2010 Month

- to

- Month Variations 1,318 1,173 1,402 828

Comparison to Design Flow 742 805 717 564

Exceeds 5,000 MGD Cap 543 585 616 570



### Combined, these corrections account for less than one percent of the flows per reporting
year

.

Unreasonable M ercury and Dioxin Values

E PA reviewed the ranges of concentrations reported to PCS and ICIS- NPDES for mercury and dioxin compounds and consulted analytical metho ds to estimate an upper limit for the range of reasonable concentrations of the compounds. EPA determined that reported mercury concentrations should not exceed 1 mg/L, and that dioxin concentrations should not exceed 0.001 mg/L. EPA automatically correcte d all reported concentrations that exceeded these limits by dividing the reporting concentrations by 1,000,000. Table 5 -4 presents the number of mercury and dioxin corrections that the Loading Tool made for each reporting year.

Table 5 -4. Automatic Mercur y and Dioxin Corrections

Correction Type 2007 2008 2009 2010 Mercury Concentrations Exceeding 1 mg/L 185 209 223 243 Dioxin Concentrations Exceeding 0.001 mg/L 23 51 54 56DRAFT Section 5 - Quality Assurance Results



### 5-5 Measurements with High Variability

D ue to the variety of pollutant parameters reported in DMR data, EPA was unable to assign reasonable ranges to all parameters. Therefore, to assist users in identifying discharges that may contain a DMR error, EPA flagged discharges that varied by more than three orders of magnitude in a reporting year. The data are flagged in both the EZ Search and Advanced Search results.

5.2 TRI Data Review

EPA reviewed the TRI data extracted from Envirofacts and compared the data to data compiled in the TRIReleases2007, TRIReleases2008, and TRIReleases2009 databases t hat EPA created for its 304m Screening-Level Analysis. EPA conducted the following checks of the TRI data:

• Compared counts of TRI records for 2007, 2008, 2009, and 2010 to verify that counts did not vary more than typical year- to

- year variation;

• Verified that the facility counts and total discharges calculated by the TRI Search Load Module were similar to the facility counts and discharges in the 304m Screening

- Level Analysis databases; and

• Verified that the universe of NAICS codes in the DMR_LOADINGS_TRI_R ELEASES table compared to the universe of NAICS codes in the 304m Screening- Level Analysis databases; and

• Verified that the universe of pollutants in the DMR_LOADINGS_TRI_RELEASES table compared to the universe of pollutants in the 304m Screening- Level An alysis databases.

Table 5 -5 presents the facility counts and discharges calculated by the TRI Search Load Module compared to the 304m Screening- Level Anlaysis databases. EPA notes that the TRI Search Load Module results match most closely with the 304m Sc reening

- Level Analysis results for 2009. The 2007 through 2010 TRI data in the Loading Tool database are current as of October 2011. The 2007, 2008, and 2009 TRI data for the 304m Screening- Level Analyses were extracted in 2009, 2010, and 2011, respectively. The TRI data are updated on a monthly basis in Envirofacts, so the underlying data used by the TRI Search Load Module are more current than the data used for the 304m Screening

- Level Analyses. EPA suspects that differences between the TRI Search Load Mo dule Output and the 304m Screening- Level Anlyses are due to TRI data updates that occurred after the 304m analysis.

Table 5 -5. Summary of TRI Facility Counts and Discharges

Reporting Year Count of Facilities Loading Tool Total Discharges (lb/yr) 304m Scree ning

Level Analysis Total Discharges (lb/yr) 2007 4,586 263 million 195 million 2008 4,641 281 million 276 million 2009 4,471 228 million 226 million 2010 4,476 245 million No AnalysisDRAFT Section 5 - Quality Assurance Results



### 5-6 5.3 304m Screening

- Level Analysis Outlier Review

During the 304m screening- level analysis, EPA calculates annual loads and toxic

weighted pound equivalents (TWPE) using DMR and TRI data. EPA ranks point source categories based on TWPE discharged and identifies categories for further review. To understand the accuracy of the calculated annual loads, EPA reviewed data for selected facilities. EPA typically conducts detailed facility reviews for less than 15 facilities per annual review

. EPA used the following criteria to select facilities for review:

• 

### Facilities with the highest toxic -weighted discharges of individual pollutant parameters; and

• 

### Facilities with relatively high percent of their discharges based on estimates for missing DMR data (estimation is not applicable to TRI data)

.
Facility discharges calculated by the Loadi ng Tool may be unusually high due to dataentry errors or incorrectly reported units of measure. In addition, the Loading Tool calculations can overestimate DMR discharges for facilities that discharge on a non -continuous basis, or can double-count dischar ges if facilities report pollutant measurements at both internal and final outfalls. EPA used the following steps to determine whether data -entry errors or Loading Tool calculation limitations contributed to the unusually high facility loads:

• 

### Reviewed DMR data corrections identified during screening- level analyses using year 2000 through 2009 DMR data and evaluated whether these corrections should be applied to the 2007 through 2010 DMR discharges

.

• 

### Reviewed TRI data corrections identified during screening

- level analyses using year 2000 through 2009 TRI data and applied these corrections to the 2007 through 2009 TRI discharges . EPA did not make manual corrections to the 2010 TRI discharges.

• 

### Reviewed 2007 through 2010 DMR data in the Loading Tool, and hand ca lculated annual pollutant loads to verify the accuracy of the Loading Tool calculations.

• 

### Reviewed PCS and ICIS -NPDES pipe description information available in PCS, EPA’s on -line Envirofacts data system, ICIS-NPDES supporting tables, or from the facility’s NPDES permit, to identify monitored pollutant discharges that are:

 Intermittent (e.g., tidal, seasonal, or occur after a storm event);

 Internal monitoring locations from which wastewater is combined with other waste streams and monitored again, resulting in double counting loads, and

 Not representative of category discharges (e.g., storm water runoff from
non-process areas, non- contact cooling water, or wastewater related to operations in another point source category).

• 

### Contacted facilities to determine if the pollutant discharges were entered correctly into PCS, ICIS

- NPDES , or TRI.

5.3.1 DMR Data Corrections

Section 3.1.5 describes how the Loading Tool Database stores DMR data corrections identified during the 304m screening level analysis and provided through public comment. Appendix B contains a compilation of all corrections made to the Loading Tool that adjustDRAFT Section 5 - Quality Assurance Results



### 5-7 calculated annual loads. There were a total of 1,054 unique records that had at least one type of correction identified (out of 3.4 million unique records in DMR_LOADINGS_CONVERT_DMR). A unique record is identified by a specific external permit number, parameter code, permit feature number (outfall), monitoring location code, monitoring period end date, and limit set designator.

Table 5 -6 below presents the number of each type of correction and the number of facilities affected by each. Flow corrections and concentration qualifiers were the most frequent and affected the most facilities. Note that because EPA has not conducted a 304m ScreeningLevel Anal ysis on the 2010 DMR data, fewer manual corrections were implemented for the 2010 DMR data.

Table 5

- 6. Manual Corrections to Data in DMR_LOADINGS_CONVERT_DMR

Type of Correction Number of Unique Records Corrected

- 2007 Number of Unique Records Corrected - 2008 Number of Unique Records Corrected

- 2009 Number of Unique Records Corrected - 2010
Concentration 229 31 67 32 Concentration Qualifiers 453 132 29 5 Quantity 101 111 27 9 Quantity Qualifiers 50 78 187 0
Flow 254 332 348 0 Number of Days 112 523 365 93

Total a 1,199 1,207 1,023 139

a – Some unique records have multiple corrections; therefore, the total is not equal to the sum of each type of correction.



### In addition, EPA identified records as containing DMR data for internal outfalls that were also monitored at final outfalls, and therefore deleted the records from the DMR_LOADINGS_CONVERT_DMR table. This correction affected 10 facilities. These identified errors do not represent all possible errors because EPA selected facility records for revi ew only if they had high TWPE or high estimated pollutant loads.

5.3.2 TRI Data Corrections

EPA stores corrections identified for the 2007 through 2009 TRI data in the Loading Tool Oracle database and applies these corrections to the DMR_LOADINGS_TRI_RELEASES table. Corrections include changes to direct releases, indirect releases, and TWFs that were identified during EPA’s 304m screening level analysis. There were approximately 15 to 20 unique records per reporting year that had at least one type of correction identified (out of approximately 20,000 unique records per reporting year in DMR_LOADINGS_TRI_RELEASES). A unique record is identified by a specific TRI Facility ID and chemical ID

. Table 5 -7 below presents the number of each type of correction applied fo r each reporting year

.DRAFT Section 5 - Quality Assurance Results



### 5-8 Table 5 -7. Manual Corrections to Data in DMR_LOADINGS_TRI_RELEASES

Type of Correction Number of Unique Records Corrected

- 2007 Number of Unique Records Corrected - 2008 Number of Unique Records Corrected - 2009
Direct Release 7 10 15
Indirect Release 3 2 2

TWF 5 10 4

Total 15 22 21



### In addition, EPA identified records as containing DMR data for internal outfalls that were also monitored at final outfalls, and therefore deleted the records from the DMR_LOADINGS_CONVERT_DMR table . This correction affected 10 facilities. These identified errors do not represent all possible errors because EPA selected facility records for review only if they had high TWPE or high estimated pollutant loads.

5.4 Calculation Module Testing

EPA calculatio n module testing is described in more detail in Quality Assurance Plan for Development of the ICIS -NPDES Pollutant Loading Tool (Revision 4) (U.S. EPA. 2011 ). EPA performed the following types of testing on the Convert Module and Load Calculator Module codes:

• 

### Unit Testing. Individual developers conducted unit testing as they coded individual functions or blocks of code. Developers ensured error-free operation of each section of code before combining the blocks of code for system testing.

• 

### System Testing. The lead programmer conducted system testing by examining integrated units and modules, grouped as appropriate. During integration testing, the lead programmer ensured that the new code addition does not impact the stable code base and that all parts of th e integrated code function properly. The lead programmer alerted the developers of any bugs identified during testing and the developers revised and retested the code. The lead programmer also reviewed code to ensure that it met design specifications.

• 

### Fu nctional Testing. An independent EPA test team conducted functional testing to verify that the Convert Module code and Load Calculator Module code correctly performed the functions described in Sections 3.1 and 3.2. EPA created a sample data set comprising five states and regions and 60 out of 164 SIC codes. EPA reproduced the Convert Module functions or Load Calculator Module functions in a Microsoft AccessTM database using the test data set. EPA then compared the output from the test database to the Convert Module or Load Calculator Module output and verified the following:

— Number of records in test data set equals number of records for the same data set in the output for both modules.

— All permits, permit features, parameter codes, and monitoring locations in test data set are included in the output for both modules; andDRAFT Section 5 - Quality Assurance Results



### 5-9 — All DMR measurements and flows in test data set match the output for both modules.

5.5 Inte rface Testing

The calculator module testing mentioned above is appropriate for database development activities. Additional testing was done for interface development. The ERG development team performed system test cases to test normal user scenarios, such as querying pollutant loads by specifying selection criteria (e.g., state, pollutant, facility). These included:

• 

### Scenarios users are expected to execute;

• 

### Types of values that should work in each scenario;

• 

### Types of values that should return errors;

• 

### The appropriate error messages according to the type of value; and

• 

### Fields to be checked against the Loading Tool database for accuracy and
currency.

In October 2009, EPA deployed a beta version of the Loading Tool on ERG servers for EPA Work Group members to review and provide comments. Per EAD’s direction, ERG incorporated Work Group comments prior to deploying the second beta version of the Loading Tool for public review.

EPA will perform final acceptance testing after deployment in WebCMS to verify that the we bsite functions as expected and meets design standards, functional requirements, and technical requirements (U.S. EPA 2008).

5.6 Error Reporting

The Loading Tool generates its output using annual data extracts from PCS and ICISNPDES

. These databases and underlying data are owned and maintained by OECA

. Users who identify an error in the Loading Tool output can report the error to OECA through EPA’s ECHO website. 19 

### User’s can access EPA’s ECHO website by clicking on “View Effluent Charts” links in the Facility Results (see Figure 5

- 2) or EZ Search Results (see Figure 5 -3).

Figure 5 -2. “Effluent Charts” Link on Facility Specific Page

19 EPA’s ECHO web site: http://www.epa

- echo.gov/echo/index.html

.DRAFT Section 5 - Qualit y Assurance Results



### 5-10 Figure 5

- 3. “Effluent Charts” Link on EZ Search Results Page (see purple “E” graphic)

The link will take the user to the Effl uent Charts page in ECHO, which displays monitoring period concentrations and loads graphically for the facility, pollutant, and reporting year of interest

. Users can review the DMR data in Effluent Charts to determine if there is an error in the data. If the user identifies a potentially erroneous measurement, they can report this error by clicking on the “Submit an Error” button at the top of the page (see Figure 5-4).

Figure 5

- 4. Submit an Error Button on Effluent Charts

When the user clicks the “Submit an Error” button they will be prompted to provide contact information and submit a comment concerning the identified DMR error (see Figure 5- 5). Figure 5 -6 presents the EPA data error correction process. Data corrections will be routed through the Office of Environmental Information (OEI), who will use OECA’s list of regional and state enforcement and compliance data stewards as the responsible officials for examining and correcting errors . Once the underlying PCS or ICIS- NPDES dat a have been corrected, the corrections will not be reflected in the DMR Loading Tool until the next annual refresh of the database.DRAFT Section 5 - Quality Assurance Results



### 5-11 Figure 5 -5. Discharge Monitoring Data Error Reporting FormDRAFT Section 5 - Quality Assurance Results



### 5-12 Figure 5 -6. EPA’s Data Error Correc tion Process

5.7 References

U.S. EPA. 2006. Quality Assurance Project Plan for 2007 Annual Screening- Level Analysis of TRI and PCS Industrial Category Discharge Data. December.

U.S. EPA. 2011. Quality Assurance Plan for Development of the DMR Pollutant Loading Tool (Revision 4). October.

U.S. EPA. 2009. Technical Support Document for the Annual Review of Existing Effluent Guidelines and Identification of Potential New Point Source Categories. October.