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