[postgis-users] Point in Poly Speed

Loren Dawe Loren.Dawe at terraremote.com
Thu Mar 24 16:58:17 PDT 2011

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
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:
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

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	

More information about the postgis-users mailing list