I am trying to return a list of geometries that satisfy two ST_Intersects conditions, and am running into a performance issue that I hope is remediable. My query looks like this:
SELECT DISTINCT a.id
FROM layer1 a, layer2 b, layer3 c
WHERE (ST_Intersects(a.GEOMETRY, b.GEOMETRY) AND b.ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name='layer2' AND search_frame=a.GEOMETRY))
OR (ST_Intersects(a.GEOMETRY, c.GEOMETRY) AND c.ROWID IN (SELECT ROWID FROM SpatialIndex WHERE f_table_name='layer3' AND search_frame=a.GEOMETRY))
Individually, both ST_Intersects operations are quite fast and return in a second or two. However, when I combine both conditions in the WHERE clause, the whole thing appears to take exponentially longer, as if it is doing a table scan.
Is there an efficient way to write this query?
