[gdal-dev] Non-standard field types in OGR

[Even Rouault]

Hi Matthias,

> 
> I'm looking for a way to load a geopackage layer with a type affinity
> set to a custom type and to detect this type.

This would likely be illegal for core GeoPackage spec since data types are 
standardized per http://www.geopackage.org/spec/#r5 . But could be made an 
extension

> 
> I.e. what I have is a table with a field called myJson, the type of
> which is JSON.
> 
> Looking at the debug output of QGIS, I currently get this message which
> seems to be generated by OGR:
> 
> Warning 1: Field format 'JSON' not supported
> 
> Warning 1: geometry column 'myJson' of type 'JSON' ignored
> 
> Is it currently possible to load such a field via OGR?

Nope, not by the GPKG driver in its current state

> 
> What I was hoping is to load this field (with fallback as string) and
> detect that it's of type JSON. I think even if I was able to load the
> field, it would only be possible to detect which of the types in
> OGRFieldType it is and not get the raw name of the type on the
> underlying data source.

Yes, you would get it as a OFTString. That said we could potentially extend 
the OGRFieldSubType enumeration to have a OFSTJSon value that would be valid 
for OFTString type. A number of drivers could use that (like Postgres)

> 
> Am I missing something and this is already possible? If not, would it be
> acceptable to patch ogr to support this? 

The driver could be made more robust not to consider columns with unknown data 
types as potential geometry column as it does currently, but try to handle 
them as attributes with fallback to OFTString. Emitting a warning could still 
be appropriate, unless the GeoPackage also contains in gpkg_extensions a 
declaration of the fact that the table has custom data types (extension to 
create for that purpose)

It would help SQLite to use 'TEXT_JSON' for correct detection as text affinity 
(see §3.1 of https://www.sqlite.org/datatype3.html)

~~~~~~

Hum actually another option, likely a better one, would be to use the already 
standardized 'schema' extension (unimplemented by the OGR driver):
http://www.geopackage.org/spec/#extension_schema
And set 'application/json' in gpkg_data_columns as the 'mime_type'
The column in the table layer would then be declared as standard TEXT, so 
consumers unaware of the extension would be able to still read it correctly.

Even

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com