[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] Problem intersecting big tables
From: Fred Lehodey <lehodey () gmail ! com>
Date: 2011-04-28 21:43:32
Message-ID: BANLkTi=pne9_w0zG9MVWDe9WdMEnvCMRoQ () mail ! gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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@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@postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
[Attachment #5 (text/html)]
Cher Pierre, (offlist)<br>bon... j 'ai juste une petite idée.....<br>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...<br> Le portugais \
a ce mot: "saudade"... difficile à traduire, mais plus ou moins = \
nostalgie.... le bon vieux temps.... bref....<br>Ha.... l'idée: découper ces gros \
25 polys en petits bouts... ça pourrait être intéressant.... et ainsi profiter mieux \
du Gist.... <br> Faut vraiment que je trouve du temps pour voir de plus près ce \
postgis raster..... ;).<br><br>A bientot, Fred<br><br><div class="gmail_quote">On \
Thu, Apr 28, 2011 at 9:40 PM, Pierre Racine <span dir="ltr"><<a \
href="mailto:Pierre.Racine@sbf.ulaval.ca">Pierre.Racine@sbf.ulaval.ca</a>></span> \
wrote:<br> <blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; \
border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">Hi,<br> <br>
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:<br>
<br>
CREATE TABLE result AS<br>
SELECT ST_Intersection(a.geom, b.geom) as geom, aif, bid<br>
FROM tablewith25poly a, tablewith21000000poly b<br>
WHERE ST_Intersects(a.geom, b.geom);<br>
<br>
The server crashes after something like two hours.<br>
<br>
The weird thing is that if I do the same query with one polygon from the 25 polygon \
table at a time like this:<br> <br>
CREATE TABLE resultXX AS<br>
SELECT ST_Intersection(a.geom, b.geom) as geom, aif, bid<br>
FROM tablewith25poly a, tablewith21000000poly b<br>
WHERE ST_Intersects(a.geom, b.geom) and aid=XX;<br>
<br>
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.<br> <br>
How can I quickly trace where the problem comes from? I can install my own compiled \
version of PostGIS if necessary...<br> <br>
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"<br> <br>
Thanks for any help,<br>
<br>
Pierre<br>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" \
target="_blank">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br> \
</blockquote></div><br>
_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic