Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question

2 Answers 2

up vote 3 down vote accepted

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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