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.