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.
SELECT AddGeometryColumn('MYTable', 'Geometry',4326, 'POLYGON', 'XY');
SELECT CreateSpatialIndex('MYTable', 'Geometry');
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.
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
So, the final query will look like this:
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.