<div dir="ltr"><div>Paul, thanks so much for the quick response.</div><div><br></div><div>This is making sense - unfortunately I'm hitting a topology error that I'll try to deal with at a better hour... It's helpful to see your code for this though as I try to get a better sense of how to work most efficiently with PostGIS/sql.</div><div><br></div><div>Best,</div><div>mike</div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Jul 20, 2016 at 11:35 PM, Paul Ramsey <span dir="ltr"><<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div dir="ltr"><div class="gmail_extra">I wish I could edit emails after sending, I forgot to group the parks by their island:</div><div class="gmail_extra"><br></div><div class="gmail_extra"><span class=""><div style="font-size:12.8px">WITH parks_to_remove AS (</div><div style="font-size:12.8px"> SELECT <a href="http://islands.id/" target="_blank">islands.id</a>, </div><div style="font-size:12.8px"> ST_Collect(parks.geom) AS geom</div><div style="font-size:12.8px"> FROM islands </div><div style="font-size:12.8px"> JOIN parks </div><div style="font-size:12.8px"> ON ST_Intersects(parks.geom, islands.geom)</div></span><div style="font-size:12.8px"> GROUP BY <a href="http://islands.id" target="_blank">islands.id</a></div><span class=""><div style="font-size:12.8px">)</div><div style="font-size:12.8px">SELECT <a href="http://islands.id/" target="_blank">islands.id</a>, </div><div style="font-size:12.8px"> ST_Difference(islands.geom, parks_to_remove.geom) AS geom</div><div style="font-size:12.8px">FROM islands </div><div style="font-size:12.8px">JOIN parks_to_remove </div><div style="font-size:12.8px">ON <a href="http://islands.id/" target="_blank">islands.id</a> = <a href="http://parks_to_remove.id/" target="_blank">parks_to_remove.id</a>;</div></span></div><div><div class="h5"><div class="gmail_extra"><br></div><div class="gmail_extra"><br></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Jul 20, 2016 at 8:33 PM, Paul Ramsey <span dir="ltr"><<a href="mailto:pramsey@cleverelephant.ca" target="_blank">pramsey@cleverelephant.ca</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><div dir="ltr">This would be easier w/ pictures, but<div>- for each island you want to </div><div>- create the set of parks that are contained w/i that island and</div><div>- remove that whole set of parks from the one island they refer to</div><div>Because: ST_Difference() is a two parameter function: one parameter for the island, and one parameter for all the things you want removed.</div><div>So.</div><div><br></div><div>WITH parks_to_remove AS (</div><div> SELECT <a href="http://islands.id" target="_blank">islands.id</a>, </div><div> ST_Collect(parks.geom) AS geom</div><div> FROM islands </div><div> JOIN parks </div><div> ON ST_Intersects(parks.geom, islands.geom)</div><div>)</div><div>SELECT <a href="http://islands.id" target="_blank">islands.id</a>, </div><div> ST_Difference(islands.geom, parks_to_remove.geom) AS geom</div><div>FROM islands </div><div>JOIN parks_to_remove </div><div>ON <a href="http://islands.id" target="_blank">islands.id</a> = <a href="http://parks_to_remove.id" target="_blank">parks_to_remove.id</a>;</div><div><br></div><div><br></div><div>ATB,</div><div>P</div><div><br></div></div><div class="gmail_extra"><br><div class="gmail_quote"><div><div>On Wed, Jul 20, 2016 at 7:34 PM, Michael Treglia <span dir="ltr"><<a href="mailto:mtreglia@gmail.com" target="_blank">mtreglia@gmail.com</a>></span> wrote:<br></div></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left-width:1px;border-left-style:solid;border-left-color:rgb(204,204,204);padding-left:1ex"><div><div><div dir="ltr">Hi All,<div><br></div><div>Please pardon this fairly general question - I'm struggling with using ST_Difference, and think I might be missing something just about the intended way ST_Difference functions, as I'm still learning PostGIS... I think the answer to this will help me troubleshoot or at least lead me to ask better questions down the line.</div><div><br></div><div>So, I have a set of large and many multipolygons (islands) within one layer, and many smaller sets of multipolygons that are contained within the islands (parks) in another layer. I'm trying to effectively clip out the parks from the islands, so basically the end result will be the Islands with many holes throughout. The code I was expecting to use was along the lines of this:</div><div><br></div><div>SELECT ST_Difference(islands.geom, parks.geom) AS newgeom INTO test.islands_noparks FROM staging.parks, staging.islands <br></div><div><br></div><div>However, this is producing the entire islands, but with many rows (just about as many rows as there are parks). Thus, I think I might be a bit confused about how ST_Difference is intended to function. Would anybody be able to clarify?</div><div><br></div><div>Thanks so much for your time! Best,</div><div>Mike</div></div>
<br></div></div>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>
</blockquote></div><br></div><div class="gmail_extra"><br></div><div class="gmail_extra"><br></div><div class="gmail_extra"><br></div></div></div></div>
<br>_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a href="http://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">http://lists.osgeo.org/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>