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)

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)
  • This is right thing to do but also utilizing the r-tree spatial index of GeoPackage with subquery or join would probably help a lot erouault.blogspot.com/2017/03/…. – user30184 Mar 20 '19 at 13:20
  • right join is not part of QGIS set of sql queries. using left join instead just retreives all the features. – user3386170 Mar 21 '19 at 19:10
  • @user30184 Given my capacities in sql, the solution provided is much simpler and easier to implement than r-tree spatial index, which I can't figure out yet. I expect that for more complex queries, I will eventually use r-tree spatial indices, but I will put that off as long as I can. – user3386170 Mar 21 '19 at 19:54
  • You do not really need how it works because spatial index tables in GeoPackage are always created in the same way. Copy-pasting the SQL from the blog and editing the table names to suit with your tables is enough. In simple cases software like QGIS applies spatial indexes automatically. – user30184 Mar 21 '19 at 21:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.