[postgis-users] query multipolygon

[Paul Ramsey]

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 <sgrocho at gmail.com> 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: postgis-users-bounces at postgis.refractions.net
>> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
>> sgrocho2
>> Sent: Tuesday, June 02, 2009 5:08 PM
>> To: postgis-users at postgis.refractions.net
>> 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 <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
>>
>>
>>
>> _______________________________________________
>> 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-tp23836573p23842660.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
>