Web GIS as a Public Access Interface for a Data Warehouse: Going Public with Oregon's Environmental Systems-Approach and Lessons Learned

Abstract

The Oregon Department of Environmental Quality (ODEQ) is responsible for protecting and enhancing Oregon's water and air quality and for managing the proper disposal of solid and hazardous wastes. ODEQ is participating in the Environmental Protection Agency's (EPA) One Stop Program, which is directed toward three primary goals:

Project Background:

Oregon's Legacy Systems

The Oregon Department of Environmental Quality (ODEQ) had built and maintains several media-specific information systems which track information related to facilities regulated by one of the media programs (e.g. Air Quality, Water Quality, and Cleanup). These systems are generally discrete, and implement a different set of business processes based on media and on the underlying statutes. As early as 1992, ODEQ adopted an enterprise Computer Aided Software Engineering (CASE) tool for major systems development. The agency elected to use a single data encyclopedia and a common geographic location identifier system in several media-specific systems. As a result, in 1997 when this project kicked off, location data existed for a number of the ODEQ legacy applications within a shared data structure.

In addition, the agency had implemented a common facility identifier in many of the non-CASE systems. For a variety of reasons, by 1997, of the four systems originally using the system, only two remained active users, and for the most part did not actually share common facility data, but maintained their own set of records within the shared data structure. In July 1997 ODEQ received a grant from the EPA One-Stop program based on a proposal to integrate information stored in legacy systems, and to provide public access to that data.

In total, the "Facility Profiler" project was intended to integrate data from seven legacy systems. Those systems included information about air quality permits, water quality permits, hazardous waste generators and facilities, solid waste facilities, licensed underground storage tanks, leaking underground storage tanks, and contaminated sites. The nature of the data contained within each of those systems varied based on the mission of the program that it supported. The limitations of ODEQ's existing data, combined with the primary objectives of the project shaped the Oregon approach somewhat differently than the numerous data integration efforts now underway in other states.

Integration of Facility Data Nationally

Like ODEQ, many state environmental regulatory agencies have arrived at a need to integrate facility data. Historically, the federal Clean Water Act, Clean Air Act, Resource Conservation and Recovery Act (RCRA) and other environmental laws were adopted at different times, and provided a different statutory regulatory environments. States charged with administering programs under those statutes developed information systems piecemeal to meet program-changing objectives.

Increasingly, states and U.S. EPA have needed to consider a "cross-media" approach to regulation. The conventional approach left no way for regulators to connect the various interactions that they might have with a single facility. Materials removed from stack gases might wind up in a water outfall, or as contamination resulting from improper disposal. Levels of permissible toxins were evaluated independently for each media, without examining cumulative effects. Anecdotes are common of regulators presenting "good citizen" awards to a facility for work in hazardous waste reduction, only to find that the facility was being prosecuted (by that same agency) for water quality permit violations.

Pressures to integrate disparate data sources have led several states, in concert with U.S. EPA, to develop a standard for facility identification. Many states have undertaken projects to clean up and integrate data from legacy systems. These projects have typically consisted of the following steps:

• Re-engineering legacy systems. Typically, these systems use different data structures, software architectures, and even computing environments. Re-engineering is, in many cases, the only viable alternative.
• Developing a shared facility identification structure
• Developing business rules to enable agreement on common names and other attributes to be shared between systems, and to resolve disputes that would otherwise lead to data corruption
• Developing end-user tools and "clean" legacy data to reconcile facility identifiers across systems.

Projects of this nature have been recently completed or are underway in several states. Several inherent issues make this approach challenging. First, programs may have differing views of a "facility". For example, only one air emissions permit may be issued for any one operating facility. At the same time, separate operating permits may be issued for underground storage tanks. Each may be permitted to different operating units within the facility. Statute requires that a specific (and different) legal name be used for each permit. Contaminated site cleanup brings in an historic context. While a facility owner may have several interactions with the regulatory agency, cleanup work may be underway at the same location, related to a past owner of the property. While some states have resolved these details through appropriate data structures, reconciliation of legacy data is a complex process and requires some final arbiter function. Finally, such re-engineering efforts can be extremely expensive. The seven systems ODEQ sought to integrate represented an investment of $6 million. With only $500,000 available to accomplish the integration and data cleaning and to develop the public interface, total re-engineering was not viable for Oregon.

The U.S. EPA One-Stop Program

