[postgis-users] query multipolygon

Paul Ramsey pramsey at opengeo.org
Tue Jun 2 14:21:05 PDT 2009


Can you share this test data with me? (Just enough to demonstrate the
condition.) What does your postgis_full_version() return?

P

On Tue, Jun 2, 2009 at 2:08 PM, sgrocho2 <sgrocho at gmail.com> wrote:
>
> 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.
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>



More information about the postgis-users mailing list