[mapserver-users] How to know when WFS can utilize native SQL for fitering?

[Even Rouault]

On mercredi 21 décembre 2016 15:51:36 CET Rahkonen Jukka (MML) wrote:
> Hi,
> 
> Is there any way to know how Mapserver is going react to WFS GetFeature
> request with filters? It would be good to know if it is going to build some
> native SQL or if it decides to read everything from the database and filter
> the result on server .

No, there's no way to know it. But the issue is rather specific to the OGR backend.

> 
> I have made an "idrangequery" stored query which should be very fast if it
> would create a SQL query
> 
> SELECT ... FROM ... WHERE query_id BETWEEN ${lower} AND ${upper}
> 
> The stored query does not give errors but I can't get any data our from my
> biggish GeoPackage db. The error from the ms_errorfile is:
> 
> msConnPoolClose(): General error message. Closing connection
> /users/wms/data/vektorit/kiinteistot/palsta.gpkg even though ref_count=1.
> 
> It feels like Mapserver is trying to read the whole table and filter after
> that but there are two million polygons in the table, worth 4.5 gigabytes,
> and result is timeout.

Yes, currently translation from MapServer expressions (which is how WFS filters are 
translated in an intermediate way) to SQL in the OGR backend is only done for Spatialite (or 
PostGIS if using OGR/PostGIS). Otherwise this will result in MapServer filtering. Only BBOX 
filtering is transmitted to OGR in the general case. This has probably regressed w.r.t the pre 
7.0 situation since the OGR provider was not fully updated to support the changes of
http://mapserver.org/development/rfc/ms-rfc-91.html

It is very likely that I will have the opportunity to improve the situation for other OGR drivers 
as well in the next weeks (with some limitations, depending on what can be translated as 
OGR SQL).

> 
> My stored query is below. I have managed to get some data with it by
> limiting the amount of data with COUNT:
> service=WFS&version=2.0.0&REQUEST=GetFeature&STOREDQUERY_ID=idrangequery&lo
> wer=1&upper=100&COUNT=2
> 
> Am I out of luck generally or is it just GeoPackage that makes problem?
> Could I have better possibilities for success with PostGIS?

Yes, PostGIS (through dedicated provider at least) should be fine.

Even

-- 
Spatialys - Geospatial professional services
http://www.spatialys.com
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20161221/2712fe5f/attachment-0001.html>