The One-Stop grant program was developed by U.S. EPA to provide funding for a broad range of demonstration projects showing results in one or more of the following objectives:

1. Foster Cross-media and geographic approaches to environmental problem solving.
2. Provide the public with meaningful and timely access to environmental information.
3. Reduce the reporting burden on industry.

ODEQ proposed to develop a geographically-based mechanism to access data regarding regulated facilities, across all media areas, focusing on the first two objectives above. The Oregon proposal required that four legacy systems be modified to include geographic references where none were included, or to modify the applications to use the central location data structure. Geographic locations would be determined for any facilities lacking that information. A data warehouse structure would be developed to integrate data from disparate data structures, and a public interface developed to present information in a useful manner to the general public. Although intended for Internet distribution, the resulting application also serves many internal users' needs. In January of 1999, ODEQ contracted with Excell Data Corporation to develop the Facility Profiler application. This paper will focus on the principles and design of the geographically integrated data warehouse, and on development of the public interface.

System Overview

ODEQ's "Facility Profiler" application is a GIS-enabled web interface to seven distinct production databases. The production systems are housed in an enterprise Oracle database. The Profiler warehouse is contained in an Microsoft SQL Server 7.0 Relational Database Management System in conjunction with Spatial Database Engine (SDE) software from Environmental Systems Research Institute (Esri), running on Windows NT (table 1).

 Table 1. System Architecture

The user interface for the facility profiler is provided through an application running on a separate Internet server (figure 2). This application hosts five separate sessions of Esri's MapObjects Internet Map Server to support mapping and search capabilities. To provide adequate performance in a variety of browser environments and connection speeds, all processing is performed at the server. The system is essentially stateless, using a set of parameters to maintain session information without any client-side processing. The interface application allows a user to specify a number of parameters, both geographic and facility oriented, and returns a set of facilities matching the query.

For each facility, a link is provided to a report detailing ODEQ's regulatory interests. The specific type of interest drives the report format, so that sections for a water quality permit, for example, are formatted differently that the section addressing an underground storage tank's information.

Warehouse Architecture

Population of the Facility Profiler warehouse is a multi-step process that involves both the locations of the facilities and their attributes.

Data Import - Facility Attributes

Production data is queried nightly to update the Profiler warehouse using Microsoft's Data Transformation Services (DTS). These Services control all data importation and assimilation routines for population of the System. The database is set "offline" and Open Database Connectivity (ODBC) database connectivity is used to access data through each of the seven donor system views, other customized data structures and two views that span all of the donor systems and the master facility geolocation table. As each table is updated, the results are placed into a working area. When all updates are complete, the system backs up the existing warehouse, then replaces changed data, adds new records, and removes any records missing from the new version.

Data Import - Facility Point Locations

During the update, a Spatial Data Engine (SDE) process is utilized to create and manage a layer for DEQ facility locations. Decimal latitude and longitude values are extracted via SDE query from the master "GeoLocation" table. All facilities that have a qualified longitude and latitude have a point created using the Oregon State standard projection and coordinate system. This service also creates new points when new sites are inserted into the profiler database and updates sites when longitude or latitude is changed. A SQL Extended stored procedure utilizes the C language application programming interface (C-API) provided by SDE to manipulate the layer representing DEQ points of interest. This information is stored in the warehouse, and used to expedite search processes.

A Geographically-Based Warehouse

Although seen by ODEQ staff as a GIS project with data warehousing elements, developers from Excell Data had perceived the project as a warehousing project with a geographic component. While GIS has been used as a front end for warehoused or production data (most often for a single system), the Facility Profiler implements MapObjects and SDE technology from Esri, which gives strong geographic orientation to the warehouse. Data with little more in common than a location, geography is the primary integrating mechanism of the warehouse, and the resulting data store is focused on answering geographically-oriented questions.

Geography as a Data Integrator

As described above, most integration of regulatory data has focused on reconciliation of naming and the business units concepts that loosely define a "facility". Much of the impetus for such integration is based on a desire to get a corporate "big picture", although it coincidentally serves (when geo-referenced) to answer the question "What's in my back yard?" Oregon was uniquely positioned, with so many of our systems already geographically referenced, to take a different tack. Early analysis revealed that reconciliation of ownership would be complex and expensive. On the other hand, existing data could be manipulated rather directly to address the "back yard".

