A case study of the migration of the NT Heritage Register database from Microsoft Access to an Oracle / Web environment, including the integration with GIS tools and the Integrated Land Information System (ILIS). Describes the benefits and steps performed.
The Heritage Branch within the Department of Natural Resources Environment and the Arts (NRETA) administers the NT Heritage Conservation Act. The Act requires that the Department maintains a publicly available register of heritage places, known as the NT Heritage Register.
At present, the legal Register consists of hard copies of all the legal instruments signed by the Minister over the years, declaring places as heritage places under the Act. The instruments usually include maps or diagrams showing the extent of the registered heritage place. The register is backed by a Microsoft Access database which is used for internal administrative purposes. This database includes all places that have ever been nominated to the Register.
This document will describe the migration of the NT Heritage Register from a file-based Microsoft Access database to a web-based Oracle Spatial system, detailing the steps and benefits of the procedure.
This information can be used as a guide for migrating other file-based databases to an environment where they can be more effectively managed and accessed by a range of applications and users.
NTLIS is happy to consult to developers working in or on behalf of NT Government free of charge regarding available infrastructure and data for such projects.
The NRETA Environment and Heritage group identified a number of requirements above and beyond that which their existing database system could deliver, including the:
- automatic update of the publicly accessible NT Heritage Register web site
- display of heritage listings in the Integrated Land Information System (ILIS) as administrative interests on land parcels
- integration of attached files including scanned documents and photographs, so that they are managed within a single database system
- improvement of data quality through user input validation, particularly address validation against current NT street addresses
- display of heritage places on maps and ability to overlay with existing spatial data layers in a range of mapping systems
- ability for more advanced spatial analysis and reporting of heritage places using specialised GIS (spatial analysis) software
The Northern Territory Land Information System (NTLIS) was engaged to design and build a web-based database maintenance system to address these requirements. The techniques and computer infrastructure used by NTLIS for this project are available to all parties performing this type of work in or on behalf of the NT Government.
NTLIS is responsible for maintaining various online services including ILIS and the NT spatial data infrastructure, and promotes the 'dissemination database' model for storing and delivering spatial data in the NT. The model recommends that data be made available by publishing it to a single reliable and accessible location, typically Oracle Spatial. From there applications and users can be guaranteed to be using up to date data, consistent with its metadata documentation. This model is described in the diagram below, where a range of applications and users are able to see the data stored in the dissemination databases, to a level agreed upon by the data custodians.
Figure 1: Dissemination database model for the NT Government Spatial Data Infrastructure (NTG SDI)
The dissemination database model does not recommend a particular data maintenance tool or technique. However, the process is most effective when data is being maintained directly in the Oracle database, as this affords live access between the data custodians and end users, and reduces the complexity of the system as a whole. In the case of the Heritage Register the data is maintained using web-based forms generated by the Oracle Application Express environment. Oracle Application Express is a free extension to Oracle Database 10g, and provides the standard searching, reporting and validation logic that enables the rapid development of online database applications. Importantly, it stores its data in a transparent way directly to Oracle tables, including storing geometries as proper Oracle SDO geometries, making 3rd party access to the information very easy, assuming authentication is granted.
Steps for migration
The job of migrating the Heritage database involved completing a number of steps, these are described below.
Importantly, NTLIS have developed a guide for data custodians to follow to document their requirements for a new web-enabled database. This guide can be found here:
Examination and adjustment of the existing data model
This step included:
- choosing which fields of the old database to include, their types and their validation rules. For example, some fields needed to be completed only if others had a certain value.
- definition of pick-lists (drop down lists)
- metadata fields for the attached file uploads
Build the maintenance screens
The Heritage Register uses the traditional online database model:
- a login screen
- an initial screen for searching and displaying a summary of all records
- a screen for viewing all information for a record, including a map showing the particular heritage record location, available from the initial summary screen
- a screen for updating a particular heritage record, available from the view screen
- a screen for entering and validating the address of a heritage location, available from the view screen
- screens for maintaining pick-lists and uploading files
Migrate the existing data
The data in the access database needed to be brought across as a once-off process. This presents problems such as where existing data breaks new data validation rules or invalid addresses cannot be located. Fixing this data will take several months before the new Heritage Register can be considered to be correct.
Add administrative interest to ILIS
An important driver of the database migration is the ability for Heritage records to be displayed in ILIS along with other interests on land. The following screen shot is an example of this happening.
Note this functionality is not implemented in production ILIS as the data in the new Heritage Register has not yet been verified.
Figure 2: A Heritage record being displayed in TEST ILIS as an Administrative Interest
The link between the Heritage Register and ILIS is live, ILIS reflects changes made in the register immediately.
Creation of a spatial layer for using in GIS applications and ILISMaps
When the Heritage Register data has been verified, a layer in the NRETA spatial dissemination database (NRDB1) will be created created, alongside the existing layers including bores, parks and vegetation surveys. This layer will include only the records the data custodians flag as 'public', and even then will only include a subset of the fields contained in the Register. This allows the custodian to control which heritage locations are visible to the NT government when they connect using their oracle clients.
Examples of such clients include Microsoft Access, ILISMaps, MapInfo and NT Visualiser (Google Earth Enterprise).
The new Heritage Register has been completed, and the custodians are currently verifying the data, this is expected to take several months from the sign-off date of July 2007. During this review stage no new data will be written to the old access database, and after verification the new Register will be declared 'in production', and the links to ILIS and NT Spatial Data Infrastructure established in production.
Contacts for Further Information:
Phillip Rudd - 08 899 55302 or Phillip.Rudd@nt.gov.au