[postgis-users] Hints or tips on Large Intersect

[Martin Davis]

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
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>   

-- 
Martin Davis
Senior Technical Architect
Refractions Research, Inc.
(250) 383-3022