[postgis-users] polygons inside polygon
paul.malm at lfv.se
paul.malm at lfv.se
Tue Nov 30 02:42:43 PST 2021
Thank you Marcin!
I’m not sure if I understand you correct. Here is what I tried to do to get the outer most polygons:
Select * from "org" Where "fid" IN(
select distinct on (t1.fid) t1.fid
from "org" t1,"org" t2
where ST_Area(t1.the_geom)>ST_Area(t2.the_geom)
and ST_intersects(t1.the_geom,t2.the_geom)
order by t1.fid,ST_Area(t2.the_geom) desc)
AND "fid" NOT IN (
select distinct on (t1.fid) t2.fid
from "org" t1,"org" t2
where ST_Area(t1.the_geom)>ST_Area(t2.the_geom)
and ST_Intersects(t1.the_geom,t2.the_geom)
order by t1.fid,ST_Area(t2.the_geom) desc);
I’m still getting polygons inside other polygons.
Kind regards,
Paul
Från: postgis-users <postgis-users-bounces at lists.osgeo.org> För Marcin Mionskowski
Skickat: den 30 november 2021 08:40
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.
Assuming (1) your problem is not trivial (you have more then one "outer most" polygon) and (2) there can be only one type of spatial relationship (polygons are properly contained https://postgis.net/docs/ST_ContainsProperly.html<https://url11b.mailanyone.net/v1/?m=1mrxkX-0008NY-3a&i=57e1b682&c=E3fbWP_UxOp_aLqY_yLoWpoHnd9en_osfYJj-MVUi5KueBYHM1BjtHCJY7dzm-LB011qRSoveVUqU8Wbo-SQYSOYr9IMVYP063lEVQCr3G9ErfMMDLx0JWLLcyDsdbz4OsKdCpzFuBZU-g6Zf2gdwauTuWm-WmCCd-yXe9BTojKOCWOHXliBKmmUzJm2IMYt21G3fa06qgsJNMVB57og9hfef8SNtdDMCfyM41WFZcL6sAWT7ulje7X4_yE-ywIBR2t6-OHWw7iVq5THHQ_FPz6JAbbD-P0i4FQ6Ze6X30U>) below is my take on this:
select distinct on (t1.id<https://url11b.mailanyone.net/v1/?m=1mrxkX-0008NY-3a&i=57e1b682&c=9KJuH3f7Mex6ks3lu5cPQvny5O6XFhyh-eH6svguvEFAWIQVxt2r_IF0pHeMBwTmQ0XE2xxmCMWnaDbfaMp5XAI5b_Dcd0CClUb5RGp_7jG34xScneDpX0vYGQ4vngdzDwWvGm-5Vrd9D1J_a38Rof5Lup9vwpMo_mGHYNGYu4f1wTbn36eA3P8CNM6_0ioVin4dzKvW5LcIMv9Lun1VjfK4qpwRVaVWuelYCEa3WiGmNGu1zEEv9fA7t7zcNFT0>) t1.id<https://url11b.mailanyone.net/v1/?m=1mrxkX-0008NY-3a&i=57e1b682&c=9KJuH3f7Mex6ks3lu5cPQvny5O6XFhyh-eH6svguvEFAWIQVxt2r_IF0pHeMBwTmQ0XE2xxmCMWnaDbfaMp5XAI5b_Dcd0CClUb5RGp_7jG34xScneDpX0vYGQ4vngdzDwWvGm-5Vrd9D1J_a38Rof5Lup9vwpMo_mGHYNGYu4f1wTbn36eA3P8CNM6_0ioVin4dzKvW5LcIMv9Lun1VjfK4qpwRVaVWuelYCEa3WiGmNGu1zEEv9fA7t7zcNFT0>,t2.id<https://url11b.mailanyone.net/v1/?m=1mrxkX-0008NY-3a&i=57e1b682&c=MB1btGDh1LRWWz9_qH-7fMRyiv5SylMmhNclzuXCnly7_WjBFSzNNi8uoxskAeJ4w4vgf__otNnIlUBwzIcTXhXbUpsTFsuilzyfmYd9zwlz05eB_sxpFqejtXwlOFEeYlEQLMRezJUhez4-Az3YSqWptDxKMQo3xICyLgQ_J7X-Y60pOXOjXiF2bxqaDL7qbXenH7LOd0rsTK8ovDtTM0OQ4EBcMuXDxIvRB7k1Wb5YS5ejXpy-7iOFHBuUUIEB>
from t t1,t t2
where ST_Area(t1.geom)>ST_Area(t2.geom)
and ST_Intersects(t1.geom,t2.geom)
order by t1.id<https://url11b.mailanyone.net/v1/?m=1mrxkX-0008NY-3a&i=57e1b682&c=9KJuH3f7Mex6ks3lu5cPQvny5O6XFhyh-eH6svguvEFAWIQVxt2r_IF0pHeMBwTmQ0XE2xxmCMWnaDbfaMp5XAI5b_Dcd0CClUb5RGp_7jG34xScneDpX0vYGQ4vngdzDwWvGm-5Vrd9D1J_a38Rof5Lup9vwpMo_mGHYNGYu4f1wTbn36eA3P8CNM6_0ioVin4dzKvW5LcIMv9Lun1VjfK4qpwRVaVWuelYCEa3WiGmNGu1zEEv9fA7t7zcNFT0>,ST_Area(t2.geom) desc;
Enclose this in CTE and then just select "outer" polygons based on left column and "inner" based on right column.
wt., 30 lis 2021 o 07:30 <paul.malm at lfv.se<mailto:paul.malm at lfv.se>> napisał(a):
Hi,
I have a polygon layer where polygons are inside other polygons (could be polygons in polygons in polygons…).
I would like to separate them in 2 different layers, one layer with the outer most polygon and one layer with all polygons that has a surrounding polygon.
Is there a smart way to do this in SQL?
Thanks,
Paul
[1_LFV_svensk_96]
Paul Malm
Operations / AIM Flyginfo SE
Direkt 08-797 70 23 Mobil 070-860 11 15
paul.malm at lfv.se<mailto:paul.malm at lfv.se>
Besöks- och postadress
LFV Flyginfo SE
Af Pontins väg 6
115 21 STOCKHOLM
Tänk på miljön innan du skriver ut detta e-postmeddelande.
_______________________________________________
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=1mrxkX-0008NY-3a&i=57e1b682&c=_uxAORlsjsjbGN7PYxaRnlHKfS3HxcXJKlgGb8oZkkQQ7KeYaBvj63dVA9iAwHJFsiNVtticrWS07KEdiJM7B5FK1Yaq43by7MzodRIkmRm9i5TYx5AMTzK7gTO_Qito-2qIlo3A7Ie0mipOiHVt2vWc3OvcW4JCOK1neLV9mQTj4o8JyaGnGIG3M_sLpXMopSV6-fN3Qc5DR2cPPfCZ4ySDLNEacKWQP5buUcMtkzhGviuZh24fRX4mSjaR9t7d9eZEeB6bPE0YFX2AgKUEY2w9g5s3z41MQ6FNL6ZvrJU>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20211130/47571ac5/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.jpg
Type: image/jpeg
Size: 3211 bytes
Desc: image001.jpg
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20211130/47571ac5/attachment.jpg>
More information about the postgis-users
mailing list