[mapserver-users] Spatialite and spatial index?

[Rahkonen Jukka]

Sven Geggus wrote:
> 
> Rahkonen Jukka <jukka.rahkonen at mmmtike.fi> wrote:
> 
> > GDAL has been supporting Spatialite spatial index since
> > http://trac.osgeo.org/gdal/ticket/4212 Some further work was done in
> > http://trac.osgeo.org/gdal/ticket/4508 I am remembering from my own
> > experience that a query like the one you are using needs GDAL 1.10 but
> > with that version the query should be fast. What GDAL version do you have?
> 
> 1.9.0 from Debian 7.1. Unfortunately neither Debian nor Ubuntu have 1.10.x yet
> and I usually don't like to manually build and install such an essential library.
 
> > I guess that you have already checked that you really have a valid
> > spatial index with CheckSpatialIndex SQL function.
> 
> Yes, a query like this works fine and fast:
> 
> SELECT geometry,
>        ogc_fid,
>        height
> FROM   contours
> WHERE  ogc_fid IN (SELECT pkid
>                    FROM   "idx_contours_geometry"
>                    WHERE  xmax >=- 1931114.875
>                           AND xmin <=- 1903377.5
>                           AND ymax >= 3251495.5
>                           AND ymin <= 3276423.5);
> 
> > I am also remembering that if a huge Spatialite table is built little
> > by little for example by appending separate shapefiles into one common
> > table then the result may be slow even if there is a valid spatial
> > index because the SQLite data file gets so badly fragmented. Running
> > VACUUM may have a great effect in such case.
> 
> I build the sqlite database directly from postgis using ogr2ogr.
> 
> I consider this spatial index thing a mapserver Issue because Index usage seems
> to work to some extend using mapnik.

Hi,

I consider it is not a Mapserver issue at all because Mapserver is reading Spatialite through GDAL/OGR.  Using the spatial index may be suboptimally implemented in GDAL 1.9 but if it works well with GDAL 1.10 as I suppose it does then what we have left is a Debian/Ubuntu packaging issue. 

I encourage you to have a try with GDAL 1.10 because I believe that for your use case Spatialite will be considerable faster than PostGIS. Do not forget to create an index for "height", though. If you can tolerate Windows the builds from http://gisinternals.com/sdk/ should work well. 

Mapnik does utilize Spatialite spatial index. If I remember right it used to do it by using a slightly wrong logic but I had a quick look on the code in github https://github.com/mapnik/mapnik/tree/master/plugins/input/sqlite and there has been many new commits recently and spatial index subquery looks good now.  Mapnik also creates automatically an external database with spatial index tables and makes Spatialite to use that as an attached database if the main database is missing the index. I am not sure if it is especially efficient to do it that way and for sure it will make trouble if the main database is updated so that the rowids are changing. But Mapnik does not want to be slow so perhaps this system is also fast enough.

-Jukka Rahkonen-