Document ID: EPA-HQ-OW-2004-0032-2303
Agency: epa
Document Type: Supporting & Related Material
Title: 
Posted Date: 2006-12-21T05:00Z

MEMORANDUM

TO:	2006 Effluent Guidelines Program Plan Docket 

	EPA Docket Number OW-2004-0032

FROM:	TJ Finseth, ERG	

DATE:	27 November 2006

SUBJECT:	SteamConcentrations2002 Database Dictionary

This memorandum describes the tables and queries that are contained in
the SteamConcentrations2002 database (DCN 03939).  The tables and
queries described were used to create the analyses that are described in
Chapter 5 of the Detailed Study Report for the Steam Electric Power
Generating Point Source Category (DCN 03401) and the memorandum
entitled, Response to Comments: Database Methodology Issues (DCN 04074).

The concentration data used in most of these analyses was downloaded
from EPA’s mainframe.  The downloaded concentrations are those input
to the Permit Compliance System (PCS) database from facility Discharge
Monitoring Reports (DMRs) for facilities reporting SIC codes 4911 and
4931 during reporting year 2002.  ERG downloaded these data for the
purpose of reviewing the specific concentration data that were used to
calculate the pollutant loadings for this industry.  ERG used these
concentration data for the analyses described by the queries included in
the SteamConcentrations2002 database.  This memorandum describes these
tables and queries.  

Input Data Tables

This section describes the data tables that were either downloaded
directly from the 2002 PCS or were created in another database (e.g.,
PCSLoads2002 – DCN 03654) and used as inputs to the analyses performed
by the SteamConcentrations2002 database.  The title of each of these
input data tables is listed below along with a description of the
information contained in each table.  For more information on the
creation of the PCSLoads2002 database and the screening-level review
process, see the 2005 Screening-Level Analysis Report (DCN 02173).

“Conc_AK_MN”

This table contains PCS concentration data for all the facilities
reporting SIC codes 4911 and 4931 that are in 23 states (Alaska through
Minnesota) and the District of Columbia.  This table was created from
the raw PCS data on the EPA mainframe.  

“Conc_MO_WY”

This table contains PCS concentration data for all the facilities
reporting SIC codes 4911 and 4931 that are in the remaining 27 states
(Missouri through Wyoming), Puerto Rico, and the Virgin Islands.  This
table was created from the raw PCS data on the EPA mainframe.

“Database Changes – Loads”

This table contains a list of the changes that were made to the
“PCS2002” table (described below) for all industrial categories
based on facility-specific changes to pollutant loads.  For a list of
the changes that were made to the steam electric industry, see the
memorandum entitled, “Changes made to the PCSLoads2002 Database Based
on Facility-Specific Comments” (DCN 03910).

“Discharge Pipe”

This table contains descriptions for the discharge pipes reported in
PCS.  This table was created from the raw PCS data on the EPA mainframe.

“Parameter Groupings”

This table contains a list of the parameters in PCS that are grouped
(e.g., the use of total arsenic over dissolved arsenic if both are
reported from the same discharge pipe at a facility).  This table also
contains the hierarchy by which the parameters are chosen for the
grouping.  For more information on parameter groupings, see Section
2.2.2.2 of the 2005 Screening-Level Analysis Report (DCN 02173).

“PCS2002”

This table contains the estimated annual loadings by facility, discharge
pipe, monitoring location, and parameter for all facilities in the PCS
database that have an annual loading greater than zero.  This is the
main table used in the PCSLoads2002 database. 

“PCSFAC”

This table contains a list of the facilities in the PCSLoads2002
database.  This table includes information about the facility, including
the name and location.

“PRAM_Codes”

This table contains a list of the parameter codes in PCS and their
descriptions.

“SUPERCAS_category”

This table contains a list of the parameter codes in PCS, their
descriptions, and the associated CAS Numbers.  The CAS number is used to
link the parameter code to its toxic weighting factor (TWF).

“TWFs”

This table contains the toxic weighting factors (TWFs) for each of the
pollutants in the April 2006 TWF database (DCN 03195).  The TWFs are
used to calculate toxic-weighted pound equivalent (TWPE).  To compute a
TWPE for each parameter reported, the estimated mass (in pounds) of the
chemical discharged is multiplied by its TWF. Additional information on
the calculation of TWPE and the PCS loading calculations can be found in
the 2005 Screening-Level Analysis Report (DCN 02173).

Analyses

This section describes the analyses that were performed for the steam
electric detailed study.  For each analysis, a list of the queries and
resulting tables used in the analysis is provided, as well as a
description of their function.

Concentration Analysis

The queries described below are used to perform a concentration analysis
for a single pollutant.  At the time they were created, the
concentration analysis was performed on arsenic, thus it is used in the
query and table titles; however, the queries can be used to perform the
analysis on any pollutant.  To perform the analysis for a different
pollutant, simply substitute the name of another pollutant for arsenic
in the Arsenic Concs 1 and Arsenic Concs 2 queries for the Parameter
Description (PRAMDESC).  

Arsenic Concs 1

This query pulls the concentration data for arsenic from the first set
of steam electric facilities (i.e., those located in Alaska through
Minnesota) from the “Conc_AK_MN” table.  It also identifies the
facility as a major or minor (both are included in the analysis) and it
pulls in the correct flow data.  Data are only pulled for monitoring
locations specified as “effluent gross value” or “effluent net
value” (MLOC 1 and MLOC 2, respectively).

Arsenic Concs 2

This query pulls the concentration data for arsenic from the second set
of the steam electric facilities (i.e., those located in Missouri
through Wyoming) from the “Conc_MO_WY” table.  It also identifies
the facility as a major or minor (both are included in the analysis) and
it pulls in the correct flow data.  Data are only pulled for monitoring
locations specified as “effluent gross value” or “effluent net
value” (MLOC 1 and MLOC 2, respectively).

Arsenic Concs 3

This query combines the data from Arsenic Concs 1 and Arsenic Concs 2 so
that all the concentration data are in one query.

Arsenic Concs 3a

