Opened 7 years ago

Closed 7 years ago

Last modified 6 years ago

#5816 closed defect (fixed)

Create consistent Layer creation options for geometry column name, spatial index and FID

Reported by: jpalmer Owned by: Even Rouault
Priority: normal Milestone: 2.0.0
Component: default Version: unspecified
Severity: normal Keywords:
Cc:

Description (last modified by jpalmer)

Various OGR vector drivers provide the option to define the name of the geometry field/column when creating a layer. When scripting for multiple output formats at runtime this is an annoyance.

GDAL should have one consistent layer creation option for this option across all drivers. It should also be possible to retain the older LCO values for compatibility reasons, but not advertise them in the documentation any more. Could be a good idea to address this before GDAL 2.0 is released :)

Below is a list of the different LCO for each driver:

DriverLayer creation option
MSSQLSpatialGEOM_NAME
PostgreSQLGEOMETRY_NAME
GPKGGEOMETRY_COLUMN
FileGDBGEOMETRY_NAME
MySQLGEOMETRY_NAME
SQLite - none - (GEOMETRY / WKT_GEOMETRY hardcoded)
OCIGEOMETRY_NAME

==> Use GEOMETRY_NAME

Similar situation with FID column name:

DriverLayer creation option
MSSQLSpatial -none - (ogr_fid hardcoded)
PostgreSQLFID
GPKGFID
FileGDBOID_NAME
MySQLMYSQL_FID
SQLite - none - (OGC_FID hardcoded) -->TODO
OCI - none, but use OCI_FID configuration option that default to OGR_FID

==> Use FID

And a few minor issues with spatial index with the OCI driver using INDEX and the documentation being inconsistent across the drivers:

DriverLayer creation optionDocumented
MSSQLSpatialSPATIAL_INDEXNot documented
PostgreSQLSPATIAL_INDEXOFF/ON
GPKGSPATIAL_INDEXYES, NO
MySQLSPATIAL_INDEXYES, NO
SQLiteSPATIAL_INDEXyes/no
OCIINDEXOFF

Change History (12)

comment:1 Changed 7 years ago by Even Rouault

Description: modified (diff)
Milestone: 2.0
Owner: changed from warmerdam to Even Rouault
Summary: Create consistent Layer creation options for geometry column nameCreate consistent Layer creation options for geometry column name and FID

comment:2 Changed 7 years ago by jpalmer

Description: modified (diff)
Summary: Create consistent Layer creation options for geometry column name and FIDCreate consistent Layer creation options for geometry column name, spatial index and FID

comment:3 Changed 7 years ago by Jukka Rahkonen

It might also be good to review also which case should be used for the geometry column names in each database. For example Spatialite driver creates geometry column named "GEOMETRY" and inserts that as upper case also into geometry_columns. However, native Spatialite tools are using cast to lower case when storing field names into geometry_columns and it would be better to have lower case name also in the main table. GeoPackage standard mandates lower case:

"All specified table and column name values SHALL be lowercase."

Different cases may lead to troubles if GDAL 2.0 starts to use double-quoted identifiers. SQLite does not really care much about the case but for example Oracle can have fields "geometry" and "GEOMETRY" in the same table and in that case the upper case GEOMETRY gets selected with

select geometry from table;

while the lower case field is selected with

select "geometry" from "table";

comment:4 Changed 7 years ago by Even Rouault

Jukka, regarding 'All specified table and column name values SHALL be lowercase' statement, reading it in the context, and nothing the 'specified' word, I *think* it only applieds to 'system' tables and columns defined by the GPKG spec, but not to user tables and columns.

comment:5 Changed 7 years ago by Jukka Rahkonen

I agree. I read it that "features user data tables" may have also user defined columns which do not have lowercase names. I was thinking about the name of the geometry column which is "specified" and perhaps needs to be lowercase because of that. But it looks like all critical triggers etc. has made to make string comparisons by using Lower() function and having geometry column "geoMetRy" would not break anything.

Whole chapter 1. Base from the standard:

The required capabilities specified in this clause serve as the base
for options specified in clause 2 and extensions specified in 
clause 3. All gpkg_* tables and views and all tiles user
data tables specified in this standard SHALL have only the 
specified columns and table constraints. Any features 
user data tables MAY have columns in addition to those specified.
All specified table and column name values SHALL be lowercase.

comment:6 Changed 7 years ago by Even Rouault

trunk r28513 "MSSQLSpatial and GPKG: use standardized 'GEOMETRY_NAME' option name; Add GEOMETRY_NAME to SQLite; FileGDB and MySQL: use standardized 'FID' option name (#5816)"

comment:7 Changed 7 years ago by Even Rouault

trunk r28514 "SQLite: add a FID layer creation option (#5816)"

comment:8 Changed 7 years ago by Even Rouault

Resolution: fixed
Status: newclosed

comment:9 Changed 7 years ago by jpalmer

Excellent - thank you.

comment:10 Changed 7 years ago by Even Rouault

Hum forgotten changes: r28572 "FileGDB: use FID as layer creation option (deprecate OID_NAME) (#5816)"

comment:11 Changed 6 years ago by tamas

Implemented FID layer creation option for MSSQL in r28987

comment:12 Changed 6 years ago by Even Rouault

Milestone: 2.02.0.0

Milestone renamed

Note: See TracTickets for help on using tickets.