Robert P. Czaja, Alexandra Clark, Sharon Einloth, D. Philip Guertin

Design of a Customized ArcView Application and Database for Addressing Water Supply Problems

Environmental projects can be difficult to manage given the amount of data generated with long-term monitoring. This paper describes development of an ArcView/Microsoft Access application for use by the China Lake Naval Air Weapons Station in Ridgecrest, California for water resource management. Data such as well locations, construction details and water quality for over 1500 wells were verified, normalized and added to an Access database. A customized ArcView application allows visualization and spatial analysis of the data. The ArcView application also allows visualization of the output from groundwater and contaminant transport analyses performed using MODFLOW and STRATAMODEL. This is an ongoing project being conducted by researchers from The University of Arizona Advanced Resources Technology Group in the School of Renewable Natural Resources and the Department of Hydrology and Water Resources.

This ongoing project consists of three components: a hydrologic investigation of Indian Wells Valley (IWV), a geochemical investigation of IWV and creation of a combined relational database Geographic Information System (GIS).

Indian Wells Valley is located in southern California (Figure 1). It is bounded by the Coso Range to the north, the El Paso mountains to the south, the Argus mountains to the east, and the Sierra Nevada mountains to the west. The cities of Ridgecrest and Inyokern and the China Lake Naval Air Weapons Station (CLNAWS) are located within the IWV. IWV is entirely dependant on groundwater for municipal, industrial and agricultural uses. Concern has developed on both regional and local scales over the last several decades regarding the sustainability of the water supply and water quality.

Unconsolidated deposits within the IWV basin are heterogeneous, especially in the area around the China Lake playa. The basin consists predominantly of alluvial fill and Pleistocene lacustrine deposits. The subsurface can be broadly categorized as interfingered deposits of high permeability sands and low permeability clays.

Excessive groundwater pumping, primarily in the intermediate region between Ridgecrest and Inyokern, located approximately 13 miles west-northwest of Ridgecrest, has produced a net negative water budget balance. Since 1966 groundwater extraction by wells has exceeded natural discharge by evapotranspiration (Dutcher and Moyle, 1973). Berenbrock and Martin (1991) claim that by 1959 annual ground-water pumpage exceeded the estimated annual natural recharge of 9,850 acre-ft/yr. Under steady-state conditions, according to Berenbrock and Schroeder (1994), Berenbrock and Martin (1991), and Bloyd and Robson (1971), the overall dominant groundwater flow direction in Indian Wells Valley was toward the topographic low in the basin (China Lake) from the deeper aquifer to the shallow, upper aquifer. Pumping in a transient situation has produced a gradient reversal such that flow currently is directed towards the intermediate region with flow from the shallow aquifer to the lower aquifer (Berenbrock and Schroeder, 1994).

The introduction of water from the shallow to the deep aquifer is of concern due to the contamination of the upper, shallow unit. Zellmer (1988), notes the Total Dissolved Solids (TDS) of the upper aquifer exceeds 67,000 parts per million (ppm), rendering it non-potable, while that of the potable waters in the deep aquifer overall is less than 500 ppm. Known contaminants in the upper aquifer include "aviation fuels, solvents, beryllium, propellants, explosives, pyrotechnics, laboratory waste, heavy metals, cyanide, industrial detergents, degreasers and other materials", (Zellmer, 1988, pg. 449).

2.1 Hydrologic Investigation
The major emphasis of the hydrologic portion of this project is to assess the potential volume of water that may be utilized by the IWV community and to identify the flow paths where this water may be intercepted.

Successful water management programs often include the use of a groundwater flow model. A model serves as a valuable tool for presenting vast amounts of information in a visual, easily updated manner. Using a model, different hydrologic regime scenarios may be examined in a relatively short time. The modeling software used in this investigation was MODFLOW(1), a numerical groundwater flow model that simulates flow in three dimensions developed by the United States Geological Survey (USGS) (McDonald and Harbaugh, 1988).

