<div dir="ltr">Hi, <div><br></div><div>The problem in your query is that it doesn't unnest/ST_Dump some intermediate states. Here's working one:</div><div><br></div><div><br></div><div><pre style="background-color:rgb(43,43,43);color:rgb(169,183,198);font-family:"Fira Code""><span style="color:rgb(204,120,50);font-weight:bold">drop table if exists </span>split_parkings<span style="color:rgb(204,120,50)">;<br></span><span style="color:rgb(204,120,50);font-weight:bold">create table </span>split_parkings <span style="color:rgb(204,120,50);font-weight:bold">as </span>(<br><span style="color:rgb(204,120,50);font-weight:bold">with<br></span><span style="color:rgb(204,120,50);font-weight:bold"> input as </span>(<br> <span style="color:rgb(204,120,50);font-weight:bold">select<br></span><span style="color:rgb(204,120,50);font-weight:bold"> ref as </span>poly_id<span style="color:rgb(204,120,50)">,<br></span><span style="color:rgb(204,120,50)"><br></span><span style="color:rgb(204,120,50)"> </span>geom <span style="color:rgb(204,120,50);font-weight:bold">as </span>geom<br> <span style="color:rgb(204,120,50);font-weight:bold">from<br></span><span style="color:rgb(204,120,50);font-weight:bold"><br></span><span style="color:rgb(204,120,50);font-weight:bold"> </span>juno_areas<br> <span style="color:rgb(204,120,50);font-weight:bold">where type </span>= <span style="color:rgb(106,135,89)">'airport_parking_lot'<br></span><span style="color:rgb(106,135,89)"> </span>)<span style="color:rgb(204,120,50)">,<br></span><span style="color:rgb(204,120,50)"> </span>unlabeled_cloud <span style="color:rgb(204,120,50);font-weight:bold">as </span>(<br> <span style="color:rgb(204,120,50);font-weight:bold">select<br></span><span style="color:rgb(204,120,50);font-weight:bold"> </span>poly_id<span style="color:rgb(204,120,50)">,<br></span><span style="color:rgb(204,120,50)"> </span>geom<span style="color:rgb(204,120,50)">,<br></span><span style="color:rgb(204,120,50)"> </span>(<span style="color:rgb(255,198,109);font-style:italic">ST_Dump</span>(<span style="color:rgb(255,198,109);font-style:italic">ST_GeneratePoints</span>(geom<span style="color:rgb(204,120,50)">, </span><span style="color:rgb(104,151,187)">1000</span>))).geom pt<span style="color:rgb(204,120,50)">,<br></span><span style="color:rgb(204,120,50)"> </span>(<span style="color:rgb(255,198,109);font-style:italic">ST_Area</span>(geom) / <span style="color:rgb(104,151,187)">1000</span>) :: <span style="color:rgb(204,120,50);font-weight:bold">int </span>k<br> <span style="color:rgb(204,120,50);font-weight:bold">from input<br></span><span style="color:rgb(204,120,50);font-weight:bold"> group by </span><span style="color:rgb(104,151,187)">1</span><span style="color:rgb(204,120,50)">, </span><span style="color:rgb(104,151,187)">2<br></span><span style="color:rgb(104,151,187)"> </span><span style="color:rgb(204,120,50);font-weight:bold">order by </span>geom<br> )<span style="color:rgb(204,120,50)">,<br></span><span style="color:rgb(204,120,50)"> </span>labeled_cloud <span style="color:rgb(204,120,50);font-weight:bold">as </span>(<br> <span style="color:rgb(204,120,50);font-weight:bold">select<br></span><span style="color:rgb(204,120,50);font-weight:bold"> </span>poly_id<span style="color:rgb(204,120,50)">,<br></span><span style="color:rgb(204,120,50)"> </span>pt<span style="color:rgb(204,120,50)">,<br></span><span style="color:rgb(204,120,50)"> </span><span style="color:rgb(255,198,109);font-style:italic">ST_ClusterKMeans</span>(pt<span style="color:rgb(204,120,50)">, </span>k)<br> <span style="color:rgb(204,120,50);font-weight:bold">over </span>(<br> <span style="color:rgb(204,120,50);font-weight:bold">partition by </span>poly_id<br> ) cluster_id<br> <span style="color:rgb(204,120,50);font-weight:bold">from<br></span><span style="color:rgb(204,120,50);font-weight:bold"> </span>unlabeled_cloud<br> )<span style="color:rgb(204,120,50)">,<br></span><span style="color:rgb(204,120,50)"> </span>labeled_centers <span style="color:rgb(204,120,50);font-weight:bold">as </span>(<br> <span style="color:rgb(204,120,50);font-weight:bold">select<br></span><span style="color:rgb(204,120,50);font-weight:bold"> </span>poly_id<span style="color:rgb(204,120,50)">,<br></span><span style="color:rgb(204,120,50)"> </span>cluster_id<span style="color:rgb(204,120,50)">,<br></span><span style="color:rgb(204,120,50)"> </span><span style="color:rgb(255,198,109);font-style:italic">ST_Centroid</span>(<span style="color:rgb(255,198,109);font-style:italic">ST_Collect</span>(pt)) center<br> <span style="color:rgb(204,120,50);font-weight:bold">from </span>labeled_cloud<br> <span style="color:rgb(204,120,50);font-weight:bold">group by </span>poly_id<span style="color:rgb(204,120,50)">, </span>cluster_id)<span style="color:rgb(204,120,50)">,<br></span><span style="color:rgb(204,120,50)"> </span>voronoi_poly <span style="color:rgb(204,120,50);font-weight:bold">as </span>(<br> <span style="color:rgb(204,120,50);font-weight:bold">select<br></span><span style="color:rgb(204,120,50);font-weight:bold"> </span>poly_id<span style="color:rgb(204,120,50)">,<br></span><span style="color:rgb(204,120,50)"> </span>(<span style="color:rgb(255,198,109);font-style:italic">ST_Dump</span>(<span style="color:rgb(255,198,109);font-style:italic">ST_VoronoiPolygons</span>(<span style="color:rgb(255,198,109);font-style:italic">ST_Collect</span>(center)))).geom<br> <span style="color:rgb(204,120,50);font-weight:bold">from </span>labeled_centers<br> <span style="color:rgb(204,120,50);font-weight:bold">group by </span>poly_id<br> )<br><span style="color:rgb(204,120,50);font-weight:bold">select </span>v.poly_id<span style="color:rgb(204,120,50)">, </span><span style="color:rgb(255,198,109);font-style:italic">ST_Intersection</span>(v.geom<span style="color:rgb(204,120,50)">, </span>i.geom)<br><span style="color:rgb(204,120,50);font-weight:bold">from </span>voronoi_poly v <span style="color:rgb(204,120,50);font-weight:bold">join input </span>i <span style="color:rgb(204,120,50);font-weight:bold">on </span>i.poly_id = v.poly_id)<span style="color:rgb(204,120,50)">;<br></span></pre></div><div><br></div><div>Produces:</div><div><br></div><div><br></div><div><br></div><div><br></div><img src="cid:1641788e5e9cb971f161" alt="image.png" class="" style="max-width: 100%; opacity: 1;"><div>Trivial ones are split this way:</div><div><br></div><div><img src="cid:164178a9cb3cb971f162" alt="image.png" class="" style="max-width: 100%; opacity: 1;"><br></div><div><br></div><div><br></div><div>Good luck!</div><div><br></div><div><br></div><div><br></div><br><div class="gmail_quote"><div dir="ltr">вс, 17 июн. 2018 г. в 17:55, Jibran Khan <<a href="mailto:jibran@envs.au.dk">jibran@envs.au.dk</a>>:<br></div><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div lang="EN-US" link="blue" vlink="purple">
<div class="m_-8816071745129299852WordSection1">
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Hi,<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Thanks a lot for your help. Here is my first attempt.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">/*## Start ##*/<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">With foo as(<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Select<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> gid poly_id, geom,<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> ST_GeneratePoints(geom, 1000) ptcloud,<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> (ST_Area(geom)/100)::int k<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">From<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> My_polygon_shp<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Group by gid, geom<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Order by gid, geom),<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> bar as(<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Select<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> poly_id, geom, ptcloud,<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> ST_VoronoiPolygons(ST_Centroid(ST_Collect(ptcloud))) vorpoly,<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> ST_ClusterKMeans(ptcloud, k) over() kmeans<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">From<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> foo<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Group by poly_id, geom, ptcloud, k<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Order by poly_id)<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Select<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> poly_id,<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> st_intersection(geom, vorpoly)<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">From<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> bar<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Order by poly_id<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">/*## End ##*/<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">The query may not be efficiently written but it works. However, I don’t get any significant result.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Please see if you can you reproduce the same error or above query can be improved?<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Best,<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Jb
<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-GB" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif"> postgis-users <<a href="mailto:postgis-users-bounces@lists.osgeo.org" target="_blank">postgis-users-bounces@lists.osgeo.org</a>>
<b>On Behalf Of </b>Darafei "Kom?pa" Praliaskouski<br>
<b>Sent:</b> 17. juni 2018 12:26<br>
<b>To:</b> PostGIS Users Discussion <<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a>><br>
<b>Subject:</b> Re: [postgis-users] Split polygons based on polygons' area<u></u><u></u></span></p></div></div><div lang="EN-US" link="blue" vlink="purple"><div class="m_-8816071745129299852WordSection1">
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<p class="MsoNormal">The way I see it, for any kind of polygon:<u></u><u></u></p>
<div>
<p class="MsoNormal"> - 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);<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> - Decide how many parts you'd like to split into, (ST_Area(geom)/max_area), let it be K;<br>
- Take KMeans of the point cloud with K clusters;<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> - For each cluster, take a ST_Centroid(ST_Collect(point));<u></u><u></u></p>
</div>
<div>
<p class="MsoNormal"> - Feed these centroids into ST_VoronoiPolygons, that will get you a mask for each part of polygon;<br>
- ST_Intersection of original polygon and each cell of Voronoi polygons will get you a good split of your polygon into K parts.<br>
<br>
Will it work for you?<u></u><u></u></p>
</div>
</div>
<p class="MsoNormal"><u></u> <u></u></p>
<div>
<div>
<p class="MsoNormal">вс, 17 июн. 2018 г. в 13:11, Jibran Khan <<a href="mailto:jibran@envs.au.dk" target="_blank">jibran@envs.au.dk</a>>:<u></u><u></u></p>
</div>
<blockquote style="border:none;border-left:solid #cccccc 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm">
<div>
<div>
<p class="MsoNormal"><span lang="EN-GB">Hello everyone,</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB"> </span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB">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):</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB"> </span><u></u><u></u></p>
<p class="MsoNormal"><img border="0" width="262" height="308" style="width:2.7291in;height:3.2083in" id="m_-8816071745129299852m_-7525801946671791139Picture_x0020_1"><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB"> </span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB">The following query:</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB"> </span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB">/*Start of query*/</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB">Select
</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB"> a.gid poly_id, count(b.geom) pt_cnt,</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB"> st_area(a.geom)::int poly_area</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB">FROM</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB"> poly a</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB">LEFT JOIN</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB"> pts b
</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB">ON st_contains(a.geom, b.geom)
</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB">GROUP BY a.gid</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB">Order by a.gid;</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB">/*End of query*/</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB"> </span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB">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
<i>straightforward</i> like above). The division does not have to be “strictly” equal. Does anyone know or have any idea how this can be achieved?</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB"> </span><u></u><u></u></p>
<p class="MsoNormal"><b><span lang="EN-GB">Side Notes:</span></b><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB"> </span><u></u><u></u></p>
<p class="m_-8816071745129299852m-7525801946671791139msolistparagraph"><span lang="EN-GB" style="font-family:Symbol">·</span><span lang="EN-GB" style="font-size:7.0pt">
</span><span lang="EN-GB">I earlier posted this question here at <a href="https://gis.stackexchange.com/questions/286184/postgis-how-do-i-split-polygon-in-equal-parts-based-on-polygon-area" target="_blank">
GIS SE</a></span><u></u><u></u></p>
<p class="m_-8816071745129299852m-7525801946671791139msolistparagraph"><span lang="EN-GB" style="font-family:Symbol">·</span><span lang="EN-GB" style="font-size:7.0pt">
</span><span lang="EN-GB">Also, came across <a href="https://gis.stackexchange.com/questions/239801/how-can-i-split-a-polygon-into-two-equal-parts-along-a-n-s-axis" target="_blank">
this</a> relevant question but I guess my problem is different</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB"> </span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB">Any help or idea(s) would be highly appreciated.</span><u></u><u></u></p>
<p class="MsoNormal"><span lang="EN-GB"> </span></p></div></div></blockquote></div></div></div></blockquote><div><br></div></div></div>