SWQDM: Surface Water Quality Data Miner - Software page

Information

The SWQDM database application was developed by the U.S. Geological Survey in cooperation with the Federal Highway Administration for use in the analysis of regional and national hydrologic data sets. The surface-water quality data miner (SWQDM) database application was developed to coordinate use of NWIS Web, NWiz, and the Kendall-Theil Robust Line analysis software. The SWQDM database application provides water-quality data and streamflow statistics by ecoregion and by proximity to a user-defined site of interest. The SWQDM database application also provides custom lists of U.S. Environmental Protection Agency water-quality parameter codes and lists of water-quality monitoring sites selected by proximity to a user-defined site of interest to facilitate NWIS web queries by use of the NWiz program.

Documentation

Granato, G.E., 2009, Surface-Water-Quality Data-Miner SWQDM Database Application (Version 1.0)--Appendix 4 in Granato, G.E., Carlson, C.S., and Sniderman, B.S., 2009, Methods for development of planning-level estimates of water quality at unmonitored stream sites in the conterminous United States: Washington D.C., Federal Highway Administration Report FHWA-HEP-09-003, CD-ROM.          Manual               Data dictionary

The surface-water quality data miner (SWQDM) database application was developed by the U.S. Geological Survey in cooperation with the Federal Highway Administration for use in the analysis of regional and national hydrologic data sets. The SWQDM database application was developed to coordinate use of the National Water Information System on-line database (NWISWeb), National Water Information System Wizard (NWiz), and the Kendall-Theil Robust Line analysis software. The SWQDM database application provides water-quality data and streamflow statistics by U.S. Environmental Protection Agency level III nutrient ecoregion and by proximity to a user-defined site of interest. The SWQDM database application also provides custom lists of U.S. Environmental Protection Agency water-quality parameter codes and water-quality monitoring sites selected by proximity to a user-defined site of interest. The output files containing parameter codes and water-quality-monitoring site numbers facilitate NWISWeb queries by use of the NWiz program.

The process for manipulating data and information is described. Step-by-step use of the database application graphical user interface is illustrated. The program code, written in Microsoft Visual Basic for applications, is documented in the Microsoft Access database file on the computer disk accompanying this report. Some basic information about database design and implementation in Microsoft Access is provided. This report, however, was written with the assumption that the intended audience has a working knowledge of Microsoft Access and some background in the design or use of relational databases.

Granato, G.E., Carlson, C.S., and Sniderman, B.S., 2009, Methods for development of planning-level stream-water-quality estimates at unmonitored sites in the conterminous United States: Washington, D.C., U.S. Department of Transportation, Federal Highway Administration, FHWA-HEP-09-003, 53 p.           Report on-line

See the SELDM project web page here

Abstract

This report documents methods for data compilation and analysis of water-quality-transport curves that meet data-quality-objectives for order-of-magnitude planning-level estimates of stream-water quality at unmonitored sites in the 84 U.S. Environmental Protection Agency Level III nutrient ecoregions in the conterminous United States. The water-quality- transport curves developed in this analysis are intended for use with a stochastic data-generation algorithm, for use with a highway-runoff model designed to better quantify the risk of exceeding water-quality criteria as precipitation, discharge, ambient water quality, and highway-runoff quality vary from storm to storm. Transport curves are regression relations used to estimate constituent concentrations from measured or estimated water-discharge values. Three constituents, total phosphorus, total hardness, and suspended sediment, were selected for regression analysis to develop transport curves for each ecoregion. However, the data compilation and interpretation methods described herein may be used with other water-quality constituents. A total of 24,581 USGS surface-water-quality monitoring stations with drainage areas ranging from 0.002 to 1,140 square miles were identified in the conterminous United States and cataloged for retrieval of water-quality data. The number of paired water-discharge and water-quality samples for total phosphorus, total hardness, and suspended sediment concentrations was 246,403; 107,289; and 275,950, respectively. Examination of transport curves developed with these data indicate that these curves are appropriate models describing the underlying processes of washoff or dilution expected for each constituent, and that predictions made using these transport curves are comparable with published estimates for each water-quality constituent. All of the geographic information system files, computer programs, data files, and regression results developed for this study are included on the CD-ROM accompanying this report. The CD-ROM also contains a data directory with more than 1,876,000 paired discharge and water-quality measurements that include 21 other constituents commonly studied in highway- and urban-runoff studies.

