#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 )
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:
Driver | Layer creation option |
MSSQLSpatial | GEOM_NAME |
PostgreSQL | GEOMETRY_NAME |
GPKG | GEOMETRY_COLUMN |
FileGDB | GEOMETRY_NAME |
MySQL | GEOMETRY_NAME |
SQLite | - none - (GEOMETRY / WKT_GEOMETRY hardcoded) |
OCI | GEOMETRY_NAME |
==> Use GEOMETRY_NAME
Similar situation with FID column name:
Driver | Layer creation option |
MSSQLSpatial | -none - (ogr_fid hardcoded) |
PostgreSQL | FID |
GPKG | FID |
FileGDB | OID_NAME |
MySQL | MYSQL_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:
Driver | Layer creation option | Documented |
MSSQLSpatial | SPATIAL_INDEX | Not documented |
PostgreSQL | SPATIAL_INDEX | OFF/ON |
GPKG | SPATIAL_INDEX | YES, NO |
MySQL | SPATIAL_INDEX | YES, NO |
SQLite | SPATIAL_INDEX | yes/no |
OCI | INDEX | OFF |
Change History (12)
comment:1 by , 9 years ago
Description: | modified (diff) |
---|---|
Milestone: | → 2.0 |
Owner: | changed from | to
Summary: | Create consistent Layer creation options for geometry column name → Create consistent Layer creation options for geometry column name and FID |
comment:2 by , 9 years ago
Description: | modified (diff) |
---|---|
Summary: | Create consistent Layer creation options for geometry column name and FID → Create consistent Layer creation options for geometry column name, spatial index and FID |
comment:3 by , 9 years ago
comment:4 by , 9 years ago
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 by , 9 years ago
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 by , 9 years ago
comment:8 by , 9 years ago
Resolution: | → fixed |
---|---|
Status: | new → closed |
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
while the lower case field is selected with