[gdal-dev] Geopackages and Datetime fields

[Richard Duivenvoorde]

Hi Devs / Geopackage masters,

Sorry for this long email, but I have some Geopackage/Datetime questions
of which I hope people here have some knowledge and find some time to
read this...

I'm experimenting with writing time-based model results into
geopackages, and make those results (1 run = 1 geopackage) automatically
available as WMS-t.

-1- First thing I wonder is how gdal/mapserver is handling geopackages
in a multi-threaded environment: if more web=clients are making a lot of
requests, is gdal/mapserver able to open the geopackage in parallel?
Reason I ask is because of the '-shm' and '-wal' files.

-2- Seconds Question is about Datetime handling.
Reading [0] I understand that SQLite (and so gpkg) does not have a
native store format for date/times. And there is something called
'affinity' which determines IF something is stored as integer, float or
string.

I wonder how Mapserver/GDAL is handling filters with datetimes. Trying
to setup mapserver with a geopackage with datetimes as ISO-strings all
worked. But doing the same with a datetime column holding
'unixepoch'-integers was NOT working.
Should this work? Or is filtering only working with ISO datetime strings?

-3- I tried both Mapserver and Geoserver (I know, not gdal...). But
Geoserver is not 'seeing' the datetimes as... well datetimes, but as
strings.
Created an issue for this [1], and wondered how GDAL or other software
was handling this.
In my issue [1] Jukka proposed to use a query like:
 SELECT sql FROM sqlite_master
 WHERE type = 'table' AND name = 'tablename'
which then gives you the sql to create the table WITH the types in it.
BUT this is not working for 'views' based on that table (my use case...)

Then I found in GDAL sources [2] the following:
 PRAGMA table_info('tablename')
which works BOTH for tables AND views !
Question here: would that be the best route to take to make Geotools-lib
more datetime-aware?
Looking in the sqlite descriptions about pragma's [3] it is not very
safe, but ... hey, if GDAL is using it... :-)

Thanks for anybody willing to shed some light in this.

Regards,

Richard Duivenvoorde


[0] https://www.sqlite.org/datatype3.html
[1] https://osgeo-org.atlassian.net/browse/GEOS-9392
[2]
https://github.com/OSGeo/gdal/blob/master/gdal/ogr/ogrsf_frmts/sqlite/ogrsqlitetablelayer.cpp#L525
[3] https://www.sqlite.org/pragma.html