Agriculture Western Australia
3Baron-Hay Court South Perth Western Australia 6151
Phone 9368 3963 Fax 9368 3939
2Agriculture Protection Program, Information Co-ordinator
Agriculture Western Australia
3Baron-Hay Court South Perth Western Australia 6151
Phone 9368 3453 Fax 9474 2479
3Manager, Spatial Resourses information Group
Agriculture Western Australia
3Baron-Hay Court South Perth Western Australia 6151
Phone 9368 3453 Fax 9474 2479
Agriculture W.A., through the Agricultural Protection Program (APP), is currently undertaking a program to integrate the agency’s information systems relating to events that occur in agricultural properties. The Client/Property/Event Project (CPE) will reduce duplication, that has developed over the years of operation, in Agriculture Western Australia’s computer systems and implement the latest developments in information technology and service delivery. The Database of the CPE system will eventually contain the vast majority of information on the agency’s activities on agricultural properties, both current and historical. The CPE project also plans to include data from other government department, required by Agriculture Western Australia, in the performance of its business activities.
As part of a long term strategic objective, embraced by the APP, of online delivery of government information and services to the agricultural community, certain areas of this information are being made available via easily accessible, reliable and cost effective delivery systems. The provision of such systems is beyond the expertise and financial capabilities of community group or landholders as the required information is held by a wide variety of sources, each with their own unique systems.
This paper discusses the requirements, philosophies and methodologies used in the development of this major project
The development of a Client Property Event (CPE) System was identified in the findings of the “Information Requirements and Systems Review”, commonly known as the “Platinum Report” produced by Platinum Technology for the Agricultural Protection Board, dated January 19981.
The Platinum Report’s Implementation Plan recommended the design and development of a Client, Property Event System. This is a repository for all Client and Property related event information that has potential relevance across the Agriculture Protection Program (APP) and Agriculture Western Australia.”
The development of the CPE System commenced in financial year 2000/2001with the production of a proof of concept demonstration system.
The CPE Project has always been seen as one of a number of core systems within the Agency. There are some 40 current APP applications that will to be migrated into the CPE system that have functionality duplicated in other Agency core systems. The direction taken in CPE development has been to assume that this duplication should be handled by these other core systems. i.e. Invoicing to FMIS system, Staff information from HR system etc.
Because of the requirement for the interaction of systems within the agency it is not possible to treat the requirements of any CPE Applications in isolation from those of other core systems currently being implemented or in use in the Agency.
The project’s aim is to develop an integrated data model and provide access to this information through a Web based query facility, displaying this information for clients using a standard PC web browser.
Apart from a continuation of the integration of the APP’s existing systems, other thrust of this project will cover:-
- Developing Web based linkages between similar systems providing information on distribution, identification, and control, both intra and interstate.
- Allowing community groups and individuals to input collected information into the System.
Apart from the normal textural based applications, that the APP has in operation, there are requirements for the generation of graphical reports, such as thematic map and charts, from the information held in the system.
The technology and systems used in the provision of the services will provide a central point at which information can be retrieved and stored as well as being able to point proponents in relevant directions for the ready sharing of information.
The rationalisation of Agency Systems to produce a single system for Clients and Properties is to eliminate multiple references to the same information held in the various datebase within the Agency and setup corporate datasets for Client and Property records.
Industry standard software, Oracle Version 8 Database Management System and Integraph’s Geomedia GIS system are the base of the CPE System.
The CPE system has, from its outset, been designed to be Web based. This will require minimal expenditure on user software requirements, as only a Web Browser such as Internet Explorer or Netscape is required. It will reduce commissioning costs and reduce the implementation time for new applications, as no work will need to be performed on user systems. All software required in running the system, is down-loaded and setup automatically when the user logs onto the web site.
The CPE Project has always been seen as just one of a number of core systems within the Agency. There are a number of current APP applications that will to be migrated into the CPE system that have functionality duplicated in other Agency core systems. The direction taken in CPE development has been to assume that this duplication should be handled by these other core systems. i.e. Invoicing to FMIS system, Staff information from HR system
The interaction between the different databases allows for the delivery of a wide variety of reports that cover the full requirements of Agriculture Western Australia.
The Client Property System (AgWest Client)
The Client and Property information forms the core of the CPE system. The AgWest Clients system consists of land parcel, property and client information, that is used as a common client base in a number of systems within the agency. It is linked to a spatial cadastral database of the state, obtained as ASCII files from the Western Australian Department of Land Administration (DOLA) land title data. This database has existed for some time as a traditional GIS system with client attribute data held in Oracle tables and spatial data held in Microstation design files.
This project includes the conversion of the system to be stored entirely within the database, using Oracle Spatial Cartridge to hold all graphics data and will be discussed later.
The basis of the Agwest Clients system is outlined in figure 2.
It consists of a number of tables for Clients, Addresses, Paddock, Enterprise, Properties and Parcels.
Clients – A Persons or organisations that have contact with Agriculture Western Australia, including individuals, Partnerships, Companies and Trading entities.
Parcel – A Titled or leased area of land based on the DOLA Cadastral information.
Paddock – An area of land on interest that cannot be defined as by the DOLA Cadastral information.
Property – An entity on or to which a event takes place. Normally is 1 or many contiguous parcels or paddocks managed as a single unit. It can also be an item unrelated to land, such as a shipping container, stock carrier.
Enterprise- A grouping of properties which are managed as one overall enterprise.
These major tables are bound together via association tables which hold the information about the time frame for which the association is valid, thus allowing a history for all the relevant records in the tables
Spatial and Textural data are linked via unique Property, Parcel and Paddock ID’s.
This allows other spatial datasets outside the bounds of the CPE project to be linked to textural information held within.
The Event System
Because of the requirement for the interaction of systems, it would not be possible to treat the requirements of CPE Applications in isolation from those of other systems in use or currently being implemented within Agriculture Western Australia. Because of these requirements, the system is been built using a number of generic modules that carry out the basic requirements of particular event functions, such as inspections, registrations, movements and certifications. New modules can be easily attached to carry out other requirements, as required.
The base modules are used as components of specific applications and thus allow an
easy and cost effective method of generation for new applications, as they arise.
The Event in some cases, will generate spatial information that will need to be store. To this end an Event Spatial object dataset has had to be created, linked to the Event ID of the Textural database.
An Example to show the capabilities of the CPE concept, is a requirement that Agriculture Western Australia has to monitor and maintain information on non-indigenous vertebrate animal species (EVS system). The EVS Systems has a number of component, registration of Breeders, inspection of containment sites and certification of individuals or organisations to keep these animals, as well as its own unique requirements (See Fig 3).
Field Recording System
Laboratory Info. Management System
Exotic Vertebrate System
Financial Management Info. system
Stock Identification and Registration
WA Quarantine Inspection Service
Pesticide Residue System
Apart from events other applications can tap into the Client and Property tables.
This allows such applications as an agency wide mailing list system in which duplication of data is kept to minimum.
GIS Approach used in CPE project
Large amounts of digital spatial data have been collected using Geographic Information Systems. These typically hold graphics data in flat files, known variously as design files or shape files, while related attributes are held in a relational database such as Oracle.
The only access to this data is using a GIS application, imposing cost and expertise limitations. Distribution and maintenance of this data has often been by copying these files, with potential data consistency problems. With large data sets it has been necessary to “tile” the data into sections, such as map sheets, to avoid file size limitations or processing time constraints.
Spatial Data Management
As well as the above-mentioned issues of data consistency and the need to tile datasets, there has been an increasing demand for geographic data to be distributed widely over networks including the Web, and for managed multi-user access. All major GIS vendors are now offering products that allow graphics data to be stored in the database along with the attribute data. This means that all data can be managed by the DataBase Administrator, and query retrieval is from a single source.
API versus Database functions
Various technologies have been introduced to address the management of geographic data within a database. The two main approaches are the spatial middleware approach, such as ESRI’s Spatial Database Engine (SDE) and the database spatial function approach, such as Oracle’s Spatial Cartridge.
The spatial middleware approach involves storing graphics objects as BLOBs and accessing it using an API supplied by the vendor. This has the advantage of being more tightly integrated with the vendor’s GIS, and typically provides support for a wider range of geometric objects. Disadvantages include proprietary data formats, the limitations of tables with LONG columns and the need for specialist programming skills not always present in a database development team.
Oracle’s Spatial Cartridge stores the coordinate data in normal numeric columns, allowing access from any SQL statement. The geographic functions are available in database packages. The main advantages are that the data can be loaded, managed and queried exactly as any other data, by anyone connected to Oracle. The SDO/SC format is supported by various GIS vendors, including ESRI and Intergraph. The main disadvantages are that integration with GIS front-end products requires some work, and only limited geometry types are available.
Oracle Spatial Cartridge
Spatial Cartridge Components
Spatial Cartridge does not provide spatial visualisation or analytic functionality as GIS software does. To view or map SC data it will still be necessary to have a GIS product. But SC provides a data format and a set of packaged functions to store and query the spatial data.
The primitive elements handled are 2-D points, line strings and polygons. Geometric objects are collections of elements, e.g. a polygon within a polygon represents a hole, multiple lines represent a line string.
A layer is a collection of geometric objects with the same attributes, e.g. PARCEL_POLY is the set of polygon geometric objects that represent parcels.
This requires four tables:
The first two of these represent meta data. The third contains the coordinate data with a single numeric key value. The number of coordinate pairs is customisable by layer. The fourth table contains the spatial index.
Indexing is by tiling, i.e. binary decomposition of the object space into tiles known as HHCODEs. These use the quad-tree concept used in other spatial engines. Within the SC, the level of tiling is a user-definable parameter for each layer.
Spatial Indexing Diagram
In the above example, the first tessellation (tiling) consists of four tiles, the tile groups A,B,C,D. All four interact with the geometry shown above. On further tiling into 16 smaller tiles, only 8 of them interact with the geometry. There is a tradeoff between less tiles or finer granularity. Choosing an appropriate tiling is an important tuning exercise.
This is a two-tier process. The primary filter is a coarse fitting process that quickly determines tiles in common between the data layer and the window (fence) layer. The secondary filter applies exact computational geometry to return the exact elements that meet the condition queried.
Spatial Query Process
- join LAYER and WINDOW spatial INDEX
- get LAYER rows with HHCODE in common
- check for returned LAYER rows that interact with WINDOW
- get result set (can apply other criteria)
The two steps would be separate in procedural code, but can be combined in one step in SQL.
The following example from the AgWest Clients project selects the parcel and property IDs for all parcels that intersect another layer (‘fence’), defined separately.
select sdo_gid, parcel_id
from (select distinct a.sdo_gid gid1
from property_poly_sdoindex a,
where b.sdo_gid = 1
where sdo_gid = gid1
The initial objectives of the AgWest Clients project to be addressed by SC were:
- to build a complete set of cadastral graphics data to allow extraction of line work on request;
- to speed up extracting data from a rectangular window (possible but slow with SQL between command);
- to extract data from arbitrary areas such as LGAs and districts. This is not possible using SQL between command;
- to extract graphics data and simultaneously apply attribute selection, including joined tables;
- to do point in polygon and simple overlay operations within the database without tying up GIS resources particularly where output required is text only;
- to build spatial warehouses using GeoMedia to speed up data access and analysis, and to publish maps on the Web without the need for graphic files;
The first version of SDO with adequate functionality for this project is that with Oracle 7.3.3. Curiously, the functionality differences from this version to SC with Oracle 8 are not great, though there are significantly greater tuning options for large datasets with Oracle 8 and SC. This project was commenced with SDO 7.3.3 and migrated to SC 8.0.5.
SDO/SC is a relatively new product. It is extensively used by GIS vendors but not very widely used in Oracle database sites. Consequently the product is reasonably mature and stable but there are problems getting information and support. In this project, most of the problems encountered related to restrictions that were not adequately documented but had relatively easy workarounds. Finding the answer was sometimes difficult and on one occasion we had to go to some lengths to escalate the problem before getting the answer from the product development team.
The main elements of AgWest Clients are parcel-related tables and property-related data. Parcels contain land title data, essentially DOLA data. A property is a collection of parcels that make up an enterprise managed as one unit, essentially an Agriculture WA client.
The base data consists of NORM files (a set of fixed format ASCII files containing details from DOLA’s cadastral database), loaded into tables that are virtually replicas of the NORM files.
The line work is then loaded into the SC layer PARBDY using an Oracle PL/SQL script. This layer contains line strings, 2 ordinate pairs per row, as most linework in the NORM data is simple single segment lines. All arcs are stroked into line strings at five degree intervals, as arcs are not supported in either SC or MGE, the most commonly used GIS product in Agriculture WA. This layer is an intermediate data set, used to extract line work for creation of traditional graphics files (design files) and for the creation of the parcel polygons described next.
The PARCEL_POLY layer contains simple polygons, 8 ordinate pairs per row, so most parcels are contained in 1 row. The key is the parcel ID, a generated sequential identifier. There are over 1 million rows.
The PROPERTY_POLY layer contains compound polygons, with the coordinate data for each parcel stored again as part of the property it belongs to. This represents duplication of the coordinate data, but the data is maintained entirely by triggers so no integrity issues arise.
The following times are from the previous 2 processor Pentium server running Windows NT, and would be much less on the current 4 processor Xeon. It took about 23 hours to create the PARCEL_POLY layer. Creating the spatial index on 100,000 records took 1 hour, but validating the geometry of 10,000 parcels took 6 hours.
This shows that geometric operations such as validation of topology or exact spatial overlays are quite time consuming. Spatial indexing, and querying based on it, is very fast. This means that it is important to tune the tiling level to make sure not too many excess polygons are qualified by the first pass filter. For parcel data the ideal for metropolitan data would be smaller tiles but for pastoral areas much larger tiles. While variable tiling is possible it is not recommended, so a compromise tiling of 12 was chosen. This represents 12 binary decompositions of the whole world, or grids about 5Km square (not really square).
One of the main advantages of using SC is the data management advantages of having all data held within the database. This removes the temptation for analysts to clone datasets and make modifications that can get out of synch. Additionally all backup, recovery and update concurrency can be controlled with the database technology used for all other non-graphics data.
For large projects like AgWest Clients, it enables the whole dataset to be handled as a single continuous theme. In previous versions of AgWest Clients, the graphics data was broken up by LGA to make files that could be handled by the GIS. The work involved in doing this was significant, and it complicated the maintenance of the data, particularly on the boundaries of data subsets.
In the past, updates to the parcel data have been made by getting a complete set of the cadastral data. The logistics of delivering this to users was enormous, especially as the process of matching parcels to properties extended over many months. In the latest design the data model allows for timeslices, with currency flags to allow phased implementation one area at a time. This means that new cadastre can be obtained for any area desired, made available to specific functions to allow matching to occur while other users see the previously completed production graphics. Once the new area has been fully matched, and the relevant SC layers updated, it can be implemented simply by setting a status flag.
With Oracle 8 partitioning the timeslice hardly adds a performance overhead as historical, current and in-progress parcel data are organised into separate partitions.
Integration with GIS
Agriculture WA uses the Intergraph products MGE, Geo Media and Web Map. Geo Media is designed to work with Spatial Cartridge and it can be used to translate layers between SC and MGE and vice versa.
Geo Media has its own meta data tables. It maintains these automatically for features created within the product. If a layer is created directly in Oracle it can be made accessible to GeoMedia, but this requires some extra steps to get the meta data right. It is quite a simple process if you know how, but tricky if you don’t.
The basic principle is to create the SC layer tables with a dummy element in a warehouse (an Oracle account with GeoMedia meta data already created) with the correct coordinate system. After using the gdoo.Create_GDOMetadata procedure, the layer could then be exported to the target account (warehouse) using GeoMedia and would have the correct columns, meta data and attributes. The tablespace and storage parameters are not picked up, so large tables would need to be altered and indexes re-created after this step.
The Spatial Cartridge geographic layers are accessible through the Agency’s intranet using Intergraphs’s GeoMedia Web Map product. This means anyone with a Web browser can view the data and generate their own user defined maps over the Web. A Web page has been set up to query parcel attributes such as owner name or land title details and return maps, or to click on displayed maps to return parcel reports.
The map generation and retrieval over the Web is faster when querying SC features than when using the traditional Microstation design file features.
Geographic Functions in SQL
To enable user-defined functions in SQL, a set of geographical functions have been created in a package. One returns the LGA for a point specified by a latitude and longitude. A point in polygon operation determines which geometry in the LGA layer contains this point. It can be used for ad-hoc queries or to set a field in a table, including a derived field maintained by a trigger. This removes the need for users to enter codes, or for manual GIS operations.
Other functions include conversions between AMG coordinates and latitude/longitude. These are even available remotely from the Agency’s production database instance. When building such functions it is important to use PRAGMA RESTRICT_REFERENCES and avoid package variables if the function is to be used remotely.
Spatial Merge Operations
Spatial Cartridge allows creation of polygons, lines and points from coordinate data and a wide range of spatial queries such as intersects or contains. It does not allow the creation of derived geometric objects such as intersections or merged polygons with dissolved boundaries.
In the past, AgWest Clients property boundaries were created by using the GIS to dissolve common boundaries between adjacent parcels assigned to the same property.
In this version, properties are created as a compound geometry consisting of the parcel polygons that make up the property. In some ways this is better as the relationship to the parcels is more obvious. The internal boundaries can still be seen, but can be hidden by appropriate choice of fill or boundary colour. The component polygons are selected as a single object and it handles non-contiguous parcels in a way that the previous system didn’t.
The primary advantage is data management and processing. The creation of the properties can be done by a PL/SQL procedure without any GIS processing. Building property data on about half the agricultural properties in the State was done in a single overnight run of a script. The equivalent process in the original AgWest Clients system took a GIS specialist several weeks to complete.
Triggered Spatial Updates
Matching of the parcels to Agriculture WA’s properties is an on-going process driven by district office field personnel reporting changes from client contacts. These are entered into a parcel_properties intersection entity that defines this relationship in the context of a cadastral timeslice. A database trigger on this table builds the property geometries from parcel polygons. The graphics layer for the in-progress timeslice is thus kept continuously up to date without any GIS processing.
The creation of the parcel data is currently an occasional bulk update process. If incremental updates were available it would be possible to automate much of this, though it would be necessary to cater for invalid geometries. Similarly, other processing, such as setting derived fields, could be automated, e.g. setting the LGA, catchment or soil type of a record for which the coordinates are known.
The use of Oracle Spatial Cartridge to handle the graphics data for the AgWest Clients project has been an outstanding success. The work in setting it up was greater than anticipated, as the product is fairly new. As the project progressed opportunities were identified that had not been considered in the original objectives. In particular, the ability to automate the property parcel merging operation. The seamless integration of multiple cadastral timeslices over the whole State and the triggered maintenance of graphics datasets from data entry actions has revolutionsed the traditional GIS processing used in this system in the past.
Spatial Cartridge is a stable product that brings great efficiencies to the management of spatial data. The ability to carry out so many operations in simple SQL statements or PL/SQL procedures is the greatest gain over traditional GIS processing or API based spatial middleware.
A possible limitation is the restricted set of geometric objects and operations that the product handles. In the case of AgWest Clients the data types and operations available satisfied the project requirements. Most requirements could be catered for by work-arounds such as the handling of merged polygons and arcs as described in this paper. The latter is being addressed by some vendors offering extra types using Oracle 8 objects.
Spatial Cartridge comes into its own where geographic queries and processing are to be made available to the wider database user community, without the need for LONG data, API processing or GIS application tools.
1 Information Requirements and System Review release 1.0, Platinum Technology, January 1998