back

Spatial Index: best practices

The 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 DATABASE

SQLite 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.

---

back