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 <jib...@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-boun...@lists.osgeo.org> *On Behalf
> Of *Darafei "Kom?pa" Praliaskouski
> *Sent:* 17. juni 2018 12:26
> *To:* PostGIS Users Discussion <postgis-users@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 <jib...@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.
>
>
>
>
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
