[postgis-users] Problem intersecting big tables

[Fred Lehodey]

Cher Pierre, (offlist)
bon... j 'ai juste une petite idée.....
Je me permet d écrire en français... je viens, curiosité, de lire votre
présentation sur le CEF... hahaha j'ai rigolé !!... Montpellier et le
pastis.... génial...
Le portugais a ce mot: "saudade"... difficile à traduire, mais plus ou moins
= nostalgie.... le bon vieux temps.... bref....
Ha.... l'idée: découper ces gros 25 polys en petits bouts... ça pourrait
être intéressant.... et ainsi profiter  mieux du Gist....
Faut vraiment que je trouve du temps pour voir de plus près ce postgis
raster..... ;).

A bientot, Fred

On Thu, Apr 28, 2011 at 9:40 PM, Pierre Racine
<Pierre.Racine at sbf.ulaval.ca>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,
>
> Pierre
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110428/9e05596b/attachment.html>