<div dir="ltr">Hi<div><br></div><div>I'm not sure if I completely understand what you are trying to achieve, but I'll give it a try anyways. In your second sql query, you don't seem to be using the unione table you created, so it seems to me that you are not taking the union of the polygons after all. Also the OR-ing of st_crosses and st_contains with st_intersects is redundant as any geometry crossing or containing another geometry must also intersect that geometry. In addition, st_difference will leave the geometry unmodified if it is completely outside of the other geometries, so you shouldn't need the coalesce and intersects-combo either.</div>
<div><br></div><div>As far as I understand this might be closer to what you want:</div><div>SELECT st_difference(geom, (</div><div> SELECT st_union(geom) FROM <span style="font-family:arial,sans-serif;font-size:13px">rt_201</span></div>
<div><span style="font-family:arial,sans-serif;font-size:13px">)) AS geom</span></div><div><span style="font-family:arial,sans-serif;font-size:13px">FROM u_rt_801</span></div><div><br></div><div><br></div><div>Åsmund</div>
</div><div class="gmail_extra"><br><br><div class="gmail_quote">On Thu, Apr 24, 2014 at 3:56 PM, Pier Lorenzo Marasco <span dir="ltr"><<a href="mailto:pl.marasco@gmail.com" target="_blank">pl.marasco@gmail.com</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>Hi everyone, </div><div><br></div><div>I'm a little bit bogged down using ST_Difference. Probably there is something that I'm doing wrong, but I'm novice and I don't know what I'm in wrong .</div>
<div>I've two tables, the first one represents some lines (linestring 3d ) and the second one represent some houses (polygon 3d). I need to to cut away all the line's parts that fall inside the polygons (in 2d), keeping all the lines that doesn't touch anything. </div>
<div><br></div><div>CREATE TABLE unione AS SELECT St_Union(geom) as geom FROM rt_201;</div><div>CREATE TABLE results AS SELECT NEXTVAL('serial'), COALESCE(ST_Difference(a.geom,b.geom),a.geom) as geom FROM u_rt_801 as a LEFT JOIN rt_201 as b ON ST_Crosses(a.geom, b.geom) OR ST_Intersects (a.geom, b.geom) OR ST_Contains (a.geom, b.geom)</div>
<div><br></div><div>Some lines intersect more than one object; to solve this problem, following some advice, I've made an union of all the polygons.</div><div>More or less everything is working but at the end I've some lines that are not cutted. In the result I notice that, most of the time, the wrong lines are all the lines that don't cross all the polygon and that have a small part outside the polygon. Obviously there are some cases that contradict this theory (lines completely inside, lines crossing completely polygons...). In the fake results, most of the time, there are two lines; one is the correct one and the other one is the original one. Is that correlated to the "COALESCE" option ? </div>
<div>In Qgis everything is working as expected... </div><div>Tnx,</div><div><br></div><div>L.</div><div><br></div><div>here the original files... </div><div><div style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:13px">
<br><div class="gmail_chip gmail_drive_chip" style="width:396px;min-height:18px;max-height:18px;background-color:rgb(245,245,245);padding:5px;color:rgb(34,34,34);font-family:arial;font-weight:bold;border:1px solid rgb(221,221,221)">
<a href="https://docs.google.com/file/d/0B1ZgaWuX8IaDY3dkWVh1blpXV1k/edit?usp=drive_web" style="display:inline-block;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;text-decoration:none;padding:1px 0px;border:none;width:396px" target="_blank"><img src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" style="vertical-align:bottom;border:none"> <span dir="ltr" style="vertical-align:bottom;text-decoration:none">rt_201.dbf</span></a></div>
<br><div class="gmail_chip gmail_drive_chip" style="width:396px;min-height:18px;max-height:18px;background-color:rgb(245,245,245);padding:5px;color:rgb(34,34,34);font-family:arial;font-weight:bold;border:1px solid rgb(221,221,221)">
<a href="https://docs.google.com/file/d/0B1ZgaWuX8IaDU19OR1FkNktkaHc/edit?usp=drive_web" style="display:inline-block;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;text-decoration:none;padding:1px 0px;border:none;width:396px" target="_blank"><img src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" style="vertical-align:bottom;border:none"> <span dir="ltr" style="vertical-align:bottom;text-decoration:none">rt_201.shp</span></a></div>
<br><div class="gmail_chip gmail_drive_chip" style="width:396px;min-height:18px;max-height:18px;background-color:rgb(245,245,245);padding:5px;color:rgb(34,34,34);font-family:arial;font-weight:bold;border:1px solid rgb(221,221,221)">
<a href="https://docs.google.com/file/d/0B1ZgaWuX8IaDbHN4N3NDcFRCUXc/edit?usp=drive_web" style="display:inline-block;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;text-decoration:none;padding:1px 0px;border:none;width:396px" target="_blank"><img src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" style="vertical-align:bottom;border:none"> <span dir="ltr" style="vertical-align:bottom;text-decoration:none">rt_201.shx</span></a></div>
<br><div class="gmail_chip gmail_drive_chip" style="width:396px;min-height:18px;max-height:18px;background-color:rgb(245,245,245);padding:5px;color:rgb(34,34,34);font-family:arial;font-weight:bold;border:1px solid rgb(221,221,221)">
<a href="https://docs.google.com/file/d/0B1ZgaWuX8IaDNTc3U0dpVmt1N00/edit?usp=drive_web" style="display:inline-block;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;text-decoration:none;padding:1px 0px;border:none;width:396px" target="_blank"><img src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" style="vertical-align:bottom;border:none"> <span dir="ltr" style="vertical-align:bottom;text-decoration:none">u_rt_801.dbf</span></a></div>
<br><div class="gmail_chip gmail_drive_chip" style="width:396px;min-height:18px;max-height:18px;background-color:rgb(245,245,245);padding:5px;color:rgb(34,34,34);font-family:arial;font-weight:bold;border:1px solid rgb(221,221,221)">
<a href="https://docs.google.com/file/d/0B1ZgaWuX8IaDWUxQa0pFQ3RCajg/edit?usp=drive_web" style="display:inline-block;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;text-decoration:none;padding:1px 0px;border:none;width:396px" target="_blank"><img src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" style="vertical-align:bottom;border:none"> <span dir="ltr" style="vertical-align:bottom">u_rt_801.shp</span></a></div>
<br><div class="gmail_chip gmail_drive_chip" style="width:396px;min-height:18px;max-height:18px;background-color:rgb(245,245,245);padding:5px;color:rgb(34,34,34);font-family:arial;font-weight:bold;border:1px solid rgb(221,221,221)">
<a href="https://docs.google.com/file/d/0B1ZgaWuX8IaDdEx6R2daekNMTjQ/edit?usp=drive_web" style="display:inline-block;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;text-decoration:none;padding:1px 0px;border:none;width:396px" target="_blank"><img src="https://ssl.gstatic.com/docs/doclist/images/icon_10_generic_list.png" style="vertical-align:bottom;border:none"> <span dir="ltr" style="vertical-align:bottom">u_rt_801.shx</span></a></div>
<span class="HOEnZb"><font color="#888888"><br></font></span></div><span class="HOEnZb"><font color="#888888"><div dir="ltr" style="color:rgb(80,0,80);font-family:arial,sans-serif;font-size:13px"></div></font></span></div>
<span class="HOEnZb"><font color="#888888"><div><br></div>-- <br><div dir="ltr"><div><font face="times new roman, serif" size="4">P.L. Marasco </font><br></div>
<div><br></div></div>
</font></span></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/cgi-bin/mailman/listinfo/postgis-users" target="_blank">http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users</a><br></blockquote></div><br></div>