[MapServer-users] Possible to pass sql dialect when querying a flatgeobuf?
Trygve Aspenes
trygve at aspenes.priv.no
Wed Oct 19 22:28:53 PDT 2022
Even,
wow, thanks. I did had a look in the mapogr.cpp file and guessed some
changes needed to be added. But the code is far too complicated for me
to follow.
I don't have a setup now to compile the mapserver, so I better try out
Spatialite or GPKG.
However I googled a bit and found a hint using virtual layer like this:
CONNECTION "<OGRVRTDataSource>
<OGRVRTLayer name='li_flatgeobuf_demo'>
<SrcDataSource>/data/mapserver/</SrcDataSource>
<SrcSQL dialect='sqlite'>select * from li_flatgeobuf_demo
where unixepoch(TimeStamp) between unixepoch('%time%')-300 and
unixepoch('%time%')</SrcSQL>
</OGRVRTLayer>
</OGRVRTDataSource>"
With this, mapserver works, at least no error messages are returned. But
I only get an empty image in return.
If a create a separate .ovf file to use with ogrinfo I get the same
(working but no data returned):
ogrinfo /flatgeobuf.ovf li_flatgeobuf_demo
INFO: Open of `/flatgeobuf.ovf'
using driver `OGR_VRT' successful.
Layer name: li_flatgeobuf_demo
Geometry: Point
Feature Count: 0
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)
with ovf file like this:
<OGRVRTDataSource>
<OGRVRTLayer name="li_flatgeobuf">
<SrcDataSource>/data/mapserver/</SrcDataSource>
<SrcSQL dialect='sqlite'>select * from li_flatgeobuf_demo where
unixepoch(TimeStamp) between unixepoch('2013-06-20T07:00:00Z')-30 and
unixepoch('2013-06-20T07:00:00Z')+60</SrcSQL>
</OGRVRTLayer>
</OGRVRTDataSource>
Well almost there, but nope.
Thanks for your time
Trygve Aspenes
Den 2022-10-19 21:35, skrev Even Rouault:
> 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" [1]
>> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" [2]
>> xsi:schemaLocation="http://www.opengis.net/ogc
>> http://schemas.opengis.net/wms/1.3.0/exceptions_1_3_0.xsd" [3]>
>> <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.
>
>
> Links:
> ------
> [1] http://www.opengis.net/ogc
> [2] http://www.w3.org/2001/XMLSchema-instance
> [3]
> http://www.opengis.net/ogchttp://schemas.opengis.net/wms/1.3.0/exceptions_1_3_0.xsd
More information about the MapServer-users
mailing list