[mapserver-users] Spatialite and spatial index?

[Sven Geggus]

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.

Sven

-- 
"Thinking of using NT for your critical apps?
                                  Isn't there enough suffering in the world?"
                   (Advertisement of Sun Microsystems in Wall Street Journal)
/me is giggls at ircnet, http://sven.gegg.us/ on the Web