The fundamental difference between the Oregon approach and that of other states is the focus on the geographic link, rather than a link to a facility, owner, or operator. ODEQ uses a "front-door" point location standard. A latitude and longitude, street address, and data accuracy of the location comprises a GeoLocation record. To ensure completeness of the warehouse, location data from almost any source is permissible, with allowable methods ranging from differentially corrected Global Positioning System (GPS) to Zip Code centroid where no other information is available. Each regulatory interest (facility, as loosely defined in different donor systems) is linked to a GeoLocation record.

Oregon's approach does not guarantee a complete picture based on ownership, but is always complete about environmental concerns related to a specific location. The following examples illustrate the effect of incomplete integration when relying solely on geography (figure 3). In the first case, Acme Fasteners is associated with a total of eight environmental interests. The facility is known under different names because of differing business practices in various regulatory programs. Acme Plating is a currently operating subsidiary of Acme Fastenings, while Acme Forge is a previous line of business operated at the same site. In this example, an air quality Title V permit has been issued along with a current water discharge permit (NPDES). In addition, there are three underground storage tanks licensed between the two lines of business. Acme Plating is registered as a hazardous waste generator, and cleanup of contamination from the Acme Forge line of business is underway at the site. All these activities have been correctly associated with the Acme Plating location.

Maintenance of "correct" integration requires a significant resource commitment. Staff from every program must work together to identify duplicate facilities. This integration approach does not admit the possibility of legitimate disagreement about "facility" identification. In the example below (figure 4), program staff have failed to note that Acme Plating is, in fact, co-located with the existing Acme Fastenings. A new GeoLocation record is created, but with the same (or nearly the same) latitude and longitude. The regulatory interests then have a different set of associations.

In the example above (figure 4), integration of the same set of regulatory interests is incomplete. However, any selection criteria based on geography will return the same set of results. The answer to the question "What's in my back yard?" is unaffected by the fact that there are multiple geographic location records. Where integration of relational data structures is typically based on identifiers which are intentionally meaningless (randomly assigned ID fields, etc.), geographic integration departs from that approach. The value used to associate data from different systems is a meaningful value, making associations like "Record A is almost related to record B", and expressing the difference as a distance.

From a more practical point of view, this approach allowed ODEQ to almost immediately make fully integrated data available to the public. Data from two of the older systems had not been geo-referenced, and was completed using address matching or similar approaches. Data in the system is tagged as to collection method and accuracy so that it can be a part of a continuous improvement process. While ODEQ wishes to reconcile as many of the location records as possible in cases where duplicate records truly identify the same facility, viability of the system is not dependent on the reconciliation.

Disparate Data

The seven donor system addressed by the Profiler contain data which differs in format and content. The task of designing the warehouse included definition of a useful set of data that is relatively common across all the donor systems. In the first phase of development, the primary objective of the profiler was to provide a comprehensive picture of ODEQ regulatory interests by location. The much larger task of providing the ability to "drill down" into donor system detail was left for a future release. Even a very limited requirement for commonality is difficult to apply across different systems. To address this problem, a set of 16 fields were defined, and appropriate data was found to populate each system (table 2). In addition a data label for each field was provided for each donor system. For example, a "reference" identifier is a part of the warehouse, and contains the unique record key that identifies the "environmental interest" identified within the donor system. Although stored in the same field, in an air quality permit report, unique key is called a "Permit number", while in a cleanup site report, it is a "Site ID". As a result, a single data model within the profiler supports varying data content. The donor system context is integral to understanding the system. The table below is a sample of the data incorporated into the warehouse.

Table 2. Donor System Attributes

Geographic Analysis

In addition to data from the seven donor systems, the profiler application uses a number of data layers extracted from the Oregon State Spatial Data library (table 3). The layers include political boundaries, habitat, land use, and natural areas such as watersheds. While the geographic analysis could be performed "on the fly", early analysis indicated that system performance could suffer as a result. Since the selection of parameters queried through the interface is defined in advance, results can be pre-calculate and fields can be indexed for improved performance. An extended stored procedure call the "point-in-polygon" method is used within SDE to populate geographic reference fields unique to the warehouse. The method uses data layers extracted from the State of Oregon Spatial Database Library and calculates the watershed, political district, county, and other geographically associated information for each facility point. Analytical layers are used only as search criteria. For example, a search can be limited to facilities in forested parts of the second state Senate district which lie in the Willamette River watershed. The system makes no effort to determine if such criteria provide any overlapping areas, so the user can specify a set of factors with no possible solutions, and simply receive an empty query result set.