The groundwater flow model developed for this investigation updates a USGS model created in 1991 by increasing its sophistication and adding data acquired since 1991. The model simulates both steady state and transient conditions in the IWV aquifer. Steady state conditions are those conditions that would exist in an aquifer if pumping, or artificial recharge of water was not occurring and the system was at equilibrium. Groundwater storage properties of the aquifer are not considered in the steady-state system because inputs and outputs are in equilibrium. Transient conditions are those conditions that exist within an aquifer when it is placed under the stresses inducted by the pumping or injection of water. Generally speaking, steady state conditions could be considered to the "natural" condition of the aquifer, while transient conditions could be considered to be "real world" conditions.

The Groundwater Modeling System (GMS) interface was used in conjunction with MODFLOW to facilitate input file construction and to automate construction of the three-dimensional grid necessary for MODFLOW. GMS was developed by the Brigham Young University Engineering Computer Graphics Laboratory (1988) in conjunction with the U.S. Army Engineer Waterways Experiment Station, and it is distributed by the Department of Defense. GMS consists of a graphical user interface for use with several numerical models in addition to MODFLOW including: MT3D, MODPATH, and FEMWATER. Use of the GMS interface assists in integrating MODFLOW with GIS applications.

2.2 Geochemical Analysis
The purpose of the geochemical analysis is to construct a geochemical model of the groundwater in IWV as it travels radially inward towards the China Lake playa by evaluating the chemistry and isotopic signature of groundwater samples collected from multiple wells. The intent of the geochemical model is to evaluate the interaction between the deep and shallow aquifers and therefore assess the potential for shallow aquifer contamination to spread into the lower aquifer water supply.

STRATAMODEL(2) was used in this project to visualize the high and low permeability zones, and to assess the connectivity of the clay layers so as to estimate their capacity as a confining layer between the shallow and deep aquifers. STRATAMODEL is a subsurface imaging program used primarily by the petroleum industry to model heterogeneous rock and fluid properties in three dimensions for geological analysis and visualization. The program creates a three dimensional grid of cells, arranged in stratigraphically related layers which are constrained as defined by faults and other reservoir features. The model can resolve up to 100 attributes, each of which can be visualized and rotated in three dimensions as cross sections, fence diagrams, stratigraphic slices, and geobodies.

A simple geological model was constructed with STRATAMODEL using lithologic and gamma ray well logs for geologic data, a surface Digital Elevation Model (DEM) and a grid approximating bedrock to create the model structure. The well log data was split into two categories entered as high or low permeability by depth. For example, in terms of percent fines low permeability equals 100 percent fines and high permeability equals zero percent fines. For some wells there was both a lithologic and gamma ray log; these were used to develop a relationship between the percent fines and the gamma ray API units. In this way the maximum amount of data could be used in the same model. Once the well model is complete, STRATAMODEL performs kriging in three dimensions to assign a value for the user defined attribute to each cell. The resultant stratigraphic slice images, viewed with the penetrating wells, are then matched up with the corresponding chemistry data to aid in the interpretation of the system geochemistry. Given sufficient data, the chemistry information could itself be built into the model for visualization; this was not possible at the time of publication due to insufficient data.

2.3 Relational Database and GIS Application
The relational database/GIS (RDB/GIS) will be used by the environmental staff at CLNAWS. The RDB/GIS will contain the data and selected output from the hydrologic and geochemical portions of this project together with historic data collected from numerous sources. Traditionally, this type of information would have been contained within separate reports making it inconvenient at best to compare the data or results. Combined use of relational database and GIS applications allows easy visualization and analysis of the data. Additionally, queries may be performed simultaneously on all datasets.

The RDB/GIS application was developed with several requirements. The end users for the application are not computer oriented, therefore the application needed to be easy to use. The final RDB/GIS application needed to be executable on a desktop personal computer. Finally, the data needed to be updated easily. The software selected for use, Environmental Systems Research Institute's (Esri) ArcView and Microsoft's Access, were selected for their ease of use and their ability to meet the stated requirements. The RDB/GIS provides the end users with several customized features while maintaining all of the individual applications original functionality.

