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

[Rahkonen Jukka]

Even Rouault wrote:
> 
> 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.

I prepared a new Spatialite database from the OSM data of Finland. It contains 230000 points, 480000 lines and 440000 polygons and takes 700 MB on a disk. I made indexes for everything that is used in the WHERE selections and the result is just wonderful.  If there is just few features to render the msDrawMap() takes just 0.01 - 0.10 seconds per layer (1000x800 sized output). And my computer is eight year old Windows WP with IDE disks and 1 GB of memory. Rendering lots of features takes naturally longer time but I do believe that technically GDAL/OGR/Mapserver side is in the order now and there is not much room for further improvements there.

Best way for improving the speed of the service from this point is to generalize the data for rendering at small scales. Spatialite has Simplify() and SimplifyPreserveTopolygy() functions so it will be very easy to create simplified versions of the data into the same database. Features which are unreasonably small for rendering at the target scale can be sorted out at the same. I can imagine that one day some clever individual will write a tool that automatically runs SQL script which creates a set of new tables with simplified geometries into Spatialite and at the same time writes a corresponding mapfile which collects those tables as scale dependent layers under one Mapserver layer group.  

By the way, it would be very nice if the full stop at TopologyExceptions could be avoided. My Spatialite databases are created by ogr2ogr and self-intersections did not stop OGR then so perhaps it can be made to tolerate them also in this case. Now I fixed this problem in a crude way by running "DELETE from osm_polygon where ST_IsValid(GEOMETRY)<=0".

-Jukka Rahkonen-