[GIS] How to speed up spatialite-query using spatialindex

queryspatial-indexspatialite

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?

Best Answer

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.

Related Question