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="gmail_quote">2011/9/7 Nicolas Ribot <span dir="ltr"><<a href="mailto:nicolas.ribot@gmail.com">nicolas.ribot@gmail.com</a>></span><br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">
<div class="im">> 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="im">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="im">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="im">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 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></blockquote></div><br><br clear="all"><br>-- <br><font face="SANS" size="3"><span style="border-collapse:collapse;color:rgb(32, 32, 32);font-family:'Droid Sans', arial, sans-serif;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:part1.07020105.06030603@tecogroup.ca" 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>