SpatialDB Advisor

FOSS4G 2009 Sydney Presentation

I gave a rather manic presentation (Open Source, Oracle and PostGIS) at the FOSS4G 2009 Conference at Darling Harbour in Sydney, Australia, on Thursday October 22nd. The presentation was a little risqué a times (anyone offended please accept my apologies: I seem to become a different person when I present) and, I suspect, its message was somewhat hidden by the volume of slides and the speed of presentation.

This short note is an attempt to clarify what I was trying to do.

Oracle Spatial and PostgreSQL’s PostGIS are two of the most mature implementations of a spatial type system for their relevant host databases. One is open source, the other proprietary. Yet open source software supports both products with some open source products finding aspects of Oracle’s implementation of standards problematic.

PostGIS’s function set for its basic spatial type is far more extensive than Oracle’s. As each release is made, PostgreSQL/PostGIS increases in strength with EnterpriseDB aiming to do the impossible, which is to hide the differences between Oracle and PostgreSQL in order to convert businesses from Oracle to PostgreSQL.

In my working career I have rarely seen, on a customer’s servers, only ONE DB product. The geospatial professional thus has to learn to work with all databases that support a spatial type: you can’t demand the removal of one or other of the databases! You have to learn to work with both.

In order to improve open source software support for Oracle, and to help the geospatial professional manage both databases at the one site, the talk I gave tried to provide:

• An understanding of Oracle Locator/Spatial concepts and components;
• The relevant OGC/SQLMM standards in common;
• How to bring the separate geospatial metadata structures together;
• An understanding of the type systems of Oracle SDO_Geometry/ST_Geometry and PostGIS’s geometry;
• Oracle’s tolerance model (as this impacts how one can port PL/SQL or PL/pgSQL code from one database to the other);
• Programmatic and framework issues.

Once these were described I hoped that the audience would be aware of the issues when migrating between the databases. It was also intended to show how to minimise the amount of code that would need to be rewritten when deploying a custom function on both databases.

The talk was not meant to be a promotion of Oracle. Rather it was meant to foster improved access by open source software (eg ogr2ogr) and to help when called upon to manage both Oracle Spatial and PostGIS installations.