Efficiently query two properties QGIS spatialite

Question

I have a simple query that involves two steps:

1. non-spatial criteria (should be super-fast)
2. spatial criteria (which is really slow)

I would like to run the spatial query only on the filtered layer (which is only one polygon). Is this possible?

SELECT 31H.* FROM bois_corridor_forestier, 31H
WHERE bois_corridor_forestier.ID="1" /*run this first*/
AND st_intersects(bois_corridor_forestier.geometry, 31H.geometry)

Answer 1

your query wil first combine every feature from 'bois_corridor_forestier' with '31H'. This makes a huge intermediate-table to look for an intersection. You better select the feature from bois_corridor_forestier you want and then do the intersection.

There are multiple ways to write the query, but for the clarity I wil do it with 'with-clause'.

with selection as (select * from bois_corridor_forestier where ID = "1")

select 31H.* from 31H
right join selection on st_intersects(31H.geometry, selection.geometry)