&EPA
United States
Environmental Protection
Agency
EarthSoft, Inc.
Environmental Quality
Information System - EQulS®
Innovative Technology
Evaluation Report
SUPERFUND INNOVATIVE
TECHNOLOGY EVALUATION
-------
-------
EPA/540/R-02/503
May 2002
EarthSoft, Inc.
Environmental Quality Information System - EQuIS®
Innovative Technology Evaluation Report
National Risk Management Research Laboratory
Office of Research and Development
U.S. Environmental Protection Agency
Cincinnati, Ohio 45268
Recycled/Recyclable
Printed with vegetable-based ink on
paper that contains a minimum of
processed chlorine free.
-------
NOTICE
The U.S. Environmental Protection Agency (EPA) through its Office of Research and Development under the
auspices of the Superfund Innovative Technology Evaluation (SITE) Program funded the research described
here under Contract No. 68-C5-0036 to Science Applications International Corporation (SAIC). It has been
subjected to the Agency's peer and administrative review and has been approved for publication as an EPA
document. Mention of trade names or commercial products does not constitute endorsement or
recommendation for use.
-------
FOREWORD
The EPA is charged by Congress with protecting the Nation's land, air, and water resources. Under a mandate
of national environmental laws, the Agency strives to formtdate and implement actions leading to a compatible
balance between human activities and the ability of natural systems to support and nurture life. To meet this
mandate, EPA's research program is providing data and technical support for solving environmental problems
today and building a science knowledge base necessary to manage our ecological resources wisely, understand
how pollutants affect our health, and prevent or reduce environmental risks in the future.
The National Risk Management Research Laboratory (NRMRL) is the Agency's center for investigation of
technological and management approaches for reducing risks from pollution that threatens human health and
the environment. The focus of the Laboratory's research program is on methods and their cost-effectiveness
for prevention and control of pollution to air, land, water, and subsurface resources; protection of water quality
in public water systems; remediation of contaminated sites, sediments and ground water; prevention and control
of indoor air pollution; and restoration of ecosystems. NRMRL collaborates with both public and private sector
partners to foster technologies that reduce the cost of compliance and to anticipate emerging problems.
NRMRL's research provides solutions to environmental problems by: developing and promoting technologies
that protect and improve the environment; advancing scientific and engineering information to support
regulatory and policy decisions; and providing the technical support and information transfer to ensure
implementation of environmental regulations and strategies at the national, state, and community levels.
This publication has been produced as part of the Laboratory's strategic long-term research plan. It is published
and made available by EPA's Office of Research and Development to assist the user community and to link
researchers with their clients.
E. Timothy Oppelt, Director
National Risk Management Research Laboratory
-------
ABSTRACT
This project consisted of an evaluation of the Environmental Quality Information System (EQuIS) software
designed by EarthSoft, Inc. as an environmental data management and analysis platform for monitoring and
remediation projects. In consultation with the EQuIS vendor, six primary modules were tested in this
evaluation. These were: Chemistry, Geology, Arc View Geographic Information System (GIS) Interface, Data
Verification Module (DVM), CrossTab Report Writer, and Electronic Laboratory Data Checker (ELDC).
These modules were chosen for testing because they are the most commonly used. As a part of this evaluation,
a demonstration of the technology was conducted by the SITE Program at Science Applications International
Corporation's (SAIC) offices in Cincinnati and Columbus, Ohio and McLean, Virginia. The purpose of the
demonstration was to determine whether the software performs the functions claimed by EarthSoft, Inc. and
to assess the accuracy of the EQuIS output. In addition, demonstration results and other sources of cost
information were used to develop detailed cost estimates for full-scale application of the technology.
The primary objectives for the EQuIS software evaluation were to:
1. Verify that all system functions were fully operational and had no significant programming errors.
A significant programming error was defined as: the inability of a software function to execute
properly (e.g., a fatal error) or a software function which produced an erroneous result (e.g., incorrect
statistical calculation).
2. Determine the conformance of the EQuIS system's input and output functions to data exchange
standards. Data import was tested by comparing values in the EQuIS database with input files to
ensure data were not corrupted. Similarly, data export involved a comparison of EQuIS output files
with values in various commercial-off-the-shelf (COTS) software to which EQuIS exports data.
The secondary objective of the system evaluation was to:
1. Estimate the cost of implementing, using, and maintaining the system for a "typical" hazardous waste
site data management program.
In general, major system functions of the six modules tested performed as claimed by the vendor, with the
exception of several functions of the DVM module which impacted the usefulness of this module. Other
modules exhibited minor problems with system functionality, but none of these impacted the overall utility of
the software. Most system functions were easy to use for anyone familiar with Microsoft Windows.
The software demonstrated the ability to adhere to data exchange standards while importing data from and
exporting data to a variety of COTS software. Data exchange was not always straightforward and frequently
required support from EarthSoft's help desk or an operator experienced with data exchange related to other
databases.
IV
-------
The total cost for a large-scale, multi-user implementation of the software, based on experience at the New
Jersey Department of Environmental Protection (NJDEP), was estimated to be $190,500. At the time of the
demonstration, NJDEP had a 40-user license and had received over 16,000 submissions from hazardous waste
sites throughout the state. This estimate assumed the equivalent of two full-time staff to manage the program
and operate the software. Data entry was performed by the equivalent of three full-time student co-op students.
This estimate included:
• site preparation
• equipment (software and hardware purchase/upgrades)
• startup and fixed costs
first year operating costs (primarily labor)
• supplies
• maintenance • , . •
Total costs for a smaller-scale, multi-user application were estimated at $45,000 based on information from
the Colorado Department of Health. This estimate assumed part time operation by two permanent employees
and data entry by temporary employees.
The cost to'implement this technology will be highly site specific depending upon the number of modules and
users required, the current availability of computer equipment, the amount of data processed, and the
familiarity of personnel with basic scientific software. The cost estimates do not include operating costs for
successive years.
The reader is cautioned that, due to the rapid nature of software development, the versions of EQuIS modules
utilized during this demonstration have since been superceded. The developer'claims that many of the minor
problems noted during this demonstration have been corrected or rendered moot because of changes to the
software (see the Vendor Claims in Appendix B) . In some cases, these changes were reportedly ongoing or
completed by the time this evaluation was completed. Due to scheduling and budgetary constraints, the SITE
Program was unable to verify these claims. However, as part of its routine sales operations, EarthSoft provides
software demonstrations. Such demonstrations can be used as an opportunity for potential customers to verify
that the vendor has upgraded the system as claimed.
-------
TABLE OF CONTENTS
Page
NOTICE ii
FOREWORD iii
ABSTRACT iv
LIST OF FIGURES k
LIST OF TABLES x
ACRONYMS AND ABBREVIATIONS xi
ACKNOWLEDGMENTS xiii
EXECUTIVE SUMMARY ES-1
SECTION 1
INTRODUCTION 1
1.1 BRIEF DESCRIPTION OF PROGRAM AND REPORTS 1
1.2 PURPOSE OF THE ITER 2
1.3 TECHNOLOGY DESCRIPTION 2
1.4 DESCRIPTION OF THE DEMONSTRATION DATA 4
1.5 DESCRIPTION OF DEMONSTRATION ACTIVITIES 5
1.6 SUMMARY OF DEMONSTRATION RESULTS 5
1.7 KEY CONTACTS 6
SECTION 2
TECHNOLOGY APPLICATIONS ANALYSIS 7
• 2.1 REGULATORY CONSIDERATIONS 7
2.2 OPERABILITY OF THE SYSTEM 7
2.3 TECHNOLOGY APPLICABILITY 8
2.4 KEY FEATURES OF THE EQuIS SOFTWARE 8
2.5 AVAILABILITY OF THE TECHNOLOGY 8
2.6 EASE OF USE .- 8
2.7 SITE SUPPORT REQUIREMENTS 8
2.8 LIMITATIONS OF THE TECHNOLOGY 9
2.9 REFERENCES 9
VI
-------
TABLE OF CONTENTS (continued)
SECTION 3
ECONOMIC ANALYSIS 10
3.1 INTRODUCTION 10
3.2 BASIS OF ECONOMIC ANALYSIS 10
3.3 ISSUES AND ASSUMPTIONS 10
3.3.1 Site Preparation Costs 11
3.3.2 Permitting and Regulatory Costs 11
3.3.3 Equipment 11
3.3.3.1 EQuIS Products 11
3.3.3.2 EQuIS Applications 12
3.3.3.3 EQuIS Modules 12
3.3.3.4 EQuIS Interfaces 12
3.3.3.5 Third-Party Products 14
3.3.3.6 Hardware Costs 16
3.3.4 Startup and Fixed Costs 16
3.3.5 Operating Costs 17
3.3.6 Supplies 17
3.3.7 Consumables 17
3.3.8 Effluent Treatment/Disposal 17
3.3.9 Residuals 18
3.3.10 Analytical Services 18
3.3.11 Modifications, Repair, and Replacement 18
3.3.12 Site Demobilization 18
3.4 RESULTS OF THE ECONOMIC ANALYSIS 18
3.5 REFERENCES 18
SECTION 4
TECHNOLOGY EFFECTIVENESS 21
4.1 BACKGROUND 21
4.1.1 DataReview 21
4.2 METHODOLOGY 21
4.2.1 System Functionality 21
4.2.2 Conformance of Input and Output With Data Exchange Standards 21
4.3 DEMONSTRATION RESULTS 22
4.3.1 Functionality Test Results 22
4.3.1.1 EQuIS ELDC Functionality Test Results 22
4.3.1.2 EQuIS Chemistry Functionality Test Results 23
4.3.1.3 EQuIS DVM Functionality Test Results 29
4.3.1.4 EQuIS Geology Functionality Test Results 31
4.3.1.5 EQuIS ArcView Interface Functionality Test Results 37
4.3.1.6 EQuIS CrossTab Report Writer Functionality Test Results 41
4.3.2 Conformance to Data Exchange Standards Test Results 42
4.3.2.1 EQuIS Chemistry Conformance to Data Exchange
Standards TestResults 42
4.3.2.2 EQuIS Geology Conformance to Data Exchange
Standards Test Results 44
4.3.2.3 EQuIS ArcView Interface Conformance to Data Exchange
Standards Test Results 48
VII
-------
TABLE OF CONTENTS (continued)
4.4 QUALITY ASSURANCE/QUALITY CONTROL 49
4.4.1 QA/QC Conclusions and Data Quality Limitations 49
4.4.2 QA Efforts and Results 49
4.4.2.1 QA/QC - ELDC Functionality Test Results 49
4.4.2.2 QA/QC - Chemistry Functionality Test Results 50
4.4.2.3 QA/QC - DVM Functionality Test Results 50
4.4.2.4 QA/QC - Geology Functionality Test Results 50
4.4.2.5 QA/QC - Arc View Interface Functionality Test Results 50
4.4.2.6 QA/QC - CrossTab Report Writer Functionality Test Results 50
4.3.2.7 QA/QC - Chemistry Data Exchange Test Results 50
4.4.2.8 QA/QC - Geology Data Exchange Test Results 50
4.4.2.9 QA/QC - ArcView Interface Data Exchange Test Results 50
4.5 RESIDUALS , 50
SECTION 5
OTHER TECHNOLOGY REQUIREMENTS 51
5.1 ENVIRONMENTAL REGULATION REQUIREMENTS 51
5.2 PERSONNEL ISSUES 51
5.3 COMMUNITY ACCEPTANCE 51
SECTION 6
TECHNOLOGY STATUS 52
APPENDIX A
VENDOR CLAIMS Al
APPENDIX B
CASE STUDY NO. 1 • Bl
viii
-------
LIST OF FIGURES
Figure
age
1-1. Flow diagram for data through the EQuIS software 3
3-1. Cost of EQuIS applications 12
3-2. Cost of EQuIS modules 12
3-3. Cost of EQuIS interfaces 13
3-4. Cost of EQuIS interfaces 13
3-5. Costs for QMS packages 14
3-6. Costs for QMS Individual Modules 14
3-7. QMS costs for workstation and NT Server hardware locks • • • *4
3-8. Costs of the LogPlot software 14
3-9. Costs of the RockWorks software 15
3-10. Costs of the EVS • l5
3-11. Costs of the Surfer software 16
3-12. Sample configuration for the Pentium IE (866 MHz) listed in Table 3-4 17
3-13. Cost of EQuIS services ^
B-l Landfill Site Photo °3
B-2 Cross-section of old stream channel B3
B-3 Water level vs. time at MW-2 B4
B-4 Methylene chloride concentrations vs. time in MW-2 B4
B-5 Site plan and potentiometric map for metal plating site B6
B-6 Bedrock tin for Rocky Flats groundwater model B6
IX
-------
LIST OF TABLES
Table
ES-1. Summary Results for Primary Objectives ES-2
ES-2. Superfund Feasibility Study Evaluation Criteria for the EQuIS Software ES-3
3-1. Twelve Cost Categories for the EQuIS SITE Demonstration 10
3-2. Costs for the ArcView software 14
3-3. Comparison of PC Typical Costs 16
3-4. Costs Estimate for a Large-Scale, Multi-User Implementation of EQuIS 19
3-5. Costs Estimate for a Small-Scale, Multi-User Implementation of EQuIS 20
4-1. EQuIS User Access Levels 23
6-1. End Users of EQuIS 52
-------
ACRONYMS AND ABBREVIATIONS
ARAR Applicable or Relevant and
Appropriate Requirement
ATTIC Alternative Treatment Technology
Information Center
BDAT Best Demonstrated Available
Technology
CAA Clean Air Act
CDPHE Colorado Department of Health and
Environment
CERCLA Comprehensive Environmental
Response, Compensation, and
Liability Act
CFR Code of Federal Regulations
CLU-IN Cleanup Information
COTS Commercial off-the-shelf
CWA Clean Water Act
DVM Data Validation Module
EDD Electronic Data Deliverable
ELDC Electronic Laboratory Data Checker
EM electromagnetics
EPA U. S. Environmental Protection Agency
EQuIS Environmental Quality Information
System
EVS Environmental Visualization System
FTE full-time employee
GB gigabyte
Ghz gigahertz
GIS Geographic Information System
GIU graphic interface utility
QMS Groundwater Modeling System
GPR ground penetrating radar
HVAC heating, ventilation, and air
conditioning
ITER Innovative Technology Evaluation
Report
LDRs Land Disposal Restrictions
MB megabyte
MCL maximum contaminant level
mHz megahertz
NAAQS National Ambient Air Quality
Standards
NJ New Jersey
NJDEP New Jersey Department of
Environmental Protection
NPDES National Pollutant Discharge
Elimination System
NRMRL National Risk Management Research
Laboratory
ORD Office of Research and Development
xi
-------
ACRONYMS AND ABBREVIATIONS (Continued)
OSHA Occupational Safety and Health
Administration
OS WER Office of Solid Waste and Emergency '
Response
PC personal computer
PCB polychlorinated biphenyl
POTW publicly-owned treatment works
PPE personal protective equipment
ppm parts per million
PTE part-time employee
QAPP Quality Assurance Project Plan
QA quality assurance
QC quality control
RAM random access memory
RCRA Resource Conservation and
Recovery Act
RI Remedial Investigation
RPM remedial project manager
SAIC Science Applications International
Corporation
SARA Superfund Amendments and
Reauthorization Act
SDWA Safe Drinking Water Act
SITE Superfund Innovative Technology
Evaluation
SVOCs semi-volatile organic compounds
TBC to be considered
TER Technology Evaluation Report
TSCA Toxic Substances Control Act
UPS universal power supply
VISITT Vendor Information System for
Innovative Treatment Technologies
VOCs volatile organic compounds
WAM Work Assignment Manager
2D 2-dimensional
3D 3-dimensional
XII
-------
ACKNOWLEDGMENTS
Ms Superfund Innovative Technology Evaluation (SITE) Program report was prepared under the direction
and coordination of Richard Eilers, U.S. Envkonmental Protection Agency (EPA) National Risk Management
Research Laboratory (NRMRL) Work Assignment Manager (WAM).
This report was prepared for EPA's SITE Program by the Energy and Environment Group of Science
Applications International Corporation (SAIC) in Hackensack, New Jersey and Cincinnati, Ohio under
Contract No.68-C5-0036. This report was written by Jim Rawe, the SAIC WAM, with assistance from Evelyn
Meagher-Hartzell, Ravi Kanda, Steve McBride, and Bill Samuels of SAIC, the software evaluator(s). The
author is grateful to Izak Maitin of the New Jersey Department of Environmental Protection (NJDEP) for
providing data sets for use in this evaluation, and to Rita Schmon-Stasik and Joe Evans of SAIC who
performed quality reviews.
XIII
-------
-------
EXECUTIVE SUMMARY
This document presents an evaluation of the Environmental
Quality Information System (EQuIS) software and the ability of
this data warehouse to import, review, utilize, and export
chemical and geological data during a Superfund Innovative
Technology Evaluation. (SITE) demonstration conducted by the
U.S. Environmental Protection Agency (EPA). This software
receives chemical and geologic data via manual input or
electronic data deliverables (EDDs). EDDs are checked for
formatting errors utilizing the Electronic Lab Data Checker
(ELDC). Data in the Chemistry module can receive a limited
validation using the Data Verification Module (DVM). EQuIS
links to Microsoft Office® allowing the user to generate custom
formats for graphing and reporting. Custom reports can also be
generated with the CrossTab Report Writer. EQuIS contains an
ArcView Interface that allows data to be queried from both the
Geology and Chemistry modules, and 3-dimensional (3D)
visualization to be accessed. Finally, EQuIS data can be
exported from Chemistry, Geology, or the ArcView Interface to
a variety of commercial off-the-shelf (COTS) software for 3D
visualization, production of well logs and geologic cross-
sections, and other graphical representations.
During the SITE demonstration, data from the Martin Aaron site
in Camden, New Jersey were utilized to evaluate the software
capabilities. The primary objectives for the EQuIS software
evaluation were to:
1. Verify that all system functions were fully operational
and had no significant programming errors. A
significant programming error was defined as: the
inability of a software function to execute properly
(e.g., a fatal error) or a software function which
produced an erroneous result (e.g., incorrect statistical
calculation). Major system functions in the Chemistry,
Geology, ArcView Interface, DVM, CrossTab, and
ELDC modules were executed to verify operability.
2. Determine the conformance of the EQuIS system's
input and output functions to data exchange standards.
EQuIS interfaces with several COTS packages. These
include Groundwater Modeling System (QMS),
Rockworks, LogPlot, Environmental Visualization
System (EVS), Surfer, and ArcView. The data
exchange between EQuIS Chemistry, Geology, and
ArcView Interface was tested for interoperability with
this list of select COTS products.
The secondary objective of the system, evaluation was to:
1. Estimate the cost of implementing, using, and
maintaining the system for a "typical" hazardous waste
site data management program.
A Category E Quality Assurance Project Plan (QAPP) was
developed for this project. No samples were collected nor were
any analyses performed. Therefore, standard quality assurance
(QA) objectives for data quality indicators (precision, accuracy,
etc.) do not apply to this project. Project QA efforts centered on
documentation of various tests performed to support conclusions
regarding the evaluation of software functionality and data
exchange. In addition, the QA review evaluated the
completeness of planned testing and the impact of any QAPP
changes. Overall, test results were well documented and
complete. QAPP modifications, arising from efforts to limit
redundancy, address project financial constraints, correct test
plan errors, or remove obsolete or seldom used software
functions, were fully described and justified in project
documentation. Functions that were not evaluated were not
considered crucial to the overall system functionality. These
deviations from the QAPP did not impact overall project
objectives.
The EQuIS demonstration results indicate that five of the six
modules performed successfully. For these five modules, the
majority of system functions performed as claimed by the
vendor. Minor problems with system functionality were
discovered, but none of these impacted the overall utility of the
software. Most system functions were easy to use for anyone
familiar with Microsoft Windows. The evaluation of the sixth
module (DVM) indicated the successful performance of most
major functions, but performance problems with the application
of three of the functions tested. The Precision and Blank Ratio
functions performed inconsistently. The Flag Order function did
not perform as expected. These problems have a significant
impact on the application of the software for data verification
tasks.
ES-1
-------
The software also demonstrated the ability to adhere to data
exchange standards while exporting data to a variety of COTS
software. Data exchange was not always straightforward and
frequently required support from EarthSoft's help desk or an
operator experienced with data exchange related to other
databases. Table ES-1 presents the summary results for the two
primary objectives as they relate to each of the modules.
Table ES-1. Summary Results for Primary Objectives
EQulS Module
ELDC
Chemistry
DVM
Geology
ArcView Interface
CrossTab Report
Writer
System Fully Functional/
No Significant
Programming Errors
YES
YES
PARTIAL1
YES
YES
YES
Conformance to
Data Exchange
Standards
NA
YES
NA
YES
YES
NA
NA
The system was partially functional; however, some key functions did
not operate as claimed by the vendor. This had a significant impact on
the application of the software for data verification tasks.
Not applicable
The total cost for a large-scale, multi-user implementation of the
software, based on experience at the NJDEP, was estimated to
be $190,500. At the time of the demonstration, NJDEP had a
40-user license and had received over 16,000 submissions from
hazardous waste sites throughout the state. This estimate
assumed the equivalent of two full-time staff to manage and
operate the software. Data entry was performed by the
equivalent of three full-time student co-ops. This estimate
included:
• site preparation
• equipment (software and hardware purchase/upgrades)
startup and fixed costs
first year operating costs (primarily labor)
• supplies
• maintenance
The total time to implement this technology, including setup and
training, is estimated to be at least 1 year, although this number
will vary greatly depending upon the resources allocated to
system setup and the experience of the personnel involved. ••
Total costs for a smaller-scale, multi-user application were
estimated at $45,000 based on information supplied by the
Colorado Department of Health. This estimate assumed part
time operation by two permanent employees and data entry by
temporary employees.
The cost to implement this technology will be highly site-
specific depending upon the number of modules and users
required, the current availability of computer equipment, the
amount of data processed, and the familiarity of personnel with
basic scientific software. The cost estimates do not include
operating costs for successive years.
The reader is cautioned that, due to the rapid nature of software
development, the versions of most EQulS modules utilized
during this demonstration have since been superceded. The
developer claims that many of the minor problems noted during
this demonstration have been corrected or rendered moot
because of changes to the software (see Appendix B - Vendor
Claims). In some cases, these changes were reportedly ongoing
or completed by the time this evaluation was completed. Due to
scheduling and budgetary constraints, the SITE Program was
unable to verify these claims. However, as part of its routine
sales operations, EarthSoft provides software demonstrations.
Such demonstrations can be used as an opportunity for potential
customers to verify that the vendor has upgraded the system as
claimed.
The EQulS software was also evaluated based on the nine
criteria used to evaluate technologies in the Superfund feasibility
study process. Table ES-2 presents the results of this evaluation.
ES-2
-------
Table ES-2. Superfund Feasibility Study Evaluation Criteria for the EQuIS Software
a,b
Evaluation Criterion
Performance
Overall Protection of Human
Health and the Environment
Federal ARARC Compliance
Long-term Effectiveness and
Permanence
Reduction of Toxicity,
Mobility, and Volume through
Treatment
Short-term Effectiveness
Implementability
Cost"
State Acceptance
Community Acceptance
Does not directly impact this criterion; however, cost-effective data management and assurance of data quality
may result in overall protection of human health and the environment.
Does not apply. However, the software may be a useful tool in managing data required to demonstrate compliance
with ARARs. As of March 2002, EPA Regions 1 through 5 were using this software to assist in managing their
hazardous waste site data.
Does not directly impact this criterion; however, cost-effective data management and assurance of data quality
may result in the long term effectiveness of monitoring programs associated with remediation projects.
Does not apply.
Implementation of the EQuIS data management system may result in improvements in the short term effectiveness
of monitoring programs and remediation efforts.
Software impementability appears to be straightforward and well-supported by the EarthSoft help desk. As with
any complex software, at least one user with significant computer software experience will be required to manage
the implementation of each application. In addition, initial and some ongoing user training will be necessary for
all software users.
The cost of using this technology, for a multi-user license, is estimated at between $45,000 and $190,000 e
including startup and fixed costs (e.g., insurance), software and hardware purchases (costs can vary significantly
depending upon current availability at the user's facility), training, data entry (use of EDDs for current data will
reduce this cost, although historical data will typically require manual entry), supplies, and system operation and
maintenance (only first year operating costs are included). The cost for a single-user aplication (e.g., small
environmental consultant), with a license for the six EQuIS applications evaluated during this demonstration,
would be approximately $11,000 for the software. Assuming that the consultant had the necessary hardware, the
only additional costs would be vendor-supplied training costs, labor for implementation of the system, and any
applicable overhead costs.
The willingness of vendor to perform software demonstrations should increase acceptability Currently, the
EQuIS software is being utilized by 10f states to manage environmental data for hazardous waste cleanup sites.
Use of the software to provide graphical representations of site data should assist site managers in raising
community awareness and acceptance regarding selected remediation approaches.
a Based on the results of the SITE demonstration using data from the Martin Aaron site in New Jersey.
b Information contained hi this table should not be used without examining all other parts of this evaluation report.
c ARARs = Applicable or Relevant and Appropriate Requirements . .
d Actual cost of the technology is site-specific and dependent on the characteristics of the site data and upon the qualifications of the personnel implementing the
estimates are based on information supplied by two states: Colorado and New Jersey. Colorado has a license for five users (additional details on the
use of the software are provided in Section 3.4). New Jersey has a 40-user license and has received over 16,000 submissions from hazardous waste sites throughout
X 0? MarSS " « 'S2£>Sg5&$ De,aware NREC, Florida DEP, Mississippi DEQ, Nebraska DEQ, Nevada DEP, New Jersey DEP, New
York DEC, Pennsylvania DEP, Rhode Island DEM, and West Virginia DEP.
ES-3
-------
-------
SECTION 1
INTRODUCTION
An evaluation of the Environmental Quality Information System
(EQuIS) software system was conducted by the U. S.
Environmental Protection Agency (EPA) National Risk
Management Research Laboratory (NRMRL) under the
Superfund Innovative Technology Evaluation (SITE) Program.
EarthSoft, Inc., the developer of the EQuIS data management
system, was responsible for providing the software and training
of evaluation personnel during the demonstration. Science
Applications International Corporation (SAIC) was the SITE
Program contractor for the implementation of this demonstration
and conducted the evaluation of the EQuIS software and report
writing activities in support of this effort,
This introduction provides an overview of (1) the SITE Program,
(2) the purpose of this Innovative Technology Evaluation Report
(ITER), (3) the EQuIS software, (4) the data set utilized for the
evaluation, (5) demonstration activities, (6) demonstration
results, and (7) additional sources of information on the SITE
Program and the demonstration. Section 2 presents an
applications analysis for the technology. Section 3 discusses the
results of an economic analysis of the technology. Section 4
presents the results of the demonstration. Section 5 discusses
requirements to be considered when using the technology.
Section 6 discusses the status of the technology. Appendix A
contains case studies. Appendix B contains vendor claims for
the technology.
1.1 BRIEF DESCRIPTION OF PROGRAM
AND REPORTS
In 1986, the EPA Office of Solid Waste and Emergency
Response (OSWER) and the Office of Research and Develop-
ment (ORD) established the SITE Program to promote the
development and use of innovative technologies to clean up
Superfund sites across the country. Now in its fifteenth year, the
SITE Program is helping to provide the treatment technologies
necessary to implement new Federal and State cleanup standards
aimed at permanent remedies rather than quick fixes. The SITE
Program is composed of four major elements: the Demonstra-
tion Program, the Emerging Technology Program, the
Measurement and Monitoring Technologies Program, and the
Technology Transfer Program.
The major focus has been on the Demonstration Program, which
is designed to provide engineering and cost data for selected
technologies. To date, the Demonstration Program projects have
not included funding for technology developers. EPA and
developers participating in the program share the cost of the
demonstration. Developers are responsible for demonstrating
their innovative systems at chosen sites, usually Superfund sites.
EPA is responsible for sampling, analyzing, and evaluating all
test results. The final product of each demonstration is an
assessment of the technology's performance, reliability, and cost.
This information is used in conjunction with other data to select
the most appropriate technologies for the cleanup of Superfund
sites.
Developers of innovative technologies apply to the
Demonstration Program by responding to EPA's annual solicita-
tion. EPA also accepts proposals any time a developer has a
Superfund waste treatment project scheduled; To qualify for the
program, a new technology must be available as a pilot- or full-
scale system and offer some advantage over existing
technologies. Mobile technologies are of particular interest to
EPA. This is the second SITE demonstration of a data
management system.
Once EPA has accepted a proposal, EPA and the developer work
with the EPA regional offices and State agencies to identify a
site containing waste suitable for testing the capabilities of the
technology. EPA prepares a detailed sampling and analysis plan
designed to evaluate the technology thoroughly and to ensure
that the resulting data are reliable. The duration of a
demonstration varies from a few days to several years, de-
pending on the length of time and quantity of waste needed to
assess the technology.
The second element of the SITE Program is the Emerging
Technology Program, which fosters the further investigation and
development of treatment technologies that are still at the
laboratory scale. Successful validation of these technologies can
lead to the development of a system ready for field
-------
demonstration and participation in the Demonstration Program.
The third component of the SITE Program, the Measurement and
Monitoring Technologies Program, provides assistance in the
development and demonstration of innovative technologies to
improve characterization of Superfund sites.
The fourth component of the SITE Program is the Technology
Transfer Program, which reports and distributes the results of
both Demonstration Program and Emerging Technology
Program studies through ITERs and abbreviated bulletins. A
Technology Evaluation Report (TER) was also developed for the
EQuIS SITE demonstration. The TER provides greater detail on
the demonstration and presents a complete package of
measurement results. The TER is on file at EPA NRMRL.
1.2 PURPOSE OF THE ITER
The ITER provides information on the EQuIS software and
includes a comprehensive description of the demonstration and
its results. The ITER is intended for use by EPA remedial
project managers (RPMs) and on-scene coordinators,
contractors, and others involved in the remediation decision-
making process and in the implementation of specific remedial
actions. The ITER is designed to aid decision makers in
determining whether specific technologies warrant further
consideration as applicable options in particular cleanup
operations. To encourage the general use of demonstrated
technologies, EPA provides information on the applicability of
each technology to specific sites and wastes. The ITER includes
information on cost and site-specific characteristics. It also
discusses advantages, disadvantages, and limitations of the
technology.
Each SITE demonstration evaluates the performance of a
technology in treating a specific waste, or in this case, in
managing environmental data. The environmental data at other
sites may differ from those examined during this demonstration.
Therefore, successful demonstration of a technology for these
site data does not necessarily ensure that it will be applicable at
other sites. Results from the demonstration may require
extrapolation to estimate the operating ranges in which the
technology will perform satisfactorily. Only limited conclusions
can be drawn from a single demonstration.
1.3 TECHNOLOGY DESCRIPTION
The EQuIS software is designed as an advanced environmental
data management and analysis platform for monitoring and
remediation projects. According to EarthSoft's web page at the
time of the demonstration, the EQuIS system consists of 7
applications, 5 modules, and 12 interfaces which link with
commercial off-the-shelf (COTS) packages that perform a
variety of graphical and 3-D visualization functions. In
consultation with the EQuIS vendor, a total of six primary
applications, modules, and interfaces were tested in this
evaluation. These were: Chemistry, Geology, ArcView
Geographic Information System (GIS) Interface, Data
Verification Module (DVM), CrossTab Report Writer, and
Electronic Laboratory Data Checker (ELDC), In order to
simplify the discussion, these six software applications,
modules, and interfaces are all referred to as modules in this text.
These modules were chosen for testing because they are the most
commonly used. A brief description of each software module,
and the functions that the vendor claims the module performs, is
presented below. A schematic diagram of the system that was
evaluated is presented in Figure 1. For the EQuIS modules
tested, the entire project database is generated as a Microsoft
Access database which can be queried using standard MS-
Access or SQL commands. The vendor states that an Oracle
database can be substituted for the MS-Access database without
affecting module functionality.
EQuIS Chemistry (version 3.3) offers a user interface and
Microsoft Access relational database that can be used to
organize and manage sampling information and chemical
analytical data generated in the field or by commercial
laboratories. Sample information, test data, and results can be
input manually or imported into the EQuIS Chemistry database.
From the database, queries can be generated and data can be
interfaced with other analytical software for visualization,
graphing, and reporting.
EQuIS Geology (version 2.3) is a companion module that
manages geological and geotechnical information. Soil boring
and sample data can be entered manually or imported directly
into a project database. Site information is categorized by
location. Data may be exported for reporting, 3-dimensional
(3D) visualization, contouring, borehole logging, solid modeling,
or groundwater flow modeling. It should be noted that version
2.3 was a beta version; me vendor opted to have a beta version
with better capabilities evaluated, knowing that an increased
likelihood of system errors was likely.
The EQuIS ArcView GIS Interface (version 1.6) has features
that allow users to query and view EQuIS Chemistry and
Geology data inside of the ArcView environment (see next page
for a description of the ArcView Software). This GIS Interface
consists of linked tables, the EQuIS Location View, and a menu
system that supports a number of activities. The module tested
was based on ArcView version 3.2. As of March 2002,
EarthSoft's EQuIS for ArcGIS, built upon ESRTs ArcGIS 8.1
platform, had been released. However, due to schedule and
financial constraints, this module could not be evaluated as part
of the SITE demonstration.
The ELDC (version 2.6) allows users to check electronic deliv-
-------
EQuIS Arc View Data
EQuDS Geology Data
EQuISChenistryData
Figure 1-1. Flow diagram for data through the EQuIS software
-------
erables before they are incorporated into a database. It is
designed to identify electronic data deliverable (EDD) problems
before data are used. The ELDC checks data against a defined
EDD format; no computations or data validation are performed.
The EQuIS DVM (version 1.2^ provides data review and partial
validation in accordance with selected EPA Guidelines (e.g.,
CLP program) and analytical program requirements from other
agencies. It should be noted that in late 2000, subsequent to
initiation of the demonstration, this module was replaced by
EarthSoft with the Data Quality Management (DQM) module.
Due to schedule and financial constraints, the DQM could not be
evaluated as part of the SITE demonstration. (See the Vendor
Claims in Appendix B for the vendor's rationale for replacing
the DQM).
The EQuIS CrossTab Report Writer (version 1.6) is a tool
that can be used in conjunction with the EQuIS Chemistry
module to design and create various complex cross tab reports
without having to re-key or cut and paste data.
In addition to the six EQuIS modules described above, the
interface to six commercial-off the shelf (COTS) packages that
are integrated with EQuIS, were also evaluated. These packages
were:
• ArcView (version 3.2);
QMS (Ground Water Modeling System);
• Surfer (Contouring, Gridding, and Surface Mapping
Package);
LogPLot98 (well log data plotting);
Rockworks99 (geologic mapping tools); and
• EVS (Environmental Visualization System).
ArcView is a desktop mapping and analysis tool that allows
users to visualize, explore, query, and analyze data spatially.
ArcView works with geo-spatial data, both vector and raster
formats. The system can perform both spatial and tabular
queries. It can be customized to change the graphic interface
utility (GIU) automate a series of functions, and add new
functions.
The Department of Defense Groundwater Modeling System
(GMS) integrates and simplifies the process of groundwater flow
and transport modeling by integrating a number of tools. GMS
supports the following models: MODFLOW, MODPATH,
MT3D, FEMWATER, SEEP2D, andRTSD.
Surfer is a contouring and 3D surface plotting program that runs
under Microsoft Windows and has extensive variogram
modeling capabilities. Surfer converts data into contour,
wireframe, vector, image, shaded relief, and post maps. Maps
can be customized to produce user-specific output.
The LogPlot98 program is the newest version of the log plotting
software published by RockWare, Inc. It reads user-created data
files that contain descriptive, quantitative, and other data, and
plots these data as graphic strip logs. The format or "blueprint"
of the logs (the components and their locations) is designed
within the LogDesign program, included with LogPlot98.
The RockWorks99 program is a Windows application that reads
a variety of data types (stratigraphic formation elevations, XYZ
data, lineations, etc.) from a built-in spreadsheet-style data
window. It offers graphic output of a variety of maps (point,
contour, color-filled, and 3D surface maps), strip logs, hole-to-
hole cross sections, fence diagrams, 3D stratigraphic diagrams,
3D solid model diagrams, general data plots, rose and stereonet
diagrams, and more. RockWorks also offers isopach, volume,
and trend surface residual computations that are presented as
reports. Advanced 2-dimensional (2D) and 3D volumetrics
include thickness, overburden, and data filters.
EVS has been designed to provide streamlined reproducible
methods to complete visualization and analyses. The modular
structure of the program allows the user to graphically construct
his/her own visualization programs, which can be saved as
applications for subsequent use with the same or different data
sets.
1.4 DESCRIPTION OF THE
DEMONSTRATION DATA
In order to properly evaluate the EQuIS software, a
comprehensive data set was required to ensure that all major
system functions were utilized and evaluated. The data set from
the Martin Aaron site in New Jersey was selected because it was
a comprehensive data set that was already available in an
electronic format compatible with EQuIS. NJDEP had
previously input data into its data management system and
prepared an EDD. This file was then forwarded to EarthSoft for
review and correction of data entry and data formatting errors.
The corrected file was then supplied to SAIC for use in
evaluation of the EQuIS software. '
Available site data consisted of information presented in the
Draft Final Remedial Investigation (RI) Report dated February
1999. Summary chemical analytical data were provided in
tables. These data included positive analytical results for VOCs,
SVOCs, metals, pesticides/polychlorinated biphenyls (PCBs),
and dioxins/furans in surface and subsurface soils as well as
both shallow and deep groundwater wells. Additional
information, including well boring logs and water level
measurement data, were provided in the appendices to this RI.
1.5 DESCRIPTION OF DEMONSTRATION
ACTIVITIES
A kickoff meeting was held on January 11, 2000 to verify the
-------
vendor claims, to define the demonstration project objectives,
and to discuss a software training session to be provided by the
vendor. The preliminary project objectives were re-defined
based upon input provided by EarfhSoft. These initial project
objectives were used by EPA's contractor to prepare the Quality
Assurance Project Plan (QAPP) after the software training was
completed.
EarthSoft customized its standard training module based upon
the qualifications of SAIC's evaluators. In late February 2000,
training was provided to four evaluators, SAIC's Work
Assignment Manager, and one other staff member assisting with
the project. EarthSoft supplied a training manual and
demonstration versions of its software and that of key COTS
products. EarthSoft staff demonstrated important functions of
all software to be tested while SAIC staff utilized the software
to mimic the actions of the trainer.
Subsequent to completion of software training, SAIC staff
intermittently utilized the software to gain further expertise in
software capabilities. Based upon knowledge of the software
functions, SAIC developed a QAPP which contained the test
plan (a series of matrices that identified key functions and their
purpose or capabilities, summarized a procedure to test the
identified function, presented the rationale for determining
whether the test was a success or failure, and provided a
template for entering test results in the electronic file). During
the QAPP development process, the project objectives were
modified based upon evaluator observations and input from the
vendor. The draft QAPP was first submitted to the vendor for
review and comment, and then to the EPA QA Program.
Comments were incorporated, and the QAPP was finalized on
November 3,2000.
Over the next six months, SAIC evaluated each of the modules
designated for testing. Results were recorded in the electronic
matrices; additional documentation was assembled in two forms:
1) electronic copies of input screens and results (output tables
and reports, error message screens, and other documentation of
results), and 2)handwritten notes on procedures (including any
modifications that were required) and results that were recorded
in bound notebooks with numbered pages.
At the beginning of the evaluation of each module, all results
were forwarded to SAIC's project QA Coordinator for a review.
This review evaluated completeness of documentation, whether
the results adequately addressed project objectives as stated in
the test matrix, and consistency among the four evaluators. The
QA Coordinator also reviewed any changes in evaluation
procedures to determine whether there was an impact on the
ability to evaluate project objectives. After this initial review,
the QA Coordinator randomly reviewed a percentage
(approximately 5 to 10 percent) of the evaluation results to
ensure completeness and consistency. The evaluation was
completed in early April 2001.
1.6 SUMMARY OF DEMONSTRATION
RESULTS
The results obtained in support of the primary objective are:
In general, the EQuIS ELDC, Chemistry, Geology,
ArcView Interface, and CrossTab Report Writer system
functions were fully operational and had no major
programming errors. For these five modules, major
system functions were successfully tested. A few
functions did not operate as expected, but these
functions were either obsolete (had been removed or
were planned for removal in future software modules)
or were judged by the evaluators to be minor functions
that did not impact the overall usability of the software.
The DVM module was only partially functional;
several key functions did not perform as claimed by the
vendor. Most system functions for all six modules
were easy to use for anyone familiar with Microsoft
Windows.
Based upon the limited testing planned under this
demonstration, the EQuIS Chemistry, Geology, and
ArcView Interface modules were determined to be in
conformance with data exchange standards, as judged
by the ability to import data and export data to other
commercial software.
The results obtained in support of the secondary objectives are:
« The total cost for a large-scale, multi-user
implementation of the software, based on experience at
the New Jersey Department of Environmental
Protection (NJDEP), was estimated to be $190,500. At
the time of the SITE demonstration, NJDEP had a 40-
user license and had received over 16,000 submissions
from hazardous waste sites throughout the state. This
estimate assumed the equivalent of two full-time staff
to manage and operate the software. Data entry was
performed by the equivalent of three full-time student
co-ops. This estimate included site preparation,
equipment (software and hardware purchase/upgrades),
startup and fixed costs, first year operating costs
(primarily labor), supplies, and maintenance. Total
costs for a smaller-scale, multi-user application were
estimated at $45,000 based on information from the
Colorado Department of Health. This estimate
assumed part time operation by two permanent
employees and data entry by temporary employees.
The cost estimates do not include operating costs for successive
years. The cost to implement this technology will be highly site
-------
specific depending upon the number of modules and users
required, the current availability of computer equipment, the
amount of data processed, and the familiarity of personnel with
basic scientific software. For example, the cost of a single-user
application (e.g., a small environmental consulting firm) with a
single license for each of the six EQuIS applications evaluated
during this demonstration, would be approximately $11,000 for
software. Assuming that the consultant had the necessary
hardware, the only additional costs would be vendor-supplied
training costs, labor for implementation of the system, and any
applicable overhead costs.
The reader is cautioned that, due to the rapid nature of software
development, the versions of EQuIS modules utilized during this
demonstration have since been superceded. The developer
claims that many of the minor problems noted during this
demonstration have been corrected or rendered moot because of
changes to the software. In some cases, these changes were
reportedly ongoing or completed by the time this evaluation was
completed (see Appendix B, Vendor Claims, for additional
information). Due to scheduling and budgetary constraints, the
SITE Program was unable to verify these claims. However, as
part of its routine sales operations, EarthSoft provides software
demonstrations. Such demonstrations can be used as an
opportunity for potential customers to verify that the vendor has
upgraded the system as claimed.
• The Alternative Treatment Technology Information
Center (ATTIC) is a comprehensive, automated
information retrieval system that integrates data on
hazardous waste treatment technologies into a
centralized, searchable source. This data base provides
summarized information on innovative treatment
technologies. The modem access number is (513) 569-
7610. Voice assistance is available at (513) 569-7272.
The TelNet number is CINBBS.CIN.EPA.GOV.
• Version 5.0 of the Vendor Information System for
Innovative Treatment Technologies (VISITT) data
base contains information on 346 technologies offered
by 210 developers. VISITT can be down-loaded from
www.prcemi.com/visitt. Technical assistance or a disk
copy of VISITT can be obtained by calling (800) 245-
4505.
The OSWER Cleanup Information (CLU-IN)
electronic bulletin board contains information on the
status of SITE technology demonstrations. The system
operator can be reached at (301) 589-8268. Modem
access is available at (301) 589-8366 or www.clu-
in.com.
Technical reports can be obtained by contacting EPA-NRMRL's
Technology Transfer Branch, 26 West Martin Luther King
Drive, Cincinnati, Ohio 45268 at (513) 569-7562.
1.7 KEY CONTACTS
Further information concerning the EQuIS software described in
this report can be obtained by contacting the individuals listed
below:
1. EPA Project Manager for the SITE Demonstration:
Richard fillers
U.S. Environmental Protection Agency
National Risk Management Research Laboratory
26 West Martin Luther King Drive
Cincinnati, Ohio 45268
Phone: (513)569-7809
Fax:(513)569-7676
E-mail: eilers.richard@epa.gov
2. Technology Developer Contact:
Mitch Beard, President
EarthSoft, Inc.
Cantonment, FL
Phone: (800) 649-58855
E-mail: mbeard@EarthSoft.com
Information on the SITE Program is also available through the
following on-line information clearinghouses:
-------
SECTION 2
TECHNOLOGY APPLICATIONS ANALYSIS
This section provides information on the ability of the EQuIS
software to meet regulatory and operational requirements
associated with the remediation of Superfund sites. Subsection
2.1 presents a discussion of the considerations associated with
seven major regulatory programs. The operability, applicability,
key features, availability, site support requirements, and
limitations of the system are discussed in Subsections 2.2
through 2.8.
2.1 REGULATORY CONSIDERATIONS
For typical treatment technology evaluations, this subsection
discusses seven major regulatory programs, starting with the
Comprehensive Environmental Response, Compensation and
Liability Act (CERCLA). CERCLA requires compliance with
all applicable or relevant and appropriate requirements
(ARARs), providing the entrance point for the other regulations
discussed in this subsection. Other regulatory programs include
CERCLA, and the Resource Conservation and Recovery Act
(RCRA), Clean Air Act (CAA), Safe Drinking Water Act
(SDWA), Clean Water Act (CWA), Toxic Substances Control
Act (TSCA), and Occupational Safety and Health Act (OSHA).
Each statute can have corresponding State or local laws that are
more stringent or broader in scope than analogous Federal
regulations.
However, the EQuIS software does not treat hazardous
materials; rather it is used to manage data from liquid and solid-
phase materials. Therefore, EQuIS is only applicable for data
management and reporting requirements under these statutes.
2.2 OPERABILITY OF THE SYSTEM
The EQuIS software is described in detail in Subsection 1.3.
The core component of the system is the environmental
chemistry data management system. It is written in Visual Basic
and uses the Microsoft Access database engine. EQuIS
Chemistry offers a user interface and relational database
warehouse to organize chemical field and lab data. Data can be
input manually or imported directly into EQuIS Chemistry's
database. Once data have been entered, queries can be generated
and data interfaced with industry-standard products for
visualization, graphing, and reporting. An interface with
ArcView is also available.
EQuIS Geology offers a relational database warehouse to
organize geologic, geotechnical, and hydrogeologjc data. Soil
boring and sample data can be entered manually or imported
directly into the project database. Once data have been entered,
queries can be generated and data interfaced with several
different industry-standard products for visualization, modeling,
boring logs, cross sections, fence diagrams, and reporting.
EQuIS Geology currently interfaces with the following systems:
• GMS - Department of Defense (developed by Brigham
Young University)*
LogPlot98 - Rockware*
Rockworks98 - Rockware*
• Surfer - Golden Software*
• Groundwater Vistas - Environmental Simulations
EVS - C-Tech*
gTNT
ArcView GIS - ESRI*
* - these were evaluated in this demonstration
Site information is categorized by location. At each location,
well construction and transient water level measurement
information may be defined. Additionally, stratigraphy and
continuous vertical trace sample data such as CPT data may be
stored and modified. Point parameters such as porosity,
hydraulic conductivity, and organic carbon content may be
specified for discrete samples at any number of vertical
locations. All data may then be exported for visualization and
analysis, such as contouring, borehole logging, solid modeling,
or groundwater flow modeling.
The EQuIS ArcView Project Interface permits users to view
EQuIS project data in the ArcView environment. It also allows
users to query and view EQuIS Chemistry and Geology data
inside of ArcView. The Interface consists of linked tables, the
EQuIS Location View, and a menu system that supports a
-------
number of activities.
The EQuIS ELDC is designed to assist in checking EDO
(Electronic Data Deliverable) files to ensure smooth importing
into the EQuIS Chemistry module. It can be useful for labs that
are submitting deliverables to users, as well as to the users who
are importing the files. ELDC can check for common data
loading problems including duplicate data, data of the wrong
type, and required data that are missing. It can also check
selected fields against a predefined list of allowed values.
ELDC performs most of the same checks as EQuIS Chemistry
does in its Import function. ELDC includes on-line help,
including context sensitive help.
The EQuIS DVM provides data review and validation in
accordance with quantifiable sections of the EPA Functional
Guidelines and CLP programs, as well as other analytical
program requirements from other agencies. Data in the EQuIS
Chemistry module are reviewed and MS Word or HTML reports
for contamination blanks, precision, accuracy, detection limits,
and surrogate recoveries are produced. Flags are written back to
the Chemistry data management system. All logic and rules are
documented and editable. (Note: as mentioned in Section 1, the
DVM has been replace with a new module, the DQM.)
The EQuIS CrossTab Report Writer is a tool that can be used in
conjunction with EQuIS Chemistry. The interface allows users
to create complex cross tab reports - using data from existing
EQuIS Chemistry project databases. The EQuIS CrossTab
Report Writer allows users to design various types of cross tab
reports using pull-down menus to select column and row
content.
2,3 TECHNOLOGY APPLICABILITY
The applicability of the technology is fairly well established.
According to the developer, more than 2,000 units (one or more
software modules each) have been purchased since its first
release in 1996. As of the publication of this document, the
vendor asserts that the EQuIS software is being utilized by EPA
Regions 1 through 5; the states of Colorado, Delaware, Florida,
Mississippi, Nebraska, Nevada, New Jersey, New York;
Pennsylvania, Rhode Island, and West Virginia. In addition, the
software is reportedly being applied by numerous environmental
consulting firms throughout the U.S. and several DoD and DOE
facilities, including Argonne, Rocky Flats, several Naval
Shipyards, Warner Robbins AFB, several Port Authorities, and
Army Corps of Engineers Districts in Sacramento and Korea.
Case studies in Appendix A summarize the results of the use of
the technology at different sites. Appendix B contains vendor
claims for the technology. These case studies and many of the
vendor claims have not been independently evaluated by EPA-
NRMRLorSAIC.
2.4 KEY FEATURES OF THE EQuIS
SOFTWARE
EarthSoft's EQuIS software is an environmental
management platform written in Visual Basic. It is designed to
warehouse and manage chemical, geologic, geotechnical, and
hydrogeological data, including field sampling data and field and
laboratory analytical results. Other important features of the
software include:
Uses Microsoft Access as a relational database
organize and query data
to
• Provides a limited data verification and validation
function to document data quality
• Offers a user interface to import and export data for
visualization, graphing, and reporting through a number
of existing commercial software products
• Allows users to view and manipulate data in the
ArcView environment
Specific functions of various modules were described in Section
2.2.
2.5 AVAILABILITY OF THE
TECHNOLOGY
The EQuIS software is readily available and can be obtained
directly from EarthSoft's web site. A number of environmental
consulting firms also provide EQuIS software, as well as support
for the installation, customization, and utilization of the
software, under agreements with EarfhSoft. It should be noted
that EarthSoft no longer supports the DVM. It has been re-
written as the DQM.
2.6 EASE OF USE
Li general, the software is easy to use. Most system functions
were easy to use for anyone familiar with Microsoft Windows.
The ease of use of the functions tested was facilitated by the
graphical user interface. Most operations were point and click.
Many operations had confirmation messages, informing the user
that a particular operation was about to take place and providing
the option to not proceed on with the operation. Some were
helpful and some were not. Additional information regarding
the ease of use of specific modules and functions is reported in
Section 4.3.
2.7 SITE SUPPORT REQUIREMENTS
No significant additional site support requirements will be
needed for the typical application of EQuIS by a consulting firm
or government agency. Complex applications and efforts to
integrate existing data may require additional commitment of
-------
resources or outside expertise to implement.
2.8 LIMITATIONS OF THE
TECHNOLOGY
The EQuIS software, like most comprehensive data management
systems, requires personnel familiar with, or who can be trained
in, the use of Microsoft Windows or similar menu-based
software. In addition, anywhere from three full days to several
weeks of training will be required (depending on user experience
and aptitude, as well as the complexity of the application) to
adequately implement this software. Again, based on the
experience of the evaluators, this is consistent with other
complex environmental data management software.
Some important data verification and validation procedures are
not addressed by the DVM version tested. Among these are
chain of custody reviews, temperature of samples as received by
the laboratory, instrument performance data, internal standards,
external standards, retention time windows, interference results,
serial dilutions, appropriate selection of constituent data from
subsequent dilutions, and selection of best results for samples re-
extracted and /or re-analyzed due to
QC performance problems. These items would still have to be
addressed by a person performing a manual review of the project
data. Limitations of this and other specific software functions
are described in Section 4.3.
Program or site-specific trials are recommended to determine the
effectiveness of software for each application.
2.9
l.
2.
3.
REFERENCES
CERCLA/Superfund Orientation Manual. U.S.
Environmental Protection Agency. EPA/542/R-92/005,
October 1992.
Superfund LDR Guide #5 Determining When Land
Disposal Restrictions (LDRs) are Applicable to
CERCLA Response Actions. U.S. Environmental
Protection Agency. OSWER Directive 9347.3-OSFS,
July 1989.
Guidance on Remedial Actions for Superfund Sites
with PCB Contamination. U.S. Environmental
Protection Agency. EPA/540/G-90/007, August 1990.
-------
SECTIONS
ECONOMIC ANALYSIS
3.1 INTRODUCTION
The primary purpose of this economic analysis is to estimate the
costs for using the EQuIS software on a commercial scale .for
advanced environmental data management and analysis
regarding monitoring and remediation projects.
3.2 BASIS OF ECONOMIC ANALYSIS
The cost analysis was prepared by breaking down the overall
cost into a subset of the 12 standard SITE cost categories. The
cost categories and the areas that each of them generally
comprise are listed in Table 3-1. The basis for the economic
analysis was derived from two sources: the New Jersey
Department of Environmental Protection and the Colorado
Department of Health
and Environment (CDPHE). These two sources reflect,
respectively, a large- and small-scale, multi-user implementation
of EQuIS with associated cost estimates at the high and low end.
Because some of the cost categories are very site specific, costs
for these categories should be used with caution., Values
presented in this section have been rounded to a realistic
number of significant figures.
Costs for a single-user license for the six EQuIS modules
evaluated during the SITE demonstration would be
approximately $11,000 for software. Assuming that the
consultant had the necessary hardware, the only additional costs
would be vendor-supplied training costs, labor for
implementation of the system, and any applicable overhead
costs.
3.3 ISSUES AND ASSUMPTIONS
This subsection summarizes the issues and assumptions of the
economic analysis for this study. Subsections 3.3.1 through
3.3.12 describe assumptions that were made in determining
project costs for the 12 cost categories. Because this evaluation
was performed on a software product, several cost categories
typically used for a physical or chemical process were deemed
not applicable to the economic analysis for an environmental
Table 3-1. Twelve Cost Categories for the EQuIS SITE Demonstration
1. Site Preparation
2. Permitting and Regulatory
• Not applicable
3. Equipment
• Computer hardware and software
4. Startup and Fixed Costs
• Working capital
• Insurance
• Contingencies
5. Operating Costs
• Labor
• Management costs
• QA labor
6. Supplies
• Operating supplies
7. Consumables
8. Effluent Treatment/Disposal
• Not applicable
9. Residuals
• Not applicable
10. Analytical services
• Not applicable
11. Modifications, repair, and replacement
• System maintenance
• Software upgrades
• Hardware upgrades
12. Site demobilization
• Not applicable
10
-------
information management system. These included: (3.3.2)
permitting and regulatory costs; (3.3.8) effluent treatment and
disposal; (3.3.9) residuals and waste shipping, handling and
transport; (3.3.10) analytical services; and (3.3.12) site
demobilization. The remaining cost categories are discussed in
detail below.
Additional issues and assumptions regarding data management
costs are described below. These were derived from a private
consulting firm's experience with EQuIS as well as the
experience of the CDPHE.
According to one consulting firm's experience with EQuIS, with
respect to data management costs, there is some time involved
with establishing business practices to take advantage of the
software defining standards, data flow, etc. That can be brief or
very lengthy depending on the organization or use. Once that is
determined, for new data with perfect lab EDDs and using the
checking tools it should take no longer than 5 to 15 minutes to
load a data package.
This firm prefers to manually enter the chain-of-custody
information. They report the use of DVM and ELDC
significantly reduces (50 - 75 percent) the amount of time spent
doing data quality assessments from the old-fashioned way.
For loading historical data, it is assumed that the data are either
not in digital form or are in a digital form that will require
significant reformatting before data migration. Conversion can
take quite a bit of time unless it's already in a relational database
format (anywhere from 2 to 20 hours to do the conversion).
Most data entry/conversion time is related to finding information
not readily available (What is the sample date? Which location
does it belong to? What's the CAS number). As a rule though,
it takes about one-half of an hour per sample. It should be noted
that these same issues will apply to any other electronic
database, not only EQuIS.
The division in CDPHE using EQuIS consists of 120 people
regulating Superfund, RCRA, Solid Waste, Voluntary Cleanup,
and State CERCLA. CDPHE implemented EQuIS two years
ago with one individual devoted mainly to the database
administration. This individual is a geologist and Visual Basic
programmer. CDPHE has implemented EQuIS on a site-by-site
basis. Historic data are loaded on a site-by-site basis by student
interns at $1 I/hour from local universities who have advanced
knowledge of geology, chemistry, or GIS to enter the data.
CDPHE has not fully implemented an Electronic Data
Deliverable throughout the Division's regulated sites, but have
gotten a full EDO submittal implemented from several of their
main labs. They are implementing EQuIS on a distributed basis
where the project managers/scientists are responsible for
learning the use of the system and the evaluation of their own
data. They have focused on a power user approach, training first
those with the proven aptitude and enthusiasm and with projects
ready to utilize the system.
3.3.1 Site Preparation Costs
Since EQuIS is a computer-based environmental information
management system, the potential site preparation costs
associated with EQuIS could range from simply setting up a
single computer in an office to run EQuIS to the establishment
of a computer facility to house servers, data storage devices,
workstations, network equipment, and peripherals (printers,
plotters). In the simple case, there may be no preparation cost at
all since the system could be easily set up in an existing office
with no special requirements. In the case of establishing a new
computer facility, the costs of setting up a computer room would
involve: 1) proper environmental controls (i.e., temperature and
humidity), 2) establishment of telecommunication infrastructure
(voice and data lines), 3) purchase of furniture for setting up the
computers and peripherals, and 4) purchase of universal power
supply (UPS) equipment. According to McKinsey and
Company, Inc., twenty percent of the costs for hardware is for
retrofitting - electrical and heating, ventilation, and air
conditioning (HVAC) upgrades. [1]
3.3.2 Permitting and Regulatory Costs
The costs associated with permitting and complying with
environmental regulations are not applicable to this SITE study.
No permits are required to set up or run EQuIS.
3.3.3 Equipment
The equipment costs associated with EQuIS are broken down
into two major components: software and hardware. Software
costs are separated into two categories: 1) EQuIS products and
2) third-party products that interface with EQuIS. Presented
below are exhibits that summarize the costs of these two
categories of software. This is followed by a discussion of
hardware costs.
3.3.3.1 EQuIS Products
A series of exhibits are presented below; these summarize the
costs of the EQuIS applications, modules, and interfaces. EQuIS
applications operate independently of other software, although
they can be integrated with other software. Modules operate on
EQuIS data to generate certain output which can be imported by,
or functions linked to, other software packages. Interfaces
provide a direct link to other software packages and utilize
EQuIS data within these software packages.
These costs were obtained from the EarthSoft website
rhttp://www.EarthSoft.com/Droducts/nrices.t).hp3) as of
February 2001.[2] The costs shown in these figures are for a
11
-------
single user license. EarthSoft provides discounted costs with
multi-user purchases.
333.2 EQuIS Applications
Figure 3-1 summarizes the costs associated with these EQuIS
applications: Chemistry, Geology, ELDC, Hydrology, DMR,
DUMPStat, and Carstat. For this project, only the Chemistry,
Geology, and ELDC modules were evaluated
3333 EQuIS Modules
Figure 3-2 shows the costs for the following EQuIS modules:
ArcView Interface, DVM, SiteMaster, CrossTab Report Writer,
and Completeness Checker. For this project, only the ArcView
Interface, DVM and CrossTab Report Writer were evaluated.
333.4 EQuIS Interfaces
Figures 3-3 and 3-4 below show the costs of EQuIS interfaces
to a variety of third-party software packages including: GMS,
EVS,
STATISICA, RockWorks, DUMPStat, Brio, DMR reports,
Groundwater Vistas, Logplot, gINT, Earthvision, CARStat, and
NJDEP Import/Export. These costs are for the interface only;
they do not include the cost for the third party software. For this
project, only the interfaces to GMS, EVS, Rockworks, and
LogPlot were evaluated.
Applications
[Discounts available with 'multi-user purchases,
ingle User License:
EQuIS Chemistry Includes a single user license of EQuIS Geology $ 6,000
EQuIS Geology $ 1,500
Electronic Lab Data Checker $1,000
EQuIS Hydrology '$ 1,000
EQuIS DMR $1,000
P.. nuioeL-i. Commercial Site: DUMPStat is sold on a per site (i.e. , _ ___
DUMPStat landfill) basis; each site must have a licensed copy. *2'500
Municipal Site: $ 2,000
CARStat $4,000
Figure 3-1. Cost of EQuIS applications
[^••BKZ*
EQuIS ArcView Interface $1,0001
I
EQuIS Data Verification Module $2,000 |
EQuIS SiteMaster $1,0001
jjijj EQuIS CrossTab Report Writer $700 I
3 EQuIS Completeness Checker , , *soo |
Modules
[Discounts available \vi1;tt iplif tiriiset piirttlase^, .]•.
Figure 3-2. Cost of EQuIS modules
12
-------
Interfaces
Spi:
[These prices do NOT include the 3"'*' party software.]
Single User License
CHEM
EVS
STATISTICA
RockWorks
DUMPStat
Brio
Create native GMS borehole and material files. Export borehole and $ 450
sample data from Geo to build solid models, create cross-sections, and
prepare groundwater flow models. Observation coverages for model
calibration can be prepared automatically using water level data.
Produce native EVS geology and chemistry data. $ 450
Produces flies for electronic import Into Statistica, We have written $ 400
the following automated statistical reports: Analysis of variance
(ANDVA), Box and Whisker plots, Parametrlc/Non-parametrIc
tolerance limits, Parametric/Non-pararnetrlc prediction limits, Kruskal-
Wallace non-parametric prediction limits, Wllcox rank-sunns, Time-series
charts, Tolerance Intervals, a Interwell and intrawell comparisons,
Export borehole drill data from EQuIS Geology and create strip logs, $ 350
cross- sections, fence diagrams, 2D contour/30 surface maps, simple
and advanced volumetrics. Images are easily printed and exported.
Produces files for electronic Import into DUMPStat, for SubTitle C and $ 400
D groundwater statistics,
Builds files for electronic import into Brio for advanced cross tab $ 500
reporting and data mining.
Figure 3-3. Cost of EQuIS interfaces
DMR Reports
H Groundwater Vistas
LogPlot
gINT
EarthVision
!H CARStat
NJDEP Import/Export
A collection of various reports written by EarthSoft. $1,000 for the $ 250
complete library,
Export geologic sample'data parameters to Groundwater Vistas far $ 350
groundwater flow model preprocessing.
Build LogPlot data flies Including stratigraphy, CPT-type data, geologic $ 350
samples, and well construction.
The new EQuIS Geology interface with gINT will make bore logs and $ 500
fence diagrams even easier, EarthSoft developers have collaborated
closely with gINT developers to produce a "live link" interface. That
means that once you have created the "live link" from your EQuIS
project to gINT, the data virtually exports Itself. Any new data
entered Into EQuIS Geology will automatically appear for reporting in
gINT. Just a few minor ODBC issues are being resolved before we can
release this exciting interface.
Export EQuIS data to EarthVision, * 80°
The new EQuIS Chemistry Interface with CARStat will make statistical $ 400
reporting even easier, The Interface will allow the user to create a
"live link" from CARStat to the EQuIS database. Any new data
entered Into EQuIS wll automatically be available for reporting in
CARStat,
Interface directly with NJDEP, * 35°
Figure 3-4. Cost of EQuIS interfaces
13
-------
333.5 Third-Party Products
Software prices were obtained only for the third-party packages
tested in this evaluation. These include: GMS, ArcView,
LogPlot; Rockworks, EVS, and Surfer.
Groundwater Modeling System (GMS)
The costs associated with obtaining the GMS software are
shown in Figures 3-5 through 3-7. Figure 3-5 shows costs for
various GMS packages. Figure 3-6 shows costs forindividual
GMS modules. Figure 3-7 shows costs for workstation and NT
Server hardware locks.
QMS for Windows
R*comm*nd*d Packages
BASIC Pkg (M'P.OrM.MODFLOW) $ 1 OQQ
tSODFLjpW Pkg CM.p.Sub.rt.Oild.o.ort.uODFLOW) $ 1,90Q
MODPATH Pkg CMjp.Sut.rf.Oild.0.ojl.WODFLOW,MODPATH) $ 2,200
MT3D Pkg (U.p.Sutuf,Orid.O«FLOW.MT3D) $ 2.3DO
MODFLOW/MODPATH/MT3D Pkg 5 3,350
FEMWATER Pkg cMjp.3ub*rf.M«ih.FeMwATER) $ -\ ,350
SEEP2D p.ond.o<°iQ $ 1,100
UTCHEM Pkg (Mjp.orid.o>eitit.uTCHEM) $ 1,900
QMS 3.1 Windows Packaga Prle* $ 6,260
Figure 3-5. Costs for GMS packages
lndlv£^»l Modules
Map
Subiurfaca Characterization coor.t,oi..TiM..so!idfl
M««h<30i3DM.ih Moaul..)
Grldc2D«.3DOildlll«)
GaOKatlltlca (20 &3D SuHtt Point Modul<9)
MODFLOW
MODPATH
MT3D
RT3D (|E>* l«qutf«» MT3D)
SEAM3D
FEMWATER
SEEP2D
PEST
UTCHEM
$
$
I
5
$
s
£
I
$
$
J
$
$
*
250
350
450
450
550
450
300
350
400
450
450
250
450
650
ArcView
The costs for the ArcView software are shown in Table 3-2.
The ArcView GIS shrink-wrap license agreement allows for
network use. Network use is limited to the number of seats
that the user has purchased.
Table 3-2. Costs for the ArcView software
Quantity
1-5
6-25
25-50
51-100
101-500
501+
Standard/Volume
List Price
(S)
1,195
1,076
956
837
657
598
Federal
List
Price
956
861
765
670
526
478
Reseller
Cost
(S)
717
646
574
502
394
359
LogPlot
The costs for the LogPlot software are shown in Figure 3-8.
Rockworks
The costs of the Rockworks software are shown in Figure 3-9.
Figure 3-6. Costs for GMS Individual Modules
ID #
LogPlot 2001 Pricing
Description
Plica
Unlv*r«lti«s -CO% Discount
Workstation Hardware Lock - $ 65
Network Server Hardware Locks
On* User - $ 65
Two Users - $ 85
Five Usars - $ 1OO p.
Twanty-fiva Users - $ !2S)O
Figure 3-7. GMS costs for workstation and NT Server
hardware locks
Figure 3-8. Costs of the LogPlot software
Environmental Visualization Software (EVS)
The costs of the EVS is shown in Figure 3-10.
Surfer
The costs of the Surfer software are shown in Figure 3-11.
14
-------
Rock\Vorks99 Pricing
ID # Description
100,02 RwkWorks99j SfeniSaS 5-user license (iteiwbafc or$-ji^~_~ -
IflO-O"? RockWofcksi^" Stanford Ifl-user MCR wsfft***''*'*'!5: »yWjgacfe)^
100-04 RockWorks99iAcaaeinicsuigleHqensft
100-06
" " "
100.08
9V4U4WIC/
,onrks99i Academic 3D W«al Pra - single usi
v._K-ufi»»iP«aqt Academic 3D VisvSl^m^ nmOk.lii!
user
Mita-
MJto
lflfl-11
(network not available)
IQfi-13 RockW«rks99i%gradfi:KockWaiKS5»»i»Hiicj».i(VB^»iii;r
100-14 RockWorl5s99: l^gi-ade: Anyprevims TOrsion|pfin0kWor&99
\ rtft t .& MocltWioz'l^^^i TfesTJiyCtei ^.oclcWbi.fes98 to, 30-vs^iisl Px^s
^UU-I^ ^r*^ ™ ~ ™*
It30-16 HotikWarks99: T^grade: Anypreraous TOrsionto 3D 'VIsiialEro
mo-it
Figure 3-9. Costs of the RockWorks software
EVS For
Arc View/
EVS For
Arc View
RENTAL.
t=vs> ror WKVUSW id a subset of EVS Standard, plus an AV3.1J3.2 j
extension that provides dozens of 3D volumetric visualization and ;
analysis tools for the mapping professional. These tools are EVS ;
applications that are launched directly from ESRPs ArcView CIS, yet
provide the supreme functionality of EVS gridding, estimation and ;
visualization modules. Use GIS queries or read In ASCII data to |
i make 3D volumetric models. i
Monthly rental rate for EVS For ArcView. Rate does not Include j
refundable $1OO deposit for Software Key.
EVS Standard
nvronme System modular analysis and
visualization software (under Windows NT/95/98) for the high-end
environmental, geologic or mining engineer. EVS provides user
friendly kriging routines to create fully three-dimensional models of
'' chemical distributions by geologic layer. Directs site assessment
j efforts by identifying locations of highest uncertainty and lowest
^statistical confidence. Representations can be isovolumes, colored
^surfaces, grids, arbitrary slices, visualization of vector data such as
streamlines of potential flow, isolines, etc. Volumes and masses of
chemical plumes can be calculated for the total system or by
geologic layer. Also creates three-dimensional fence diagrams
displaying geologic layers and chemical concentrations with up to
eight separate fence cross-sections simultaneously displayed DXF
.files can be imported and overlayed along with colored and labeled
i axes and a color legend. Includes a statistics module and a fully
functional EVS Viewer. Includes one year of software maintenance,
upgrades, and technical support.
$2,495;
$495 i
$4,995;
Figure 3-10. Costs of the EVS
15
-------
Surfer.
MapViawar,.,
Didgsr. ,
Demo CD-ROM (includes all products) No shipping charges
or DOWNLOAD FREE demos from the Internet -
wsvw.qoldBnsoflW8re.com
Upgrados
(Requires serial number from any previous version) SsnaLIf
Surfer. . f
Graphar , |
MtpViiwer |
Dldger.. |
Price
$599
$299
$249
$329
$ 10
Price
; $139
. fi QQ
--
6 $79
i: .$89
•si
Table 3-3. Comparison of PC Typical Costs (data from a
leading manufacturer)
Figure 3-11. Costs of the Surfer software
3.3.3.6 Hardware Costs
Any standard business personal computer (PC) in the price range
of $900 - $2,000 is capable of managing the EQuIS applications.
To use the other modules and interfaces that EQuIS takes
advantage of, such as ArcView or QMS, one should consider the
NT operating system and have at least 256 megabytes (MB)
random access memory (RAM). Table 3-3 shows a comparison
of typical PC costs from a leading manufacturer. Naturally, the
speed of the processor, along with available RAM, and other
specifications, impact the rapidity with which software functions
can be performed. During the demonstration, no attempt was
made to determine the most cost-effective hardware
configuration. The size of the data set, the labor rate for data
entry and management, and a host of other factors impact this
determination. Therefore, the selection of the most cost-
effective hardware configuration must be determined on a site-
specific basis.
The specific configuration associated with one of the systems
listed in Table3-4 (Pentium HI, 866 MHz) is shown in Figure
3-12. This system was selected as a low-cost configuration
that exceeded the basic software requirements.
3.3.4 Startup and Fixed Costs
Startup and fixed costs include the costs for training, working
capital, insurance, taxes, monitoring and contingencies.
Processor
Intel Celeron
Pentium HI
Pentium III
Pentium 4
Pentium 4
Pentium 4
Speed
700MHz
866MHz
933MHz
1.3 GHz
1.4 GHz
1.5 GHz
Cost
$878
1,028
1,462
1,887
2,037
2,237
The cost associated with on-site EQuIS training is $1200/day.
There are three categories of training offered; these are listed
below.
CONSULTANT/INDUSTRY TRAINING
• on site typically 3 days (for initial -training, subsequent
trainings vary)
• generally less than 10 students
• 1-2 instructors
PUBLIC SECTOR (EPA/STATE) TRAINING
• on site or at a central location
• typically 2 days, casual user
• 20, sometimes more students
• typically 2 instructors
OPEN TRAINING
• open to anyone, central location
• 2-3 days
• 10-30 students
• 2-3 instructors.
Working capital consists of the costs of borrowing capital for
operating supplies, utilities, and labor necessary to keep
EQuIS operating without interruption due to financial
constraints. The working capital for this system is based on
maintaining 2 months of payroll for labor and 1 month of
inventory for supplies. Based on the operating costs listed in
the next section (total of$119,000peryear), the working
capital costs would be approximately $ 21,000 (1/6 of
119,000 = $ 20,000 + $ 1,000 for supplies).
A contingency cost is included to cover additional costs
caused by unforeseen or unpredictable events, such as strikes,
storms, floods, and price valuations. The project contingency
cost is estimated to be 10 percent of the operating cost. Based
on the NJDEP experience, the operating cost is estimated to
be $119,000 per year (see Section 3.3.5); thus the contingency
cost is estimated to be $12,000.
16
-------
Speed:
Ulamoiy:
keyboard: '•
Monitor:
Video Card:
Hard Drive: :
:loppy Drive:
Operating System:
Mouse:
Network Card:
Modem:
DVD-ROM or CD-ROM Drive:
Sound Card:
Speakers:
Bundled Software:
Hardware Support Services:
CPU Procesor: Pentium® III Processor at 86SMHz
28MB 133MHz SDRAM
28M-P1 1-7001] ._ _ _ __
QuietKey® Keyboard
IW- [310-7002] _ „
7" (16.6" vi8waDle,.2Bdp)E770
E770 - t320-48'W] , ,„„.„
6MB ATI Rage 128 Pro
1BATI- [320-721 1]^ ._ _ „ „„.,„ „»„„
20GB ultra ATA Hard Drive (7200 RPM)
SO . P340-08001 ,
3.5" Floppy Drive
S-J340.2408]
Microsoft© Windows® 2000 Professional
W2K - |420-1887][422.
-------
3.3.9 Residuals
EQuIS is an environmental information management system. It
does not require the storage, transportation, or treatment of
residuals or wastes. Therefore the cost of residuals and waste
shipping, handling, and transport is not applicable.
3.3.10 Analytical Services
EQuIS is an environmental information management system.
It does not require a sampling or analytical program to be
established. Therefore the cost of analytical services is not
applicable.
3.3.11 Modifications, Repair, and Replacement
The costs associated with EQuIS modifications, maintenance,
repair, and replacement are listed in Figure 3-13. Training costs
were previously discussed in Section 3.3.4.
3.3.12 Site Demobilization
EQuIS is an environmental information management system.
The costs associated with site demobilization are not
applicable.
3.4 RESULTS OF THE ECONOMIC
ANALYSIS
This subsection summarizes the results of the economic analysis
of the EQuIS technology as applied to the environmental data
collected by two environmental agencies, the NJDEP and the
CDPHE.
The NJDEP reflects a large-scale, multi-user implementation of
EQuIS. NJDEP has received over 16,000 submissions to date
and moves approximately 500 files into EQuIS at a time.
NJDEP currently has a 40-user license. The storage
requirements are estimated to be 4-6 GB/year for receiving
submissions and making EQuIS data available. Based on the
NJDEP experience and the hardware/software costs presented in
this report the overall costs for implementing and operating
EQuIS are listed in Table 3-4.
The CDPHE represents a small-scale, multi-user implementation
of EQuIS. In Colorado,,EQuIS is operated by two staff: a
GIS/geologist and a VB programmer/geologist. Historic data are
converted on a site by site basis by student interns at $1 I/hour
•from local universities who have advanced knowledge of
geology, chemistry, or GIS to enter the data. Data uploading and
quality checks are performed by the VB programmer/geologist.
Staff commitment is approximately 15-20 percent. ; An
Electronic Data Deliverable is not fully implemented throughout
the Division's regulated sites, but a full EDD submittal has been
implemented from several of the main labs. EQuIS is being
implemented on a distributed basis where the project
managers/scientists are responsible for learning the use of the
system and the evaluation of their own data. Training is
supported. The focus is on a power user approach, training first
those with the proven aptitude and enthusiasm and with projects
ready to utilize the system. Based on this operational setting, it
is assumed that the small- scale operating cost = 10% of the
large scale operating cost ($119,000, see Table 3-4) = $ 11,900.
The costs estimated for a small-scale implementation of EQuIS
are shown in Table 3-5.
3.5 REFERENCES
1. McKinsey & Company, Inc. 2000. Identifying Costs
and Sources of Funding. Website UE.L,
hltp://w\y\v.Benton.org/I.ib,rary/
2. EarthSoft Inc., 2000. EarthSoft products and prices.
Website URL, htto://www.earthfo.ft.com
3. Putnam , S. Weaver , C. Thompson and M. Beard,
2000. The power of EQuIS in environmental
management and decision analysis: case studies in
Colorado, Website URL,
http://www.EardiSoft.com/news/articles.php3
4. Defina, J., Maitin, I, and Gray, A., 1998. New Jersey
Uses GIS To Collect Site Remediation Data, Arcuser
April-June, 1998, Website URL,
http://www.esri.eom/n.ews/ arcuser/
arcuser498/newiersev.html
18
-------
Maintenance:
On Site Training:
Data Migration:
Customization:
Custom Reports:
mmmaiBmn i •lillllllllllimillllllllill^^
ffiliBffilMpWMIlMiiBM
jjWTIBIMlllHlHlMilM^
MS3gSSSSBfaiMimm\mmm$B&eK*BBBSmmu&a*^^^^^^B^^^^^^^*&e'^^a*a'^n^^^^a~*m*u*"^a~mmma'"n
Annual maintenance on all EarthSoft software and interfaces (not 3rd party
software). Includes bug fixes, new releases, FTP access, and 'reasonable'
installation and implementation assistance. Assumes EarthSoft will support one
Power User. Renewed Annually.
Certified EQuIS trainers will visit your site to give hands-on training for EQuIS
software.
Migrate your existing data from existing sources into the EQuIS data
• structure,
Experienced EarthSoft developers will customize EQuIS to suite your specific
needs.
Experienced EarthSoft developers will create custom reports for your EQuIS
system to match your specifications.
^WMftB^WWiiMflffiSBBtt
SSHffiSttfiiS^IJKffiaBffffiffiilBaB^^SF
15% of list price'?
i
$ 1,200 / day j
$ 90 / hour |
$ 70 / hour
$ 35 / hour j
Figure 3-13. Cost of EQuIS services
Table 3-4. Costs Estimate for a Large-Scale, Multi-User Implementation of EQuIS
Item
Site Preparation (2)
Permitting and Regulatory
Equipment (1)
Startup and fixed (3)
Operating costs
Supplies (4)
Consumables
4,994
Not applicable
24,973
36,600
1 19,000
1,190
Not applicable
Effluent treatment and disposal Not applicable
Residuals and waste shipping, handling and transport Not applicable
Analytical services Not applicable
Modification, repair and replacement (5)
Notes:
(1) Software:
Chem
Geo
ELDC
ArcView
DVM
6000
1500
1000
1000
2000
?nn
3,746
Not applicable
190,503
QMS
EVS
Rockworks
LogPLot
2.6
Not applicable
19.2
62.4
/\ /\i
U.Ul
Not applicable
Not applicable
Not applicable
Not applicable
2.0
Not applicable
100.0
($) Third Party Software
450 ArcView
350 Rockworks
350 LogPLot
1,600 Total
($)
1195
2495
899
599
6,787
Hardware: 3 Pentium HI (933 MHz) = $4,386
Total Equipment Costs = $12,200 + $1,600 + $6,787 + $4,386 = $24,973
(2) Site Preparation = 0.20 x Equipment Cost = $4994
(3) Training = 3 days x $1,200 per day = $3600; Working capital = $21,000; Contingency = $12,000
(4) Supplies = 0.01 x 119,000 = $ 1,190
(5) Maintenance = 15% of equipment costs (0.15 * $ 24,973 = $3,746)
19
-------
Table 3-5. Costs Estimate for a Small-Scale, Multi-User Implementation of EQuIS
Item
Site Preparation (2)
Permitting and Regulatory
Equipment (1)
Startup and fixed (3)
Operating costs (4)
Supplies (5)
Consumables
Effluent treatment and disposal
Residuals and waste shipping, handling and transport
Analytical services
Modification, repair and replacement (6)
Site demobilization
Total operating costs
Notes:
(1) Equipment; Software = $ 20,587; Hardware: 1
Cost ($)
4,904
Not applicable
22,049
3,600
11,900
119
Not applicable
Not applicable
Not applicable
Not applicable
3,307
Not applicable
45,384
Pentium m (933 MHz) = $ 1462
Percent of Total Cost (%)
9.7
Not applicable
48.6
7.9
26.2
0.3
Not applicable
Not applicable
Not applicable
Not applicable
7.7
Not applicable
100.0
Total Equipment Cost = $ 20,587 + $ 1,462 = $ 22,049
(2) Site Preparation = 0.20 x Equipment Cost = $ 4409
(3) Training only = 3 days x $ 1,200 per day = $3600
(4) Small scale operating cost = 10% of large scale cost ($ 119,000) = $ 11,900
(5) Supplies = 0.01 x 11,900 = $119
(6) Maintenance = 15% of equipment costs (0.15 * $ 22,049 = $3,307)
20
-------
SECTION 4
TECHNOLOGY EFFECTIVENESS
This section discusses the effectiveness of the EQuIS software
in managing data during the SITE demonstration. Subsection
4.1 contains background information on the demonstration,
including a discussion of predemonstration activities and a list
of the three demonstration objectives. Subsection 4.2 contains
a brief description of the methodology employed during SITE
demonstration testing. Subsection 4.3 summarizes the
demonstration results.
4.1 BACKGROUND
4.1.1 Data Review
The Martin Aaron data set was examined ,to become familiar
with the data fields that were essential to testing the functionality
of the various modules. The data, as received, were reviewed
with respect to the test plan to determine whether all functions
could be evaluated or whether the data required alteration or the
use of a new data set for completion of all aspects of the
evaluation. Missing data were identified and alternative data
were identified for all applicable tests.
4.2 METHODOLOGY
The primary objectives were:
1. Verify that all system functions were fully operational
and had no significant programming errors. A
significant programming error was defined as: the
inability of a software function to execute properly
(e.g., a fatal error) or a software function which
produced an erroneous result (e.g., incorrect statistical
calculation). Each system function in the Chemistry,
Geology, ArcView Interface, DVM, CrossTab Report
Writer, and ELDC modules were executed to verify
operabiliry.
2. Determine the conformance of the EQuIS system's
input and output functions to data exchange standards.
EQuIS interfaces with several COTS packages. These
include GMS, Rockworks, LogPlot, EVS, Surfer, and
ArcView. The data exchange between EQuIS and each
COTS product was tested for interoperability.
The secondary objective of the system evaluation was:
1. Estimate the cost of implementing, using, and
maintaining the system for a "typical" hazardous waste
site data management program. Costs were broken out
as follows: software maintenance, hardware
maintenance, data management, and training. The
results of this cost estimate were presented in Section 3.
4.2.1 System Functionality
System functionality (Primary Objective No. 1) tests were
performed for each of the following modules: ELDC, Chemistry,
DVM, Geology, ArcView Interface, and CrossTab Report
Writer. Tests involved execution of key features identified in
the test plan. Items that were investigated included:
• Project file directory structure
• Intuitive design of software features
• Default values for data entry
• Sequential data entry
• Pop-up or online reference data
Other features specifically designed to ease repetitive
or time-consuming practices performed during
management of environmental data.
System functions were evaluated using the method and rationale
for success/failure identified in the respective test matrices in the
QAPP. Comments were also noted for software elements that,
in the opinion of the evaluator, were particularly easy or very
difficult or akward to implement. This was a qualitative
evaluation.
4.2.2 Conformance of Input and Output With Data
Exchange Standards
Conformance to data exchange standards was evaluated for input
to and output from EQuIS Chemistry, Geology, and ArcView
21
-------
Interface with GMS, Rockworks, LogPlot, EVS, Surfer, and
ArcView. The general test procedure consisted of data exchange
between the applicable EQuIS modules and each associated
COTS product to test for interoperability. Input sample data
were created in User-Defined Import Format and GMS Data
format. Data import functions were tested with these sample
inputs. This testing consisted of comparison of values in the
EQuIS database (Chemistry, Geology, and ArcView Interface
modules) and the respective input file to ensure that values were
not corrupted during the import process. Output sample data
were created for export to GMS, Rockworks, LogPlot, EVS,
Surfer, and ArcView. Data export functions were tested with
these sample outputs. Using the test data set, EQuIS passed data
to each of the COTS packages listed above. The outputs
produced by the respective COTS packages were compared to
values in the EQuIS Access database to ensure that data integrity
was maintained during the export and display process.
4.3 DEMONSTRATION RESULTS
This subsection contains results for the demonstration.
Subsection 4.3.1 presents the results of the software evaluation
with respect to Primary Project Objective No. 1. Subsection
4.3.2 presents the results of the evaluation with respect to
Primary Objective No. 2. The cost analysis (Secondary
Objective No. 1) was presented in Section 3.0.
4.3.1 Functionality Test Results
The functionality results for each of the six modules evaluated
are reported, in the order originally presented in the QAPP, in
subsections 4.3.1.1 through 4.3.1.6.
4.3.1.1 EQuIS ELDC Functionality Test Results
The ELDC is a tool designed to assist laboratories in checking
that the electronic data deliverables, or EDDs, they produce will
be acceptable to their clients. Alternately, when a laboratory
does not provide this service, it is run as a "stand-alone" product
to do an initial scan of EDDs prior to loading the data into the
EQuIS Chemistry Module.
The ELDC evaluation was performed on four major functions:
1) EDD Data Check, 2) Error Logs, 3) Created Test Data, and 4)
Historical and Y2K Data. The ELDC passed each of the major
functionality tests performed. The ELDC ran quickly and
smoothly. Data errors in the EDD were correctly identified in its
reports. The identified errors were systematically corrected by
the evaluator and the EDD was rerun after each correction. The
error logs always acknowledged that the corrections had been
made. Correcting EDD errors was often time consuming, but
would usually be done by the laboratory that generated the data.
The time required for this process appears to be dependent on
the EDD as supplied by a laboratory, not a function of the
ELDC. However, this length of time could be shorter if the
ELDC identified the specific error location within the EDD
records. In this version, only the type of error and total number
of each error type was reported.
The following subsections summarize the ELDC performance
for
the four functions tested. The first two subsections summarize
results using the Tutbasic EDD. Some functions were not
originally tested with this data set due to the lack of pertinent
data to evaluate the functions in question. A second data set was
created to test these functions; the results of these evaluations
are reported in the final two subsections.
EDD Data Check
The ELDC imports project data from laboratory generated EDDs
written by a laboratory in a format or file structure defined by
the client. The ELDC checks various items against a defined
EDD format and makes simple data comparisons. The ELDC
makes no computations using the project data. Its output
information alerts its user as to types of errors it has found and
identifies their general location within the EDD file structure.
This evaluation verified that the ELDC checked the EDD and
produced an Error Log. Items that were originally tested by the
Tutbasic EDD were: Required Fields; Duplicates Records; and
Values Out Of Range. Text fields were not tested because 'they
already use dashes. The Tutbasic EDD loaded quicldy and the
ELDC performed functions in about 10 to 15 seconds. Longer
run times should be expected based on the file size of the EDD
(numbers of samples, methods, and analytes per method). The
EDD used during the evaluation had 7 samples, 1 analytical
method, and 44 total analytes.
Error Log
When the ELDC checks the EDD, an error log is produced to
document data not in the defined format or meeting simple data
requirements defined by the user.
This part of the test verified that the ELDC Error Log that was
generated was accurate. The EDD was successfully corrected,
using information the in Error Log, to a "no error status". A
warning remained (non-critical) in reference to lab name
(MendoLab). This was not a problem since this data field
(lab_name_code) was not a required field.
Created Test Data
The Tutbasic EDD did not contain data to evaluate all portions
of the EDD data check. This evaluation verified accurate Error
Log generation for items that were not tested in the initial testing
of the Tutbasic EDD.
Items newly tested (not_ tested in the initial run) under this
section that produced errors as expected were: CAS number
errors, wrong chemical method numbers, and unit changes
22
-------
(reference values) that replaced letters with numbers. Items
tested under this section that did not produce errors as initially
expected are: misspelled chemical names, negative numerical
results, and the use of future years. There are no vendor claims
for these categories. They were originally included as part of the
Test Plan Matrix under the general concept of testing reference
values. However, they are not reference values. The fact that
these items did not produce errors is acceptable and actually
makes the software more versatile.
Historical and Y2K Data
The vendor claimed that the ELDC properly handles older data
and data that span the Y2K period. This section of the test
verified the ELDC's ability to work with historical data and that it
is Y2K compliant.
All aspects of this test were successful. Samples with collection
dates of both 5 and 10 years ago were properly processed.
Likewise, samples with collection data of December 1999 and
extraction dates of January 2000 were properly handled.
4.3.1.2 EQuIS Chemistry Functionality Test Results
The Chemistry module functionality evaluation was divided into five
major sections: 1) System Administration and Maintenance, 2)
EQuIS User Functions, 3) Data Entry, 4) Editing and Viewing Data,
and 5) Reporting and Graphing. Each of these sections consisted of
a number of functions for which the results are described below.
" Each of the five major functions generally performed as claimed by
the vendor. However, at least one individual function in each of the
major functional categories failed to perform as claimed. In some
cases, these failures were minor (had little impact on system
functionality) or were contrived situations that are unlikely to occur
during normal software operation. In other cases, the failures were
more significant, ranging from problems that made the system more
difficult to use to failures that could potentially result in data errors.
None of these failures, however, impacted the overall performance
of the software.
System Administration
System Administration worked well to allow the set up of, and
provide the security necessary to maintain, multiple users with
different levels of access. There were three major elements to this
evaluation: User Administration and Maintenance, Project
Administration and Maintenance, and Communication With
External Applications.
User Administration and Maintenance - User Administration and
Maintenance is used to maintain data security within EQuIS
Chemistry. Each user must be entered through System
Administration, and have a unique name and password with which
they may log into the EQuIS Chemistry system. Users may be
created, edited, or deleted through System Administration. EQuIS's
User Administration system is hierarchical in design, allowing for
multiple users with varying levels of access to particular projects and
the system in general. The levels of access granted to various users
should be determined by the types of data management functions
they perform. An important consideration in determining user
access levels is identifying those users who should have, or will
need, access to the System Administration functions. System
administration functions include assigning user and project
passwords; designating user access levels; importing, exporting, and
merging data; and performing system database maintenance
activities. There are four levels of system access built into the
system for various combinations of users and projects. Access
levels determine what functions EQuIS will allow each user to
perform and what applications he/she can run. The access levels are
listed in Table 4-1. Each level can also perform all of the functions
associated with the previous level.
Table 4-1. EQuIS User Access Levels
User Access
Level
Accessible Functions
Casual
Operator
Power
Super
View graphics and reports; export data
Casual user functions plus: import data;
manually enter data
Operator functions plus: merge data;
edit reference tables, data tables, and
groups
Power user functions plus: project and
user setup
The User Administration functions worked as claimed by the vendor
with only minor exceptions. After entering the System
Administration as a Super User, access was easily given to the
Power User, Casual User, and Operator. The Password, User
Name, User's Directory, and Administration Access for a Casual
User, an Operator, a Power User, and a Super User were
successfully modified and deleted using the appropriate menus. As
claimed by the vendor, the User Login Name could not be
duplicated. On the other hand, User Directories could be
accidentally duplicated, indicating the potential for improper
duplication of information. However, the likelihood of such
duplication seems remote based on the lengths to which the
evaluator had to go to create this scenario.
Project Administration/Maintenance - The EQuIS Chemistry data
module is based on projects. Projects contain chemical data from
single or multiple sites. The data for each project are stored in a
Microsoft Access© database. The System Administrator needs to
consider several things when creating and setting up projects,
including file management, database organization, and data security.
Before a project can be opened in EQuIS Chemistry, it must be
created through System Administration. When a project is created,
the system administrator enters a project name, project code, and
23
-------
path to the project's database. Other information (including project
start date, site, client, and project manager) may also be included
when creating a project. The system administrator may also choose
which users may have access to that project, and what type of access
those users have to the project data. Existing projects may also be
modified (except for the project code) or deleted through System
Administration or from EQuIS Chemistry. The System
Administrator can also copy reference tables between EQuIS
projects.
New projects were easily created; no significant time lags occurred.
System Administration worked as claimed by preventing new
projects from being set up using the same database as an existing
project; however, new projects could be assigned to inactive project
databases. This result indicates the potential for improper
duplication of project information although, as with User
Administration, the likelihood appears to be remote. Project access
worked as expected. Projectmaintenance was successfully accessed
and utilized from the Edit and File menus. System Administration
prohibited the duplication of a project. The evaluator was able to
remove users, add a new user, and edit a current user status.
However, the evaluator was able to remove the Super User from the
list of User Names displayed by the Project Maintenance screen.
The system would be improved by including a statement cautioning
the user that she/he is planning to remove the only project Super
User. Projects were easily deleted from the Project Maintenance
screen with no time lags.
Communication With External Applications - EQuIS allows
system administrators to change the home directories of external
software applications accessed from the tools menu. As well as
allowing various access privileges to built-in functions, EQuIS
Chemistry also allows the user to access external applications.
Communication with external software generally worked well,
although a minor problem was identified. Changes to the ProgPath
were appropriately retained after exiting and re-entering the
Applications Location Maintenance screen. The program also did
not allow the evaluator to delete any external software applications
which were considered "system entries"; other applications could be
deleted. It also did not allow the user to change the ProgName for
any of the applications. However, the system did not prevent the
evaluator from changing or assigning the ProgPath of an existing
application to a path that did not exist (e.g., by deleting one letter
from the path) or to a directory which did not contain anything but
an *.exe file. This portion of the software did not perform as
claimed by the vendor, however, this problem was not considered
critical to proper function of the software's System Administration.
EQuIS User Functions
EQuIS User Functions were evaluated in six parts: 1) Getting
Around, 2) Data Grids, 3) Drop Down List, 4) List Box 5) Selecting
Data, and 6) Querying Data. Each basic User Function, and the
corresponding evaluation result, is described in one of the following
sections.
Getting Around - Within the program there is a general pattern
followed on most screens. Logic flow proceeds from the upper left
panel to the upper right panel and then to the bottom panel. The
upper left panel generally contains identification information such
as the project name and sample ID. Gray fields are simply display
fields, whereas white fields can be edited. The upper right and
bottom panels are for data entry and modification. The mouse or the
key may be used to move from field to field on the EQuIS
Chemistry system screens.
Screen logic was verified to flow from upper left to right and then
to the bottom.
Data Grids - Many modules within EQuIS Chemistry use data grids
to display data. These grids are a flexible interface between user and
data. When using a data grid, the user may:
• Change the order of the columns
• Change the width of the columns
• Select an individual record (row)
• Select multiple records
•' Enter or edit data within the grid cells for select modules
• Select a field value from a drop-down list (when
applicable)
Choose a value from a drop-down grid in select fields.
Actions that could and could not be performed with data grids were
appropriate.
Drop-Down List - A drop-down list is signified by a down arrow
at the right of a data field. When this down arrow is clicked, the list
appears. To select an item from the list, simply click on the desired
item.
Drop down lists were easy to use and worked as expected.
List Box - A list box is a box that lists one or more columns of data
items. If the list of items is too long for the box, then scroll bars
allow the user to access the entire list. When selecting multiple
items from a list box, pressing while selecting allows
multiple, non-adjacent selections. Pressing while
selecting from the list selects everything from the first click to the
next click.
The and keys were successfully used to select
items from list boxes.
Selecting Data - Many functions of EQuIS Chemistry allow the
user to select specific data. There are three different methods with
which the user can select data: Single Location, Location Groups, or
Select From Map. When using Select From Map, data may be
selected singly or in groups with a polygon or circle.
Data were selected by single locations and a report was successfully
generated. Location group data were used to generate a plot and an
export file. The deselect function also worked as claimed. The
24
-------
Select From Map function worked well except that single locations
could not be selected. While this can be an inconvenience, it is not
a major obstacle to the software functionality since unwanted
locations from a group can be easily deleted.
Querying Data - Before using the Query Tool, the user must
determine the purpose of the query. For example, the user may want
to query a table for records that contain a certain range of dates.
Once the purpose of the query has been determined, the user must
decide which data fields will be used in the query. Once a query has
been outlined by the user, the Query Tool is used to perform the
query.
The basic query function (set up, run, and show queries) also
worked well; however, queries are not designed to be saved and re-
used by EQuIS Chemistry.
Data Entry
EQuIS provides two means for transferring or importing data into
the system: Manual Data Entry or Electronic File Import. If the
data to be loaded into EQuIS are only available in hardcopy format,
the data can be entered manually into the system through the manual
data entry screens or the data can be entered into spreadsheet
templates and imported into EQuIS. Alternatively, if the data are
already resident in an electronic database or spreadsheet, or if
corresponding Electronic Data Deliverables (EDDs) have been
provided for the field sampling and laboratory analysis work, these
data can be imported directly into EQuIS. It should be noted that the
electronic data format will need to be EQuIS compatible (e.g.,
IRPMS or similar format or equivalent). In the event the data
format is non-standard, (or unknown) the instructions in the
Importing Data Electronically section will need to be followed to
correlate the data to the EQuIS database fields. However, EQuIS is
not a fixed format. New formats can be created and custom EDDs
are available from many sources.
Data for import into EQuIS can be prepared in several ways:
• Using the MS Excel© spreadsheet templates. EQuIS
provides spreadsheets that have been set up as import file
templates. The columns are labeled with the field names,
data types, and width. Columns are also color-coded to
indicate which are required. The user selects the
appropriate worksheets according to the type of data.
Each set of worksheets includes an information sheet
explaining the available templates. After loading the
worksheet templates with data, each worksheet is saved as
a separate text file (tab or comma delimited) to be
imported into EQuIS. The text files are imported into
EQuIS, not the spreadsheet itself.
• Using an editor to create ASCII files (i.e. DOS files, not
word processing files). Each text line must end with a
CR-LF (carriage return-line feed) and each data element
must be separated by a tab or a comma. See the EDD
Format Definitions located in the EQuISVDoc directory on
EarthSoft's web page for a detailed description of each
format.
• Using an outside database system. Use that system's
export or reporting capabilities to either create ASCII files
or to load data into one of the many EQuIS spreadsheet
templates. If the data from the other system do not match
column-for-column with the selected spreadsheet, the data
will not import into EQuIS. However, new spreadsheet
formats can be created to match other databases.
The Data Entry function evaluation consisted of six areas: 1) Data
Templates, 2) Data Import Preferences, 3) Error Log, 4) Import
Rollback, 5) Temporary and Permanent Databases, and 6) Merging
the Database.
EOuIS Data Templates - EQuIS provides several templates for
loading data. These templates are provided as MS Excel©
workbooks. Each workbook includes an information sheet that
explains the templates included in that workbook. Each template is
a single worksheet and represents an available EQuIS data import
format. The column headers on the worksheet represent field names
in various tables of the EQuIS database. The columns have been
named according to requirements in the database. The second row
headers indicate the order, field data type, and size. If the columns
are moved out of order, the import will be rejected. The column
names that represent fields whose data are required (cannot be
empty) in the database are highlighted in yellow.
Field sampling, test method, and analytical result data were entered
into MS Excel worksheets which were imported into EQuIS
Chemistry. Data completeness, correctness, and order were
maintained during import.
Data Import Preferences - The Preferences section of the Import
Data File screen contains the following options.
• Require Parent Records - the import requires that all data
entered have the appropriate parent records, which
prevents any orphan data in the Temporary database. For
example, information must exist in the TEST table for
result records to import. The import will check for parent
records in both the Temporary and the Permanent
databases. When this field is checked, the Create Missing
Records option is grayed-out and is unavailable. If the
data have no parent record, an error log will be generated.
The error log messages are not generated during merge.
• Create Missing Parents. If checked, missing parent
records are automatically created in the Temporary
database as the data are loaded, thereby making it easier to
load without needing to manually create parent records. If
this field is checked, then the Require Parent Records field
is unavailable. Only key field or skeletal information is
25
-------
created for the parent. Additional information may be
desired before merging the record to the Permanent
database. This option should be used with caution
because a single typographical error in the imported
data could result in the creation of an unwanted
parent record.
• Overwrite Existing Data. If checked, the new data can
overwrite existing data when an overlap occurs as the data
are being loaded into the Temporary database. This option
does not affect data in the Permanent database.
• Add New Reference Values to Lookup Tables. If
checked, when the data are loaded into the Temporary
database, reference (look-up) data are automatically added
to the Permanent database reference tables. This option
should be used with caution because a simple
typographical error in the new data could result in the
creation of an unwanted reference value.
• Use Default If Missing. If checked, default values are
loaded to replace missing data as the data are imported into
the Temporary database.
Click on each option to select. Unavailable options display in gray.
The system successfully identified an intentional error and prevented
the import of test data without a field sample parent record in the
temporary database. The system was able to create the missing
parent records required for data import. Reference values and
default values were added to those already present in the system.
Error Log - The error log shows errors that occurred while
importing data into the Temporary database. The error log is an
ASCII file describing errors encountered, (i.e. missing reference
value, etc.) and whether it is an error, warning, or other information.
Records with errors will not be loaded into the Temporary database.
Records with warnings or other messages will be loaded into the
Temporary database. The file name for the error log is the same as
the file selected for import. If the import file name is filename.txt,
then the default error log name would be filename.ERR.
An Error Log listing errors and explanatory messages was
generated; its configuration agreed with that described in the EQuIS
literature. This log could be sorted by row, field, and error, or could
be displayed as a. summary of errors only.
Import Rollback - Each time data are imported electronically,
these data are assigned a unique batch identification. This
identification is written into the IBatch field of eachrecord imported
into an EQuIS data table (any table beginning with dtj. The
Rollback function allows the user to remove all records with a
particular IBatch number from the project's Temporary database.
The Rollback function is available from the Editmenu on the Import
screen. Selecting the function displays a form with a drop down box
from which can be selected an IBatch number for aparticular import
to 'rollback'. The imports are listed with the IBatch number, the type
of import, the date that the import was completed and the number of
records imported. The results of the Rollback are shown in a text
box on the form. The number of records deleted from each affected
table are reported.
The Import Rollback function successfully removed data entered
during a previous step.
Temporary and Permanent Databases - Each manual data entry
window has a Search Temp DB check box. Select this box to enter
or edit data in the Temporary database. Data entered into the
Temporary database must be merged into the Permanent database.
Data in the Permanent database can be edited and deleted, but new
records cannot be entered directly into the Permanent database.
New records must be entered into the Temporary database and
merged into the Permanent database. When adding (or editing) data
in the Temporary database, the drop-down lists for each applicable
field (such as sys_loc_code) display values from the Temporary and
Permanent databases. When editing data in the Permanent database,
the drop-down lists display data only from the Permanent database.
The manual data entry window allows the user to enter field data,
lab sample data, and test data. (Note: Entering result data can be
done in two different ways. The first method allows the user to
enter data one result at a time. The second method allows the user
to use parameter groups to enter multiple results.)
Field and analytical result data were successfully entered and viewed
in Temporary and Permanent Databases. Although the system
prevented the entry of invalid dates, times, and invalid codes for
certain fields, it did not generally check and prevent the entry of
anomalous data such as negative depths or shipment and receipt
dates which predated sampling events.
Merging the Database - The Merge function in EQuIS moves data
from the Temporary database to the Permanent database. It allows
a System Administrator to move single records or groups of records
from single tables or groups of associated tables, based on user
specified options. When records are successfully merged from the
Temporary to the Permanent database, they are removed from the
Temporary database. The merge function can only be performed by
users with Super or Power access privileges. The two Merge types
are available in EQuIS are New and Update. The new merge is
used to put data into the Permanent database that did not previously
exist in the Permanent database. The Update merge is used to
update existing records in the permanent database. The Update
merge does not overwrite any existing data, it only updates empty
fields in the Permanent database with data from the Temporary
database. The merge function creates new records in the Permanent
database from data stored in the Temporary database. The selector
allows the user to specify what data are to be merged. One of the
followOing merge options may be used: by table, by sample, by
location, by borehole, by well or all tables.
26
-------
The Merging the Database function merged data tables from
temporary to permanent databases without error.
Editing and Viewing Data
Six functional categories were evaluated under Editing and Viewing
Data: 1) Data Table Maintenance, 2) Reference Table Maintenance,
3) Table Indexer, 4) Database Table Record Counts, 5) Group
Maintenance, and 6) Data Screens Overview.
Data Table Maintenance - Data tables contain the dynamic or
project data and are maintained in the Temporary and the Permanent
database. These data can be edited or viewed using data table
maintenance. User access levels determine editing capability. The
Select Table drop-down list on the Data Table Maintenance screen
has three columns. The first column shows the common name of
the table, the second column shows the actual table name (within the
database), and the third column shows the parent table (if any).
Changes that were made to data tables in the permanent and
temporary databases were retained by the system after exiting and
reentering the Data Table Maintenance screen to view the modified
tables, hi addition to editing cell contents using the cursor, data were
also copied, cut, and pasted between cells within the same column
and cells within different columns. Rows from the data tables in the
permanent and temporary databases were also deleted. As claimed
by the vendor, it was not possible to change a data entry in a key
•field in a data table located in the permanent database. However,
data in a key field in a data table located in the temporary database
were modified. These changes were made using the cursor and by
pasting the contents from one cell over the contents in another cell
in a data table. Furthermore, the changes were retained by the
system after exiting and re-entering both the EQuIS Chemistry and
the Data Table Maintenance screen. Although it is possible that the
system will detect and remove these problems during normal use,
this could not be confirmed. Therefore, this portion of the test was
not a success, hi another test, users without access to the NJ Demo
Test Project were appropriately prevented from opening a project
when logging into EQuIS Chemistry or by using the Edit...Open
Project function. As a result, they were prevented from accessing
the,Data Table Maintenance screen. Also, Casual users and
Operators with access to the project were unable to access the Data
Table Maintenance screen from the Edit menu since this function
was greyed out.
Reference Table Maintenance - Reference data are maintained in
the Permanent database only in the reference tables. These tables
provide the information contained in the drop down lists of look up
information in the system and are edited by system administrators
and users with edit privileges. The Table drop-down list on the
Reference Table Maintenance screen has three columns. The first
column shows the common name of the table. The second column
shows the actual table name (within the database), and the third
column shows the parent table (if any).
hi addition to editing cell contents using the cursor, data were also
copied, cut, and pasted between cells within the same column and
cells within different columns. Rows from the reference table were
also deleted. In general, EQuIS Chemistry appropriately prevented
a user from changing an entry in a reference table key field.
However, when working with the Unit Conversion Factor table, it
was possible to improperly cut and paste, or copy and paste, a value
(e.g., cm) from one cell in the Reported Unit column over the value
in another cell in the Reported Unit column (e.g, over ft) if the value
in the adjacent column (key field Default Unit) also contained me
same value being pasted (e.g., cm). Furthermore, these changes
were retained by the system when the user exited and re-entered the
Reference Table Maintenance screens. Therefore, although EQuIS
Chemistry has been set up to a prevent a number of modifications to
the fields, there was one instance where the system did not prevent
a change(s) to a key field(s) in the reference tables. It should be
noted that these modifications introduced as part of the evaluation
would not necessarily be the result of casual use and that, in general,
a user would need to be trying to make these changes in order for
them to occur. In another part of the test, users without access to the
NJ Demo Test Project were appropriately prevented from opening
this project when logging into EQuIS Chemistry or by using the
Edit...Open Project function. As a result, they were also prevented
from accessing the Reference Table Maintenance screen. In
addition, Casual users and Operators with access to the project were
appropriately prevented from accessing the Reference Table
Maintenance screen from the Edit menu since this function was
greyed out.
Table Indexer - The Table Indexer is a tool that allows the user to
add indexes to tables in the Temporary and Permanent databases.
The user may also view existing table indexes using the Table
Indexer. The Temp Db box allows the user to choose tables from
the Temporary database (checked) or the Permanent database
(unchecked). The user may also choose to hide the default indexes
that are read-only. This is done by checking the Hide Read-Only
Indexes box. Casual users are prevented from editing data, and
some key system features are reserved for Power users.
Indexes were successfully added to tables, viewed, and modified in
both the temporary and permanent databases. Furthermore, it was
confirmed that the indexes were properly retained by the system, as
entered, after closing and reopening the EQuIS Chemistry - Indexer
screen. Finally, the evaluator was appropriately prevented from
creating an index in which the same field was selected twice. Since
the evaluator was unable to select any of the fields in the Field in
Index box, no changes could be made to read-only indices
associated with tables in either the permanent or temporary
databases. The evaluator also confirmed that the fields in the read-
only index appeared gray. Users without approved access to the NJ
Demo Test Project were unable to open this project when logging
into EQuIS Chemistry or by using the Edit...Open Project function.
As a result, they were appropriately prevented from accessing the
EQuIS Chemistry - Indexer screen. Also, Casual users and
Operators with access to the project were unable to access the
EQuIS Chemistry - Indexer screen from the Edit menu since this
_
27
-------
function was greyed out.
Database Table Record Counts - The user may see a summary of
the number of records in each data table of the Permanent or
Temporary databases by using the Database Table Record Counts.
The record counts obtained for data tables in the permanent and
temporary databases remained consistent during repeated access of
the Table Record Counts window and changed by an appropriate
amountafter the evaluatorremovedrecords between viewings of the
Table Record Counts window.
Group Maintenance - There are three general categories of Group
Maintenance; Using Groups, General Group Maintenance, and
Analytical Group Maintenance.
• Using Groups - Group maintenance allows the user to
save a selection, or group of samples, locations, analytes,
or wells (etc.) for reporting and graphing. For example,
instead of selecting individual samples every time a report
is created, the group maintenance feature allows the user
to create reports using a previously saved group of
samples. Selecting and saving groups (or samples from
the same locations) for reporting and graphing functions
ensures that precisely the same samples are reported each
time; preventing the accidental omission of any
component of the group. Group maintenance is managed
by EQuIS in two pairs of tables. Each pair consists of a
parent table containing user-defined names of groups, and
a child table containing the members of each group.
* General Group Maintenance - The user may create
groups for use within a project. The possible group types
(Boreholes, Excavations, Field Samples, Soil Gas, Lab
Samples, Site Locations, Other Areas, Product Thickness,
Samples, or Wells) are listed in rtjjroupjype. Each
group is a selection of records from one data table. If a
field sample group is created, all members of the group
must reside in the Field Sample table and records from
different tables cannot be combined into the same group.
However, any record can be part of many groups created
in the same table. The groups can be used for most
reporting and graphing. Within reporting and graphing,
wherever a location is selected, a location group is also
selected. The same holds true for all possible group types.
• Analytical Group Maintenance-The Method Analytical
Group and the Method Analytical Group Member tables
select records from the reference table Analytes. Within
reporting and graphing, wherever an analyte is selected, an
analyte group can also be selected. This grouping
mechanism does not allow data from the Sample
Parameter Measurement, Geotechnical Result, or
Biological Result data tables to be grouped; this grouping
works only to retrieve data from the Result data table. The
functionality of this pair is similar to the Group pair; a
group member (an analyte) can be in many Method
Analyte groups. Groups were created with relative ease.
Existing group information was edited, saved, and later successfully
viewed after exiting the system. Deletion of a group was easily
performed and was confirmed in the Analyte Group Maintenance
screen.
Data Screens Overview • The screens used to enter data manually
may also be used to edit or view data. Data stored in the Temporary
database or Permanent database may be viewed using the View
menu for those who do not have Edit privileges. All users can view
field sample collection data, corresponding lab sample information,
analytical tests conducted, and the corresponding analytical results.
(Note: Users may not delete a single sample and all of its dependent
records in a single operation in a project's Temporary database. In
the Permanent database there is some capability for cascading
deletes of dependent records.)
After field sample and result data were viewed and edited, the
evaluator re-entered the respective Manual Data Entry screens and
confirmed that the changes were retained. Changes were made from
the different tabbed views; when a change was made to one tabbed
view, it was saved to the other views when Apply was selected.
Changes that were made from the Result tab view could only be
observed in the Data View tab if Apply was first selected. Changes
could not be made to the data displayed from the Data View tab
view.
Reporting And Graphing
Once data have been entered into the EQuIS Chemistry database
(either manually or by electronic import), the user can use the data
in a variety of ways. EQuIS Chemistry can provide detailed reports
and graphs tailored to the needs of the user. The user may use any
subset of data to create a report or graph. When reporting, graphing,
and exporting, only data that have been approved by the user are
eligible. This is done by entering 'Yes' in the repoitable_result field
of dt_result - allowing the user to validate each record. The
reportable_result field may be accessed either through Manual
Result Data Entry or by using Data Table maintenance. Data
imported using the ESBasic EDD can use a default value for the
reportable_result field. Once the data have been entered and
indicated as reportable, they can be accessed for reporting, graphing,
or exporting.
Three types of functions were tested under Reporting and Graphing:
production of reports, creation of various plots and exporting data.
A total of seven functions were evaluated (these were judged by the
evaluator to be representative of the functions most likely to be used
by software users); all were successful.
Reporting Data - EQuIS offers three different reporting formats to
give users a variety of tools readily available to view and report data.
28
-------
Quick Reports: Provides a report for a single table or a
single predefined query of the data.
Standard Reports: EarthSoft provides these predefined
reports, which link to either MS Excel© or Crystal
Reports.
Custom Reports: Reports that a system administrator
creates and links to EQuIS for use on their project or
within the company. This option allows the user to create
or install any reporting application. If an executable file
name custjptexe exists in the main executable directory,
then this menu option will start the application. EQuIS's
ability to generate Custom Reports will not be evaluated
during the demonstration
Three Quick Reports were developed from a data source, a reference
source, and a query source. Each was successfully viewed in MS
Excel. An attempt was also made to generate a Quick Report using
unapproved data. This test was not successful. According to
EarthSoft's documentation only data that have been approved by the
user and contain a "Yes" in the reportable_result field of dt_result
are eligible for use in a report generated from a data table. However,
after changing the reportable_result field for a result to "No" using
Data Table Maintenance, the affected result was inappropriately
included in the resulting Quick Reports. An Analytical
Concentration Standard Report was created and saved in MS Excel.
A printout was compared to the!data grid displayed by the
Worksheet tab in the Report Criteria screen; all results matched. An
attempt was made to generate an Analytical Concentration Standard
Report using unapproved data. The data were appropriately
screened out by the software.
Creating Graphs From Within EOulS - EQuIS provides links to
third party applications for further analysis and modeling of the data.
The current applications linked are Surfer by Golden Software and
Microsoft Excel. Surfer is used to create contours; Excel is used to
create graphs. There are five types of graphs that can be created
from within EQuIS (only Trend and Dot Plots were evaluated.
Trend Plot: This is a 2D plot completed in Grapher. User
can select/filter data from the project database.
Map: This is a 3D plot using Surfer. User can select/filter
data from the project database.
Trend Plot from File: Automates Trend Plots with data
from a user-specified data file (CSV type).
• Map from File: Automates maps with data from a
user-specified data file (CSV type).
Dot Plot: 3D standard dot plot/posting using Surfer.
Users can select and filter data from the project database.
saved, and viewed. The saved files were re-opened and matched the
original plots.
Exporting Data - Data can be exported to other EQuIS modules
(e.g., ELDC) or COTS software (e.g., EVS) for reporting and
graphing.
A reference file was exported and successfully opened in ELDC; the
data structure and content were unchanged and the number of rows
in the export file remained the same as had been in the original file.
Data were exported to a tabular scatter point 3D file (* .xyz) and then
successfully imported into QMS 3.0. The original *.xyz files was
saved as an *.sp3 file and then imported into EQuIS Chemistry; the
data structure and content were unchanged during export.
4.3.13 EQuIS DVM Functionality Test Results
The EQuIS DVM is linked to the EQuIS Chemistry module and
imports project file data maintained in the Chemistry module. The
imported data are subjected to an array of verification/validation
functions. The DVM reports data qualifiers or "flags" relative to
pre-set Quality Control (QC) criteria. Preset criteria are written into
the DVM software as previously established by USEPA Guidelines
or specified in the pertinent analytical method; these can be edited.
System Administration Functions maintain defined users and user
levels. The module also provides access protection for users without
system authorization to the Chemistry module. As claimed by the
vendor,,some important data verification and validation procedures
are not performed by the DVM version tested. Among these are
chain of custody reviews, temperature of samples as received by the
laboratory, instrument performance data, internal standards, external
standards, retention time windows, interference results, serial
dilutions, appropriate selection of constituent data from subsequent
dilutions, and selection of best results for samples re-extracted and
/or re-analyzed due to QC performance problems. These items still
need to be addressed by a person performing a manual review of the
project data.
Overall, the system performed several tasks well. However, three
functions either performed inconsistently (Precision function and
Blank Ratio) or did not perform as expected (Flag Order function).
These problems have a significant impact on the application of the
software for data verification tasks. The software was easy to use.
Data loaded to the DVM using specified project code from
Administration (project setup) and verified data quickly with the
module (depending on test data involved), with a range of 55
seconds to 7 min and 45 seconds. Nine DVM functions were tested:
1) System Administration, 2) Analytical Methods Table, 3) The
Hold Time Function, 4) The Blank Ratio Function, 5) The Flag
Order Function, 6) Accuracy Function, 7) Precision Function, 8)
Semivolatile(SVOA)Analytes,and 9) Settings/Default. The DVM
functions tested and the corresponding results are described in the
following subsections.
Trend Plots and Contour or Dot Plots were successfully created, System Administration
29
-------
System administration was tested to verify security measures were
in place to prevent unauthorized access through the DVM into the
Chemistry module (see Section 4.3.2.3 for a detailed description of
System Administration). Three items were designated for testing: I)
access to the database with a fictional ID, 2) operation of the system
at a higher user level than defined, and 3) access to the system as a
deleted user.
Overall, the System Administration provided adequate security for
a controlled implementation of the DVM and Chemistry modules.
Database Access Using a Fictional ID - This test was designed to
verify security from unauthorized access using an incorrect or
fictional identification.
The system performed as claimed by the vendor; access was denied
using the fictional ID.
System Operation at a Higher Lever Than Defined - This test
was designed to evaluate whether a user could operate the system at
a level higher than previously defined for that user level.
This test was not attempted since the tutorial software already
operates at the highest user level.
Database Access Using a Deleted User - This test was designed to
verify security from unauthorized access using a deleted user
identification.
The system performed as claimed by the vendor; access was denied
to the deleted user.
Analytical Methods Table
The Analytical Methods Table provides a list of standard analytical
methods. The table was tested to verify that the table could be used
and modified as expected. Four specific functions were tested: 1)
methods were compared to those in DVM Data 2) a new method
was added to the table, 3) a method was deleted from the table, and
4) the deleted method was added back to the table.
Overall, the Analytical Methods Table provided adequate versatility
for the use of a wide variety of project data. The table was easy to
use. Specific results are as follows.
Compare Methods to the DVM Data - This test verified whether
the Analytical Methods Table functioned correctly with respect to
the evaluation using DVM Data (Martin Aaron site).
All method references were verified as correct.
Add a New Method to the Table - This procedure tested the ability
to update the list of methods with new or revised methods not
currently included.
Method numbers 8280 and 8290 for dioxins/furans were
successfully added to the Analytical Methods Table.
Delete a Method from the Table - This evaluation verified the
ability to remove methods that are no longer used.
Method numbers 8280 and 8290 for dioxins/furans were
successfully deleted from the Analytical Methods Table.
Replace a Deleted Method In the Table - This test evaluated the
ability to replace a method that had been accidentally deleted or to
re-instate an outdated method to accommodate the use of historical
data. Method number 8290 for dioxins/furans was successfully
added back to the Analytical Methods Table.
The Hold Time Function
The Analytical Methods Table also provides criteria fields for the
evaluation of holding times and maintains the necessary fields for
sample-specific data (date fields) relative to holding time
calculations. Data imported from EQuIS Chemistry were evaluated
against these reference fields for holding time violations. The Hold
Time Function was tested to verify that holding time violations,
historical data, and Y2K sensitive data would be reported accurately.
This function performed as expected and was easy to use.
Hold Time - This test evaluated DVM's hold time determination
function for information accuracy.
Test method 9060 has a Technical Holding time of one day. The
DVM correctly flagged data from a sample that had a lapse time of
18 days between collection and analysis. During the evaluation for
older project data, the sampling date was successively changed to 5
and then 10 years before the sample preparation date. The DVM
reported the 5 and 10 year lapse between sampling and preparation
as aholdingtime violation and correctly flagged the affectedresults.
Historical Data - This procedure evaluated the ability of DVM to
accurately handle data and information from older projects (defined
as before 1/1/00).
Sample date fields were populated with sampling dates of 8/20/94
and 8/20/89. The DVM used the dates correctly.
Y2K Compliance - This test evaluated whether data that bridged
the Y2K threshold were appropriately handled.
A sampling date of 12/21/99 was used and an analysis date of
1/2/00. These dates were used correctly and reported accurately
indicating Y2K compliance.
The Blank Ratio Function
The Blank Ratio Function records blank contamination and assesses
the impact to associated environmental samples with respect to the
USEPA 5x/10x rule. This rule establishes an analyte specific factor
(ratio) by which the blank contamination is multiplied. This process
sets a QC screening action level below which the analyte is
30
-------
considered an artifact due to the observed chemical contamination
and the affected result is flag U (non-detect). The Blank Ratio
Function was tested to verify mat the DVM would: 1) correctly
report contamination present in a blank, 2) accurately report analyte-
specific action levels, and 3) correctly assess and report the affect on
sample results. This testing was accomplished using a variety of
methods, analytes, blank contamination levels, and variations of
target compound concentrations in the affected field samples.
During the EQuIS Demonstration Project the DVM, reported both
false positive and false negative data. False positive data were
represented by many analytes (VOCs, SVOCs, pesticides, and
metals) with concentrations below the QC action level that should
have been flagged by the DVM but were not. False negatives were
represented by heptachlor and chlordane, each with a concentration
of 26 A/.g/kg and flagged U. The Blank Ratio (5x) action level for
these compounds is reported correctly by the DVM as 25 ,ug/kg for
the observed contamination of 5 /wg/kg. This means that no sample
result greater than 25 Mg/kg should be qualified. In the case of
metals, no data were flagged in any of the samples and the DVM
reports did not indicate the presence of contamination in the blanks.
The original EarthSoft DVM data had low level contamination in
the blanks that were not acknowledged. The SITE EQuIS
Demonstration test data placed a high level of lead contamination in
the blank and an equal concentration in the field samples with the
same result. Populating the database for metals used the same
procedure as for organics with the exception that laboratories do not
flag metal data with a B for blank contamination. Accordingly, B
flags were used for organic data but not for metals. This is in
keeping with USEPA guidance and analytical laboratory industry
standards. The failure of metal data to register at all appears to be
relational. These determinations are critical in that investigation data
should not have false positive or false negative data.
Mismanagement of data at the validation level may have expensive
downline consequences with respect to risk assessments and
remediation decisions.
As this function is one of several useful features of the DVM
module, these problems are considered to have a fairly severe impact
on the software's usefulness.
The Flag Order Function
One important goal of data validation is to determine the degree to
which project data are compromised by any observed problems.
The correct ranking and application of QC problems is essential in
achieving mis goal. In assessing all applicable review items, the
most appropriate flag for a given data point, indicative of the most
serious quality issue associated with the sample data, can be
determined. This test evaluated the ability of the Flag Order
Function to ascribe the most stringent flag applicable to a result
where multiple flags of different severity were indicated by the
supporting QC data. For example, a J flag qualifies a sample result
as an estimate and an R flags indicates data were not usable. To
reject data (R) is the more severe determination and should be the
most stringent flag assigned.
In the test of the created test dataj estimated DDT and heptachlor
were flagged with a U as a result of blank contamination and were
later appropriately flagged with the U instead of a J in the precision
summary. This was the expected outcome. However, in a second
DVM report DDT and heptachlor were flagged with a U for blank
contamination and later (in the surrogate summary), flagged with a
J (less severe) instead of the U. This outcome was not correct
according to the flag order. These failures indicate that this function
did not perform as claimed.
Accuracy Function
The Accuracy Function was tested to verify the correct use of
certain QC criteria percent recovery data and proper qualifier
application to the affected sample data.
Pesticide tests used various high and low percent recovery data,
including 1 percent under the low QC criteria and 1 percent above
the high QC criteria. The test also used percent recoveries below 10
percent (non-detects were correctly flagged R) and equal to 10
percent (non-detects were correctly flagged UJ).
Precision Function
Precision is a measured comparison of substantially similar data
points using the RPD as the indicator of their closeness. The correct
application and reporting of precision data is important to
interpretations of the current project data and decisions concerning
potential additional data needs. The Precision Function was tested
to verify the correct use of certain QC criteria (relative percent
difference or RPD) and proper qualifier application to the affected
sample data.
Several concerns were noted during the evaluation. The RPD did
not appear to be calculated correctly (the unspiked sample was
compared to the spiked sample result). While the DVM report
flagged sample results impacted by outlier RPD values, the wrong
RPD value was used. In addition, the DVM report column used to
present RPD data was missing the appropriate results. These
failures indicate that this function did not perform as claimed.
Semivolatile (SVGA) Analytes
The Semivolatile (SVGA) Analytes function was tested to verify the
correct use of surrogate percent recovery criteria and ensure proper
qualifier application to the affected sample data. Semivolatile
compounds are segregated into one of two groups. The groups, acid
and base, are named for the. type of sample preparation that is
performed to maximize the extraction efficiency of distinct chemical
classes.
The DVM correctly flagged SVGA results according to acid/base
fraction assignments for out of control surrogate recoveries.
Settings/Default
Tab 1 was tested to verify the correct use of dilution factors and
appropriate application of volatile surrogate QC data. The Tab 1
setting provides a function for allowing "out of control" surrogate
31
-------
recoveries in a diluted sample. The Test Plan was written to test a
5:1 dilution threshold. (Dilutions of 5:1 and greater were believed
to be the DVM criteria) The dilution threshold actually starts at 6:1
per EarthSoft DVM Manual.
Three reports were generated. Dilutions of 10:1 and 5:1 were set in
the first two reports with the Tab 1 option selected and not selected,
respectively. The system passed the test at 5:1 (the DVM flagged
data for out of control surrogates)., On a re-test at 6:1, the DVM
passed as it did not flag data for out of control surrogates with the
Tab 1 option selected, reflecting the impact of dilution on surrogate
recovery.
4.3.1.4 EQuIS Geology Functionality Test Results
The EQuIS Geology Module consists of linked database tables, and
a menu system that supports a number of functionalities. In total,
eight functions were evaluated: 1) Getting Around, 2) Preferences,
3) Material and Material Groups, 4) Unit Calculator, 5) System
Administration, 6) Manual Data Entry, 7) Editing and Viewing
Functions, and 8) Quick Reports. The first four of these related to
the customizability and ease of use of the user interface windows.
System Administration is similar to what was described in Section
4.3.1.2; it consists of user and project administration and
maintenance functions. Once a project has been created, the first
step in using EQuIS Geology is getting data into the project. Data
may be brought into EQuIS Geology either manually or
electronically. If the data are available only in hardcopy format, they
may be entered manually through data entry screens. However, if
the data exist in electronic format either in text files, spreadsheets,
or another database, data can be imported automatically. In most
cases, electronic data import will be used at the outset of a new
project Where data currently reside in text files, they can be
imported into the database. When data are present in spreadsheets,
electronic data import requires only a properly formatted text file.
Only Manual Data Entry was evaluated under Primary Objective
No. 1, because data import to EQuIS Geology was evaluated under
Primary Objective No. 2 (see Section 4.3.2.2.) The ability to
accurately view and edit tables was evaluated for two groups of
operations: Data Table Maintenance and Reference Table
Maintenance. Finally, the reporting capabilities in EQuIS Geology
were accessed through Quick Reports.
Getting Around
Several data management tools are used with the EQuIS Geology
system. Understanding how to use the system helps the user avoid
unnecessary errors.
This test consisted of an evaluation of the ease of use and
functionality of four specific functions: 1) System Screens, 2) Data
Grids, 3) Lists, and 4) Query Tool. The first three performed as
claimed by the vendor; the fourth function had a mixed result, as
described in the following paragraphs.
System Screens - Within the program there is a general pattern
followed on most screens. Logic flow (and description in this
manual) proceed from upper left panel to upper right panel to
bottom panel.
The borehole, well, and sample data entry system screens were
opened and verified that the logic flow proceeded from the upper
left to the bottom right, as stated by EarthSoft.
Data Grids - Many modules within EQuIS Geology use data grids
to display data. These grids are a flexible interface between user and
data. When using a data grid, the user may: 1) change the order of
the columns; 2) change the width of the columns; 3) select an
individual record (row); 4) select multiple records; 5) enter or edit
data within the cells of the grid (except when data are specified as
read-only); or 6) select a field value from a drop-down list (when
applicable).
Manipulation of the data grid and the data within it were very
straightforward and logical.
Lists - Lists are used in system screens for choosing what table or
borehole data the user wants to display or edit, changing the units of
depth, and selecting fields in a query. Basically, lists allow the user
to choose a value or a record, where multiple values or records can
be associated with a given field. There are two types of lists: drop
down lists which allow the user to select only one value and list
boxes which allow the user to select multiple values.
• Drop-Down List - A drop-down list is signified by a
down arrow at the right of a data field. When this down
arrow is clicked, the list appears. To select an item from
the list, simply click on the desired item.
Drop down lists were tested using the Borehole, Well, and
Stratigraphy view screens. Selecting a borehole
identification (ID) in the upper left panel changed the
displayed data in the rest of the fields to those data
associated with the selected ID. Drop down lists were also
tested (as part of Materials & Material Group maintenance
tests below), for changing the status flag for records, by
choosing an accept or reject flag (A or R) for that field.
• List Box - A list box is a box that lists one or more
columns of data (information). List boxes were tested in
export screens for Surfer, QMS, and LogPlot.
List boxes were used to select multiple (non-continuous as
well as continuous) export records, and checked for
navigation scroll bars when the entire list could not be
displayed on a system screen. The lists performed as
claimed by the vendor and the design was found to be
intuitive. Even for a user without any spreadsheet or
database experience, these lists will be easy to use because
extensive online help is provided with the software.
32
-------
Query Tool - This tool is available in Data & Reference Table
Maintenance (under the Edit Menu), and in export screens (under
the File/Export Menu) of the Geology module and can be accessed
by clicking on the QueryTool button. Once a query has been
outlined by the user, the Query Tool can be used to actually perform
the query. The user can choose to display records according to
his/her criteria, which could be easily specified in the bottom part of
the Query Criteria screen.
A query was designed and saved to a file for later use; its SQL
script was also displayed. The query tool functioned flawlessly in
multiple queries that were performed. However, there was no option
to recall or invoke a previously saved query in the Query Criteria
screen. EarthSoft confirmed that this version, as well as subsequent
versions, do not support this feature to prevent users (other than the
SUPER user) from, accidentally modifying the structure of the data
or reference tables.
Preferences
EQuIS Geology allows the user to customize the program and
databases for the users needs through preferences. Program
preferences may be set in the Preferences screen, displayed by
selecting Preferences from the File menu. After the user has
selected the desired preferences, clicking OK will save and activate
these preferences, whereas clicking Cancel will ignore the changes
the user has made. The user can specify Screen Preferences, Unit
Preferences, Import Preferences, and Miscellaneous Preferences;
only the first two of these were evaluated.
Screen Preferences - Screen Preferences allow the user to change
background color, select between two sizes for toolbar buttons, and
to remove the toolbar entirely from the display.
All of these functions worked as stated by the vendor and changes
were saved upon exiting the module.
Unit Preferences - Unit Preferences allow the user to specify length
units for data being electronically imported or exported. They do
not change-the units of depth already entered (or manually entered)
into the EQuIS Geology system.
It was verified that a chosen unit length was saved upon exiting the
system.
Materials & Material Groups
EQuIS Geology is available with three standard material
classifications (USCS, AASHTO, andUSDA) and a fourth category
that contains petroleum and mineral resources called Other.
However, because these materials may not necessarily meet all of
the required material definitions, other materials and material groups
may be added. For each material a definition and color may be
defined. Material groups allow the user to group materials that are
common to a specific project or site. The user may create additional
groups if needed for a specific project. User-defined groups may
contain any combination of standard or user-defined materials and
may be updated as needed.
During this test, new groups and new materials were successfully
added, and then used during data entry. A new material group was
created and both existing and new materials were added to that
group. The evaluator verified that duplicate material groups, as well
as duplicate materials, were appropriately rejected by the software.
However, two problems were noted with the patterns used to denote
material groups. First, no information was available in the EQuIS
Geology module for patterns corresponding to various standard
geologic materials. A list of default patterns corresponding to one
or more standard geologic classifications systems would be useful.
Secondly, although material properties for any geologic material can
be assigned only globally, and cannot be different in different
material groups for a given project (appropriately preventing
inadvertent duplication of materials), this feature was not clearly
explained on the Material Group Maintenance screen. These two
issues deal primarily with ease of use and, therefore, were judged to
be minor; this function worked essentially as claimed.
Unit Calculator
The unit calculator is a simple tool to do unit conversions. This tool
may be useful not only for users of EQuIS Geology, but also in any
office, laboratory, or classroom setting where geologic, geotechnical,
or hydrogeologie calculations are required. To access the Unit
Calculator, select Unit Calculator from the Tools menu or type
This Unit Calculator tool was tested, by carrying out two sets of
conversions (English-Metric and vice versa) for each of seven
entities: length, area, volume, mass, density, pressure, and
temperature. These conversions were checked with a hand
calculator for accuracy. The calculator tool provided with the
EQuIS Geology module was found to be accurate and easy to use.
System Administration
Management of EQuIS Geology projects is carried out with the
System Administration module. In System Administration new
projects are created, users are established and assigned to various
projects, and the location of third-party visualization and analysis
tools is designated. Each user should have a unique name and
password, with which they may log into the EQuIS Geology system.
Major User and Project Administration functions, including
customizing user and project access for different levels of users
were evaluated. Both functions essentially performed as claimed by
the vendor, with the exception of part of the user access test (see
details below). Overall, user administration was easy to use and
could be implemented securely by any system administrator with
super user access to the EQuIS Geology system.
User Administration/Maintenance - To maintain data security
within EQuIS Geology, each user is established in System
Administration. When a new user is created, a login name and
password (as well as user name and directory) are recorded. When
33
-------
logging into the program, the user must use his/her login name and
password to access EQuIS Geology. When a user is created, he/she
may choose whether or not to give administration access. If they are
given administration access, they can use System Administration to
create, edit, or delete projects and users (see Section 4.3.1.2 for
additional description of User Administration).
The User Administration Maintenance function worked as claimed.
Four new users, called Casual-Martin, Operator-Martin, Power-
Martin, and Super-Martin, were created with appropriate
permissions to cover all four types of project access levels built into
the EQuIS Geology module. The User Administration screen was
very easy to use and followed the same upper left to lower right
logic flow that was common to most EQuIS system windows.
Super-Martin was used to add another user Casual - MA. This user
was later deleted as part of the testing. It was noticed that deleting
the user does not delete the user directories. This was, however,
easy to do from the Windows Explorer screen, and does not affect
the overall functionality of the module. It was also verified that a
user without administration privileges could not add a new user.
Overall, user administration was easy and could be implemented
securely by any system administrator with super user access to the
EQuIS Geology system.
Project and Database Maintenance - When a project is created,
the system administrator will enter aprojectname, project code, and
path to the project database. Other information such as project start
date, site, client, and project manager may also be entered when
creating a project. This information (except for the project code)
may be changed later through System Administration. When a
project is created, the system administrator may choose which users
may have access to that project, and what type of access those users
have to the project data. In addition to indicating which users can
access each individual project, System Administration allows users
to access project data with different access levels. Lower user
access levels allow users to view and export data. Higher user
access levels allow users to import and edit data in addition to
viewing and exporting data. In addition to the business context of
the data management plan being employed, there are also security
considerations. EQuIS allows multiple levels of password-protected
access in the system that are generally sufficient for stand-alone,
single-system operations. For network environments with both local
and modem access, additional security measures such as storing
client data only on non-network accessible media may be in order if
the user is unsure about the security of existing system "firewalls"
and other protective devices. A consultation with the user's network
system administrator is particularly useful in planning for secure
operation of the system and protection of the data. Four functions
were evaluated under Project and Data Maintenance: 1) File and
Directory Organization, 2) Creating New Projects, 3) User Access
to Projects, 4) Modifying Existing Projects, and 5) Editing/Deleting
Projects.
• File and Directory Organization - EQuIS organizes data
into directories that are set up through the system's
administration and user registration processes. Naming
and relationships established between the directories used
should reflect the business context of the data that EQuIS
is intended to manage. An example of project
organization is to place all project files in directories
named after the projects. (For example,
c:\earthosft\geo\projectname).
File and directory organization were tested. Changing
program path field information and adding new programs
was an easy process which was intuitively organized in the
Applications Location Maintenance screen. The process
involved replacing the path of the original application
location with the new application path in the Applications
Location Maintenance screen.
New Project - Similar to the Chemistry module, the
EQuIS Geology moduel is based on projects which
contain geologic data. These data are stored in a
Microsoft Access© database. Subsection 4.3.1.2 provides
additional information on project administration.
A new project was successfully created for this evaluation.
As part of the EQuIS Geology SITE project, a database
was created and filled with 34 records of borehole and
well data. It was verified that a Template database was
first copied over to the location of the Martin-Aaron
project working directory, with the indicated database
name. Users created through the User Administration test
above were given access to this project, covering all
possible access levels allowed in EQuIS Geology.
User Access - User access to projects is controlled
through System Administration to maintain data security.
As with the Chemistry module (see Section 4.3.1.2), there
are four user access levels: casual, operator, power, and
super.
The test of user access to projects yielded mixed results.
The Martin-Aaron project was created by the SUPER user
and project access was tested to confirm that it was
governed by the accessibility hierarchy prescribed by the
vendor. The evaluator verified that users could access
only the projects to which he/she was assigned. In
addition, only super users could access the System
Administration module to be able to modify other users'
access - Casual, Operator and Power users appropriately
could not access the System Administration screen.
However, several other parts of this function failed to
perform as claimed by the vendor. First, the casual user
(Casual-Martin) could modify the database even though
permission had not been granted. Secondly, the operator
user (Operator-Martin) could not import data, a function
for which he/she was authorized. Finally, the Power and
Super users (except the SUPER user) could neither edit
34
-------
nor view the data/reference tables or groups. All of
these observations run contrary to the hierarchy
prescribed by the vendor. EarthSoft claims that these
issues have been rectified in later releases of the
software; however, SAIC was not able to verify this
claim due to schedule andmonetary constraints on the
project.
Modifying an Existing Project - Projects can be
modified by super users. This test was designed to verify
that the modified project information was saved upon
closing and reopening EQuIS Geology.
Only the user SUPER and other super users can modify
the status of an existing user, including other super users.
However, since users at lower levels of hierarchy cannot
even access the System Administration screen, they cannot
modify any users. Therefore, this test was successful.
This function was easy to use and was designed
intuitively.
Edit/Delete - Several controls are important to ensure that
project edits are made only by authorized personnel. In
addition, it is critical that project files can not be
improperly duplicated or accidentally modified. The
ability to edil/delete a project is available to only super
users. Error messages are produced when a new project
with the same name as an existing one is created or when
an existing project database is specified for anew project.
Editing and deleting projects was easy and straightforward
to use; all functions worked as claimed by the vendor.
\ual Data Entry
e main data 'objects' exist with the EQuIS Geology data
;ture: borehole, geologic sample, and well. Each object must
a unique ID. EQuIS Geology has four specific kinds of data
ts: borehole, well, sample and stratigraphy. The accuracy and
of use of data entry functions for six types of geology data were
uated in this portion of the test. The data types were: 1)
shole, 2) well, 3) water level, 4) well segment, 5) sample, and 6)
igraphy.
i of these functions performed the basic tasks claimed by the
or for data entry. It should be noted, however, that there were
basic types of issues related to the use of each of these
tions. First, several functions lacked QA checks where the
uator thought them appropriate. Secondly, a number of functions
ided no way to correct data entry errors, often resulting in the
of data already entered. Overall, the data entry functions passed
F;st, but they could be modified to improve the user friendliness
e system and prevent simple data entry errors.
rehole Data - Borehole data consists of X Coordinate, Y
Coordinate, Surface Elevation, Longitude, Latitude, Confined
Water Level, Unconfined Water Level, and Aquifer Zone. For
every borehole location, construction and water table information
may be directly associated. Additionally, the drilling or sampling
method may be described and the stratigraphy defined for every
borehole.
Borehole data entry was easy. All borehole data were entered,
edited, and deleted from a screen that was dedicated to borehole data
input. Entering an already existing borehole ID displayed all data on
the existing borehole. If data did not exist, data fields remained
blank. Saving the edits to a borehole saved the data when the
borehole ID was pulled up after exiting and restarting the Geology
module. Deleting a borehole ID removed all of the daughter objects
(well, sample, and stratigraphy data) for that ID. The user was
prompted to make sure he/she wanted to delete the ID. If a user
wanted to change only the name of the borehole without changing
the rest of the information under it, he/she had to use the Access
database to edit the borehole ID directly. Because the borehole
name is a key field for most tables in the database, this change
needed to be made in multiple Access tables. This was a time-
consuming task and could be prone to user errors.
Well Data - As with borehole data, well data will typically consist
of location, construction, and water table information. Since a well
typically coincides with a borehole in the same location, they share
the same unique ID, x, and y coordinates.
Entering well data was easy. All well data were entered, edited, and
deleted from a screen dedicated to well data input. Entering an
already existing well ID displayed all of the data on the existing
well. If data did not exist, data fields remained blank. Data were
successfully entered for about 16 wells in creating the
MartAron jndb database. It should be noted that changing well data
did not change borehole data because the borehole was the parent
object, and its information was grayed out and uneditable from the
well data entry window. The same issue that applied to changing
only the borehole name applied to wells data entry. Deleting a well
did not remove the borehole because the borehole was the parent
object. The user was prompted to make sure he/she wanted to delete
the ID. If the user accidentally deletes a well, there is no Undo
option. All of the data have to be entered again, unless the user is
deleting the well record from inside the Access database.
Water Level Data - Water level data include date and time of
measurement, reference elevation, depth and elevation, technician,
batch number, comment, and dry indicator.
Water level data were entered in the Well data screen by clicking on
the Water Levels tab. The fields in this screen included date and
time of measurement, water level depth and elevation, reference
elevation, and technician. These fields could be edited from the
Water Levels tab. Deleting a water level was accomplished by
selecting a record to delete and clicking the delete button. There
were a number of issues with this data entry window and, although
35
-------
they do not affect the module's overall functionality, they have the
potential of wasting time by a) not checking for incorrect data entry
format in some fields (data and time); b) not being specific in its
error messages before it shuts down; c) shutting down the entire well
data screen without giving the user a second chance; d) and lastly,
by not prompting the user to save well segment data before moving
on the water level data entry. In addition, there was no check to
make sure that the depth to water level from the top of casing (TOC)
and water level elevation sum up to the TOC elevation.
Well Segment - Well segment data include start depth elevation,
end depth, inner diameter, outer diameter, material, and comment.
Well segment data were entered in the Well data screen, by clicking
on the Well Segments tab. Entering data was intuitive and the
screen was easy to use. Deleting a well segment was accomplished
by selecting a record to delete and clicking the delete button. There
were two issues with the well segment data entry screen. First, if
well segment data were not entered, the Well Data screen crashed
after displaying an error message, and all data had to be re-keyed.
The user did not get a second chance to re-enter a new well segment
type. Secondly, the program did not check whether the inner
diameter was less than the outer diameter and also did not check that
the well start depth was less than the end depth.
Sample Data - A geologic sample is defined as a physical sample
of the media taken from the borehole. This may be drill cuttings, or
a pushed or driven sample; however, it is important to note that mis
is not sampling data used for analytical chemistry. Sampling and
drilling parameters typically acquired in the field such as SPT
blowcount and recovery may be associated with each sample.
Additionally, laboratory parameters that typically come from
geotechnical laboratory tests may be assigned to the same samples.
These data (i.e. porosity, hydraulic conductivity, etc.) may then be
used as input parameters in groundwater flow models. Samples may
also be defined irrespective of location, such as material taken from
the bucket of a loader in the construction of a road to be tested for
quality control. EQuIS Geology allows the user to enter various
types of sample data. These data may be entered, edited, or viewed
from the Sample Data screen. The Sample Data screen displays
information about the borehole that the current sample is associated
with. This information cannot be edited.
Sample data were entered, edited, and deleted from a screen
dedicated to sample data input. This screen could be accessed either
from the Edit/Sample menu or from the Sample button from the
EQuIS Geology screen tool bar. Deleting a sample was as simple
as selecting the field value to be deleted and hitting the delete key on
the keyboard. Two issues with this input screen: a) there were no
data checks in place during the data entry process, for either the
Sample Parameters, or for Static Props data, and b) if a sample ID
existed, the corresponding records were pulled up. There was no
way of editing just the sample ID while keeping the rest of the data
fixed, unless the database was accessed directly from Access. That
involved a cumbersome update of multiple linked tables in the
Stratigraphy - In addition to location information, each borehole ID
may have an associated stratigraphic description. The stratigraphy
may be defined on a layer-by-layer basis as well as on a larger scale
geologic unit basis. The stratigraphy information may be used to
create boring logs or for solid modeling. The X and Y coordinates
may vary from layer to layer allowing non-vertical boreholes. The
field immediately below the X and Y coordinate fields is used to
indicate the top of the current stratigraphic layer either in depth or
elevation (according to the entry option set in the Borehole Data
screen).
• The Geologic Unit field allows the user to group lithologic
layers together in units. The user may choose an existing
geologic unit by selecting from the drop-down list. The
user may also enter a new geologic unit by typing the
name and clicking Yes when prompted to add a new
geologic unit.
• The Material Group box allows the user to select the group
from which available materials will be displayed. In
addition to the four system groups (AASHTO, USCS,
USDA, OTHER), the user may create custom groups to
which he/she can assign any material. The user may also
create new materials. The user may also change between
groups for different layers in the borehole.
• The Description box allows the user to store a description
of a lithologic layer. Each layer may have two separate
text descriptions. A description may be entered while
adding the layer. A description may also be entered (or
edited) by selecting a layer in the grid and typing in the
description.
Add Layer/Change Material is used to add a new layer to the
stratigraphy or modify and existing layer. If the user has entered a
new depth (or elevation), this button reads Add Layer. Delete
removes the current layer from the defined stratigraphy. The top
layer may not be deleted, only modified. When any succeeding
layer is deleted, the material of the overlying layer is assumed where
the deleted layer previously existed. Graph Labels On toggles the
display of depth/elevation labels on the borehole graph. In cases
where several layers exist within a short vertical interval, the labels
may run together and it may be preferable to turn the graph labels
off. This affects only the display of the Stratigraphy screen and has
nothing to do with any logs that may be created. Geologic Units
toggles the display of geologic unit labels on the borehole graph.
The user may wish to view the graph with the geologic units labeled
or the stratigraphy alone.
Stratigraphy data were entered, edited, and deleted from a screen
dedicated to stratigraphy data input. That screen could be accessed
either from the Edit/Stratigraphy menu or from the Stratigraphy
button from the EQuIS Geology screen tool bar. Stratigraphy data
36
-------
were successfully entered for 34 boreholes in conjunction with other
borehole data, while creating the MartAron.mdb database described
in the data and borehole sections above. Data entry is very easy.
Data flow was roughly from the top left to the bottom. New
materials were added during the testing under Material groups
above. The graphical display of colors was found to agree with the
scheme chosen while creating the material and its group. Overall,
the addition of stratigraphic levels was easy to perform using EQuIS.
I Editing and Viewing Functions
The ability to edit tables accurately was evaluated for two groups of
operations: Data Table Maintenance and Reference Table
Maintenance.
Data Tables - Data tables contain dynamic or project data and are
maintained in the project database (dtjablename). These data can
be edited or viewed using Data Table Maintenance. User access
levels determine editing capability. The Table drop-down list on the
Data Table Maintenance screen has three columns. The first column
shows the common name of the table. The second column shows
the actual table name (within the database), and the third column
shows the parent table (if any). The View menu allows all users (all
user access levels) to view data in the current project database. Data
cannot be entered, modified, or deleted using any of the View menu
options.
Accuracy of Data Tables was checked by opening them using MS
Access. The Access Table was confirmed to display the same
information as the Data Table. Viewing capability was tested for
users of all levels and was found to be not accessible to anyone other
than SUPER. The error was reportedly due to (he fact that the
evaluator's installation was an evaluation version of the software
which does not support full security, as the full license version does.
(It should be noted that the original software installation was made
by EarthSoft). An observation regarding the Data Table
Maintenance screen was that the data display window at the bottom
of this screen could not be adjusted by itself. Its size was
automatically adjusted in proportion to the window size. For large
monitor screens, this wastes space that could otherwise be used to
display additional data records. Logging in as the user SUPER, a
new borehole "abc" was added to the database through the data table
maintenance screen. The data table was closed, and then EQuIS
Geology was closed. On restarting the module, the borehole was
still in the data table. It was verified that casual and operator users
cannot open the data tables for editing. But neither power nor super
users other than the master user (SUPER) could access the data
tables for editing in the evaluation version of the software. Also,
none of the users created by the evaluator could even view the data
tables as mentioned in the previous section, even though all users
should have been able to do so. It was found that the safest way to
add a new borehole is through the New Borehole screen (as
discussed above) and the only way to edit an existing borehole is
through the Borehole Data screen created by clicking on the
Edit/Boreholes menu, instead of editing the data tables directly (due
to the complexity of tracking all the key fields being modified in the
affected tables). However, trying to change the key fields in a data
table produced an error message. Therefore, editing date tables is
convenient only when editing non-key fields.
Reference Tables - Reference data are maintained in the database
only in the rt_ tables. These tables provide the information
contained in the drop down lists of look up information in the
system and are edited by system administrators and users with edit
privileges. The Table drop-down list on the Reference Table
Maintenance screen has three columns. The first column shows the
common name of the table. The second column shows the actual
table name (within the database), and the third column shows the
parent table (if any).
Accuracy of Reference Tables was checked by opening them using
MS Access. The Access table displayed the same information as the
Reference table. Logging in as SUPER, accept status flags were
added for some materials in the Materials table through the
Reference Table Maintenance screen. The reference table was
closed, and then EQuIS Geology was closed. On restarting the
module, the status flags chosen were still in the data table. The same
problems were experienced with Reference Tables as had been
experienced with Data Tables.
Quick Reports
Quick Reports are a simple way to get data from the project database
into Microsoft Excel. These data may then be used for creating
plots or formatted for reporting. The top panel of the Select Quick
Report screen provides three different types of Quick Report
Sources: Data Table, Reference Table, and Query. The Data Table
source option provides a single data table to produce a report from.
The Reference Table source option provides a single reference (look
up) table to report from. The Query source option provides a list of
previously-defined queries to use to select data to report. View
Report will launch the viewing application and allow the user to
view the report.
All steps involved in generating a Quick Report (constructing and
running a query, and generating areport from the query results) were
successfully carried out as part of testing this functionality. An issue
with this functionality was that a report could be generated only
from one table at a time. Even the Query tool allowed the choice of
only one table or query at a time, and that was sometimes a
limitation of this function.
4.3.1.5 EQuIS ArcView Interface Functionality Test Results
ArcView GIS software was created by ESRI as an geographic
information system to that can be used to create and integrate
visualization functions (charts, spreadsheets, CAD drawings,
pictures, etc.); maps (land use, topographic, etc.); spatial analysis;
and other functions using new or existing data sets.
The EQuIS ArcView Project Interface permits users to view EQuIS
project data .in the ArcView GIS environment The Interface
37
-------
consists of linked tables, the EQuIS Location View, and a menu
system that supports a number of activities. A total of fourteen
functions were tested covering all aspects of this Interface. Twelve
out of the fourteen functions passed their respective test. One
function (EQuIS ArcView Default Project) had mixed results and
one function failed (Recreate the Default EQuIS ArcView Project).
The tests covered: 1) EQuIS Default ArcView Project, 2) Recreate
the Default EQuIS ArcView Project, 3) Create Custom Project, 4)
EQuIS-ArcView Project, 5) EQuIS Chemistry Menu-Change
Layout Views, 6) Selection Tool Functionality, 7) Permanent and
Temporary Theme Test, 8) Create Chemical Themes, 9) Create
Chemical Chart, 10) Post Data,-ll)AddLocation Data, 12)Toggle
Tool Theme, 13) Advanced Labeling, and 14) Cross Tab Reporting.
This Interface was invoked from both the Chemistry and Geology
modules. An ArcView project (martin_aron.apr), built from data
collected at a hazardous waste site in New Jersey was used for
testing purposes. This project contained borehole data, groundwater
monitoring data and GIS overlays of the site. Thus it could be used
to test Interface functions related to both the Chemistry and Geology
modules.
The ease of use of the functions tested was facilitated by the
graphical user interface. Most operations were point and click.
Many operations had confirmation messages, informing the user that
a particular operation was about to take place and providing the
option to not proceed with the operation. The use of the EQuIS
ArcView Interface does not require extensive training in the
operation of ArcView. The menus and dialogs added to the standard
ArcView interface from the EQuIS Chemistry and Geology
extensions were intuitive and provided for the automation of
ArcView functionality which was tailored to the EQuIS data. For
users trained in ArcView, all of the basic ArcView functions were
also available for use.
EQuIS Default ArcView Project
The user ArcView Interface supports two types of ArcView
projects, the default project located in the EQuIS root directory, and
custom projects that have been created by the user.
The EQuIS.apr ArcView project was successfully opened and
saved. However, when Test_site.aprwas opened through ArcView,
the EQuIS Chemistry and Geology extensions could not be loaded.
Message screens appeared from ArcView indicating that EQuIS
ArcView Interface extensions cannot be loaded into a project unless
that project is called directly from EQuIS.
Recreate the Default EQuIS ArcView Project
The Default Project is included with the Interface installation. It is
named EQuIS.apr and cannot be modified. Work done in the
EQuIS.apr can be saved as another filename. If the default project
is damaged or deleted by accident, it may be recreated by installing
the EQuIS Interface extension into a New project and saving that
project under the name "EQuIS.apr". This project must exist in the
EQuIS "shared" directory, e.g. X:\EQuIS\ESShared.
This test evaluated the ability to open the EQuIS ArcView project
directly from ArcView. The default file could not be successfully
recreated through the steps associated with this test. When the
EQuIS extensions attempted to load, the following message
appeared, "The EQuIS ArcView Interface extensions cannot be
loaded into a project unless that project is called directly from
EquIS." The fact that EQuIS ArcView project cannot be opened
directly from ArcView rather than from EQuIS is only an
inconvenience, not a system failure.
Create Custom Project
Custom Projects may exist in any directory for which the user has
write permission. The EQuIS Interface will create a work directory
for the custom project APR called \Eqarcwrk. The user must have
write privileges to the ArcView working directory.
A custom project was created. The EQuIS extensions were retained
upon opening. The project opened cleanly in the EQuIS ArcView
interface called through the Geology module. The steps required to
create fee custom project and open it are all menu driven and could
be easily navigated to create and open the custom project.
EQuIS-ArcView Project
An EQuIS ArcView session is started through the 32-bit EQuIS for
Windows application. The user will be prompted by the Connect
EQuIS - ArcView dialog screen to use the EQuIS default project,
or to select an existing ArcView project. A list of ArcView projects
that have been associated with a specific EQuIS Chemistry or
Geology project is presented. The ArcView project to be used with
EQuIS must have the EQuIS GIS extensions(s) installed. Six
operations were tested under this function: add, delete, and set
default each for Chemistry and Geology projects. All of these
procedures performed as claimed by the vendor and were very easy
to use. All steps were menu driven and easy to navigate - all mouse
clicks and menu selections. Keyboard input was minimal, just one
entry for a 10 character name of the ArcView project.
Add New Chemistry Project - ArcView - An existing project is
made compatible with EQuIS by installing the EQuIS Interface
extension and then saving the project. EQuIS functionality will only
be available when the project is launched through EQuIS. An
EQuIS ArcView session is started through the 32-bit EQuIS for
Windows application. The user is prompted by fee Connect EQuIS
- ArcView dialog screen to use fee EQuIS default project, or to
select an existing ArcView project. A list of ArcView projects feat
have been associated wife a specific EQuIS Chemistry project is
presented. The ArcView project to be used wife EQuIS must have
fee EQuIS GIS extension(s) installed.
This test was invoked from the Chemistry module. Through fee
project maintenance tab, a new apr was added to fee existing EQuIS
project. When this project was open in ArcView, fee ft_fleld
samples and dislocation tables were open. The user needed some
basic familiarity wife ArcView to navigate to fee Table GUI to
verify feat dtjocations and dt_field_samples exist.
38
-------
EOuIS-ArcView Project-Delete Chemistrv-ArcView Project -
This is an EQuIS-ArcView interface maintenance task. This option
deletes an ArcView APR - EQuIS Chemistry project association.
the actual ArcView project is not deleted from disk.
This test confirmed that an existing ArcView project could be
removed from the Chemical EQuIS project. The path to the selected
ArcView project was deleted from the GUI menu. A nice feature
was the confirmation message, "You are about to delete the path to
the ArcView Project, Continue?"
EOuIS-ArcView Project-Set Default Chemistrv-ArcView
Project This is an EQuIS-ArcView interface maintenance task.
Setting.an ArcView project as default moves the project to the top
position in the ArcView APR list box. In subsequent ArcView
sessions, the Connect EQuIS- ArcView dialog screen will display
with this project selected. Multiple ArcView projects may be
associated with each EQuIS Chemistry project. EQuIS Chemistry
and Geology projects will have different associations.
This test confirmed that an existing ArcView Project could be set to
the default in the Chemical EQuIS module. The existing ArcView
project, martin_aron.apr in the ESShared directory was added to the
EQuIS project and set as the default. This project opened
successfully through the EQuIS-ArcView interface.
EOuIS-ArcView Project-Add New Geology-ArcView Project -
An existing project is made EQuIS compatible by installing the
EQuIS Interface extension and then saving the project. EQuIS
functionality will only be available when the project is launched
using through EQuIS. An EQuIS ArcView session is started through
the 32-bit EQuIS for Windows application. The user is prompted by
the Connect EQuIS - ArcView dialog screen to use the EQuIS
default project, or to select an existing ArcView project. A list of
ArcView projects that have been associated with a specific EQuIS
Geology project is presented. The ArcView project to be used with
EQuIS must have the EQuIS GIS extensions) installed.
This test showed that a new ArcView project could be added to the
Geology EQuIS project. The martin_aron.apr ArcView project was
selected from the project list and successfully opened through the
EQuIS-ArcView interface invoked from the EQuIS Geology
module.
EOuIS-ArcView Project-Delete Geologv-ArcView Project -
This is an EQuIS-ArcView interface maintenance task. This option
deletes an ArcView APR - EQuIS Geology project association. The
actual ArcView project is not deleted from disk.
This test confirmed that an existing ArcView Project could be
removed from the Geology-ArcView EQuIS project. The path to
the selected ArcView project was deleted from the GUI menu. A
nice feature was the confirmation message,"You are about to delete
the path to the ArcView Project, Continue ?"
EOuIS-ArcView Project-Set Default Geologv-ArcView Project -
This is an EQuIS-ArcView interface maintenance task. Setting an
ArcView project as default moves the project to the top position in
the ArcView APR list box. In subsequent ArcView sessions, the
Connect EQuIS - ArcView dialog screen will display with this
project selected. Multiple ArcView projects may be associated with
each EQuIS Geology project. EQuIS Chemistry and Geology
projects will have different associations.
This test verified that an existing ArcView Project could be set to
the default in the Geology EQuIS module. The existing ArcView
project, martin_aron.apr in the ESShared directory was added to the
EQuIS project and set as the default. This project opened
successfully through the EQuIS-ArcView interface.
EQuIS Chemistry Menu-Change Layout Views
When the EQuIS project is invoked the menu bar will display the
EQuIS Chem options. Selecting either menu option will produce a
dropdown menu containing EQuIS ArcView functions. The
functions for the EQuIS Chem menu are described below:
• Full Data View - This menu option calls the default
window configuration for working with EQuIS data - this
consists of three windows containing the tables dt_location
and dt_field_sample, and the EQuIS Location View.
Full Location View - Selecting this option maximizes the
EQuIS Location View and removes all tabular record data
from the screen.
This test confirmed that Screen Views may be changed in the
Chemistry application. The records selected on the map (63 out of
140) matched the records selected in the dt_locations table (63 out
of 140). Upon invoking the clear selection tool, all records in the
table as well as features on the map were deselected. This tool was
easy to use; however, it did require the user to have some familiarity
with the basic ArcView tools, menus, and buttons.
Selection Tool Functionality
EQuIS locations are selected by using standard ArcView methods.
The Select Feature tool is applied to dislocation theme and the
active theme. The clear selection tool clears all currently selected
records and features.
This test compared selected features in the view with selected
records in the dtjocation table. The records selected on the map
(63 out of 140) matched the records selected in the dtjocations
table (63 out of 140). Upon invoking the clear selection tool, all
records in the table as well as features on the map were deselected.
This tool was easy to use; however, it did require the user to have
some familiarity with the basic ArcView tools, menus, and buttons.
Permanent and Temporary Theme Test
To support the creation of chemical themes, each new chemical
theme is identified as "Condition: Temporary" in the themes
39
-------
comment section under Properties. The condition of a theme,
temporary or permanent may be toggled through the EQuIS menu.
Both temporary and permanent themes may be manipulated using
any standard ArcView tool or Avenue script - the user is not limited
to the analytical capabilities provided by the EQuIS Interface.
This test verified that permanent and temporary themes were saved
with projects. Two chemical data themes were created: queryl_qry
and heptachlor_qry. Queryl_qry was toggled to be permanent.
After saving the project and re-opening it, only the permanent
theme, query l_qry was listed in the table of contents. This function
was relatively easy to use but required knowledge of the interface
to build a chemical data theme. However, this interface was entirely
menu driven, and thus, easy to manipulate. A nice feature was the
information screen that allowed the user to go back and toggle
additional themes to permanent status prior to exiting this function.
A temporary chemical data theme was created (styrene_qry). The
attribute table for this theme was opened and a chart was made for
the start depth variable. The project was saved, ArcView was
exited, and then theprojectwasre-opened. The temporary chemical
data theme, styrene_qry was present in the table of contents.
Creating the temporary chemical data theme was straightforward
using the EQuIS Chemistry menu item. The user needed basic
ArcView knowledge in order to create the chart. Prior to saving the
project, the user was warned that associated charts and attribute
tables would be deleted upon saving and exiting, unless the skip
button was pressed.
Create Chemical Themes
The EQuIS GIS Interface provides a theme builder forworking with
chemical data. Building single chemical themes for analysis supports
large EQuIS projects with a minimum of performance decay as file
size increases. The "Building Chemical Data Themes" option
invokes the Chemical Data Theme Builder window. The user is
required to select the sample matrix, date range or task, total or
dissolved, and a specific chemical. Non-required fields are set to
represent the extremes for the samples and matrix selected; these
include start and end depths and an all inclusive date range.
This test confirmed that chemical data themes could be created
using several methods. The first chemical data theme was created
from the ground water matrix, using endosulfan - all default values
(35 out of 140 wells were selected). The second theme also used
endosulfan with the start depth range as 10-40, end depth range 15-
50, and the date range of 01/01/80 to 21/1/00 (1 out of 140 wells
were selected). The third theme used toluene but reportable results
and hits only were checked (14 out of 140 wells was selected). The
fourth theme applied a color scale to the results. The screen for
creating a chemical data theme was very easy to use. Check boxes
and list boxes were a useful feature. The show SQL feature would
be useful to programmers who require knowledge of the query
statement built to create the chemical data theme. For testing
screening levels, lindane was selected and G W screen level applied -
15 records were selected. For the second screening test, the GW
screen was applied to the entire set of chemicals - 556 records were
selected. The chemical data theme builder was easy to use - list
boxes and check boxes were used to select screen criteria. It took
about 15 seconds to perform the screen against all the chemicals.
Create Chemical Chart
Using the ArcView charting function, the data in a chemical theme
table can be displayed in one or more chart types (bar chart,
histogram, scatter diagram, etc). If a Chemical Data Theme has
been used to construct a chart, and this theme is to be deleted, the
user will be presented with a dialog screen requesting that a decision
be made about the theme. It may be "skipped" or deleted along with
related charts and tables.
This test confirmed that data could be modified in the chemical
charting function. Two charts were created from different chemical
data themes - one chart for Lindane, one chart for a subset of all
screened chemicals. After selecting the delete chemical themes item,
the dialog box was displayed indicating the associated charts and
attribute tables that would be deleted along with these themes. All
temporary themes were then deleted from the view. An easy-to-use
dialog box, indicating associated charts and tables to be deleted, was
a helpful informational message. The user had the option to skip the
deletion of these charts as an option.
PostData
Selecting this option will produce a drop down list that permits the
user to select the data field they would like to post. Data may be
posted for the dt_location theme and all query themes. ONLY those
locations currently selected will be posted. If no locations are
selected, all locations will be posted. The Clear Posted Information
tool clears all labels from the EQuIS Location View for all visible
themes.
This test verified that data can be posted. From the chemical data
theme query l_qry, sys_loc_code values were posted for all wells in
the table. Two wells were selected from the attribute table. The
sys_loc_code for the selected wells matched the sys_loc_code
posted on the view. All posted values were deleted after selected the
clear posted values menu item. It was extremely easy to use this
menu item. It required only one mouse click, then the selection of
an attribute from a list box. The only drawback to the GUI was that
the attribute names appear to be truncated in the list box.
Add Location Tool
The Add Location capability permits users to create a new EQuIS
Chem or Geo location by selecting a point in the EQuIS Location
View. The Add Location tool is available while the EQuIS Location
View is active. New locations are included and the dt_location
legend updated after saving the ArcView project.
Two locations (tl and t2) were added to the view and the
allocations table using the add location tool. The presence of two
additional records in the dt_locations table was verified. This was
a very simple tool to use - the user placed the cursor on the map
where the location was to be added, then a dialog box popped up
40
-------
prompting the user to add in the attribute information such as
sys_loc_code, description, elevation, etc.
Toggle Theme Tool
Themes may be toggled between "temporary" and "permanent" by
selecting the Toggle Theme tool. After this tool has been selected,
highlighting a theme's legend will produce a dialog window that
allows the user to modify the theme condition. Multiple themes may
have their status changed before closing this window.
The chemical data theme queryl_qry was toggled from permanent
to temporary. The project was saved, then exited. Upon re-opening,
queryl_qry was not in the table of contents. The toggle tool was
easy to use - one mouse click changes status from permanent to
temporary and vice versa. A dialog box was displayed providing
information about what associated objects (i.e., tables, charts) would
also be deleted. The user had the option to skip these deletes.
Advanced Labeling
The advanced labeling tool allows the user to select a location from
the current theme, drag a leader to the position for the label, then
identify those fields to be included in a feature label.
Using the advanced labeling tool, two fields were checked on for
display (sys_loc_code and primary site code). These fields were
posted on the map and verified in the associated attribute table. For
the site selected, two features were co-located, a soil boring and a
monitoring well - both were labeled correctly. This tool was easy to
use - check boxes enabled the display of user selected attributes.
The use of a leader was helpful to offset the label from the feature.
Cross Tab Reporting
The cross-tabular report tool allows the user to produce a report
window detailing chemical data for a selected location. Reports are
created by selecting the cross-tab report tool then selecting a location
from the EQuIS Location View. Data from the active theme are
used. Chemical data themes that have been created using the
Screening Level option include the threshold value applied to each
chemical.
A chemical data theme was created for ethylbenzene. The cross-tab
tool was selected and locations were selected from the view. A
comparison of the cross-tab report and the attribute table for the
chemical data theme showed that the same stations were selected in
each window. It was not intuitive that the cross-tab tool is also used
as the selection tool for the stations.
4.3.1.6 EQuIS CrossTab Report Writer Functionality Test
Results
The EQuIS CrossTab Report Writer is a tool that can be used in
conjunction with EQuIS Chemistry. The interface allows users to
quickly create complex cross tab reports - using data from existing
EQuIS Chemistry project databases. The versatality of the EQuIS
CrossTab Report Writer allows users to design various types of
cross tab reports with this interface. The CrossTab Report Writer
module functionality evaluation was divided into four major
sections: 1) Initial User Access; 2) Open a Project; 3) Close a
Project; and 4) Open, Submit, and Save a New Query and
Generate/Save a CrossTab Report Developed From That Query.
The results for each of these sections is described below. All
functions that were tested performed as claimed by the vendor.
Initial User Access
As with the Chemistry and Geology modules, it is important to
assign and control user access. User access to the data within the
Chemistry module is critical to the creation of cross-tab reports.
After system log on was successfully completed using Login Name
"SUPER" and Project Code "NJ Demo Test" the CrossTab Report
Writer screen appeared This function was easy to use and
performed as claimed by the vendor.
Open a Project
The ability to open projects is a basic function necessary to use
chemistry data to create cross-tab reports.
The "tutor" project was successfully opened by selecting File ...
Open Project andthen selecting "tutor" from the Project Code drop-
down displayed by the EQuIS CrossTab Report Writer Login
screen. After the tutor project was selected, "EQuIS Project =
"tutor"" was displayed on the bottom left corner of the EQuIS
CrossTab Report Writer screen. This function was easy to use.
Close a Project
The ability to close projects is a required function to maintain project
security and integrity.
The "tutor" project was successfully closed by selecting File . . .
Close Project from the main menu. After the tutor project was
closed, "no active EQuIS project" was displayed on the bottom left
corner of the EQuIS CrossTab Report Writer screen.
Open, Submit, and Save a New Query and Generate/Save a
CrossTab Report Developed From That Query
The query function is integral in the preparation of cross-tab reports.
Queries allow the user to identify and pull into CrossTab Report
Writer specific data required for certain reports.
The results of a new query containing arsenic soil data were opened,
submitted, and viewed. After the results of the query were saved as
a Microsoft Excel file, a cross-tab version of the report was
generated and saved as an Excel file. The saved query was re-
opened in the CrossTab Report Writer and the two Excel files
containing the query results and the cross-tab results, respectively,
in Excel. A comparison of the different files indicated that these
files retained their original content and structure, although, as
expected, additional formatting (e.g., changes to the column widths
and shading) would be needed in order for the format of the Excel
41
-------
files to match the format displayed by the CrossTab Report Writer
for this and all additional queries.
A new query containing arsenic soil data sorted by analyte group
was opened, submitted, and the results viewed. After the results of
the query were saved as an Excel file, a cross-tab version of the
report was generated and then saved as an. Excel file. The saved
query was re-opened in the CrossTab Report Writer and the two
Excel files containing the query results and the cross-tab results,
respectively, in Excel. A comparison of the different files indicated
that these files retained their original content and structure.
Two new queries were opened, submitted, and the results viewed,
the first using Sample Results (groundwater) and the second using
Sample Results (soil). The queries erroneously displayedno results.
Changes were required, under the direction of EarthSoft, to the
cross-tab.mdb file. This allowed query results to be obtained and
saved as an HTML file. The saved query in the CrossTab Report
Writer and the HTML files, containing the cross-tab results in
Netscape Navigator, were reopened. A comparison of the different
files indicated that these files retained their original content and
structure.
A new query using Sample Results (soil), was opened, submitted,
and the results viewed. After the results of the soil query were saved
as a user defined text file called "Sample Results (soil) - Arsenic",
the evaluator then generated a cross-tab version of the report. (Note:
The strings in the user defined text file were separated by commas
and used quotation marks as delimeters.) The cross-tab report
generated with the soil results was saved as a user defined file. The
saved query in the CrossTab Report Writer and the user defined text
files containing the query results and the cross-tab results in MS
Excel was re-opened. A comparison of the different files indicated
that these files retained their original content and structure.
The operator did not attempt to generate a CrossTab Report by
opening a water level query, since the NJ Demo Test data set does
not contain water level data.
4.3.2 Confonnance to Data Exchange Standards
Test Results
The subsequentthree subsections summarize the results for Primary
Objective No. 2 - Confonnance to Data Exchange Standards for the
Chemistry, Geology, and ArcView Interface modules, respectively.
The purpose of these tests was to determine the conformance of the
EQuIS system's input and output functions to data exchange
standards.
4.3.2.1 EQuIS Chemistry Conformance to Data Exchange
Standards Test Results
Data Entry/Import
EQuIS provides two means for transferring or importing data into
the system: Manual Data Entry or Electronic File Import. If the
data to be loaded into EQuIS are only available in hardcopy format,
the data can be entered manually into the system through the manual
data entry screens or the data can be entered into spreadsheet
templates and imported into EQuIS. Alternatively, if the data are
already resident in a database or spreadsheet, or if corresponding |
Electronic Data Deliverables (EDDs) have been provided for the
field sampling and laboratory analysis work, these data can be
imported directly into EQuIS. (Note: The electronic data format will
need to be EQuIS compatible (e.g., IRPIMS or similar format or '
equivalent). In the event the data format is non-standard, (or
unknown) the instructions in the Importing Data Electronically
section will need to be followed to correlate the data to the EQuIS
database fields.)
Data for import into EQuIS can be prepared in several ways:
MS Excel© Spreadsheet Templates - EQuIS provides
spreadsheets that have been setup as import file templates.
The columns are labeled with the field names, data types,
and width. Columns are also color-coded to indicate
which are required in the EQuIS data model. The
appropriate worksheets are selected according to the type
of data. Each set of worksheets includes an information
sheet explaining the available templates. After loading the
worksheet templates with data, each worksheet is saved as
a separate text file (tab or comma delimited) to be
imported into EQuIS. The text files are imported into
EQuIS, not the spreadsheet itself. Loading data using
templates is explained in EarthSoft's documentation.
ASCII Files - Using an editor to create ASCII files (i.e.
DOS files, not word processing files). Each text line must
end with a CR-LF (carriage return-line feed) and each data
element must be separated by a tab or a comma.
• Outside Database - Use that system's export or reporting
capabilities to either create ASCII files or to load data into
EQuIS spreadsheet templates. If the data from the other
system do not match column-for-column with the
spreadsheet, the data will not import into EQuIS.
Additional information on data entry and import functions can be
found in the documentation located in the EQuIS/Doc directory on
EarthSoft's web page. Two functional groups were evaluated: 1)
EQuIS Data Templates and 2) Data Import Preferences.
EOuIS Data Templates - EQuIS provides several templates for
loading data. These templates are provided as MS Excel©
workbooks. Each workbook includes an information sheet that
explains the templates included in that workbook. Each template is
a single worksheet and represents an available EQuIS data import
format. The columnheaders on the worksheet represent field names
in various tables of the EQuIS database. The columns have been
named according to requirements in the database. The second row
42
-------
I headers indicate the order, field data type, and size. If the columns
I are moved out of order, the import will be rejected. The column
I names that represent fields whose data are required (cannot be
I empty) in the database are highlighted in yellow.
Field sample, test, and result data were entered into three MS Excel
worksheets. The evaluator then saved the worksheets as text files
and imported them into the dt_sample, dt_field_sample, dtjest, and
dtjresult data tables located in the temporary database within EQuIS
I Chemistry. A comparison of the MS Excel worksheets and the data
[tables displayed by the Data Table Maintenance screen indicated
that data completeness, correctness, and order were maintained
during the import. The evaluator also used MS Explorer to confirm
that import summaries were added to the Import.log file. An Import
Summary was also added to the Import.log file in the global
| ESShared directory on the completion of Check and Load.
Data Import Preferences - The five parameters that were evaluated
under Data Import Preferences were 1) Require Parent Records, 2)
Create Missing Parents, 3) Overwrite Existing Data, 4) Add New
Values to Lookup Tables, and 5) Use Default if Missing.
• Require Parent Records - The import requires that all
data entered have the appropriate parent records, which
prevents any orphan data in the Temporary database. For
example, information must exist in the TEST table for
result records to import. The import will check for parent
records in both the Temporary and the Permanent
databases.
The system successfully identified an error and prevented
the import of a row of test data for a sample/test without a
field sample parent record in the temporary database. An
error log was also created which showed that the parent
table record was missing for a sample. The system later
allowed the import of the test data when the import was
attempted after the "Create Missing Parents" parameter
was selected.
Create Missing Parents - If checked, missing parent
records are automatically created in the Temporary
database as the data are loaded, thereby making it easier to
load without needing to manually create parent records. If
this field is checked, then the Require Parent Records field
is unavailable. Only key field or skeletal information is
created for the parent. Additional information may be
desired before merging the record to the Permanent
database.
The system successfully created missing parent records
when importing result data for five samples which did not
have parent records in dtjest in the temporary database.
In addition to allowing the records to be imported into
dt_result, the appropriate defaults were added for the
missing parent records in dt_test, but not to
dt_field_sample or dt_sample. A message was also
written to the error log following the Import noting that
parent records had been created.
Overwrite Existing Data - If checked, the new data can
overwrite existing data when an overlap occurs as the data
are being loaded into the Temporary database. This option
does not affect data in the Permanent database.
This test was a success. The system successfully imported
a text file containing new result_values for the five
samples/results. An analysis of dt_result in the temporary
database using the Data Table Maintenance screen
indicated that the new results had replaced the previously
imported results.
Add New Reference Values to Lookup Tables - If
checked, when the data are loaded into the Temporary
database, reference (look-up) data are automatically added
to the Permanent database reference tables.
EQuIS successfully added a new reference value (TRG1)
to the result_type_code field in the Result Type reference
table as confirmed in the Reference Table Maintenace
screen. The new reference value was added during the
import of result data for five samples.
• Use Default If Missing - If checked, default values are
loaded to replace missing data as the data are imported into
the Temporary database.
The system successfully added a default value (T) to the
"T/D" column in the dt_result data table in the temporary
database as confirmed in the DataTable Maintenace
screen. The default value was added during the import of
result data for five samples.
Exporting Data
The export functions within EQuIS Chemistry enable easy retrieval
of data from the databases. Export functions send the data to a file
for use in other applications. The File Export functions associated
with each type of report are described below. EQuIS Chemistry
allows export of data to multiple products such as EQuIS Geology,
EQuIS Site Master, EQuIS CrossTab Report Writer, EarthSoft Lab
Data Checker, IRPIMS, DUMPStat, STATISTICA, GMS, MTech,
glNT, GTGS and New Jersey HazSite etc. Data export to the
ELDC module and two COTS software packages (GMS and Surfer)
was tested; the results follow.
Export to ELDC RefVals - The ELDC RefVals Export is a part of
the standard EQuIS Chemistry product. It is designed to provide the
user's set of reference values in a format that can be used by the labs
that are using the EarthSoft Lab Data Checker. It allows selection
of any or all of the reference tables. It also provides the ability to
export sample numbers or locations. The use of this information
43
-------
would require some additional setup on the part of the Lab Data
Checker user, but would then allow the lab to check to be sure that
the location and sample codes they are providing in the EDD file are
acceptable to the EQuIS Chemistry user receiving the data.
A reference file (rt_action_level_type) was exported to a *.dat file
and then successfully opened in ELDC. It was confirmed that the
data structure and content were unchanged and that the same
number of rows saved during the export were transferred during the
import.
Export to CMS - The QMS Export is an optional interface that
may be purchased separately and used with EQuIS Chemistry. This
interface allows export of data to the Department of Defense's
Groundwater Modeling System (QMS), published by the Brigham
Young University.
Monitoring well arsenic records from a single sampling event were
exported to a tabular scatter point -3D file (*.xyz) and then
successfully imported into GMS 3.0. Since the evaluator was
unable to determine how to view a data table containing the
imported data in GMS 3.0, the original *,xyz file was saved as a
*.sp3 file and then imported into EQuIS Chemistry in order to
confirm that the data structure and content were unchanged during
the export.
Exporting to Surfer - Surfer is directly linked to EQuIS Chemistry.
When the Plot tab is selected during a graphing operation, EQuIS
opens Surfer and exports two files (a griding and a * .csv file), which
Surfer uses to plot the graph. EQuIS stores these files in the current
user's directory (e.g., EQuIS/Tutor/Users/Super for a Super user
working with the Tutor database). The files are named in ascending
order as follows: TempOOOl.grd and TempOOOl.csv for the first
plot; Temp0002.grd and Temp0002.csv for the second plot; and so
forth.
A Dot Plot and a Contour were successfully plotted and viewed in
Surfer using analytical concentration soil data from the NJ database.
The plots were viewed in Surfer, exported to a *. csv file, and saved
from Surfer to a *.sfr file. The configurations for these plots were
also saved and the *.csv and Surfer files were later opened and the
evaluator confirmed that the Contour and Dot Plot were recovered
as entered. An examination of the *.csv file in MS Excel indicated
that data completeness and order were maintained during the export.
Also, no errors where identified when originally plotting the graphs
in Surfer from EQuIS and when attempting to open the file.
4.3.2.2 EQuIS Geology Conformance to Data Exchange
Standards Test Results
Once a project has been created, the first step in using EQuIS
Geology is getting data into the project. Data may be brought into
EQuIS Geology either manually or electronically. If the data are
available only in hardcopy format, the user will need to enter them
manually through data entry screens. However, if the data exist in
electronic format either in text files, spreadsheets, or another
database, data can be imported automatically, often with little or no
modification of the original files. The method used for transferring
data into EQuIS Geology depends on the data media, the quantity of
data, and the objective. In most cases, electronic data import will be
used at the outset of a new project. Where data currently reside in
text files, they can be imported often with no modifications into the
database. When data are present in spreadsheets, electronic data
import requires only a properly formatted text file.
EQuIS Geology interfaces with several COTS products, including
GMS, LogPIot, Rockworks, EVS, and Surfer. The data exchange
(import and export) between EQuIS and each of the above COTS
product was tested for interoperability. These third-party geoscience
software products may be accessed from the EQuIS Geology
module and are configured using EQuIS Administrator module.
The geological data used for these tests were developed from the
Martin. Aaron hazardous waste site data provided by the NJDEP,
Electronic Data Import - EQuIS Geology provides a method for
importing electronic data into the database. Currently supported
imports include gINT databases, GM.S borehole and material files,
and a user-defined text file format. After the import type has been
selected, the user need only select any options, indicate the file type,
and indicate the number of header lines before importing the file. If
the user has defined a Geologic Sample/Stratigraphy import, the user
must indicate which he/she is actually going to import. The default
number of header lines is 1. If the user indicates more header lines
than are actually in the file, data will be omitted from the import. If
the user indicates fewer header lines than are in the data file, the
import may not function correctly. Only file types with the specified
extension (.txt for tab delimited, .csv for comma delimited) will be
displayed. Thus, the user has to make sure that his/her file has the
appropriate extension. For comma delimited only, text strings do not
need to be enclosed in quotes. Thus, if the user intends to import
names or comments, it is preferable to use the comma delimited
format. If any of the data are unable to be imported, an error log
called importerr is created that will show the date, time, file, line
number, offending field, offending value, and error.
The data import test was organized into three major sections: 1)
Import Preferences, 2) GMS Data, and 3) User Defined Formats,
which include data for location, cone penotrometer (CPT), geologic
sample/stratigraphy, water levels, and well segments.
Import Preferences - In order to streamline data import, several
import options may be set in the Preferences screen. Selecting either
comma or tab delimited import determines which delimiter will be
selected when the user defines an import format. If the user
consistently uses one type of delimiter, making it the default will
eliminate one step in defining import formats, which is preferable.
The user may check import data for invalid characters such as
apostrophes. If the user is unfamiliar with the data he/she is
importing, or if text fields are lengthy and varied it is wise to check
the imported data. However, if the user is certain the dataset
44
-------
I contains no apostrophes or it is very simple (e.g. one-word
I description), bypassing the data check may accelerate data import.
I When fields that require a valid entry in a reference table (aquifer
I zone, phase, drilling subcontractor, engineering subcontractor) are
I imported, the reference table is checked to see if the value being
I imported exists. If the value exists in the reference table, the import
I continues. If the value is not found in the reference table, it must be
I added to the reference table.
Importing data was easy. Data flow was intuitive and logical, and
creating a template was as easy as selecting, dragging, and dropping
the fields required in the order needed. Twenty borehole ID's were
created for each of the two types of import file formats (csv and tab-
delimited). Two other files with arbitrarily placed apostrophes were
also created from these files (Commal-a.csv & Tabl-a.dat) to test
I the software's ability to identify inappropriate data. Location data
import configuration was stored in a file. The Skip Column function
was found to perform as claimed. All imports were based on a
Location Data template, which was created for this evaluation by
clicking on. the Define button of the Import screen. Imported data
were verified from within EQuIS. The program accurately
identified all apostrophes that were deliberately introduced into the
Commal-a.csv and Tabl-a.dat files and created an error log for
them. The file import function performed as expected.
CMS Data - EQuIS Geology works side-by-side with the
Department of Defense Groundwater Modeling System, GMS. In
addition to exporting borehole stratigraphy to GMS, native GMS
borehole files can be imported directly. Borehole data are used in
GMS for solid models and cross-sections. Sample parameters can
be used to interpolate to a grid for a flow model, and water level
measurements can be utilized for automatic creation of an
observation coverage for model calibration. Borehole files are used
to describe borehole data. Multiple holes can be defined in a single
file. Each hole is defined by a list of contacts representing the
boundaries between different materials. The contacts are defined by
xyz coordinates. In the case of a vertical hole, the xy locations of all
of the contacts are identical. A hole can also have a name and a
water table elevation associated with it.
A GMS input file martaron-GMS-Import.bor was created. The'
corresponding *.mat file was also renamed martaron-GMS-
Impottmat and both were put in the same directory. The borehole
information in the *.bor file and the material information in the
* .mat file were both successfully extracted into EQuIS. Some of the
imported boreholes were randomly checked (visually) to make sure
the import was successful and it was found to be so. When the file
format was correct, this function was extremely easy to use.
However, in spite of the ease of use of this functionality, the
evaluator wasted time not knowing that a *.mat file defining all
materials in the *.bor file should accompany the latter, and both
should be in the same directory. This is not obvious either from the
error message or from the online help provided with the program.
User Defined Formats - The user-defined import format is the most
flexible of all EQuIS Geology imports. The first step in defining an
import format is determining what type of data the user wants to
import. The import types include Location Data, Cone Penetrometer
Data (CPT), Geologic Samples/Stratigraphy, Water Level Data, and
Well Segments. Each data type has different requirements. Once
the user has selected the appropriate data import format, he/she must
define the parameters which actually exist in the data file. This is
done by selecting each parameter in the Available Parameters list
and dragging it to the Selected Parameters list. The user may also
select multiple parameters in the Available Parameters list (by
holding down the key) and then click the right arrow to
move the selected parameters to the Selected Parameters list. If the
user assigns an incorrect field by dragging the wrong parameter,
he/she may delete that parameter by selecting it in the Selected
Parameters list and clicking the left arrow to remove it from the list.
The evaluation of User Defined Formats included data for 1)
location, 2) CPT, 3) geologic sample/stratigraphy, 4) water levels,
and 5) well segments. Imports of all of these data types were
successful.
• Location Data - For Location import, the example
parameters that can be defined include ID, X Coordinate,
Y Coordinate, Latitude, Longitude, Total Depth, Surface
Elevation, Start Date, End Date, Driller, Aquifer Zone,
Measured Depth, and Stickup Height.
Importing location data was carried out successfully,
including using Skip Column, and retrieving saved
templates for importing data.
• CPT Data - The CPT-type data format is used for a series
of any number of linear (typically vertical) points and any
number of parameters describing the linear profile. In
addition to the borehole ID being in the first column, this
data format requires that depth be included (in any position
in the template). However, before any parameters will be
available to be selected, they must first be entered in the
CPT Parameters reference table using Reference Table
Maintenance.
CPT data were added at one depth for all boreholes
defined in the Tabl.dat file, and the new file renamed as
InputData.csv. The hydraulic conductivity parameter was
added to the CPT Reference Table. Upon importing the
csv file containing the hydraulic conductivity data, these
values were viewed from the CPT data table. Imported
data were compared to the original and were found to be
correct. CPT data were imported successfully.
• Geologic Samples/Stratigraphy - The Geologic Samples/
Stratigraphy format is used when parameters are assumed
to be constant over a discrete interval. These data are
sometimes termed "From-To" samples where some
property is constant "From" one depth "To" another depth.
_
45
-------
In addition, this formatrequires that both FromDepth
and To Depth are included in the import template.
Although the data format is the same for both
geologic samples and stratigraphy, it is unlikely that
the user will use the same file for both types of data.
If the user is importing stratigraphy, his/her intervals
will probably be continuous (0 to 14,14 to 41,41 to
53,...) whereas geologic samples are more likely to
be taken at distinct intervals (10 to 11,20 to 21 feet,
30 to 31 feet,...). Datatypes for Geologic Sample/
Stratigraphy import include Atterberg Limit data,
compaction, fluid flow, weight volume, and other
sampling data.
More columns were added to the file InputData.csv to
include compaction (weight-volume) data: moisture
content and porosity. This file was renamed
InputData2.csv. This import was representative of imports
through this window (Atterberg limits, compaction data,
other weight-volume data, etc.). The data were
successfully imported from the input file ImportData2.csv
and were verified to be identical to the original data.
Geologic sample and stratigraphy data were successfully
imported into EQuIS Geology. When sample ID was not
part of the import, EQuIS set default names for aE future
samples based on borehole ID. This function has multiple
categories of sample properties from which to choose and
an input file can have these in any order without confusing
EQuIS. This format offers flexibility in importing data
from any source into EQuIS.
Water-Level Input -Water Level Input format allows the
user to import data files containing water level
measurements. Each line of the data file represents a
unique water level measurement. The available import
fields allow the user to import date and time measurement,
as well as other information.
Water level data were imported successfully from the
input file ImportDataS .csv. It is important to include Date
and Time fields with any water level import since EQuIS
looks for them during data import or entry. The imported
data were checked to make sure they were identical to the
original data, by viewing them from the data table. This
function was easy to use especially since it deals with only
one list.
Well-Segment Data - Well segment data format allows
the user to import dat files containing well construction
data. Each line of the data file represents a well segment.
Available import fields include inner and outer diameter,
segment top and segment base, as well as other well
segment information.
Well Segment data were imported successfully from the
input file ImportData4.csv. It is important to make sure to
include Segment base and Segment top data with any well
segment import as EQuIS looks for them during data
import or entry. The imported data were checked to make
sure they were identical to the original data. This function
was easy to use as it dealt with only one list.
Electronic Data Exports
Much of the utility of EQuIS Geology comes in utilizing the export
capabilities to:
• create solid models and cross sections
• produce boring logs and well construction diagrams
• view contour plots or 3D surfaces
• use data in pre-processing for groundwater flow models
• create reports
In some cases, data is communicated directly to atarget database; for
other applications, data is exported to a flat-file ASCII format that
can be read by the destination program.
The Export screen is designed to facilitate exporting all types of
data. The Select Export Type drop-down list allows the user to
select either Borehole Data (stratigraphy), Sample Data or Water
Level Data. The tabs in the lower portion of the screen display the
data that can be selected for export. The Locations tab displays the
location (boreholes) that can be selected for export. The user may
select a specific combination, or use the Select All locations box to
export all boreholes. The user may also use the Query Tool to
further refine the locations of interest, such as querying for all
locations where the surface elevation is greater than 2200 feet. The
Data sets tab displays the CPT data sets that are available for export.
If no data sets have been defined, then no data sets will be available
for export. The Samples tab displays geologic samples and
parameters that are available for export. This tab is only available
if the user is exporting sample data. The tab allows the user to query
for samples of certain depths by entering the boundary depths and
clicking Refresh. The user may select any specific combination of
samples to export, or use the Select All Samples box to export all
samples. The user must also select which desired parameters are to
be exported. The Data Options and File Options buttons allow the
user to choose various export options. This makes it possible to
tailor each exported data file precisely to the input format of the
destination program. Error Log will be enabled if any of the
selected data caused an error during export. The user may click the
button to view a text file that describes, in detail, each error that
occurred. If no errors occurred during export, the Error Log button
will not be enabled.
Data export tests were organized by the five COTS programs to
which data were being exported: GMS, LogPlot, RockWorks, EVS,
and Surfer. Borehole data were exported to all fives COTS
programs. Sample and water level data were additionally exported
to both GMS and Surfer.
46
-------
[Export to CMS - Borehole data, sample data, and water level data
lean be exported to GMS. Borehole data include stratigraphy and
IcPT-type sample data. Borehole files exported to GMS are opened
•in GMS as borehole files. Sample data include discrete locations
Isamples where any of the following parameters may be defined at
leach sample location: moisture content, optimum moisture content,
•specific gravity, saturation, void ratio, minimum void ratio,
•maximum void ratio, porosity, hydraulic conductivity, unit weight,
I dry unit weight, saturated unit weight, minimum dry unit weight,
Jmaximum dry unit weight, relative density, relative compaction, and
I organic carbon.
I All 35 boreholes that were part of the MartAron site data were
I successfully exported to GMS. Accuracy of data exported was
I visually checked by picking three boreholes at "random" in both
I Notepad and the GMS Viewer. The GMS project was saved as
I MartAron-Final.gpr. Seven borehole samples were created and
successfully exported to GMS as 2D scatter point data. Accuracy
of data exported was visually checked by picking three samples at
"random", in both Notepad and the GMS Viewer. The GMS
project was saved as MartAron-Samples.gpr, Thirty-five boreholes
were exported as a scatter point File. Accuracy of exported water
levels was visually confirmed by picking three samples at "random"
in both Notepad and the GMS Viewer.
Export to LogPlot - Stratigraphy, CPT-type sample data, sampled
intervals and well construction data can be exported to LogPlot for
boring log plotting. EQuIS Geology creates an import file which is
directly read into LogPlot. Data Options allows the user to define
specific data options for the export. The user can select whether to
export stratigraphy as individual layers, or as geologic units. Four
other options are also available when creating a LogPlot data file:
• Stratigraphy - exports the material and any descriptive
comment for each layer (or geologic unit).
• Sampled Intervals - exports sample point as the top of
sample and sample thickness as the length of sample
recovered.
Well Construction - exports well construction elements to
produce well construction diagram.
•. CPT-type Data - exports CPT-type sample data for each
data set selected.
The Data sets tab allows the user to select which CPT data sets will
be exported. After selecting the desired options, choose the
appropriate boreholes and data sets.
Thirty-five boreholes were exported to LogPlot, along with
representative well segment information. After entering well
segment data for well SB-24, the data were re-exported to LogPlot
as SB-24(MW-2M).dat file. A Log was compiled for this well and
exported as a JPEG image, which was consistent with the data
exported. Accuracy of exported borehole data was visually checked
by picking three boreholes at "random" in both Notepad and the
LogPlot spreadsheet screens.
Export to RockWorks - EQuIS Geology will export a text file in
the borehole stratigraphy (*.atd) input format used by RockWorks.
The file contains stratigraphy information for each of the select
boreholes. These files allow to view cross sections, fence diagrams,
and other complex visualizations within RockWorks. Data Options
allows the user to export either stratigraphy or geologic units. The
user may also choose to automatically create a reference borehole,
or select a reference borehole from the available list. A manually
selected reference borehole may or may not be included in the
export. If the Include box is checked, then the stratigraphy of the
reference borehole will be included in the export file. Otherwise, the
reference borehole information will be used only as reference and
will not be included uvthe export file.
Seven boreholes (with two wells amongstthem) were exported from
EQuIS Geology to RockWorks, along with a Reference Borehole.
The corresponding master RockWorks file was martinaron.atd. The
data in RockWorks spreadsheets were compared to data that were
exported and were found to be accurate. Lithologies were preserved
during the export. This was checked using the stratigraphy screen
in the Create Cross Section screen of RockWorks. During this
export, the evaluator found that exporting data to RockWorks was
not straightforward, and depended on the exact set of boreholes
being exported (in order to create a Reference Borehole). When an
accurate Reference Borehole was not created or if it was absent, data
export was not successful.
Export to EVS - EQuIS Geology will export a text file in the
borehole geology (*.geo) input format used by EVS. The file
contains stratigraphy information for each of the selected boreholes.
This file can be opened in EVS and used for complex 3-dimensional
geologic modeling. Data Options allows the user to export either
stratigraphy or geologic units. .The user may also choose to
automatically create a reference borehole, or select a reference
borehole from the available list. A manually selected reference
borehole may or may not be included in the export. If the Include
box is checked, men the stratigraphy of the reference borehole will
be included in the export file. Otherwise, the reference borehole
information will be used only as reference and will not be included
in the export file. If any of the boreholes the user has selected do
not have stratigraphy to be exported to EVS, he/she will be notified
and an error log will be created.
Borehole data, along with a reference borehole, were exported from
EQuIS Geology to EVS readable martaron.geo file. An option for
importing geology data existed in EVS; however, the demo version
of the software being used for the SITE demonstration did not allow
viewing files other than those supplied with that version. Exporting
data to EVS appeared to be straightforward; however, this could not
be confirmed because the evaluation team was provided with a demo
version without this capability. Data Options to be chosen were
straightforward to use.
Export to Surfer - Both borehole stratigraphy data and sample data
may be exported to Surfer. Where stratigraphy is constant or
47
-------
relatively simple, layer contacts may be interpolated to define, for
example, bedrock surface elevation over a two-dimensional field.
Sample data parameters can be interpolated to a grid and then used
in groundwater flow model pre-processing.
• Exporting Boreholes to Surfer - There are no Data
Options or File Options when exporting borehole
stratigraphy data to Surfer. The stratigraphy for selected
boreholes will be exported in a comma delimited text file
with the x and y coordinates first, followed by the
elevation of each material interface proceeding up the
borehole. If a borehole with no defined stratigraphy, is
exported, the surface elevation and total depth of the
borehole follow the x and y coordinates. Some thought
must be exercised in exporting boreholes to Surfer.
NOTE: If boreholes with differing numbers of layers are
exported in the same file, the data will be difficult to
interpret in Surfer as a given column will contain one
material interface for one boring and a completely
different interface for another boring.
Borehole data were exported to Surfer input format as
martaron.csv. Three records were picked at "random" to
check the integrity of this data export. It has to be kept in
mind that plotting borehole stratigraphy in Surfer is
meaningless unless all boreholes have the same number
and type of layers which was not the case with the SITE
demonstration data. Therefore, a stratigraphy plot was not
made. However, this visualization was not a requirement
of this test and data were successfully and accurately
exported to Surfer.
• Exporting Samples to Surfer - To create a surfer data
file, select all of the samples to export. The user may query
for samples of a specified depth. After selecting the
desired samples, choose the parameters to export from the
parameter list on the right side. Neither Data Options nor
File Options are available when exporting sample data to
Surfer.
Sample data export to Surfer was a one step process (no
data options or file options required) and was easily and
successfully accomplished. Only one sample parameter
could be exported to Surfer at a time. When multiple
parameters were exported, they were input into the same
third column (the first two being the location coordinates
(X and Y), leading to mismatch of records across columns.
Moisture content data for all of the samples were exported
to martaron-samples-moisture.csv. Accuracy of exported
data was confirmed by visual comparison of three records
chosen at "random". Exporting samples was easy - it was
as simple as first picking all of the samples that the user
was interested in and then picking the sample parameters
that the user wished to export. However, there was a
problem with exporting multiple sample parameters to
Surfer. Although this problem does not affect the overalll
export functionality to Surfer, it might be inconvenient tol
users who want to export all sample data at once (insteadl
of one at a time) and then use Surfer to plot one parameterl
at a time. Borehole water level data were exported to|
Surfer input format as martaron.csv. Accuracy of
exported data was checked by visual comparison of three I
records chosen at "random". These data were also plottedl
in Surfer. Data export to Surfer was successfully!
accomplished in one step (no data options or file options]
required), and was easy to complete.
4.3.2.3 EQuIS ArcView Interface Conformance to Data]
Exchange Standards Test Results
If the ArcView Interface has been invoked through EQuIS Geology,
or an EQuIS Chemistry project has a complimentary Geology
project, the EQuIS Geology menu options will be available. EQuIS
Chemistry and EQuIS Geology projects are considered |
complimentary when they are have the same project code, e.g.
TUTOR - TUTOR. It is expected that not only the names will be the
same, but that the projects share a set of common locations. A
number of geoscience software products may be accessed using the
EQuIS-ArcView Interface. Third-party products are configured
using EQuIS Administrator module. It is important that paths and
file locations be correctly entered prior to invoking the interface.
Geological Data Test
If the ArcView Interface has been invoked through EQuIS Geology,
or an EQuIS Chemistry project has a complimentary Geology
project the EQuIS Geology menu options are available. The View
Stratigraphy option is invoked by selecting a location from either the
dt_location table or the current display. Invoking this menu option
calls the stratigraphy screen for the selected location. Once this
screen is visible, users may use the drop down list to review
lithology and sample descriptions for other locations having this
information.
The Site Test Project was opened in the Geology module, the
ArcView interface was invoked, and the boreholes were displayed
in the view. A single borehole was selected and the view lithology
menu item was invoked. All features were then selected and the
view 3D lithology menu item was selected. A 3D scene was
generated. This function was easy to use. The user needed to know
the ArcView select tool to perform this function. If a feature was
not selected, an informational message appeared indicating that a
feature must be selected.
Log Plot
This menu invokes Rockware's LogPlot application. By selecting a
single location and selecting the Boring Log menu option the user
is able to automatically produce a boring log using data that are
managed in EQuIS Geology. All rules regarding the template and
formatting of the log must be managed through LogPlot directly.
48
-------
bis function performed as claimed and required only two
erations. Borehole SB-67 was selected from the ArcView
titerface. The Create LogPlot Log menu item was invoked from the
3QuIS Geology menu. The data were automatically passed to the
ogPlot software and the log was created.
Fence Diagram
Selecting a group of locations and applying this menu option
sports data to RockWorks. The user is presented with a
onfiguration screen that allows the modification of the parameters
t will be used to produce the fence diagram. Once this has been
ompleted the user moves to a second screen where locations may
be connected to create a facet of the fence. This is accomplished by
[clicking on one location and then a second location. Repeat this
[process for each facet to be displayed. Continuing beyond this
(screen will create a fence diagram in the RockWorks product.
iBoreholes were selected through the EQuIS ArcView Interface.
(These data were automatically passed to Rockworks. The create
I fence diagram menu item was selected from Rockworks, then
I boreholes were selected from the map interface. Several error and
I warning messages appearedhavingto do with no intervals available.
I These messages were due to the lack of data for certain depth
I intervals in some site boreholes, not a system failure. The end result
I was a fence diagram.
Cross Section
Selecting a group of locations and applying this menu option
exports data to RockWorks. The user is presented with a
configuration screen that allows the modification of the parameters
that will be used to produce the cross-section. Once this has been
completed the user moves to a second screen where locations may
be connected to create each segment of the cross-section. This is
accomplished by clicking on one location and then a second
location. Repeat this process for each facet to be displayed.
Continuing beyond this screen will create a cross-section in the
RockWorks product.
Nine boreholes were selected from the ArcView interface. Then the
Create 2D cross section menu item was invoked. The data were
successfully passed to Rockworks and Rockworks was invoked.
Boreholes were selected from the Rockworks interface to produce
the cross section. Two warning messages appeared: 1) "Attempting
to plot numeric data" and 2) "Unable to find the file
E:\Site|EQuIS\Eqarcwrk\l 83.42 file. All log files mustreside on the
same directory as the spreadsheet file". This is a minor functional
failure, resulting in inconvenience for the user in relocating files
after the fact
Export Borehole Data to CMS
Boreholes are exported to the Groundwater Modeling System
(QMS) using this menu option. The user first selects a set of
locations for export. Next, by selecting this option data are exported
and made available for manipulation within GMS.
Eight boreholes were selected from the EQuIS-ArcView interface
and these were successfully passed to the GMS application. This
interface was easy to use - it involved only the EQuIS selection tool
and the GMS menu option.
Export Borehole Data to EVS
Export of borehole data to EVS is similar to Log Plot, except that
the Launch EVS Application menu options is selected.
This function performed unsuccessfully. An error message
appeared from the EQuIS ArcView interface. The EVS application
was never launched. Even though the test was unsuccessful, the user
interface was easy to use - similar to exporting data to GMS - one
selection tool, then invoking the Launch EVS menu option.
Export/Import data to Surfer
Data can be exported to Surfer from EQuIS through a number of
industry standard data exchange formats including *.dxf and *.wmf
files. Surfer functionality, such as 3-D wireframe displays, and grid
and contour functions, can be applied to the data.
An * .wmf file was exported to the Surfer sample directory. This file
was successfully imported to Surfer through the basemap option.
This procedure required knowledge of ArcView functionality to
export a view.
Chemical Data Statistics Button
Activating a chemical data theme allows the user apply the chemical
data theme statistics button. A statistical summary window is
produced for the active theme. When a set of locations is selected
only chemical records belonging to these locations will be included
in the analysis.
A chemical theme table was created for toluene. The chemical
theme statistics menu item was invoked. The statistics generated
showed a sample size of 48, which matched the number of selected
records in the query l_qry chemical theme table. This function was
easy to use - once the chemical theme table is created, the user
selects the chemical theme statistics item from the Geology menu.
4.4 QUALITY ASSURANCE/QUALITY
CONTROL
QA may be defined as a system of activities the purpose of which is
to provide assurance that defined standards of quality are met. A
QA program is a means of integrating the quality planning, quality
assessment, QC, and quality improvement efforts to meet user
requirements. However, standard QA objectives for data quality
indicators (precision, accuracy, etc.) do not apply to this project. For
this evaluation of the EQuIS Software, QA efforts centered
primarily
on the documentation of the various functionality tests performed for
49
-------
each of the software modules. In addition, QA efforts assessed the
impact that changes to and deviations from the test plan had on the
achievement of project objectives, and an evaluation of the
completeness of the testing planned in support of the project's
primary and secondary objectives
4.4.1 QA/QC Conclusions and Data Quality
Limitations
Primary objectives for the evaluation of the various modules of the
EQuIS software were achieved through the execution of the test
plans described in the QAPP. Modifications to the test plan, arising
from efforts to limit redundancy, address time and financial
constraints, correct errors in specific function directions, or remove
from evaluation seldom used or obsolete functions, were
documented in the evaluation tables through use of redline strikeout
and highlighted edits. While some of these modifications resulted
in certain functions not being evaluated, in general these functions
were not considered critical to the overall functionality of the
modules affected. Two minor procedures of the Chemistry module
evaluation (one in the Administration and one in the Data Entry
function tests) were not performed as planned; other test procedures
for these functions provided an overall evaluation. Overall, test
results were well documented and complete, and modifications to
and deviations from the test plan were described and justified, and
did not impact overall project objectives.
4.4.2 QA Efforts and Results
As part of the QA oversight for this evaluation, the results recorded
on the various test matrices were reviewed as the evaluation of each
software module began. This initial review centered on the
completeness of the documentation, whether the conclusions drawn
were supported by the test results recorded, and whether any
changes to the test protocols were described, explained, and/or
justified. Changes were made to future entries into the test matrices
as needed. QA review of the results entered onto the test matrices
was performed periodically throughout the evaluations, as well as on
a random sampling of results at the conclusion of the evaluation for
each module. The review of the completed test matrix for each
software module is summarized below.
4.4.2.1 QA/QC - ELDC Functionality Test Results
Deviations from the original test plan centered on the data set
provided, along with a minor change in the Y2K test sequence.
These deviations had no impact on the achievement of project
objectives since the data set used could be manipulated to allow
evaluation of all desired functions as described in the QAPP. All
tests planned in the assessment of this module of the software were
executed as described in the test matrix.
4.4.2.2 QA/QC - Chemistry Functionality Test Results
Numerous edits and revisions to the test procedures were requir
and were documented in project files. Specific review comment!
about each of the evaluation subcategories are provided below.
During the evaluation of the Chemistry module sy
administration functions, the ability to create new projects
tested. The creation of the new projects was confirmed through the
System Administration function, as per the test plan. However,!
projects were not opened in EQuIS Chemistry to confirm that new|
project information created in System Administration was retaine
and could be reopened in both System Admin and EQuIS|
Chemistry.
Among the EQuIS Chemistry data entry functions tested werel
functions to allow for manually entering lab sample data. The initial I
test procedure was revised, to avoid redundant tests. However, I
manual entry of lab sample data was not performed since lab sample I
data were not part of the database used ("NJ Demo Test"). I
However, it should be noted that all other types of data entry
procedures (field data, test data, and result data) were successful,
while parameter group entry was not performed due to time and |
resources constraints.
4.4.2.3 QA/QC - DVM Functionality Test Results
Results for the evaluation of the Data Verification Module of the
EQuIS software indicated that, with minor modifications, all test
procedures were performed as described in the QAPP.
4.4.2.4 QA/QC - Geology Functionality Test Results
The evaluation of the EQuIS Geology module was completed in
accordance with the test plan presented in the QAPP. Reasons for
the modifications to the test procedures were clearly described as
results were entered onto the evaluation table.
4.4.2.5 QA/QC - ArcView Interface Functionality Test
Results
No modifications were required to the test plan and all procedures
were performed in accordance with the steps given in the summary
table.
4.4.2.6 QA/QC - CrossTab Report Writer Functionality Test
Results
Minor modifications to the evaluation test plan did not impact the
overall assessment of the software functions.
4.4.2.7 QA/QC - Chemistry Data Exchange Test Results
A second primary objective of the evaluation for the Chemistry
module was to determine ability of the software to perform data
exchange functions. Several tests of this function were not
performed due to time and financial constraints.
50
-------
JU.2.8 QA/QC - Geology Data Exchange Test Results
he Geology module was evaluated to determine the ability of the
nodule to perform import/export functions as part of the second
primary project objectives. One new test procedure was added to
he original test plan to assess a function not previously included.
^.11 other tests were performed and recorded as per the test plan.
M.2.9 QA/QC - ArcView Interface Data Exchange Test
Results
The test plan for the evaluation of the second primary objective for
tie GIS ArcView Interface was followed. The automatic data
schange functions were tested as planned without modification.
1.5 RESIDUALS
Because .EQuIS is not a treatment technology, no processing
(residuals will be generated.
_
51
-------
SECTION 5
OTHER TECHNOLOGY REQUIREMENTS
5.1 ENVIRONMENTAL REGULATION
REQUIREMENTS
There are no regulatory requirements on the software itself. The
content and format of reported data may be defined by applicable
Federal, state, and local regulations. Data query and cross-tab
reporting functions offer the versatility to meet many of these
requirements.
5.2 PERSONNEL ISSUES
Personnel issues related to this software center on the capabilities of
staff to learn and implement the EQuIS software. The software is
complex in that there are several modules and numerous functions
within each module. In addition, the use of COTS software may
require further specialized expertise. However, these factors are also
what provides the software with power to perform the required
tasks. During the demonstration, personnel with experience in
Windows-based products and basic spreadsheet and word
processing software were able to learn and apply the major functions
after a fairly short learning curve. Based on the experience of the
evaluators, it is believed that anyone can learn to be a basic user of
this software. Obviously, it is expected that the time required to
learn and utilize the system will, to some extent, be impacted by the
degree of experience of the users. A typical application will,
however, require one staff with experience in using basic scientific
software. Full utilization of COTS software will require an
individual with more experience (e.g., GIS software, databases, and
3D modeling programs) or a significant commitment and aptitude to
learn to fUlly utilize these programs. In addition, a knowledge of
various reporting requirements will be needed to ensure that data are
managed and reported in a manner consistent with applicable
regulatory requirements.
5.3 COMMUNITY ACCEPTANCE
Community acceptance of a technology is affected by both actual I
and perceived hazards. The only aspect of the EQuIS technology I
that may uniquely affect community acceptance is that this!
technology may assist site managers in demonstrating site activities I
to local communities through the use of data management, quality |
assurance, and visualization functions.
Two Visitors' Days were held: one on June 21,2000 in Philadelphia, I
Pennsylvania and the second on November 14, 2000 in Seattle,
Washington. Presentations were made by EPA-NRMRL, the
developer, and various software users. Brief overviews of the SITE
Program and the software were provided. During each Visitor's
Day, interactive technology demonstrations were available from |
EarthSoft and a number of environmental consultants which have
partnered with EarthSoft. Participants in Visitor's Day included
regulatory personnel, remediation contractors, and members of the
general public. This is an example of an activity to inform the
public and improve community acceptance.
52
-------
SECTION 6
TECHNOLOGY STATUS
rior to the SITE demonstration, the EQuIS software had
neen implemented at several state and Federal offices to
lanage environmental data from the regulated community.
I According to EarthSoft, EQuIS is currently being used in 10
[states and 5
ITable6-l. End Users of EOuIS.
EPA Regions (see Table 6-1).
Representative case studies are discussed in greater detail in
Appendix B.
STATE/REGION
STATES
Colorado HMWMD
Delaware NREC
Florida DEP
Mississippi DEQ
Nebraska DEQ
Nevada DEP
New Jersey DEP
New York DEC
Pennsylvania DEP
Rhode Island DEM
POINT OF CONTACT
Candy Thompson/ Andy Putnam
Steve Johnson
Kathleen Lurding
William MoKercher
Steve Kemp
Bill Story
Izak Maitin / Irene Kropp
Koon Tang
Mike Arnold
Joe Martella
TELEPHONE NUMBER
(303) 692-3424
(302) 395-2622
(850) 921-9823
(601)961-5731
(402)471-0803
(775)687-4670
(609) 777-1763
518402-9549
(717) 783-9475
(401)222-2797x7109
E-MAIL ADDRESS
Andrew.PutnaiTi@state.co.us
siohnsonfHidnreo.state.Je. us
kath1een.liirdina@deti.state.fl.us
Willie McKercheriiadeo.state.ms.us
Steve.Kemp@NDEO.State.NE.US
bstorv(g).ndep.carson-Mtv.nv.us
imattin@dep.state.n.i.us
kstang(2iaw.dec.state.nv.us
miamoldfgistate.pa.us
imartelK8idem.state.ri . us
West Virginia DEP
Rick Doneghy
304-558-7763
rdoneghy@MAlL.DEP.STATE.WV.US
Also, we have received a verbal agreement to purchase a Five User License from the California DTSC, and think we will sell systems in the near future to the Maine
DEP and Texas NRCC.
EPA Regions
Region 1
Region 2
Region 3
Region 4
Region 5
Carolyn Casey
Andy Grassland
Matt Mellon
David Jenkins
David Wilson
(617)918-1368
(212) 637-4436
(215) 814-3168
NA
312-886-1476
Casev.Carolvmgiepamail.epa.gov
crossland.andy@epamail.epa.gov
mellon.niatthew@epa.gov
Jenkias.Davagjepamail.ena.gov
WOON.DAVlPfaiepafflaH.epa.gov
EQuIS is also being used by several DoD and DOE facilities, including Argonne, Rocky Flats, several Naval Shipyards, Warner-Robins AFB, several Port
Auth/wfifiG anH cpvpfal ArtYiv rVvfnc nictripfQ inclndinp Sacramento and Korefl. __^^_
53
-------
-------
APPENDIX A
VENDOR CLAIMS
Background
Today, many companies can not make heads or tails of their data.
llriey have little data consistency, poor data quality, and can make
•little use of the data to support decisions and analysis. Poor data lead
Ito poor decisions. These decisions flow up from industrial chent to
[state regulators to federal regulators. Without addressing data
I quality early in the process, far before the data are actually submitted
Ito the EPA, little can be done to improve the EPA's decision
I making ability. Unfortunately, most EPA systems are only for fee
I EPA, and are so large and expensive that they will never be very
I useful to industrial clients. Islands of Automation are the result, as
I systems are built that are unable to share data. By comparison,
I EQuIS can be used by small property owners, industrial clients,
I consultants, labs, and then by the regulatory community. EQuIS was
I not originally written for the regulatory community, but we have
accidentally found that we can help. EQuIS can be used as a small
local system, a regional departmental system, and as a large
| Enterprise system. In this manner, the data are high quality earlier
in fee process, and indeed, throughout fee process. In fact, in many
cases, fee data are available from fee industrial chent in EQuIS, and
can be submitted as is. Never before has this been fee case. Never
before has fee EPA been able to obtain data from more than a site
or two in fee 'native' or original format, and been so able to
immediately judge data quality and completeness.
EQuIS is today fee most widely used subsurface data management
system in fee world. EarfeSoft maintains feat 'standards' are not set
by committee, but rather by market forces and volume. The market
has established EQuIS as fee industry standard. What this means is
feat innovation is extensive and ongoing, because fee large costs can
amortized over a larger chent base. Because of the large installed
base of EQuIS users, continued innovation is guaranteed. Today,
EPA Regions 1,2, and 5 are large EQuIS users, and EPA Regions
3 and 4 are evaluating EQuIS. Today, fee states of West Virginia,
New Jersey, Colorado, Delaware, Nebraska, Nevada, and New
York have multiple licenses of EQuIS. Several other states have one
or two licenses of EQuIS, and several other states are either
evaluating EQuIS, or we are 'in Purchasing' to procure EQuIS.
Furthermore, almost all of fee largest US consultants today have
EQuIS, and worldwide, nearly 2,000 licenses of EQuIS have been
purchased since its first release in 1996. Because EQuIS is not
limited to use by just fee EPA, States, fee Army, Navy or Air Force,
or fee United States, EQuIS is ubiquitous in many projects around
fee US.
Because of EarfeSoft's Open Systems business practices, a viable
and active 'shareware' market is emerging for EQuIS modules and
value added capability. Several new EQuIS modules are emerging
that were not written by EarfeSoft. And in fee truest test of Open
Systems, EQuIS clones are emerging.
Technology Advantages
The value of an electronic data management system such as EQuIS
is feat data are of reliable quality, and are readily available and easy
to review, report, and utilize for model construction and analysis.
Projects long completed and archived can be accessed in a fraction
of fee time that would be required for hardcopy reports or logs.
The EQuIS user interface provides an extensive suite of reporting
tools and interfaces for sending data to many different visualization
and analysis applications, fee open system design also allows fee
development of custom interfaces without being bound by fee cost
and time requirements of fee developer as is often fee case wife
closed, proprietary systems. Another benefit of fee open systems
architecture is that fee user is not locked into a specific visualization
or analysis application. They may choose between any of several
popular tools for creating borehole logs, groundwater models, solid
models, or performing other types of analysis.
Furthermore, EarfeSoft's Open Systems architecture provides fee
opportunity for a data manager to access data directly, outside of fee
user interface.
A1
-------
Caveat
As with any evaluation this complete and thorough, by the time the
evaluation is completed, a new version of EQuIS is already
released. Software is such a moving target that any single point in
time is not completely representative. We chose to evaluate EQuIS
Geology Version 3 instead of Version 2, because of added new
features, even though Version 3 was still in 'beta' status.
EarthSoft, at the time of this writing, in Spring of 2002, is
preparing for a major overhaul of the entire system, and a
complete facelift of the user interface, in response to continued
changes by Microsoft, ESRI, and our other development
platforms and integrated third party products.
Upcoming developments include:
A shift to ArcGIS (ArcView 8) away from ArcView 3. This is a
huge change for us since ArcView 3 used the unique and
proprietary Avenue programming language, and ArcGIS uses the
ubiquitous Visual Basic language. Since the rest of EQuIS is
written in Visual Basic, our code can now be reused in the GIS
interface, and functions need not be re-coded. This leads to our
GIS interface being much more powerful, supportable, and
Windows-like (i.e., easier to use)
A complete re-write of the View module, producing a
much simpler version (EZView) and an equally
powerful but more modem version (QueryBuilder).
A complete redesign and rewrite of the Data
Verification Module (DVM). The new Data
Qualification Module is a completely new product,
replacing the DVM, which was too limited, restricted,
rigid, and hard to use.
The new EQuIS Enterprise is a Web-enabled, Oracle or
SQL/Server system for organizations with a large
number of users or a large number of projects, or
wishing to easily run regional (i.e., Watershed wide)
queries against the data.
Many bug fixes, patches, and enhancements. We have |
released new versions of the ELDC, Rockworks
interface, gINT interface, EVS interface, and the GMs|
interface, after the SITE Program testing started.
EQuIS Air is now under design.
The Field Data Checker, recently written at the request |
of an EPA Region, was not tested.
The exciting new Pocket EQuIS was not available at 1
time of the evaluation. This PDA software supports
electronic field data collection activities.
A new 'Dashboard' is available that provides a link to
Document Management, and allows for the posting of
EQuIS databases on a private network or Intranet.
A new module is nearly available to support Time
Series Data is now available, that provides links to
automatic data collection equipment, via manual or
wireless data collection techniques, and provides data
reduction capability such that continuously monitored
locations can report a single value calculated a number
of user-defined ways.
A2
-------
APPENDIX B
CASE STUDIES
tie power of EQuIS in environmental management and decision
nalysis: case studies in Colorado
*i. Putnam1, S. Weaver, C. Thompson1, & M. Beard2
\Colorado Department of Public Health & Environment, U.S.A.
\EarthSqft, Inc, U.S.A.
Abstract
lelecting an optimal remediation strategy for environmental sites is
lever a straightforward, easy process. Successful management and
decision analysis requires not only the availability of spatial,
[chemical, and geologic data, but also an integrated environmental
quality information system which allows a project manager to utilize
-and analyze the data. EarthSoft's Environmental Quality
•Information System (EQuIS6) has been implemented by the
(Colorado Department of Public Health & Environment (CDPHE)
•to aid in achieving these data management and analysis objectives.
EQuIS tightly integrates data management with industry-standard
visualization and analysis tools resulting in an environmental
management system that allows the user to easily investigate "What
If...?" scenarios. The EQuIS solution is enabling CDPHE to
conduct a more comprehensive and effective evaluation of
environmental impacts, migration pathways, fate and transport
mechanisms, appropriate remediation methods, effects of
remediation, and compliance. Examples illustrate how CDPHE is
using EQuIS and what benefits have been derived therefrom.
Introduction
The selection of an appropriate remediation methodology requires
extensive field sampling. While data collection is a critical step in
a site characterization effort, collection alone is not sufficient. Far
too often, data painstakingly obtained from field investigation,
particularly geologic data, cannot be located or easily used. Such
data is often hardcopy instead of electronic, and may even be stored
off-site in a nearly-forgotten repository. When the effort required to
find and obtain data from a previous study is comparable to
collecting the data in the first place, it is just as good as having no
data to begin with!
The value of an electronic data management system such as EQuIS
is that data is readily available and easy to review, report, and utilize
for model construction and analysis. Even projects long completed
and archived can be accessed in a fraction of the time that would be
required for hardcopy reports or logs.
Data Usability
One scenario frequently encountered among environmental data
managers today is the 'data hostage'situation. This problem may
result not only from the use of a proprietary database that prevents
'back-door' access to data, but also from Has process of storing data
in a particular visualization or analysis application. The open
systems architecture, upon which EQuIS is based, provides the
opportunity for a data manager to access data directly, outside of the
user interface. This philosophy is rapidly gaining wide acceptance
as users are able to go directly into the database to build custom
queries and write need-specific applications for reporting and
formatting data. Whereas the EQuIS user interface provides an
extensive suite of reporting tools and interfaces for sending data to
many different visualization and analysis applications, the open
system design also allows the development of custom interfaces
without being bound by the cost and time requirements of the
developer as is often the case with closed, proprietary systems.
Another benefit of the open systems architecture is that the user is
not locked into a specific visualization or analysis application. They
may choose between any of several popular tools for creating
borehole logs, groundwater models, solid models, or performing
other types of analysis. This flexibility provides the opportunity to
switch to a higher-level application if the currently used application
is not adequate without migrating data to a new system. Many
proprietary systems provide their own visualization and analysis
tools and if these do not prove adequate, th& user is at mercy of the
developer or is required to migrate their data to a more suitable
system. Proprietary evaluation tools are also often poorly
documented as to the application of algorithms and are not generally
accepted throughout the industry. This creates a situation where
disagreement may occur between a facility and the regulating entity -
as to the applicability of the evaluation tool.
The dangers of storing data in a specific visualization or analysis
B1
-------
tool are illustrated by the hypothetical case of a project manager who
has successfully used a groundwater modeling environment to
produce the results needed by his client. However, when the client
then needs borehole logs, cross-sections, or solid models in addition
to the groundwater modeling, the manager is left in a quandary.
Heretofore, the solution has been the costly investment not only in
an additional product and the time required to learn the procedures
necessary to produce the desired results, but also in understanding
file formats and getting the appropriate data into the new application.
EQuIS greatly simplifies this task by facilitating the creation of
borehole logs, cross-sections, fence diagrams, reports, contours,
groundwater models, solid models, and more all very quickly and
easily... without having to understand the intricacies of specific file
formats. This mechanism permits more time to be devoted to
science and analysis rather than the overhead of a specific piece of
software.
The Colorado Department of Public Heath and the Environment,
Hazardous Materials and Waste Management Division has chosen
EQuIS to facilitate better understanding of contaminated sites in
Colorado and improve the decision making on cleanup of these sites
Sitel
An unlined municipal solid waste landfill composed of two separate
areas has been in operation since 1968. The northernmost area is 60
acres in size; the southern area, slated for future expansion, is 259
acres.
The landfill began accepting waste in 1968. To comply with state
regulations, agroundwatermonitoring system was installed in 1990.
This system consisted of 4 monitoring wells. Four more wells were
installed in the expansion area to the south (See Figure 1).
Monitoring well MW-4A serves as the background well and
provides upgradient water quality data; MW-2 is the
compliance/downgradient well for the existing 60-acre site.
Natural ponding started around MW-3 in October of 1995. The
facility operator later created a retention pond out of the low area in
the summer of 1996. A rising trend in water levels was seen in
MW-2, downgradient of the retention pond, soon after. In
November of 1997 the east cell of the landfill was capped and the
retention pond was removed. At this point the rising trend of water
levels in MW-2 reversed (See Figure 3).
A statistically significant trend of increasing bicarbonate and other
inorganic constituents was observed in MW-2. This trend started in
the first quarter of 1997. An increasing.trend in the concentrations
of certain organic compounds started in the second quarter of 1997.
This is best illustrated by the concentration of methylene chloride
over time. A theory was presented by site personnel that the capping
of the landfill caused the increase of chemical constituents in MW-2
by restricting the volatile chemicals from escaping to the atmosphere
and forcing them into a new migration path. Therefore, they
proposed using an extraction system as an interim corrective
measure. They also installed a new well, MW-9, to function as 1
downgradient, between MW-2 and the properly line. A ne|
upgradient well, MW-10, was also installed. These wells we
installed in September of 1998. Elevated organic compounds ha\|
not been found in MW-9, the new downgradient well.
The Department began evaluating the landfill site using EQuIS i
the fall of 1998. All available geologic data and chemist
information from 1994 was loaded into the database. For
geographic information system (GIS) display, topographic maps z
historic and recent aerial photographs were obtained. Geospati|
locations were obtained by taking GPS readings at a number
surficial features at the site. These data and geographic in
allowed the project manager to perform more complex evaluatioii
of the site geology and groundwater monitoring data.
The registration of all data, maps, and aerial photos into on
coordinate system allowed a more sophisticated temporal, chemic^
and geologic depiction of the site. The 1968 aerial photo (
landfill) shows an old streambed directly underlying the
storage cells of the existing landfill (Figure B-l). It was expecte
that the alluvium underlying the stream system would create an are!
of high permeability. This was verified by the geologic cross!
section (Figure B-2) seamlessly created by the integration of th|
warehoused data to ageologic visualization application. This forme
stream drainage makes an excellent path of migration foj
contaminants coming for the landfill. MW-9, the new well propose
to depict downgradient conditions, was set in the same draina
system, but was actually found to lie across the channel of th^
stream from MW-2 and not directly downgradient.
A group of time series plots were created from database queries tq
explain what is occurring at MW-2. Figure B-3 shows water levels'
vs. time at MW-2. There is a marked increase in the water levels
after the installation of the retention pond.
Soon after the installation of the cap the water levels start to drop,!
but do not return to their original level. Bicarbonate concentrations!
increase coincidentally with the increasing water levels. However,!
these bicarbonate concentrations do not decrease with the water!
levels, although their concentrations no longer continue to rise. Thel
methylene chloride concentrations do not start to rise at the samel
time as the bicarbonate and water levels (Figure B-4). The organic ]
constituents begin to increase with the start of construction of the I
final cover. The organic chemical concentrations start to drop at the j
installation of the gas extraction system.
A number of conclusions can be reached from this case study. The I
first is that the elevation of the bicarbonate concentrations is not |
related to the capping of the landfill but is, instead, connected to the
elevated water levels. In the relatively arid climate of Colorado, an
elevation of alkalinity is often observed in wells where the water
level rises dramatically above the previous saturated zone. The
elevated water levels appear to be caused by the presence of the
retention pond that was expanded in the summer of 1996. It also
B2
-------
igure B-l. Landfill site photo
5,525
5,525
ireB-2. Cross-section of old stream channel
_
B3
-------
era
i
ft Above Mean Sea Level
5
ro
o
S
n>
B
i
3
CA
cn
CD
June-94
August-94
Oclober-94
December-94
Febniary-95
April-95
June-95
August-95 -
October-95
December-95 -
February-9S -
April-9S-
June-96-
August-96 -
Oclober-96 -
December-96 -
Febmary-97
ApnT97
June-97
August-97
October-97
Decetnber-97
February-98
Aptil-93
June-98
August-98
October-98
December-98
Febraaiy-99
April-99
ug/L
Ponding at
WIW-3
Creation of
Retention Pond
Final Capping of
East Cell
s
-\
sa
r4-
t
J
ft)
m
m
m
O
5
o
i
IO
Creation of Retention
Pond
Final Capping of East
Cell
Startup of Gas
Extraction System
I
O
2.
CB
-------
ppears that the installation of the cap and the removal of the
ention pond removed the source for the rise in water level
evations and, coincidentally, stopped the rise in bicarbonate
centration.
.„ concentrations of methylene chloride and other organic
Imtaminants do not appear to be directly related to the rise in water
jvels. The rise appears to start before the installation of the cap,
Lt may be related to the placement of a temporary cap previous to
le placement of the final cap. The gas extraction system is having
•beneficial effect exhibited by a decrease in the concentrations of
le organic contaminants. This lends support to the option of letting
3 facility continue to use it as a remediation method.
lly, the use of the integrated data system has brought up a
[uestion about the usefulness of the data from MW-9. MW-9 was
t in place to monitor whether the plume of contamination was
-grating off site. The location of this well is in the same sediments
j MW-2, but because of the complex nature of alluvial sediments
1 probably not in direct hydraulic connection with the known area
If contamination. A new well should be placed on the same side of
lie channel as MW-2 to determine if the plume is migrating off site.
like 2
i defunct metal plating shop located in an industrial area was found
3 be out of compliance with numerous environmental regulations.
"hese included the storage of hazardous waste onsite for a greater
period of time then was allowed, multiple instances of poor
housekeeping, unregulated discharge to the storm sewer, and the
Humping of hazardous material on site. The site was closed by the
Environmental Protection Agency (EPA) and local fire authorities
jn the early 1990s, and approximately 100 cubic yards of
pontaminated soil was removed from the south end of the building
,vhere the operators of the facility had been dumping waste. This
i done as an emergency response to remove a potential source of
,.~undwater contamination. The EPA also installed two monitoring
.veils on the site. Testing of water from these wells showed elevated
(concentrations of cyanide and certain metals. The EPA placed the
•site under a corrective action order that made it impossible for the
•property to be redeveloped due to the liabilities that were involved.
I Several issues needed to be resolved prior to the corrective action
I order being removed. Since it was not known if all of the
I contaminated soil was removed initially, there may have been
I contaminated material still acting as a source for groundwater
I contamination. One possible location for this contaminated material
was underneath the floor of the site building. Also, the direction of
groundwater flow was not known at the site. It was possible that the
contamination was moving into a stream nearby via shallow
groundwater. This stream flowed into a river just below the site, and
there was a municipal drinking water intake serving a population of
approximately 30,000 people downstream of the confluence of these
two surface water bodies. Additionally, there was a drinking water
well within 90 feet of the property which produced water for a
family of six.
'Further investigation needed to answer certain questions. If
groundwater was flowing toward the stream was the contaminated
groundwater influencing water quality? If so, was the drinking
water intake in jeopardy? Was the drinking water well already
affected? Was there significant residual contamination under the
building on site? A sampling plan was developed to answer these
questions. To better understand the site, a CIS project was
constructed and used in the development of the sampling plan.
Based on the sampling plan, 11 monitoring wells were installed
around the site. Nine sub-slab borings were installed beneath the
building to check for soil contamination. Samples were collected
from the concrete floor and plating residue within the building and
the soil beneath the building. Groundwater.samples were collected
from the 11 monitoring wells and the residential well. Surface water
samples were collected from the stream and river. The elevations of
the monitoring wells were determined by survey. Locations for the
well points were collected using GPS. The lithology, well
completion information, and water level readings were loaded into
EQuIS. This allowed for the creation of cross-sections of the site
and bedrock and groundwater contour maps.
Laboratory results of the samples collected showed that groundwater
was contaminated at the facility. Based on the cross-sections it was
determined that a bedrock high was situated to the south and west of
the site and the bedrock surface greatly influenced groundwater flow
direction. This was also apparent in contour plots of the bedrock
elevations and the groundwater gradient. This high prohibited the
flow of groundwater toward the stream. Therefore, the site did not
affect the water quality of the stream and river. The drinking water
supply was safe. The groundwater contour map showed that
groundwater flow was to the northwest and that the drinking water
well was not down gradient of the contamination (Figure B-5).
Groundwater contamination, though present, attenuated and was
below regulatory limits at the site boundary to the northwest.
Although there was residual contamination at the site, this study
showed that it did not warrant further remedial action. The results of
this study allowed the EPA and state RCRA group to lift the
corrective action order from the site and open the site for
redevelopment.
Site3
Rocky Flats has implemented EQuIS on a much larger scale. The
Rocky Flats Environmental Technology site, owned by the U.S.
Department of Energy (DOE),, manufactured components for
nuclear weapons for national defense until 1992. The plant is
currently undergoing environmental cleanup, waste management,
and decommissioning. The industrial complex of more than 100
buildings is located in the center of nine square miles of
undeveloped land northwest of Denver. Rocky Flats stores the
B5
-------
> Patentfemetric Line, Hydraulic Gradient to the Northwest
M*nit«rinji Well Locations
Figure B-S. Site plan and potentiometric surface map for metal
plating site
largest quantity of radioactive and hazardous wastes in Colorado.
Due to its size, all of the data from Rocky Flats has not yet been
completely loaded into EQuIS. As of the end of 1999, geologic and
well completion information has been input Approximately 1300
borings, of which 1200 are wells, have been entered. As more
borings are being drilled at this site, these are being uploaded into
the system. There are 22,000 water level measurements for these
wells. Chemical data is being migrated into the system from two
older databases. Data from an older state system are being loaded
to help personnel evaluate the quality of remediation efforts taking
place at the site
From this data a groundwater model was constructed to help
evaluate groundwater activity. See Figure 6 for the bedrock tin
created for the model. The database was shared with site personnel.
This sharing of data allows for the better evaluation of activities at
the site. The integrated EQuIS data management system facilitates
a better understanding of the geology and hydrogeology of the site.
Once complete, the full system will permit better public access to
information.
Figure B-6. Bedrock tin for Rocky Flats groundwater model |
Conclusion
The sites discussed illustrate many of the advantages of)
implementing a database system such as EQuIS. With the storage!
of data in one centralized repository and the ability to export this!
data to many different evaluation tools, site characterization andl
analysis becomes quicker and more effective. Decisions can be I
made based on the data with much more confidence and reliability. [
Though the state has not fully implemented the system, benefits are I
already being seen. These include better understanding of the sites I
we regulate, improved data quality, easier design of sampling andl
remediation plans, and easier access to the data. The ease of data I
query and export to evaluation tools afforded by EQuIS allows
project managers to test the sensitivity of different applications to the
data. Also, several different statistical, contouring and groundwater
modeling tools can be utilized with limited effort to check the |
validity of applying different algorithms to the problem.
A number of other uses are perceived in the future. As more sites
are incorporated into the system it will be possible to use the data in
the warehouse to help in the evaluation of newly discovered sites.
Because of the standardized format, it will be easier to share data
and conclusions with the general public.
B6
-------
-------
S-EPA
United States
Environmental Protection
Agency
National Risk Management
Research Laboratory
Cincinnati, OH 45268
Official Business
Penalty for Private Use
$300
Please make all necessary changes on the below label,
detach or copy, and return to the address in the upper
left-hand corner.
If you do not wish to receive these reports CHECK HEREd ;
detach, or copy this cover, and return to the address in the
upper left-hand corner.
PRESORTED STAND/
POSTAGE & FEES P/
EPA
PERMIT No. G-35
EPA/540/R-02/503
May 2002
------- |