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?