In addition to data of interest in the context of environmental facility location, several data layers are incorporated to serve as "reference" layers. For example, without roads and highways, cities, county lines, and other identifying features, users will have difficulty locating themselves on a map. The table below summarizes the geographic data incorporated into the Facility Profiler. Reference layers are displayed on maps (at some predetermined scale).

Public Interface Design

The design of the Facility Profiler needed to make integrated facility information easily available internally and to the public. Because our target audience was broad we did not assume any prior familiarity with desktop GIS software or that people be "GIS-savvy". The system was not designed for our GIS peers nor to be a Web version of our favorite GIS software. However, some basic computer and Internet skills, along with a desire to explore are helpful.

Design Parameters

We have tried to plan and design the system so that assistive technology (tools to help people to more effectively use computers) can best use the system. When we started the project, existing design approaches were varied, with several countries publishing guidelines. At that time, the World Wide Web Consortium's (W3C) HTML 4.0 Recommendation seemed to be leading toward a very useful draft accessibility guideline. Ultimately, we adopted the goal of providing for system compatibility with text browsers (such as Lynx http://lynx.browser.org/). Two different interface options now provide opportunities for exploration of geographic-based information -- one uses a map directly, the other relies principally on useful geographic attributes. These are labeled as the "Graphic" and "Text" options. The final Text option of the Facility Profiler conforms to W3C's "Web Content Accessibility Guidelines 1.0".

The public interface technology that existed at the time of project planning was set as the minimum -- Netscape Navigator 3.0, Microsoft Internet Explorer 3.0 and Lynx. In addition, the design had to comply with the Oregon State guidelines for web pages. The requirement to support both Microsoft and Netscape's earlier browsers dictated that the web site was created without the use of client-side DHTML, ActiveX controls, or Java.   

Network performance is not included as a performance measurement for the interface. However, even with a server-only system, people often relate system performance to the interface they see and use. Many factors external to the local network cannot be controlled. Processing time to answer a request was defined as the amount of time to finish a cycle consisting of request from the web server sent to the database and response returned to the web server. Test of the system using the local network showed that initial Map-based query cycles were completed and results displayed in 19-28 seconds, and that subsequent stepwise zooming/queries, into a target area, range from 7-14 seconds depending on the size of the result set. In demonstrations to public groups conducted using dial up Internet connections, responses were satisfactory.

Search/Query Overview

A fixed set of query filters is available to the user, both text and map-based options. The application accommodates composite or nested filters; for example filter on program and county and type simultaneously, limited to one filter value per key item. The three "Search Criteria" pages allow the users to set filer criteria and browse in geographical fashion through sites where DEQ maintains facility interests:

• Facility Search Criteria (p4215.gif)
• Geographic Search Criteria (p4216.gif)
• Location Search Criteria (p4217.gif)

The following session example describes the steps for a typical search/query exploration.

Graphic/Map Option:

1. Select the "Graphic Version (p4218.gif)". Next a statewide map will be displayed.
2. Position your pointer over any county on the map. When you click on a county, the map window will zoom to the county selected. The large groups of dots usually represent cities and towns.
3. Position your pointer over any group of dots or town. Click to select a new point of interest. The map window will zoom in one step. The new map is centered on the area you selected.
4. Continue to point and click to further zoom and pan to your final area of interest. (You can also use the "Map Scale" bar above the map to zoom in and out.)
5. Review the selected list of businesses or places by clicking "Search Results" or just scroll down to the list. Note that with each change of the map, the list of environmental related companies and places changes.
6. Click on a company or place name to obtain a profile report.

Text Option:

1. Pick the "Text Version" (p4219.gif) of the Profiler. (Note: Most of the options used below will work from the graphic version also.) The screen display begins with the list of counties. (Note that this is not a strange shaped list. The counties are arranged across the page much the way the are across the state. Different, but fun.).
2. Position your pointer over the name Clatsop. Click on the county name. All the facilities in the county will be selected.
3. Click on "Search Results" to see how many facilities were found.
4. To narrow your area of interest click on "Search Criteria" and then select "Geographic Search" from the small set of options. Note that the "Geographic Search" page contains a long list of options.
5. Locate the "Vegetation" option. Using the scrollable pull-down list select "Coastal Sand Dunes - Spits and Beaches."
6. Click the "Map" (located below the search options). This will begin the selection of the mapped information based on the geographic search options selected.
7. When the search is done, click on "Search Results" to display the list of found facilities. Note how the number of selected facilities changes.
8. Click on "Search Criteria" again and from the small set of options, select "Facility Search."
9. From the scrollable drop-down list called "Facility Program", select "Hazardous Waste" and then click the "Map".
10. Select "Search Results" to see how many facilities were found.
11. Click on a company or place name to obtain a profile report.

