[MapServer-users] Possible to pass sql dialect when querying a flatgeobuf?
Even Rouault
even.rouault at spatialys.com
Wed Oct 19 12:35:26 PDT 2022
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 at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/mapserver-users
--
http://www.spatialys.com
My software is free, but my time generally not.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20221019/3f1196f8/attachment.htm>
More information about the MapServer-users
mailing list