<html><body><div style="color:#000; background-color:#fff; font-family:times new roman, new york, times, serif;font-size:12pt"><div><span>Hi Edward,</span></div><div><br><span></span></div><div><span>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.</span></div><div><br><span></span></div><div><span>So, thank you all for the help.</span></div><div><br><span></span></div><div><span>Ge</span></div><div><br></div><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 face="Arial" size="2"><hr size="1"><b><span
style="font-weight:bold;">From:</span></b> Edward Mac Gillavry <emacgillavry@hotmail.com><br><b><span style="font-weight: bold;">To:</span></b> gves2000@yahoo.com; postgis-users@postgis.refractions.net<br><b><span style="font-weight: bold;">Sent:</span></b> Thursday, September 8, 2011 1:19 PM<br><b><span style="font-weight: bold;">Subject:</span></b> RE: [postgis-users] Question about st_difference<br></font><br><div id="yiv182901003">
<style><!--
#yiv182901003 .yiv182901003hmmessage P
{
margin:0px;padding:0px;}
#yiv182901003 .yiv182901003hmmessage
{
font-size:10pt;font-family:Tahoma;}
--></style>
<div dir="ltr">
Ge,<br><br>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:<br><br>1. Append the id of the polygon in Tbl_a that intersects with a polygon in Tbl_b.<br>2. Perform an ST_Union only on those polygons in Tbl_b that have the same id from Tbl_a.<br>3. Perform the ST_Difference.<br><br>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.<br><br>Regards,<br><br>Edward<br><br><br><div><hr id="yiv182901003stopSpelling">Date: Wed, 7 Sep 2011 23:48:14 -0700<br>From: gves2000@yahoo.com<br>To: postgis-users@postgis.refractions.net<br>Subject: Re: [postgis-users] Question about st_difference<br><br>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 face="Arial" size="2"><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="yiv182901003ecxyiv797237241">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="yiv182901003ecxyiv797237241gmail_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="yiv182901003ecxyiv797237241gmail_quote" style="border-left:1px solid rgb(204, 204, 204);padding-left:1ex;">
<div class="yiv182901003ecxyiv797237241im">> 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="yiv182901003ecxyiv797237241im">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="yiv182901003ecxyiv797237241im">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="yiv182901003ecxyiv797237241im">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;" 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.965509592@web161018.mail.bf1.yahoo.com" align="bottom" border="0"><br style="font-family:arial, helvetica, sans-serif;">
<font style="font-family:arial, helvetica, sans-serif;" size="2">TECO Natural Resource Group Limited</font><br style="font-family:arial, helvetica, sans-serif;"><font style="font-family:arial, helvetica, sans-serif;" size="2">301 · 958 West 8th Avenue</font><br style="font-family:arial, helvetica, sans-serif;">
<font style="font-family:arial, helvetica, sans-serif;" 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 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><br></div></div><br>_______________________________________________
postgis-users mailing list
postgis-users@postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users</div> </div>
</div><br><br></div></div></div></body></html>