Improving Regional Water Quality Assessment Geodatabase improves data management and analysis capabilities By Jon Becker, U.S. Environmental Protection Agency Region 4 A geodatabase developed by the U.S. Environmental Protection Agency (EPA) Region 4 Water Management Division (WMD) manages georeferenced water quality assessment data from the region's eight southeastern states for various years far more efficiently than the previous system. In accordance with the Clean Water Act, each state environmental agency tracks the status of water quality for water bodies located within its boundaries. Every even-numbered year, an assessment is made of each water body to determine whether it is meeting its designated uses. Each state agency tracks the status assessments in a local database and generates GIS datasets that depict the locations of these water bodies. Each lake, estuary, or river reach is called an assessment unit (AU) and assigned a unique identifier. While the EPA suggests recommended formats for assessment data and complementary GIS data, states often deviate somewhat from these formats to meet local needs. Eight southeastern states submit assessment data and GIS files to EPA Region 4. These files are then forwarded on to EPA national headquarters for eventual input into the national Assessment and TMDL Tracking and Implementation System (ATTAINS). [The Total Maximum Daily Load (TMDL) program determines the safe level of loading for a pollutant.] The GIS files are addressed by river reach to the 1:100,000-scale National Hydrography Dataset (NHD) and incorporated into EPA's Watershed Assessment, Tracking and Environmental Results System (WATERS). This system integrates various EPA waterrelated databases via reach-addresses of the NHD. Over the years, the staff of EPA Region 4 have obtained numerous assessment databases and GIS datasets from its eight southeastern states that were furnished in assorted and incompatible shapefiles, tables, and Microsoft Access databases. Using these datasets to analyze water body status for more than one state or year was difficult and time consuming even for experienced GIS users. In 2007, EPA Region 4 staff explored the possibility of using a geodatabase to better manage water quality assessment data. Although 22 ArcUser Spring 2009 Users can use the Identify tool to click on a feature and view records about those features stored in related tables or activate the hyperlink to the EPA database record for the feature. data is eventually reformatted by contractors at EPA headquarters into consistent formats for inclusion in ATTAINS and WATERS, the obvious roadblock in creating a geodatabase for Region 4 was the variety of assessment data formats and GIS data used by the states. Region 4 contracted with Research Triangle Institute to develop a prototype geodatabase and populate it with reach-addressed versions of the states' assessment datasets. Region 4 staff then generated tables detailing the status of each AU from ATTAINS (then called the National Total Maximum Daily Load Tracking System). Relationship classes were created to relate the point, line, and polygon AU features to the assessment status tables using the AU identifier field. This was a one-to-many relationship because the AU is sometimes listed multiple times in the assessment status table if it has been tracked for several cycles and/or multiple water quality standard impairments (e.g., failures to meet swimming designated use because of high levels of pathogens). Although this geodatabase format functioned fine, Region 4 staff soon realized it might be advantageous to populate it with the original state of GIS shapefiles rather than reach-addressed versions. While many states use NHD as the basis for their assessment units, some have reasons for not doing this. For example, because Florida has mostly flat topography, and complex, highly modified hydrography is not well depicted at the NHD 1:100,000 scale, the Florida Department of Environmental Protection prefers to use its own water body identifiers (WBIDs) that depict small contributing drainage areas. If Region 4 staff are reviewing assessment data for Florida in a GIS, it is helpful to be able to view and use WBIDs instead of a reach-addressed approximation of those features. However, Region 4 recognizes the value of having all state assessment GIS data in a consistent NHD-based format and continues www.esri.com