[postgis-users] Question about st_difference

[G. van Es]

Hi Edward,

After several days of fighting with the data we finally managed to get the desired results. I followed your recipe. On step 3 however I have seen that ST_Intersects doesn't work any more between de UNION'ed and source data. Although most objects are on top of each other ST_Intersects remains false. To overcome this I use the id from the temp table which was created in step 1.

So, thank you all for the help.

Ge



________________________________
From: Edward Mac Gillavry <emacgillavry at hotmail.com>
To: gves2000 at yahoo.com; postgis-users at postgis.refractions.net
Sent: Thursday, September 8, 2011 1:19 PM
Subject: RE: [postgis-users] Question about st_difference


 
Ge,

Instead of an ST_Union on the full Tbl_b table, merging all polygons into one big polygon, establish first which polygons in Tbl_b intersect the same polygon in Tbl_a. Your recipe then would be:

1. Append the id of the polygon in Tbl_a that intersects with a polygon in Tbl_b.
2. Perform an ST_Union only on those polygons in Tbl_b that have the same id from Tbl_a.
3. Perform the ST_Difference.

Thus you will have more iterations in the ST_Difference step, but with smaller geometries. Hope that will allow you to deal with 7M+ records.

Regards,

Edward




________________________________
Date: Wed, 7 Sep 2011 23:48:14 -0700
From: gves2000 at yahoo.com
To: postgis-users at postgis.refractions.net
Subject: Re: [postgis-users] Question about st_difference

Hi Nicolas and Chris,

Thanks for pointing me in the right direction. On a small scale it works great. Now the job is to implement it on tables with 7million records.

Thanks again and kind regards,
Ge


________________________________
From: Chris Hermansen <chris.hermansen at tecogroup.ca>
To: PostGIS Users Discussion <postgis-users at postgis.refractions.net>
Sent: Wednesday, September 7, 2011 8:39 PM
Subject: Re: [postgis-users] Question about st_difference


In addition to Nicolas' suggestion, one thing that can work, for example when the goal is to "make holes" with one theme in another, is to st_union the two themes, then re-attribute, then delete the unwanted polygons.  This has worked for me in relatively small datasets but I've never truly stress tested it.


2011/9/7 Nicolas Ribot <nicolas.ribot at gmail.com>

> Hello Group,
>>
>> About st_difference I've seen several topics but unfortunately with none of them I could solve my problem.
>>
>> What I want to do is subtract one layer from the other. I could simplify this down to the following;
>>
>> Tbl_a contains 1 record
>> "POLYGON(((168119.443682473 451093.811197312,197555.469699649 451093.811197312,197555.469699649 437539.54805452,168119.443682473 437539.54805452,168119.443682473 437539.54805452,168119.443682473 451093.811197312)))"
>>
>> Tbl_b contains 2 records of which both intersects with the object in tbl_a
>>
>> "POLYGON(((171424.484910418 453680.34544058,171424.484910418 435697.56759188,174568.140889894 435697.56759188,174568.140889894 453680.34544058,174568.140889894 453680.34544058,171424.484910418 453680.34544058)))"
>>
>> "POLYGON(((184904.90800885 453627.063135843,184904.90800885 435644.285287143,188048.563988327 435644.285287143,188048.563988327 453627.063135843,188048.563988327 453627.063135843,184904.90800885 453627.063135843)))"
>>
>> The result I'm looking for is the object of tbl_a abstracted with both objects from tbl_b, since they are both intersecting. In this example the result should be 3 squares since it is cut twice by tbl_b.
>>
>>
>> Does anyone know a solution for this?
>>
>> Many thanks in advance,
>>
>> Ge
>
>Hi Ge,
>
>You may want to union objects in table B to create a single polygon,
>then perfom the difference:
>
>
>select st_difference(a.geom, b.geom)
>from
>(select 'POLYGON((168119.443682473 451093.811197312,197555.469699649
>
>451093.811197312,197555.469699649 437539.54805452,168119.443682473
>437539.54805452,168119.443682473 437539.54805452,168119.443682473
>451093.811197312))'::geometry as geom) as a,
>(
>select st_union(geom) as geom
>from (
>       select 'POLYGON((171424.484910418 453680.34544058,171424.484910418
>
>435697.56759188,174568.140889894 435697.56759188,174568.140889894
>453680.34544058,174568.140889894 453680.34544058,171424.484910418
>453680.34544058))'::geometry as geom
>       UNION
>       select 'POLYGON((184904.90800885 453627.063135843,184904.90800885
>
>435644.285287143,188048.563988327 435644.285287143,188048.563988327
>453627.063135843,188048.563988327 453627.063135843,184904.90800885
>453627.063135843))'::geometry as geom
>) as foo
>) as b;
>
>(see attached pictures)
>
>Nicolas
>
>_______________________________________________
>postgis-users mailing list
>postgis-users at postgis.refractions.net
>http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>


-- 
Chris Hermansen
Vice President

TECO Natural Resource Group Limited
301 · 958 West 8th Avenue
Vancouver BC CANADA · V5Z 1E5
Tel +1.604.714.2878 · Cel +1.778.840.4625

_______________________________________________
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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110916/cb3c84c7/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: teco_sig.jpg
Type: image/jpeg
Size: 4928 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110916/cb3c84c7/attachment.jpg>