Shorter Version of this SpatiaLite Query

Question

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?

Answer 1

I don't believe there is any shorter way of writing this query. Yes, you're right, there does appear to be redundancy on first sight, however I believe the idea is that the index subquery is evaluated first by the query optimiser. This then computes the subset of records where the query could possibly be true. This is a very quick operation because it's mathmatically simple: min x_mbr < point_x < max x_mbr AND min y_mbr < point_y < max Y_mbr. In addition it has an efficient RTree index to speed up data lookup.

The actual within operation has to carry out a point in polygon algorithm. If the polygon is very complicated this has the potential to take a significant amount of time because each vertex must be evaluated separately.

In this way, say a point is in Lazio (my geography of Italy isn't the best but bear with me), the query doesn't have to evaluate a whole load of point in polygon operations for every region in Italy, becuase chances are it will be outside the MBRs of the regions of interest, so the Within() function will not get called for that point. Which dramatically decreases the query time.

So if you want your query to run as quickly as possible, you must include the separate index clause, because the index is in a separate table. The tutorial you link to is fairly clear on this:

RTrees are only loosely integrated into the SQLite's query engine, so we need some extra care in order to use them. You can't assume SpatiaLite will automatically use some Spatial Index in order to speed up a slow query; you have to explicitly request this feature.