Interaction of ArcInfo and ArcView with ORACLE in Tracking Hazardous Material Information in Jefferson County, Kentucky

INTRODUCTION



The idea to integrate ArcInfo and ArcView with MSD's Oracle 

database IWIS originated from a brainstorming session that was 

conducted between IWD and myself. IWD was a relatively new user in 

the GIS field, so they were not certain how they could apply the vast 

quantities of data they had both spatially and graphically. IWD did not 

want to place IWIS data into INFO format for a number of reasons:



	1. IWD only has one workstation and one ArcInfo trained 

	user.  It was impractical to utilize ArcInfo for data storage 

	when so much time and money had been expended in the use of 

	the Oracle database

	2. Oracle has the ability to support multi-user data. Since the 

	IWIS Oracle database has the capability to allow pure database 

	users to maintain and enter information into the same database 

	that can also be accessed by GIS, there is no real need to change 

	to an INFO format.

	3. Oracle databases can be accessed by other data access 

	programs such as MSQUERY, ACCESS, and other PC-based 

	software.



IWD staff members knew that the IWIS database contained vast amounts 

of data that could be utilized by GIS in a variety of ways. The problem 

was that they did not know how to get the two systems to "talk" to one 

another. Originally, if a user at IWD wanted to create a layout of 

hazardous material spill incidents for a particular month, a complicated 

and inefficient set of procedures had to be conducted. The first step 

required the user to implement the PC database query program called 

MSQUERY for extracting the data from IWIS. Second, the user had to 

convert the data into dBASE 4 format and transfer it to a floppy disk. The 

final step required the extraction of the dBASE 4 file from the floppy to 

the SUNS workstation so that it could be accessed by both ArcView and 

ArcInfo. The procedures involved in the extraction of Oracle data 

were indirect and inefficient for use in both ArcInfo and ArcView. 

IWD needed a process that allowed a user to extract IWIS data directly 

from a workstation in as efficient a manner as possible. In order to 

accomplish this goal we decided to initiate the following tasks:



	1. Oracle and ArcInfo had to talk to one another. A gateway 

	between the Oracle server and the SUNS server had to be 

	developed.

	2. Staff had to be trained in the use of simple SQL database 

	query commands.

	3. Staff were also trained to use ArcView's SQL Connect menu. 

	This menu allows the user to connect, extract, and directly 

	utilize tabular data from Oracle.

	4. Training was also conducted for various forms of table 

	conversion.



METHODS FOR EXTRACTING ORACLE TABULAR DATA



Once the gateway was set to allow ArcInfo and ArcView the 

capability to access the IWIS Oracle database's data, some background in 

the use and manipulation of the SQL language and methods of data 

conversion had to be taught.



In order to begin access to the IWIS Oracle database, the user must utilize 

a login sequence. This is in addition to the user's normal system login. 

One of the most interesting features inherent to Oracle databases is its 

internal security. Every part of an Oracle database can be set with 

different access rights and privileges. It is a user's login sequence that 

determines what features he/she has access. A sample login sequence can 

be seen as follows:



	CONNECT ORACLE johndoe/johnmsd1@T:sun01:iwis



johndoe is the login name; johnmsd1 is the password; sun01 is the SUNS 

workstation that houses the database; and iwis is the database itself. If a 

user wants to access the database from the home terminal, everything 

after the login/password is excluded. To disconnect or stop accessing 

Oracle data, the user types:



	DISCONNECT ORACLE



The process of connecting and disconnecting from the Oracle IWIS 

database is much simplified in ArcView. In the SQL Connect menu in 

ArcView, the menu defaults connection and disconnection to the Oracle 

database. Once the user has connected to IWIS after typing in the login 

sequence, the menu will display a scrolling list of all tables in which the 

user has access. The user has the option of either double-clicking and 

selecting tables and columns from the scrolling list to define the query or 

by entering a SQL script.Once the selection process is finished, the user 

selects query in order to transfer the records to a SQL table. This table 

can be utilized like any other table within ArcView. It can be used to 

create shape files, layouts, charts, etc. It is important to remember that a 

SQL table is stored by the SQL Connect option and not by the records 

themselves. The records are only stored as long as the project is open. If a 

project that contains a SQL query is opened again, ArcView will 

reconnect the database and relate any changes made to the database in 

your table. In order to save the query results displayed in the SQL table, 

you must save the records to an INFO of dBASE 4 table and disconnect 

the SQL query.



ArcInfo accesses Oracle data through the relate environment. 

Basically, the user has to set up a relate between a coverage and the 

Oracle table. To accomplish this, there must be a minimum of one 

common item present in both the ArcInfo coverage and the Oracle 

table. For example:



	Arc: relate add

	Relate Name: company

	Relate Table: company

	Database: oracle

	Item: incident_no

	Relate Column: incident_no

	Option: first

	Access: ro



It is important to know that the Option is always FIRST for Oracle 

databases. Read-Only (RO) is better for the Access since it queries three 

times faster than Read-Write (RW). Once the relate is set, a SQL query 

script can be initiated in order to create a table.It is important to note that 

tables that have been queried from an Oracle database can be easily 

converted into other table formats from ARC. Here are a few examples:



	INFODBASE - Converts INFO tables into a dBASE 4 table.

	DBASEINFO - Converts dBASE 4 tables into INFO tables

	SHAPEARC - Converts ArcView Shapefiles into ArcInfo 

	coverages



ArcView has the ability to access both dBASE 4 and INFO tables, but 

ArcInfo cannot directly access a table created by ArcView. The user 

has to save a table in INFO format in order to make it accessible.



CURRENT IWD USES FOR ORACLE DATA



