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

[Even Rouault]

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&VERSION=1.1.1&WIDTH=663&HEIGHT=530&LAYERS=roadsclose_01&TRANSPARENT=TRUE&FORMAT=image%2Fpng&BBOX=1496457.8169701756,6889765.873308353,1497120.611899381,6890295.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).

Even