[gdal-dev] Geopackages and Datetime fields
rdmailings at duif.net
Fri Jan 24 06:38:51 PST 2020
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
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  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
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
Created an issue for this , and wondered how GDAL or other software
was handling this.
In my issue  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  the following:
which works BOTH for tables AND views !
Question here: would that be the best route to take to make Geotools-lib
Looking in the sqlite descriptions about pragma's  it is not very
safe, but ... hey, if GDAL is using it... :-)
Thanks for anybody willing to shed some light in this.
More information about the gdal-dev