Integration of a Remote DB Query System with ArcIMS Applications for Environmentally Sensitive Sites

Joseph F. Giacinto
Robert F. Nickols
James W. Bodamer
Lonnie D. Norman

 

Abstract

Technology developed and utilized for an Internet based Graphical User Interface application was integrated with ArcIMS applications, allowing clients to efficiently retrieve tabular data sets from a database repository for Superfund and Brownfield sites. Emphasizing a design that would smoothly integrate into Microsoft’s Dot Net technology, the software application was integrated with the ArcIMS 3.1 Java Viewer to provide detailed and customized queries for a complex database of analytical and water level data gathered over a period of approximately ten years. Integrating user requirements with Microsoft COM technology and ArcIMS 3.1 permitted tabular database queries to be returned to ArcIMS as shape files. These shape files could then be manipulated and displayed by the end user in several ways including color/size coded map symbols and/or tables. Since spatial and tabular database queries were performed on the server, the client PC processing load was light eliminating the need for high CPU speed and expensive client PCs.


Introduction

This study documents the RAD (Rapid Application Development) life cycle of an SQL (Sequel Query Language) Internet database query interface tool integrated into ArcIMS. With the exception of the tabular database query interface, the ArcIMS mapping application contained all of the necessary mapping tools and components required by the user group. Integration of a tabular database query interface was desired that would query tabular data and coordinates from a SQL database, convert the queried data to a point shape file, and overlay the shape file on the ArcIMS client map window. Once the queried data was converted to a shape file and in the client map window, the user requirements included interaction and manipulation of the newly created map layer.

Originally constructed with MapObjects 2.1 and Microsoft COM (Component Object Model) tools, the tabular database query interface was modified to run within a frame of an active ArcIMS 3.1 Java Viewer session. Through the tabular database query control, SQL requests were processed on a database server computer. Conversion of the tabular database queries to shape files was performed with ArcIMS. Measurements were then returned to the Java Viewer as shape files for representation as scaled symbols or colors based on the measurement value, and/or labeled according to any field (e.g., concentration, well ID, etc.) within the DBF file returned in the shape file data set.

The custom-built tabular database control was designed to dynamically build SQL statements thru ADO (ActiveX Data Objects)/DAO (Data Access Objects) connectivity. This connectivity allowed the exploitation of RDO (Remote Data Objects)/RDS (Remote Data Services) record set objects to multiple differing COTS (Commercial Off The Shelf) software including Microsoft Excel, Golden Software's Surfer, and ArcIMS.

Upon the initial visit to the ArcIMS site, the application uses the Microsoft Package and Deployment Wizard’s capabilities to register the files on the user's computer necessary to run the tabular database query control. This method was particularly efficient when deploying newer versions of the control. When a user browses to the URL hosting the application, a version check would commence. If the client machine control held an outdated version, then an overwrite would take place. Typically unless major changes of the control were implemented, the users only knowledge of an update occurring would be a revised time stamp displayed on the control. Digital signatures were applied to the user control and distributed installation files to permit the tabular database query control to operate in levels of high security within Microsoft Internet Explorer.

Tabular Database Query Interface Integration Evolution

Utilizing Visual Basic standard forms and DAO (Data Access Objects), the first tabular database application release was compiled into an executable and deployed via FTP (File Transfer Protocol) to a LAN (Local Area Network) for internal use. This tool efficiently built automated Microsoft Excel tables and charts saving valuable time and employee resources. From this application, a solution was developed that integrated the tabular database query application into custom MapObjects applications. Combining the tabular database query interface tool with MapObjects capabilities added functionality to produce dynamic point shape files into a MapObjects control on a standard Visual Basic form. MapObjects provided compatibility with Microsoft’s COM (Component Object Model) technology ADO (ActiveX Data Objects). This compatibility permitted a light footprint and asynchronous connectivity to a remote data repository with minimal code adaptation. During the tabular database query interface integration with MapObjects, the primary method of database connectivity was performed through with Microsoft’s DAO (Data Access Objects). The primary problem with this database connectivity method was client compatibility and deployment of the application. The application and database had to be installed onto the client intranet, which took considerable deployment planning and client resources. The advent of ADO (ActiveX Data Objects) allowed client/server load balancing providing lighter, simpler, and diverse methods of application designs and deployment options.

The structure and functionality of the tabular database query tool expanded with additional user requirements. Standardizing the ease of implementation and minimizing redundancy for many possible components within the tool became our next milestone. To achieve this goal and better timely application development for a growing client base, code structure and methods of integration to differing scenarios gave way to a finite break down of objects and code modules. These controls needed to be easily adaptable to differing database constructs and client intranet configurations. For the ArcIMS 3.1 integration, a 3-tier model provided the adaptability, separation and encapsulation of functionality, maintainability, multi-user support,  the desired low resource distribution, and efficient access to a remote database source.

Tabular Database Query Interface Three Tier Architecture Model

