In vector GPKG with DateTime field Geoserver sees it as a String field

Closing this one out:

• David and Richard added mappings, and found aggregate functions were not respecting mapping - in part by turning off aggregate functions

• Andrea addressed the performance implications - by adding some logic to visitor results so they could determine if a conversion was needed

See also https://github.com/geotools/geotools/pull/2811

I just added a geopackage ‘model.gpkg’ with several tables and views in it.

To test I would use ‘view3’ (>34000 polygons and a datetime column).

But to test how it ‘detoriates’ the generation of the capabilities it is also a good one because it contains 3 views and tables.

Adding 2 example datasets for reference.

cloud.gpkg is a dataset with table named ‘grid’ holding polygons, a table named ‘data’ holding a ‘Value' and a ‘Time’ and a ‘Cell’-id which is the polygon id from ‘grid’. There there is the view ‘view’ which joins them together (as in adds the polygons).This cloud.gpkg is not yet adapted to have a DATETIME column in it’s data

effective.gpkg is a dataset in which I created copies of the ‘data’ table by redefining type and filling it with a copy of the data but changing the epoch to a valid iso-datetime string.

For example view3 should work because then time is an isostring.
view4 still has epoch integers.

Note that both dataset are kept rather small. Normal grids have at least 40000 records (but can be a multitude).

@Jukka Rahkonen you are right. But for example QGIS is able to ‘load’ the view from attached cloud.gpkg. Then loaded with the timemanager plugin you can filter on Time (in hours).

Same for view3 from effective.gpkg

By some hardcoding hacking into the gpkg dialect I’m at least able to registre a layer in a way that datetime is recognized as as a time column. But then geoserver is still not able to read the strings/epochs as datetime…

Have to find out where the mapping back is arranged

Spatial views in geopackage are still little bit unclear, see https://github.com/opengeospatial/geopackage/issues/446 and other related tickets. Does Geoserver/Geotools have logic for finding the spatial index from the main table if it is usable, which means that geometry in the view is selected simple as “select geometry from main_table”?

For views, as far as I know, using PRAGMA is the only direct way. Alternatively the client could try to follow the route by finding the CREATE VIEW AS… from sqlite_master.

Even PRAGMA can’t handle this
create view viewtest4 as
select CAST(dt as text) dt
from datetimetest;