The SWQDM application can serve as a data preprocessor for the Stochastic Empirical Loading and Dilution Model, which is designed to facilitate the estimation of runoff flows, concentrations of constituents, and loads of constituents from highways and in receiving waters at unmonitored sites on the basis of site characteristics.

History

SWQDM version 1.0.0 - Initial release 2009

SWQDM version 1.1.0 - Added selected data, converted the database to Access 2010, and updated the file open/save driver for use with Windows 2010

System Requirements

The SWQDM is written in Visual Basic for Applications and therefore use is limited to Microsoft Windows operating systems. The graphical display forms require a screen resolution exceeding 1024 x 768 pixels.

Installation package

For best results use the SWQDM installation file, it ensures that you have the proper drivers and settings. If you do not own a copy of Access, the installation file will provide a runtime version for use with the SWQDM. The installation file was created with the Sagekey Access Deployment Wizard. The uncompiled version is available for advanced Access users. The uncompiled Microsoft Access files listed under source code are provided for future use by model developers.
 
 
SWQDM version 1.1 installation file -- SWQDM installation instructions are on the CD-ROM

Source Code (Version 1.1.0)

The SWQDM is a Microsoft Access database application, but the code can be incorportated into stand-alone software. The the VBA code is available for use within the uncompiled database application.
Uncompiled Access 2010 version in a .zip file with the application icon (forward compatible)
Compiled Access 2010 version in a .zip file with the application icon (forward compatible)
Decompiled version in a .zip file with the application icon (forward compatible)

Note: After saving the database to your computer, right-click the database icon and uncheck the read-only box so that the database will work properly on your computer.

32-Bit Microsoft Access vs 64-Bit Microsoft Access

This application is developed for the 32-bit version of Microsoft Access. Microsoft recommends the 32-bit version of Office for most users, because it’s more compatible with most other applications. That is why the 32-bit version of Office is the default installation for 32 and 64 bit versions of Windows. The 32-bit Office client is supported as a Windows-32-on-Windows-64 (WOW64) installation. WOW64 is the x86 emulator that enables 32-bit Windows-based applications to run seamlessly on 64-bit Windows systems. This lets users continue to use existing Microsoft ActiveX Controls and COM add-ins with 32-bit Office. Only people who have to manipulate extremely large recordsets (such as spreadsheets with more than one-million rows) would need to use the 64-bit version of Office. Furthermore, 64-bit files are not backward compatible.

As such, this database application was created and compiled by using the 32-bit version of MS Access. The installed runtime version of the 32-bit application should run, but the compiled version will not run if the user has the 64-bit version of Office installed.

If you have the 64-bit version of MS Office use the decompiled version of the 32-bit database (.accdb), copy it over to a 64-bit version of MS Access. Compact and repair the database, then compile the code. At this point you will need to address any errors (such as missing references) and once you have sucessfully compiled the database, compact and repair it again. You may make an executable version but keep an uncompiled version for future updates.

Data Requirements

The SWQDM is a database application to provide data and statistics for data already in the database. However additional data from NWIS Web can be easily loaded by using standard MS Access protocols. Additional data are available for importing on the FHWA-HEP-09-003 available from the SELDM project page.

Support

Limited support is provided for correcting bugs and clarification of how the code is intended to work. Currently, there are no known bugs or suggestions for improvement. For support or to provide feedback or suggestions for improvement contact Greg Granato.

User Rights

This software and related material (data and documentation) are made available by the USGS to be used in the public interest and the advancement of science. You may, without any fee or cost, use, copy, modify, or distribute this software, and any derivative works thereof, and its supporting documentation, subject to the USGS Software User Rights Notice

Product Disclaimer

Any use of trade, product, or firm names is for decriptive purposes only and does not imply endorsement by the U.S. Government.