I've been doing some spatial queries using spatialite, and they're large enough that a spatial index becomes a must. An example of using an index is given in the tutorial
spatialite> SELECT NewRegions.Name, COUNT(*) FROM NewTowns, NewRegions WHERE NewRegions.Name IN ( 'VALLE D''AOSTA', 'PIEMONTE', 'UMBRIA', 'LOMBARDIA', 'CALABRIA', 'MOLISE', 'MARCHE', 'BASILICATA') AND Within(NewTowns.geom, NewRegions.Geometry) AND NewTowns.ROWID IN (SELECT pkid FROM idx_NewTowns_geom WHERE xmin > MbrMinX(NewRegions.Geometry) AND xmax < MbrMaxX(NewRegions.Geometry) AND ymin > MbrMinY(NewRegions.Geometry) AND ymax < MbrMaxY(NewRegions.Geometry)) GROUP BY NewRegions.Name;
This is a bit of a mouthful, and for
Within queries it's entirely redundant, it will always be
xmin > MbrMinX(NewRegions.Geometry)... Is there any way to do this query less verbosely?