<div dir="ltr">I think the problem is that the condition on length should not be in the ON clause. Because it's a LEFT OUTER JOIN, the result will include records with h.the_geom = NULL when NOT (contained AND length > 2000). This is equivalent to (NOT contained OR length <= 2000). So all lines with length <= 2000 will be erased.<div><br></div><div>Try using</div><div><br></div>SELECT <a href="http://s.id">s.id</a> FROM orglines AS s<br> INNER JOIN polygons AS h<br> ON (ST_Contains(h.the_geom, s.the_geom)<br>WHERE St_Length(s.the_geom) > 2000)</div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Sep 9, 2019 at 7:53 AM <<a href="mailto:paul.malm@lfv.se">paul.malm@lfv.se</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">
<div lang="SV">
<div class="gmail-m_7209712458403225942WordSection1">
<p class="MsoNormal"><span lang="EN-US">Hi,<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">Could someone please tell me why I don’t get this to work?<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">I have one layer with lines and one layer with polygons. The lines are broken in the intersections of the polygons.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">I would like to save a new layer with all lines inside the polygons and update the orig layer without those lines:<u></u><u></u></span></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:14pt">CREATE TABLE removedlines AS SELECT s.*
<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:14pt">FROM orglines s<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:14pt">LEFT JOIN polygons h ON
<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:14pt">ST_Contains(h.the_geom, s.the_geom)
<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:14pt">WHERE <u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:14pt">h.the_geom IS NOT NULL;
<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:14pt">DELETE FROM removedlines WHERE ST_Length(the_geom) <= 2000<u></u><u></u></span></b></p>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">This seems ok!<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">Then I would like to remove the same lines from orglines, so that I have one layer with only the lines inside and > 2000 (that I have)<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">and one layer without those lines.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">I tried to erase those lines from the orgline layer like this:<u></u><u></u></span></p>
<p class="MsoNormal"><b><span lang="EN-US"><u></u> <u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:14pt">DELETE FROM orglines
<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:14pt"> WHERE id IN (<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:14pt">SELECT <a href="http://s.id" target="_blank">s.id</a> FROM orglines AS s<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:14pt"> LEFT OUTER JOIN polygons AS h ON
<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:14pt"> (ST_Contains(h.the_geom, s.the_geom)<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:14pt"> AND St_Length(s.the_geom) > 2000)<u></u><u></u></span></b></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:14pt">WHERE h.the_geom IS NOT NULL)</span></b><b><span lang="EN-US"><br>
<br>
</span></b><span lang="EN-US"><u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">I thought that only the iines inside and > 2000 should be erased but there were more lines erased than I thought.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">I think the two sql sequences correspond, can someone explain to me why they don’t
<u></u><u></u></span></p>
<p class="MsoNormal"><b><span lang="EN-US"><u></u> <u></u></span></b></p>
</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="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>