[postgis-users] Question about st_difference

[Edward Mac Gillavry]

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 Limited301 · 958 West 8th Avenue
Vancouver BC CANADA · V5Z 1E5Tel +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/20110908/c5251e39/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/20110908/c5251e39/attachment.jpg>