Access was selected for its ease of use and its ability to link with ArcView using the Object Database Connectivity (ODBC) standard. One of the major objectives in the database development portion of this project was to compile data from various sources and identify and remove duplicate data.

Data to be included in the relational database (RDB) was provided by China Lake Naval Air Weapons Station, United States Geological Survey, California Department of Water Resources, Houghton Hydro-Geo-Logic, TetraTech, Inc., Kern County Water Agency, Indian Wells Valley Water District, and IMC Chemicals. The type of data provided included, but was not limited to: well locations, construction details, electronic and physical logs, analytical data for soil and groundwater, pump test data, and aquifer data.

The data was provided in disparate formats which included hard copy, various word process formats, spreadsheets, text files and database files. Much of the information had been previously shared between the sources. Since most of the sources were not using a relational database to maintain the data, duplication of the data was common. In many cases, the format of the data varied between sources. For example, it was common for positional information about a particular well to be maintained by each source using a different number of decimal places. The conventions used to identify individual wells also was not consistent between sources. The most significant portion of work on the RDB was the normalization of the data to enforce consistent formatting and the identification and removal of duplicate records. Details regarding the normalization process, assigning unique well identifiers and locations, and removal of duplicate records is provided below.

3.1 Data Normalization
All of the data provided by our sources was added to an Excel workbook for normalization. Excel was used because it allows easier conversion and manipulation of data since relational database rules do not apply. Once added to the Excel workbook, the data was separated into several Excel worksheets based on logical connections. For example, all of the water level measurements were placed together as were all of the well construction details. Each of the worksheets would later become tables in the RDB.

Once all of the data was grouped, the field headings were modified to increase clarity and ensure consistency between worksheets for the fields that would serve as primary or foreign keys in the RDB. Data that was frequently repeated such as well type, or drilling contractor was replaced with a unique code number for each entry type. The code number and its corresponding entry were placed in a separate worksheet to serve as a lookup table. When entering data the user is presented with the list of acceptable entries from the lookup table, one of which must be selected. The lookup table method ensures consistency in the entries and prevents problems such as one user using "M" for monitoring well and another using "MW" for the same well type.

Both Excel and Access consider two digit years dates prior to 1929 to be in the next millennium and would therefore change them to be 2029. Since some of the data was collected in the 1920's all dates were modified using the format mm/dd/yyyy.

The Universal Transverse Mercator (UTM) coordinates were determined for all wells where the positional data was provided in another coordinate system. A text file containing the non-UTM coordinates was created and the coordinates projected into UTM coordinates using ARC/INFO. The projected coordinates were then added to the appropriate worksheet, the original coordinates were also retained.

3.1.1 Normalization of Well Positions
Positional information from all sources was converted to longitude and latitude in decimal degrees using Excel. Excel was also used to convert all elevations to feet. Well positions with only one data source were assumed to be correct. Conflicting positions were noted for many of the wells and were handled in several ways. If the conflict in longitude and latitude did not exceed the area of a , section the values were averaged. This distance was selected to conform with the level of precision used in the State of California well identification system.

In some cases position coordinates and elevation information was not provided, or the variation in reported positions was beyond the project tolerances. In these cases the position and elevation of the wells were determined from 1:24000 or 1:62500 scale topographic maps. Several wells were located on both map scales to determine the sensitivity of this estimation method to map scale. Values of longitude and latitude were in good agreement, but the accuracy of elevation increased with smaller contour intervals for obvious reasons. Conflicting elevations not varying by more than five feet were averaged.

Records for wells that have had any averaging of latitude, longitude, or elevation within these tolerances were flagged within the database. Each well position record in the database includes a code to allow the user to determine how the position was determined, thereby allowing a qualitative assessment of the accuracy or the position.

