How to speed up spatialite-query using spatialindex?

Question

I have got 2 tables, one of type LineLayer called strlayer, the other one is a PolygonLayer called blayer. The LineLayer contains two rows called fromBland and toBland that should be filled with an sql-query. They should be filled for each line according to in which polygon (from the Polygon-layer) the start- and endpoint of the line is. The names are in the Table PolygonLayer in a row called 'name'. With the following query it would work basically:

update strlayer set fromBland =(select name from blayer as n, strlayer
as s where strlayer.strid = s.strid and n.blevel = 4 and
within(startpoint(s.geometry),n.geometry));

blevel=4 only selects certain polygons, as there are also polygons of another level in this table, that aren´t needed in this query. Now I'd like to speed up the query as the database is very large and the operation takes several hours. As I already read it should be much faster with a spatialindex. So I´d like to know how the query above would look like with this index. I already know how to create the index - but not how to use it. Has anybody an idea how to write that query with the spatialindex and so speed it up?

Answer 1

Assuming you have a spatialindex created already on the polygon layer 'blayer', then your query would be:

UPDATE strlayer SET fromBland =(
SELECT name FROM blayer AS n, strlayer AS s 
WHERE strlayer.strid = s.strid AND n.blevel = 4 
AND within(startpoint(s.geometry),n.geometry)
AND s.ROWID IN (
  SELECT ROWID FROM SpatialIndex 
  WHERE f_table_name='strlayer' AND search_frame=blayer.geometry)
);

But note that if the polygon layer is very large and complicated, and lines extend over the whole area of the polygons, then a spatial index won't help so much here.

Answer 2

as the @Micha's answer.

The query need to explicitly use spatial index. Look here and here for more details.

It works great for me. The query took 8 seconds originally, now it takes 10 milliseconds.