[postgis-users] Problem intersecting big tables

[Stephen Woodbridge]

On 4/28/2011 4:40 PM, Pierre Racine wrote:
> Hi,
>
> I have one table with 21 000 000 polygons (most of the forest stands of Canada) and another one with 25 very big polygons (I expect each of them intersects with some 100000 polygons from the first one). When I intersect the two tables like this:
>
> CREATE TABLE result AS
> SELECT ST_Intersection(a.geom, b.geom) as geom, aif, bid
> FROM tablewith25poly a, tablewith21000000poly b
> WHERE ST_Intersects(a.geom, b.geom);
>
> The server crashes after something like two hours.
>
> The weird thing is that if I do the same query with one polygon from the 25 polygon table at a time like this:
>
> CREATE TABLE resultXX AS
> SELECT ST_Intersection(a.geom, b.geom) as geom, aif, bid
> FROM tablewith25poly a, tablewith21000000poly b
> WHERE ST_Intersects(a.geom, b.geom) and aid=XX;
>
> Where XX goes from 01 to 25, every query pass without problem... So I assume there is no problem with the validity of the 21 000 000 polygons.
>
> How can I quickly trace where the problem comes from? I can install my own compiled version of PostGIS if necessary...
>
> I have "PostgreSQL 8.4.5, compiled by Visual C++ build 1400, 32-bit" and Postgis "2.0 USE_GEOS=1 USE_PROJ=1 USE_STATS=1"
>
> Thanks for any help,


Doesn't ST_Distance(a.geom, b.geom)=0.0 do the same thing as 
ST_Intersects() does in this case? And I would think that it would be 
much faster and does not deal with topology of the polygons, so it would 
use less memory and validity issue should not be a problem if they exist.

Anyway, I would give this a try first.

-Steve W