3.1.2 Assigning Unique Well Identifiers
Assignment of unique well identifiers was performed in Access since it was directly related to the process of determining the primary key for each table. A unique identifier was required for each well to serve as the primary key in several of the Access tables. The State of California uses well identification numbers consisting of a combination of the township, range, and section numbers combined with a lettering system to designate the , section. A state identification number was available for the majority of the wells. A unique name such as "Peachy" was available for some wells. These names typically reflected some unique local feature in the vicinity of the well.

A unique identifier for each well was assigned with the order of preference being state well number, local name, or a concatenated version of the longitude and latitude of the well in decimal degrees. To ensure that the longitude and latitude coordinates could serve as a unique identifier, all values were truncated to four or five decimal places respectively. Controlling the number of decimal places in this manner corrected problems associated with different sources maintaining data at different levels of precision.

For some type of data a composite primary key was required to ensure that each record in a table was unique. For example, the well usage table requires a composite primary key since it contains multiple years worth of records for each well. In a case such as this, the well identification number would not be sufficient to make each record unique.

3.1.3 Identification and Removal of Duplicate Data
The final step in normalization of the Access database tables was to locate and delete duplicated data. This ensured that each non-primary key item related to an individual well appeared only once in the database. A series of Structured Query Language (SQL) queries were used to determine the duplicated records in each table based on the table's primary key. For example, the well usage table was searched for duplicate records based on the well identification number, and the day, month and year of pumping. Together, these fields form the primary key for the well usage table. The duplicated information was compared and a decision as to what data to keep was made. In some cases the records were true duplicates and one of the records was deleted. In other cases a judgement need to be made about what information would be maintained based on the perceived quality of the data. For example, the well identification number field described above serves as the primary key for the well locations table. One of the common problems in this table was that the same well identification would be listed numerous times with different position information. In this case the decision of which records to delete was based on the method used to determine the position of the well with preference given to positions determined by post-processed global positioning system (GPS) methods and surveying techniques.

3.2 Effects of Normalization
It is interesting to note the effects of normalization on the size of the IWV database. Two tables in particular benefitted from the normalization process, well locations and well usage. The well locations table decreased in size from 1574 records to 1522 records, a decrease of approximately three percent. The effects of normalization on the well usage table were more dramatic with a decrease from 19680 records to 17106, or approximately 13 percent. Over 1,000 records were located that did not contain either location or identification information. Although this data was saved, it was removed from the relational database.

Initially it may seem as though the normalization process resulted in the removal of data from the database. However, removal of the duplicated or useless data described above resulted in a smaller more efficient database which contains only valid data.

Numerous themes were created for use in the ArcView View application. These themes provide location references to be used in conjunction with the well data contained in the RDB. Due to the complexity of the processing steps, many of the themes were produced in ARC/INFO and later added to the ArcView application as themes. Descriptions of the themes and details of the processing methods used to create them is provided in the following sections. To provide consistency with GIS products already in use by the CLNAWS, all of the coverages/themes created for this project use the UTM projection and the 1983 North American Datum (NAD83).

4.1 Boundary and Easements: CLNAWS
Survey records for the property limits and utility easements on the CLNAWS were obtained. Polygonal coverages for both were created using the Coordinate Geometry (COGO) feature of ArcEdit. The boundary coverage was created without significant problems. The utility easement coverage was problematic because many easements overlapped when in reality they should have been adjacent. This was attributed to several reasons including differences in the accuracy of the survey methods used. This issue was resolved by creating each easement as a region, which not only allowed the easements to overlap, but greatly simplified assigning attributes by decreasing the total number of features that needed to be attributed.

4.2 Roads
A roads coverage for the IWV was created using 1:100,000 Spatial Data Transfer Standard (SDTS) formatted files acquired from the USGS. The SDTS formatted files were converted to ARC/INFO line coverages using an ARC/INFO Macro Language (AML) utility provided by the USGS. The USGS AML combined the conversion and attributing steps and made processing of the SDTS file relatively easy.

