Spatial Index: best practicesThe latest versions of SpatiaLite (starting since version 3.0.0) introduced a new mechanism supporting the Spatial Index and based on VirtualSpatialIndex.
Please note well: always using this latest interface is the suggested best practice in order to query any Spatial Index. Just a short rationale accounting for this:
- old versions of SQLite suffered from rounding and truncation issues.
SpatiaLite internally store all coordinate values as double precision floating point numbers.
- The SQLite R*Tree on its own actually just supports single precision numbers.
- So it could eventually happen that some Geometries were wrongly excluded from a Spatial Index query due to truncation and rounding effects.
- VirtualSpatialIndex always take care to slightly extend by a little bit the filtering rectangle; so no Geometry will never badly lost.
- Eventually, more Geometries than strictly expected could be returned; but this never is an issue, because a Spatial Index query simply is a quick approximative spatial filter necessarily implying some more accurate refinement e.g. by using ST_Intersects().
- Last but not least: VirtualSpatialIndex is simplest and easiest to be used than any other alternative Spatial Index interface previously used.
SELECT * FROM com2011 AS c, prov2011 AS p WHERE ST_CoveredBy(c.geometry, p.geometry) = 1 AND nome_pro = 'AREZZO' AND c.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'com2011' AND search_frame = p.geometry);The above SQL snippet shows how to use an inner sub-query so to take full profit from a Spatial Index.
- SpatialIndex simply is a generic virtual table wrapping any Spatial Index defined into the current DB-file.
- you could eventually explicitly reference some f_geometry_column too; but this is strictly required only when the same table defines more Geometries, and is redundant in most ordinary cases.
- f_table_name selects a specific table supported by a corresponding Spatial Index.
- search_frame simple is a Geometry acting as a spatial filter.
Spatial Index and ATTACH DATABASESQLite allows to attach more than a single DB-file to the current connection.
Even in this case you can still use the VirtualSpatialIndex interface:
ATTACH DATABASE secondary.sqlite AS a SELECT * FROM a.com2011 AS c, prov2011 AS p WHERE ST_CoveredBy(c.geometry, p.geometry) = 1 AND nome_pro = 'AREZZO' AND c.ROWID IN ( SELECT ROWID FROM SpatialIndex WHERE f_table_name = 'DB=a.com2011' AND search_frame = p.geometry )For this special case, you can still use the SpatialIndex interface, but with a slightly different syntax:
- the f_table_name value is now prefixed. DB=a. simply means: I intend to reference the table named com2011 from within the attached DB identified by the a symbol.
- you could eventually use this extended notation even while accessing a table directly contained within the primary database.
In this case you simply have to use this conventional prefix: DB=main.
However, this is not required since any non prefixed table name will always be searched from within the primary database.
- Since f_geometry_column is always assumed to belong to the given f_table_name, a prefixed name (DB=main) should never be used.