[postgis-users] Hints or tips on Large Intersect

[Stephen Woodbridge]

Yeah, this sounds like it will run for a very very long time. A couple 
of thoughts that have more to do with managing the process:

1) make sure you run an is_valid() check on both tables and remove/fix 
any geometries that are not valid. It is a pain when you hit one of 
these and it nukes your transaction or crashes the server and you have 
to start over.

2) you might want to break this into multiple queries based on some 
subset of the record in the smaller table. Like do 1-10000, 10001-20000, 
etc. This would allow you to get the results of each commited so a 
restart would be less painful, also this would allow you to get some 
timing statistics to better predict how long the remainder of the rows 
will take.

-Steve

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