Due to the rural natural of the IWV, many of the roads in the USGS files did not contain attributes. This was not considered to be a problem since the major roads through the valley were sufficiently attributed. A simplified roads coverage was created by reselecting only those roads which contained route numbers or names.

4.3 Digital Raster Graphics
The Digital Raster Graphics (DRGs) for all 91 quadrangles within the IWV watershed were obtained from the USGS. It is expected that the DRGs will be used as an infrequent, but useful reference for the CLNAWS environmental staff. For the purpose of this project, the white border around the edge of the map or collar, has been maintained. De-collaring of the maps may be performed in the future to allow mosaicing of the maps if use of the application indicates this would be of value.

4.4 Digital Orthographic Quarter Quads
The Digital Orthographic Quarter Quadrangles (DOQQs) for the IWV watershed were obtained from the USGS. Like the DRGs, it is expected that the DOQQs will be used as an infrequent, but useful reference for the CLNAWS environmental staff. One potential use is to allow visual assessment of remote areas of the IWV prior to performing field investigations.

4.5 Digital Elevation Models
The 30 meter resolution DEMs for the watershed including the IWV were acquired from the USGS on a CD ROM. All of the DEMs except for one were available as Level 2, providing adequate quality for this project. The entire watershed is covered by 91 7.5 minute DEMs. Hand processing of this many DEMs would have taken a prohibitively long period of time. therefore, several Unix shell scripts and ARC/INFO AML utilities were created to speed the process and ensure consistent treatment of the DEMs.

Initially, a Unix script was used create a text file containing basic information about each DEM. In addition to the file name used by the USGS on the CD, the Unix script extracted the following information from the header of each DEM: quadrangle name, corner coordinates, elevation unit code, and level code. Awk, named after its authors Aho, Weinberger and Kernighan, is a Unix programming language used to manipulate structured text files and produce formatted output. Awk was used on the text file noted above to create various input files for the AML utilities described below.

An ARC/INFO AML was used to convert all of the USGS DEM files into ARC/INFO GRID formatted files. Using the information in the text file noted above, a GRID AML was used to convert any DEM with elevation units in meters to feet using GRID. A second GRID AML was then used to create a mosaic of the individual DEMs. Interior nodata values were removed from the mosaic DEM using a GRID conditional statement.

A hillshade of the mosaic DEM was created and visually inspected for errors. In general, the quality of the mosaiced DEM was good, with some anomalies visible in portions of the seams between quadrangles covering large areas of low relief, such as the surface of a playa lake. In several instances the hard copy source maps were reviewed to assess potential reasons for the anomalies. In all cases the source maps showed features such as drainages that did not continue across the boundary of two quadrangles, or contour lines that did not edge match. It is estimated that the anomalous areas of the mosaic represent much less than one percent of the total area and do not affect the intended use of the mosaic DEM or hillshade. Further investigation would be required if the mosaic DEM were to be used for surface water modeling or analysis.

The DEM and hillshaded DEM were added to the ArcView product. The hillshaded DEM was added as an image to allow greater control of the contrast and visual quality.

4.6 Quadrangle Reference
A polygonal coverage showing the outline and locations of all 7.5 minute quadrangles in the IWV watershed was generated using information extracted from the DEM headers with the Unix script noted above. The coverage was created using an ARC/INFO AML that generated and attributed a polygonal coverage for each quadrangle and then appended them together to form the quadrangle reference coverage for the entire watershed.

5.1 MODFLOW Data
GMS allows the user to select the area to be modeled by creating polygons on a registered and rectified image used as a base map, or by inputting a georeferenced grid directly. The latter method was selected for use in this project.

The original model produced by Berenbrock and Martin was not spatial in nature, i.e. it was not necessary to locate the models grid relative to known locations for the model to function properly. In their report, the location of the model grid was drafted on to a based map assembled from relevant 1:24,000 topographic maps. The based map was then reduced to allow it to fit an 8.5 inch by 11 inch page for purposes of illustration in their report (Berenbrock and Martin, 1991).

