On Tuesday 02 September 2008, Mark Cave-Ayland wrote: > Dylan Beaudette wrote: > > Hi, > > > > I am performing a basic intersection between two very large geometries. > > The resulting query plan looks like this: > > > > Nested Loop (cost=0.00..2761762.20 rows=429619 width=20202) > > Join Filter: _st_intersects(state_lu.wkb_geometry, > > m_polys.wkb_geometry) -> Seq Scan on state_lu (cost=0.00..13467.93 > > rows=198693 width=1158) -> Index Scan using mapunit_spatial_idx on > > mapunit_poly m_polys (cost=0.00..13.66 rows=3 width=19044) > > Index Cond: (state_lu.wkb_geometry && m_polys.wkb_geometry) > > Filter: (state_lu.wkb_geometry && m_polys.wkb_geometry) > > > > Both tables have spatial indices, and I am using PostgreSQL 8.3.1 and > > PostGIS 1.3.3. I am using the ST_Intersects() test as the join condition > > between these two tables. > > > > The query looks like this: > > > > SELECT m_polys.areasymbol, m_polys.mukey, > > -- crops and their codes > > class1 || '-' || subclass1 || '-' || specond1 || '-' || irr_typ1pa as c1, > > class1 || subclass1 as c1_code, > > class2 || '-' || subclass2 || '-' || specond2 || '-' || irr_typ2pa as c2, > > class2 || subclass2 as c2_code, > > class3 || '-' || subclass3 || '-' || specond3 || '-' || irr_typ3pa as c3, > > class3 || subclass3 as c3_code, > > -- save county name and land use survey year > > lower(county) as county, year, > > ST_Intersection(dwr.state_lu.wkb_geometry, m_polys.wkb_geometry) as > > wkb_geometry > > FROM > > mapunit_poly as m_polys > > JOIN > > dwr.state_lu > > -- join condition: only those polygons which completely intersect > > ON ST_Intersects(dwr.state_lu.wkb_geometry, m_polys.wkb_geometry); > > > > Is there any way to make this query more efficient, or is the 'seq scan' > > in the query plan unavoidable? > > > > Thanks! > > > > Dylan > > Hi Dylan, > > Unfortunately I think this is unavoidable when using a join with 2 > column arguments; the way I see it is that you have to scan through at > least 1 column completely in order to determine whether it has any > intersection with the other (Hashing and sorting for Hash Joins/Merge > Joins cannot really help you here in multi-dimensional space). > > If there is an extra constraint you can add to a WHERE clause based upon > another (non-geometry) field value, you may find that things can start > to work better... > > > ATB, > > Mark.
Thanks for the tips Mark. In the end the query took over 8 hours, which is not all that long considering the size. Cheers, Dylan -- Dylan Beaudette Soil Resource Laboratory http://casoilresource.lawr.ucdavis.edu/ University of California at Davis 530.754.7341 _______________________________________________ postgis-users mailing list [email protected] http://postgis.refractions.net/mailman/listinfo/postgis-users
