[postgis-users] Could someone explain the difference
Martin Davis
mtnclimb at gmail.com
Mon Sep 9 08:52:48 PDT 2019
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>
More information about the postgis-users
mailing list