10

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?

3
  • 9
    See here for some tests on the speed of spatialite - it suggests a 200-fold speed increase for an ST_Intersects operation on a large dataset IF you use indexes!
    – Simbamangu
    Commented Oct 2, 2012 at 4:51
  • thanks for the link Fezter. The only problem with that example was that he had to write extra SQL code to include a bounding box (and, he had to force feed it the envelope). It would be nice if the next version of spatialite would simply make use of the spatial indexes that are already there.
    – ajl
    Commented Oct 10, 2012 at 12:46
  • Welcome to gis.stackexchange.com! The format for this site implies that answers posted should be answers to the original question. When responding to an answer or comment it is best to make it a comment.
    – Sean
    Commented Oct 10, 2012 at 13:37

3 Answers 3

17

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)));
3
  • DavidF: thanks for your answer. That will definitely speed things up. Its too bad that the spatial operations don't implicitly use the spatial index. However, I suppose the last AND clause could be tacked on to any query one issues. Do you think that spatialite will one day support the spatial indexes implicitly?
    – user12319
    Commented Oct 29, 2012 at 13:37
  • My understanding is that the issue is inherent in SQLite's architecture. You can definitely post to the SpatiaLite Google Group with more questions though. groups.google.com/forum/?fromgroups#!forum/spatialite-users
    – DavidF
    Commented Oct 29, 2012 at 15:11
  • Note that the latest versions of Spatialite implement a Virtual Spatial Index, and the above syntax no longer works. The WHERE clause would be rewriten as WHERE map2.ROWID in ( SELECT ROWID from SpatialIndex WHERE f_table_name = 'map1' AND search_frame = map1.geometry) Commented Jan 10, 2014 at 8:46
4

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.

1
  • Not "surprisingly", as simple queries run about 2··3× faster in SQLite than they do in MySQL InnoDB engine. Commented Aug 1, 2018 at 20:22
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.