[postgis-users] Could someone explain the difference

[Martin Davis]

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.

Try using

SELECT s.id FROM orglines AS s
                      INNER JOIN polygons AS h
                      ON (ST_Contains(h.the_geom, s.the_geom)
WHERE St_Length(s.the_geom) > 2000)

On Mon, Sep 9, 2019 at 7:53 AM <paul.malm at lfv.se> wrote:

> 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 <http://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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20190909/5eb609a9/attachment.html>