This query pulls in the information from Arsenic Concs 3 that is needed
to group the parameters reported by the facility.  Only the facility,
pipe, and parameter information are needed, not the concentration data. 
For more information on parameter groupings, see Section 2.2.2.2 of the
2005 Screening-Level Analysis Report (DCN 02173).

“Conc Analysis – Grouping”

This table acts as a temporary storage table to allow the grouping of
the pollutants for the concentration analysis.  Parameters are grouped
to eliminate potentially double counting parameters that represent the
same pollutant.  A hierarchy is used to determine which parameter best
represents the total pollutant discharge.  For more information on
parameter groupings, see Section 2.2.2.2 of the 2005 Screening-Level
Analysis Report (DCN 02173).  For example, the arsenic hierarchy selects
the parameters representing arsenic in the following order: 

The data for total arsenic has precedence over all other arsenic data;

If total arsenic is not reported, the data for arsenic has precedence;

If total arsenic and arsenic are not reported, the data for total
recoverable arsenic has precedence;

The data from dissolved arsenic are used to represent total arsenic in
the absence of other arsenic parameters.

This table is created by the query Arsenic Concs 3b, which also collects
the first set of parameters in the pollutant hierarchy (Rank 1) from
Arsenic Concs 3a.

Arsenic Concs 3c

This query adds the second set of parameters (Rank 2) from Arsenic Concs
3a to the “Conc Analysis – Grouping” table.  

Arsenic Concs 3d

This query adds the third set of parameters (Rank 3) from Arsenic Concs
3a to the “Conc Analysis – Grouping” table.  

Arsenic Concs 3e

This query adds the fourth set of parameters (Rank 4) from Arsenic Concs
3a to the “Conc Analysis – Grouping” table.  

Arsenic Concs 3f

This query adds the fifth set of parameters (Rank 5) from Arsenic Concs
3a to the “Conc Analysis – Grouping” table.  

Arsenic Concs 3g

This query adds the sixth set of parameters (Rank 6) from Arsenic Concs
3a to the “Conc Analysis – Grouping” table.  

Arsenic Concs 3h

This query adds the seventh set of parameters (Rank 7) from Arsenic
Concs 3a to the “Conc Analysis – Grouping” table.  

Arsenic Concs 4

This query pulls all of the information in Arsenic Concs 3, but only for
the discharges and parameters in the “Conc Analysis – Grouping”
table.

Arsenic Concs 5

This query pulls all of the information from Arsenic Concs 4 for
discharges that have reported average concentration data.

Arsenic Concs 6

This query pulls all of the measurements from Arsenic Concs 5 that are
reported as below detection limit.

Arsenic Concs 7

This query pulls all of the facility information from Arsenic Concs 5
that have average concentrations that are not reported as below
detection limit.  This query also counts the number of measurements that
are not reported as below detection limit.

Arsenic Concs 8

This query calculates a new average concentration for the measurements
reported as below detection limits in Arsenic Concs 6.  For discharges
where the pollutant is always reported below detection limit, the
concentration is changed to zero.  For discharges where the pollutant
was not reported below detection limit at least once, the concentration
for measurements that were reported below detection limit is calculated
to be ½ of the detection limit.   

Arsenic Concs 9

This query copies the average concentrations from Arsenic Concs 5 that
are reported as above detection limit into a new field so that they can
be combined with the new below detection limit concentrations.

Arsenic Concs 10

This query combines queries Arsenic Concs 8 and Arsenic Concs 9.

To complete the concentration analysis, the results of Arsenic Concs 10
were exported into a Microsoft Excel® file for further manual
manipulation.  See DCNs 03829 and 03830.  In the spreadsheets, all zero
concentrations and zero flow rates were removed and facility-specific
concentration data changes described in Table 3-2 of the Preliminary
Engineering Report: Steam Electric Detailed Study (DCN 02176) and the
memorandum entitled, “Changes made to the PCSLoads2002 Database Based
on Facility-Specific Comments” (DCN 03910) were corrected.  The
minimum, maximum, average, and median concentrations were then
calculated.

Sample Specific MDL 1

This query pulls all of the average concentrations from Arsenic Concs 5
that are reported below the detection limit.

Sample Specific MDL 2

This query counts the number of times that each of the detection limits
from Sample Specific MDL 1 is reported.  This query was used to
determine the median value for the sample specific MDL.

Concentration Analysis by Waste Stream

The queries described below are used to perform a concentration analysis
for a single pollutant.  At the time they were created, the
concentration analysis was performed on arsenic, thus it is used in the
query and table titles; however, the queries can be used to perform the
analysis on any pollutant.  To perform the analysis for a different
pollutant, simply substitute the name of another pollutant for arsenic
in Arsenic Concs 1 and Arsenic Concs 2 for the parameter description
(PRAMDESC).  Then run the queries Arsenic Concs 3b – Arsenic Concs 3h.
 Then change the pollutant streams table (e.g., “Aluminum Streams –
AVG” table) that is used in the Stream Conc query and change the
source of the “pipe code” field for the query.  

Arsenic Concs 11

This query retrieves the pipe description from the “Discharge Pipe”
table for all waste streams that have average concentration data for the
specified pollutant in Arsenic Concs 10.  

These pipe descriptions were then exported into a Microsoft Excel® file
for further manipulation.  In the spreadsheets, duplicate entries were
deleted using data from permits, Envirofacts, and comments.  The pipe
descriptions were then used to determine which type of waste stream it
represents.  ERG used the pipe descriptions to put discharges into the
following categories:

Unknown discharge;

Cooling tower blowdown;

Other cooling water;

Ash handling discharges;

Coal pile runoff;

Metal cleaning waste;

Low-volume waste;

Boiler blowdown;

Final effluent;

Stormwater;

Flue gas desulfurization waste;

Nuclear discharges; and

Wastewater treatment.

For each pollutant, a list of the waste streams that have average
concentration reported and the pipe descriptions were created and
imported into the database.  The tables are titled Pollutant Streams –
AVG (e.g., “Aluminum Streams – AVG”). 

“Waste Stream Codes”

This table contains a description of the pipe codes that were used in
the concentration analysis by waste stream.

“Aluminum Streams – AVG”

