[postgis-users] Point in Poly Speed

[Stephen Woodbridge]

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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users