The process of reengineering the system for the management and tracking of a county's land records is no small task, and while there are highly sophisticated tools available to assist in this challenge, integrating them into a single system requires a thorough understanding of the business rules and the software being utilized to implement the system. This paper presents the results of PlanGraphics experiences with Chester County Pennsylvania's efforts to establish such a system and cites the experiences gained with ArcStorm, ArcTools, and Oracle in developing a pilot parcel and planimetric data base in a distributed computing environment. A discussion of ArcStorm's feature locking and Oracle's integrity constraint capabilities and their impacts on the process of developing and maintaining the data base will be presented.
The transfer of real property often involves several departments handling multiple documents in a time effective manner. Deeds, surveys and subdivision plats are processed by planning, assessment and recorder offices both sequentially and simultaneously, and in some instances under critical time constraints. The accuracy of the information entered is essential and the relationships amongst the documents can be very complex. A property transfer such as a large subdivision can involve several deeds with several owners each, multiple tax maps and multiple individuals accessing or editing any of these records at a particular time. Therefore, the system developed must posses tools for managing concurrent access, insuring the integrity of the data entered and be capable of distributing the database across a wide area network.
A comprehensive land records system contains an abundance of geographic data from several themes, spanning hundreds of mapsheets and has extensive attributes associated with it. However, for efficient management of the data, the system must provide rapid access to the data, be able to group these spatial data from small discrete areas into a large seamless database, and coordinate changes to the geographic features with their related attributes. The system must provide the ability to lock individual features for updating, avoiding the unnecessary locking of features not involved in the transaction. Also, because of the dynamic nature of the data, the system developed must provide tools to maintain database consistency, recover from incomplete transactions, and provide the ability to view the data form any point in the databases history. Considering the number of users of the database, the system also needs safeguards against accidental or intentional altering of the database schema.
The Setting
Three departments within Chester County government, Computing and Information Services(DCIS), Office of Recorder of Deeds(RCD) and the Bureau of Land Records(BLR) each have a responsibility for maintaining some portion of the County's land records. Chester County is currently experiencing a significant growth in new development and change (50+ subdivisions/month), resulting in a serious record keeping challenge for all land recording activities, such as deed, assessment information and tax map maintenance. Keeping these property records up to date and in synch required the implementation of a new and comprehensive automated system.
To enable the development of this automated system, the County initiated a document conversion effort for all tax maps, acquisition of digital orthophotography, development of a planimetric base, and the migration of legacy databases to an RDBMS. Also, in an effort to minimize redundancies in data collection and processing, the flow of information and the role of those involved is being reengineered. Information that was formerly captured and processed several times will now be captured once and made available to all participants.
In addition, the County is implementing a geographically based parcel identifier as a method of assigning a simpler yet unique identifier to each parcel. Add to this the sharing of the responsibility for this project amongst three departments and you have a full plate for all involved.
The Solution
Recognizing the spatial component of the land records management problem and the spatial tools possessed by GIS, the County decided to implement a GIS to handle the management and maintenance of its spatial data. ArcInfo running on a network of RS6000 workstations was chosen as the GIS solution to the land records management system.
As important as the responsibility for managing the spatial information, was the management of the attributes of the spatial information. Attributes such as property ownership, location and dates of transactions are critical to the responsibilities of the participants and it is necessary that they be current and correct. To insure that these criteria were met as well as making the data available to all departments county wide, and easily accessible to the GIS, the county decided to implement an RDBMS on the same network of RS6000 workstations as the GIS. Oracle's Oracle 7, SQL*PLUS, SQL*NET and Forms were chosen as the RDBMS solutions to the land records management project.
To coordinate the spatial data with the attribute data during the database maintenance and update phases as well as simplify these processes, ArcStorm, Esri's spatial data management system, was chosen. ArcStorm is a set of tools for managing the databases integrity, distribution and history, as well as enabling the presentation of the database as continuous and seamless.
Implementation
The two major components of the Land Records System are the Application Tracking and Property Update sub-systems. The Application Tracking sub-system is being developed in entirely in Oracle and is used to track the progress of various property transactions, such as subdivisions, parcel combines and simple transfers, to cite just a few. It tracks the application from its inception to completion and contains applicant, owner, property and billing information and transaction dates.
The Property Update sub-system has been developed for updating the various map layers and their associated attributes involved in the property transfers. It has been developed using a combination of ArcInfo, ArcStorm, Database Integrator, and Oracle. The remainder of this discussion focuses on the Property Update (PUP) component of the Chester County Land Records System.
Property Update Prototype
The Property Update Prototype (PUP) application was developed using ArcInfo v7.0.3 and Oracle v7.2.2. ArcInfo's editing module ArcEdit with the COGO option and Oracle's DML and DDL were the primary products used for the application. Much of its spatial functionality was borrowed from ArcInfo's Edit Tools, which were modified for the Land Records project's particular needs. PUP utilizes ArcStorm and Oracle for their data management and coordination capabilities. ArcStorm and Oracle are essential in managing the integrity of the database, and simplifying its access.
Many of PUP's functions are standard editing capabilities, such as arc, polygon and label editing. However, ArcStorm and Oracle contribute many additional capabilities that are not available through Arcedit alone, nor through Librarian, ArcInfo's map filing system. Some of these essential capabilities are listed here.
Database Continuity
When selecting a feature to edit, users no longer have to worry about which coverage or coverages they need for accessing that parcel. Once the individual parcel coverages have been joined and loaded into ArcStorm, the features can be selected using graphic selection tools, Figure#2, or by an attribute selection, Figure #3.
Figure 2
Figure 3
The fact that the one parcel originated from two coverages, Figure #4, does not affect the operation, Figure #5.
Figure 4
Figure 5
Feature Level Locking
To prevent concurrent editing of features and rows, ArcStorm supports feature level locking. Traditionally when a feature needed editing, all of the features in the geographic area of that feature also needed to be made available for editing. Due to this coverage level locking , a large number of features were unnecessarily off limits to other editing activities. Now with ArcStorm's feature level locking, there is a substantial reduction in the number of unnecessarily locked features during the update procedure. Also, the feature is truly locked. No other processes can alter that feature. Through the use of the LOCK__ID item, one of four items ArcStorm requires a coverage to have for entry, AS knows that feature is currently in use. This locking is enabled by the process of checking the features out of the database, which creates temporary local coverage and copies of attribute tables.
Oracle tables are also locked through the use of a LOCK__ID field, if they are registered with ArcStorm. The use of the LOCK__ID is the same for the Oracle row as it is for an Arc feature. The major difference with the Oracle row is that Oracle does not automatically check for the presence of this field, nor its contents, and therefore an Oracle application can mistakenly alter its contents even though it may be involved in another transaction. This project will implement Oracle locking of individual rows through conditions in the procedures that access the Oracle tables by checking the contents of LOCK__ID.
Density Dependent Tiling Scheme
To expedite the database search process to find features of interest, a transparent tiling scheme is utilized by ArcStorm. This is a rectangular scheme that is created by the user in one of two ways. Either the user specifies the number of tiles they want or they specify the maximum number of features they want per tile. In an example of the former case the user would specify the they want 16 tiles. ArcStorm would then create a 4x4 grid of equal size tiles for the library, Figure 6. This tiling method would be acceptable if the library contained layers of features that are uniformly distributed.
Figure 6
In the latter case, the user would specify that they want no more than 500 features per tile and ArcStorm would generate a grid of rectangles of varying sizes, each one containing fewer than the number specified, Figure 7. Figures 8,9, and 10 portray tiles of 1000, 5000, and 10000 features respectively. This tiling method is preferable if the layers in the library had a clustered distribution. To generate this type of tiling scheme, the user needs a point coverage to use as an indicator of feature distribution and density.
Figure 7
Figure 8
Figure 9
Figure 10
For many users a coverage that accurately portrays this distribution is not available until the source documents are completely converted. If the database is needed prior to completion of the conversion process, the user can substitute an indicator coverage. For the initial Land Records database, a point coverage generated from the nodes of the TIGER streets coverage was used as a substitute indicator of feature density and distribution.
A test of three tiling schemes was conducted to determine the performance associated with each in selecting, checking out and committing a single parcel from a 14,000 parcel pilot database. The storage overhead associated with each scheme was also determined, Figure 11.
Tiling Scheme Performance *
Selection Storage
Time Check Out Check In Overhead
# of Features/Tile 10,000 :01 sec :26 sec 1:1 350%
5,000 :01 sec :21 sec 1:18 85%
1,000 :01 sec :27 sec :50 420%
500 :01 sec :37 sec :51 650%
Figure 11
* No relates, ArcStorm server and client on same machine, and data mounted locally. Pilot database of 1400 parcels.
Unified Transactions
Updates to features and their associated rows occur within the same transaction, thereby insuring that the spatial features are always consistent with their attributes. Should a problem occur upon check in, and it fails to complete, the original data is still intact. No changes are made unless the transaction completes successfully. Updates are made by checking the features and related rows out of the database, editing them, and then checking them back in, or what is more commonly known as committing them to the database. The process of checking the features out creates a temporary coverage and temporary tables for the attributes. The edits are posted to these files and do not become permanent until the data is committed.
Integrity Constraints
Oracle performs checks on the data being posted to the attribute tables to insure that the values being entered are unique for key fields and are valid for categorical data. It also checks to make sure that parent child relationships are maintained. For example if one table relates to another table and they are related by a primary key, then for every row in the child table there must be a row in the parent table. Any attempt to add or delete a row from one without the other will result in an error. ArcStorm checks incoming features to insure that they are within a certain geographic area and also checks that new features topologically match existing features, i.e. that arcs do not intersect without a node being present. Another useful feature is currency checking. Through the use of timestamps, Oracle can check whether a feature is expired or not yet activated. ArcStorm through the use of the TRANSACTION__ID insures that features id is not higher than the current id in the GENERATOR table.
Transaction Auditing
Utilizing ArcStorm's TRANSACTION__ID and Oracle's EVENT ID, the history of the database and individual features is maintained, enabling the development of historical views and the tracing of changes to the database. All changes to the database are stored in a Weather Service log. The entries include the type, date and originator of the transaction. This feature enables the user to quickly determine the existence and date of an editing session and whom it was performed by. This also provides a critical component of a data sets metadata and creates an institutional memory about a data set.
Security
ArcStorm and Oracle both provide protection from unauthorized access of the database. Only owners or designated groups may alter the contents of library features or Oracle tables registered with ArcStorm. Only the ArcStorm database administrator may alter the schema of the database.
SchemaEdit
To manage the database schema, ArcStorm includes SchemaEdit, which provides comprehensive set of database creation, entry, modification and management tools.
Discoveries
With 600+ transactions behind us, we felt it was time to share some of our findings and discoveries about ArcStorm and Oracle. As with any new software product, there were a number of things to learn.
Speed
Like one of the many Nor'easters the Eastern US coast experienced this winter, this storm sometimes moves very slowly. Although it still is significantly faster than dealing with coverages and tables, especially when the area of interest spans several coverages, checking features out of and into the database can take several minutes. The time requirements increase significantly when the client and server are on separate locations on a WAN. In Chester County the Bureau of Land Records and the Department of Computing and Information Services are connected via a T1 line, and instances of commits taking 15-20 minutes were not uncommon when the network was busy. Transactions completed in much less time, however, when the related rows were omitted from the transaction.
Overhead
The amount of disk space required to load the data into ArcStorm varied depending upon the density threshold selected for the tiling schema, Figure 7. As also cited in Figure 7, decreasing the maximum number of features per tile by 95%, increased the storage requirements by 600%. The lack of a significant changes in the access and checkout speeds, raises the question as to whether the penalty in storage space requirements is it worth it given the lack of performance improvement, and even degradation, in checking features out? Another inefficiency discovered is the definition of the PHASE__ID for Oracle rows as an integer data type. These are a 38 byte data type in Oracle and given the function of this field, this much space is unnecessary.
Feature Alteration
Even though the influence of tile boundaries is greatly reduced, they are still evident. Specifically, if a polygon is split by an ArcStorm tile, it receives an additional label point for the new polygon. Also, the original label point is moved. Upon checkout only the original label point is retained, but its location is no longer where originally placed. In this project that presented a serious problem as Parcel Identifier is based upon its geographic location, obtaining its unique value from its xy coordinate. Moving the location rendered the PIN incorrect.
Escalating Locking
When a feature is selected and checked out for editing, any features that share a common feature, are also locked. This is safety feature that prevents inconsistent edits. However if the layer being edited has any extensive features, a large portion of the layer ends up being locked. At the outset of this project, road right of ways were stored in the parcel layer. However if a parcel adjacent to a road, which most are, was checked out, the road right of way for the entire county, and the adjacent parcels, were locked. This basically locked the entire parcel layer from any other edits. It is hoped that the placement of the ROW in a different layer and the addition of breaklines at regular intervals will solve this problem.
Cross Tile Searches
Many times when selecting a feature, a user also wishes to select adjacent features. For instance, if a parcel's boundaries need to be moved, the user needs to make the neighboring parcels part of the checked out set. To accomplish this from ArcEdit, PUP starts an arcplot session and performs an ASELECT ADJACENT. Unfortunately, ASELECT does not support cross tile indexing. This means that features that fall on the other side of an ArcStorm tile boundary fail to get selected. This problem is slated for correction in the next release.
Database Recovery
If a system problem occurs during checkout or checkin, the database is many times left in a state where the transaction can not be released. Specifically if the disk fills up or the server issues an NFS time out warning during check in, the database can be left in a inconsistent state and must be recovered. RECOVERDB the database recovery utility, performed well every time. The problem however is the number of times that we had to use it.
Oracle Procedures
The property update process updates the Oracle database at several points. One example is when an application has been submitted by a developer to subdivide a tract of land, a row is added to an application queue. This event queue is presented to the user of the PUP for selecting an application to work on. When they select an application that row needs to be expired and a new row inserted indicating that the work has begun, by whom and at what time. This will alert the next person that that application has already been assigned, and that they should look for another one.
At the outset of the project a design decision was made to store whatever is going to alter the Oracle tables, in Oracle as procedures. However, upon execution it was discovered that procedures could not be called with a variable as a parameter. Because of the syntax necessary to call the procedure, the variable was never resolved and therefore was passed as a literal. The only way a procedure could be called, was with a constant. This restriction eliminated this option.
The option of using a function was also discovered to be a problem as the Database Integrator requires that it be used in a select statement. As long as the SQL statement did not alter the database, e.g. increment a number, this worked fine. But if the statement alters the database in any way, e.g. updates, inserts or deletes a record, SQLPLUS rejects the statement.
Therefore the only solution was to implement all the updates, inserts and deletes, in AML. Then all of the SQL statements are issued to Oracle via AML's DBMSEXECUTE statement. So far this is an acceptable solution.
Event ID's
If a database is copied from one server to another using the unix cp -r, indices need to be re-created and TRANSACTION__ID's have to be synchronized. Specifically each layer in the library has to have CREATE2DINDEX run on it to re-establish spatial indices, and CREATEINDEX has to be run to create indices on relate items. CREATEINDEX also has to be run on the OBJECT__ID. If the database originated on a machine whose TRANSACTION.ID is higher than the new machine, ArcStorm will not allow it features with a CREATE__ID higher than the current TRANSACTION.ID to be checked out. You can trick it by repeating the checkout until the TRANSACTION.ID is incremented to equal the features CREATE__ID. The ArcStorm cpdb command has yet to be used for this purpose.
Correct Installation
After installing ArcStorm, it is essential to change the ownership to root and modes to 6755 for six ArcStorm commands. Without these changes the subsequent use of these and other ArcStorm commands can result in incomprehensible problems. These changes are documented toward the end of the installation literature, but not in ArcDoc. It would be useful to have them in both locations.
Miscellaneous
Attempting to register an Oracle table with REGISTERDBMS and HISTORY enabled complains that the table does not exist. If you create it first, it indicates that it already exists. ADDHISTORY is the only way to track the history of a DBMS table. The wservice daemon should be started in the directory where you want to store the wservice.log. This log provides a valuable history of events and should be stored in a safe, easy to find location. The draw command in ArcEdit somehow clears the selected features for a library. After redrawing the display, the features need to be selected again.
Conclusions
In spite of the discoveries made about ArcStorm's quirks and limitations, ArcStorm and Oracle represent a quantum leap in database management capability over previous ArcInfo capabilities. By providing the user with the freedom to access any feature in the database, without regard to its geographic or physical location, they relieve them of tedious clerical tasks and simplifies the task of getting the work done. From the work groups perspective, knowing that updates to the database can be conducted without fear of conflicting with someone else's edits, reduces the amount of time and effort necessary to maintain the database. By providing the DBA with the ability to protect the integrity of the database, both in form and content, they can use their time to more effectively concentrate on the other tasks at hand.
Anyone considering developing a large land records database, and not already using ArcStorm and an RDBMS for their system, should give serious consideration to them as tools in developing their system. The amount of time invested in getting through the learning curve, will be more than saved in accessing and managing the system.