<div dir="ltr"><div>How about swapping out your CTEs for `CREATE MATERIALIZED VIEW` statements so you can inspect each stage and narrow down the issue. This might have some side effects about what is being kept in memory and so even get rid of the issue. <br></div><div><br></div><div>As for collecting not disjoint line groups I would assign a group id by a similar method to what you're trying. Run st_union on your input polygons, and split the output into single part polygons these are the 'group_polygons'. Assign each of those a 'group_id'. You can then assign the group ids to the original input polygons by doing a join on 'st_contains(group_polygons.geom, st_point_on_surface(input.geom))'.<br></div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, 17 Mar 2020 at 02:16, Alexandre Neto <<a href="mailto:senhor.neto@gmail.com">senhor.neto@gmail.com</a>> wrote:<br></div><blockquote class="gmail_quote" style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="ltr">Hi,<div><br></div><div>I am running a Overlay analysis, and I am following what is proposed by Paul Ramsey in this article:</div><div><br></div><div><a href="http://blog.cleverelephant.ca/2019/07/postgis-overlays.html" target="_blank">http://blog.cleverelephant.ca/2019/07/postgis-overlays.html</a><br></div><div><br></div><div>I am using this query on PostgreSQL 10 and PostGIS </div><div><br></div><div>With isolated_polygons as <br>(<br>select st_exteriorring((st_dumprings((st_dump(geom)).geom)).geom) as geom<br>from OUTPUTS_FINAIS.estabelecimentos_as_400<br>)<br>, boundaries as (<br>SELECT st_union(geom) AS geom<br>FROM isolated_polygons<br>)<br>, poligonos as (<br>SELECT (st_dump(st_polygonize(geom))).geom as geom<br>FROM boundaries<br>)<br>, n_poligonos as (<br>SELECT row_number() over() as id, geom<br>FROM poligonos<br>)<br>SELECT <a href="http://p.id" target="_blank">p.id</a>, count(id) as n_sobreposicoes, p.geom::geometry(polygon,4326)<br>into temp.sobreposicoes_400m<br>FROM n_poligonos as p <br> join outputs_finais.estabelecimentos_as_400 as t <br> on st_contains(t.geom,st_pointonsurface(p.geom))<br>GROUP BY id, p.geom;<br></div><div><br></div><div>When I use this with a sample set of input multipolygons, The results are what we would expect, all the overlapping polygons split and the count of the number of overlapping polygons.</div><div><br></div><div>When I run it with the full set (around 4800 polygons)</div><div><br></div><div>The precison of the output polygons gets truncated and instead of detailed polygons I get a "pixelied" output:</div><div><br></div><div>POLYGON ((-8.99 39.42, -8.99 39.43, -8.98 39.43, -8.97 39.43, -8.97 39.42, -8.98 39.42, -8.99 39.42))<br>POLYGON ((-8.19 39.56, -8.2 39.56, -8.2 39.57, -8.19 39.57, -8.18 39.57, -8.19 39.56))<br>POLYGON ((-8.54 41.02, -8.55 41.02, -8.55 41.03, -8.54 41.03, -8.54 41.02))<br>POLYGON ((-8.39 40.17, -8.4 40.17, -8.4 40.18, -8.39 40.18, -8.39 40.17))<br>POLYGON ((-8.57 38.83, -8.58 38.83, -8.59 38.84, -8.58 38.84, -8.57 38.84, -8.57 38.83))<br></div><div><br></div><div>My gut feeling is that this is happening at the polygonize level, but I am not sure.</div><div><br></div><div>I have also tried to use st_node + st_collect getting the same kind of results</div><div><br></div><div>Any idea of what can this be?</div><div><br></div><div>Since not all polygons are overlapping each other, I wanted to try to separate the analysis by chunks, but I fail to see how can I union or collect only not disjoint line groups.</div><div><br></div><div>I am using</div><div><br></div><div>PostgreSQL 10.12 (Ubuntu 10.12-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit<br></div><div><br></div><div>POSTGIS="2.5.3 r17699" [EXTENSION] PGSQL="100" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 4.9.3, 15 August 2016" GDAL="GDAL 2.2.3, released 2017/11/20" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.2.1" RASTER<br></div><div><br></div><div>Thanks,</div><div><br></div><div>Alexandre Neto</div><div><br></div><div><br></div></div>
_______________________________________________<br>
postgis-users mailing list<br>
<a href="mailto:postgis-users@lists.osgeo.org" target="_blank">postgis-users@lists.osgeo.org</a><br>
<a href="https://lists.osgeo.org/mailman/listinfo/postgis-users" rel="noreferrer" target="_blank">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></blockquote></div>