![](media/image1.png){width="4.997916666666667in" height="0.75in"}

# **USGS WRD National Internal QW Database Pilot Project**

For Internal USGS Release Only

In July 2003 the National Water Quality Assessment (NAWQA) Database Team
completed the first phase of a pilot project to integrate 76 NWIS water
quality databases[^1] into a single national database. The first phase
of the pilot project was the following:

-   Feasibility of aggregating from many NWIS hosts in a weekly time
    step

-   Develop performance expectations for ad hoc queries with a limited
    number of concurrent users to check scalability of the NAWQA data
    warehouse model

-   Evaluation of commercial data integration software in both
    transferring data using direct database access and in applying a
    precedence scheme to collapse duplicate sites and samples across
    multiple NWIS systems

The purpose of this document is to highlight some of the features of the
pilot system. A word of caution: the pilot project was completed on
shared and/or surplus computer hardware, so performance estimates may
very well be significantly slower than what is possible in a production
environment. For additional information about the pilot project, please
contact Nate Booth or Sandy Williamson.

The database contains 1.3M sites, 3.8M samples and 61M results[^2]. The
data load pipeline contains both a lifecycle and NWIS system ownership
audit trail for sites, samples and results, between data aggregations.
If a site, sample, or result has been modified in an NWIS database since
the last aggregation, the record from the previous aggregation and the
new record are stored. All site, sample and result records are stored in
the pipeline so that different precedence schemes can be applied to
resolve duplicate keys. NWIS access is achieved using Open Database
Connectivity (ODBC).

The NAWQA defined precedence scheme has been implemented in this phase
that allows for all unique NWIS sample natural keys (agency, station,
begin and end date and medium) to exist, while picking one site or
sample record where that key is duplicated across NWIS databases.

In order to meet data refresh goals, the pilot team built two data
aggregation schemes, one for new and updated sites, samples and results;
the other for deleting purged sites, samples and results. The former can
be completed for the 76 databases in the test case in approximately 48
hours while the latter requires approximately 3 days.

The National Database pilot is in a star schema design and indexed for
data retrieval. For ease of ad hoc querying, all domain lookups have
been attempted, where practical, to provide end users with both NWIS
codes and their associated lookup text descriptions. Oracle data
warehousing index strategies have been applied and Oracle spatial
indexes have been created to allow for spatial analysis using SQL and
display using various GIS products.

Limited testing after basic database tuning indicates ad hoc query
performance to be good. Runtime estimates are given throughout this
document.

The pilot project uses an Oracle 9i database on Linux, Informatica
PowerMart data integration/aggregation software on Windows 2000 and a
bank of Oracle Discoverer ad hoc query servers on Windows 2000 (load
balanced using a mirrored set of Cisco Local Directors). All systems are
housed and maintained by the Database Development Team in the Wisconsin
District. Systems are firewalled as per DOI/USGS headquarters guidance
and database access is password protected.

Some screen shots are included in the following pages to demonstrate
some possibilities in querying the National database. Building queries
to produce rollups and computed statistics is possible with a few
keystrokes and result sets are returned oftentimes in fewer than five
minutes. The example queries demonstrate the following concepts: [nested
analytical functions](#_Example_1:_Simple); [atomic level data
retrieval](#_Example_2:_Atomic); [querying the audit
trail](#_Example_3:_Audit); [animal tissue data](#_Example_4:_Animal);
[sample accounting with drilldown](#_Example_5:_Sample); and [nested
spatial functionality](#_Example_6:_Spatial).

## Example 1: Simple statistics. For a list of constituents, find the min and max constituent concentrations for surface water samples that have the respective constituent detected. Also, count the number of detections and non-detections for the group of samples for which the constituent was analyzed.

![](media/image2.png){width="5.995138888888889in" height="5.91875in"}

In a standard Windows interface, the above entities are selected to be
displayed. Note metadata for the entities in the bottom of the wizard
screen. Domains are presented in drop-down pick lists.

![](media/image3.png){width="5.999305555555556in" height="4.28125in"}

Custom or predefined conditions can be applied.

![](media/image4.png){width="2.8722222222222222in"
height="1.2493055555555554in"}

The database collects statistics and "learns" performance estimates.

![](media/image5.png){width="5.997222222222222in"
height="5.444444444444445in"}

The query produced these results for Caffeine, filtered in surface water
with statistics by State, ordered by percent detections. Query completed
in about 2 minutes.

![](media/image6.png){width="4.622916666666667in"
height="3.2979166666666666in"}

By pivoting the elements in the layout wizard, an end user can easily
display the results in a new format.

![](media/image7.png){width="5.997222222222222in"
height="5.444444444444445in"}

The following output shows percent detections for 17-beta-Estradiol,
Caffeine, and Cholesterol in surface water by state, ordered by state.

## Example 2: Atomic level data retrieval. Atrazine in surface water at stations named like "DUCK CREEK" and in Wisconsin.

![](media/image8.png){width="4.752083333333333in" height="3.35625in"}

Wildcard condition chooses station names like "DUCK CREEK" and in
Wisconsin.

![](media/image9.png){width="5.997916666666667in"
height="5.254861111111111in"}

This query completed in 3 seconds.

## Example 3: Audit Trail. For the pilot project, there is not a Windows based interface for querying the audit trail. Using standard SQL, what hosts have sample keys duplicated across DBNUMs in the pilot?

![](media/image10.png){width="5.997916666666667in"
height="6.240277777777778in"}

There are 18 hosts with samples duplicated across DBNUMs. While the
staging database is not indexed for ad hoc query, this query still
completed in about 1 minute.

## Example 4: Animal Tissue Data. For Colorado, min, max, average and counts of tissue concentrations in trout for all constituents analyzed. 

![](media/image11.png){width="6.331944444444445in"
height="6.872222222222222in"}

Query completed in 20 seconds.

##  

## Example 5: Sample Accounting. Number of Orthophosphate and BOD samples by water year in Wisconsin since 1980. Drill into 1997 to counts by Station Names.

![](media/image12.png){width="5.995138888888889in"
height="6.752083333333333in"}

Query completed in 30 seconds.

![](media/image13.png){width="6.375in" height="6.138194444444444in"}

Drill to station level.

## Example 6: Spatial Analysis for planning a group of wells for a sampling effort. Considering Alachlor samples in water year 2002, find what well has a sample with an Alachlor concentration closest to the 99^th^ percentile of all groundwater environmental Alachlor concentrations in WY 2002. Then find the 9 nearest wells to broaden a future sampling effort.

![](media/image14.wmf)

*The pilot project team is as follows:*

Sandy Williamson, Lead, Tacoma WA <akwill@usgs.gov>

Nate Booth, Technical Lead, Middleton WI <nlbooth@usgs.gov>

Jessica Thompson, Developer, Middleton WI <jlthomps@usgs.gov>

Rick Bell, Testing, Little Rock AR <rwbell@usgs.gov>

[^1]: Candidate NWIS Hosts and DBNUMs correspond to those that contain
    NAWQA data.

[^2]: Sample and Result counts after NAWQA precedence scheme applied and
    Data Quality Indicators of "Proprietary, not reviewed",
    "Proprietary, reviewed and accepted", "Reviewed and rejected",
    "Proprietary, reviewed and rejected" and District Status Code of
    "Local-use data" are filtered.