Oracle Spatial

Oracle Spatial to PostGIS

Ora2Pg fully exports Spatial objects from Oracle database. There are some configuration directives that can be used to control the export.

• AUTODETECT_SPATIAL_TYPE

  By default, Ora2Pg looks at indexes to see the spatial constraint type and dimensions defined under Oracle. Those constraints are passed at index creation using for example:

CREATE INDEX ... INDEXTYPE IS MDSYS.SPATIAL_INDEX
PARAMETERS('sdo_indx_dims=2, layer_gtype=point');

If those Oracle constraint parameters are not set, the default is to export those columns as generic type GEOMETRY to be able to receive any spatial type.

The AUTODETECT_SPATIAL_TYPE directive allows Ora2Pg to autodetect the real spatial type and dimension used in a spatial column; otherwise a non- constrained "geometry" type is used. Enabling this feature will force Ora2Pg to scan a sample of 50,000 columns to look at the GTYPE used. You can increase or reduce the sample size by setting the value of AUTODETECT_SPATIAL_TYPE to the desired number of lines to scan. The directive is enabled by default.

For example, in the case of a column named shape and defined with Oracle type SDO_GEOMETRY, with AUTODETECT_SPATIAL_TYPE disabled it will be converted as:

shape geometry(GEOMETRY) or shape geometry(GEOMETRYZ, 4326)

and if the directive is enabled and the column just contains a single geometry type that uses a single dimension:

shape geometry(POLYGON, 4326) or shape geometry(POLYGONZ, 4326)

with a two or three dimensional polygon.

• CONVERT_SRID

  This directive allows you to control the automatic conversion of Oracle SRID to standard EPSG. If enabled, Ora2Pg will use the Oracle function sdo_cs.map_oracle_srid_to_epsg() to convert all SRIDs. Enabled by default.

  If the SDO_SRID returned by Oracle is NULL, it will be replaced by the default value 8307 converted to its EPSG value: 4326 (see DEFAULT_SRID).

  If the value is higher than 1, all SRIDs will be forced to this value. In this case, DEFAULT_SRID will not be used when Oracle returns a null value, and the value will be forced to CONVERT_SRID.

  Note that it is also possible to set the EPSG value on the Oracle side when sdo_cs.map_oracle_srid_to_epsg() returns NULL if you want to force the value:

system@db> UPDATE sdo_coord_ref_sys SET legacy_code=41014 WHERE srid = 27572;

• DEFAULT_SRID

  Use this directive to override the default EPSG SRID to use: 4326. Can be overwritten by CONVERT_SRID, see above.

• GEOMETRY_EXTRACT_TYPE

  This directive can take three values: WKT (default), WKB and INTERNAL. When it is set to WKT, Ora2Pg will use SDO_UTIL.TO_WKTGEOMETRY() to extract the geometry data. When it is set to WKB, Ora2Pg will use the binary output using SDO_UTIL.TO_WKBGEOMETRY(). If these two extract types are called at the Oracle side, they are slow and you can easily reach Out Of Memory when you have lots of rows. Also, WKB is not able to export 3D geometry and some geometries like CURVEPOLYGON. In this case, you may use the INTERNAL extraction type. It will use a Pure Perl library to convert the SDO_GEOMETRY data into a WKT representation, the translation is done on Ora2Pg side. This is a work in progress, please validate your exported data geometries before use. Default spatial object extraction type is INTERNAL.

• POSTGIS_SCHEMA

  Use this directive to add a specific schema to the search path to look for PostGIS functions.

• ST_SRID_FUNCTION

  Oracle function to use to extract the SRID from ST_Geometry meta information. Default: ST_SRID, for example it should be set to sde.st_srid for ArcSDE.

• ST_DIMENSION_FUNCTION

  Oracle function to use to extract the dimension from ST_Geometry meta information. Default: ST_DIMENSION, for example it should be set to sde.st_dimension for ArcSDE.

• ST_GEOMETRYTYPE_FUNCTION

  Oracle function to use to extract the geometry type from an ST_Geometry column. Default: ST_GEOMETRYTYPE, for example it should be set to sde.st_geometrytype for ArcSDE.

• ST_ASBINARY_FUNCTION

  Oracle function used to convert an ST_Geometry value into WKB format. Default: ST_ASBINARY, for example it should be set to sde.st_asbinary for ArcSDE.

• ST_ASTEXT_FUNCTION

  Oracle function used to convert an ST_Geometry value into WKT format. Default: ST_ASTEXT, for example it should be set to sde.st_astext for ArcSDE.