Hi Nicolas and Chris,<br /><br />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.<br /><br />Thanks again and kind regards,<br />Ge<br><div style="font-family: times new roman, new york, times, serif; font-size: 12pt;"><div style="font-family: times new roman, new york, times, serif; font-size: 12pt;"><font size="2" face="Arial"><hr size="1"><b><span style="font-weight:bold;">From:</span></b> Chris Hermansen <chris.hermansen@tecogroup.ca><br><b><span style="font-weight: bold;">To:</span></b> PostGIS Users Discussion <postgis-users@postgis.refractions.net><br><b><span style="font-weight: bold;">Sent:</span></b> Wednesday, September 7, 2011 8:39 PM<br><b><span style="font-weight: bold;">Subject:</span></b> Re: [postgis-users] Question about st_difference<br></font><br><div id=yiv797237241>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.<br>
<br><div class="yiv797237241gmail_quote">2011/9/7 Nicolas Ribot <span dir="ltr"><<a rel="nofollow" ymailto="mailto:nicolas.ribot@gmail.com" target="_blank" href="mailto:nicolas.ribot@gmail.com">nicolas.ribot@gmail.com</a>></span><br><blockquote class="yiv797237241gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div class="yiv797237241im">> Hello Group,<br>
><br>
> About st_difference I've seen several topics but unfortunately with none of them I could solve my problem.<br>
><br>
> What I want to do is subtract one layer from the other. I could simplify this down to the following;<br>
><br>
> Tbl_a contains 1 record<br>
> "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)))"<br>
><br>
> Tbl_b contains 2 records of which both intersects with the object in tbl_a<br>
><br>
> "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)))"<br>
><br>
> "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)))"<br>
><br>
> 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.<br>
><br>
><br>
> Does anyone know a solution for this?<br>
><br>
> Many thanks in advance,<br>
><br>
> Ge<br>
<br>
</div>Hi Ge,<br>
<br>
You may want to union objects in table B to create a single polygon,<br>
then perfom the difference:<br>
<br>
<br>
select st_difference(a.geom, b.geom)<br>
from<br>
(select 'POLYGON((168119.443682473 451093.811197312,197555.469699649<br>
<div class="yiv797237241im">451093.811197312,197555.469699649 437539.54805452,168119.443682473<br>
437539.54805452,168119.443682473 437539.54805452,168119.443682473<br>
</div>451093.811197312))'::geometry as geom) as a,<br>
(<br>
select st_union(geom) as geom<br>
from (<br>
select 'POLYGON((171424.484910418 453680.34544058,171424.484910418<br>
<div class="yiv797237241im">435697.56759188,174568.140889894 435697.56759188,174568.140889894<br>
453680.34544058,174568.140889894 453680.34544058,171424.484910418<br>
</div>453680.34544058))'::geometry as geom<br>
UNION<br>
select 'POLYGON((184904.90800885 453627.063135843,184904.90800885<br>
<div class="yiv797237241im">435644.285287143,188048.563988327 435644.285287143,188048.563988327<br>
453627.063135843,188048.563988327 453627.063135843,184904.90800885<br>
</div>453627.063135843))'::geometry as geom<br>
) as foo<br>
) as b;<br>
<br>
(see attached pictures)<br>
<font color="#888888"><br>
Nicolas<br>
</font><br>_______________________________________________<br>
postgis-users mailing list<br>
<a rel="nofollow" ymailto="mailto:postgis-users@postgis.refractions.net" target="_blank" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><br>
<a rel="nofollow" target="_blank" href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</a><br>
<br></blockquote></div><br><br clear="all"><br>-- <br><font face="SANS" size="3"><span style="border-collapse:collapse;color:rgb(32, 32, 32);font-size:13px;"><font style="font-family:arial, helvetica, sans-serif;" face="SANS" size="3">Chris Hermansen</font><br style="font-family:arial, helvetica, sans-serif;">
<i style="font-family:arial, helvetica, sans-serif;"><font size="2">Vice President</font></i><br style="font-family:arial, helvetica, sans-serif;"><img style="font-family:arial, helvetica, sans-serif;" src="cid:1.875730775@web161009.mail.bf1.yahoo.com" align="bottom" border="0"><br style="font-family:arial, helvetica, sans-serif;">
<font style="font-family:arial, helvetica, sans-serif;" face="SANS" size="2">TECO Natural Resource Group Limited</font><br style="font-family:arial, helvetica, sans-serif;"><font style="font-family:arial, helvetica, sans-serif;" face="SANS" size="2">301 · 958 West 8th Avenue</font><br style="font-family:arial, helvetica, sans-serif;">
<font style="font-family:arial, helvetica, sans-serif;" face="SANS" size="2">Vancouver BC CANADA · V5Z 1E5</font><br style="font-family:arial, helvetica, sans-serif;"><font face="SANS" size="2"><span style="font-family:arial, helvetica, sans-serif;">Tel +1.604.714.2878 · Cel +1.778.840.46</span>25</font></span></font><br>
</div><br />_______________________________________________<BR>postgis-users mailing list<BR><a ymailto="mailto:postgis-users@postgis.refractions.net" href="mailto:postgis-users@postgis.refractions.net">postgis-users@postgis.refractions.net</a><BR><a href="http://postgis.refractions.net/mailman/listinfo/postgis-users" target=_blank >http://postgis.refractions.net/mailman/listinfo/postgis-users</a><BR><br><br></div></div>