You might try dropping the intersects() test, and just keep results
where the intersection() is non-empty. Both of these operations are
relatively expensive, and really they are doing almost the same thing.
AFAIK you'll have to use a nested query to filter out the non-empty
results from the intersection.
niels hoffmann wrote:
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
------------------------------------------------------------------------
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
--
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022
_______________________________________________
postgis-users mailing list
[email protected]
http://postgis.refractions.net/mailman/listinfo/postgis-users
