<div dir="ltr">Hi,<div><br></div><div>I finally got to test this situation a bit more. The culprit seems to be the Union process:</div><div><br></div><div>with this to get all rings from the polygons, and which return their linestrings in full precision:</div><div><br></div><div><font face="monospace">create materialized view isolated_polygons as (<br>select st_exteriorring((st_dumprings((st_dump(geom)).geom)).geom) as geom<br>from OUTPUTS_FINAIS.estabelecimentos_as_400<br>)<br></font></div><div><br></div><div>All these produced a reduced precision output:</div><div><br></div><div><font face="monospace">create materialized view boundaries as<br>(<br>SELECT ST_Union(geom) AS geom<br>FROM isolated_polygons<br>);</font><br></div><div><br></div><div><font face="monospace">create materialized view boundaries2 as<br>(<br>SELECT ST_UnaryUnion(st_collect(geom)) AS geom<br>FROM isolated_polygons<br>);</font><br></div><div><br></div><div><font face="monospace">create materialized view boundaries3 as<br>(<br>SELECT ST_MemUnion(geom) AS geom<br>FROM isolated_polygons<br>);</font><br></div><div><br></div><div>BTW, my data is in EPSG:4326</div><div><br></div><div>I am now trying to convert data to another EPSG, to see what happens.</div><div><br></div><div>Thanks,</div><div><br></div><div>Alexandre Neto</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Mar 17, 2020 at 4:19 PM 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">I will, thank you!</div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Tue, Mar 17, 2020 at 4:10 PM Martin Davis <<a href="mailto:mtnclimb@gmail.com" target="_blank">mtnclimb@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">It seems odd that precision is being reduced that much. In order to isolate where it is happening, you should capture output from each stage of the CTE (which is easy to do, by just truncating the CTE after each query and doing a "SELECT * FROM cte_tbl" . Then post the results here if you need help analyzing them, and so we can investigate further.<div><br></div><div>Even better would be to provide the dataset which is causing the problem, to aid in sorting this out.</div></div><br><div class="gmail_quote"><div dir="ltr" class="gmail_attr">On Mon, Mar 16, 2020 at 7:16 PM Alexandre Neto <<a href="mailto:senhor.neto@gmail.com" target="_blank">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>
_______________________________________________<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>
</blockquote></div>