SE Coastal Network Water Quality Inventory Monitoring

SE Coastal Network Water Quality Inventory & Monitoring Program Database Development Wade Sheldon & John Carpenter Dept. of Marine Sciences University of Georgia

Project Background n NPS identified need to compile information on long-term monitoring of water quality in SE coastal waters n In Fall 2007 we proposed to: Ø Ø Conduct workshop to gather input, info on regional activities Compile database of long-term monitoring program metadata Produce report summarizing monitoring in region Develop interactive web applications for querying database

Database Design Process - Research n Reviewed other environmental monitoring database efforts Ø International: GTOS TEMS (http: //www. fao. org/gtos/tems/) Ø Regional: Gulf of Maine Council on the Marine Environment (http: //restoration. gulfofmaine. org/) Ø OOS, regional consortia (SECOORA), … n Reviewed monitoring program/observation metadata standards Ø MMI (http: //marinemetadata. org/) – various schemas, drafts Ø EPA STORET (parameters) – legacy metadata Ø NEIEN (EPA/States/Tribes) § Environmental Data Standards Council (2006 standards/schemas) § Water Quality Exchange (WQX) – STORET replacement Ø NWQMC - Water Quality Data Elements

Database Design Process - Evaluation n Concluded huge amount of work going on to standardize and coordinate metadata/data exchange Ø Regional consortia, EON/IOOS Ø New NSF programs (INTEROP, FEON) n Differences in approaches, progress Ø Detailed schemas & standards -> basic metadata content recommendations without implementation tools -> plans in development Ø Some controlled vocabularies, but not widely applied n Common threads Ø Geospatial query/visualization (Google Maps API common) Ø Thematic searches at varying granularity (topics, programs, parameter types)

Database Design Process - Approach n Decide on initial scope Ø NPS goal: Water Quality monitoring in SE coastal zone (NC, SC, GA, FL) Ø Geographic bounds Ø Initial monitoring programs to target n Design, prototype relational database to support thematic and geospatial queries for targeted content Ø Support relevant metadata content standards (EDSC, NWQMC) Ø Flexibility for adding new metadata fields, varying completeness Ø Support varying granularity in searches (broad-to-narrow), hierarchies

Geographic Scope: Defining the Coastal Zone

Geographic Scope: Defining the Coastal Zone

Initial Monitoring Programs n USGS National Water Information System Ø Large amount of relevant, long-term data (water quality, streamflow, groundwater) Ø Excellent support for data mining (XML reports, RDB data) Ø Use STORET parameter codes (basic groupings, units, methods) n NOAA National Estuarine Research Reserve SWMP Ø Relevant, medium-long-term data Ø Support for mining stations, parameters via SOAP n EPA STORET Ø Problem: many sites without locations, complete metadata n “In house” programs (GCE-LTER, NPS)

Prototype Relational Database n Draft completed in May 2008, implemented in SQL Server 2000 n Basic structure: Research Projects Monitoring Programs Monitoring Stations/Sites Parameters/Methods n Scalable metadata model used for projects, monitoring programs Ø Some fixed fields (title, desc. , dates, web url) Ø Additional editable lists of available fields with web display options n Developed administrative forms (MS Access) n Prototype web interfaces under development (demo’d here)

Administrative Forms – Metadata Fields

Administrative Forms – Projects

Web Forms Interface

Next Steps n Gather workshop feedback on: Ø Ø Overall approach Metadata content to survey Queries, output formats to support Controlled vocabularies, hierarchies for parameters n Complete web interfaces for interactive input, queries, map display n Solicit metadata from other groups n Continue to develop automated ingestion approaches Ø XML/XSLT/SOAP Ø Spreadsheet templates n Prepare NPS report

Map Visualizations – Draft Google Earth Display

Spatial Query – Google Maps & Place Names

Web Access n This workshop: Ø Router: SSID = GCRC (passphrase = Water Quality) Ø http: //192. 168. 1. 100/wqmeta/ § Register or username = nps_workshop, pw = nps_workshop n After the workshop: Ø http: //www. gcrc. uga. edu/wqmeta/ n Background materials: Ø Database design (ERD) Ø Candidate parameter list, hierarchy Ø GIS resources for coastal zone determination