The most desirable approach was to recreate the USGS 1991 model as closely as possible and then to build upon that foundation. The Berenbrock and Martin (1991) grid was simulated by estimating the longitude and latitude in decimal degrees of the lower left and upper left corners of their grid from the figures in their report. ARC/INFO and Arc/Edit were then used to create a fishnet with the same outline and number of cells. Berenbrock and Martin (1991) used a uniform cell size of 0.5 mile for both width and height, which was maintained as single polygons in the fishnet for purposes of ensuring spatial accuracy of the data.

Individual coverages of aquifer parameters such as transmissivity, horizontal hydraulic conductivity, and vertical hydraulic conductivity (as leakance) and areal attributes such as recharge and evapotranspiration were constructed using the fishnet. The value of the attributes was assigned to each fishnet polygon, and the polygons dissolved to produce larger polygons with the same attribute value.

Once the input coverages were constructed the model was run and the output to be incorporated in the GIS product was created. Output from the model consisted of maps presenting water level contours and flow direction vectors for both steady state and transient conditions (Figure 2). The output maps were exported from GMS in Digital Exchange Format (DXF) files for later incorporation into the GIS application.

Version 2.1 of GMS, used in this modeling effort, does not allow attributes associated with polygons or lines to be imported or exported, although annotations are maintained. The use of the annotations alone was determined to be insufficient for this project. While viewing the contour themes in ArcView, it is possible to zoom into an area without annotations, thereby losing the elevational reference within the view extent. This problem was corrected by converting the DXF files into ARC/INFO coverages and manually attributing the contours with their elevation. Version 3.0 of GMS supports importing and exporting of attributes however, the release of this version occurred subsequent to this modeling effort.

The STRATAMODEL analysis encompasses an area of approximately equal to nine 7.5 minute quadrangles. STRATAMODEL results were output as Tagged Image Format (TIF) images and ASCII raster grids for incorporation into the GIS product. STRATAMODEL is a highly specialized and expensive program that is not accessible to the environmental staff at CLNAWS. Exporting the STRATAMODEL output to the GIS application provided the CLNAWS environmental staff access to the data without incurring the purchase or educational expenses related to using STRATAMODEL.

5.2.1 TIF Images
Cross sections and fence diagrams created in STRATAMODEL were exported to the GIS as TIF images which portray the full complexity of the STRATAMODEL data to the end user.

In STRATAMODEL, a cross section is produced by displaying a spatially correct data set and selecting points along the desired line using the mouse. The coordinates of each point selected are automatically recorded in a text file. After selecting all of the desired points the cross section is produced by the program and saved as a TIF image.

The text files containing the x and y coordinates of the selected points were concatenated into a single Excel worksheet. A third field containing a cross section number was added to each set of points. The file was then save as a database formatted file. An ArcView view was created and the hillshaded DEM added for reference. The database file containing the cross section points was added to the view as an event theme and the legend edited so that each set of points was displayed with a unique color based on the value of the cross section number. A labeled cross section line was then added by connecting the cross section points. Each cross section line was then hotlinked to the appropriate TIF image (Figure 3).

5.2.2 ASCII Raster Grids
STRATAMODEL output was exported to the GIS as ASCII grids for presentation in ArcView 3D Analyst. Simplification of the STRATAMODEL results were required since the three dimensional capabilities of STRATAMODEL far exceed those of 3D Analyst. Even so, the 3D Analyst representations are useful, especially for observing the spatial relationships between the wells and subsurface features.

The STRATAMODEL results are presented as a block diagram composed of a framework of cells divided into 1,000 horizontal layers. The number of cells in each layer is determined by the resolution of the terrain model used when setting up the program. The value contained in each cell represents a probability that the cell has a low permeability.

