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?