[postgis-users] Split polygons based on polygons' area

[Darafei "Komяpa" Praliaskouski]

Hi,

The problem in your query is that it doesn't unnest/ST_Dump some
intermediate states. Here's working one:


drop table if exists split_parkings;
create table split_parkings as (
with
        input as (
        select
            ref  as poly_id,

            geom as geom
        from

            juno_areas
        where type = 'airport_parking_lot'
    ),
        unlabeled_cloud as (
        select
            poly_id,
            geom,
            (ST_Dump(ST_GeneratePoints(geom, 1000))).geom pt,
            (ST_Area(geom) / 1000) :: int                  k
        from input
        group by 1, 2
        order by geom
    ),
        labeled_cloud as (
        select
            poly_id,
            pt,
            ST_ClusterKMeans(pt, k)
            over (
                partition by poly_id
                ) cluster_id
        from
            unlabeled_cloud
    ),
        labeled_centers as (
        select
            poly_id,
            cluster_id,
            ST_Centroid(ST_Collect(pt)) center
        from labeled_cloud
        group by poly_id, cluster_id),
        voronoi_poly as (
        select
            poly_id,
            (ST_Dump(ST_VoronoiPolygons(ST_Collect(center)))).geom
        from labeled_centers
        group by poly_id
    )
select v.poly_id, ST_Intersection(v.geom, i.geom)
from voronoi_poly v join input i on i.poly_id = v.poly_id);


Produces:




[image: image.png]
Trivial ones are split this way:

[image: image.png]


Good luck!




вс, 17 июн. 2018 г. в 17:55, Jibran Khan <jibran at envs.au.dk>:

> Hi,
>
>
>
> Thanks a lot for your help. Here is my first attempt.
>
>
>
> /*## Start ##*/
>
> With      foo as(
>
> Select
>
>                 gid poly_id, geom,
>
>                 ST_GeneratePoints(geom, 1000) ptcloud,
>
>                 (ST_Area(geom)/100)::int k
>
> From
>
>                 My_polygon_shp
>
> Group by gid, geom
>
> Order by gid, geom),
>
>
>
>                 bar as(
>
> Select
>
>                 poly_id, geom, ptcloud,
>
>                 ST_VoronoiPolygons(ST_Centroid(ST_Collect(ptcloud)))
> vorpoly,
>
>                 ST_ClusterKMeans(ptcloud, k) over() kmeans
>
> From
>
>                 foo
>
> Group by poly_id, geom, ptcloud, k
>
> Order by poly_id)
>
>
>
> Select
>
>                 poly_id,
>
>                 st_intersection(geom, vorpoly)
>
> From
>
>                 bar
>
> Order by poly_id
>
> /*## End ##*/
>
>
>
> The query may not be efficiently written but it works. However, I don’t
> get any significant result.
>
> Please see if you can you reproduce the same error or above query can be
> improved?
>
>
>
> Best,
>
> Jb
>
>
>
> *From:* postgis-users <postgis-users-bounces at lists.osgeo.org> *On Behalf
> Of *Darafei "Kom?pa" Praliaskouski
> *Sent:* 17. juni 2018 12:26
> *To:* PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> *Subject:* Re: [postgis-users] Split polygons based on polygons' area
>
>
>
> The way I see it, for any kind of polygon:
>
>  - Convert a polygon to a set of points proportional to the area by
> ST_GeneratePoints (the more points, the more beautiful it will be, guess
> 1000 is ok);
>
>  - Decide how many parts you'd like to split into,
> (ST_Area(geom)/max_area), let it be K;
>  - Take KMeans of the point cloud with K clusters;
>
>  - For each cluster, take a ST_Centroid(ST_Collect(point));
>
>  - Feed these centroids into ST_VoronoiPolygons, that will get you a mask
> for each part of polygon;
>  - ST_Intersection of original polygon and each cell of Voronoi polygons
> will get you a good split of your polygon into K parts.
>
> Will it work for you?
>
>
>
> вс, 17 июн. 2018 г. в 13:11, Jibran Khan <jibran at envs.au.dk>:
>
> Hello everyone,
>
>
>
> I need some help in terms of splitting polygons based on the polygon area.
> I am using PostgreSQL 9.5.12/PostGIS 2.3 on Windows 10 (x64). I have two
> tables i.e. polygon (poly) and points (pts) in my Postgres db. Some of the
> polygons contain points inside them while, some do not (sample scenario
> below):
>
>
>
>
>
> The following query:
>
>
>
> /*Start of query*/
>
> Select
>
>     a.gid poly_id, count(b.geom) pt_cnt,
>
>     st_area(a.geom)::int poly_area
>
> FROM
>
>     poly a
>
> LEFT JOIN
>
>     pts b
>
> ON st_contains(a.geom, b.geom)
>
> GROUP BY a.gid
>
> Order by a.gid;
>
> /*End of query*/
>
>
>
> Returns the area of polygons (from left to right) as 1079, 744, 340
> square-meter, respectively. I need to split these polygons' geometry based
> on the area i.e. if area is above 100 meter-square, then each polygon needs
> to be divided/split into “approximate equal” parts (assuming that majority
> of the polygons are *straightforward* like above). The division does not
> have to be “strictly” equal. Does anyone know or have any idea how this can
> be achieved?
>
>
>
> *Side Notes:*
>
>
>
> ·         I earlier posted this question here at GIS SE
> <https://gis.stackexchange.com/questions/286184/postgis-how-do-i-split-polygon-in-equal-parts-based-on-polygon-area>
>
> ·         Also, came across this
> <https://gis.stackexchange.com/questions/239801/how-can-i-split-a-polygon-into-two-equal-parts-along-a-n-s-axis>
> relevant question but I guess my problem is different
>
>
>
> Any help or idea(s) would be highly appreciated.
>
>
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180619/0ad7d688/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 43431 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180619/0ad7d688/attachment.png>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 29585 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20180619/0ad7d688/attachment-0001.png>