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

[Rahkonen Jukka (MML)]

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 .

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.

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&lower=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?

My stored query is below.

<?xml version="1.0" encoding="UTF-8"?>
<StoredQueryDescription xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:gml="http://www.opengis.net/gml/3.2" id="idrangequery">
<Title>ID range query</Title>
<Abstract>ID range abstract</Abstract>
<Parameter name="lower" type="xs:integer"/>
<Parameter name="upper" type="xs:integer"/>
<QueryExpressionText isPrivate="false" language="urn:ogc:def:queryLanguage:OGC-WFS::WFS_QueryExpression" xmlns:ms="http://mapserver.gis.umn.edu/mapserver" returnFeatureTypes="ms:palstat">
<Query xmlns:fes="http://www.opengis.net/fes/2.0" typeNames="ms:palstat">
<fes:Filter>
<fes:PropertyIsBetween>
<fes:PropertyName>ms:query_id</fes:PropertyName>
<fes:LowerBoundary><fes:Literal>${lower}</fes:Literal></fes:LowerBoundary>
<fes:UpperBoundary><fes:Literal>${upper}</fes:Literal></fes:UpperBoundary>
</fes:PropertyIsBetween>
</fes:Filter>
</Query>
</QueryExpressionText>
</StoredQueryDescription>


-Jukka Rahkonen-
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20161221/a58ec270/attachment-0001.html>