In No-Cost [Migration Methodologies] PostgreSQL (PostGIS) to Oracle ( Spatial Data )

Translation Methods : PostgreSQL (PostGIS ) to Oracle ( Spatial)

Introduction:

Oracle which is one of world famous leading relational , multi-model database called as Converge Database , has good capability of handling Relational, Spatial, JSON, XML, IoT.

PostgreSQL is one of the popular open-source, object-relational database database. It adds support for geographic objects allowing location queries to be run in SQL. It also adds functions, operators, and index enhancements that apply to these spatial types .

PostGIS is a spatial database extender for PostgreSQL object-relational database. It adds support for geographic objects allowing location queries to be run in SQL. It also adds functions, operators, and index enhancements that apply to these spatial types. With PostGIS, spatial data are stored in a Geometry column. This column stores data in a spatial coordinate system that’s defined by an SRID (Spatial Reference Identifier). For additional information , refer here

In this article, due to increasing demand of migration from PostGIS to Oracle Spatial, we walk-through, the different migration methodology to migrate / translate from PostgreSQL ( including Spatial data ) data to Oracle Database.

Why Oracle:

PostgreSQL has real-time challenges, likes performance issues under peak loads and security threat, that make Oracle an appealing, primary option as migration target.

Since Oracle has enterprise capabilities like TDE, RAC , DG, Parallel Query, Partitioning, MV,AQ, Sharding. And oracle a Converge Database supports Multi-Data model ( RDBMS, Spatial, Graph, IoT, Streaming, JSON / Document, XML including ML) and Multi Workload ( OLTP and DW) there is NO choice to say NO for Oracle Database.

PostgreSQL ( PostGIS) — Source Data could be available any of following data-model.

Either it can be shared as PG binary dump
OR it can accessible directly from PostgreSQL database
OR it can be shared as Shape/ GeoJSON / KML files

We can rely on below migration methods, for above use cases.

Method 1: Using GDAL Utility

GDAL is a translator library for raster and vector geospatial data formats that released under an MIT Open Source License.

When to use GDAL ?

If source data is more of Spatial data ( Raster and Vector), then GDAL utility would be best method for migration. GDAL is comprised of various functions and utilities. We rely on the gdal_translate and ogr2ogr functions which used for Spatial data migration.

When NOT to use GDAL ?

In my previous experience, data with time-zone formats are not translated properly. Either it truncates or skips or fails the process. So if you have table with time-zone , avoid GDAL utility for migration

**Method 2: Using Sql*Loader**

SQL*Loader is oracle’s native loader utility to load data from external files into tables of an Oracle database. It has a powerful data parsing engine that parses spatial data WKT format to Oracle SDO_GEOMETRY. SQL Developer is a free no cost product that users can download from OTN.

When to use Sql*Loader ?

If source data is more of

relational with time zone data type
or more of Spatial (Vector) data, then sql*loader would be optimal method for migration

Note: Sql*Loader does not support Raster Data

Method 3: Using Python

Python is open source, an interpreted, object-oriented, high-level programming language with dynamic semantics. By using below two modules we can read the PostgreSQL ( relational and spatial ) data and it can be inserted to Oracle database.

When to use Python ?

If source is provided as PostgreSQL binary dump file ( including Spatial — Vector ) , by using small routine, we can extract and insert into target Oracle database.

Note: Above modules does NOT support Raster Data

Method 4: Using Spatial Studio

Oracle Spatial Studio (also known as Spatial Studio) is a web application providing self-service access to the spatial capabilities of Oracle Database. It is a free web-based tool for use with Oracle Autonomous Database, Oracle Database Cloud service, and Oracle Database on premises. Spatial Studio allows users to create and share spatial analysis and interactive web maps using self-service GUIs.

When to use Spatial Studio ?

if Source Data ( PostGIS — Spatial ) available as Shape/GeoJSON / KML format, then using spatial studio, we can load above data set and it can be inserted into oracle database seamlessly

Refer DataLoad-SpatialStudio for steps and additional information

Method 5: Using Oracle Sql-Developer

We need to download the pgJDBC pluggin and attach to SQL Developer ( go to Tools > Preferences > Data Modeler Third Party JDBC Drivers)

Simply, follow the wizard. That’s it.

When to use Sql-Developer ?

For NON-Spatial Data migration, we can rely on Sql-Developer

Conclusion

Depends upon source data set, we can use any of above method. The main advantage of above methods are FREE to use. Using our skill, we can have full control on migration with NO cost on migration process.

Thanks for your time to reading this article.

If you need more information about any of above method, don't hesitate to ping me via mail2crsathishkumar@gmail.com

Happy Learning and Happy Sharing.