Hi, I have been playing a bit with Mapserver 6.0 and Spatialite and I was a bit surprised about how slow this combination is with any bigger Spatialite database. I asked for an opinion from Mr. Alesandro Furieri who wrote me some hints for making efficient queries which are utilising spatial index.
This query comes basically from OGR Spatialite driver page http://www.gdal.org/ogr/drv_sqlite.html SELECT geometry, osm_id, highway, ref, name, tunnel FROM osm_line WHERE highway IS NOT NULL AND MBRIntersects(geometry, BuildMBR(1487400, 6894200, 1487500, 6894300)) ORDER BY z_order; OGR documentation says that query is using spatial index. However, Alesandro writes that it does not and suggests another SQL which is 20 times faster. Alesandro wrote: " Berlin dataset, your original SQL: I've simply adjusted the MBR coords. Execution time: 397 millis Please note: this layer contains very few rows (< 100k): so not using the Spatial Index imposes a very bland (but still noticeable) overhead. ================= SELECT geometry, osm_id, highway,ref, name, tunnel FROM osm_line WHERE highway IS NOT NULL AND ROWID IN ( SELECT pkid FROM idx_osm_line_GEOMETRY WHERE xmax > 1487400 AND xmin < 1487500 AND ymax > 6894200 AND ymin < 6894300) ORDER BY z_order; Same query: this time using the Spatial Index. Execution time: 17 millis ... really a lot better :-D and if applied to some larger dataset (> 1 million rows) the difference will become absolutely dramatic, as you are already experiencing in the Finland test case. SpatiaLite isn't PostGIS: you *must* explicitly write your SQL Queries in such a way to access the corresponding Spatial Index table as appropriate. Please note well: in SQLite/SpatiaLite the R*Tree Spatial Index simply is another table between many others. The SQL engine has absolutely no idea that a strict correlation exists between the "geometry" table and the corresponding R*Tree. So you are explicitly required to define an explicit sub-query in order to inquiry the R*Tree. " It seems that Mapserver, or at least the version shipped with MS4W, does not use Spatialite in an efficient way. With big Spatialite tables and small bounding boxes when spatial index can effectively limit the query it should be possible to make Mapserver to render at least 10 times faster. Is anybody interested in having a look on this? Or is this perhaps more suitable question for gdal-dev mailing list? -Jukka Rahkonen-_______________________________________________ mapserver-users mailing list mapserver-users@lists.osgeo.org http://lists.osgeo.org/mailman/listinfo/mapserver-users