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'; 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;
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?