[postgis-users] query multipolygon

sgrocho2 sgrocho at gmail.com
Tue Jun 2 14:08:12 PDT 2009


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 <sgrocho at gmail.com> 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
>> postgis-users at postgis.refractions.net
>> http://postgis.refractions.net/mailman/listinfo/postgis-users
>>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> 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.




More information about the postgis-users mailing list