[mapserver-users] Does Mapserver utilise Spatialite spatial index correctly?

[Even Rouault]

Le samedi 11 février 2012 12:53:08, Even Rouault a écrit :
> Le samedi 11 février 2012 11:42:00, Rahkonen Jukka a écrit :
> > Hi,
> > 
> > Perhaps it is not the spatial index at all, or it is not the most
> > important part? It looks like OGR is using Spatialite through a
> > connection pool and creating a new connection and pool is pretty heavy. 
> > A first aid with a map with many layers is to use PROCESSING
> > "CLOSE_CONNECTION=DEFER" even when running Mapserver as cgi.  It can
> > save a couple of seconds per layer which makes a very big difference
> > with the OSM default WMS group (from 40 sec to 10 sec) . However,
> > "CLOSE_CONNECTION=DEFER" cannot help with the pretty slow initial time
> > but it would need some other kind of
> > optimisation.
> 
> Jukka,
> 
> I'm currently analyzing your test case. Currently, I'm just using the
> mapserv executable from command line and after several consecutive runs,
> it stabilizes around 1.7 second to run
> 
> REQUEST_METHOD=GET
> QUERY_STRING="map=/ms4w/osm_maps/osm_wms.map&REQUEST=GetMap&SERVICE=WMS&VER
> SION=1.1.1&WIDTH=663&HEIGHT=530&LAYERS=roadsclose_01&TRANSPARENT=TRUE&FORMA
> T=image%2Fpng&BBOX=1496457.8169701756,6889765.873308353,1497120.611899381,6
> 890295.70937544&SRS=EPSG:3857&STYLES=" mapserv > /dev/null
> 
> Not as bad as what you are seing, but I believe it could be made faster.
> For the record my machine is a Core i5 750 with 4GB RAM, Ubuntu 10.04
> 64bit.
> 
> I've found 2 main areas that are time inefficient :
> 
> 1) The opening of the DB needs some time to enumerate the layers.
> 
> 2) The spatial index is not taken into account with your mapfiles. This is
> due to using stuff like "DATA "select geometry, osm_id ,highway,ref, name,
> tunnel from osm_line where highway is not null order by z_order"" that
> causes OGR to return a 'select layer' and not a table layer. Table layer
> currently benefit from spatial index when SetSpatialFilter() is called on
> them, but select layers do not. So you end up enumerating all features
> returned by the SQL request in the DATA.
> 
> For 1), I'm looking if it wouldn't be possible for the OGR SQLite driver to
> defer that enumeration
> For 2), I'm looking if it wouldn't be possible for the OGR SQLite driver to
> modify the provided SQL request to insert in it the spatial filter, at
> least in simple cases (no join, no union).

I've commited improvements in GDAL trunk for both points ( 
http://trac.osgeo.org/gdal/changeset/23944 and 
http://trac.osgeo.org/gdal/changeset/23945 ) that make the above request go 
from 1.7 sec to 0.54 sec . I don't think there's any more significant speed 
gain to expect now (at least on OGR side).

> 
> Even
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users