[postgis-users] Point in Poly Speed
woodbri at swoodbridge.com
Thu Mar 24 17:18:01 PDT 2011
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.
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
> 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:
> 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
> TIA, Loren.
> Loren Dawe:GIS Manager
> 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
> postgis-users at postgis.refractions.net
More information about the postgis-users