[postgis-users] Could someone explain the difference

[paul.malm at lfv.se]

Hi,
Could someone please tell me why I don't get this to work?
I have one layer with lines and one layer with polygons. The lines are broken in the intersections of the polygons.
I would like to save a new layer with all lines inside the polygons and update the orig layer without those lines:
CREATE TABLE removedlines AS SELECT s.*
FROM orglines s
LEFT JOIN polygons h ON
ST_Contains(h.the_geom, s.the_geom)
WHERE
h.the_geom IS NOT NULL;
DELETE FROM removedlines WHERE ST_Length(the_geom) <= 2000

This seems ok!

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)
and one layer without those lines.
I tried to erase those lines from the orgline layer like this:

DELETE FROM orglines
 WHERE id IN (
SELECT s.id FROM orglines AS s
                      LEFT OUTER JOIN polygons AS h ON
                      (ST_Contains(h.the_geom, s.the_geom)
                      AND St_Length(s.the_geom) > 2000)
WHERE h.the_geom IS NOT NULL)

I thought that only the iines inside and > 2000 should be erased but there were more lines erased than I thought.
I think the two sql sequences correspond, can someone explain to me why they don't

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190909/a3c33f86/attachment.html>