Reports Overview

The Facility Profile report is made up of five main pieces of information:

1. Site Information - Name, Address
2. Aliases - Helps resolve the different names or spellings for a business or place.
3. GeoFeatures - Interesting list of geographic areas associated with a certain business or place.
4. SIC - The Standard Industrial Classification (SIC) is a commonly used classification of different industries.
5. DEQ Program Interest - Here you will see the link between the business or place and the different divisions or "programs" in DEQ. Acronyms used in the "Environmental Interest" section of Profile Report:

Interface shortcomings

The Facility Profiler can be judged as a successful information technology project. However, with the broad base of users, creating a useful interface using the "server side only" design requirements was not easy. The most common request is for access to more and more data: either more data within the application or a gateway to detailed reports for a single environmental interest. In some cases, other agencies own the needed data. While using the system people may find themselves wishing for certain functions, surprised by the behavior or lack of one, or they may not be sure what approach to take in their exploration of the data. Some of the specific shortcomings include:

Lessons Learned

Data Import - Facility Attributes

The initial proposal called for the consultant to design and build mechanisms to extract the appropriate data from the ODEQ enterprise Oracle database. Although extraction was feasible as indicated, the knowledge level required to apply appropriate business rules to the legacy data models threatened to overwhelm the project team. To ensure appropriate use of the data, ODEQ maintenance programmers were tasked with a one-week project to construct a view of each system data conforming to a specific template containing the fields described above.

Geographically-Based Data Warehouse

The data warehouse works fine and with some minor changes has been very stable. This is a critical foundation of the Facility Profiler and its importance should not be overlooked. Without a reliable source of data, no amount of GIS analysis or state-of-the-art interface design will provide the public with meaningful information.

Interface

When we began our design most of the user base was beginning to use Netscape Navigator 3.0 or Microsoft Internet Explorer 3.0. We saw the browser implementations of Java as a design risk for development of a public system. Use of ActiveX was absolutely not considered because of platform dependence. Now less than 5 percent of our external site visits are from people using these older browsers so we can design for browser clients other than HTML. However, we think we still have an obligation to users of text only browsers.

Accessibility

When the planning phase ended and the conceptual design phase started accessibility was only informally acknowledged. The proposed approach consisted of stripping out the graphics and turning off the GIS. But it was communicated to the designers and programmers that they needed to embrace this goal and the obligation not to drop content but to communicate through alternative channels. This is a service opportunity as much as a design challenge.

Moving in the Right Direction - User Comments

Conclusion and Next Steps

In the short term we plan to link the Facility Profiler's report page to detailed reports from the different donor systems. The unique identifying codes found in the Profile report will serve as the connection. This will greatly add to the amount of useful information available to citizens, plus provide a Web interface that the donor systems currently lack.

Less than 5 percent of our users are now using the older browsers that restricted our earlier design. This means that we can plan, design and construct for browser clients than HTML, such as Java script. We prefer to not "maintain" or re-model a second version of the Facility Profiler interface into existence. One approach considered would be to demolish the interface and rebuild from the ground up.

We still think we have an obligation to the users of text-only browsers to provide access to geographic information. User requests and the ODEQ strategic plan will both influence system redesign and construction, however, the original business goals still guide all project development:

1. Fostering Cross-media and geographic approaches to environmental problem solving.
2. Providing the public with meaningful and timely access to environmental information.

Mitch West: Co-Author
Manager, Business Systems Development
west.mitch@deq.state.or.us

Doug Terra: Co-Author & Presenter
GIS Coordinator and One Stop Grant Author
terra.douglas.m@deq.state.or.us

(503) 229-5696
TDD (503) 229-6993
Toll-free within Oregon (800) 452-4011
Oregon Department of Environmental Quality
811 SW 6th Ave.
Portland, OR 97204-1390.