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?


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';
CREATE INDEX temp_lines_idx ON temp_lines USING GIST(wkb_geometry);
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';

CREATE INDEX lines_idx ON new_lines USING GIST (wkb_geometry);


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
  • 2
    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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.