John Sawicki
Relational databases are used by GIS packages like ArcInfo to store attribute information for spatially related features. Organizations with databases that contain simple spatial features are now looking to exploit these databases using GIS. Many organizations' legacy databases contain large quantities of spatial and attribute data that are used to store and track information on products or other spatially oriented holdings. These organizations have requirements, based on current operations, to input, store, and query data in these existing databases. Some find GIS impractical for storing and handling the complexity and the amount of data found in their databases. In many cases these databases contain simple spatial feature representations, such as minimum bounding rectangles, with related attribute information in several relational tables. This paper will discuss methods for building an ArcInfo AML Geographical User Interface (GUI) for these spatially oriented databases. The discussion will focus on creating a GUI interface that allows a user to input, store, query, and spatially display data from a legacy relational database without transitioning the spatial information to a GIS.
It is presumptuous to assume that all organizations store their spatially related data sets in a GIS and its respective data structures. Many organizations have dedicated numerous years and considerable funds to the development of databases to store data records containing simple spatial representations (such as minimum bounding rectangles). These databases are designed with the emphasis on feature attributes and not on spatial representations. Most organizations use these databases to record, query, and report data holdings. Data found in these tables may be shared by several departments.
With the advent of GIS, organizations that have databases such as these are starting to express interest in graphically displaying the spatial features stored in tabular databases. Database users may find a GIS inadequate to support their querying and reporting needs. These same users find it more attractive to keep their existing database systems and add GIS functionality to them. Some reasons why users do not convert these data sets to a GIS are:
The information in this paper is based on a system development activity involving the integration of GIS functionality with relational database tables. The Production Requirements Management System (PRMS) supports softcopy and hardcopy map production requirements for a government client. The PRMS uses ArcInfo version 6.1 and ORACLE 6.0. This paper does not address functionality found in newer versions of these products. Application development was performed in ArcInfo's Arc Macro Language (AML) and Oracle Forms.
This paper addresses the development tactics and methods in building this system, but does not address specific information about the PRMS except where it is necessary to describe the requirements. The goal of this paper is to discuss basic AML programming techniques to access and exploit spatial and attribute data stored in a relational database.
System development utilized AML code and menu based user interface development. A UNIX workstation running ArcInfo with Database Integrator (i.e., RDBI) and ARCPLOT accessing an ORACLE version 6 database served as the base configuration for the PRMS. The PRMS interface used ARCPLOT as the base application module. The system runs routines that require ARC, INFO, Tables, or ARCEDIT functionality initialized through the &DATA AML directive.
Database Integrator, a set of commands within the core ArcInfo product, allows interactive and programmatic access to relational database tables and functions using standard SQL statements. There are three basic commands to access tables connected through Database Integrator: DBMSEXECUTE, DBMSCURSOR, DBMSINFO/INFODBMS. Building an AML-based system connected to a relational database relies heavily on the use of these commands in conjunction with AML functions, directives, and variables.
DBMSEXECUTE allows the user to issue a standard SQL statement to any connected database and view the results. The SQL statements in Database Integrator functions must consist of standard SQL commands and must not contain vendor specific SQL extensions. These statements must meet all host database syntax requirements. DBMSEXECUTE commands can be issued on one ArcInfo command line or by entering a multi-line dialog mode. Using the command line option limits the command line length to 1024 characters, while the multi-line dialog mode allows the user to enter 4000 characters. The DBMSEXECUTE command displays the results of the SQL statement in the window in which the command was issued.
DBMSCURSOR produces an ArcInfo cursor from a standard SQL query statement issued to any connected database. A cursor is a pointer to a set of selected rows in a table. Cursor commands allow users to navigate, inspect, or update the selected set of records. The RDBI cursor implementation is similar to the INFO version, but lacks some functionality for navigating the selected data set. The DBMSCURSOR command, like DBMSEXECUTE, uses standard SQL commands properly formatted for a specific host database system and can be issued on a single command line or through the multi-line dialog.
DBMSINFO copies a relational database table or view into an INFO
table and automatically converts the database field definitions
to types allowable in INFO.
The PRMS is designed primarily as a database management application using ArcInfo AML to provide the Geographical User Interface (GUI). A "Geographical" user interface provides an application with spatial components and functions in the interface where none had existed previously. The purpose of the GUI is to combine the best of relational database and GIS into a cohesive system to perform the required tasks of a tabular data system while allowing the user to access the spatial data components and to allow the user to benefit from a geographic point-and-click paradigm.
The GUI design focuses on accommodating a wide user community with varied GIS and computer skills. An intuitive menu structure and layout allows the beginner and advanced user to perform any task with relative ease. The PRMS GUI hides all ArcInfo modules and programs from the users. Access to ArcInfo's command line interface is available to the experienced user. The PRMS interacts with the users through AML menus, in most cases, except where the system utilizes Oracle Forms with UNIX and custom C routines. The PRMS GUI interface uses ArcInfo because of its ability to access relational database information and the ability to perform spatial display operations.
The PRMS requirements specify that all spatial coordinate information be stored in relational database tables. Because of this unique requirement it was necessary to devise ways to enter, display, and query spatial and attribute data from a relational database table via AML menus and programs. The basic spatial feature in the database is a minimum bounding rectangle represented by two corner points. The PRMS stores all spatial data features in geographic coordinates (latitude/longitude) in decimal degrees. All form menus that require the user to input coordinates into the database or require the system to display coordinates from the database required coordinate conversion to degrees-minutes-seconds (DMS) format to meet a customer requirement for coordinate data entry and display in DMS.
System functions including data entry, data query, data graphics display, data reporting, and other functions are initialized directly from the ArcInfo GUI. Functions like database security, setup and control features are implemented in ORACLE. All AML code recognizes and follows all database characteristics (table relationships, table accesses, field types, etc.) to ensure all AML functions work properly.
An important part of any database system is the control and security of all database tables involved. ORACLE provides data security by controlling the access to tables at several levels. The first level of security is a userid and password combination. The PRMS requires each user to enter an ORACLE userid and password during the startup routine. The first step of the startup AML routine is to connect ArcInfo to ORACLE by prompting the user for their userid and password. The AML proceeds to check to see if the connection to ORACLE is successful (Example 1).
CONNECT oracle %passwd$user%/%passwd$passwd%
&if [keyword oracle [show connects] xx ] lt 1 &then
&type Cannot connect to ORACLE ..... Is your userid and password correct ?
&else
&do
&type Connected to ORACLE as user %passwd$user%
&s .sys$user = %passwd$user%
&s .sys$passwd = %passwd$passwd%
&end
Example 1.
If the connection to ORACLE is not successful, the system warns the user of the situation and halts execution of the AML. The connection to ORACLE is refused if the database is unavailable or if the userid and password combinations are invalid. If the connection is successful, the system stores the userid and password in global variables for future use and proceeds with the remaining startup procedures.
ORACLE provides a second level of security by controlling the access to individual tables. ORACLE and other relational database systems maintain and enforce primary table ownership to one user. When users connect to the database using their userid and password, ORACLE allows them to view and access tables they own. This system uses an application database administration user as the owner of all system database tables. All DBMS commands in the PRMS reference this table owner name in each SQL statement. If the statement does not specify a table owner name an error will occur during execution. In Example 2, the cursor command prefixes the table owner "sysdba" to the table name "master_inventory."
DBMSCURSOR allrecords DECLARE ORACLE select * from sysdba.master_inventory
Example 2.
The owner of a given table is by default the only user who can view and access it. The PRMS database administration grants access to the database tables to individual users or to a role which represents a group of users. These accesses allow the users of the PRMS to view and update the systems' tables.
When an AML executes a DBMS command, ORACLE interprets the user's privileges and table accesses first. If the user does not have access to the table, the SQL statement fails and ORACLE returns an error back to the DBMS command. ArcInfo displays this error in its command window only, and does not pass the error to the AML directive. If an error occurs, the issuing AML program may fail if the AML expects data to be returned. To prevent this error from affecting the rest of the AML code it is essential that each AML routine check table access prior to issuing any DBMS command. In this example (Example 3), the AML code checks to see if a user is granted the role of "ADMIN."
DBMSCURSOR check_admin DECLARE ORACLE select granted_role ~
from user_role_privs where granted_role = 'ADMIN'
DBMSCURSOR check_admin open
&if not %:check_admin.AML$NEXT% &then
&do
&type 'No Access.... Stopping AML'
DBMSCURSOR check_admin remove
&return
&end
&else
DBMSCURSOR check_admin remove
Example 3.
If the AML cursor returns a record the user has the role and the AML continues. If the cursor is empty, the user does not have this role and the AML warns the user and exits. All AMLs that use DBMS commands to update tables or look at tables containing sensitive system information run checks like the above example to validate the user's access.
The PRMS contains several functional areas that support data entry into ORACLE tables. The PRMS user interface presents the users with two types of data entry menus, AML menus and ORACLE forms. Procedures requiring the user to enter spatial data and attribute data use AML menus. Procedures requiring the user to enter attribute data only use ORACLE forms.
Data entry from ArcInfo requires a combination of AML form menus, AML functions and directives, and Database Integrator commands. The combinations of these commands allow for several possible methods and scenarios for developing data entry routines. The PRMS uses two basic methods for entering data into the database from AML menus. One method uses the DBMSCURSOR command and the other method uses the DBMSEXECUTE command to access the database tables. The ArcInfo manual "Managing Tabular Data" describes a method similar to the ones employed here and served as the basis for these methods. The PRMS uses a modified version of this method that improves AML performance and that meets the requirement for the use of multiple cursors (ArcInfo 6.1 limits the number of simultaneously open DBMSCURSORs to five).
The ArcInfo manual suggests opening a cursor before creating the menu and using the cursor variable in the menu. When DBMSCURSOR opens a cursor, ORACLE places a lock on the table for the duration of the cursor. These locks prevent other users from updating these tables. If an AML update routine fails for any reason there is a chance that ORACLE will not remove the lock on the table. In this situation a database administrator must remove the lock before anybody can update the table again.
These two side effects (open cursor number limitation and table locking) led to the development of hybrid methods using DBMSCURSOR and DBMSEXECUTE. These methods allow the user to enter all information into a menu and then press an update button that triggers either DBMSCURSOR or DBMSEXECUTE to update the tables. This limits the time the tables are locked by one user. These methods also reduce the number of AML operations while the cursor is open which in turn reduces the chances of an unexpected AML error. Both methods use global variables to store all user entered information. In the DBMSCURSOR method when the user chooses to update the database, the AML routine opens a cursor and transfers the information from the global variables to the cursor variables. In the DBMSEXECUTE method when the user chooses to update the database, the AML routine builds a SQL statement using the global variables. The PRMS uses the DBMSEXECUTE method frequently when the table update procedure involves one table and a small amount of information. Complex procedures involving many tables and a substantial amount of information use the DBMSCURSOR method.
Data entry from ORACLE Forms requires small amounts of AML programming and more ORACLE programming. An advantage to programming data entry functions in ORACLE is the inherent data integrity checks and query capabilities. A disadvantage to using these menus is the inability to use ArcInfo to capture screen coordinates within ORACLE data entry menus.
Developing a data entry function in AML takes a considerable amount of coding to control data entry and to ensure data integrity during all steps. The basic steps to performing data entry are:
1. Check table access
2. Display data entry menu and allow the user to input data
3. Validate all data entries
4. Create a unique ID for the record
5. Declare and open cursor
6. Perform cursor insert or update
7. Transfer data from menu to cursor variables
8. Close cursor and commit to database.
These steps describe the DBMSCURSOR method for data entry. The DBMSEXECUTE method replaces steps 5 through 8 with a DBMSEXECUTE command and a SQL statement to update the database.
As mentioned above the first task of an AML program that performs data entry is to check the user's access and update privileges for the database tables involved in the data entry procedure. Most data entry procedures update several relational database tables at one time. If the procedure involves more than one table, the AML routine checks each table. The AML routine stops any users who do not have access to the tables from proceeding with the procedure.
AML form menus store the data entered by the users in global variables. All AML menu fields match their corresponding database field types and length specifications. The AML menus also require the user to enter all mandatory data fields. Menus that require spatial data coordinate entry use AML code to allow the user to choose coordinates from the ARCPLOT display (i.e., with the mouse).
When the user completes the data entry tasks in the menu, the AML routine checks the format of the data in the variables to see if they are properly formatted for ORACLE. Fields such as dates, numbers, and text strings should match their ORACLE field requirements. In most cases the menu checks the data entered for the proper format. If the menu does not check the format of the data during data entry, the AML routines check the data format before adding or updating the database tables to prevent any database errors.
Most data entry procedures in the PRMS use and require a unique ID value for each record in the database. This unique ID must be obtained for each record before entering the data into the database through DBMSCURSOR or DBMSEXECUTE. Example 4 demonstrates a cursor used to get this value from a next value generator found in ORACLE.
DBMSCURSOR reqseq DECLARE ORACLE select sysdba.id_nmbr_seq.nextval from sys.dual
Example 4.
When all variables are checked, the AML routine performs all steps required to pass the data to ORACLE. The DBMSCURSOR method declares and opens a cursor for update and populates the cursor variable with the values stored in the global variables. After completing the transfer the cursor is closed and the AML routine issues a DBMSEXECUTE command with the SQL commit command to commit the new data into the database. The DBMSEXECUTE method uses a pre-defined SQL statement with the values in global variables to update the database. After issuing this command it is necessary to commit the new data to the database.
Using ORACLE Forms in an AML system is achievable through the use of the &SYSTEM directive. This paper will not discuss how to build an ORACLE form. Example 5 demonstrates how to use the &SYSTEM directive in conjunction with the UNIX XTERM command to display an ORACLE form.
&sys xterm -bg CornflowerBlue -fg black -title "Identify Map Cell" ~ -n ID_CELL -geo 80x25-0+0 -fn 9x15bold -l ~ -e sqlplus -silent %.sys$user%/%.sys$passwd% @$SYSDIR/oracle/sql/identify_cell
Example 5.
As with the AML data entry procedures, a check for table access was run prior to executing this command. When using the xterm command as listed in the above example, the PRMS displays the menu as a modal object. This prevents the user from using the ArcInfo system while the menu in the xterm is still open. When the user closes the menu, control is returned to the application. Some of the system's ORACLE data entry procedures require the menu be created in the background (a UNIX term for an independent window or process). The &SYSTEM directives for these menus append the "&" background character to the end of the XTERM statement.
The PRMS displays spatial data from the database when triggered by either a user-defined query or by a pre-defined system query. The user-defined query allows the user to select a set of records from the database and then display the spatial and attribute data returned. Pre-defined system queries select and display records from the database based on a standard set of queries. All queries perform basic spatial and attribute selections.
The PRMS provides query tools to allow the user to query records from the database in a tabular and spatial form. Tabular queries allow the user to apply standard logical operations against database columns and values to select database records. Spatial queries allow the user to define a box to select records contained in or passing through the box.
All user-defined query functions operate in two modes: a "Build and Execute Query Statement" mode and a "View Selected Records" mode. The first mode allows the user to build and edit tabular and spatial query statements. The user builds the tabular statement by selecting the column name, logical operator, and database value from pulldown list boxes. The user adds individual statements (i.e., query constraints) to a statement box and logically combines them using an "and" or "or" operator. The user builds a spatial query by defining a box on the display screen or by entering coordinates manually into the query menu. The spatial and tabular queries can be performed independently or tied together using a logical "and" statement to combine the two queries.
When the user executes a query statement, the AML code builds the SQL statement and uses a "count(*)" select statement to return the number of records found. Example 6 shows the AML code used to execute a spatial and tabular query against a given ORACLE table. In this example, local variables store the coordinates (%nwlat%, %nwlon%, %selat%, %selon%) required for the spatial box search and the tabular query statement (%string%).
DBMSCURSOR %cursor% DECLARE oracle select count(*) from sysdba.%table% where (((( NLAT >= %nwlat% and SLAT < %nwlat% ) or ( NLAT > %selat% and SLAT <= %selat% ) ) and (( WLON <= %nwlon% and ELON > %nwlon% ) or ( WLON < %selon% and ELON >= %selon% ) ) ) or (( WLON < %selon% and ELON > %selon% ) and ( NLAT < %nwlat% and SLAT > %selat% ) ) or (( WLON < %nwlon% and ELON > %nwlon% ) and ( NLAT < %nwlat% and SLAT > %selat% ) ) or (( NLAT > %nwlat% and SLAT < %nwlat% ) and ( WLON > %nwlon% and ELON < %selon% ) ) or (( NLAT > %selat% and SLAT < %selat% ) and (WLON > %nwlon% and ELON < %selon% ) ) or (WLON >= %nwlon% and ELON <= %selon% and NLAT <= %nwlat% and SLAT >= %selat% ) ) and ( [unquote %string%] ) end
Example 6.
After the user executes the statement a cursor retrieves the number of records that will be selected and displays this in the query menu. The user can redefine either the tabular or spatial query and re-execute the statement in order to decrease or increase the number of records selected. Once the AML code has a valid count (greater than zero) the menu activates a button which lets the user view the selected records.
When the user selects the view button, the AML code reissues a statement similar to the one depicted in example 6. This new statement contains a query statement identical to the previous statement except the count statement is replaced with a select "*" statement to return all columns. The user also has the option to enter a range of records to view. If the user enters a range restriction on the view an additional SQL clause is appended to the original statement.
When the user selects the view button the AML routine replaces the query menu with the view menu. The view menu shows a record key for each selected record in a scrolling list. The record key combines the information from several fields in a single character string. In most cases the record key contains the unique ID with other fields to describe the record to the user. The scrolling list allows the user to select a record by viewing these record keys. Once a user selects a record, the user has the ability to perform specific operations on that selected record. Some of these operations include the following: display all information on this record directly from ORACLE, display the minimum bounding rectangle on the map display, change the record, and delete the record.
There are two methods for displaying spatial coordinates and attribute information from ORACLE. The first method uses a DBMSCURSOR to select and return each of the desired records. After declaring and opening the cursor, the AML uses a &DO &WHILE loop to access and draw each record using the coordinate information and another field as the symbology key. The AML routines keep the cursor open to redraw the features and issue a DBMSCURSOR FIRST command to start the cursor at the first record. When drawing is complete, the AML closes the cursor.
Another method uses DBMSEXECUTE to issue the user query or pre-defined query statement to create an ORACLE view that represents the results of this query. DBMSINFO copies the data from the ORACLE view to an INFO table where it can be further exploited. The display AML uses an INFO cursor to display the records from the table in similar fashion to the DBMSCURSOR. This method is more flexible because it allows the programmer to perform further analysis and manipulation on the resulting records and to save the set for future use. Accessing an INFO table is similar to a DBMS table using cursors except the programmer has more control over the INFO cursor. The INFO cursor command allows navigation using the FIRST and NEXT commands like DBMSCURSOR, but the INFO version allows navigation to a specific record as well.
Regardless of which cursor is being used to control access to the returned records, each record is drawn on the ARCPLOT display using a preset or user selected data field to drive the symbology. The PRMS contains several INFO lookup tables that relate the field values to a symbol. All system symbols are stored in the ARCPLOT symbol palette. During the system startup AML all symbols in the default ARCPLOT symbol set were cleared from memory and the custom symbol sets were created or loaded. ARCPLOT stores up to 999 symbols in one symbol set. The first one hundred symbols contain generic symbols that the user can select for drawing routines through a menu interface. The other symbols store the reserved system symbols used by the AML and lookup tables to represent the spatial features.
All drawing routines use ARCPLOT drawing commands like BOX to draw graphic primitives on the display that represent the selected records. These graphics are drawn on top of a set of base coverages stored in decimal degrees. The PRMS also captures all drawing commands in a map composition file. The user can elect to save or plot the current map composition at any time. When the user or system updates the screen through a clear or redraw (clear, zoom, pan) command the map composition file is opened to capture all ARCPLOT drawing elements. It is necessary to group the individual spatial records drawn from the queries to avoid exceeding the number of graphic elements allowable in a map composition.
The PRMS contains several database utilities to help the user manage database tables, user accesses and privileges. Some of these utilities include functions to import and export tables, to manage user roles and table accesses, and to edit system validation tables. All utilities require the user to enter the application database administration userid and password as a security procedure. All utilities use AML menus and commands to issue DBMSCURSOR and DBMSEXECUTE commands to retrieve and store database information.
Users also have access to a SQL command line interface from the AML main menu. Example 7 shows the AML menu code to issue a DBMSEXECUTE command to provide the SQL interface.
7 dbmsline.menu %b1 DBMSEXECUTE oracle %input1 %b1 BUTTON KEEP 'Exit' &thread &delete &self %input1 INPUT DBMSLINE 80 TYPEIN YES SCROLL NO ~ KEEP ~ RETURN '&full &popup; &tty DBMSEXECUTE oracle %dbmsline%;&full &on; &set dbmsline = ' ~ CHARACTER
Example 7.
GIS technology provides the capability to store, manage, analyze, and display spatial data. Using this technology as a basis for a system to manipulate and display spatial data stored in a relational database allows an application to use features of both GIS and relational database systems. The PRMS successfully demonstrates the integration of GIS and relational database technologies to store and display spatial features managed in database tables.
This development activity focused on providing the customer with a spatial view of their legacy databases while not re-engineering their database systems and data structures. The PRMS provides a new menu interface system for their database tables that did not significantly change the underlying database structure or functionality of previous systems. Users of the PRMS have access to their databases' tables through the AML-based system and through ORACLE SQLPLUS. This AML-based system is complex in that it requires knowledge of both ORACLE and ArcInfo to manage and maintain, but for an organization who was familiar with ORACLE, this transition was easily accepted. The PRMS focused on keeping and embedding all ORACLE reports, queries, and SQL operations used by the customer in the system's AML menus. The AML menus provide the user with a simple way to perform tasks that they used to perform through command line operations. The added capability to display and plot spatial features from their databases' tables allows users to view new spatial displays to create a better understanding of their data in the context of both queries and reports. As users work with the PRMS, they discover new operations and data processing methods that bring new meaning to their data.
In the future, functions from the PRMS such as the GUI interface may transition to a package like ARCVIEW. An ARCVIEW version of this system would allow the user to view and perform simple queries from a desktop environment.
Environmental Systems Research Institute, Inc. Managing Tabular Data. Redlands, CA: Esri, 1990, 1991, 1992.
John Sawicki
GIS Applications Engineer
TASC
12100 Sunset Hills Road
Reston, VA 22090 USA
Telephone: (703) 834-5000
FAX : (703) 318-7900
Email: jsawicki@tasc.com