[mapserver-users] WFS query slow on large postgis table (and small table disappointing as well)

[deduikertjes]

Hi Steve, List,

Hmm, I compiled mapserver from sources (GitHub Master). Actually that 
was not that bad, all tough I did not find out how to run the mapserver 
from ubuntu packages next to the one I compiled myself.

The new mapserver is working ... a bit.
The new mapserver doesn't support custom dimensions (yet ?) which I do 
use. So, for now I've removed that from the client side requests and 
getmap requests are fine .

Then do a simple query like:

    <wfs:GetFeature xmlns:wfs="http://www.opengis.net/wfs" service="WFS"
    version="1.1.0" xsi:schemaLocation="http://www.opengis.net/wfs
    http://schemas.opengis.net/wfs/1.1.0/wfs.xsd"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
         <wfs:Query typeName="hb:jan2014_high" srsName="EPSG:900913"
    xmlns:hb="http://maps.md2.com/schemas/hb_wfs.xsd">
             <ogc:Filter xmlns:ogc="http://www.opengis.net/ogc">
                 <ogc:Intersects>
    <ogc:PropertyName></ogc:PropertyName>
                            <gml:Polygon
    xmlns:gml="http://www.opengis.net/gml" srsName="EPSG:900913">
                                 <gml:exterior>
                                 <gml:LinearRing>
                                 <gml:posList>493014.59839229
    6795936.8942158 493014.59839229 6796022.885872601 493102.97870623
    6796022.885872601                         493102.97870623
    6795936.8942158 493014.59839229 6795936.8942158</gml:posList>
                                 </gml:LinearRing>
                                 </gml:exterior>
                         </gml:Polygon>
             </ogc:Intersects>
         </ogc:Filter>
    </wfs:Query>
    </wfs:GetFeature>


Resulting in an error:
msWFSGetFeature(): WFS server error. Invalid or Unsupported FILTER in 
GetFeature : <Filter ogc="http://www.opengis.net/ogc"> <Intersects> 
<PropertyName /> <Polygon gml="http://www.opengis.net/gml" 
srsName="EPSG:900913"> <exterior> <LinearRing> <posList>492858.14135005 
6795805.518073499 492858.14135005 6796116.0435008 493197.33066301 
6796116.0435008 493197.33066301 6795805.518073499 492858.14135005 
6795805.518073499</posList> </LinearRing> </exterior> </Polygon> 
</Intersects> </Filter>
(Mapserver 6.4 accepts this query and returns a result (albeit taking 
long)).

A simple query like this works:
<wfs:GetFeature xmlns:wfs="http://www.opengis.net/wfs" service="WFS" 
version="1.1.0" xsi:schemaLocation="http://www.opengis.net/wfs 
http://schemas.opengis.net/wfs/1.1.0/wfs.xsd 
http://maps.md2.com/schemas/hb_wfs.xsd 
http://xxxxx/maps/xxxx/rc1/services/basic/default.map?service=WFS&version=1.1.0&request=DescribeFeatureType&typeName=jan2014_high" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<wfs:Query typeName="feature:jan2014_high" srsName="EPSG:900913">
<ogc:Filter xmlns:ogc="http://www.opengis.net/ogc">
<ogc:And>
<ogc:BBOX>
<ogc:PropertyName>msGeometry</ogc:PropertyName>
<gml:Envelope xmlns:gml="http://www.opengis.net/gml" srsName="EPSG:900913">
<gml:lowerCorner>491667.6470239 6795223.3576443</gml:lowerCorner>
<gml:upperCorner>494192.4576141 6796212.2616977</gml:upperCorner>
</gml:Envelope>
</ogc:BBOX>
<ogc:PropertyIsLessThan>
<ogc:PropertyName>value</ogc:PropertyName>
<ogc:Literal>0</ogc:Literal>
</ogc:PropertyIsLessThan>
</ogc:And>
</ogc:Filter>
</wfs:Query>
</wfs:GetFeature>

but that one was already reasonably fast.

Any pointers very much appreciated.

MArco



On 03-11-14 19:06, Lime, Steve D (MNIT) wrote:
> MapServer 7.0 should fix issues like this. A more general solution to the one presented in #4049 has been added to that version. You might try the development version and see how it goes.  I'm surprised simple queries aren't working well though since older versions of MapServer have optimizations specific to PostGIS for those simple cases.
>
> Steve
>
> -----Original Message-----
> From: mapserver-users-bounces at lists.osgeo.org [mailto:mapserver-users-bounces at lists.osgeo.org] On Behalf Of deduikertjes
> Sent: Monday, November 03, 2014 9:56 AM
> To: mapserver-users at lists.osgeo.org
> Subject: [mapserver-users] WFS query slow on large postgis table (and small table disappointing as well)
>
> Hi List,
>
> I'm experiencing real slow performance when doing WFS queries on a postgis table with about 1500000 records.
>
> A simple spatial query (which features in a box) takes about 1 to 2 minutes.
> In postgis the same query takes less than a second. Same story for attribute queries.
> Indeed on a postgis table with some 60000 records things are still very very slow.
>
> WMS on the other hand is flying taking less than a second for rendering most tiles.
>
> By using the debug functions I see that mapserver fetches all features in the map extent (set at map level) and then starts filtering feature by feature which takes a long time.
>
> I found ticket http://trac.osgeo.org/mapserver/ticket/4049 which seems to be related.
>
> Is there any way to speed up this kind of WFS queries? Is there any progress on the ticket?
>
> Any help appreciated, MArco
>
>
>
> --
> View this message in context: http://osgeo-org.1560.x6.nabble.com/WFS-query-slow-on-large-postgis-table-and-small-table-disappointing-as-well-tp5170988.html
> Sent from the Mapserver - User mailing list archive at Nabble.com.
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/mapserver-users/attachments/20141104/2627eb8e/attachment.htm>