[postgis-users] Hints or tips on Large Intersect

[niels hoffmann]

Hi,

I am fairly new to Postgis so I like some feedback whether I am going
through the right moves.
I am trying to create a new table with the intersected results from two
input polygon tables.
Both tables are in NZMG (2193) the first table has 100000+ records, the
second table has 400000+ records.
The query I am using is:

Create table ablc_pol with OIDS as
SELECT intersection(a.geom, l.geom) as intersect_geom, a.*,
l."CLASS",l."NAME", l."REPLID"
from first_table a, second_table l
Where a.geom && l.geom
AND intersects(a.geom, l.geom);
Currently this query is taking >200 hours before I cancelled it because I
wasn't sure it would ever end. However, running it on a small subset showed
satisfactory results...
I am using version 1.2 on Windows.
Does it matter which table I put first in the query or would the optimizer
take care of that?


Cheers,
Niels
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080125/d173fb2d/attachment.html>