AQUATIC GAP
Central Regional Database for Great Lakes Regional Aquatic GAP
Introduction
The Great Lakes Regional Aquatic GAP began as a regional project in 2001 with the objective of developing an aquatic gap analysis for riverine systems in all eight states in the Great Lakes region by 2009 (Myers et al. 2002). In addition, three pilot studies are under way in western Lake Erie, eastern Lake Ontario, and Lake St. Clair as part of the Great Lakes Coastal Pilot subproject. The goals of the Great Lakes Regional Aquatic GAP Project are (1) to evaluate the biological diversity of Great Lakes aquatic habitats and identify gaps in the distribution and protection of these species and their habitats; and (2) to use an integrated approach in which common methods and protocols are established and results are comparable across the Great Lakes landscape. An objective of the project is to produce a central database for Great Lakes Regional Aquatic GAP data. Development of this regionally consistent database and spatial data layers, with uniformity across state boundaries, is a major focus of the Great Lakes Regional Aquatic GAP Project (Stewart et al. 2004).
Data Sources and Types
Georeferenced biological data (catch, effort, and location) were contributed by state and federal agencies and academic institutions, which are collaborating in Great Lakes Regional Aquatic GAP (Table 1). Variability in the accuracy of latitude and longitude for fish sampling locations has presented a problem, and some collections could not be loaded into the central database. Some of these locations were manually corrected using other descriptive information, such as railroads, highway crossings, and other landmarks. Central database staff members have needed to achieve an understanding of the raw data and underlying structures to extract and load the data into the central database structure. Fish data (fish species and locations) have been reviewed by expert reviewers for each state to ensure quality assurance/quality control (QA/QC). QA/QC is conducted by the central database staff during the assessment and loading of the data into the database. The enduring features data have been obtained from federal (e.g., National Hydrography Data Set [NHD], National Elevation Data Set [NED], Hydrological Unit Code [HUC]), state (e.g., surficial geology, bedrock geology, and land cover), and academic institutions (PRISM climate data from Oregon State University). Collaborators at the Institute of Fisheries Research (Michigan) and the Wisconsin Department of Natural Resources are generating stream water-temperature data by modeling, using regression equations containing groundwater flow and other variables.
Fish life history and fish habitat affinity databases have been acquired from NatureServe and from Eakins (2005). These data are being integrated with data from the fisheries literature and entered into the habitat affinity component of the central database. The process of linking the fish sampling locations to the National Hydrography Data Set has been time-consuming, but habitat specialists in the Water Resources Discipline, U.S. Geological Survey, have used processing scripts (Arc Macro Language [AMLs]) to facilitate this step.
Data Structure and Standardization
The Central Regional Database for the Great Lakes Regional Aquatic GAP Project is centrally housed at the Great Lakes Science Center in Ann Arbor, Michigan. The database is built using Oracle® relational database software, with interfaces that utilize Oracle Discoverer and Oracle 9i web-enabled technology. The Great Lakes database was built with a common structure to ensure regional consistency for the three Great Lakes Aquatic GAP states. Data structure was based in part on the Missouri Aquatic GAP, the Ohio Aquatic GAP, and the Nature Conservancy database structures. The database has three major components: enduring features, biological catch and effort, and habitat affinity characteristics (Figures 1 and 2). The structure is flexible in that columns can be added to tables to describe further attributes, and tables can be added to model new entities. Standardization within each component is a major task. For instance, the Integrated Taxonomic Information System (ITIS) codification and naming system for fish species is used for standardization across the basin.
Figure 1. Database structure for the central regional database of the Great Lakes.
Figure 2. Structure of the enduring features component of the Central Regional Database.
Database Interface
Investigators in Great Lakes Regional Aquatic GAP access the data through Oracle Forms (Figure 3) and Oracle Discoverer (Figure 4), which are both accessed using a web browser. Data are flagged for data ownership, and ownership issues determine access to the data. Comprehensive query access to all tables and columns is provided through Oracle Forms and Discoverer interfaces. The Forms interface is an interactive data-entry system (Figure 3). Discoverer (Figure 4) permits point-and-click access to tables and columns. Subsets of data can be downloaded to a local system for further analysis through the Discoverer interface. Investigators in Great Lakes Regional Aquatic GAP are accessing the data to conduct modeling of fish-habitat interactions and produce fish distribution maps (Rosenfeld 2003; McKenna et al. forthcoming; and Steen et al. 2005). Collaborators in Great Lakes Regional Aquatic GAP access the data through a password-protected interface. In the future, we anticipate that data developed for this project will be used as the core information in a decision-support system for developing basin-wide freshwater biodiversity plans for the Great Lakes (Sowa et al. 2004).
Figure 3. Screen shot of the Great Lakes Regional Aquatic GAP web form.
Figure 4. Oracle Discoverer interface into the Great Lakes Aquatic GAP database.
Literature Cited
Eakins, R. J. 2005. Ontario Freshwater Fishes Life History Database, Version 2.67. Online database. URL: <http://www.afs-soc.org/fishdb/index.htm>.
McKenna, J. E., Jr., R. P. McDonald, C. Castiglione, S. Morrison, K. Kowalski, and D. R. Passino-Reader. Forthcoming. A process to model habitats and fish-environment relationships in support of Aquatic Gap Analysis in the Great Lakes. In Influences of landscape on stream habitat and biological communities, ed. R. Hughes, L. Wang, and P. Seelbach. American Fisheries Society, Bethesda, Md.
Myers, D. N., J. McKenna, D. Passino-Reader, and J. S. Stewart. 2002. Great Lakes Aquatic GAP Project. Gap Analysis Bulletin 11:33–37.
Rosenfeld, J. 2003. Assessing the habitat requirements of stream fishes: An overview and evaluation of different approaches. Transactions of the American Fisheries Society 132:953–68.
Sowa, S. P., G. M. Annis, D. D. Diamond, D. Figg, M. E. Morey, and T. Nigh. 2004. An overview of the data developed for the Missouri Aquatic GAP Project and an example of how it is being used for conservation planning. Gap Analysis Bulletin 12:7–19.
Steen, P. J., D. R. Passino-Reader, and M. J. Wiley. 2005. Modeling brook trout presence and absence from landscape variables using four different analytical methods. In Influences of landscape on stream habitat and biological communities, ed. R. Hughes, L. Wang, and P. Seelbach. American Fisheries Society, Bethesda, Md. In press.
Stewart, J. S., S. S. Aichele, D. R. Passino-Reader, J. E. McKenna, and S. A. Covert. 2004. Great Lakes Regional Aquatic GAP. Gap Analysis Bulletin 12:64–65.
