Linda M. McConnell, P.E.; David Van Vliet, P.Eng.; Thomas J. Cook, P.G.

A New Suite of User-Friendly GIS Tools for Accessing Environmental Monitoring Databases

In the course of environmental remediation and monitoring, relational databases are created to manage hundreds of thousands, or even millions, of records of physical and chemical monitoring data. Although the investment in these databases is often extremely high, direct access to the data is usually limited to a few individuals.

We have developed a suite of GIS tools that provide intuitive access to these databases. These tools include an ArcView GIS extension, a stand-alone MapObjects/Visual Basic application, and a GIS-based Web application created using MapObjects IMS. Our goal in developing these applications has been to provide all team members, including our clients, with direct access to databases.

We have realized a number of benefits by making the investment in developing our suite of products including better utilization of data and new business opportunities.

In the course of environmental remediation and monitoring, relational databases are created to manage hundreds of thousands, or even millions, of records of physical and chemical monitoring data. Although the investment in these databases is often extremely high, direct access to the data is usually limited to a few individuals. Similarly, site drawings and aerial photographs are usually available, with significant time and cost invested in their creation, but the maps, drawings and aerial photographs are often not handy, and electronic versions are often accessible only by those in CAD or GIS departments. And then there are those other project information items such as ground-level photos, field logs, definitive reports and permits that are needed from time to time for reference, and presentations such as three-dimensional visualizations that help define the current and/or historical conditions of the site.

In 1999, Conestoga-Rovers and Associates (CRA) took up the challenge to find or create a means of making site data more readily accessible and meaningful to those working on environmental remediation projects. Following an evaluation of existing tools and options for development of new applications, CRA committed to an investment in the development of the needed tools. The initial effort resulted in the creation of two related GIS tools, one for use by those with access to and training in the use of ArcView GIS, and the other, also including a GIS interface, for immediate intuitive use by environmental project personnel unschooled in the use of GIS programs. A third member of the suite, added later in the year, provides similar access to project information via the Internet. CRA’s suite of GIS-based tools has been given the name e:DAT – Electronic Data Access Tool; the components are referred to individually as e:DAT (the stand-alone application), e:DAT extension for ArcView GIS, and e:DAT Web.

The primary goal in developing these applications has been to provide all team members – including both in-house (consulting) environmental project managers and (client) site owner/operator project managers with direct and meaningful access to project information. With the successful implementation of the program, which gives ready intuitive access to site information via the various routes, benefits have accrued to an expanded group, with access extended to additional interested parties, including site owner/operator corporate staff, attorneys, regulatory personnel, parties to negotiations, and interested public. Similarly, with realization of flexibility of the tools, the range of their applications has been extended to numerous applications beyond the realm of environmental site remediation.

We have realized a number of benefits by making the investment in developing our suite of products including better utilization of data and new business opportunities.

CRA’s e:DAT is a stand-alone data access tool that has been developed based on powerful GIS and database management concepts. It was developed using Visual Basic and Esri’s MapObjects LT, and includes links to ODBC objects. e:DAT provides quick and easy access to site maps and environmental information, including data from site photos, reports, field data, and analytical chemistry. Modeling and 3-D visualization results can also be incorporated. As a stand-alone application, e:DAT requires no additional database or GIS software, and can be installed on individual PC’s. The entire application (software and project files) can be copied and distributed via CD. An e:DAT project loaded on a laptop can be used at meetings in remote locations to facilitate discussions and negotiations by providing quick and easy access to various views of maps, photos, and analytical data. Views of typical e:DAT screens are presented in Figures 1 and 2.

GIS Features. e:DAT’s GIS features, which derive from the incorporation of Esri’s MapObjects LT, allow users to display and query a wide variety of data sources. The GIS features include:

• use of industry-standard shape files and images;
• ability to pan and zoom through multiple, controllable map layers, including a rapid zoom to pre-defined areas of interest;
• automated labeling;
• ability to modify entity properties (e.g., colors, symbols);
• legends and overview maps;
• multiple preset views;
• ability to display information about map features;
• ability to print map and image views

Data Query, Charting, and Export Features. e:DAT works with data in a standard Access database to provide handy data querying, charting, and exporting. Columns (fields) of data displayed are flexible and can be varied to suit the project. Using the e:DAT Query feature, a table can be displayed for selected locations and parameters. Click on a column heading to sort the data in either ascending or descending order.

