United States
Agency
Office of Municipal
Pollution Control (WH 595)
Washington DC 20460
October
1987
Municipal Facility/Waterbody
Computerized Information
An Introduction
800F87100
Snte of Wiscons
\
-------
-------
Environmental Protection Agency collects a wide assortment of
waterbody and facility data which are compiled into numerous computer
data bases. Many data bases are maintained on the EPA IBM 3090
mainframe computer at the National Computer Center (NCC) in
Raleigh, North Carolina. These data bases have proven to be very useful
within their founding program areas, and may also be useful to other
program areas within EPA Headquarters and Regions, to state offices,
and others. Applications of the data have tremendous potential,
especially when the data are combined, for use in managing, planning,
tracking, analyzing, and reporting. To help perform these tasks, some
offices at EPA Headquarters have developed computer software
packages which allow non-programmer's to easily access and retrieve
data, perform analyses and produce reports.
The purpose of this document is to introduce interested EPA and state
users to some of the data and data management tools that are available
for municipal facility/waterbody applications. Part I discusses methods
of using data from EPA mainframe data bases; describes the
development of user capabilities, and how those capabilities are being
improved; and explains what is needed to use the data bases that are
available. Part II is a descriptive inventory of key data bases available.
And Part III is a descriptive inventory of key software available,
including samples of output generated using these tools.
GLMTOl&iay Goaedm 0>L-l£i
77 West Jackson Boufaarf
60604-3590
-------
Parti
Gaining A
User's
Perspective
On
Municipal
Facility/
Waterbody
Data
The Office of Municipal Pollution Control (OMPC) and several other
offices at the EPA maintain a number of mainframe computer data
bases which contain a variety of municipal facility/ waterbody data on
pollution sources, hydrology, water quality and EPA water quality
programs. These data bases, which are illustrated below (Figure 1) and
described in Part III, can be used to conduct analyses and produce
reports. A chart of EPA Headquarters contacts for more information is
provided in Figure 2 for quick reference.
Partial
Access
Reach
Char.
File
Modeling
Parameters
PCS
NPDES
Data
Needs
File
POTW
Data
GICS
Grants Info
IFD
File
60,000
NPDES
Discharges
STORET
Water Quality
File
Monitoring
Data
Reach
File
atalogs Major
Waterbodies
Gage
File
Streamflow
Data
Office of Water
Software
Database Management
Analytical Software -
Individual Files
Integrated Database
Analysis Software
' /\ 305bData /
Drinking
Water
File
Intakes,
Figure 1. Overview of Municipal Facility/Waterbody Data Bases
1-1
------- An error occurred while trying to OCR this image.
-------
Using the Data
In order to use the municipal facility/waterbody data in EPA mainframe
data bases, interested users will want ways to:
retrieve data from the data bases,
organize, combine and analyze the data,
display the data and/or produce reports.
In most cases, users will need computer programs to perform these tasks.
A variety of pre-programmed procedures are available from OMPC and
other offices (see Part III) to provide interested users with simple tools
for using the data bases. Commercial "canned" programs may also be
used to perform some data processing tasks, and programs may be
written as they are needed (ad hoc) for specialized applications. Pre-
programmed "user friendly" procedures may require more time and skill
to develop than ad hoc programs, but once they are operational almost
anyone can use them and they can be used repeatedly.
Some uses of EPA municipal facility/waterbody data may require data
from only one data base at a time, while others may require that data
from several different data bases be combined (integrated). To be
capable of being integrated, data bases must:
contain data which can be meaningfully compared (e.g., facility data
from data bases being integrated are for the same group of facilities),
share common "linking" data elements (e.g., the facility identifiers in a
data base are the same as those used in other data bases).
The most basic method of using data bases in combination is to retrieve
the data using computer programs and then combine, analyze and report
the data manually. However, this method is only practical for small
amounts of data. Alternatively, the processes of combining, analyzing
and reporting data may be computerized. There are two methods of
integrating data by computer:
1. Data may be "physically integrated" using computer programs which
retrieve data from various data bases and copy them into a new,
independent data file. Data from the new data file may then be
analyzed and/or reported using a commercial software package or by
customized software. This approach is most useful for short-term or
specialized analyses that will not be repeated frequently. Its major
disadvantage is that, if both the original data bases and the new data
base are maintained, the data bases must be reconciled periodically,
and problems with data quality and consistency are likely.
1-3
-------
2. Data may be "logically integrated" using computer programs which
retrieve, analyze and report data from various data bases
simultaneously in "real time". This is a more sophisticated one-step
process which usually requires that computer programs be custom-
written because commercial packages generally are not appropriate.
Logical integration is the most efficient approach for applications that
will be run frequently using data from large, dynamic, national data
bases because the data being processed come directly from the
national data bases and are the most up-to-date data available.
Logical integration is used in many of the pre-programmed
procedures listed in Part III.
Development of User Capabilities
The municipal facility/waterbody data bases have been widely used by
EPA Headquarters and Regions, and the States for managing, tracking
and analyzing individual program areas. For example, GICS data have
been used extensively by states and EPA for tracking construction grants
expenditures. Several pre-programmed procedures have been available
for retrieving, analyzing and reporting data from individual data bases,
but there has been little computer integration capability available for
general use.
Computer integration of municipal facility/waterbody data bases has
been done by OMPC and others at EPA Headquarters for several years.
Physical integration and logical integration have been used, often on an
ad hoc basis, to assist in specific environmental analyses and to respond
to inquiries about program accountability.
The Reach File forms the basis for integrating municipal
facility/waterbody data bases by allowing municipal and industrial
discharges, stream gages, water quality stations, and drinking water
intakes to be located geographically on stream segments. Water quality
standards, some stream modeling parameters and State 305(b) report
data may also be linked to streams through the Reach File.
The experiences of OMPC and others at EPA Headquarters in the area
of data integration have indicated that logical integration is the best
approach for developing fast and easy pre-programmed procedures using
these national data bases because logical integration accesses the
program area data bases directly. Until recently, there have only been a
few pre-programmed procedures which have allowed users to logically
integrate data. In the past year OMPC and others have been working
toward improving logical data integration capabilities, not only for EPA
Headquarters, but for EPA Regions and especially for the States. These
efforts have included data quality improvement and software
development.
Several pieces of software have been implemented, and more are being
developed, to facilitate the general use and logical data integration
potential of municipal facility/waterbody data bases. These procedures
allow interactive updating capabilities, data retrieval, analysis, reporting
and mapping for several data bases.
1-4
-------
Improving Capabilities
Efforts to improve the usefulness of municipal facility/waterbody data,
especially in the area of logical integration, are continuing through
various projects at EPA Headquarters which are currently underway or
planned:
Improving location data for POTW dicharges and STORET water
quality stations.
Developing and/or enhancing the Water Quality Standards,
Waterbody, and Needs Survey data bases.
Updating and expanding the Reach File.
Ensuring the integrity of the linkages between data bases.
Enhancement of existing queries and analytical software to provide
access to more data bases.
Development of additional analytical procedures for comparing
discharges, water quality, standards, and other data with a major
emphasis on graphics and mapping capabilities.
State personnel can help to improve the user capabilites of municipal
facility/waterbody data bases by providing EPA with comments and
ideas. Because state users are the intended beneficiaries of these
capabilities, their comments and ideas are helpful in improving existing
data bases and software and developing new software. State personnel
are more familiar with the data pertaining to their states and can more
easily identify and correct data quality problems, which will lead to
better linkages between data bases and more accurate results.
1-5
-------
Requirements
Access to EPA mainframe data bases. This requires a TSO account
and user identification which must be obtained from the National
Computer Center (FTS 629-7862 or 919-541-7862).
Computer programs to retrieve, analyze and display data. Software
capable of performing logical integration usually must be custom
software written by skilled programmers. Commercially available
software packages generally are capable of physical integration but not
logical integration. Software can be designed for any level of user
expertise, and may use full-screen or line-by-line mode.
Computer hardware capable of communicating with the EPA
mainframe. The basic requirements are some type of computer
terminal and telecommunications (modem or direct line), but
requirements may vary according to the type of software being used.
For example, some applications require equipment with full-screen
capabilities, such as IBM 3270 hardware or a terminal that can
emulate a 3270 (e.g., a VT100 or a PC with communications software)
using a 1200 baud (or greater) modem. Graphics applications may
require specific graphics hardware. Users should refer to the contacts
listed in the software inventory for specific requirements.
Information
For more information about this document contact Elaine Greening or
Brian Thompson (WH595) at (FTS/202) 382-7251.
1-6
-------
DataBase Page
Data ReachF"e ""'
B9 *56 Reach Characteristics File II-2
IHV6lltOry Needs Survey File II-3
Grants Information Control System (GICS) II-4
Permits Compliance System (PCS) II-5
Industrial Facilities Discharge File (IFD) 11-6
Water Quality Standards File II-7
305(b) Waterbody System II-8
STORET Water Quality File II-9
Gage File 11-10
Drinking Water Supply File II-l 1
STORET Nonpoint Source Stream Station File 11-12
-------
Reach Description
File
The Reach File is a digital data base of streams, lakes, reservoirs, and
estuaries divided into segments called "reaches". Reaches may be divided
into subreaches. Each of the 68,000 reaches included in the file is
uniquely identified by an eleven-digit reach number. The reach numbers
are closely controlled, and once a number has been assigned to a reach,
neither the assignment nor the reach number will be changed regardless
of whether the reach is subdivided. Existing reaches become subdivided
where new tributaries are added to the file. Subreaches are defined by
reach milepoints in a standard manner described in the Reach File
Manual. Watersheds and waterbodies are defined by reach milepoints
using similar notation.
The data available from the file include stream names, open-water
names, stream and shoreline traces, and mileage information. Reaches
are referenced to each other in a special manner which makes it possible
to traverse upstream and downstream through the nation's rivers and
open waters while scanning other data bases for any reach-indexed data
along the traversal path. This is the foundation of EPA's ability to
integrate data from other data bases in hydrological order and in
common by river mile relationships.
Data Collection/ Update
EPA Headquarters is adding new reaches to increase the utility of the
Reach File for data integration and water quality analyses. Software is
being tested which will allow authorized and trained users to update the
Reach File including addition of new reaches, revision of stream names,
etc.
Access
The software for performing Reach File traversals in concert with other
data bases has been adapted to the IMS data base management system
by the STORET office, the Water Quality Analysis Branch (WQAB),
and other EPA offices. The WQAB software provides extensive
opportunities to store, retrieve, analyze, and interact with several EPA
water-related data bases. STORET procedures can be used to retrieve
water quality monitoring data by reach index and to plot reach maps.
Programs are also being developed which will enable users to add
reaches, and to make alphabetical, hydrological and numerical listings of
reaches.
Linking Data Elements
Reach numbers and reach mileages allow the Reach File to be linked to
numerous other EPA water-related data bases including IFD, the Needs
Survey and the Water Quality File.
Contact: C. Robert Horn (WH-585)
OWRS, Criteria and Standards Division, Nonpoint Sources
Branch (FTS/202) 382-7103
II-l
-------
Reach
Characteristics
File
Description
The Reach Characteristics File contains low and mean flow, and
hydraulic and kinetic modeling data for the 50,000 routable reaches in
the Reach File. Data elements include upstream and downstream
elevation, mean slope, mean temperature, mean pH and estimated ranges
for mean width, depth, velocity, CBOD decay rate and reaeration rate.
There are approximately 50,000 records in the Reach Characteristics File
containing 26 data elements per record. Data in the Reach
Characteristics File are organized by reach number.
Data Collection/ Update
The data were assembled as part of a national water quality modeling
effort conducted during the 1982 and 1984 Needs Surveys. Elevation,
slope and flow data were obtained from USGS, and temperature and pH
data from STORET. Other values were estimated using algorithms
which use flow and slope as input parameters. A user familiar with a
reach can make corrections or updates to the low-flow stream
characteristics using an on-line update system. Some reach charateristics
data are also contained in other "national" files, but those data will not
be affected by these local updates to the Reach Characteristics File.
Access
Data from the Reach Characteristics data base may be retrieved and
updated through an interactive procedure called the Reach Fact Sheet
Update System, which uses IHS data base management system software
to access the data base.
Linking Data Elements
The reach number allows linking to the IHS (IFD, Needs, Gage, Reach
files) and STORET files.
Contact: Elaine Greening (WH595)
OMPC, Municipal Facilities Division, Needs and Priorities
Branch (FTS/202) 382-725
11-2
-------
Needs
Survey
File
Description
The Needs Survey is an inventory of all existing or proposed publicly
owned treatment works which need construction to meet the
requirements of the Clean Water Act. These data form the basis for the
biennial Needs Survey Report to Congress which is due to be published
again in 1989. The data base includes construction cost estimates and
how they were documented, facility characteristics and location,
populations served by collection and treatment, flow capacity, effluent
characteristics, treatment processes and more.
The Needs Survey File contains final versions of the 1984 and 1986 data
bases, which are available to all users, and a pre-1988 data base (to be
updated for the 1988 survey) which is only available to authorized state
and EPA users. There are approximately 24,000 facility records in the
1986 data base and approximately 220 data elements per record. Data
are organized by facility within each state.
Data Collection/ Update
The data base was initially compiled through a survey of all POTWs
using paper (EPA-1) forms, and has been updated by state
representatives every two years for the Needs Survey Report to
Congress. Needs estimates and technical data are derived from various
state planning documents, and must be supported by the appropriate
documentation. During the 1988 survey, updates will be made by
authorized state users through an on-line update system and will be
reviewed by EPA.
Access
Data from the Needs Survey data base may be retrieved and updated
through an interactive procedure called the 1988 Needs Survey Review,
Update, Query System (RUQuS), which uses the IHS data base
management system software to access the data base.
Linking Data Elements
The Authority/Facility (A/F) number allows linkage to GICS; the
NPDES number to PCS and IFD; and the reach number to the Reach
File and Reach Characteristics File.
Contact: Lee Pasarew (WH595)
OMPC, Municipal Facilities Division, Needs and Priorities
Branch (FTS/202) 382-7251
11-3
-------
Grants
Information
Control
System
(GIGS)
Description
The Grants Information and Control System (GICS) data base is the
primary source of grants program and project information, including
wastewater treatment works construction grants, for managers at the
state, regional, and headquarters levels as well as for members of
Congress and the public. GlCS-generated data plays an essential role in
the planning, operation, management, and oversight of the multi-billion
dollar federal grant program, and makes possible the effective tracking
of all construction grant projects. The system contains administrative,
financial, technical and project status information on each EPA grant.
This information can be manipulated and displayed in a variety of ways
for informational, analytical, and management purposes.
There are 103,728 records of construction grants data in GICS with each
record size equaling 2,606 characters. There are currently 75 nationally
required data elements which include grant identification, applicant
information, awards and payments, technical data, and construction,
completion and closeout information. Data are organized primarily by
grant number within each state.
Data Collection/ Update
Updates are currently made to computer tape, but an interactive system
is being tested and will be implemented in the near future. States and/or
Regions update their data as grants are processed and data entry made.
Currently, each Region updates their file with new records and changes
to existing records. Twice a week, an update program at the mainframe
computer at NCC reads in the previous GICS master file from computer
tape and writes out a new master file with appropriate additions,
changes, and deletions received from States and Regions. A set of
updated sub-files are created from the master file for Regional use.
Access
The GICS data base currently exists both on computer tape and in the
ADABAS data base management system environment, however the tape
environment is to be phased out in favor of ADABAS. The tapes can be
accessed using the IRS and COBOL programming languages or through
various pre-programmed reports. The ADABAS version can be accessed
using any of several programming languages. Pre-programmed
procedures are currently being developed to access GICS using
ADABAS.
Linking Data Elements
The Authority/Facility number (TN-32) allows linkage to the NEEDS
Survey. The NPDES permit number (TN-C2) links GICS to the Permit
Compliance System (PCS). The grant number will also allow linkage to
the Financial Management System (FMS).
Contact: Sylvia Bell (WH547)
OMPC, Municipal Construction Division, Program
Management Branch (FTS/202) 382-5837
11-4
-------
Permits
Compliance
System
Description
The Permits Compliance System (PCS) is a computerized management
information system for tracking permit, compliance and enforcement
status data for the National Pollutant Discharge Elimination System
(NPDES) under the Clean Water Act. The PCS data base is the national
inventory for NPDES permit issuance and compliance/enforcement
data. The data base consists of more than 5 million data records on over
75,000 active water discharge permits issued nationally. The data are
organized by permit number, and each permit record contains many
items of information which:
identify and describe the facility to which the permit has been granted;
specify the pollutant discharge limits for that facility;
record the actual amounts of pollutants measured in its wastewater
discharge; and
track the facility's history of compliance with construction, pollutant
limits and reporting requirements.
Data Collection/Update
The data in the PCS data base were initially loaded by the EPA several
years ago. Currently, the data may be entered or edited by the Regions
and States using PCS on-line, by submitting batch jobs or by uploading
from a PC.
Access
The Permits Compliance System includes interactive data retrieval and
data entry software which use the ADABAS data base management
system software to access the data base. Access is currently restricted to
authorized users.
Linking Data Elements
The NPDES permit number allows linkage to IFD, the Needs Survey
and GICS.
Contact: Dela Ng (EN 338)
OWEP, Enforcement Division, Compliance Information and
Evaluation Branch (FTS/202) 475-8323
II-5
-------
Industrial
Facilities
Discharge
File
Description
The Industrial Facilities Discharge (IFD) File, containing 119,000
NPDES facilities, was designed and implemented for the specific
purpose of providing the Monitoring and Data Support Division of the
Office of Water Regulations and Standards with a comprehensive data
base of industrial and municipal point source dischargers to surface
waters in the United States. The data base includes general information
about each facility including discharge and location information for
direct and indirect point source dischargers, standard industrial
classification (SIC) codes, and categorization of process and discharge
type.
Data Collection/Update
The Permit Compliance System (PCS) was used to identify NPDES
permitted facilities to be included in the IFD file. NPDES permits at
EPA Regional offices were used to provide general information, and
various state and local agencies provided additional and more recent
information. The Needs Survey was used to add information on existing
Publicly Owned Treatment Works (POTWs). Updates are made by EPA
Headquarters as needed to update, correct and add new facilities and
location coding.
Access
Facility reports can be generated using template programs that access the
data base using the IMS data base management system software. Many
conversational programs access IFD and use data for logical data
integration. IFD data are also accessed by STORET batch programs
using Reach, HYDRO and IFD keywords.
Linking Data Elements
NPDES number, reach number, and SIC codes are the most frequently
used data elements for linking to other environmental data.
Contact: Phillip Taylor (WH553)
OWRS, Monitoring and Support Div., Water Quality Analysis
Branch (FTS/202) 382-7046
II-6
-------
Water
Quality
Standards
File
Description
The Standards data base is designed to allow states to store (1) lists of
criteria, (2) lists of designated uses, and (3) reach assignments for each
use. Criteria are defined using a series of data elements, including a
STORET parameter code number, an upper and/or lower limit, and
several others. Each designated use record consists of a short use name, a
long use name, a set of EPA generic use codes, and a set of criteria
numbers. Each designated use may be assigned to the appropriate
waterbodies in the state. Where necessary, general criteria may be
replaced by site-specific criteria to accommodate local variation. The
data are organized by state and by reach within states. Each standard
may be defined by up to 40 data elements.
Data Collection/ Update
Because this data base is designed to be a repository for state data, the
States will be responsible for entering the data. The data base presently
has no user community, however, it is anticipated that when a software
system has been completed, several states may be interested in tracking
their water quality standards using this system.
Access
The Standards data base will be accessible through an interactive update
system, using the IHS data base managment system software, which is
currently being developed.
Linking Data Elements
The reach number allows linkage to the Reach File, for integration with
other data bases.
Contact: Phillip Taylor
OWRS, Monitoring and Support Div., Water Quality Analysis
Branch (FTS/202) 382-7046
11-7
-------
305(b)
Waterbody
System
Description
The 305(b) Waterbody System (WBS) is an inventory of all waterbodies
that have been assessed for water quality. The WBS will serve as the
basis for the biennial 305(b) report to Congress. It also serves as the
mechanism for managing the following Water Quality Act of 1987
requirements: lists of waters requiring control strategies; identification
and information for publicly-owned lakes; and identification and
information for waters affected by nonpoint sources.
The WBS data base is organized by waterbody, and once a waterbody is
established it remains fixed. Data related to a particular assessment (at
one point in time) repeat'for each new assessment. There are
approximately 25 data elements which contain information on the
identity and description of the waterbody, the designated uses, the water
quality status, how the status was determined, causes for poor quality,
sources of pollution, and planned activities. The Checklist File which
contains information on monitoring and wasteload allocation planned
activities from state grant applications will be merged into the system.
Data Collection/Update
States update the data base using the results of monitoring activities.
Updates are done either through an interactive on-line system or by
updating PC or other state system and transferring the data to the
mainframe file. Data are updated every 2 years, or more frequently if the
state desires.
Access
The Waterbody System includes interactive update and retrieval
programs which access the data base using the IHS data base
management system software.
Linking Data Elements
The reach number allows linkage to the Reach File, and the NPDES
number to IFD and PCS.
Contact: Bruce Newton (WH553)
OWRS, Monitoring and Data Support Division, Monitoring
Branch (FTS/202) 382-7056
II-8
-------
STORET
Water
Quality
File
Description
STORET is a computerized data base utility maintained by EPA for the
STOrage and RETrieval of parametric data pertaining to the quality of
the waterways within and contiguous to the United States. Since its
inception in the early 1960's, the original data base has evolved into a
comprehensive system capable of performing a broad range of reporting,
statistical analysis and graphics functions, while continuing to serve its
original role as the repository of parametric water quality data. The data
contained in STORET are collected, stored, and used by a variety of
Federal, State and local government agencies and their contractors. The
system is comprised of several individual but related files which contain
various types of information, including:
Geographic and other descriptive data about the sites where water
qualtiy data have been collected, referred to in STORET as "station"
data.
Data related to the physical characteristics and chemical constituents
of the water, fish tissue, or sediment sampled, referred to in STORET
as "parametric" data.
Information on municipal waste sources and disposal systems.
Data on pollution-caused fish kills.
Daily stream flow data.
Data Collection/ Update
Data in the STORET data base are collected, coded, and entered by the
State, EPA, and other federal agencies with monitoring programs. Data
are entered daily, and the data base is updated weekly. Data from the
USGS WATSTOR system are transferred to STORET on a regular
schedule.
Access
The STORET data base is accessed using numerous batch and
conversational procedures in the STORET environment which provide
reports, analysis, and graphics. An interface with the Statistical Analysis
System (SAS) provides extended analysis capabilities. Various
conversational procedures access the data base as a part of logical data
integration.
Linking Data Elements
Reach numbers allow linkage to other data bases for data integration.
Contact: Phillip Taylor (WH553) Philip Lindenstruth (PM 218B)
OWRS, Monitoring and STORET User Assistance
Support Div., (FTS/202) 382-7220
Water Quality Analysis Branch
(FTS/202) 382-7220
11-9
-------
Gage
File
Description
The Gage File contains information on approximately 36,000 stream
gaging locations throughout the United States. Information stored
includes location of gaging stations, types of data collected, frequency of
data collected, media in which data are stored, identification of the
collecting agency, and mean and annual flow and 7Q10 low flow where
available. The Gage File provides a common place for gage information
to assist those involved in activities such as water quality studies, waste
load allocations, dilution studies, and advanced waste treatment
assessments. Special data currently in this file are estimated mean and
low stream flows, and velocities for mean and low flow conditions for
each hydrologically connected stream segment.
Data Collection/ Update
During 1979, cataloging units and reach numbers were determined and
coded for the USGS gaging stations identified in the Basin
Characteristics File. These stations are considered to have the longest
periods of record of natural flow data (i.e., flow not affected by human
activities) and would therefore be valuable in water quality assessment
work. Updates are made by EPA Headquarters as needed to update and
correct location coding.
Access
Reports can be generated using template programs which use the IHS
data base management system software to access the data base. Several
conversational procedures access water supply data for logical data
integration. The data base can also be accessed by batch programs when
using Reach and HYDRO keywords.
Linking Data Elements
The Reach number is the primary linking data element used for data
integration with other environmental data bases.
Contact: Phillip Taylor (WH553)
OWRS, Monitoring and Support Div., Water Quality Analysis
Branch (FTS/202) 382-7046
11-10
-------
Drinking
Water
Supply
File
Description
The Drinking Water Supply File contains data on surface water supplies
across the nation, including locations of utilities, intakes and sources,
and the hydrologic cataloging unit numbers and reach numbers of their
receiving waters. The data base contains data on 824 utilities serving
communities with populations greater than 25,000, and 6,840 utilities
serving communities with populations less than 25,000.
Data Collection/ Update
During 1979 and 1980, data for surface water supplies were matched
with reach numbers from the Reach File. Data were derived from several
sources including the STORET system, Public Health Service data
bases, and EPA Office of Drinking Water data bases. Data on utilities
serving communities greater than 25,000 were derived from the Office of
Drinking Water's FRDS data base. Updates are made as needed by EPA
Headquarters to update, correct, and add new facilities and location
coding.
Access
Reports can be generated using template programs which use the IHS
data base management system software to access the data base. Several
conversational procedures access water supply data for logical data
integration. The data base can also be accessed by batch programs when
using Reach and HYDRO keywords.
Linking Data Elements
The Reach number is the primary linking data element for data
integration with other environmental data bases.
Contact: Phillip Taylor (WH553)
OWRS, Monitoring and Support Div., Water Quality Analysis
Branch (FTS/202) 382-7046
11-11
-------
STORE!
Non-Point
Source
Stream
Station
File
Description
The STORET Non-point Source Stream Station File (SSF) provides a
means for extracting information from STORET water quality data for
stream stations known to be impacted by non-point source (NFS)
pollution. The SSF was created in 1986 and contains STORET station
codes, state and county FIPS codes, river reach numbers,
latitude/longitude, and estimated relative contributions of NFS to the
wet weather and low flow loadings of nine general pollutant types. There
are approximately 700 stations from 25 states in the SSF, and 37 data
elements per station. Data are organized by stream station, but can be
accessed by any variable in the SSF.
Data Collection/ Update
A census survey of all states was performed to create the data base.
States update the information in the SSF with assistance from the EPA
Regional NFS Coordinators. The Office of Non-point Sources enters the
data to computer files and submits listings to the States for final error
checking. Updates are done as needed. Update procedures will be
formalized when the data base is nearer completion.
Access
The SSF exists as flat files maintained on the NCC-IBM mainframe.
Template programs on the NCC-IBM mainframe computer can be
accessed for use in generating NFS station lists and/or retrieving NFS
data from STORET. The Statistical Analysis System (SAS) is used in
the template programs.
Linking Data Elements
The STORET Agency Primary Station Code allows linkage to
STORET; the river reach number to the Reach File; and State and
County FIPS codes to many other files.
Contact: Steve Dressing (WH585)
OWRS, Criteria and Standards Division, Nonpoint Sources
Branch (FTS/202) 382-7110
11-12
-------
PART
SOFTWARE
INVENTORY
Software
OMPC Review, Update, Query System (RUQuS)
Page
III-l
Needs Survey III-l
-Short Query (Needread) III-2
-General Query III-3
Reach Factsheet III-4
Needs/GICS/PCS III-5
WQAB Procedures III-6
Help.
III-6
ASIWPCA III-6
PCS/STORET Interface III-6
Sitehelp III-7
Browse III-8
Pathscan III-9
Interactive STORET Retrieval III-9
RCHDAT III-9
Flow
III-9
IFDPLOT 111-10
DFLOW 111-10
City
111-10
Routing and Graphical Display System (RGDS) III-l 1
-------
OMPC
Review,
Update,
Query
System
(RUQuS)
OMPC has developed several interactive data entry and retrieval programs,
primarily associated with the Needs Survey File, which are grouped under a
management system called the Review, Update and Query System (RUQuS).
RUQuS is implemented under full-screen TSO on the NCC-IBM and uses
IHS software to directly access the data bases. All of the procedures in
RUQuS are full-screen applications, except General Query which is a line-by-
line application. A general Help function will be available by the start of the
1988 Needs Survey. RUQuS is expected to contain additional functions in the
future.
RUQuS procedures require a terminal with full-screen capabilities that can
communicate with the EPA mainframe at NCC. An IBM 3270 or any
terminal operating in 3270 mode can be used. Most full-screen terminals can
be used if they have a modem link to the NCC-IBM operating at a baud rate
of 1200 or greater.
Needs Survey
Several procedures are available under the Needs Survey option. Retrieval
procedures allow users to access facility data from the 1984, 1986 and 1988
Needs Survey data bases. Until the 1988 Needs Survey is finalized only
authorized state and EPA users will be able to access the 1988 data base for
update and review, and state users will be restricted to their state's data. A
user's guide will be available by the start of the 1988 Needs Survey.
Contact: Lee Pasarew (WH595)
OMPC, Municipal Facilities Division, Needs and
Priorities Branch
(FTS/202) 382-7251
III-l
-------
Short Query (Needread)
Short Query is a full-screen procedure which allows the user to review,
update or make inquiries of information on any POTW in the Needs
Survey data base. Nine output screens display data such as:
facility name and location
present and projected future treatment levels
current and year 2005 needs estimates
documentation types
reach number
NPDES number
present and projected future population data
present and projected future flows and effluent concentrations
treatment processes and projected changes
state-controlled data
FACILITY HEEDS DATA (SCREEN F) MO PREVIOUS UPDATES
COMMAND ===>
Next Screen => P
A/F Number : 367002001 NPDES Number => NY0024414
Facility Name => BING JOHN CITY JOINT STP Facility Status - 7A ==> 1
Authority Name => B-J CITY JOINT SEUAGE BD Nature Present - 7B ==> 3
City Name => BINGHAMTON Nature Projected - 7C ==> 3
County Name => BROOME Facility Change - 7D ==> 2
County Number => 36 7 Effluent Present - 24A => 4
Effluent Future - 24B => 5
Grant/Loan Deduction ===> ===> ===> ===>
Grant Numbers EPA Needs Estimatesdn Thousands of Dollars)
Category Design Year Current Doc Type
1 ===> 8885 ===> 8885 ===> 20
2 ===> 2962 ===> 2962 ===> 20
3A ===> ===> ===>
3B ===> ===> ===>
4A ===> ===> ===>
4B ===> ===> ===>
5 ===> ===> ===>
Example 1. Short Query (Needread) Facility Needs Data Screen
III-2
-------
General Query
General Query is a powerful analytical tool which allows the user to
review, update and make inquiries of the Needs Survey File on any
selected set of POTW's and to generate summary reports. General Query
has an on-line data dictionary containing approximately 220 data
elements which form the basis for creating a query. The procedure
retrieves data using user-specified selection criteria, and displays the
information in a user-specified or standard format. General Query will
be extremely useful for answering a variety of questions concerning
publicly owned municipal treatment works, and for state water quality
analysis and planning. It is potentially even more powerful because it can
be modified to access other EPA data bases in addition to the Needs
Survey.
MY OPERATIONAL POTUS INELIGIBLE MEEDS (IM $1000) SERUIMG GT 100,000 PEOPLE
FACNAME
CITYNAM
F NEDELIG PPRRT
CEDAR CREEK UPC PLANT
BAY PARK UPC PLANT
OAKUOOD BEACH UPCP
NEUTOUN CREEK UPCP
OULS HEAD UPCP
COMEY ISLAMD UPCP
TONAUANDA SD»2
FRAMK E UAM LARE UUTP
BIMG JOHM CITY JOIMT STP
OMEIDA CO UPCP
YOMKERS JOIMT STP
ROCK CO SD »1
ROCKAUAY UPCP
HEMPSTEAD
EAST ROCKAUAY
STATEM ISLAMD
QUEEMS
BROOKLYM
BROOKLYN
TOMAUAMDA
ROCHESTER
BINGHAMTON
UTICA
YOMKERS
ORAMGEBURG
ROCKAUAY
3
1
1
1
1
1
1
1
1
1
1
1
1
1
92768
112998
547343
176587
251719
232332
13794
7517
11847
20541
3711
30390
13969
401183
479200
152667
1051791
655896
624386
110406
441459
111329
120023
366461
118861
113624
1515516
13
=====
4747E86
13
Grand Totals ...
Example 2. General Query User-defined Report Including Totals
of Design Year Eligible Needs (NEDELIG) and
Resident Population Presently Receiving
Treatment (PPRRT).
1-3
-------
Reach Factsheet
The Reach Factsheet option in RUQuS allows the user to retrieve and
update low-flow modeling parameters for routable reaches contained in
the Reach Characteristics File, and to retrieve data pertaining to the
specified reach from other data bases. Modeling parameters such as
slope, velocity, width, depth, CBOD decay rate and reaeration rate can
be updated. Reach summary data can be retrieved from the Reach File,
as well as data on POTW and industrial facilities (Needs Survey and
IFD) and STORET stations (Water Quality File) located on the reach.
The data are displayed in a series of screens (Reach Summary, Low
Flow Characteristics, Industrial Facilities, Municipal Facilities,
STORET Stations) which the user calls up by entering an eleven-digit
reach number. Reach Factsheet has an extensive on-line Help facility,
but there is no written documentation at this time.
Contact: Elaine Greening (WH595)
OMPC, Municipal Facilitites Division, Needs and
Priorities Branch
(FTS/202) 382-7251
-REACH SUMMARY-
COMMAND
==>
Next Option ===> C
Reach:02850103018
End=Main Menu
Reach Home :SUSQUEHPNMfl R Reach Type :R
Length (mi) : 9.9 Subbasin Mo : 7
Stream Leu : 1 Mean Flow : 6406.89
CONNECTING REPCHES
UPSTREAM DOWNSTREAM
Reach Home rSUSQUEHANNA R Reach Name :SUSQUEHANNA
Reach No :8E050181081 Reach No :82058183888
Trib Name :CHENflNGO R Comp Name :CHOLOhUT CR
Trib No :02050102001 Comp No :02058183883
MUNICIPAL DATA EXISTING
No of Discharging POTLJs : 2
Total Flow to Reach (MGD) : 26.3
Total BOD Load to Reach (LB/DAY) : 59.0
INDUSTRIAL DATA
No of NPDES Industries : 2
No of Pipes : 11
Total Flow to Reach (mgd) : 6758.8
DESIGN NEEDS MET
0
0.0
0.0
STORET DATA
No of Ambient Stations
Assigned to Reach :
No of Observations :
9
6666
Example 3. Reach Factsheet Reach Summary Screen.
III-4
-------
Needs/GICS/PCS
This procedure is designed to combine data from GICS, the Needs
Survey, and PCS for tracking progress toward fulfilling construction
needs and achieving compliance with the National Municipal Policy.
Display screens are called up by entering a grant number or
authority/facility number. Construction grants awarded after December
31, 1985 are matched with facilities from the 1986 Needs Survey. The
procedure displays a summary of the grants awarded or on the FY87
priority list for the facility, and the needs and grant dollars by category
for comparison; facility information such as name, location and effluent
characteristics; and grant information such as the award date and eligible
amount. The PCS compliance data are not yet available except for states
in EPA Region V.
Contact: Elaine Greening (WH595)
OMPC, Municipal Facilities Division, Needs and
Priorities Branch
(FTS/202) 382-7251
A/F(TN32) 367002001 B-J CITY JOINT SEUAGE BD FACILITY NEEDS AND GRANTS
NPDES NO.: NY0024414 PCS DATA NOT AUftlLABLE
EFF.: SECONDARY
EFF. BODCMG/L):
TOTAL FLOLJ(MGD):
--> AT
27.0 --> 30.0
19.1 EXISTING
FAC. CHANGE: UPGRADE
EFF. TSSCMG/L): 47.0 --> 30.0
18.3 DESIGN 18.3 FUTURE
COMPARISON OF ELIGIBLE COSTS IN NEEDS TO ELIGIBLE COSTS IN GICS FOR GRANTS
AUARDED AFTER 31 DEC 1985 AND ON FY87 PRIORITY LIST BY CATEGORY ($10003)
CAT I/Y0 II/Y1 IIIA/Y2 IIIB/Y3 IUA/Y4 IUB/Y5 U/Y6 TOTAL TN29
NEEDS 8885 2962 0 0 0 0 0 11847
GICS 14292 2500 90000 16792 16792
GRANTS AND PRIORITY LIST EXCERPTS ( * NOT COMPARED TO NEEDS86)
NUMBER AUDCCERT) CD&DT ELIG AMTCTN29) DESCRIPTION(TN20-NOT REQ'D)
36096114 A5 A 870811 11792 INSTALL IN-UESSEL SLUDGE COMPOSTING
36096116 AS T 900130 5000 STP-UP PH2
ENTER GRANT NO. FROM LIST, "SUM", "EXP", "SCHED",
',"NEIJ" OR "END":
Example 4. Needs/GICS/PCS Summary Screen
III-5
-------
WQAB
Procedures
The Water Quality Analysis Branch (WQAB) in OWRS supports several
data retrieval and mapping procedures on the NCC-IBM. These
procedures are designed to assist users in water-quality analysis tasks
using data from EPA water-related data bases. Most of the procedures
are line-by-line applications which can be run interactively in TSO; some
may also be run in batch for large jobs. These procedures can be run on
almost any graphics or non-graphics terminal that can communicate
with the NCC-IBM (e.g., a PC with a modem), but graphics hardware is
required to perform graphics functions. WQAB can provide more
information on the capabilities of these procedures and how to use them.
Contact: Phillip Taylor (WH553)
OWRS, Monitoring and Support Div., Water Quality
Analysis Branch
(FTS/202) 382-7046
Help
This procedure provides instructions for using WQAB conversational
procedures and enables the user to keep abreast of new procedures and
important changes and updates.
ASIWPCA
ASIWPCA is an interactive TSO procedure which reports information
compiled by the Association of State and Interstate Water Pollution
Control Administration (ASIWPCA) in their States' Evaluation of
Progress 1972-82 (STEP) and States' Nonpoint Source Assessment 1985
(NPS) reports. The STEP and NPS reports provide information on the
extent of stream use impairment due to point sources and nonpoint
sources, respectively. Data from these reports have been stored in the
IHS WQAB Analysis file. The procedure reports the stream name,
length of stream, reach number, reach type and the extent that stream
uses were impaired by point and nonpoint sources. This interactive
procedure reports on one use at a time, and a batch procedure is
available which can produce a report on a State or multiple reaches.
PCS/STORET Interface
The PCS/STORET Interface allows the user to analyze, summarize and
report data from permitted facilities in PCS and from water quality
stations in STORET, as well as stream flows, downstream water
supplies, bioassay results and indirect discharges to POTWs from other
files. This procedure is currently a pilot project which will be fully
implemented in early FY88. The user may produce tables, graphs and
maps by selecting from a menu of standard reports or by using other
(STORET) procedures to process PCS data. Interactive graphics
capabilities are available using the STORET Browse procedure, and a
wide variety of analysis and mapping options are available when using
STORET in batch.
111-6
-------
Sitehelp
Sitehelp is a conversational TSO procedure which allows a user with
graphics or non-graphics terminals quick access to IFD, Gage, Water
Supply, City, CETIS, Reach Structure, Reach Traces and STORET
Water Quality Files. Inquiries can be made by use of USGS gage
number, Water Supply FRDS number, NPDES number, Dunn and
Bradstreet number, Needs Survey A/ F number, city name, or stream
reach number. Various options in Sitehelp provide reach data such as
location, length, flows and velocities; and summary data on locations of
gages, industrial and municipal dischargers, drinking water intakes,
surface water and groundwater monitoring stations. On graphics
terminals, the procedure displays a reach trace with locations of gages,
dischargers, drinking water intakes, monitoring stations and cities.
Additional data and more complete formats can easily be obtained by
using other WQAB TSO procedures such as RCHDAT and
IHSBRWSE, STORET and IHS batch programs, or the STORET
browse procedure.
T>»IKIT» R
:2838ies8isi8i LEN 15.15 LR '.ae
*TP£ R L£VEL 1 THUS
DOWNSTREAM: 12*31105814 COMPLEMENT 12838185824
1*>LEFT: 123381*5823 UPRIBHT: 12838185816
SCALE 1:64688 DATE 86*482 TIME 808817
MONITORING STATIONS
1 21TEXVR IMSS7-58S BELT LINE ROAD
2 ZI'EWR 88*58288 TRINITY R. AT BE.T LINE R5 E
3 21TXW08 88*58288 TRINTv R I BEL* LINE RD E OC
4 21TXTDH TXD*S3t286S8W»' MOORE INDUSTRIAL DISPOSAL 21
5 2ITXTDM TXM53128658MH2 KOORE INDUSTR;*. DISPOSAL 21
E 21TX-D* TXD«53l28E5anw3 WOKE INDUSTRIAL DISPOSAL 21
7 ZITXTDU TxpesatTeesamM MOORE INDUSTRIAL DISPOSAL 21
Example 5. Sitehelp Listing and Display of Monitoring Stations
on the Trinity River
III-7
-------
STORET Browse
Browse is an interactive procedure which allows the user to easily and
quickly retrieve STORET water quality monitoring station data. It can
be executed using either graphics or non-graphics terminals. Data from
an individual station of interest can be retrieved by specifying the
STORET agency and station codes. Browse will provide facility
information and a summary of the data available for all parameters or
just selected parameters. The data for selected parameters may be
displayed in more detail and graphed in time-series plots. The procedure
is being adapted to access the facility monitoring data from PCS, but
access to that data is currently restricted to authorized users.
32 37 M.« IBS 37 15.6 4
TWINTY R BELT LI* RO E Of VILHER
481 I 3 TEXAS DALLAS
VESTENN GULF I2*3M
TRINITY I SAN JACINTO
2ITXMB I2*3(1K«1S (MS.ee* ON /TYPA/AWNT /STREAK
WCI4 DEPTH
INDEX
MILES
STORET
CI* W43*NH4- S TOTAL
NOBS
94
AVE NAX
S.2S4 13.*M
HIN K6-OATE EIC-OATE
I. 13* 7C/1I/M 84/11/18
Slop* a' R»gr»»«10T> LI to "*'9S U*lt« »» Y««r.
878 '078 iose laei toe; aei
B76 '87
Example 6. Browse Time-series Plot of NH3 x NH4 in the
Trinity River
III-8
-------
Pathscan
Pathscan is an interactive TSO procedure which allows the user to
retrieve data from NPDES and drinking water facilities upstream or
downstream from a specified location. The request can be restricted by
miles or stream levels, and the specified location can be pointed to by a
stream reach number, NPDES number, pipe number, Dunn &
Bradstreet number, Needs facility number, gaging station number, or
Drinking Water FRDS, plant, intake, or source numbers. The procedure
provides a summary report and an optional report showing mile, stream
reach, stream name, level, length in miles, stream flow, type of location
(drink, pipe or gage) and information about the location. Pathscan is
limited to 1,000 locations, but larger requests can be made using a batch
procedure called HYDRO.
Interactive STORET Retrieval (ISR)
ISR is an interactive TSO procedure which allows a user without
knowledge of STORET to make quick and easy retrievals from the
STORET Water Quality File using a NPDES number or reach number.
Three tabular reports may be printed on a NCC remote printer and a
reach location map may be plotted at NCC and mailed to the user.
RCHDAT
RCHDAT is an interactive TSO procedure which provides streamflow
and pipe discharge data for a user specified stream reach. This procedure
is adaptable so that the user can request specific flow units, or report
various combinations of data. For stream gages, the Gage ID, miles on
the reach, site name, on/off code, method, drainage area, mean flow,
low flow and flow units are displayed. For pipes, up to four flows are
shown: IFD, and the current BPT and BAT flows used by the WQAB.
The summary table shows, for each treatment level, the discharge flow in
each group as a percentage of 1) total flow in all group pipes and 2) total
discharge by all IFD pipes to the water body. When a NPDES number
is provided, the procedure lists the facility, discharge flow, stream name,
segment number code and mileage.
Flow
WQAB FLOW is an interactive procedure which allows the user to
obtain flow data for USGS gaging stations located in the Gage File. The
procedure will provide daily flows (CFS) or statistics to determine the
7Q10 low flow when the user enters a gage number, state and begin/end
years. Flow data can be saved in a file.
III-9
-------
IFDPLOT
IFDPLOT is an interactive procedure which allows the user to map
NPDES facilities in IFD for the whole country, an EPA region, a state
or a cataloging unit. The user can specify facilities or individual pipes to
be mapped or can map facilities by SIC code. Scales, symbols and legend
may be done automatically or may be user-specified.
DFLOW
This procedure enables the user to set up a batch retrieval of flow data
from STORET, and to perform calculations using retrieved data
interactively in TSO. The procedure calculates a biologically-based
facility design based on a method developed by EPA in determining
water quality-based pollution controls.
City
City is an interactive TSO procedure that allows the user to obtain
information from the City Master File in IHS which may be helpful for
other WQAB procedures. Given a city name, the procedure provides
latitude, longitude, stream names, reach numbers, census population and
FIPS State-County numbers.
111-10
-------
Routing
And
Graphical
Display
System
(RGDS)
RGDS is a data base centered water quality model combined with
graphical display capabilities which operates in the STORET environment.
Input data for the model are derived from the Reach File and IFD, and
include data on pipe discharge flows and concentrations, stream flows and
velocities, permit numbers, stream names, mile points and stream map
data. The system is designed for state-wide and basin-wide analyses of
dischargers and water bodies to help in screening water quality problems.
It has been limited to loading, mixing, diluting, decaying, and transporting
pollutants and calculating instream concentrations for broad-based
analyses without the refinements and complexities of reaeration,
hydrolysis, biolysis, photolysis, etc. RGDS provides many controls and
options for the user to build a variety of his or her own water quality
simulations to help separate major water quality, problems from those of
lesser concern through worst-case/ best-case scenarios and sensitivity
analysis. The graphical display capabilities provide concentraion profile
plots and plots of Reach File maps with reaches colored according to a
variety of user-specified parameters, including reach concentrations
computed by the water quality model. Tabular outputs show the results by
stream name, reach number, NPDES permit number and discharger
names.
RGDS is a batch program that can be submitted from the STORET
environment in TSO. Execution of the program requires a terminal which
can communicate with the NCC-IBM, and printing and plotting may be
done by any output device accessible by STORET users. The procedure is
documented in the Routing and Graphical Display System User's Manual
(68 pages with examples).
Contact: C. Robert Horn (WH585)
OWRS, Criteria and Standards Division, Nonpoint
Sources Branch
(FTS/202) 382-7103
III-ll
-------
U.S. Environmental ftatecliai
GLNPO Library Collection (PL-12*
77 West Jackson Boulevard
Chicago, II 60604-3590
------- |