Querying spatial data using spatialite with indexes

Spatialite is an amazing SQLite extension. Using this extension you can query your database using complex spatial functions like ST_Intersection, ST_Difference, ST_Buffer, etc.
Check more at SpatiaLite SQL functions reference list

For those like me who have a MS-SQL background, you should be aware that performing spatial queries
using spatial indexes is a bit different.

After creating your spatial index, you have to change your query to take advantage of it.

Instructions

Adding a spatial column

SELECT AddGeometryColumn('MYTable', 'Geometry',4326, 'POLYGON', 'XY');

Creating a spatial index

SELECT CreateSpatialIndex('MYTable', 'Geometry');

Querying

Suppose that you want to check if a point is inside a POLYGON among many stored into your database.
If you write a query like the one below, you are not taking advantage of R*Tree spatial indexes.

SELECT *
FROM MYTable
WHERE ST_Contains(Geometry,MakePoint(-46.521291, -23.519995)) = 1

To do so, the first step is to query to match your input data against the R*Tree index of stored data.
This can be done via virtual table SpatialIndex.

SELECT ROWID
FROM SpatialIndex
WHERE f_table_name = 'MYTable'
AND f_geometry_column = 'Geometry'
AND search_frame = MakePoint(-46.521291, -23.519995, 4326)

So, the final query will look like this:

SELECT *
FROM MYTable
WHERE ST_Contains(Geometry,MakePoint(-46.521291, -23.519995)) = 1
AND ROWID IN (
    SELECT ROWID
    FROM SpatialIndex
    WHERE f_table_name = 'MYTable'
    and f_geometry_column = 'Geometry'
    AND search_frame = MakePoint(-46.521291, -23.519995, 4326)
)

This query will fetch a few candidates from the SpatialIndex and then those candidates will be checked by a more complex function like ST_Contains.