STRATAMODEL does not require the layers to be of uniform thickness. Therefore, although the number of layer across the diagram is constant, the thickness of each layer can change across the diagram. This effect is most pronounced near the surface and attenuates with depth. Layers at elevations of 700, 900, 1100, 1300, 1500 and 1700 feet above mean sea level were extracted from the STRATAMODEL data and exported as ASCII grids. Processing of these grids was conducted in ARC/INFO using an AML utility.

The ASCII grids were converted to Arc grids and the values reclassified to form ten groups ranging from 10 to 100. Two additional sets of grids were created by reclassifying the Arc grids a second time. In the first set only cells with values greater than or equal to 75 were retained. In the second, only cells with values less than or equal to 25 were retained. All 18 of the resulting grids were converted to polygonal coverages and the polygons dissolved based on the grid cell values. This step was necessary to improve the final appearance of the layers. Finally the AML utility converted the 18 coverages to shape files.

The shape files were manually added to the ArcView application as themes in a 3D scene. Each layer was extruded 200 feet and the base elevation of each established at the top of the underlying layer. For example, the 700 foot layer was extruded to cover the depth range of 700 to 900 feet. The base level of the 900 foot layer was then established at 900 feet and so on. This method allowed the layers to be stacked upon one another seamlessly. The result of the processing steps outlined in this section is three, 3D objects that represent the original STRATAMODEL data, areas of high permeability and areas of low permeability.

The IWV DEM and hillshade were clipped using ARC/INFO to create smaller themes representative of the STRATAMODEL study area. A Triangulated Irregular Network (TIN) was created from the DEM and used as a reference surface. The hillshade was draped over the TIN and added to the scene to provide a topographic reference.

Wells were added to the scene by using an SQL connection to the Access database which executed a query in Access. The Access query returned a table containing positional, depth and elevation data for all wells for which that information was known. The table was added to the ArcView view as an event theme and then added to the 3D scene. The wells were extruded to a value equal to their total depth using the STRATAMODEL study area TIN as the surface elevation reference. The resulting scene including the 3D object representing the areas of low permeability is presented in Figure 4.

5.3 Access Database
Data in the Access database is linked to the GIS application in two ways. Simple common queries are accessed through a button added to the standard ArcView toolbar. More complex queries are constructed in Access and then accessed through the ArcView SQL Connect dialog box.

5.3.1 Common Queries
An Avenue script was written to perform common queries. The script uses SQL statements that have been saved as text files. The script is initiated by clicking a button added to the standard ArcView toolbar. The user is presented with a list of possible queries from which one is selected (Figure 5). The selected query is performed in Access and the returned table is added to the view as an event theme and displayed (Figure 6). If the theme already exists, the user is asked if it should be deleted. If the user indicates that it should not be deleted the script terminates. Adding themes in this manner assures that they are always based on the most recent version of the RDB.

5.3.2 Complex Queries
Complex queries are created using the graphic query designer in Access (Figure 7). The queries are tested to ensure that they return the desired results and then saved. All of the objects created in the RDB, i.e. tables, queries, forms etc., have been saved using simplified Hungarian notation. Hungarian notation, named for the nationality of its inventor Charles Simonyi, uses standardized conventions for naming objects. One of the conventions is to begin each object name with a prefix which denotes the object type. For example, table object names begin with "tbl" and query object names begin with "qry". In the remainder of the object name each major portion begins with a capital letter. Spaces, underscores, or dashes are not used in the object names. Examples of object names used in this database are "tblWellUsage" and "qryAllWellLocations". Using Hungarian notation makes the object type easy to identify and causes all objects of one type to sort together in the ArcView SQL Builder dialog box.

Once a query has been constructed it may be accessed through the ArcView SQL Connect dialog box. The previously defined SQL connection to the project database is selected from the pull down connection menu. The desired query is selected from the Tables area of the dialog box, and the "<All Columns>" item selected from the Columns area of the dialog box (Figure 8). The query is then performed in Access and the resulting table returned to ArcView. The table is then added as an event theme for display (Figure 9).

