Hi Jukka / Everybody,<br><br>Recently, I have noticed this behavior in this combination: Spatialite+Mapserver<br><br>In some tests with a large database containing roads and hidrography of Brazil, this is very noticeable, even with use of MBR cache or Spatial Index.<br>
<br>I converted the same database to shapefile (esri), and the renderization in mapserver of this format is always more fast (sorry, I don't have times or comparations, but only tests in bench) than the spatialite (IMHO, the spatialite must "filter" more fast than shapefile, due features like spatial index and advanced structures of libspatialite+sqlite).<br>
<br>I'll preparing two full ambients (windows 7 32bit and windows 7 64bit) to compile all packages and dependencies of mapserver, spatialite, gdal, etc ....using the GCC TDM, to do more precisely tests with GDAL >= 1.8, mapserver (svn) and spatialite 3.0.0.<br>
<br>If someone can contribute in this combination: Mapserver+Spatialite(via Gdal), this will be very useful.<br><br>When I finalize my ambients and tests, I'll return here with some values and times<br><br>Thanks in advance!<br>
<br>Eng. Msc. Thiago Tiedtke dos Reis<br>Cartographic Engineer<br>Research & Development Depto<br>Engemap Geoinformation - Brazil<br><a href="http://www.engemap.com.br">www.engemap.com.br</a><br><br><div class="gmail_quote">
2011/8/28 Rahkonen Jukka <span dir="ltr"><<a href="mailto:Jukka.Rahkonen@mmmtike.fi">Jukka.Rahkonen@mmmtike.fi</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
Hi,<br>
<br>
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.<br>
<br>
This query comes basically from OGR Spatialite driver page <a href="http://www.gdal.org/ogr/drv_sqlite.html" target="_blank">http://www.gdal.org/ogr/drv_sqlite.html</a><br>
<br>
SELECT geometry, osm_id, highway, ref, name, tunnel<br>
FROM osm_line<br>
WHERE highway IS NOT NULL AND<br>
MBRIntersects(geometry,<br>
BuildMBR(1487400, 6894200, 1487500, 6894300))<br>
ORDER BY z_order;<br>
<br>
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.<br>
<br>
Alesandro wrote:<br>
" Berlin dataset, your original SQL: I've simply adjusted<br>
the MBR coords.<br>
Execution time: 397 millis<br>
Please note: this layer contains very few rows (< 100k):<br>
so not using the Spatial Index imposes a very bland (but<br>
still noticeable) overhead.<br>
<br>
=================<br>
<br>
SELECT geometry, osm_id, highway,ref, name, tunnel<br>
FROM osm_line<br>
WHERE highway IS NOT NULL AND ROWID IN (<br>
SELECT pkid FROM idx_osm_line_GEOMETRY<br>
WHERE xmax > 1487400 AND xmin < 1487500<br>
AND ymax > 6894200 AND ymin < 6894300)<br>
ORDER BY z_order;<br>
<br>
Same query: this time using the Spatial Index.<br>
Execution time: 17 millis ... really a lot better :-D<br>
and if applied to some larger dataset (> 1 million rows)<br>
the difference will become absolutely dramatic, as you<br>
are already experiencing in the Finland test case.<br>
<br>
SpatiaLite isn't PostGIS: you *must* explicitly write<br>
your SQL Queries in such a way to access the corresponding<br>
Spatial Index table as appropriate.<br>
<br>
Please note well: in SQLite/SpatiaLite the R*Tree<br>
Spatial Index simply is another table between many<br>
others.<br>
The SQL engine has absolutely no idea that a strict<br>
correlation exists between the "geometry" table and<br>
the corresponding R*Tree.<br>
So you are explicitly required to define an explicit<br>
sub-query in order to inquiry the R*Tree. "<br>
<br>
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?<br>
<font color="#888888"><br>
-Jukka Rahkonen-_______________________________________________<br>
mapserver-users mailing list<br>
<a href="mailto:mapserver-users@lists.osgeo.org">mapserver-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/mapserver-users" target="_blank">http://lists.osgeo.org/mailman/listinfo/mapserver-users</a><br>
</font></blockquote></div><br>