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