Hmm. Blush. Now that I have posted and annoyed you all, I believe I see my problem - I'm getting an outer product effect from my st_difference as it visits each polygon.<br><br>So I need to go away and think about the join condition on those queries.<br>
<br>Sorry...<br><br><div class="gmail_quote">On Mon, May 30, 2011 at 5:06 PM, Chris Hermansen <span dir="ltr"><<a href="mailto:chris.hermansen@tecogroup.ca">chris.hermansen@tecogroup.ca</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">
Ladies and gentlemen;<br><br>Nice to be back.<br><br>I am wrestling with st_difference() and it is getting the better of me. I am hoping someone who has seen this before can help.<br><br>I have two tables that are buffer polygons, built by PostGIS, around some linear features (trails and traplines).<br>
<br>I am seeking to combine these two tables to produce a single set of polygons that shows areas that are inside the trail buffers, inside the trapline buffers, both, or neither.<br><br>The pattern I am following is<br>
<br>
st_difference(btrail, btrapline)<br>union<br>st_intersection(btrail, btrapline)<br>union<br>st_difference(btrapline, btrail)<br><br>The btrail table has 9 rows, all valid POLYGONs; the btrapline table has 18 rows, all valid POLYGONs. Nothing self-overlaps. All have SRID of 3005.<br>
<br>There is a place in the southwest of the area where one trapline buffer and one trail buffer cross over each other twice. No other features come close.<br><br>If I copy just those two polygons into two separate test tables and use my difference/intersection/difference operation, I get the result I expect - 10 polygons, none overlapping, where some are trapline-only, some are trail-only, and some are both trail and trapline. A visual inspection of the result in QGIS "makes sense".<br>
<br>If I perform the same analysis on the full data set, I get a result that seems wrong - looking at the portion of the data related to those two features, instead of 10 polygons, I see 35 polygons. Many of these "result" polygons overlap each other. Visually (in QGIS again), I see what appears to be the two original input polygons covering a bunch of other polygons.<br>
<br>I can of course post the data; I also have a few screen shots from QGIS saved as jpg files.<br><br>This is from PostgreSQL 8.4.8 running on Ubuntu 10.10. The output from postgis_full_version() is<br><br>POSTGIS="1.5.1" GEOS="3.2.0-CAPI-1.6.0" PROJ="Rel. 4.7.1, 23 September 2009" LIBXML="2.7.6" USE_STATS<br>
<br>This is the code that produces the multiple overlapping result polygons with the full data set and seemingly normal results with the two test buffers.<br><br>-- create the diff/int/diff on the full table<br><br>drop table utrailtrapline;<br>
<br>create table utrailtrapline (<br> gid serial,<br> istrail boolean,<br> istrapline boolean);<br><br>SELECT AddGeometryColumn('','utrailtrapline','the_geom','3005','POLYGON',2);<br>
<br>insert into utrailtrapline (istrail, istrapline, the_geom)<br>select<br> istrail,<br> FALSE as istrapline,<br> (st_dump(st_difference(btrail.the_geom, btrapline.the_geom))).geom as the_geom<br>from btrail, btrapline;<br>
<br>insert into utrailtrapline (istrail, istrapline, the_geom)<br>select<br> istrail,<br> istrapline,<br> (st_dump(st_intersection(btrail.the_geom, btrapline.the_geom))).geom as the_geom<br>from btrail, btrapline<br>
where st_intersects(btrail.the_geom, btrapline.the_geom);<br><br>insert into utrailtrapline (istrail, istrapline, the_geom)<br>select<br> FALSE as istrail,<br> istrapline,<br> (st_dump(st_difference(btrapline.the_geom, btrail.the_geom))).geom as the_geom<br>
from btrail, btrapline;<br><br>Thanks in advance!<br clear="all"><font color="#888888"><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;">
<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>
</font></blockquote></div><br>