Just to make sure that all bases are covered, you need to be certain that the geometry columns have indices and are vacuum/analyze'ed.
r.b. -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Martin Davis Sent: Thursday, January 24, 2008 8:13 PM To: PostGIS Users Discussion Subject: Re: [postgis-users] Hints or tips on Large Intersect 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 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
