GeoPackage output contains invalid field types when publishing content from other JDBCDataStore
Description
Gpkg output can contain fields of type INT4, VARCHAR(255) and FLOAT8 which are not supported by GDAL/OGR. For this reason, QGIS, GRASS and FME ignore those fields on import.
ray@fw13:~/Downloads$ ogrinfo ext_nat_nbp.gpkg
Warning 1: GPKG: unrecognized user_version=0x00000000 (0) on 'ext_nat_nbp.gpkg'
INFO: Open of ext_nat_nbp.gpkg' using driver GPKG' successful.
Warning 1: Field format 'INT4' not supported. Interpreted as INT
Warning 1: Field format 'VARCHAR(255)' not supported
Warning 1: geometry column 'deelgebied' of type 'VARCHAR(255)' ignored
Warning 1: Field format 'FLOAT8' not supported
Warning 1: geometry column 'oppervlakte_hectare' of type 'FLOAT8' ignored
Warning 1: Field format 'VARCHAR(255)' not supported
Warning 1: geometry column 'plan_naam' of type 'VARCHAR(255)' ignored
1: ext_nat_nbp (Multi Polygon)
The INT4 fields are visible as INT, but all other other fields are ignored. (There’s also an “unrecognized user version“.)
These columns are not valid according to the geopackage specification (see Table 1).
Geotools can create GeoPackages with incorrect column types. Typically this will happen if you have a JDBC (i.e. postgis) layer and then request a WFS with GeoPackage output.
The issue is caused because some Datastores (i.e. postgis) add JDBC_NATIVE_TYPENAME and JDBC_NATIVE_TYPE column metadata (descriptor userdata) to the schema. The JDBCDatastore then treats this as "gospel" and will create the DB schema with these specific types. For most JDBC systems, this is correct. However, the GeoPackage spec (Table 1) defines specific sqllite types for columns instead of "normal" JDBC Column types.
You can see that JDBCDatastore#getSQLTypeNames() absolutely uses JDBC_NATIVE_TYPENAME and JDBC_NATIVE_TYPE over any conversion.
I am back porting, and will make a placeholder for the geoserver release notes also.
Reinout van Rees
April 19, 2024 at 1:58 PM
Thanks, done.
Andrea Aime
April 19, 2024 at 1:43 PM
A new issue should be made, and linked to this one. An eventual pull request that fixes the issue will have to refer the GeoTools issue.
Reinout van Rees
April 19, 2024 at 1:41 PM
Can the issue be moved over to GEOT? Or should a new issue be made there?
Andrea Aime
February 19, 2024 at 6:50 PM
Looks like you have a valid report then. We’ll be awaiting PRs to amend the typing system (it’s to be done inside GeoTools BTW, I can try to hunt down the class for you if you have time to work on it).
Gpkg output can contain fields of type INT4, VARCHAR(255) and FLOAT8 which are not supported by GDAL/OGR. For this reason, QGIS, GRASS and FME ignore those fields on import.
ray@fw13:~/Downloads$ ogrinfo ext_nat_nbp.gpkg Warning 1: GPKG: unrecognized user_version=0x00000000 (0) on 'ext_nat_nbp.gpkg' INFO: Open of ext_nat_nbp.gpkg' using driver GPKG' successful. Warning 1: Field format 'INT4' not supported. Interpreted as INT Warning 1: Field format 'VARCHAR(255)' not supported Warning 1: geometry column 'deelgebied' of type 'VARCHAR(255)' ignored Warning 1: Field format 'FLOAT8' not supported Warning 1: geometry column 'oppervlakte_hectare' of type 'FLOAT8' ignored Warning 1: Field format 'VARCHAR(255)' not supported Warning 1: geometry column 'plan_naam' of type 'VARCHAR(255)' ignored 1: ext_nat_nbp (Multi Polygon)The INT4 fields are visible as INT, but all other other fields are ignored. (There’s also an “unrecognized user version“.)
These columns are not valid according to the geopackage specification (see Table 1).
https://www.geopackage.org/spec/
Evaluation
Geotools can create GeoPackages with incorrect column types. Typically this will happen if you have a JDBC (i.e. postgis) layer and then request a WFS with GeoPackage output.
The issue is caused because some Datastores (i.e. postgis) add JDBC_NATIVE_TYPENAME and JDBC_NATIVE_TYPE column metadata (descriptor userdata) to the schema. The JDBCDatastore then treats this as "gospel" and will create the DB schema with these specific types. For most JDBC systems, this is correct. However, the GeoPackage spec (Table 1) defines specific sqllite types for columns instead of "normal" JDBC Column types.
You can see that JDBCDatastore#getSQLTypeNames() absolutely uses JDBC_NATIVE_TYPENAME and JDBC_NATIVE_TYPE over any conversion.
https://github.com/geotools/geotools/blob/main/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStore.java#L3392-L3396
and
https://github.com/geotools/geotools/blob/main/modules/library/jdbc/src/main/java/org/geotools/jdbc/JDBCDataStore.java#L3400-L3402