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

[Rahkonen Jukka]

Rahkonen Jukka wrote:
> 
>  Even Rouault wrote:
> 
> >
> >> 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).
> 
> > Actually, I found a new improvement (committed in
> > http://trac.osgeo.org/gdal/changeset/23946) that make the 
> query time go down
> > to 0.07 sec (*) ! There was a slowness when opening the 
> layer due to the
> > presence of an ORDER BY clause in the DATA string, that was 
> evaluated before
> > setting the spatial filter.
> 
> > (*) For comparison, I translated your database to Postgis, 
> and set-up the
> > connection mapfile to use that Postgis database (still 
> through OGR, not the
> > MapServer Postgis backend). The same request with OGR PG is 
> run in 0.18 sec.
> 
> Awesome!  About 25 times faster in the evening than it was in 
> the morning. My opinion is that because Spatialite seems to 
> be, at least in this case, more that two times faster backend 
> for Mapserver than PostGIS it should lead to some intensive 
> and controlled testing now.

I updated the cgi-bin directory of my MS4W 3.0.4beta1 with the mapserv.exe and dll files taken from the brand new binaries from http://www.gisinternals.com/sdk/ (contains Mapserver and GDAL revisions r13144, r23972, respectively).

As a result Mapserver is really serving me 10, or 25 or even 40 times faster than it did before the update measured as WMS throughput (images/minute). The times include rendering times and all the lags in the system so the difference tells exactly how the end user feels it. Greatest enhancement is with close zooms because then the apatial index bites hardest. And at least with my computer Spatialite backend is clearly faster than PostGIS with the same data and it may be even faster than shapefiles but it is too early to say that really. But this is absolutely something worth more testing.

My test database is moderately sized and contains 100000 points, 90000 lines and 149000 polygons from OpenStreetMap data. I will myself do next trials with ten times bigger Spatialite database with something like million points, lines, and polygons. We do not have much bigger tables in our production. However, I do not believe we are changing from Oracle to SQLite/Spatialite very rapidly in our core business :)

-Jukka Rahkonen-