Trygve,
you may want to change
- line
https://github.com/MapServer/MapServer/blob/698a4407265cce9db322a34e21e279e57e9746f8/mapogr.cpp#L1265
from
hLayer = OGR_DS_ExecuteSQL( hDS, pszLayerDef, NULL, NULL );
to
hLayer = OGR_DS_ExecuteSQL( hDS, pszLayerDef, NULL, "SQLITE" );
and
- line
https://github.com/MapServer/MapServer/blob/698a4407265cce9db322a34e21e279e57e9746f8/mapogr.cpp#L2569
from
psInfo->hLayer = OGR_DS_ExecuteSQL( psInfo->hDS, select, hGeom, NULL );
to
psInfo->hLayer = OGR_DS_ExecuteSQL( psInfo->hDS, select, hGeom, "SQLITE" );
A processing option should be added to control this behavior in a clean way.
The alternative is to convert your flatgeobuf file to Spatialite or
GPKG, which natively speak the SQLITE dialect.
Even
Le 19/10/2022 à 20:48, Trygve Aspenes a écrit :
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
_______________________________________________
MapServer-users mailing list
MapServer-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users
--
http://www.spatialys.com
My software is free, but my time generally not.
_______________________________________________
MapServer-users mailing list
MapServer-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/mapserver-users
