[postgis-users] Point in Poly Speed

[Loren Dawe]

Hi All!
I am a new PostGIS user and have been using
st_dwithin(geomB.the_geom,geomA.the_geom,0.000025) with excellent speed
results compared with ArcGIS spatial join. My datasets range from
1-1,000,000 points and 10,000-1,500,000 polys and most run within
minutes. However at a certain point in the larger joins, the speed goes
from minutes to many hours (20 hours on one set). The SQL is the same
for smaller sets and the larger ones.
Are there any settings/commands/configurations to allow for the larger
datasets? 
Please note that I am using the Shape File to PostGIS Importer which is
set to create the GIST index automatically. Also, after some research
tried using the SET ENABLE_SEQSCAN=OFF command which did not seem to
help. Heres the query as it stands:
 
<code> 
SET ENABLE_SEQSCAN=OFF;
SELECT geomA.the_geom, geomA.gid, geomA.height
INTO resultstest
FROM largepoly as geomA, --polygon table
     largepoints as geomB --point table
     
WHERE st_dwithin(geomB.the_geom,geomA.the_geom,0.000025)
 
GROUP BY geomA.gid, geomA.height, geomA.the_geom
ORDER BY geomA.gid
;
</code>

TIA, Loren. 

Loren Dawe:GIS Manager
www.terraremote.com <http://www.terraremote.com/> 
terra remote sensing inc.
1962 Mills Road Sidney, BC, Canada, V8L 5Y3
T:250.656.0931 F:250.656.4604 T:800.814.4212