# PostGIS Better ST_Intersects Query

I am currently using the following queries to return all features that intersect a large and complex polygon. I initially tried to use a single ST_Intersect query but because the number of features it is searching through is over 400 million this took a significant amount of time (+24hrs).

So I now start by using a bounding box query to drop the number of features that the second query (the ST_Intersects one) uses.

However, I think this is extremely verbose way of doing this, but I cannot figure out a better or quicker way.

Can anyone think of a more efficient way?

thanks

PS Extra Information

Windows 2008 Server 64-bit Postgis 2.0 64-bit PostGIS is configuration settings are tweaked using the wiki advice. All geometries have GiST indexes.

CREATE TABLE temp_lines AS SELECT lines.* FROM lines,counties WHERE lines.wkb_geometry && counties.geom AND counties.name = 'County';
COMMIT;
CREATE INDEX temp_lines_idx ON temp_lines USING GIST(wkb_geometry);
COMMIT;
CREATE TABLE new_lines AS SELECT temp_lines.* FROM temp_lines, counties WHERE      ST_Intersects(counties.geom, temp_lines.wkb_geometry) AND counties.name = 'County';
COMMIT;
DROP TABLE IF EXISTS temp_lines;

CREATE INDEX lines_idx ON new_lines USING GIST (wkb_geometry);
COMMIT;


## EDIT

After Paul's comment I went back and re-tried some of my earlier queries.

This first one took well over 24hrs

CREATE TABLE new_lines AS SELECT * FROM lines WHERE ST_Intersects((SELECT geom FROM counties WHERE name = 'County'), lines.wkb_geometry);


Then I found the following post http://revenant.ca/www/postgis/workshop/indexing.html

and I changed my query to this

CREATE TABLE new_lines AS SELECT lines.* FROM lines, counties WHERE ST_Intersects(counties.geom, lines.wkb_geometry) AND counties.name = 'County';


This took a matter of hours.

Not sure why there is such a difference as I thought my first query was the most efficient?

• The ST_Intersects() function includes an && test within it, so there's no reason this multi-step approach should be any faster then a straight tests against ST_Intersects() to start with. – Paul Ramsey Apr 17 '13 at 19:06
• Hi Paul - thanks for your response. I thought that would have been the case but my initial query took so long I thought it was not using the &&. I have edited my initial question to show the two queries – tjmgis Apr 18 '13 at 9:48
• the first is a subquery which is being evaluated after the st_intesects starts. at the second query you first limit the rows to specific attribute – nickves Apr 18 '13 at 12:30