<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Trygve,</p>
<p>you may want to change</p>
<p>- line
<a class="moz-txt-link-freetext" href="https://github.com/MapServer/MapServer/blob/698a4407265cce9db322a34e21e279e57e9746f8/mapogr.cpp#L1265">https://github.com/MapServer/MapServer/blob/698a4407265cce9db322a34e21e279e57e9746f8/mapogr.cpp#L1265</a></p>
<p>from</p>
<p> hLayer = <span class="pl-c1">OGR_DS_ExecuteSQL</span>( hDS,
pszLayerDef, <span class="pl-c1">NULL</span>, <span
class="pl-c1">NULL</span> );</p>
<p>to</p>
<p> hLayer = <span class="pl-c1">OGR_DS_ExecuteSQL</span>( hDS,
pszLayerDef, <span class="pl-c1">NULL</span>, "SQLITE" );</p>
<p>and<br>
</p>
<p>- line
<a class="moz-txt-link-freetext" href="https://github.com/MapServer/MapServer/blob/698a4407265cce9db322a34e21e279e57e9746f8/mapogr.cpp#L2569">https://github.com/MapServer/MapServer/blob/698a4407265cce9db322a34e21e279e57e9746f8/mapogr.cpp#L2569</a></p>
<p>from<br>
</p>
<p> psInfo-><span class="pl-smi">hLayer</span> = <span
class="pl-c1">OGR_DS_ExecuteSQL</span>( psInfo-><span
class="pl-smi">hDS</span>, <span class="pl-c1">select</span>,
hGeom, <span class="pl-c1">NULL</span> );</p>
<p>to</p>
<p> psInfo-><span class="pl-smi">hLayer</span> = <span
class="pl-c1">OGR_DS_ExecuteSQL</span>( psInfo-><span
class="pl-smi">hDS</span>, <span class="pl-c1">select</span>,
hGeom, "SQLITE" );</p>
<p><br>
</p>
<p>A processing option should be added to control this behavior in a
clean way.</p>
<p><br>
</p>
<p>The alternative is to convert your flatgeobuf file to Spatialite
or GPKG, which natively speak the SQLITE dialect.<br>
</p>
<p>Even<br>
</p>
<p><br>
</p>
<div class="moz-cite-prefix">Le 19/10/2022 à 20:48, Trygve Aspenes a
écrit :<br>
</div>
<blockquote type="cite"
cite="mid:52d141a373839726d0ba4122cf77a28c@aspenes.priv.no">Hi
<br>
I have a flatgeobuf file ( or files) and need to filter by time
using the time from the query.
<br>
<br>
When I test on command line using ogrinfo I get something like
this:
<br>
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/*
<br>
INFO: Open of
`/data/mapserver/tile-index/points-20130620070000.fgb'
<br>
using driver `FlatGeobuf' successful.
<br>
layer names ignored in combination with -sql.
<br>
<br>
Layer name: SELECT
<br>
Geometry: Point
<br>
Feature Count: 17
<br>
Extent: (7.254900, -6.423300) - (58.263300, 51.656400)
<br>
Layer SRS WKT:
<br>
GEOGCRS["WGS 84",
<br>
ENSEMBLE["World Geodetic System 1984 ensemble",
<br>
MEMBER["World Geodetic System 1984 (Transit)"],
<br>
MEMBER["World Geodetic System 1984 (G730)"],
<br>
MEMBER["World Geodetic System 1984 (G873)"],
<br>
MEMBER["World Geodetic System 1984 (G1150)"],
<br>
MEMBER["World Geodetic System 1984 (G1674)"],
<br>
MEMBER["World Geodetic System 1984 (G1762)"],
<br>
MEMBER["World Geodetic System 1984 (G2139)"],
<br>
ELLIPSOID["WGS 84",6378137,298.257223563,
<br>
LENGTHUNIT["metre",1]],
<br>
ENSEMBLEACCURACY[2.0]],
<br>
PRIMEM["Greenwich",0,
<br>
ANGLEUNIT["degree",0.0174532925199433]],
<br>
CS[ellipsoidal,2],
<br>
AXIS["geodetic latitude (Lat)",north,
<br>
ORDER[1],
<br>
ANGLEUNIT["degree",0.0174532925199433]],
<br>
AXIS["geodetic longitude (Lon)",east,
<br>
ORDER[2],
<br>
ANGLEUNIT["degree",0.0174532925199433]],
<br>
USAGE[
<br>
SCOPE["Horizontal component of 3D system."],
<br>
AREA["World."],
<br>
BBOX[-90,-180,90,180]],
<br>
ID["EPSG",4326]]
<br>
Data axis to CRS axis mapping: 2,1
<br>
Geometry Column = GEOMETRY
<br>
TimeStamp: String (0.0)
<br>
OGRFeature(SELECT):0
<br>
TimeStamp (String) = 2013-06-20T17:00:29Z
<br>
POINT (11.3157 -5.3703)
<br>
<br>
The trick is to use the SQLITE dialect.
<br>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/pipermail/gdal-dev/2022-October/056318.html">https://lists.osgeo.org/pipermail/gdal-dev/2022-October/056318.html</a>
<br>
<br>
But in my mapfile I have something like this:
<br>
<br>
CONNECTIONTYPE OGR
<br>
CONNECTION "/data/mapserver/"
<br>
DATA "select * from flatgeobuf_demo where
unixepoch(TimeStamp) between unixepoch('%time%')-300 and
unixepoch('%time%')"
<br>
<br>
But this does not work and I assume this is because OGR does not
use the SQLITE dialect.
<br>
<br>
So my question is: Is it possible the tell OGR to use the SQLITE
dialect?
<br>
<br>
My mapserver log is (from a docker container with mapserver v
7.6.x and GDAL 3.5.1)
<br>
[Wed Oct 19 18:40:28 2022].5067, 12230.1280: GDAL:
GDALOpen(/data/mapserver/, this=0x56387af83b60) succeeds as
FlatGeobuf.
<br>
[Wed Oct 19 18:40:28 2022].5068, 12230.1281: GDAL:
GDALClose(/data/mapserver/, this=0x56387af83b60)
<br>
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"
<br>
<br>
returning from a curl:
<br>
curl
'<a class="moz-txt-link-freetext" href="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">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</a>'<br>
<?xml version='1.0' encoding="UTF-8" standalone="no" ?>
<br>
<ServiceExceptionReport version="1.3.0"
xmlns=<a class="moz-txt-link-rfc2396E" href="http://www.opengis.net/ogc">"http://www.opengis.net/ogc"</a>
xmlns:xsi=<a class="moz-txt-link-rfc2396E" href="http://www.w3.org/2001/XMLSchema-instance">"http://www.w3.org/2001/XMLSchema-instance"</a>
xsi:schemaLocation=<a class="moz-txt-link-rfc2396E" href="http://www.opengis.net/ogchttp://schemas.opengis.net/wms/1.3.0/exceptions_1_3_0.xsd">"http://www.opengis.net/ogc
http://schemas.opengis.net/wms/1.3.0/exceptions_1_3_0.xsd"</a>>
<br>
<ServiceException>
<br>
msDrawMap(): Image handling error. Failed to draw layer named
'li_flatgeobuf_demo'.
<br>
msOGRFileOpen(): OGR error. ExecuteSQL() failed. Check server
logs.
<br>
</ServiceException>
<br>
</ServiceExceptionReport>
<br>
<br>
<br>
Trygve Aspenes
<br>
_______________________________________________
<br>
MapServer-users mailing list
<br>
<a class="moz-txt-link-abbreviated" href="mailto:MapServer-users@lists.osgeo.org">MapServer-users@lists.osgeo.org</a>
<br>
<a class="moz-txt-link-freetext" href="https://lists.osgeo.org/mailman/listinfo/mapserver-users">https://lists.osgeo.org/mailman/listinfo/mapserver-users</a>
<br>
</blockquote>
<pre class="moz-signature" cols="72">--
<a class="moz-txt-link-freetext" href="http://www.spatialys.com">http://www.spatialys.com</a>
My software is free, but my time generally not.</pre>
</body>
</html>