This application is currently under evaluation by the end users to help determine additional modifications to increase the usefulness of the application. Several modifications will be made in the near future:

Environmental monitoring and sampling projects generate large amounts of data. Combining the use of a relational database and GIS is advantageous to environmental professionals. Maintenance of the data in a relational database allows for greater security of the data, makes it easily retrievable and can ensure consistent data collection. Linking a GIS to the database enables convenient spatial representation and analysis of the data. The combined application is easily scalable making it easy to add additional data. Maintenance of the data in the relational database rather than in static themes in the GIS allows the themes to be recreated on an as-needed basis and ensures that they are based on the most recent version of the RDB.

Funding for this project is being provided by the China Lake Naval Air Weapons Station, Ridgecrest, California. The authors wish to thank the many individuals and organizations that contributed data for this project. Clifford Hathaway, Senior Systems Analyst for the ART group, provided invaluable assistance while the lead author plumbed the depths of Unix. Craig Wissler, Senior Research Specialist for the ART group, guided the authors through the inner workings of ARC/INFO and brought us back with a greater appreciation for the power of GIS.

(1) MODFLOW summary page available at:
(2) STRATAMODEL is a product of the Landmark Graphics Corporation, 15150 Memorial Drive, Houston, TX 77079. (281) 560-1000.

Berenbrock, Charles and Peter Martin. 1991. The ground-water flow system in Indian Wells Valley, Kern, Inyo, and San Bernardino Counties, California. U.S. Geological Survey Water Resources Investigations Report 89-4191, 81 p.

Berenbrock, Charles and Roy A. Schroeder. 1994. Ground-water flow and quality, and geochemical processes, in Indian Wells Valley, Kern, Inyo, and San Bernardino Counties, California, 1987-88. U.S. Geological Survey Water-Resources Investigations Report 93-4003, 59p.

Bloyd, R.M. and S.G. Robson. 1971. Mathematical ground-water model of Indian Wells Valley, California. U.S. Department of the Interior Geological Survey Water Resources Division Open-File Report, 36 p.

Brigham Young University- Engineering Computer Graphics Laboratory. 1988. The Department of Defense Groundwater Modeling System GMS v2.1 Reference Manual.

Dutcher, L.C. and W.R. Moyle, Jr. 1973. Geologic and hydrologic features of Indian Wells Valley, California. U.S. Geological Survey Water-Supply Paper. U.S. Geological Survey. Reston, VA, United States. No. 2007. 30 p.

McDonald, M.G. and Arlen W. Harbaugh. 1988. A modular three-dimensional finite-difference ground-water flow model. Techniques of Water Resource Investigations 06-A1, U.S. Geological Survey, 576 p.

Zellmer, John T. 1988. Engineering and environmental geology of the Indian Wells Valley Area, Southeastern California. Bulletin of the Association of Engineering Geologists. Vol. 25, No. 4, pp. 437-457.

Robert P. Czaja, Research Assistant
The University of Arizona
School of Renewable Natural Resources
Advanced Resources Technology Group
BioSciences East, Room 325
Tucson, AZ 85721
phone: (520) 621-3045
fax: (520) 621-7401

Alexandra Clark, Research Assistant
The University of Arizona
Hydrology and Water Resources
Harshbarger Building
1133 East North Campus Drive
Tucson, AZ 85721
phone: (520) 621-9715
fax: (520) 621-1422

Sharon Einloth, Research Assistant
The University of Arizona
Hydrology and Water Resources
Harshbarger Building
1133 East North Campus Drive
Tucson, AZ 85721
phone: (520) 621-8454
fax: (520) 621-1422

D. Philip Guertin, Associate Professor
The University of Arizona
School of Renewable Natural Resources
Advanced Resources Technology Group
BioSciences East, Room 325
Tucson, AZ 85721
phone: (520) 621-3045
fax: (520) 621-7401