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

[Rahkonen Jukka (MML)]

Even Rouault wrote:
Re: [mapserver-users] How to know when WFS can utilize native SQL for fitering?

>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).
 
I know that I could have better control over the SQL by using %variables% inside DATA, but stored queries are used in WFS and INSPIRE and DescribeStoredQueries is also quite a nice query from end users point of view.  Perhaps there could be an option for mapping the stored query parameters to variables which are used in the mapfile. That would also give a possibility to validate the variables of the stored queries. And Oracle specialists could use fine hints for boosting spatial queries if the Oracle optimizer thinks, as it sometimes do, that using spatial index last is a good idea.
 

>> 
>> 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.

-Jukka-

>Even
 
-- 
Spatialys - Geospatial professional services
http://www.spatialys.com