[mapserver-users] Mapserver unnecessary slow with Spatialite

[Rahkonen Jukka]

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-