This table contains a list of the waste streams that have average
concentration data for aluminum from the concentration analysis.  It
also contains the pipe codes that were identified manually from the pipe
descriptions in the “Discharge Pipe” table. 

 

“Arsenic Streams – AVG”

This table contains a list of the waste streams that have average
concentration data for arsenic from the concentration analysis.  It also
contains the pipe codes that were identified manually from the pipe
descriptions in the “Discharge Pipe” table. 

“Boron - NonNuclear Streams – AVG”

This table contains a list of the waste streams that have average
concentration data for boron from fossil-fueled facilities from the
concentration analysis.  It also contains the pipe codes that were
identified manually from the pipe descriptions in the “Discharge
Pipe” table. 

“Boron – Nuclear Streams – AVG”

This table contains a list of the waste streams that have average
concentration data for boron from nuclear facilities from the
concentration analysis.  It also contains the pipe codes that were
identified manually from the pipe descriptions in the “Discharge
Pipe” table. 

“Copper Streams – AVG”

This table contains a list of the waste streams that have average
concentration data for copper from the concentration analysis.  It also
contains the pipe codes that were identified manually from the pipe
descriptions in the “Discharge Pipe” table. 

Stream Conc

This query pulls the average concentration data from Arsenic Concs 10
and links it to the waste streams in the table “Arsenic Streams –
AVG”.    

To complete the concentration analysis by waste stream, the results of
Stream Conc were exported into a Microsoft Excel® file for further
manual manipulation.  See DCN 03825.  In the spreadsheets, all zero
concentrations and zero flow rates were removed and any
facility-specific concentration data changes were corrected.  The
concentrations were then separated by the waste stream they represent. 
The minimum, maximum, average, and median concentrations were then
calculated.

Maximum Analysis

The queries described below are used to perform a maximum analysis to
determine how frequently maximum values are used in the pollutant
loadings calculations.  

“Preferred Measurements_AK_MN”

This table contains the PCS concentration data from “Conc_AK_MN”,
but it also identifies which concentration/quantity and flow value was
used to calculate the discharged loads, based on the Effluent Data
Statistics (EDS) System hierarchy.  The table is created by the query
qryPreferredMeasurements_AK_MN.  The query uses only the initial
hierarchy performed by EDS because ERG does not have the statistical
base codes associated with the discharges.  Therefore, this query is an
estimate of how EDS would calculate the load.  For more information
about the EDS hierarchy and statistical base codes, see Section 2.2.2.1
of the 2005 Screening-Level Analysis Report (DCN 02173).  

“Preferred Measurements_MO_WY”

This table contains the PCS concentration data from “Conc_MO_WY”,
but it also identifies which concentration/quantity and flow value was
used to calculate the discharged loads, based on the Effluent Data
Statistics (EDS) System hierarchy.  The table is created by the query
qryPreferredMeasurements_MO_WY.  The query uses only the initial
hierarchy performed by EDS because ERG does not have the statistical
base codes associated with the discharges.  Therefore, this query is an
estimate of how EDS would calculate the load.  For more information
about the EDS hierarchy and statistical base codes, see Section 2.2.2.1
of the 2005 Screening-Level Analysis Report (DCN 02173).  

qry_Conc_All

This query combines all the records from the “Conc_AK_MN” and the
“Conc_MO_WY” tables.  

“Conc_All”

This table contains PCS concentration data for all the facilities
reporting SIC codes 4911 and 4931 (i.e., combined data from the
“Conc_AK_MN” and “Conc_MO_WY” tables).  The table is created by
qry_Make_Conc_All. 

qryPreferred Measurements_All

This query combines the records from “Preferred Measurements_AK_MN”
and “Preferred Measurements_MO_WY” tables.

“Preferred Measurements_All”

This table contains the PCS concentration data from “Conc_All”, but
it also identifies which concentration/quantity and flow value was used
to calculate the discharged loads, based on the Effluent Data Statistics
(EDS) System hierarchy.  This table was created by the query
qry_Make_Preferred Measurements_All.  The query combines the data from
the “Preferred Measurements_AK_MN” and “Preferred
Measurements_MO_WY” tables. 

Max 101

This query identifies all of the discharges in the “Preferred
Measurements_All” table that have at least one of the preferred
measurements reported as below the detection limit.

Max 102

This query identifies all of the discharges in the “Preferred
Measurements_All” table that have at least one of the preferred
measurements not reported above the detection limit.

Max 103

This query calculates a new concentration for pollutant discharges that
are always reported as below detection limit in the “Preferred
Measurements_All” table.  The query determines these discharges from
discharges that are in Max 101 but are not included in Max 102.  For
these discharges, the query shows the reported concentration, and also
calculates a new concentration of “0”.

Max 104

This query calculates a new concentration for pollutant discharges where
the pollutant concentration was not reported below detection limit at
least once, but also had reported concentrations reported below
detection limit.  For the preferred measurements reported below the
detection limit, the query shows the reported concentration, and also
calculates a new concentration equal to one-half the detection limit.

Max 105

This query pulls all the preferred measurements in the “Preferred
Measurements_All” table that are not reported below the detection
limit.  The query shows the reported concentration, and also reports a
new concentration equal to the reported value.

Max 106

This query combines the results of queries Max 103, Max 104, and Max
105.

Max 107

This query calculates an estimate monthly load for each discharge based
on the preferred quantity measurement or the preferred concentration and
flow measurements.

Max 108

This query sums the estimated monthly discharges by discharge pipe,
measurement value, and the detection limit indicator.  Therefore, if a
facility reports maximum concentrations part of the year, with some
detected and some not detected and average concentrations part of the
year, with all being detected for the same pipe, then there will be
three sums for that discharge.  However, there is not any double
counting of discharges; only those associated with each type of
discharge are summed.

“Max Analysis: Effluent Discharges”

This table acts as a temporary storage table for the maximum analysis to
collect discharges by monitoring location.  Facilities are often
required to monitor discharges at multiple locations along an outfall. 
The monitoring location is indicated by the MLOC field in PCS.  In the
case where a facility reports both an “effluent gross value” and
“effluent net value” for the same pollutant at an outfall, the loads
would be double counted if both values were used.  Therefore, ERG uses
net effluent data before it uses gross effluent data.  This table allows
ERG to add discharges to the table in a specific order, and ensure that
pollutant discharges are not double counted.   This table is created by
the query Max 109, which also adds the “effluent net value” data
(MLOC 2) from Max 108 to the table.

