Idox - Uniform Publishing Master Spatial Data from PostGIS to Oracle

System CategoryData Warehousing
Source

Astun Spatial Data Warehouse

Available since
2014
Organisation

Introduction

An Oracle based spatial data repository was upgraded to a PostGIS repository as part of a new Enterprise GIS platform comprising of iShare GIS, iShare Maps and iShare Spatial Data Warehouse (SDW).

The Oracle Spatial Data Warehouse acted as the spatial repository for Idox UNI-form which references different Planning related layers for Planning Searches and display purposes within Idox Public Access for UNI-form.  Idox Public Access provides real time tailored information to citizens, councillors and other users whilst completely integrating with the look and feel of the council web site.

There are a variety of individual layers that need to be published from the iShare SDW to Oracle on a regular basis.  The layers can be summarized into the following themes:

    • Planning Constraints (TPOs, Landfill, Conservation Areas etc)

    • Planning AdHoc layers (Country Parks, Village Greens etc)

    • Street Care (SSSI, Notable Species, Wildlife Corridors etc)

    • Ordnance Survey MasterMap (Area, Line and Point features)

Outcomes (Why?)

The iShare SDW holds the master spatial data that publishes data to the UNI-form Oracle application database on a regular basis through the iShare Studio Workflow tasks. These can be scheduled as often as required but normally just being once per day.

As UNI-form is a high profile, mission critical system the data publishing process needs to ensure that data is published in a robust, consistent and testable manner.  This isn’t just in the structure of the data but also the validity of the data via a number of features tolerance i.e. number of features is consistent with the previous number i.e. if more than 10% difference in the number of features then warn the Administrator that this is outside of the tolerance.

Therefore the methodology provides for multiple checks to be performed with the majority being performed in PostgreSQL with other checks being performed in Oracle.  This double insurance or ‘belt and braces’ approach ensures the 3 tenets for this process (robust, consistent and testable) is achieved.

The principles of the process are:

  1. Master data is massaged through PostgreSQL views to the required structure for Oracle

  2. Data is published from PostGIS to an Oracle Staging Area

  3. The data in the Oracle Staging Area is transferred to the Live area through a quality checking routine within Oracle

Steps involved

The steps provided will be in the following manner:

  1. The master layers are held in iShare SDW (PostGIS) and maintained and edited there.

  2. Associated  Views are created in iShare to ensure they are available to export to Oracle in the correct format (field names, formats etc).

  3. An iShare Workflow job is configured to export to a staging area within Oracle.

  4. Checks are performed within iShare to ensure that consistency has been achieved i.e. number of records match for each layer (table) in the source and the destination.

  5. An Oracle script is executed  (written and supported by Client but with Astun assistance if necessary) to take the data from the staging area, perform some additional data quality / audit checks and if fine them update the Live tables used by UNI-form.

Data Workflow Overview