Is Spatialite Really Slow?

Question

I have a couple of thousand polygons in SpatiaLite. I am trying to do a "touches" query:

select map1.* from map1,map2
where touches(map1."Geometry",map2."Geometry")

and wow, is it SLOW!

However, if I ask it to just do it for one parcel in map1, it runs really fast.

select map1.* from map1,map2
where touches(map1."Geometry",map2."Geometry")
and map1."ROWID" = 753

I do expect that the first query will run slower, but it is amazingly slow. It runs very fast in SQLServer, Manifold GIS, and PostGIS. Is Spatialite just really inefficient?

Answer 1

No, SpatiaLite isn't that slow, you just need to use a spatial index. Due to limitations in the SQLite design, using a spatial index in a query isn't as invisible as it is in PostGIS.

Here is an example modified from the SpatiaLite Cookbook http://www.gaia-gis.it/spatialite-3.0.0-BETA/spatialite-cookbook/html/neighbours.html

After creating a spatial index on your polygon data sets

    SELECT map1.*
      FROM map1, map2
     WHERE ST_Touches(map1.geometry, map2.geometry)
       AND map2.ROWID IN (
           SELECT pkid
             FROM idx_map1_geometry
            WHERE pkid MATCH RTreeIntersects(
                  MbrMinX(map1.geometry),
                  MbrMinY(map1.geometry),
                  MbrMaxX(map1.geometry),
                  MbrMaxY(map1.geometry)));

Answer 2

In Eric Westra's book 'Python Geospatial Development' page 188 shows that for the CONTAINS operation at least Spatialite can, perhaps suprisingly, run faster than MySQL and PostGIS - if the involved spatial indexing procedure is followed.

Answer 3

I wrote a blog about this a while back. See http://www.frogmouth.net/blog/?p=23

Micha also wrote an interesting blog on this topic.