[postgis-users] polygons inside polygon

[Nicolas Ribot]

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;


Nicolas

On Wed, 1 Dec 2021 at 11:10, <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> *För *Nicolas
> Ribot
> *Skickat:* den 30 november 2021 19:17
> *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,
>
>
>
> 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
> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20211201/109ccab7/attachment.html>