Tony Bacon
Integration of GIS into Oracle applications can take many forms. No matter the form the issues are generally the same. Maintaining feature to attribute data integrity, adding spatial query and display capabilities in Oracle forms, reports and perhaps even straight SQL queries.
Talk to a few people about developing GIS enabled Oracle applications and the chances are good if they understand the least of what you are talking about they will suggest SDE before you do. Can SDE play a role in the integration of GIS in an Oracle application? Should it?
"The role of SDE in Oracle Applications" explores making the SDE connection in Oracle at the client and the server levels and explains what SDE brings to the Oracle application developer's table.
Earlier this year I thought I would have my first real life exposure to SDE. The Forest Serviced had signed up to beta test SDE 4.0 and Arc/INFO version 8 with one of the projects to which I am assigned. As soon as the first weeks training was scheduled I submitted an abstract for this paper. Months passed, training was rescheduled three times before the whole idea was abandoned. Priorities had changed and there would not be time to participate in the beta test. None-the-less, my curiosity continued and with the encouragement I decided to go ahead with the paper.
Over the last three years I have learned a lot about developing applications combining Oracle and Esri technologies. I've worked on almost a half dozen Oracle/Arc/INFO/ArcView applications and witnessed the development of another half dozen. My client, the U.S. Forest Service has been a staunch and strict believer in Oracle as an environment, not just a database server. Applications are designed, and built, using Oracle Designer. My work involves integrating Arc/INFO and ArcView capabilities into these applications built for the Oracle environment. SDE has been suggested many times as a means to simplify that work.
Most of today's Forest Service natural resource database applications are built for the collection and management of the non-spatial, textual data only. There are many reasons why GIS has not been fully embraced, or incorporated in the lifecycle of these applications, reasons like technology limitations, management awareness, design and developer technological dispositions, and of course, politics. In my opinion the root of the problem is in the technology, or, because it has been an evolution, was the original root problem. That is there has been a lack of DBMS support from GIS. Put another way, there has been a lack of GIS support from the DBMS. Things are finally beginning to change in a way that might help us get the "maps in our apps", even for the Microsoft platform challenged, or Microsoft wary.
"The role of SDE in Oracle Applications" takes a cursory look at the contribution of SDE 4.0 to the challenges of integrating Arc/INFO and ArcView with Oracle applications. It is an attempt to clarify SDE's potential role, and to differentiate between immediate benefits, which do not require special programming with the SDE client API from those that do.
We'll start out with a cursory review of the SDE architecture to take a look at how SDE fits in the Oracle architecture, to understand the positioning of the SDE components in an application. There are three architectural variations I want to examine, generic SDE, SDE implemented under an Oracle client and SDE implemented with Oracle Spatial as the back end data store. Generic SDE is the way SDE has been portrayed in the documentation, at least the way I read it. SDE implemented under an Oracle client adds to the picture in an attempt to portray what it looks like to implement that generic SDE architecture into an Oracle Application. Finally, SDE implemented with Oracle Spatial rounds out the picture.
SDE implements what Esri calls the "cooperative" client server architecture. In general, clients are the consumers of data that is provided by the server. What makes it "cooperative" is that many of SDE's spatial functions can occur either on the client or the server side. The application developer decides on how this "spatial processing" is distributed. For an Oracle application to access SDE it must do so by incorporating the SDE Client Library, also referred to now as the Open Application Programmers Interface, or API. Originally an API was only available in the C programming language. There is now a Java API, a COM API and two SQL APIs, one for DB2 and one for Informix. Take a look at the figure below to see how this API fits into the overall Architecture.
Notice how the server "sits" over the Oracle DBMS server between the client application and Oracle. The important thing to recognize is the "middle-ware" orientation of SDE. In this configuration, access to SDE functions is dependent on accessing the SDE client. This may be more evident in the next illustration.
Take note that the figure above is basically the same as the previous one with the exception that a "user supplied interface" has been added to clarify the role of the "open API". There are two points to be made here. In its current release the API is not accessible directly from SQL or PL/SQL. What about the Java API and the choice of programming for the Oracle server in Java beginning with Oracle version 8? When I learned of the Java API I immediately got excited. The way I understand it, at some point Java becomes an alternative to PL/SQL. I took the notion quite literally that where I now have PL/SQL code I could have Java code and therefore a Java API for SDE would be a direct plug in. Not according to the documentation. Yes, it is true that some of the coding, maybe even most of it can be written in Java. The catch is that an interface must be declared for each Java routine that interfaces directly to Oracle. What that means is that before SDE can be fully utilized from an Oracle application, an interface must be developed to either the C or the Java API.
Things might bet better. Maybe we should take heart in the fact that Esri currently provides SQL API's for DB2 and Informix. Perhaps that is an indication that they will do the same for Oracle.
Even with the "user supplied interface" or a future offering from Esri to provide one for us, the picture might not be as straightforward as we might hope. Depending on the SDE functionality to be incorporated in an Oracle form, and how the "user supplied interface" is written, we might find ourselves managing data access on two fronts. Native form functions access the Oracle RDBMS directly thereby sidestepping SDE entirely. If we want our forms to be spatially aware we will have to manage the access to data on both fronts.
The figure above is an attempt to point out that an Oracle application could gain its GIS capabilities from Oracle Spatial. But that doesn't zero out SDE's contribution which is to provide manage the GIS data in the Oracle Spatial. In this configuration it is most likely that the capabilities designed into an application to meet functional requirements would be generated in the environment that most effectively addresses them. The strengths of Esri products for managing features and spatial analysis, the strengths of Oracle for its application form generation capabilities.
A good understanding of the architectural layout helps to understand where SDE manifests itself programmatically in an Oracle application but it doesn't reveal the whole story. Two more facets need to be explored. I mentioned that my work has involved integrating ArcView and Arc/INFO into Oracle applications. If we know anything about SDE we know about the performance implications of SDE on ArcView. That is one indirect benefit, at least in relationship to our Oracle application. In addition, I presume we all know that SDE stores features in Oracle in a table structure. Tables can be constrained by other tables so by applying foreign key constraints we can finally address data integrity between GIS features and attributes stored in Oracle. An examination of SDE capabilities provides an organized approach and may reveal more benefits. In the discussion I'll point out when a particular capability requires use of the Open API for the reader.
With the variations in APIs offered with SDE, and the distributed capabilities for data and processes supported by Oracle, the options for distributing processing are limited only by your imagination, skills and computer network. However, keep in mind that you must develop an interface to the API before you may begin to realize any benefits for an Oracle application.
The benefits of putting the features in the DBMS are the same as putting anything else in a DBMS:
Referential integrity can be enforced through the use of database constraints
Multi-user data access is managed by the DBMS.
One that might not be too obvious, except to the GIS/Oracle developer who has had to do it the hard way, is the benefit of having all of the attributes from the feature attribute table right there without lifting a finger. Area, length, perimeter, they are all there and maintained anytime the GIS data is edited.
SDE provides many operations that act as filters in a query. Examples are equals, touches, within, contains, crosses, overlaps and disjoint. The only way to take advantage of these operations from Oracle is through the Open API. Another alternative to this would be to go all out and implement Oracle Spatial as the back end data store for SDE. Doing that would provide similar spatial searches such as, disjoint, touch, overlapbydisjoint, overlapbyintersect, equals, contains, covers, inside, covered by, anyinteract and within_distance.
Feature manipulation is how the SDE documentation refers to overlay operations. The operations are intersect, difference, symmetrical difference, union and clip. Union stands out the most in my mind since it provides the matrix relationship that is so often modeled in natural resource database applications. As with the spatial search capabilities, the Open API is required. Would feature manipulation really be a desirable addition to an Oracle application? How would it be any better than using Arc/INFO for the same purpose? Real time updates to matrix tables? Elimination of matrix tables by way of on-the-fly generation of temporary matrices? These are some of the questions that require hands on exploration of SDE.
Shapes are verified using a number of rules such as polygons must close, sequential duplicate points are not allowed, and area and perimeter must be calculated. All ArcSDE commands that create or update shapes follow the rules, and to issue ArcSDE commands from an Oracle application requires the Open API.
SDE provides two methods for managing transactions, versioning, also known as long transactions support, and record locking or short transaction support. Both are methods of managing multiple user access.
Versioning in SDE allows multiple images, or versions, to be developed from a layer. An example of why this would be desired is in planning. Multiple alternatives can be developed on separate versions of an SDE layer. The original version is known as the "default" version. Edits made on a version do not affect the default version. However, later on, when an alternative is chosen it can be incorporated (merged) back into the default version. SDE assists with the reconciliation process pointing out any differences between the versions. The user is responsible for deciding which version is the correct one.
Record locking is provided at multiple levels allowing an entire table or specific rows to be locked. Either a spatial row selection method, such as all features within a specific boundary or non-spatial row selection may be used to determine which rows are locked.
Of the two mechanisms for managing multi-user access, locking is the most straight forward. It should provide a direct benefit to an Oracle application. However, versioning complicates the picture. Versioning is implemented in SDE, and is not visible to Oracle applications relying on native DBMS capabilities. The only way an Oracle application may take advantage of this capability is through custom programming. Since the user interface for editing the textual data in an Oracle application is an Oracle form, and since Oracle does not support SDE versioning, the forms can not be used to edit any versions textual data other than the default versions. The application must specifically address versioning. This involves access through the Open API. Alternatively, if the benefits of versioning are desired, consider custom form development in Visual Basic for only those data needed to support the versioning requirements of the application.
SDE can create and maintain layers using Oracle Spatial as the back end data store. This has already been mentioned but it is worth mentioning again. As time goes on the importance of this capability may diminish. What makes the use of Oracle Spatial as the back end data store a worthwhile endeavor, as opposed to not using Oracle Spatial at all, is the difference in capabilities that it presents and the types of capabilities required by a given application. Six months ago, before I knew anything about the Java API, or the possibility of an SQL API, I believed Oracle Spatial as the back end data store was a viable, and possibly the most palatable solution to gain spatial capabilities in an Oracle application. I would have argued that Oracle Spatial functions were vendor integrated while of SDE functions were not. However, an Open SQL API might change that. Availability of a third party PL/SQL interface to the Java API or C API could also be a possibility.
Another way to assess the role of SDE in an Oracle application is in regard to how it might fulfill specific requirements. The list of requirements presented here is certainly not an exhaustive list. It is, however, a list of the most common requirements encountered. As I describe the requirements I will do it in what I consider to be the logical order in which they should be pursued.
The requirements specifically address the needs which Oracle in general, and especially Oracle Forms can not meet. They include: Managing the link to GIS in Oracle and the link to Oracle in GIS, including maintenance of GIS data stored in Oracle for reporting purposes, i.e. area, length, TSR, etc.; spatial display and query; and maps with reports.
So far the approach to dealing with these requirements has been to augment our Oracle application with GIS capabilities as needed in via integration of capabilities from Arc/INFO 7.2.1 and ArcView 3.1.
The following figure illustrates the relative magnitude of development tasks to meet the requirements. The graph characterizes timing and magnitude. The longer the greens bar the higher the magnitude. Time advances from left to right.
Maintaining the link refers to two things; managing the relationship between GIS features in coverage and attributes in a table; and, managing the GIS derived data stored in the database such as area, length and perimeter. The approach used today is to maintain two key items. One key we refer to as the intelligent key. These are user-generated. The other is a system-generated key. The user manages any edits to a feature in coverage that would alter its relationship to its record in the database by way of the intelligent id. The user simply assigns the same values to a feature and a record. Then, when they are ready to establish the system-generated key an Arc/INFO AML is run and the system-generated keys are populated. The AML pulls Oracle generated keys from the table and puts them in the feature attribute table. It also pulls the derived information from the coverage and stores it in Oracle.
Implementing SDE would completely eliminate the need for this synchronization process.
This is the most basic necessity. The user wants to edit a record in the database and they can see the feature on the map. We need to let them select the record for editing in the form by pointing to the map. We also need to be able to verify the current record being edited. The user may select several records for a session and as they edit each one they want to visually verify the current record prior to changing the database.
Our current method incorporates an inter-application communication link between ArcView and Oracle forms. The link essentially synchronizes ArcView with a form causing the same records selected in ArcView to display in the form and visa-versa. The biggest problem with this approach is the way ArcView accesses data. Distributed ArcView processes clobber the network when data is centrally located. Every time the display is redrawn entire coverages are accessed, regardless of the viewing extent. We use ArcView heavily and will be linking and joining tables from Oracle. Accessing tables from Oracle, at least in the current release of ArcView, results in large temporary dbase files. SDE would eliminate most of the network traffic, and eliminate the temporary dbase files resulting in enormous performance gains. In this case I would make the stretch to consider SDE playing a role beneficial to an Oracle application, albeit an indirect role.
To the dismay of the GIS community, the popular approach to spatial correlation is to have the user it in input forms as records are updated. Correlation is used heavily in reports and ad-hoc queries.
SDE's feature manipulation capabilities allow us to spatially cross-reference data either on a periodic or as needed basis. I would consider this an enhancement over what could be done without SDE, using Arc/INFO. Using Arc/INFO alone would require the creation of a new coverage using the union overlay command followed by some dbms commands to update a cross reference table in Oracle. By using SDE for that function the results would already be in Oracle, although some additional processing might still be required to populate a cross reference table that contains multiple cross references. Overall the gains may be negligible.
The highly normalized structures present in the Forest Service's natural resource databases creates a problem, even for the most skilled SQL users. We need to provide simplified access to the users to make access to the "corporate" data practical. GIS, being a natural integrator of information is a good place to do that.
Our current approach is two pronged. One method is to develop database views that de-normalize, or flatten, the database structure. Another method is automated theme generation for ArcView based on pre-defined theme properties stored in the database.
SDE will not play a direct role in satisfying this requirement. However, as mentioned above in spatial query and display, it will enhance performance significantly.
The last of the typical requirements is Multi-user editing. As I said previously, the requirements are listed in the order in which I believe they should be pursued. Multi-user editing is last on the list for two reasons, the first being that the current, somewhat informal processes used by the GIS community are meeting the requirements, the second being that this is by far the most complex requirement.
SDE plays a large role in meeting this requirement. It is not the only dog in town. Transactional libraries and ArcStorm can contribute towards meeting the requirements. SDE takes it a couple of steps further with its support for versioning.
SDE can play a significant role in an Oracle application.
The direct benefits, which should not require additional application development to implement, are improved data integrity, improved database maintenance, dramatic performance improvements, and multi-user editing support.
Data integrity is improved by providing an opportunity to establish referential constraints between GIS features and related Oracle tables.
Database maintenance is enhanced in terms of GIS derived data such as area, length and perimeter. All are maintained as part of the normally daily GIS feature maintenance.
Performance of ArcView, which is relied upon heavily to provide the user interface for viewing GIS data, is dramatically improved.
Multi-user editing support is dramatically improved.
In the figure above I've recapped SDE in the benefits it provides for the hypothetical requirements. Green check marks indicate participation in that role. A red X indicates that SDE does not play a direct role. Notice the decreases in magnitude for maintaining the "GIS link" and for "Multi-User Editing" indicating a relative benefit of SDE implementation on those tasks.
Of course, further benefits could be realized by developing and implementing a PL/SQL wrapper to one of the Open APIs in the application.
Tony Bacon, tony.bacon@aris.com
Senior Consultant, ARIS Corporation, www.aris.com