[postgis-users] polygons inside polygon

[paul.malm at lfv.se]

Thank you Nicolas!

Från: postgis-users <postgis-users-bounces at lists.osgeo.org> För Nicolas Ribot
Skickat: den 1 december 2021 15:10
Till: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
Ämne: Re: [postgis-users] polygons inside polygon


Klicka bara på länkar och öppna bilagor om du litar på avsändaren och vet att innehållet är säkert.
Hi Paul,

I often use a CTE to update values for a table:


with tmp as (
    select distinct p1.id, p2.id is null as outer_pg
    from org p1
     left join org p2 on p1.id <> p2.id and st_within(p1.geom, p2.geom)
) update org o set outer_pg = t.outer_pg
from tmp t
where o.id = t.id<https://url11b.mailanyone.net/v1/?m=1msQJg-00087u-4f&i=57e1b682&c=0vE-U6c7dVKKWzkxjBIu13bHEpmR0MofmuukNTUFPwNMaCBDTRP5x06Wx5RAvWnZpHvXjToQ4OnR03iFbXuDoefHsyrPHa97giBXedq1uCHENfMjXEDKraLncqS-9We5fm7yROExsbMfL3_tbiHo1inXZ4hC0E9fhLVi1GgLOEKOpmewpogKm-gXPWwuZKOeYEGHTL1PtICR3x1rrQ2Kcd_JPVbfz0VD07WdxYCMnmUBZt3RHpZpArW6Bq8hK6NE>;

Nicolas

On Wed, 1 Dec 2021 at 11:10, <paul.malm at lfv.se<mailto:paul.malm at lfv.se>> wrote:
Thanks Nicolas,
I’ve added a boolean column (outer_pg) in my original layer (org).
Then I tried to populate that column, but it I don’t have the skills…
I tried to use your selection:

SELECT DISTINCT p1.fid, p2.fid IS null AS "outer_pg"
FROM org p1
     LEFT JOIN org p2 ON p1.fid <> p2.fid AND st_within(p1.the_geom, p2.the_geom)
order by p1.fid;

My try to populate outer_pg:
UPDATE org
SET "outer_pg" =  (SELECT outer_p FROM (
SELECT DISTINCT p1.fid, p2.fid IS null AS outer_p
from org p1
     LEFT JOIN org p2 ON p1.fid <> p2.fid AND st_within(p1.the_geom, p2.the_geom) WHERE p1.fid = org.fid) AS tmp)

But that is not correct, do you have any suggestions?
Kind regards,
Paul



Från: postgis-users <postgis-users-bounces at lists.osgeo.org<mailto:postgis-users-bounces at lists.osgeo.org>> För Nicolas Ribot
Skickat: den 30 november 2021 19:17
Till: PostGIS Users Discussion <postgis-users at lists.osgeo.org<mailto:postgis-users at lists.osgeo.org>>
Ämne: Re: [postgis-users] polygons inside polygon


Klicka bara på länkar och öppna bilagor om du litar på avsändaren och vet att innehållet är säkert.
Hi,

What about a simple left join on the table searching for polygons contained inside other polygon ?
Polygons that are contained inside other polygons will have false for the outer_pg column and true if they are not contained (ie are outer polygons).
You can then create the tables you want based on the outer_pg values


select distinct p1.id, p2.id is null as outer_pg
from polygon p1
     left join polygon p2 on p1.id <> p2.id and st_within(p1.geom, p2.geom)
order by p1.id;

id       outer_pg
1        true
2        true
3        true
4        true
5        false
6        false
7        false
8        true
9        true
10       true
11       false
Nicolas
_______________________________________________
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<https://url11b.mailanyone.net/v1/?m=1msQJg-00087u-4f&i=57e1b682&c=tpXseUCZsn9euIxSThxnno8b-S3NdyYdqzeIil9434tPYV6CUA4KaW4qap73VoCUVpspx-otwzBPuWqA-_XHpf7TFfr8dW0Q92SyNm7Li2rinIG3zcT_r6JEdBCpSEtM6_B5-z1KoYi2vFp3ReeiQ-qkQJerM3axgrwl2WJ7Sj93Y8suP0fF1wZDu44zmluTA9_FQR23uVqZ9aQRpPvyRFbu1ZbYcQPR2WJsdUgZHxbE7APf7EZIugQuuG7Eu_Ehu-7Fl05DmZPuwgQ_Ywpgv9QMXHEnD31JgxBml64vkyk>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20211202/3f72ab68/attachment.html>