The selected data can be graphed (using MS Chart) for an ad hoc selection of location(s) and parameter(s). Chart features, such as type of chart (e.g., bar or line), scale, grid lines, and colors, can be modified on the fly. A simple right-click on the table allows exporting of the selected data to an MS Excel worksheet.

Tools for Display of Additional Images and Documents. To enhance e:DAT’s role as an application that can present an entire "project in a nutshell," several e:DAT tools facilitate display of project-specific information in the form of reports and other documents, logs, and photographs. Symbols (usually arrows) on a "photograph" map layer indicate the location and direction of photograph images. By selecting the "Photographs" tool and a specific photo location ,the selected photo can be zoomed, panned, and printed.

Similarly, symbols on a map layer for logs (such as borehole drilling logs, monitor well cross-section diagrams, field notes, or other information) are associated with the corresponding location-specific log, which can be an image file or part of an Adobe Acrobat (PDF) file.

Additional documents (in PDF format) to be associated with the project, such as reports, permits, tables, schedules, or slide shows can be included and selected for immediate display from the drop-down reports list.

Ties to Other Project Information. Results of other efforts such as modeling and 3-D visualization can be incorporated in the e:DAT project. Links are available to associate with self-executing files to present videos of site conditions, results of modeling, or 3-D visualizations.

System Requirements for Stand-alone e:DAT. Recommended system requirements for running e:DAT include Windows 95, 98, NT 4, or later; Intel Pentium processor or equivalent; and 32 megabytes of available RAM. The installed program uses approximately 2 megabytes of disk space. GIS, data, and document files may be accessed on local or network drive, or from a CD. For viewing .PDF files, the Adobe Acrobat Reader is required; and exporting to Excel requires Microsoft Excel (v.8 or later).

Typical Application. A typical application for an environmental site investigation can be built from existing elements to include the following:

• aerial photo image covering project site and surrounding vicinity (scale and resolution dependent on project-specific requirements);
• map layers (shape files created from existing CAD drawing layers) for roads, buildings, piping, tanks, fence, vegetation, paved area, existing monitor wells, proposed monitor well locations, boring locations, and photos;
• ground-level photographs at specific areas of interest;
• borehole logs for soil borings (in PDF format)
• reports (in PDF format), including environmental permit(s), Remedial Investigation/Feasibility Study (or RFI and Corrective Measures Study), and project schedule;
• link to 3-D visualization presentation created from separate effort;
• analytical chemistry data for monitor wells, including for each location: location ID, date, results, detection limits, units of measure, qualifiers, and comments;
• water level elevation data at monitor wells;
• a second view with elements similar to the above, but with a historical aerial photograph as the base image;
• a third view, again with similar elements, but for a separate (but related) project location.

The project described above can be written to a CD, along with program setup files, and distributed to all project management personnel, including those at the project facility site, corporate responsible parties, consultants, attorneys, and regulatory personnel. With the typical application loaded on a laptop computer, project personnel can meet with regulators or other negotiating parties. Discussions will be facilitated by the ready access to various views of maps, photos, and data.

Advantages of e:DAT. What e:DAT users have found most beneficial have included its

• portability – e:DAT can be installed on a stand-alone PC; project personnel can take it on a laptop to meetings to facilitate discussions and negotiations;
• independence – an e:DAT project does not require any other database or GIS software;
• inclusiveness - an e:DAT project can include as much or as little project material as desired; it can provide links to documents in image or .PDF format including well hole boring logs, reports and schedules, site photographs, and links to executable files such as video files containing results of modeling;
• ability to complement existing systems – in most cases, e:DAT will coordinate with, rather than replace, existing databases & graphics;
• speed – a stand-alone e:DAT project can be created from an existing GIS project, but limited to include only those layers & features that are needed;
• cost effectiveness – there are no other software licenses to purchase; and
• ease of use – negligible training time is needed to grasp the intuitive interface; it is so easy, some users have suggested calling the program "GIS for Dummies."

While the stand-alone version of e:DAT provides outstanding accessibility to environmental project data on a PC or network installation, additional options are called for when interested parties are in various locations and/or the data set is frequently changing. The Internet can provide the perfect platform for access to such data.

