v>EPA United States Environmental Protection Agency Office of Research and Development Cincinnati, OH 46268 EPA 540/SR-94/505 May 1994 BITE Technology Capsule GISXKey™ Environmental Data Management System Introduction In 1980, the U.S. Congress passed the Comprehen- sive Environmental Response, Compensation, and Liabil- ity Act (CERCLA), known as Superfund, which Is committed to protecting human health and the environ- ment from uncontrolled hazardous waste sites. CERCLA was amended by the Superfund Amendments and Re- authorization Act (SARA) In 1987 —amendments that emphasize the achievement of long-term effectiveness and permanence of remedies at Superfund sites. SARA mandates implementing permanent solutions and using alternative treatment technologies or resource recovery technologies, to the maximum extent possible, to clean up hazardous waste sites. State and federal agencies, as well as private par- ties, are now exploring a growing number of innovative technologies for treating hazardous wastes. The sites on the National Priorities List total over 1,200 and comprise a broad spectrum of physical, chemical, and environmen- tal conditions requiring varying types of remediation. The U.S. Environmental Protection Agency (EPA) has fo- cused on policy, technical, and informational issues re- lated to exploring and applying new technologies applicable to Superfund sites. One such initiative Is EPA's Superfund Innovative Technology Evaluation (SITE) program, which was established to accelerate develop- ment, demonstration, and use of innovative technolo- gies used to perform or support site remediations and related Issues. The 6IS\Key™Envlronmental Data Man- agement System was selected for SITE testing to assess its ability to provide useful and effective information to aid In site investigations and remedial activities. The evalua- tion occurred between April and December 1993. The specific objectives of the evaluation were to: • Determine if the software performs the functions that are claimed by the developer • Assess the accuracy of the GISXKey™ output and review GISXKey™ procedures used to ensure data integrity • Review the general usability of GISXKey™ • Compare GISXKey™ features to user requirements These capsules are designed to help EPA remedial project managers, EPA on-scene coordinators, contrac- tors, and other site cleanup managers understand the types of data and site characteristics needed to evalu- ate whether a technology can be effectively employed at Superfund sites. Abstract GISXKey™ is a custom-developed software system for the management of site-specific chemical, geologic, and hydrologic data. This software system, which is being used at a number of hazardous waste and Super- fund sites, was selected for SITE testing to assess whether it truly facilitates the collection, reporting, and analysis of large volumes of site-specific environmental data. Dur- ing the SITE evaluation, operators used GISXKey™ to assess the production of a variety of contour maps, tabular chemistry reports, geology tables, hydrogeologic tables, geologic logs, and chemistry and hydrogeology graphs. The system's user-friendly design, compatibility with 386 and 486 personal computers using Disk Operat- ing System (DOS), one-time data entry requirements, and built-in data quality checks contributed to the posi- tive assessment of the software's performance. Software Description GISXKey™ is an integrated system for the manage- ment of chemical, geologic, and hydrologic data de- veloped by GISXSolutions, Inc. of Concord, California. The software is designed to meet the needs of industry and to satisfy RCRA and CERCLA reporting requirements. GISXKey™ integrates several commercial off-the-shelf SUPERFUND INNOVATIVE Region 5, Library (PL-12J) 77 West Jackson Boulevard, 12th Floor Chicago, !L 60604-3590 Printed on Recycled Paper ------- products (e.g., AutoCAD®, FoxBASE™, and QuickSurf™) to produce a variety of site-specific tables, graphs, and maps, thereby facilitating the collection, reporting, and analysis of site management data. GISXKey™ can be installed and used on 386 and 486 personal computers running DOS. The optimum hardware configuration for running GISXKey™ Is listed In Table 1. Table 1. Optimum Hardware Configuration for GIS\Key™ Release 1.1.2 Optimum Hardware Configuration 486-66 Motherboard with 256K Cache 16 Mb RAM 1.2Gb SCSI Hard Drive 3.5" and 5.25" Floppy Drives 20" Monitor 101 Keyboard and Mouse/Digitizer SCSI Controller 250 Mb Tape Backup Environmental data for a project — chemical, geo- logical, and hydrological — is stored in the GISXKey™ Database, which is a relational data management appli- cation implemented in FoxBASE™. The database is tied to the graphical component, GISXKey™ Graphics, which Is built using AutoCAD®. The GISXKey™ graphical Inter- face depicts wells and bore-holes on a map of the site. The user chooses a report from a menu, picks a location from the map, and then follows the prompts to create a variety of output. GISXKey™ can prepare geologic cross sections, boring logs, potentiometric maps, isopleth maps, structure maps, summary tables, hydrographs, chemical time series graphs, and numerous other maps and line graphs. QuickSurf™ (Version 2.91), a third-party contour- ing program developed by Schrieber Instruments, is used to contour geology, hydrology, and chemistry data stored in GISXKey™ Database. Any AutoCAD® compatible digital basemaps can be imported into GISXKey™. There are a number of vendors who can provide general basemap data, usually based on the U.S. Geologic Suivey (USGS) 7.5 minute quad- rangle maps. Additionally, users usually import specific project map data (I.e., RCRA facility and CERCLA sites) which provide greater detail and resolution necessary for comprehensive studies. With GISXKey™, users add graphic points representing wells, borings, and sampling locations onto this basemap. GIS\K€»y™ provides the ability to verify the entered chemical, geologic or hydrologic information and "tie" this information to specific graphic points placed on the basemap. Data can be entered into the GISXKey™ Database either manually or electronically. Existing databases can be converted Into GISXKey™ format and laboratory re- ports on magnetic media can be directly imported. Pull- down menus, data entry forms, and look-up lists for frequently used values aid manual data entry. The lists store such Information as EPA test methods, practical quantification limits. Chemical Abstract Service (CAS) num- bers, chemical aliases, and regulatory threshold values for over 3,500 chemicals. GISXKey™ includes features that help the user iden- tify erroneous or questionable data. Data validation rou- tines Include graphical display of summary statistics and user alerts when QA/QC results fall outside data quality objectives, sample results fall outside historical ranges, sample results exceed applicable regulatory standards, and ionic balances fall outside data quality objectives. A list of GISXKey™ chemical, geologic, and hydro- logic products that GISXKey™ provides is shown in Table 2. A brief description of the types of tables, contour maps, and graphs provided by GISXKey™ are shown in Table 3. Software Applicability GISXKey™ is designed to facilitate the collection and analysis of site management data at Superfund and other hazardous wastes sites. Based on its performance during the evaluation, GISXKey™ can be used to characterize a Table 2. GIS\Key™ Products Chemistry Geology Hydrology • Isopleth maps of soil or water quality- plan section view • Chemical concentration time series graphs • Chemical versus chemical graphs, inter- and intra-well • Trilinear Piper diagrams • Chemical concentration versus distance graphs •Presentation-quality data tables - Boring logs with company logos • Geologic cross-section maps • Isopach maps • Structure maps • Presentation-quality data tables • Density-corrected water level contour maps - Floating product contour maps ' Hydraulic conductivity contour maps • Water elevation veisus time graphs • Floating product thickness versus time graphs • Extraction well graphs -flow versus time -concentration versus time -chemical flux versus time • Presentation-quality data tables ------- Table 3. Types of GIS\Key™ Tables, Contour Maps, and Graphs Tables Contour Maps Graphs Chemistry Geology - well construction and borehole summary tables Hydrogeologic - flow data and fluid level Geologic Logs - well and borehole logs Hydrogeologic: - fluid level elevation (water table contour maps) - fluid level elevation (potentiometric surface maps) - equivalent freshwater head - hydraulic conductivity - specific storage - specific yield Chemical Concentration Isopleths: - plan view isopleths - section view isopleths Geologic Structure Elevation: - geologic formations - blow counts - soil units - other units Geologic Structure Thickness Isopach Chemistry: - concentration vs. time - chemical vs. chemical - site vs. site - distance concentration - statistics - depth vs constituent - trilinear pipers Hydrology: - hydrograph - flow rate - cumulative flow - flux rate - cumulative flux - floater thickness - sinker thickness variety of sites containing soil and/or groundwater con- tamination. Since GISXKey™ is capable of efficiently and accurately processing a large volume of data. It is par- ticularly useful during site Investigations and remedial ac- tivities at larger sites. The various tables, graphs, and maps generated by GISXKey™ can be used to produce reports meeting CERCLA and RCRA reporting require- ments. These products can also be used to develop and maintain groundwater monitoring programs, to help de- termine the location of future monitoring wells, and to evaluate contaminated groundwater movement during the design of any landfill, holding or disposal pond, or reclamation project. Depending on site- and project-spe- cific data requirements and availability, GISXKey™ can be used to process both current and historical data. The system does not provide a means for managing ecologi- cal assessments or air pollutant data. Software Benefits and Limitations During the SITE evaluation of GISXKey™, a number of software benefits and limitations were identified. A brief list of these benefits and limitations Is presented in Table 4. Performance Data During the evaluation, emphasis was placed on evalu- ating the system's performance with respect to ease of use, system requirements, personnel requirements, data entry and database creating procedures, data integrity procedures, and electronic data exchange capabilities. The steps used to evaluate GISXKey™ mirrored, in some respects, the guidance developed by the USGS for evalu- ating geographic information system (GIS) products. Em- phasis was placed on analyzing several procedures and capabilities common to GISXKey™ chemistry, geology, and hydrology modules. Ultimately, the evaluation sought to determine how well the various procedures and capa- bilities associated with GISXKey™ performed during col- lection, reporting, and analysis of a set of site management data. Two analysts, experienced in GIS and environmen- tal database management performed the evaluation. The evaluation found that GISXKey™ is an effective way to prepare the wide variety of contour maps, graphs, tables, and site maps required at a typical hazardous waste site. These products were generated without en- countering any major problems. GISXKey™ uses com- mercial off-the-shelf components (i.e., AutoCAD® graphics and FoxBASE™ database), so numerous third-party data- base tools are available to create queries and report formats not Included with GISXKey™. The system can be a cost-effective, time-saving method for managing large volumes of environmental data. A number of issues relat- ing to the general useability of GISXKey™ were addressed during the generation of the various GISXKey™ products. The following subsections address the various functions and capabilities which were assessed. This information is also summarized in Table 5. New Project Setup It was relatively easy to set up a new project, a project directory structure, and a project basemap dur- ing the evaluation. However, since all the project direc- tories must be on the same drive as the GISXKey™ directory, mass storage difficulties can arise as project flies grow. Map Management Standard AutoCAD® drawing files are used for all GISXKey™ basemaps. The utilities provided for the addi- tion and editing of map symbols were tested and no problems were encountered. During the generation of the project basemap, the basemap and symbols were ------- Table 4. Benefits and Limitations Benefits Limitations GIS\Key™ is a turnkey data management system, with chemistry, geology, and hydrology capabilities GIS\Key™ produces presentation quality graphics, in a wide variety of output formats Specialized or advanced computer skills are not needed to routinely use G/SIKey™ G/SIKey™ reviews chemical laboratory QC data, generates exception reports, and marks sample locations on the project basemap which contain data outside of the QC objectives GIS\Key™ uses industry standard file formats for data storage, facilitating the exchange of GIS\Key™ data with other applications (i.e., third-party software) GIS\Key™ stores data in a unified database that provides several validity and consistency checks G/SIKey™ relates data across data categories, improving report and map consistency Data can be analyzed interactively; users can modify their assump- tions, views, and queries GIS\Key™ provides graphic editing capabilities through AutoCAD®, a well-known design application Duplication of effort is minimized due to one-time data entry and a variety of predefined routines and queries; easy, menu-driven routines simplify complex tasks (i.e., generating contours) GIS\Key™ runs on standard DOS PCs and on local area networks G/SIKey™ stand-alone data entry modules can be purchased separately at a reduced cost Reference lists are provided (i.e., regulatory thresholds and chemical names, aliases, and registry nos.) GIS\Solutions operates a bulletin board (e.g., to exchange files) and provides personnel for call-in support routines GIS\Key™ does not manage ecological assessment or air emission data Specialized AutoCAD® and database management system skills are required to make full use of G/SIKey™ Printed report formats are relatively inflexible; general report-writing powers are not provided Basemap integrity is vulnerable; improperly used AutoCAD® commands can delete sample locations and destroy map and database links, causing map and database inconsistencies Database integrity is vulnerable; invalid or inconsistent data can be entered into the database (water data for a borehole), causing query anomalies GIS\Key™ performs predefined database queries; third-party tools are needed for ad hoc queries G/SIKey™ is limited to post-project data analysis only; no planning capabilities are provided Data storage limitations constrain the entry of location data, well construction and log information, and QA/QC data G/SIKey™ does not include audit or transaction logging capabilities Circles, irregular shapes, and spatial operators cannot be used to select sample locations; polygon operations are not supported Database structure documentation is incomplete, limiting the usefulness of electronic data transfer operations ------- Tables. Performance Summary Element/Function Evaluated Performance New project setup Map management Data entry screens - geological, chemical, and hydrogeological Data import routines Data integrity checks Data validation Data queries Contouring - geology, hydrology, and chemistry data Calculations Products Hardware configurations Project planning Training Documentation and support services User requirements Software operabttity or "user friendliness" it was relatively easy to set up a new project, a project directory tree structure, and a project basemap mass storage difficulties may arise as project files grow no problems were encountered during the addition or editing of map symbols using the utilities during the setup of a new project basemap, the basemap and map symbols were digitized with satisfactory accuracy using two methods provide several time saving features, including dynamic look-up lists and quick return to the most recent item accessed defaults are provided by GIS\Key™ for several fields on-line help is not available during data entry can input data in a wide array of formats allows for downloading of laboratory data instructions are available to help the laboratory prepare a import file performs some data quality checks on key fields for consistency and reasonableness as part of the data entry screens third-party data management tools are needed to prepare data files for GIS\Key™ import routines and for data quality checks beyond those included with GIS\Key™ tracks significant figures compares QA/QC laboratory results to user-defined QC objectives for method and field blanks, duplicates, splits, matrix spikes, control samples, surrogates, and holding times, checks ionic balances, and checks for concentrations outside of historical ranges or in excess of action levels reports automatically generated outlining exceptions to project data quality objectives seven data validation fields are available one of the most powerful and often used tools available to the user can perform both spatial and non-spatial queries - spatial data retrieval capabilities use standard AutoCAD® commands - GIS\Key™ symbol lists (i.e., user-defined subsets of frequently used sample locations) are used to simplify AutoCAD® spatial data selection carried out by QuickSurf™ (version 2.91), a third-party software application integrated with GIS\Key™ the QuickSurf™ algorithm works well for surfaces which are continuous with respect to slope and curvature (first and second derivatives) — but may not truly represent surfaces which contain breaks or faults performed well during the editing of posted values and the adding of control points during the entry of flow rate, fluid level, and QC data, G/SlKey™ automatically performs specific calculations (average flow rate, cumulative flow, spike percent recoveries, and matrix spike duplicate relative percent differences) calculates areas, perimeters, and lengths using standard AutoCAD® commands; volume calcu- lations are supported through Quicksurf™ can export to EPA GRITS/STAT to perform advanced statistical functions can effectively produce a variety of high quality products (i.e., contour maps, tabular chemistry reports, geology tables, hydrogeologic tables, geologic logs, and chemistry and hydrology graphs) standard product formats are available; however, they can be custom tailored by GIS\Solutions or by the user using third-party software performed all its functions on PC class systems in the DOS environment works quickly and presents few hardware problems using the optimum system configuration; based on timing results the optimum hardware configuration should be used to achieve worker efficiency determining how to manage the data being generated is a major challenge codes and lists must be defined to categorize project data (sampling events, preparation fractions, program types) for storage and retrieval list and code, organize or group related data and simplify data entry update and query anomalies can be avoided by using a data management perspective and third- party software to manage codes basic and advanced training is available covering AutoCAD® and a detailed walk-through of GIS\Key™ capabilities users are guided through the creation of GIS\Key™ outputs well presented but needs additional emphasis on project planning and setup the user guide is well prepared and adequately covers the system's modules and activities does not represent some changes that have occurred with new software versions helpful and readily available call-in support is offered general users do not need specialized computer skills or advanced GIS\Key™ training to operate the software project administrators need advanced database/data management skills and computer skills to be able to perform some of the more advanced project setup and maintenance tasks in general, GIS\Key™'s user interface is well organized and easy to operate User friendliness is due in part to functions that can usually be selected from pull-down or pop-up menus; prompts displaying default answers are usually available the menus guide the user through complex data manipulation and display steps ------- digitized using two methods. Satisfactory accuracy of the coordinates of map symbols was obtained. Data Entry Screens Data entry screens are available for geological, chemical, and hydrogeological data processed by GISXKey™. These screens provide several time-saving features, including dynamic look-up lists and quick return to the most recent item accessed. Defaults are provided by GISXKey™ for several fields, simplifying the entry of sequential data. On-line help Is not available during data entry. Data Import Routines Electronic import routines allow the import of data from several standard formats. A separate program is available to facilitate import of electronic laboratory data. Staff with database management expertise Is needed for data import; laboratory data requires pre-processing fol- lowing instructions from GISXSolutions and the user is re- sponsible for data integrity and consistency when other types of data are Imported. Data Integrity Checks GISXKey™ performs some data quality checks for consistency and reasonableness as part of the data entry screens and data import routines on key fields and se- lected attribute fields. Third-party data management tools are needed to prepare data files for GISXKey™ import routines and for data integrity checks beyond those included with GISXKey™. The system tracks the significant figures of all chemical concentrations and re- porting limits. Data Validation GISXKey™ can be used to compare QA/QC labora- tory results to user-defined QC objectives. Reports are automatically generated outlining exceptions to project data quality objectives. Built-in routines are available to: Identify chemical concentrations that fall outside histori- cal ranges; identify concentrations in excess of action levels; check ionic balances; and compare QC results against QC objectives for method and field blanks, dupli- cates, splits, matrix spikes, control samples, surrogates, and holding times. Seven data validation fields are avail- able to store data qualifiers reported by the lab or as- signed by the user. Data Queries The ability of GISXKey™ to query data is one of the most powerful and often-used tools available to the user. Data queries are prompt driven; therefore, knowledge of a data query language is not required. GISXKey™ spatial data retrieval capabilities are limited to those provided by AutoCAD® (individual entity selection and rectangular selection windows). GISXKey™ allows the user to save AutoCAD® selection sets by storing them in "symbol lists." These user-named "symbol lists" allow relevant entities to be quickly re-selected. "Symbol lists" must be created in advance and then manually maintained. An attribute of the system is the speed at which queries and postings can be made. Contouring Contouring geology, hydrology, and chemistry data is carried out by QuickSurf™ (Version 2.91), a third-party software package that is integrated with GISXKey™. The QuickSurf™ algorithm works well for surfaces that are continuous with respect to slope and curvature (first and second derivatives), but may not truly represent surfaces which contain breaks or faults. A number of structure maps were successfully constructed to test the effect of editing posted values and adding contour control points. Calculations During the entry of flow rate, fluid level, and QC data, GISXKey™ automatically performs specific calcula- tions (average flow rate, cumulative flow, spike percent recoveries, and matrix spike duplicate relative percent differences). GISXKey™ calculates areas, perimeters, and lengths using standard AutoCAD® commands. Volume calculations are supported through Quicksurf™. GISXKey™ can export data to the EPA Groundwater Information Tracking System/Statistics (GRITS/STAT) program for ad- vanced statistical functions. Products GISXKey™ provides an effective way to produce con- tour maps, tabular chemistry reports, geology tables, hydrogeologic tables, geologic logs, and chemistry and hydrogeology graphs. Standard formats are available from the software; however, it can be custom tailored by GISXSolutions staff or by the user through third-party soft- ware. The products produced by the system are of high quality. Hardware Configurations GISXKey™ performs its functions on standard PC class systems in the DOS environment. When using the opti- mum system proposed by GISXSolutions, the system works quickly and presents few hardware problems. Table 6 presents timing results for several processes on three different platform configurations. Although GISXKey™ was able to perform all of its functions on a 386 class PC (4 Mb RAM), the timing results shown in Table 6 indicate that a more powerful processor and memory hardware configuration can be used to obtain higher efficiency. Project Planning One real challenge at any site is associated with determining how to manage the data being generated. Through proper project planning, GISXKey™ can be used to define codes and lists to categorize project data (sam- pling events, preparation fractions, program types) for storage and retrieval. GISXKey™ uses this information to organize or group related data and to simplify data entry. With a good data management perspective and the use of third-party software, these codes can be managed to avoid update and query anomalies. Training Users can obtain basic and advanced training. Train- ing covers AutoCAD® and a detailed walk-through of GISXKey™ capabilities. Users are guided through the creation of GISXKey™ outputs. The training is well pre- sented but needs additional emphasis on project plan- ning and setup. Documentation and Support Services The User Guide is well-prepared and covers the system's modules and activities. It does not represent in all cases the changes that occurred as new versions of software were incorporated into GISXKey™. The call-in ------- support offered was readily available and of great help In understanding Issues. User Requirements GISXKey™ users fall within two categories: general users and system administrators. General users are re- sponsible for 1he day-to-day operation of the GISXKey™ system (i.e., information retrieval) and do not need spe- cialized computer skills to use the software. Project ad- ministrators, however, need advanced computer and database management skills to perform advanced project setup and maintenance tasks. Software Operabiltty or "User Friendliness" In general, GIS\Key™'s user interface Is well orga- nized and easy to operate. This Is due In part to the various menus, both graphical and text-based, employed by GISXKey™. When working within GISXKey™, functions can usually be selected from pull-down or pop-up menus. Prompts displaying default answers are usually available. GIS\Key™'s menus guide the user through complex data manipulation and display steps, making it possible for users without AutoCAD® experience to be able to pre- pare a site map, complete with a title block and border. Software Status GISXSolutions, Inc. was organized in July 1990. GISXKey™ was generated as part of a company wide effort to develop user-friendly software products which offer significant reductions in environmental compliance costs while improving the accuracy and quality of envi- ronmental data. The various chemical, geologic, and hydrogeologlc modules which make up GISXKey™ are sold to industry, environmental consultants, government, owners/operators, and regulatory agencies, directly and through distributors. Technical services, such as software customization, can be obtained at the client's request. Technical sup- port packages, such as annual software maintenance and GISXKey™ training, are included in each software sale. Professional scientists, geologists, and engineers employed by GISXSolutions are responsible for providing technical training and client support services. Notice This document has been subjected to EPA's peer and administrative reviews and has been approved for publication as an EPA document. Mention of trade names or commercial products does not constitute an endorsement or recommendation for use. Sources of Further Information EPA Contact: U.S. EPA Project Manager: Mr. Richard Eilers U.S. Environmental Protection Agency Risk Reduction Engineering Laboratory 26 West Martin Luther King Drive Cincinnati, Ohio 45268 Telephone No.: (513) 569-7809 Fax No.: (513) 569-7620 Software Developer: Mr. Garry Reid GISXSolutions, Inc. 1800 Suffer Street, Suite 830 Concord, CA 94520-2500 Telephone No.: (510) 827-5400 Fax No.: (510)827-54672 Table 6. Selected Processing Times Process 386-33/4MbRAM Time 486-33/8MbRAM 486-33/1BMbRAM load GIS\Key™ open a 1.9 Mb drawing open a 322 Kb drawing swap out to database plot 1.9 Mb drawing plot 322 Kb drawing 40 seconds 3 minutes 45 seconds 40 seconds 35 seconds 14 minutes 5 minutes 23 seconds 1 minute, 50 seconds 18 seconds 22 seconds NA NA 13 seconds 1 minute, 21 seconds 14 seconds 2 seconds 1 minute, 28 seconds 1 minute, 22 seconds NA - Not available 12th Floor •&U.S. GOVERNMENT PRINTING OFFICE: »»4 - 550-067/80211 ------- |