Aha, you're exercising a bug in GEOS http://trac.osgeo.org/geos/ticket/244 that has been fixed but not yet released. If you can compile source code, compiling the 3.0 branch will give you the fix.
P On Tue, Jun 2, 2009 at 4:07 PM, sgrocho2 <[email protected]> wrote: > > I did not do the st_isvalid check on the ST_Buffer, however when I removed > it, the query ran fast (468ms) and gave the correct answer. We use the > buffer of -1 in order to minimize error in data creation when trying to snap > things up between layers. For example Zoning is generally derived from the > parcels but at times get out of alignment when parcels are adjusted. The -1 > buffer takes care of the slight amount of movement created by the > adjustment. In this case with the fish & wildlife data the buffer really > serves no purpose, we can do without it since it is not parcel derived. Any > ideas on how to best deal with ST_Buffer? > > > Paragon Corporation-2 wrote: >> >> Did you do an ST_IsValid check on the multipolygons or the ST_Buffer. >> Could >> be the ST_buffer that is the problem. >> >> -----Original Message----- >> From: [email protected] >> [mailto:[email protected]] On Behalf Of >> sgrocho2 >> Sent: Tuesday, June 02, 2009 5:08 PM >> To: [email protected] >> Subject: Re: [postgis-users] query multipolygon >> >> >> Okay, I did run ST_IsValid() and everything from each of the layers >> returned >> a t. When visually checking the query results with 167 rows it appears >> that >> the query is only returning rows that intersect the first polygon it >> processes. Leaving out those rows that only intersect the other 5 >> polygons. >> >> The following image is an example of what is being missed. The polygons >> outlined in blue are not selected by the query since they only touch one >> of >> the parcel polygons(green). >> http://www.nabble.com/file/p23840915/missed_features.jpg >> >> >> >> >> Paul Ramsey-4 wrote: >>> >>> Slow-vs-fast I can understand... the spatial index can more >>> effectively parcel out the problem for processing when the units of >>> processing are smaller. >>> >>> Wrong-vs-right I have a problem with. Are your multi-polygons valid? >>> Run ST_IsValid() on them. It's possible that they are valid taken >>> separately but invalid when combined (multipolygons are not allowed to >>> have overlapping components). >>> >>> Paul >>> >>> On Tue, Jun 2, 2009 at 11:05 AM, sgrocho2 <[email protected]> wrote: >>>> >>>> I am having trouble querying a multipolygon feature intersection with >>>> another multipolygon feature. When I execute the query it takes 45 >>>> seconds and returns and incomplete answer, 167 rows. If I explode >>>> the parcel polygon into its 6 parts and then run the same query it >>>> takes 5 seconds and returns >>>> 209 rows which is the correct answer. Is there a way to write this >>>> query to return the all the rows without having to first manipulate >>>> the data? The same query works fine in SQL Server but we want to use >>>> PostGIS. Below is the code for each of the Parcel tables they include >>>> only one parcel - one with 1 row, the other with 6 rows. >>>> >>>> THIS IS THE SINGLE FEATURE MULTIPOLYGON CODE: 45sec 167 rows SELECT >>>> DISTINCT g1.objectid As taxpar_id, g1.tax_parcel_num As >>>> parcel_number, g2.objectid As fw_id, g2.wdfw_phs, g2.wdfw_occur_pt, >>>> g2.wdfw_haulout, g2.pc_for_fish, g2.wdfw_fish_dist, g2.pc_oaks, >>>> g2.pc_salmon, g2.wdfw_occur_pl, g2.doh_sf, g2.wdfw_owl, >>>> g2.pc_eelgrass, g2.pc_hydro, g2.wdfw_sasi >>>> FROM loader.single_parcel As g1, >>>> loader.potential_fw_habitat_cons_areas_pg >>>> As g2 >>>> WHERE (g1.tax_parcel_num = '0618161001' AND >>>> ST_Intersects(ST_Buffer(g1.Shape,-1), g2.Shape)) >>>> >>>> THIS IS THE EXPLODED 6 FEATURES CODE: 5sec 209 rows SELECT DISTINCT >>>> g1.objectid As taxpar_id, g1.tax_parcel_num As parcel_number, >>>> g2.objectid As fw_id, g2.wdfw_phs, g2.wdfw_occur_pt, g2.wdfw_haulout, >>>> g2.pc_for_fish, g2.wdfw_fish_dist, g2.pc_oaks, g2.pc_salmon, >>>> g2.wdfw_occur_pl, g2.doh_sf, g2.wdfw_owl, g2.pc_eelgrass, >>>> g2.pc_hydro, g2.wdfw_sasi >>>> FROM loader.multi_parcel As g1, >>>> loader.potential_fw_habitat_cons_areas_pg >>>> As g2 >>>> WHERE (g1.tax_parcel_num = '0618161001' AND >>>> ST_Intersects(ST_Buffer(g1.Shape,-1), g2.Shape)) >>>> >>>> -- >>>> View this message in context: >>>> http://www.nabble.com/query-multipolygon-tp23836573p23836573.html >>>> Sent from the PostGIS - User mailing list archive at Nabble.com. >>>> >>>> _______________________________________________ >>>> 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 >>> >>> >> >> -- >> View this message in context: >> http://www.nabble.com/query-multipolygon-tp23836573p23840915.html >> Sent from the PostGIS - User mailing list archive at Nabble.com. >> >> _______________________________________________ >> 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 >> >> > > -- > View this message in context: > http://www.nabble.com/query-multipolygon-tp23836573p23842660.html > Sent from the PostGIS - User mailing list archive at Nabble.com. > > _______________________________________________ > 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