e:DAT Web is a new product developed by CRA to provide data access capabilities similar to e:DAT using the Internet. This Internet software is based on e:DAT and provides a user friendly window to site maps with the ability to query analytical and field data, as well as a number of ancillary data types such as photos, logs, and reports in either Netscape or Microsoft Internet Explorer. This web-based approach provides wide accessibility to up-to-date data from a number of different sites.

e:DAT Web was developed using Esri's MapObjects and MapObjects Internet Map Server (IMS) technology. Features include:

• use of industry-standard shape files and images;
• ability to pan and zoom through multiple, controllable map layers, including a rapid zoom to pre-defined areas of interest;
• automated labeling;
• legends and overview maps;
• multiple views;
• ability to display information about map features;
• ability to print map and image views to local printer;
• link to Access database;
• database queries for data such as borehole data, measured water levels, and analytical chemistry results;
• ability to export query results to Excel for desktop applications and reporting.

A views of a typical e:DAT Web screen is presented in Figures 3.

The e:DAT extension for ArcView adds to an ArcView user-friendly querying of analytical chemical data for well locations, with convenient selection, sorting, and posting of results for selected constituent(s) at selected locations. Similar to the chemistry query, the hydrologic query provides ready access to water level data for the site wells.

Additional queries and visualization of site geology information are provided via the query and cross-section creation features of the extension. Where site soil stratification has been defined (via soil type and depths), a cross-section whose location is defined in plan view can be presented in cross-section view, including information from points up to a specified distance from the cross-section line.

A views of a typical e:DAT Extension for ArcView GIS screen is presented in Figure 4.

The application of the e:DAT suite has put at the fingertips of project personnel key project information – not only graphics and data, but also significant contemporaneous logs, photographs, modeling/visualization results, and often-referenced documents. With the use of e:DAT, the rewards of the often significant investment in developing and maintaining databases can now be realized, as project personnel now have ready, meaningful access. For many project personnel, it has made the projects come alive, has given them control to investigate items of interest, to quickly query historical data, and to visually identify anomalies via instant charting. It has facilitated sharing site data and other information with others, has empowered interested parties to explore project information, and has expedited negotiations by clarifying locations and conditions during discussions.

Besides its project-application benefits, the stand-alone implementation of e:DAT has also been praised for its portability (install on stand-alone PC or laptop), ease of use, independence from other software (no other database or GIS software required), complementary use of existing databases and GIS elements, speed, and cost effectiveness. The power of additional ArcView capabilities are added with the e:DAT extension for ArcView; and immediate access to rapidly-changing data from locations around the world is available via e:DAT Web.

Development of the new suite of tools has also led to a broader range of applications. With realization of flexibility of e:DAT, the range of use has been extended to numerous applications beyond the realm of environmental site remediation. While the greatest number of e:DAT applications have been created for projects involving physical and analytical chemical data for soil and groundwater investigations, the types of applications that can benefit from e:DAT are many and varied, and have included

• surface water quality;
• air emissions sources, permit limits, and monitoring data;
• multi-site project site locator and overview;
• multi-property due diligence (Phase I & II) assessments for highway relocation;
• sewer and manhole locations, design and maintenance information;
• underground storage tank projects;
• property zoning and appraisal

Development and application of the suite of tools has also brought rewards in terms of additional business. The inclusion of e:DAT has been a key to winning awards for environmental site investigative or remedial projects. e:DAT has also brought in its own projects, such as those for litigation or arbitration support, where communication of key concepts is crucial.

The new suite of GIS-based electronic data access tools developed by CRA has revolutionized the way project information is manipulated, reviewed and presented. For many project personnel, both in-house and external, e:DAT has empowered them with the ability to easily manipulate and review project information. By connecting the analytical data to maps and aerial photographs within a user interface easily accessed by the project manager, e:DAT has made site data come alive and work for the manager, rather than making the manager work to access the data. The linking of additional related project information, such as field logs, site photos, and project reports, elevate e:DAT beyond a simple GIS application to a comprehensive project management and presentation application.

The range of the related tools – from e:DAT for the stand-alone PC to e:DAT Web, makes the tools available for all situations; with e:DAT extension for ArcView GIS, the same ease-of-use accessibility to site data is available, along with the additional power of ArcView’s GIS features.

The benefits of development of the e:DAT suite continue to accrue as the range of e:DAT applications continues to expand, and the benefits and power of the e:DAT’s communication impact are realized.