Max 110

This query adds the “effluent gross value” data (MLOC 1) from Max
108 to the “Max Analysis: Effluent Discharges” table.  

Max 110a

This query adds the “Disinfect, Process Complete” data (MLOC A) from
Max 108 to the “Max Analysis: Effluent Discharges” table.  

Max 110b

This query adds the “Prior to Disinfect” data (MLOC B) from Max 108
to the “Max Analysis: Effluent Discharges” table.  

Max 110c

This query adds the “Sec/Bio Process Complete” data (MLOC E) from
Max 108 to the “Max Analysis: Effluent Discharges” table.  

Max 110d

This query adds the “See Comments Below” data (MLOC V) from Max 108
to the “Max Analysis: Effluent Discharges” table.  

Max 110e

This query adds the “See Comments Below” data (MLOC #) from Max 108
to the “Max Analysis: Effluent Discharges” table.  

Max 111

This query pulls in the information from the “Max Analysis: Effluent
Discharges” table that is needed to group the parameters reported by
facilities.  The measurement value and detection limit indicator could
not be included in this query because it could affect the outcome if a
facility reports two different parameters representing the same
pollutant but only for different measurement values or detection limits
indicators.  This would then not exclude the second ranking parameter. 
Therefore, the original PRAM value is kept in the query to allow the
measurement value to be recollected in a later query.  For more
information on parameter groupings, see Section 2.2.2.2 of the 2005
Screening-Level Analysis Report (DCN 02173).

“Max Analysis: Grouped Table”

This table acts as a temporary storage table to allow the grouping of
the pollutants for the maximum analysis.  Parameters are grouped to
eliminate potentially double counting parameters that represent the same
pollutant.  A hierarchy is used to determine which parameter best
represents the total pollutant discharge.  For more information on
parameter groupings, see Section 2.2.2.2 of the 2005 Screening-Level
Analysis Report (DCN 02173).  For example, the arsenic hierarchy selects
the parameters representing arsenic in the following order: 

The data for total arsenic has precedence over all other arsenic data;

If total arsenic is not reported, the data for arsenic has precedence;

If total arsenic and arsenic are not reported, the data for total
recoverable arsenic has precedence;

The data from dissolved arsenic are used to represent total arsenic in
the absence of other arsenic parameters.

This table is created by the query Max 112, which also collects the
first set of parameters in the pollutant hierarchy (Rank 1) from Max
111.

Max 113

This query adds the second set of parameters (Rank 2) from Max 111 to
the “Max Analysis: Grouped Table”.  

Max 114

This query adds the third set of parameters (Rank 3) from Max 111 to the
“Max Analysis: Grouped Table”.  

Max 115

This query adds the fourth set of parameters (Rank 4) from Max 111 to
the “Max Analysis: Grouped Table”.  

Max 116

This query adds the fifth set of parameters (Rank 5) from Max 111 to the
“Max Analysis: Grouped Table”.  

Max 117

This query adds the sixth set of parameters (Rank 6) from Max 111 to the
“Max Analysis: Grouped Table”.  

Max 118

This query adds the seventh set of parameters (Rank 7) from Max 111 to
the “Max Analysis: Grouped Table”.  

Max 119

This query adds all of the parameters that do not have a grouping
hierarchy from the “Max Analysis: Effluent Discharges” table to the
“Max Analysis: Grouped Table”.

Max 120

This query pulls the discharge data from the “Max Analysis: Grouped
Table,” and also pulls the detection limit indicator and measurement
value from the “Max Analysis: Effluent Discharges” table. 

Max 121

This query calculates the total estimated yearly load for each pollutant
discharge independent of the measurement value and the detection limit
indicator.  This will be used to proportion the loads in the
“PCS2002” table, by the amount from each of the different
measurement values and detection limit indicators.

Max 122

This query pulls discharge information from Max 121 as well as collects
the annual pollutant loads from the “PCS2002” table.  This query
also calculates the TWPE for the discharges.  When the loads and TWPE
are calculated, the amount from the different measurement values and
detection limit indicators are proportioned according to the amount of
estimated monthly loads from the different discharges.  For example, if
a facility discharges aluminum as detected average concentrations for 6
months with a total estimated load of 500 pounds, discharges aluminum as
detected maximum concentrations for 2 months with a total estimated load
of 300 pounds, and discharges aluminum as non-detected maximum
concentrations for 4 months with a total estimated load of 200 pounds. 
The total estimated yearly load would be 1,000 pounds.  Then imagine the
facility load was corrected to be 500 pounds.  In this example, the
detected average concentration load would be calculated as 250 pounds,
the detected maximum concentration load would be calculated as 150
pounds, and the non-detected maximum concentration load would be
calculated as 100 pounds.  The calculation for TWPE is the same as the
calculation for loads.  This query excludes loads that are essentially
zero, and parameters that don’t have TWFs. 

“Max Analysis: Additional Discharges”

This table contains records that were not captured in the maximum
analysis queries for various reasons.  For example, the TVA – Allen
facility (TN0005355) had several changes to the PCSLoads2002 database
because the facility’s DMR data was entered incorrectly into PCS.  The
facility’s iron discharges from Outfall 006 were entered into PCS as
lead discharges; therefore, the concentration data for iron was not
available in the “Conc_All” table.  Because the iron discharges were
not available for this analysis, the iron discharge could not be matched
to the “PCS2002” table.  However, these discharges contribute to the
TWPE; therefore, they need to be included in the analysis.

Max 123

This query combines the records from Max 122 and the records in the
“Max Analysis: Additional Discharges” table.  

Max – Corrected 1

This query pulls all of the records from Max 123 that had corrections
made to them, identified in the “Database Changes – Loads” table. 

Max – Corrected 2

This query sums the pounds and TWPE from Max – Corrected 1 by the
Measurement value.

Max – Noncorrected 1

This query pulls all of the records from Max 123 that have not had
corrections made to them, identified by not being in the “Database
Changes – Loads” table.

Max – Noncorrected 2 

This query sums the pounds and TWPE from Max – Noncorrected 1 by the
Measurement value and the detection limit indicator.

Maximum Flow Analysis

The queries described below are used to perform a maximum flow analysis
to determine how frequently maximum flow values are used in the
pollutant loadings calculations.  

This analysis uses the following tables and queries that are described
in the Maximum Analysis section:

“Preferred Measurements_AK_MN”;

“Preferred Measurements_MO_WY”;

qry_Conc_All;

“Conc_All”;

qryPreferred Measurements_All; and

“Preferred Measurements_All.”

Max Flow 101

This query identifies all of the discharges in the “Preferred
Measurements_All” table that have at least one of the preferred
measurements reported as below the detection limit.

Max Flow 102

This query identifies all of the discharges in the “Preferred
Measurements_All” table that have at least one of the preferred
measurements not reported below the detection limit.

Max Flow 103

This query calculates a new concentration for pollutant discharges that
are always reported as below detection limit in the “Preferred
Measurements_All” table.  The query determines these discharges from
discharges that are in Max Flow 101 but are not included in Max Flow
102. For these discharges, the query shows the reported concentration,
and also calculates a new concentration of “0”.

Max Flow 104

This query calculates a new concentration for pollutant discharges where
the pollutant concentration was not reported below detection limit at
least once, but also had reported concentrations reported below
detection limit.  For the preferred measurements reported below the
detection limit, the query shows the reported concentration, and also
calculates a new concentration equal to one-half the detection limit.

Max Flow 105

This query pulls all the preferred measurements in the “Preferred
Measurements_All” table that are not reported below the detection
limit.  The query shows the reported concentration, and also reports a
new concentration equal to the reported value.

Max Flow 106

This query combines the results of queries Max Flow 103, Max Flow 104,
and Max Flow 105.

Max Flow 107

This query calculates an estimate monthly load for each discharge based
on the preferred quantity measurement or the preferred concentration and
flow measurements.

Max Flow 108

This query sums the estimated monthly discharges by discharge pipe,
measurement value, and the flow measurement.  Therefore, if a facility
reports maximum concentrations part of the year, some using average flow
values and some using maximum flow values and average concentrations
part of the year, all using average flow values for the same pipe, then
there will be three sums for that discharge.  However, there is not any
double counting of discharges; only those associated with each type of
discharge are summed.

“Max Flow Analysis: Effluent Discharges”

This tables acts as a temporary storage table for discharges in the
maximum flow analysis to collect discharges by monitoring location. 
Facilities are often required to monitor discharges at multiple
locations along an outfall.  The monitoring location is indicated by the
MLOC field in PCS.  In the case where a facility reports both an
“effluent gross value” and “effluent net value” for the same
pollutant at an outfall, the loads would be double counted if both
values were used.  Therefore, ERG uses net effluent data before it uses
gross effluent data.  This table allows ERG to add discharges to the
table in a specific order, and ensure that pollutant discharges are not
double counted.   This table is created by the query Max Flow 109, which
also adds the “effluent net value” data (MLOC 2) from Max Flow 108
to the table.

Max Flow 110

This query adds the “effluent gross value” data (MLOC 1) from Max
Flow 108 to the “Max Flow Analysis: Effluent Discharges” table.  

Max Flow 110a

This query adds the “Disinfect, Process Complete” data (MLOC A) from
Max Flow 108 to the “Max Flow Analysis: Effluent Discharges” table. 

Max Flow 110b

This query adds the “Prior to Disinfect” data (MLOC B) from Max Flow
108 to the “Max Flow Analysis: Effluent Discharges” table.  

Max Flow 110c

This query adds the “Sec/Bio Process Complete” data (MLOC E) from
Max Flow 108 to the “Max Flow Analysis: Effluent Discharges” table. 

Max Flow 110d

This query adds the “See Comments Below” data (MLOC V) from Max Flow
108 to the “Max Flow Analysis: Effluent Discharges” table.  

Max Flow 110e

This query adds the “See Comments Below” data (MLOC #) from Max Flow
108 to the “Max Flow Analysis: Effluent Discharges” table.  

Max Flow 111

This query pulls in the information from the “Max Flow Analysis:
Effluent Discharges” table that is needed to group the parameters
reported by facilities.  The measurement value and flow measurement
could not be included in this query because it could affect the outcome
if a facility reports two different parameters representing the same
pollutant but only for different measurement values or flow
measurements.  This would then not exclude the second ranking parameter.
 Therefore, the original PRAM value is kept in the query to allow the
measurement value and flow measurement to be recollected in a later
query.  For more information on parameter groupings, see Section 2.2.2.2
of the 2005 Screening-Level Analysis Report (DCN 02173).

“Max Flow Analysis: Grouped Table”

This table acts as a temporary storage table to allow the grouping of
the pollutants for the maximum flow analysis.  Parameters are grouped to
eliminate potentially double counting parameters that represent the same
pollutant.  A hierarchy is used to determine which parameter best
represents the total pollutant discharge.  For example, the arsenic
hierarchy selects the parameters representing arsenic in the following
order: 

The data for total arsenic has precedence over all other arsenic data;

If total arsenic is not reported, the data for arsenic has precedence;

If total arsenic and arsenic are not reported, the data for total
recoverable arsenic has precedence;

The data from dissolved arsenic are used to represent total arsenic in
the absence of other arsenic parameters.

This table is created by the query Max Flow 112, which also collects the
first set of parameters in the pollutant hierarchy (Rank 1) from Max
Flow 111.

Max Flow 113

This query adds the second set of parameters (Rank 2) from Max Flow 111
to the “Max Flow Analysis: Grouped Table”.  

Max Flow 114

This query adds the third set of parameters (Rank 3) from Max Flow 111
to the “Max Flow Analysis: Grouped Table”.  

Max Flow 115

This query adds the fourth set of parameters (Rank 4) from Max Flow 111
to the “Max Flow Analysis: Grouped Table”.  

Max Flow 116

This query adds the fifth set of parameters (Rank 5) from Max Flow 111
to the “Max Flow Analysis: Grouped Table”.  

Max Flow 117

This query adds the sixth set of parameters (Rank 6) from Max Flow 111
to the “Max Flow Analysis: Grouped Table”.  

Max Flow 118

This query adds the seventh set of parameters (Rank 7) from Max Flow 111
to the “Max Flow Analysis: Grouped Table”.  

Max Flow 119

This query adds all of the parameters that do not have a grouping
hierarchy for the “Max Flow Analysis: Effluent Discharges” table to
the Max Flow Analysis: Grouped Table”.  

Max Flow 120

This query pulls the discharge data from the “Max Analysis: Grouped
Table,” and also pulls the measurement value and flow measurement from
the “Max Flow Analysis: Effluent Discharges” table.

Max Flow 121

This query calculates the total estimated yearly load for each pollutant
discharge independent of the measurement value and the flow measurement.
 This will be used to proportion the loads in the “PCS2002” table,
by the amount from each of the different measurement values and flow
measurements.

Max Flow 122

This query pulls discharge information from Max Flow 121 as well as
collects the annual pollutant loads from the “PCS2002” table.  This
query also calculates the TWPE for the discharges.  When the loads and
TWPE are calculated, the amount from the different measurement values
and flow measurements are proportioned according to the amount of
estimated monthly loads from the different discharges.  For example, if
a facility discharges aluminum as average concentrations for 6 months
with average flow measurements for a total estimated load of 500 pounds,
discharges aluminum as maximum concentrations for 2 months with average
flow measurements for a total estimated load of 300 pounds, and
discharges aluminum as maximum concentrations for 4 months with maximum
flow measurements for a total estimated load of 200 pounds.  The total
estimated yearly load would be 1,000 pounds.  Then imagine the facility
load was corrected to be 500 pounds.  In this example, the average
concentration and average flow load would be calculated as 250 pounds,
the maximum concentration and average flow load would be calculated as
150 pounds, and the maximum concentration and maximum flow load would be
calculated as 100 pounds.  The calculation for TWPE is the same as the
calculation for loads.  This query excludes loads that are essentially
zero, and parameters that don’t have TWFs. 

Max Flow 122a

This query pulls the discharges from Max Flow 122 that are based on
pollutant concentration and flow values (i.e., average concentration
(MCAV), minimum concentration (MCMN), and maximum concentration (MCMX)).
 These discharges based on quantities are not included because the
facility calculated the pollutant loadings, not EDS.  Therefore, the
flow values used to calculate the loading are unknown.

“Max Flow Analysis: Additional Discharges”

This table contains records that were not captured in the maximum flow
analysis queries for various reasons.  For example, the TVA – Allen
facility (TN0005355) had several changes to the PCSLoads2002 database
because the facility’s DMR data was entered incorrectly into PCS.  The
facility’s iron discharges from Outfall 006 were entered into PCS as
lead discharges; therefore, concentration data for iron was not
available in the “Conc_All” table.  Because the iron discharges were
not available in this analysis, the iron discharge could not be matched
to the “PCS2002” table.  However, these discharges contribute to the
TWPE; therefore, they need to be included in the analysis.

Max Flow 123

This query combines the records from Max Flow 122a and the records in
the “Max Flow Analysis: Additional Discharges” table. 

Max Flow 124

This query sums the pounds and TWPE from Max Flow 123 for each of the
different flow measurements.  This query also counts the number of
records that contribute to the loads.

Internal Monitoring Double Counting

There are two separate analyses that were performed to determine the
extent of double counting due to the inclusion of internal monitoring
points.  The first one described below addresses the addition of
pollutant discharges from discharge pipes with a letter designation
(e.g., the summation of copper loads from 001A and 001B into one load
for 001).  This would only lead to double counting, however, if one of
the discharges is commingled with the other, and the monitoring of the
other one occurs after commingling.  The queries for this analysis are
described below and the second analysis is described after this
analysis. 

Internal Count 1

This query pulls all of the pollutant discharges from the “Conc_All”
table by facility, pipe, reporting designator, and parameter that are
reported as “effluent net values” (MLOC 2).  The concentrations and
flow are not needed because it only matters if the pollutant was
reported for this analysis.

Internal Count 2

This query pulls all of the pollutant discharges from the “Conc_All”
table by facility, pipe, reporting designator, and parameter that are
reported as “effluent gross value” (MLOC 1), that were not reported
as “effluent net value.”  The concentrations and flow are not needed
because it only matters if the pollutant was reported for this analysis.

Internal Count 3

This query combines the records from Internal Count 1 and Internal Count
2, but does not include the MLOC, because it is not need anymore for the
analysis.

Internal Count 4

This query links the discharges from Internal Count 3 to the “TWFs”
table so that only toxic pollutants (pollutants with TWFs) are included
in the analysis.

Internal Count 5

This query counts the number of different reporting designators for each
facility, pipe, and parameter in Internal Count 4.  For any number that
is greater than 1, there is the potential for there to be double
counting from the summing of loads for the different reporting
designators.

Internal Count 6

This query pulls all the discharges from Internal Count 5 and also pulls
the grouped parameter name from the “Parameter Groupings” table for
each of the pollutant discharges, so that the loads from the discharges
can be linked from the “PCS2002” table.

Internal Count 7

This query pulls all the discharges from Internal Count 6 and also pulls
the pollutant loads for each of the discharges from the “PCS2002”
table.  The query also calculates the TWPE associated with each
discharge.  The TWPE calculated in this query does not represent the
TWPE that is double counted, it only represents the total calculated
TWPE for the pollutant discharge, for which, some of the TWPE may be
overestimated.

Internal Monitoring Double Counting – Part II

The queries described below are used to perform an analysis to determine
the extent of double counting due to the inclusion of internal
monitoring points.  This second analysis (the first is described above)
assumes that all final effluent discharges have a discharge pipe with a
“0” for the first digit (e.g., 001) and that any internal monitoring
points for this discharge will have a different first digit, but the
same second and third digit (e.g., 101 and 201).  This outfall numbering
scheme is used by many permit writers, but it is not universally used
throughout the United States. 

DSCH  1

This query calculates the TWPE for all discharges in the “PCS2002”
table.  It also divides the three digit discharge pipe number (DSCH)
into two sections.  It divides it into the first part, which is the
first digit, and the second part, which is the second and third digit.

DSCH 2

This query counts the number of first parts of the DSCH pipe by the
facility, second part, MLOC, and PRAM.  The query is filtered to only
show facilities in SIC codes 4911, 4931, 4939, or 4961 that have more
than one first part, because there could only be double counting if
there was more than one.

DSCH 3

This query pulls all of the pollutant discharge information from DSCH 1
for discharges that are in DSCH 2 and that have a first part that is
non-zero.  These would be considered internal monitoring points by the
outfall numbering scheme.

DSCH 4

This query sums the pounds and TWPE from discharges in DSCH 3 by the
facility, second part, MLOC, and PRAM.

DSCH 5

This query pulls all of the pollutant discharge information from DSCH 1
for discharges that are in DSCH 2.  

DSCH 6

This query pulls all of the discharges from DSCH 5 that have a zero for
the first part of the DSCH and sums the pounds and TWPE by the facility,
MLOC, and PRAM.  These are the effluent discharge pounds and TWPE.

DSCH 7

This query pulls discharge information from DSCH 4 (internal monitoring
points) and the corresponding discharge information from DSCH 6
(effluent monitoring points) from the same facility that have the same
second part, MLOC, and PRAM.  

DSCH 8

This query sums the internal pounds and TWPE and the effluent pounds and
TWPE from DSCH 7 by the facility.

Intake Analysis

The queries described below are used to perform an intake analysis to
determine the impact of not accounting for intake concentration on the
pollutant loads from steam electric facilities.  

Intake 101

This query pulls discharges from the “Conc_All” table that are
identified as intake measurements from the monitoring locations: MLOC 0
(“Intake”), MLOC 3 (“Intake public water”), and MLOC 7
(“Intake from stream”).

Intake 102

This query pulls all of the discharges from Intake 101 and also pulls
the parameter grouping name from the “Parameter Grouping” table, so
that the loads from the discharges can be linked from the “PCS2002”
table.

Intake 103

This query pulls pollutant discharges from Intake 102 that have not had
corrections made to them, identified by not being in the “Database
Changes – Loads” table.

Intake 104

This query groups the discharges from Intake 103 by the facility,
discharge pipe, and parameter.

Intake 105

This query pulls all the discharges from Intake 104 and also pulls in
the pounds discharged by the facility, discharge pipe, MLOC, and PRAM
from the “PCS2002” table.  By not linking the tables on the
discharge pipe, we are assuming that the intake data apply to all of the
outfalls from the facility.

Intake 106

This query calculates the TWPE for all pollutant discharges in Intake
105.

Intake 107

This query sums the pounds and TWPE by facility for discharges from
Intake 106 reported as “effluent net values.”

Intake 108

This query sums the pounds and TWPE by facility for discharges from
Intake 106 reported as “effluent gross values.”

Intake 109

This query sums the pounds and TWPE by facility for all discharges in
Intake 106.

Intake 110

This query sums the total pounds and TWPE from Intake 109, effluent
gross pounds and TWPE from Intake 108, and the effluent net pounds and
TWPE from Intake 109.  This query also counts the number of facilities
included in the analysis.

Intake 111

This query calculates the pounds and TWPE by facility and monitoring
location for all steam electric facilities in the ‘PCS2002” table.

Intake 112

This query sums the pounds and TWPE by facility for discharges in Intake
111.

Intake 113

This query sums the pounds and TWPE by facility for discharges in Intake
111 reported as “effluent net values.”

Intake 114

This query sums the pounds and TWPE by facility for discharges in Intake
111 reported as “effluent gross values.”

Intake 115

This query shows the total pounds and TWPE from Intake 112, effluent
gross pounds and TWPE from Intake 114, and the effluent net pounds and
TWPE from Intake 113.

Same Concentration Each Month

The queries described below are used to perform an analysis of how often
facilities are reporting the same concentration each month without a
less than indicator.  

Conc BDL 101

This query pulls records from the “Conc_All” table that have a
greater than zero minimum concentration reported that is not below the
detection limit.  This query groups the discharges if they have the same
concentration and counts the number of times the concentration is
reported. 

Conc BDL 102

This query pulls records from the “Conc_All” table that have a
greater than zero average concentration reported that is not below the
detection limit.  This query groups the discharges if they have the same
concentration and counts the number of times the concentration is
reported. 

Conc BDL 103

This query pulls records from the “Conc_All” table that have a
greater than zero maximum concentration reported that is not below the
detection limit.  This query groups the discharges if they have the same
concentration and counts the number of times the concentration is
reported. 

Conc BDL 104

This query counts the number of reported records for each facility,
discharge pipe, reporting designator, parameter, monitoring location,
season number, and limit pipe set qualifier.  This query excludes
reports that only have one reported record.

Conc BDL 105

This query pulls all of the records from Conc BDL 101 that are also in
Conc BDL 104 and have the same number of reported records for the
discharges.  The key is that the counts of reported records are the same
in both queries, because this means that number of every time discharge
was reported, it was reported as the same concentration.

Conc BDL 106

This query pulls all of the records from Conc BDL 102 that are also in
Conc BDL 104 and have the same number of reported records for the
discharges.  The key is that the counts of reported records are the same
in both queries, because this means that number of every time discharge
was reported, it was reported as the same concentration.

Conc BDL 107

This query pulls all of the records from Conc BDL 103 that are also in
Conc BDL 104 and have the same number of reported records for the
discharges.  The key is that the counts of reported records are the same
in both queries, because this means that number of every time discharge
was reported, it was reported as the same concentration.

Conc BDL 108

This query combines the records from Conc BDL 105, Conc BDL 106, and
Conc BDL 107, but does not include the counts of reported records, the
detection limit indicator, or the concentrations.

Conc BDL 109

This query combines the records from Conc BDL 105, Conc BDL 106, and
Conc BDL 107 by the fields that are in Conc BDL 108.  This query pulls
in the reported records and the concentrations for the minimum, average,
and maximums.

Conc BDL 110

This query pulls all the records from Conc BDL 109 and also pulls in the
parameter grouping name from the “Parameter Grouping” table so that
the loads from the discharges can be linked from the “PCS2002”
table.

Conc BDL 111

This query pulls all records from Conc BDL 110 and also pulls in the
pollutant loads from the “PCS2002” table.  This query also
calculates the TWPE for each of the discharges.  This query excludes
discharges that do not have TWFs.

Waste Stream Flow Rates

The queries described below are used to determine the flow rates of
certain waste streams from the steam electric industry based on PCS
data.  The waste streams were manually identified as representing the
waste stream using the pipe descriptions available in the “Discharge
Pipe” table.

“Pipes – Flue Gas Desulfurization”

This table contains a list of discharge pipes from steam electric
facilities that were identified as discharging flue gas desulfurization
waste, based on pipe descriptions in the “Discharge Pipe” table.

FGD Flows 1

This query pulls the discharge pipe information from the “Pipes –
Flue Gas Desulfurization” table and also pulls the flow rates for each
these waste streams in the “Conc_All” table.  The query pulls the
flow rates using the EDS hierarchy.  

“Pipes – Once-Through Cooling”

This table contains a list of discharge pipes from steam electric
facilities that were identified as once-through cooling water based on
pipe descriptions in the “Discharge Pipe” table.

Once-Through Flows 1

This query pulls the discharge pipe information from the “Pipes –
Once-Through Cooling” table and also pulls the flow rates for these
waste streams from the “Conc_All” table.  The query pulls the flow
rates using the EDS hierarchy.

Once-Through Flows 2

This query adjusts the flow rates from Once-Through Flows 1 that may
have been reported as gallons per day instead of million gallons per
day.  If the flow is reported as greater than 1,300, then the query
divides the flow by one million.  This is the same approach that EDS
uses.

“Pipes – Cooling Tower”

This table contains a list of discharge pipes from steam electric
facilities that were identified as cooling tower blowdown based on pipe
descriptions in the “Discharge Pipe” table.

Recirculating Flows 1

This query pulls the discharge pipe information from the “Pipes –
Cooling Tower” table and also pulls the flow rates for these waste
streams from the “Conc_All” table.  The query pulls the flow rates
using the EDS hierarchy.

Recirculating Flows 2

This query adjusts the flow rates from Recirculating Flows 1 that may
have been reported as gallons per day instead of million gallons per
day.  If the flow is reported as greater than 1,300, then the query
divides the flow by one million.  This is the same approach that EDS
uses.

Steam TWPE by Dischargers

The queries described below are used to determine the total steam
electric TWPE by the type of discharger (i.e., major or minor).

Steam TWPE by Type of Discharger 1

This query calculates the TWPE for each discharge in the “PCS2002”
table by the facility, discharge pipe, MLOC, PRAM, and major discharger
indicator.

Steam TWPE by Type of Discharger 2

This query sums the pounds and TWPE from Steam TWPE by Type of
Discharger 1 by facility for all facilities reporting SIC Code 4911 or
4931.  The query also contains the major discharger indicator.

Steam TWPE by Type of Discharger 3

This query sums the pounds and TWPE from Steam TWPE by Type of
Discharger 2 by the major discharger indicator (i.e., majors and minors)
and counts the number of facilities in both categories.

PCS Top Pollutants

The queries described below are used to determine the top pollutants
discharged by the steam electric industry.

PCS Major and Minor Top Pollutants 1

This query sums the pounds and TWPE by facility for all the discharges
in Steam TWPE by Type of Discharger 1.  This query excludes discharges
that are essentially zero pounds.

PCS Major and Minor Top Pollutants 2

This query sums the pounds and TWPE from discharges in PCS Major and
Minor Top Pollutants 1 for all facilities reporting SIC codes 4911 or
4931.  This query excludes pollutants that have zero TWPE.  This query
also counts the number of facilities that are contributing to the
loadings.

References

Bartram, Deb.  2006.  Eastern Research Group, Inc.  Memorandum to Carey
Johnston and Jan Matuszko, U.S. EPA.  “Response to Comments: Database
Methodology Issues.”  (November).  DCN 04074.

Eastern Research Group, Inc.  2006a. April 2006 TWF database.  (April
20).  DCN 03195.

Eastern Research Group, Inc.  2006b. Average Concentration Analysis for
Pollutants Identified by Commenters.  (May 25).  DCN 03829.

Eastern Research Group, Inc.  2006c. Average Concentration Analysis for
Top Pollutants.  (May 26).  DCN 03830.

Eastern Research Group, Inc.  2006d. Average Concentrations by Waste
Stream.  (July 7).  DCN 03825.  

Finseth, TJ.  2006.  Eastern Research Group, Inc.  Memorandum to 2006
Effluent Guidelines Program Plan Docket EPA Docket Number OW-2004-0032. 
“Changes Made to the PCSLoads2002 Database Based on Facility-Specific
Comments.”  (October 17).  DCN 03910.

U.S. EPA.  2005a. 2005 Annual Screening-Level Analysis:  Supporting the
Annual Review of Existing Effluent Limitations Guidelines and Standards
and Identification of New Point Source Categories for Effluent
Limitations and Standards.  EPA-821-B-05-003.  Washington, DC. 
(August).  DCN 02173.

U.S. EPA.  2005b. Preliminary Engineering Report: Steam Electric
Detailed Study.  EPA 821-B-05-005.  Washington, DC.  (August). 
Available online at:   HYPERLINK
"http://www.epa.gov/waterscience/guide/304m/report_steam_electric.pdf" 
http://www.epa.gov/waterscience/guide/304m/report_steam_electric.pdf . 
DCN 02176.

U.S. EPA.  2006a. Detailed Study Report for the Steam Electric Power
Generating Point Source Category.  EPA-821-R-06-015.  Washington, DC. 
(November).  DCN 03401.  

U.S. EPA.  2006b. PCSLoads2002_v4 Database.  Washington, DC. 
(September).  DCN 03654.  

U.S. EPA.  2006c. SteamConcentrations2002 Database.  Washington, DC. 
(October).  DCN 03939.

 Envirofacts located on the internet at the following address:
http://www.epa.gov/enviro/

Memorandum

27 November 2006

Page   PAGE  25