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

[Even Rouault]

Le mardi 14 février 2012 09:24:15, Rahkonen Jukka a écrit :
> 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.

I tested a bit with shapefiles with your Berlin OSM database. Even after adding 
spatial index (.qix) and attribute index (.idm & .idn) on fields in where 
clauses, it is a bit slower than spatialite. If you remove all columns from 
the DBF that are not necessary for the rendering, it improves things a bit 
again. You can also try the new special SQL command "RESIZE table_name" for 
shapefiles I've committed yesterday (see http://gdal.org/ogr/drv_shapefile.html 
) to adjust columns to their minimum needed size.

> 
> 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-
> 
> _______________________________________________
> mapserver-users mailing list
> mapserver-users at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users