[postgis-users] Could someone explain the difference

[paul.malm at lfv.se]

Thanks Martin,
I could not get the “where” command to work in the join clause.
So I made it simple for me.
Delete from orig
Where ST_contains(geom1, geom2) AND ST_lenght(geom2)

I don’t know if this is more time/prosessor consuming than other solutions.
Kind regards,
Paul

Från: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] För Martin Davis
Skickat: den 9 september 2019 17:53
Till: PostGIS Users Discussion
Ämne: Re: [postgis-users] Could someone explain the difference

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<http://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<mailto: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<mailto: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/20190911/d6489394/attachment.html>