Moving from
Oracle/ArcGIS
to
PostGresql/PostGISYves Choquette
Natural Resources Canada – Earth Sciences Sector
Centre for Topographic Information

2The Organization
 The Centre for Topographic Information (CTI)
is part of the Mapping Information Branch of
the Earth Sciences Sector, within Natural
Resources Canada; The Centre’s mandate is to create, maintainand disseminate Digital Topographic Data forCanada; To achieve its mandate, the CTI works inpartnership with the provinces and territories.

3The Geospatial Database All partners are free to work with the systems
and technologies they prefer to produce theterritory representation;
 However, everyone must deliver their data as
1:50K National Topographic System (NTS)tiles into the main repository, the Geospatial
Database (GDB); The GDB is a protected database linked to atransaction services system that allows
extraction and delivery of data in a secureway.

4Technology Watch
 The GDB management system prototyping
project is part of the CTI technology watch
activities;
 Every employee has the opportunity to invest
10% of one’s time in a project with the aim ofstudying new technologies that could be usedin daily operations; It is no secret that “Free and Open Source
Software” is a subject full of promises for theGeomatics world.

5Project Goals
To ...
 Show that the GDB management system canrun on PostgreSQL/PostGis;
 Use as much as possible free and open source
software to perform the prototype tasks;
 Assess the system performance; Explore the editing and visualization tools thatcan be used on the data stored in PostGIS.

6Current System Description

GDB

PGDB

Oracle/SDE

FME

Services

Inspection

ArcMapFMEProduction

PGDB

GDB
ManagementPrototype Description

7

PGDB

FME

XML-RPC
ServerGDBPostgreSQL
PostGisInspection

FME???
GISProduction

PGDB

GDB
Management8Proprietary Software Used
 Feature Manipulation Engine (FME):• Is an integrated collection of Spatial ETL (Extract, Transformand Load) tools for spatial data transformation and datatranslation• Produced by Safe Software• Considered to be a GIS utility to help users convert data
between formats as well as process data geometry andattributes.

 Load Sharing Facility (LSF):• Is a commercial computer software job scheduler• Sold by Platform Computing• Used to execute batch jobs on networked Unix and Windowssystems9Open Source Software Used Development Environment:• Eclipse 3.4.1– Pydev + Subversive - Pylint• Python 2.4– psycopg2 + typecheck - lxml

 Database:• PostgreSQL 8.4 + PostGis 1.3.6

 GIS Tested:• uDig 1.1.1 and 1.2 RC3• Quantum Gis 1.4.0• gvSIG 1.9

10

Implementation Phases

 Software Installation
 Database Configuration
 Data Loading Delivery Service Development
 Delivery Test
 GIS Evaluation

11

Software Installation

 Generally, open source software installation
methods are reliable, whatever the platformused.
 In case of problem, communities present on theWEB are helpful and competent.

Database Configuration

12The section 5.7 of the PostgreSql documentation
on schemas explains how an Oracle Databasecan be converted to PostgreSQL.
http://www.postgresql.org/docs/8.4/interactive/ddl-schemas.html

OracleInstance:

Account:

PostgreSQL
BDG_GEST_DEV
Server + PortBDG_DBA
Schema + Login

Cluster:

BDG840D
Server + Port

Database:

BDG_GEST_DEV

Schema:BDG_DBA

Login role: BDG_DBA

Database Configuration

13



During the creation of the database:• Use the template template_postgis to integrate the spatial
functionalities;• Create a schema and a login role with the same name;• Make sure the search_path points to the schema just created
and then, to the public schema:set search_path=bdg_dba,publicOracle
Instance:

Account:

PostgreSQL
BDG_GEST_DEV
Server + PortBDG_DBA
Schema + Login

Cluster:

BDG840D
Server + Port

Database:

BDG_GEST_DEV

Schema:BDG_DBA

Login role: BDG_DBA

Database Configuration

14

Access account to the GDB Management System:•••