Migrating the tabular database query interface to a 3-tier model prepared the application for efficient integration into the ArcIMS. From the 3-tier model, only the first tier required modification for the ArcIMS integration. By modifying only the first tier, lower costs (e.g., development and deployment design times) were incurred in the ArcIMS application integration. The 3-tiers of the model (Figure 1) are described below.

Tier 1: User Interface (Presentation Layer)

Tier 1 is a client side ActiveX Control that is referenced by the local registry assigned object ID inside HTML tags on either an HTML or ASP page. This part of the 3-tier application uses client resources to send SQL requests to IIS 4.0 (Internet Information Server) and retrieve the desired ADO (ActiveX Data Objects) record set. Upon receipt, the Interface can manipulate the record set into different software residing on the client machine. The potential form of the record set manipulation includes Excel charts, graphs, tables, Esri shape Files, or Golden Software's Surfer generated contour maps.

Tier 2: Data Access Logic (Business Logic)

The middle tier design utilizes IIS 4.0 (Internet Information Server 4.0) to connect with the ODBC (Open Database Connectivity) SQL Server 7.0 driver. Using this type of Web access allowed the application design to take advantage of the security features offered with IIS 4.0 (Internet Information Server 4.0) and serves as the intermediary that provides an efficient means for the custom interface to invoke SQL processing across the Internet or an intranet.

The connection pooling capabilities of Microsoft ODBC (Open Database Connectivity) driver enhances the application allowing concurrent user connectivity and management. This ODBC feature efficiently manages the multiple database connectivities and is utilized to translate a user's request into SQL (Sequel Query Language) statements processed by SQL Server.

Tier 3: Data Repository (Data)

Utilizing Microsoft SQL Server for database storage, allows the application to take full advantage of designed integration features for use with Microsoft COM components such as ActiveX data objects. In addition, SQL Server allows efficient means for database optimization, security, and maintaining user roles. 

  Figure 1. Three Tier Architecture Model for Tabular Database Query Interface.

Figure 1. Three Tier Architecture Model for Tabular Database Query Interface.

Tabular Database Query Interface Integration with ArcIMS

The tabular database query control integrated with ArcIMS is constructed with the same three-tier architecture with modifications to the first tier (Figure 1) only. For the first tier modifications, properties and methods of ArcIMS DLLs were included to permit creation of shape file layers and inclusions of these new shape files in the active Java Viewer session (Figure 2). With the exception of the ActiveX control user interface ArcIMS contains all of the necessary mapping tools and components required. Integration of the ActiveX control was possible through the ArcIMS ActiveX Connector and minimal configuration of Microsoft Internet Information Server 4.0. This combination of power provides the capability to interface with data stored on a Microsoft SQL Server and extract a wide variety of tabular data sets.

Figure 2. ArcIMS Tier One Tabular Database Query Interface Integration.

Figure 2. ArcIMS Tier One Tabular Database Query Interface Integration.

Extracting coordinates and other pertinent data from a SQL Server 7.0 database, the client side code retrieves the requested data from the server as an ADO (ActiveX Data Object) record set object, then converts the queried data to a point shape file, and terminates with the addition of a layer through Java scripting in the ArcIMS 3.1 Java Viewer session. Once the transformation from raw data to a layer object is complete, the manipulation of the newly created map layer was achieved by utilizing the power of the ArcIMS 3.1 Feature MapService to allow flexible map layer plotting options.

Summary

Custom tabular database query interface utilized web-based technology available through Microsoft’s Internet Information Services and ActiveX functionality to provide asynchronous connectivity to ODBC SQL data source (SQL Server 7.0). Combining the tabular database query interface with ArcIMS allows the returned data to be manipulated by the many map tools available in the Java Viewer. Following a three tier application architecture, the interface dynamically builds SQL statements returned as ADO record set objects to the client machine. These objects then get transformed into shape files through ArcIMS DLLs distributed  to the client during application installation. The shape files are then merged into an active Java Viewer ArcIMS 3.1 session as map layers. These map layers are then manipulated and displayed with different color/size coded map symbols and/or different labeling techniques. Since spatial and tabular database queries were performed on the server, the client PC processing load was light eliminating the need for high CPU speed and expensive client PCs. In addition, the client PC requires only an Internet browser and Java viewer files to interact with the application and tabular database query interfaces. The application architecture is designed to conform with the Microsoft Dot Net client/server load balancing technology, and will easily migrate into Windows XP.


Author Information

Joseph F. Giacinto
ES&T, Inc.
3708 South Main Street, Suite D
Blacksburg, VA 24060
Phone: (540) 552-0685
Fax: (540) 951-5307
jgiacinto@esnt.com

Robert F. Nickols
ES&T, Inc.
3708 South Main Street, Suite D
Blacksburg, VA 24060
Phone: (540) 552-0685
Fax: (540) 951-5307
rnickols@esnt.com

James W. Bodamer
FMC Corporation
1735 Market Street
Philadelphia, PA 19103

Lonnie D. Norman
FMC Corporation
1735 Market Street
Philadelphia, PA 19103