[MapServer-users] Possible to pass sql dialect when querying a flatgeobuf?
Trygve Aspenes
trygve at aspenes.priv.no
Wed Oct 19 11:48:14 PDT 2022
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
More information about the MapServer-users
mailing list