[mapserver-users] Mapserver unnecessary slow with Spatialite

[Thiago Tiedtke dos Reis]

Hi Jukka  / Everybody,

Recently, I have noticed this behavior in this combination:
Spatialite+Mapserver

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.

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).

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.

If someone can contribute in this combination: Mapserver+Spatialite(via
Gdal), this will be very useful.

When I finalize my ambients and tests, I'll return here with some values and
times

Thanks in advance!

Eng. Msc. Thiago Tiedtke dos Reis
Cartographic Engineer
Research & Development Depto
Engemap Geoinformation - Brazil
www.engemap.com.br

2011/8/28 Rahkonen Jukka <Jukka.Rahkonen at mmmtike.fi>

> 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 at lists.osgeo.org
> http://lists.osgeo.org/mailman/listinfo/mapserver-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.osgeo.org/pipermail/mapserver-users/attachments/20110828/1dda6437/attachment-0001.html