ArcInfo



Currently, IWD does not have much direct Oracle to ArcInfo 

interaction. ArcInfo has been used primarily for its more advanced 

geocoding applications in order to convert address event tables into point 

coverages. Even though the geocoding methods are highly efficient in 

ArcView, LOJIC has developed a geocoding applications and many other 

useful programs that are capable of acquiring a better match rate between 

address sites and address event tables



ArcView



Most of the direct Oracle to GIS interaction is utilized through ArcView's 

SQL Connect menu. Hazardous spill incident data is the most routinely 

accessed Oracle to GIS data. On a monthly basis, IWD uses a standard 

SQL script to extract spill incident information. A sample script appears:



	SELECT INCIDENT.INCIDENT_NO, 

	COMPANY.COMPNAME, INCIDENT.SPILL_LOC, 

	VARIABLES.VARDESC, INCIDENT.REPORT_DATEFROM 

	IWISDBA.COMPANY COMPANY, IWISDBA.INCIDENT 

	INCIDENT, IWISDBA.INCIDENT_MATERIAL 

	INCIDENT_MATERIAL, IWISDBA.VARIABLES 

	VARIABLES WHERE INCIDENT.COMPNO = 

	COMPANY.COMPNO AND INCIDENT.INCIDENT_NO = 

	INCIDENT_MATERIAL.INCIDENT_NO AND 

	VARIABLES.VARNO = INCIDENT_MATERIAL.VARNO 

	AND ((INCIDENT.SPILL_LOC Like '%Critte%'))



This script will produce a table that contains all the hazardous material 

release that occurred on Crittenden Drive. The table also contains the 

Company Name responsible, an incident number, the type of hazardous 

material involved, and the address the spill occurred. This table can then 

be used to produce ArcView shapefile, maps, and charts. Often IWD will 

also convert shapefiles created by ArcView into ArcInfo coverages 

that can be used to produce ArcInfo maps and statistics.



SQL queries through ArcView were also utilized to produce the 

following shapefiles/coverages:



	1. Hazardous Spill Incidents (All incidents up to 1/1/96)

	2. Significant Industrial Users (SIUs)

	3. Hazardous Material Storage Sites (HAZMAT)

	4. Industries



FUTURE USES FOR ORACLE IN ArcInfo AND ARCVIEW



Hazardous Materials Application 



IWD would like to have a QUI interface that has the ability to access a 

variety of hazardous material information in the Oracle IWIS database. 

This application would have the ability to display a variety of background 

coverages that have a direct correlation to hazardous materials (i.e., 

buildings, streams, roads, sewers, parcels, etc.). The user would have the 

ability to zoom in/out and pan to various parts of Jefferson county. The 

interface would also be able to zoom to specific addresses and street 

intersections. With regard to spatial analyses the application needs to be 

able to:

	1. Zoom to a selected address and display selected hazardous 

	material information related to either that individual parcel, or 

	the parcel and all adjacent parcels.

	2. Zoom to set of parcels that are enclosed by a particular 

	boundary and display selected information

	3. Print reports of the selected hazardous materials related to the 

	selected parcel

	4. Produce 11x17 inch maps of the selected parcels



The primary types of hazardous material data that IWD would like to 

display include:



	1. All industries within a selected set of parcels that have an 

	approved HAZMAT plan.

	2. Industries that have significant noncompliance violations

	3. All hazardous material releases or spill incidents.



Strategic Manhole Monitoring Program (SMMP)



The SMMP is a program that currently monitors 69 manholes in the 

sewer collection system for pH, color, temperature, conductivity, and 

explosively. The monitoring process of these manholes was initiated due 

to the Federal Emergency Management Administration's (FEMA) 

Mitigation in response to the sewer explosions that occurred in 

Louisville, Kentucky in 1981. Even though this program has yielded a 

great deal of satisfactory data and policies for Louisville serve area, it 

does not furnish sufficient data for other Jefferson County service areas. 

The program is inefficient in optimizing sampling analyses, locations, 

and frequencies.



The use of direct Oracle to GIS interaction could provide a more 

proficient array of data needs. These needs include the following:



	1. Determining cause and effect issues between point sources 

	and strategic manhole monitoring sites.

	2. Determine trend analyses and cause and effect issues related 

	to SIUs.

	3. Reassess local service limits according to a variety of 

	hazardous material issues.

	4. Identify and isolate the location of point sources and non-

	point sources

	5. Develop commercial discharge permits and policies according 

	various trend analyses and cause and effect studies



Other Uses



IWD would like to use direct Oracle to GIS communication for a variety 

of other needs. These needs are:



	1. Ability to implement water quality management for drainage 

	basins

	2. Tie point sources into cause and effect issues with regard to 

	instream sampling sites. If five pounds of lead is released from 

	each of four point sources, a sample made at a particular 

	connection point (Instream sampling site) should indicate that 

	twenty pounds of lead was released. If no correlation exists, 

	new policies have to be implemented.

	3. Also need to tie Combined Sewer Overflows (CSOs) and 

	Sanitary Sewer Overflows into cause and effect.

	4. Need to run risk assessment inventories and create models for 

	industrial sites (HAZMAT and SIUs)



CONCLUSION



The interplay between IWIS Oracle database and ArcInfo and 

ArcView will probably become an integral part IWD's use of hazardous 

material data.  It has already proved its usefulness in determining a 

variety of hazardous material sources such as HAZMAT sites, SIUs, 

industries, and hazardous material release (spill) incidents.  With new 

projects such as the Hazardous Materials application, SMMP project, 

cause and effect, and hazardous material policy issues, the continued use 

for this Oracle/GIS marriage seem limitless.