Can SpatiaLite Indexes be used from views?

Question

I'm working with a large SpatiaLite database and trying to take advantage of some spatial indexes I have on my tables that I'm attempting to access through views.

Essentially, I have a number of views which each query multiple spatial tables and union the results. Before I tried using the views, I was running queries against the tables themselves and was able to use the spatial indexes in a similar manner as follows:

SELECT lc1.lc_name AS "Local Council",
  lc2.lc_name AS "Neighour"
FROM local_councils AS lc1,
  local_councils AS lc2
WHERE lc2.ROWID IN (
  SELECT pkid
    FROM idx_local_councils_geometry
    WHERE pkid MATCH RTreeIntersects(
      MbrMinX(lc1.geometry),
      MbrMinY(lc1.geometry),
      MbrMaxX(lc1.geometry),
      MbrMaxY(lc1.geometry)));

[From the spatialite cookbook]

As I understand it, this is the only way that spatial indexes can be used (i.e., not implicitly) due to a SQLite limitation. If this is the case, then am I correct that views cannot take advantage of spatial indexes? I would imagine that the bounding box information would have to be hard coded into the queries in the view or entered dynamically somehow when the query against the view is made.

Am I missing anything or are these assumptions correct and my best option is to stick with my individual queries against the tables themselves?

Answer 1

You can use indexes on a spatial view, but you probably don't want to use RTreeIntersects. Please update to using the search_frame = <MBR> approach.

There is a little demonstration in Sandro's "Switching to 4.0" document:

SELECT *
FROM items_view
WHERE ROWID IN (
    SELECT ROWID
    FROM SpatialIndex
    WHERE f_table_name = 'items_view' AND f_geometry_column = 'Geometry' AND
        search_frame = BuildMbr(11, 42, 12, 43, 4326) ); 

If you are still on a 3.x release then I'd strongly suggest upgrading, but it will work if you can "look through" the view to the underlying tables:

SELECT *
FROM items_view
WHERE ROWID IN (
    SELECT ROWID
    FROM SpatialIndex
    WHERE f_table_name = 'items' AND f_geometry_column = 'geom' AND
        search_frame = BuildMbr(11, 42, 12, 43, 4326) ); 

Also, a final note: indexes are important in some situations, but for many purposes (small tables of say only a few thousand entries), you simply don't need them.