[MapServer-users] Possible to pass sql dialect when querying a flatgeobuf?

[Trygve Aspenes]

Hi
I have a flatgeobuf file ( or files) and need to filter by time using 
the time from the query.

When I test on command line using ogrinfo I get something like this:
ogrinfo -dialect SQLITE -sql "select * from li_flatgeobuf_demo where 
unixepoch(TimeStamp) between unixepoch('2013-06-20T17:00:29Z') and 
unixepoch('2013-06-20T17:00:29Z')+300" /data/mapserver/tile-index/*
INFO: Open of `/data/mapserver/tile-index/points-20130620070000.fgb'
       using driver `FlatGeobuf' successful.
layer names ignored in combination with -sql.

Layer name: SELECT
Geometry: Point
Feature Count: 17
Extent: (7.254900, -6.423300) - (58.263300, 51.656400)
Layer SRS WKT:
GEOGCRS["WGS 84",
     ENSEMBLE["World Geodetic System 1984 ensemble",
         MEMBER["World Geodetic System 1984 (Transit)"],
         MEMBER["World Geodetic System 1984 (G730)"],
         MEMBER["World Geodetic System 1984 (G873)"],
         MEMBER["World Geodetic System 1984 (G1150)"],
         MEMBER["World Geodetic System 1984 (G1674)"],
         MEMBER["World Geodetic System 1984 (G1762)"],
         MEMBER["World Geodetic System 1984 (G2139)"],
         ELLIPSOID["WGS 84",6378137,298.257223563,
             LENGTHUNIT["metre",1]],
         ENSEMBLEACCURACY[2.0]],
     PRIMEM["Greenwich",0,
         ANGLEUNIT["degree",0.0174532925199433]],
     CS[ellipsoidal,2],
         AXIS["geodetic latitude (Lat)",north,
             ORDER[1],
             ANGLEUNIT["degree",0.0174532925199433]],
         AXIS["geodetic longitude (Lon)",east,
             ORDER[2],
             ANGLEUNIT["degree",0.0174532925199433]],
     USAGE[
         SCOPE["Horizontal component of 3D system."],
         AREA["World."],
         BBOX[-90,-180,90,180]],
     ID["EPSG",4326]]
Data axis to CRS axis mapping: 2,1
Geometry Column = GEOMETRY
TimeStamp: String (0.0)
OGRFeature(SELECT):0
   TimeStamp (String) = 2013-06-20T17:00:29Z
   POINT (11.3157 -5.3703)

The trick is to use the SQLITE dialect.
https://lists.osgeo.org/pipermail/gdal-dev/2022-October/056318.html

But in my mapfile I have something like this:

         CONNECTIONTYPE OGR
         CONNECTION "/data/mapserver/"
         DATA "select * from flatgeobuf_demo where unixepoch(TimeStamp) 
between unixepoch('%time%')-300 and unixepoch('%time%')"

But this does not work and I assume this is because OGR does not use the 
SQLITE dialect.

So my question is: Is it possible the tell OGR to use the SQLITE 
dialect?

My mapserver log is (from a docker container with mapserver  v 7.6.x and 
GDAL 3.5.1)
[Wed Oct 19 18:40:28 2022].5067, 12230.1280: GDAL: 
GDALOpen(/data/mapserver/, this=0x56387af83b60) succeeds as FlatGeobuf.
[Wed Oct 19 18:40:28 2022].5068, 12230.1281: GDAL: 
GDALClose(/data/mapserver/, this=0x56387af83b60)
172.17.0.1 - - [19/Oct/2022:18:40:28 +0000] "GET 
/cgi-bin/mapserv?map=/mapfile/mapfile.map&SERVICE=WMS&VERSION=1.3.0&REQUEST=GetMap&BBOX=-20037508.34278924391,-39783385.7486210987,20037508.34278925508,44927335.42709603906&CRS=EPSG:3857&WIDTH=291&HEIGHT=614&LAYERS=li_flatgeobuf_demo&STYLES=&TIME=2013-06-20T17:00:00Z&FORMAT=image/png&DPI=96&MAP_RESOLUTION=96&FORMAT_OPTIONS=dpi:96&TRANSPARENT=TRUE 
HTTP/1.1" 200 802 "-" "curl/7.83.1"

returning from a curl:
curl 
'http://localhost:8080/cgi-bin/mapserv?map=/mapfile/mapfile.map&SERVICE=WMS&VERSION=1.3.0&REQUEST=GetMap&BBOX=-20037508.34278924391,-39783385.7486210987,20037508.34278925508,44927335.42709603906&CRS=EPSG:3857&WIDTH=291&HEIGHT=614&LAYERS=li_flatgeobuf_demo&STYLES=&TIME=2013-06-20T17:00:00Z&FORMAT=image/png&DPI=96&MAP_RESOLUTION=96&FORMAT_OPTIONS=dpi:96&TRANSPARENT=TRUE'
<?xml version='1.0' encoding="UTF-8" standalone="no" ?>
<ServiceExceptionReport version="1.3.0" 
xmlns="http://www.opengis.net/ogc" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
xsi:schemaLocation="http://www.opengis.net/ogc 
http://schemas.opengis.net/wms/1.3.0/exceptions_1_3_0.xsd">
<ServiceException>
msDrawMap(): Image handling error. Failed to draw layer named 
'li_flatgeobuf_demo'.
msOGRFileOpen(): OGR error. ExecuteSQL() failed. Check server logs.
</ServiceException>
</ServiceExceptionReport>


Trygve Aspenes