Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
8  
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 Oct 2 '12 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 Oct 10 '12 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 Oct 10 '12 at 13:37

3 Answers 3

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.

share|improve this answer

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)));
share|improve this answer
    
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 Oct 29 '12 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 Oct 29 '12 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) –  rudivonstaden Jan 10 at 8:46

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.

share|improve this answer

We're looking for long answers that provide some explanation and context. Don't just give a one-line answer; explain why your answer is right, ideally with citations. Answers that don't include explanations may be removed.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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