more than likely you have a huge polygon that is interacting with a
large number of points which is costly.
You can check this with:
select geomA.gid, count(*) as cnt
from largepoly as geomA, --polygon table
largepoints as geomB --point table
where geomA.the_geom && geomB.the_geom
group by geomA.gid
order by cnt desc limit 100;
This will tell you how many points interact with each polygon limiting
the results to the top 100 counts. I would expect that you will find a
small number of polygons with very high counts.
I think is the case, you might try tagging these polygons and
eliminating them from the initially and then processing them separately
to see if that speeds things up.
Also you want to try reversing you arguments to st_dwithin( and put the
polygon first as there may be some caching ot the first parameter that
might speed things up.
-Steve
On 3/24/2011 7:58 PM, Loren Dawe wrote:
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
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
