[postgis-users] time consuming intersection query

[Arnaud Lesauvage]

Nelson Guda a écrit :
> Hi all,
> 
> I'm trying to do an intersection query to find which elements of one  
> database (county shapes) intersects with another database of  
> thousands of shapes.  The query seems to get stuck running, and I'm  
> not sure why.  Here is the language I used:
> 
> create table iracounties as select c.name, c.state_name, c.id from  
> counties as c, iraname as i where intersects(c.the_geom,i.wkb_geometry)
> 
> Am I doing something wrong, or is there some way to make this more  
> efficient?


Hi Nelson

Intersects() is a very time consumming funtion, which does not use 
indexes, because only bounding-box-based operators use indexes.
You should change your query to something like :

create table iracounties as
select c.name, c.state_name, c.id
from  counties as c, iraname as i
where c.the_geom && i.wkb_geometry and
intersects(c.the_geom,i.wkb_geometry);

If your geometry columns are indexed, postgresql will use the 
index to evaluate "c.the_geom && i.wkb_geometry", which means 
"bounding bos of c.the_geom overlaps bounding box of i.wkb_geometry".

You should read 
http://postgis.refractions.net/docs/ch04.html#id2919118
about using indexes with geometries.

--
Arnaud