The XML-RPC server uses an account (ser_gest_bdg) to store the state of theactive processes;
The bdg_dba account gives read/write access to the main repository data;
The bdg account gives read-only access to the bdg_dba account data via the useof a group role named bdg_gest_pro_read

GDB
ser_gest_bdg

XML-RPC Server

bdg_dba«owner of»«owner of»

Instance:ser_gest_bdg

Instance:
bdg_gest_dev

Schema:ser_gest_bdg+ meta_release+ ser_reconcile_log+ ser_semaphore

PostgreSQL
PostGisSchema:bdg_dba+ bdg_aire_designee_0+ bdg_aire_designee_1+ bdg_aire_designee_2etc.

«r»bdgGDB Management«r»

Schema:public+ geometry_columns+ spatial_ref_sys

15Data Loading The data has been loaded per NTS tile, using
FME batches.
 To speed up the process, linear and spatialindexes have been created after the loading.
 This type of loading does not involve any
spatial processing; The loading time is comparable to Oracle/SDEloading time.

16Delivery Service Development
 The development took 16 weeks and wasmade by Stéphane Janvier, a COOP student
from Sherbrooke University;
 All PL/SQL services from the old system wererewritten in Python, resulting in a better systemsecurity; The Eclipse development environment is ideal
to manage the system:• Subversion repository integration;• Ability to trace the code execution on client andserver sides using the same session.

17Delivery Tests
 The execution time is comparable toOracle/SDE execution time on the old system:• The loading involves no spatial operations;• The processing done resembles the massive
loading described previously, thus the similarity
in performance;• It is difficult to make an accurate comparison
between the production server and theprototype server because of the configurationdifference.

GIS Evaluation18

 Sought-after GIS Characteristics• The GIS takes into account the display window
coordinates when extracting the information to bedisplayed;• It uses the spatial index to get the data from thedatabase;• The interface is intuitive.

 Work Method• Display of the NTS grid that covers the Canada;• Zoom on the interest zone;• Activation of the layers to work with.

19

Simulation

GIS Evaluation

20

 gvSIG• The interface is not intuitive;• Although the installation went without problem,
first attempts to display the NTS grid werelaborious and unsuccessful;• A training course is needed to figure out how it
works and what the potential is.

21

GIS Evaluation

 Quantum GIS• The interface is more intuitive;• Since the display zone is not taken into account,
it is preferable to specify a filter (“where”clause);• The application is slow: it may take a few
minutes to get a result after a button clic.

GIS Evaluation22

 uDig 1.1.1• This version satisfies the Sought-after GIS
characteristics and the performance is nearly as
good as with ArcMap– Some database tuning could improve the performance• The application does not like impatient people: itis preferable to let commands end before
sending a new one.

 uDig 1.2 RC3• Shows a display problem, both on Windows andon Mac OSX.

23

About Costs
 Some may say it is not free:• Need to pay for training and consultants• But, is it really different from proprietary
software?

 There are no license fees to pay when Internet
is involved:• This means a lot of savings for the WEB
distribution applications.

 Have you seen “The surprising truth about
what motivates us”
http://www.youtube.com/watch?v=u6XAPnuFjJc&feature=player_embedded

24Conclusion
 Although interesting, Free GIS does not seemto have reached its full potential:• This is probably due to the tasks complexity tobe managed(multi-thread, spatial functions, graphic display, DB connection, etc.)

 All the GIS tests show stability problems: These may be caused by a bad system
configuration, particularly with Java and its setof libraries.

25Conclusion
 PostgreSQL is a mature and reliable DBMS• Its existence goes back to 1985, when theIngres software was completely rewritten tobring Postgres;• The SQL language was integrated in 1995.

 PostGis integrates well into PostgreSQL• uDig tests have shown that the spatial index
system works well;• Its model is simple in comparison with SDE
Based on the “Keep It Simple” paradigm (KIS) as opposed to “Keep ItComplex” (KIC)

26

Demo

27

Questions?