ogr2ogr Oracle – Export Oracle Table with Multiple Geometry Columns to GPKG

Question

I am using ogr2ogr to export a table in Oracle to GPKG. The table in Oracle has two columns (GEOMETRY_2, GEOMETRY_20) with geometry info.

I am using the following ogr2ogr command to export the table into GPKG.

ogr2ogr -progress -f "GPKG" C:\Users\test\test.gpkg  OCI:{username}/{password}@{db_alias}:{table_name -sql "select * from TableName@XXX" -append -nln "TableName" -lco GEOMETRY_NAME=geometry -a_srs EPSG:31287

The command creates the GPKG as in the image. What I do not understand, which geometry columns (GEOMETRY_2, GEOMETRY_20) ogr2ogr uses to create the GEOMETRY column in GPKG. If it uses GEOMETRY_2, is there any way still have GEOMETRY_20 in the GPKG?

Answer 1

The documentation about how the support for multiple geometry fields is implemented in GDAL is in https://gdal.org/development/rfc/rfc41_multiple_geometry_fields.html. It does not say clearly which geometry is the default geometry but I suppose that it is the first one that GDAL finds. Better not to trust on the defaults but select the geometry explicitly with SQL just like you have already done.

When it comes to GeoPackage, you are out of luck because the GeoPackage standard allows only one geometry field. Excerpt from the standard:

Requirement 30

A feature table SHALL have only one geometry column. Feature data models [B23] from non-GeoPackage implementations that have multiple geometry columns per feature table MAY be transformed into GeoPackage implementations with a separate feature table for each geometry type whose rows have matching integer primary key values that allow them to be joined in a view with the same column definitions as the non-GeoPackage feature data model with multiple geometry columns.

You must select some other format or model your data in a different way. SpatiaLite does support multiple geometry columns and is otherwise rather close to GeoPackage.