ABSTRACT: Interpretation of hydrogeologic data frequently
involves assimilating information from many sites, each with a
unique geographic location. Typical sites for which data are obtained
include wells, streams, soil borings, weather stations and sources
of contamination. Information associated with these sites includes
well construction and soil boring specifications, soil and water
chemistry, water levels, rainfall, discharge rates, hydraulic
properties and geophysical data. Interpretation of these data
requires that the site spatial location be incorporated into the
analysis.
Geographic Information Systems (GIS) are designed
to manage, analyze and display all types of spatial data. This
capability makes GIS a powerful tool in conducting ground water
assessments. GIS maintains the spatial location of wells, borings,
etc., and provides tools to relate the well or boring to data
contained in an external relational database. GIS also provides
advanced tools for spatial analysis of the related data. In addition,
GIS provides sophisticated map-generation capabilities, useful
in communicating results of data analysis.
At the Northwest Florida Water Management District
(NWFWMD), ARC/INFO GIS has become the primary tool for conducting
ground water assessments. Data are stored in Oracle or INFO relational
database management systems (RDBMS) and are accessed directly
by the GIS. Spatial analysis and data visualization are then carried
out within the GIS environment.
KEY TERMS: GIS application development; RDBMS; ground
water assessment; ARC/INFO.
This paper describes how ARC/INFO GIS and Oracle
RDBMS were used to conduct an analysis of data generated by a
typical field investigation. The site under investigation (Naval
Technical Training Center Corry) is located near Pensacola, Florida
(Figure 1). Low-level ground water contamination, including chlorinated
pesticides and volatile organic compounds, is currently affecting
five of 10 potable water supply wells located at NTTC Corry. These
10 wells produce about 5 million gallons per day for use at NTTC
Corry and at NAS Pensacola. The NWFWMD recently completed an investigation
of this ground water contamination on behalf of the United States
Navy Southern Division. ARC/INFO GIS and Oracle RDBMS were the
primary tools used for data management and analysis. The principal
purpose of the investigation was to determine if the sources of
contamination were located on the grounds of the Naval facility.
As is typical for a ground water investigation, large
amounts of data were generated. Efficient analysis of these data
required versatile and powerful data storage and analysis tools.
The NWFWMD utilized ARC/INFO GIS and the Oracle RDBMS extensively
for these purposes. Most data generated by the project were stored
in various Oracle tables, each of which can be related to pertinent
ARC/INFO coverage features.
A point coverage of wells (wellinv) provides
the locational component of important data points utilized for
NWFWMD ground water resource management activities. Well construction
information is stored in a related Oracle table (wi_data).
Both the wells point coverage and the Oracle table contain an
item (key) which provides a one-to-one relate. Key
links the ARC/INFO point feature (a well) to a unique record containing
various data which describe the well (casing depth, total depth,
elevation, ownership, etc.).
In addition to wi_data, other Oracle tables
are utilized to store and manage pertinent data. Soil and water
chemistry and water-level data are examples for which multiple
records likely exist for a single point in space. The soil and
water chemistry table (qw_main) contains items such as
detection limit, analyte name, analyte concentration, sample date,
analysis date, etc. The water-level table (wi_wl) contains
items such as the measured water level, water level referenced
to land surface, water level referenced to MSL, and date and time
of measurement. Both tables also contain an item (key)
which relates an individual record to the ARC/INFO point feature
which represents the well. Figure 2 shows a generalized flow chart
depicting the NWFWMD GIS system access and Oracle DMBS integration.
The NWFWMD stores other data, including borehole
geophysical data and rainfall, in ASCII files. These data are
accessed by ARC/INFO plotting and graphing capabilities after
loading the ASCII data into a temporary INFO table.
The capabilities of ARC/INFO GIS allow water quality
and water-level data to be plotted relative to coverages representing
base map features and wells. ARC/INFO TIN surfaces and TIN contour
maps are generated directly from records contained in Oracle or
INFO tables. In addition, ARCPLOT graphing capabilities allow
for plotting water-level hydrographs, visualizing aquifer response
to rainfall and well-field operation, and plotting hydrogeologic
sections and borehole geophysical logs.
At the NWFWMD, desktop PCs and UNIX workstations
are integrated into a local area network (LAN). ARC/INFO GIS runs
on UNIX workstations. Users generally access the GIS via X-windows
emulation software running on desktop PCs. The Oracle RDBMS is
installed on a PC server running the NT operating system.
All ARC/INFO applications which access an Oracle
table require that ARC/INFO be connected to Oracle, utilizing
the ARC database integrator. The ARC/INFO connection to Oracle
is established by the ARC connect command as follows:
arc: connect <database> {connect_info}
arc: connect oracle
oracle_username oracle_password
Subsequent to connecting to the Oracle database,
various relates must be established. These relates provide the
linkage of the tabular Oracle data to a specific GIS feature.
An item value, which is unique to a specific GIS feature and all
Oracle records associated with that feature, must be present in
both the GIS coverage and the Oracle table.
An individual relationship is defined to establish
the connection between coverage features and a single Oracle table.
The ARC relate <add> command prompts the user for
seven characteristics which define the relate. The following is
an example:
arc: relate add
relation name: widata (name of relate, links tabular information to a coverage feature)
table identifier: wi_data (name of Oracle table to be linked or accessed)
database: Oracle (name of RDBMS to be accessed)
INFO item: wellinv-id (GIS coverage item used for relate)
relate column: key (name of Oracle column used for relate)
relate type: first (type of connection made to an external table)
relate access: ro
(read-only access to tabular data, rw read-and-write access)
The relate name identifies a specific relationship and is the name by which the relationship is invoked in order to access Oracle tabular data during GIS applications. Where access to Oracle tabular data is desired, the relate name is declared, followed by the Oracle column. The following example plots points and assigns an Oracle column value (well_name) as a pointtext.
arcplot: points wellinv
arcplot: pointtext
wellinv widata//well_name
If required, particular points and labels can be
plotted by applying a reselect criteria as a logical expression
utilizing the relate name and standard query language (SQL) as
follows:
arcplot: reselect wellinv points ^widata where well_name like 'Corry%'
arcplot: points wellinv
arcplot: pointtext wellinv widata//well_name
arcplot: clearselect
Several Oracle tables may be used in a query session,
thus requiring the declaration of multiple relationships. The
multiple relates constitute a relate environment which can be
saved and restored at will using the ARC relate <save|restore>
command. These relates serve as a 'gateway', linking spatial GIS
features to external tabular attribute data. This allows the powerful
spatial tools of a GIS to be applied to external tabular attribute
data.
Generating potentiometric surface maps is an important
element of ground water assessments. At NTTC Corry, water-level
data were collected for both the near-surface surficial zone (SZ)
and the deeper main producing zone (MPZ) of the Sand-and-Gravel
Aquifer. Approximately 60 wells were measured on multiple occasions.
Individual potentiometric surface maps were generated
for specific zones of the aquifer utilizing data collected on
a designated day. All water-level measurements (referenced to
the wellhead measure point) were entered into an Oracle water-level
table (wi_wl), along with other pertinent data (date, time,
measure point elevation above land surface and key). Wi_wl
internally calculates the water level below land surface. Water
level relative to mean sea level (MSL) is calculated based on
the water level below land surface (calculated in wi_wl)
and the land surface elevation (contained in a related Oracle
table wi_data). This calculation was performed using the
following Oracle update (SQL) statement:
Oracle SQL: update wi_wl a
Oracle SQL: set a.wl_elev = (select elevation + a.wl_lsd from wi_data b where a.key = b.key)
Oracle SQL: where a.wl_elev
is null/
Water-level data (wi_wl.wl_elev) for a given
date was then transferred to an INFO table. The INFO table allowed
for addition of items and information specific to the data application.
First, a temporary Oracle table was created based on a SQL select
statement which identified the records of interest. The INFO table
was then generated utilizing the ARC dbmsinfo command.
Oracle SQL: create table tmp as select * from wi_wl
Oracle SQL: where date
like '04-may-95'/
arc: dbmsinfo <database> <dbms_table> <info_file> {default | define} {options}
arc: dbmsinfo oracle
tmp cry_maywl.info
An ARC relate was defined (wi_maywl) in order
to link data contained in the INFO table and the well location
point coverage wellinv. An ARC TIN surface was then generated
using data contained in the INFO table cry_maywl.info.
The specific INFO records utilized in generating the TIN surface
were controlled by declaring a logical expression in the arc:
createtin dialogue shown below. In this case, the logical
expression allowed for selection of wells from a single zone (i.e.
the surficial zone (SZ)) of the aquifer. A contour map of the
TIN surface, with a two-foot contour interval was then generated
and plotted, along with base map and other coverages denoting
site features.
arc: createtin wl_maytin
createtin: cover wellinv point wi_maywl//wl_elev # # wellinv-id =
wi_maywl//key and wi_maywl//hydro_unit cn 'SZ'
createtin: end
arc: tincontour wl_maytin wl_contour 2
The hydraulic response of the Sand-and-Gravel Aquifer
to pumpage and rainfall was visualized using ARCPLOT's graphing
capabilities (Figure 3). Time-series data (rainfall, water level
and well usage) used to compose this figure were initially stored
in a series of three temporary Oracle tables. These tables were
created using the appropriate SQL logical expression. An additional
numeric column was added to each Oracle table to accommodate the
equivalent Julian date. The Julian date was then calculated (djdate),
using the Oracle statement shown below (to four decimal places,
in order to incorporate time into the Julian date).
Oracle SQL: update tmp set djdate = (to_number(to_char(date,'J'))) +
((to_number(to_char(date,'HH24')))/24) +
((to_number(to_char(date,'MI')))/1440)/
Oracle tables with the decimal Julian date were then
converted to INFO tables using ARC's dbmsinfo command.
ARCPLOT data graphing was performed with units set to graph.
The date/time (x-axis) of all three data sets (water level, rainfall,
well use) shared the same graphextent (set to the minimum
and maximum decimal Julian date desired). The graphextent
(y-axis) for the different data types (water level, rainfall,
well number) were adjusted as necessary, along with the graphlimits,
to plot each of the three data sets in the appropriate page position.
Use of declared variables for beginning and ending
Julian dates, line symbol, text size, etc., combined with calculation
of axis length in graph units facilitates AML modification. This
also facilitates plotting data for different time periods. Use
of variables also facilitates plotting multiple horizontal and
vertical reference lines on the graphs. Custom labeling of the
graph was performed with the units set to graph. This allows the
labeling to remain properly positioned despite scale changes.
Water levels and well use (Figure 3) were plotted
as line graphs. Rainfall was plotted as a bar graph. An INFO logical
expression was used to reselect data records for a specific well
prior to graphing the water-level data. Time-series data used
for line graphs must also be sorted on the item containing the
date/time component (djdate). This may be done in INFO
prior to issuing the ARCPLOT graphline command. Alternately,
it may be done using an option available within the ARCPLOT graphline
command dialogue. The following is an example of the command syntax
used to plot water-level data for an individual well.
arcplot: reselect cry_wlrec.info info key = 1993
arcplot: graphline cry_wlrec.info info djdate wl_elev # # # xsort
arcplot: clearselect
The hydrogeologic framework was defined based on
hydraulic properties interpreted from borehole geophysical logs.
To visually present this data, geophysical logs were incorporated
into figures depicting the principal hydrostratigraphic units
present at NTTC Corry (Figure 4). Data obtained from multiple
wells were available to prepare these sections. Normal electric
and natural gamma ray logs were available for most wells.
Actual log data were stored in ASCII files. One ASCII
file was maintained for each well. The first column contained
depth and subsequent columns contained geophysical log values.
The log data was loaded into an INFO file, one well per file.
All log depths were converted from distance below land surface
to distance relative to MSL.
AML variables were used to calculate and set graphlimits
and declare graphextent. This allowed for simple adjustments
to the size of the individual well logs and their respective scales.
This also simplified the repetitive process of plotting many similar
data sets. The main difference among the individual log plots
is their location on the page (determined by the actual distance
between wells). By declaring the horizontal position of an individual
well as a variable (used to calculate all required graphlimits
for well log data), altering the section length was simplified.
After declaring graphlimits and graphextent (using variables previously assigned), the log data was plotted using the ARCPLOT graphline command. The following statements select the desired data (16-inch normal log and elevation data) from the INFO file and plot the data.
arcplot: reselect cry-5-01d.info info sn ne 0
arcplot: graphline cry-5-01d.info info sn elev # # # nosort
arcplot: clearselect
Log depths are not constant. In order to calculate
the y-axis location for the geophysical log scale bars, the graphextent
was set to the appropriate log data and then read into a variable.
The minimum and maximum y (log depth relative to MSL) was extracted
from the x-min, y-min, x-max, y-max string recorded by the variable.
Only the y-min and y-max values were of interest, so these values
were extracted from the string and assigned to individual variables.
Five feet were subtracted from the y-min and five feet added to
the y-max values in order to provide for a more visually appealing
plot. These variables were then used to place the horizontal axis
(scale bars) above and below individual logs, followed by hatching
and labeling. Calculating the y-axis location for these scale
bars was performed within ARCPLOT (AML) as follows:
arcplot: graphextent <info_file> info <x-item> <y-item>
arcplot: graphextent cry-5-01d.info info ln64 elev (sets graphextent to desired log data)
arcplot: &s tmpvar [show graphext] (returns min and max values to variable tmpvar)
arcplot: &s axishmin [extract 2 %tmpvar%] (extracts the second variable of string, y-min)
arcplot: &s axishmax [extract 4 %tmpvar%] (extracts the fourth variable of string, y-max)
arcplot: &s axishmin [calc %axishmin% - 5] (y-axis position to used for bottom log scale bar)
arcplot: &s axishmax [calc %axishmax% + 5] (y-axis position to used for upper log scale bar)
arcplot: &s axisvlen [calc %axishmax% - %axishmin%} (axis length used for vertical axis
which denote log values)
Once these specific values have been extracted and
calculated, plotting of geophysical log value scale bars and associated
vertical grid lines can proceed. Figure 4 shows a portion of a
completed hydrogeologic section.
Mapping water-quality data in two-dimensional space
is a routine aspect of most contamination investigations. This
project was no exception. Samples collected from 69 wells and
28 soil borings yielded about 9,000 individual analytical records.
Mapping water-quality data for sites related to the
District's well point coverage (wellinv) is simple when
there is one data record associated with one site (one-to-one
relationship). This is a two-step process in ARCPLOT:
arcplot: mapextent <coverage>
arcplot: pointtext
<coverage> <relate
name // item name (of qw_main)>
In this investigation, however, each monitoring site
generated multiple water-quality data. These included data associated
with different parameters, replicates, and samples taken at different
dates. Because the RDBMS relate environment in ARC/INFO GIS (version
6) returns the first Oracle data record obtained for a
related GIS feature, the mapping example above could not be used
in certain circumstances. For example, the desired data (collected
at a date subsequent to the first sampling event and not stored
as the first Oracle record) would not be accessed by the above
example. In order to overcome this problem, RDBMS cursors were
utilized.
In DBMS, a cursor is analogous to a "pointer"
which is directed to a specific row in a table following a SQL
expression. Through an iterative process of declaring a cursor
and "capturing" the values of water-quality data into
AML variables, specific water-quality data were selected for each
monitoring site (wells or soil borings) and mapped (Figure 5).
This process was conducted in an ARCPLOT AML and is demonstrated
as follows:
arcplot: &s x [show select wellinv point 1 item x-coord] (AML variable x stores x-coordinate of a single previously selected well in the point coverage wellinv)
arcplot: &s y [show select wellinv point 1 item y-coord] (AML variable y stores y-coordinate of the selected well in the point coverage wellinv)
arcplot: &s site [show select wellinv point 1 item site_id] (AML variable site stores unique identifier of the selected well in the point coverage wellinv)
arcplot: dbmscursor qw declare oracle select value from (DBMS cursor qw is declared with a SQL search
qw_main where site_id like %site% and parameter_code = expression which directs cursor to the
39380 and replicate is null particular site and particular water-quality
constituent in the Oracle table qw_main)
arcplot: dbmscursor qw open
arcplot: &s data %:qw.value% (AML variable data stores the particular data record in the Oracle table)
arcplot: dbmscursor
qw close
arcplot: move %x% %y% (Move graphics pointer to the x-coord and y-coord of selected site)
arcplot: text %data%
(Write the value of AML variable data onto graphics
display)
In this section, GIS versatility is further analyzed
from the perspective of its utility in ground water modeling.
Although GIS softwares are not specialized tools for conducting
sophisticated ground water modeling, the major functionalities
inherent in GIS are useful in facilitating the preparation and
review of ground water models. In the investigation, an important
objective was to identify areas of possible sources of contamination
lying outside the local study area. Because the pumping wells
within the study area are located down-gradient from the regional
ground water divide, the purpose was to determine areas of ground
water recharge to the pumping wells; hence identify possible source
area(s) for ground water contamination within certain time frames.
In this effort, GIS was used to prepare the input files to ground
water models as well as to view simulation results (Pratt et al.,
1996; Roaza et al., 1993; Richards et al., 1993).
Two ground water codes were used for the analysis.
A numerical finite-element code was used to simulate ground water
heads beneath the study area (SWICHA). From the results of the
numerical model, advective particle-tracking analysis was conducted
using a semi-analytical code (WHPA; GPTRAC module). As with ARC/INFO
GIS, both codes were executed in the UNIX workstation to optimize
data transfer and retrieval between the softwares.
For the modeling domain, a variably-spaced numerical
grid was built as ARC/INFO polygon and point coverages. The polygon
GIS coverage represented simple linear finite elements and the
point coverage, element nodes (Figure 6). Closely spaced cells
were located in the vicinity of pumping wells. The ARC/INFO Grid
module is limited to homogenous (evenly spaced) cells and therefore,
was not used in this process.
The first step towards finite-element grid generation
was to create a polygon generate file. The generate file was created
via a simple FORTRAN program which read two ASCII files of row
spacings (y-axis) and column spacings (x-axis). Once the polygon
coverage was generated, grid cell nodes were extracted from the
polygon coverage using the nodepoint command and built
as a point coverage. The point coverage (finite element nodes)
was reserved for storing model data relating to hydraulic boundaries
(Dirichlet and Neumann type boundaries), pumping wells (flux nodes)
and nodal topology (x, y and z coordinates). The polygon coverage
stored information relating to cell area (for flux calculations)
and zonal hydraulic properties (material number zonations).
Spatial overlay tools were utilized to incorporate
boundary and hydraulic information to the study area grid (local
grid) from an existing regional ground water model. The regional
model, previously calibrated to the aquifer underlying the entire
county, was also developed using similar GIS methods (Roaza et
al., 1993). Hence, all the investigative information used to generate
the regional model as well as the simulation results already existed
as GIS thematic layers.
In order to incorporate the hydraulic boundaries, the local grid's point coverage was spotted to a TIN surface of the calibrated potentiometric surface of the regional model (tinspot). This process established an interpolated hydraulic boundary to the local modeling domain where the boundary values are based upon a calibrated model rather than arbitrary values. Zonal hydraulic parameters such as hydraulic conductivity/transmissivity and material number designations were transferred to the local grid from the regional model using point-to-polygon overlay method (identity: cell centroids of local grid to regional polygon coverage of hydraulic properties). For pumping centers in the model, individual pumping nodes were identified with the near command which allowed for the designation of model nodes closest to the pumping wells.
The attributes stored in the grid coverages were
output into several ASCII files. A set of FORTRAN programs converted
the information stored in the ASCII files into formats specific
to the SWICHA numerical code and to the WHPA semi-analytical code.
Upon completion of both the numerical and semi-analytical
simulations, the model results were converted into GIS thematic
layers for model analysis. For the numerical model, there
are basically two methods in which to view simulated ground water
heads. In the first method, the binary output from the numerical
model was written into a TIN xyz generate file format. A TIN was
built from the generate file in createtin: generate option.
From the TIN, model results were contoured by tincontour.
The advantage to this method is that the process is relatively
fast and is easy to view the results. However, simulated values
are not stored in GIS; only their contours.
In the second method, the binary output from the
numerical model was written into a ASCII file which contained
the nodal identifiers and their respective simulated values. This
file was loaded into an INFO table. The INFO table was joined
with the grid point coverage, using the nodal identifier as a
common relate (joinitem). A TIN was created from the point
coverage using the item which stored the simulated values (createtin:
cover option). From the TIN, a contour was created. The advantage
to this method is that simulated nodal values are stored in GIS.
However, the routine takes significantly longer to process.
The main output of the WHPA model are particle tracks
of advective transport. After each simulation, the particle tracks
were converted into an ARC generate file. From the generate file,
an ARC coverage of the particles-tracks were built. A conversion
routine of WHPA output to an ARC generate file is included in
the WHPA model package.
Geographic Information Systems and Relational Database
Management Systems are relatively new technologies and concepts.
Although GIS or RDBMS have been utilized for specific functions
in previous studies at the District, their combined use as an
integrated platform to store, analyze and present results was
demonstrated in support of an in-depth ground water investigation.
The integrated use of GIS/RDBMS in all aspects of the project
proved to be versatile, flexible and efficient.
The work described in this paper was supported by
the United States Navy Southern Division through Contract No.
N62467-93-C-0680, with Mr. Anthony Robinson functioning as Remedial
Project Manager. This paper has neither been reviewed nor approved
by the U.S. Navy and no official endorsement of the ideas or products
cited herein is implied.
Pratt, T.R., H. Roaza and C.J. Richards, 1996. Geographic
Information Systems-based Approach to Numerical Ground Water Modeling.
Symposium on GIS and Water Resources (Poster Session), Fort Lauderdale,
Florida, American Water Resources Association, September 1996.
Roaza, H., R.M. Roaza and J.R. Wagner, 1993. Integrating
Geographic Information System in the Analysis of Ground-Water
Flow, Contaminant Transport and Saltwater Intrusion Using the
SWICHA Finite Element Modeling Technique. Proceedings of the Symposium
on Geographic Information Systems and Water Resources, American
Water Resources Association Technical Publication Series TPS-93-1,
pp. 397-404.
Richards, C.J., H. Roaza and R.M. Roaza, 1993. Integrating
Geographic Information System in Well Field Design and Aquifer
Impact Analysis Using the MODFLOW Finite Difference Modeling Technique.
Proceedings of the Symposium on Geographic Information Systems
and Water Resources, American Water Resources Association Technical
Publication Series TPS-93-1, pp. 413-422.
1Senior Hydrogeologist, Northwest Florida Water Management District, Rt. 1, Box 3100, Havana, FL 32333
2Chief, Bureau of
Ground Water, Northwest Florida Water Management District
home page