[postgis-users] Overlap analysis / polygonize precision problem

[Alexandre Neto]

Hi,

I finally got to test this situation a bit more. The culprit seems to be
the Union process:

with this to get all rings from the polygons, and which return their
linestrings in full precision:

create materialized view isolated_polygons as (
select st_exteriorring((st_dumprings((st_dump(geom)).geom)).geom) as geom
from OUTPUTS_FINAIS.estabelecimentos_as_400
)

All these produced a reduced precision output:

create materialized view boundaries as
(
SELECT ST_Union(geom) AS geom
FROM isolated_polygons
);

create materialized view boundaries2 as
(
SELECT ST_UnaryUnion(st_collect(geom)) AS geom
FROM isolated_polygons
);

create materialized view boundaries3 as
(
SELECT ST_MemUnion(geom) AS geom
FROM isolated_polygons
);

BTW, my data is in EPSG:4326

I am now trying to convert data to another EPSG, to see what happens.

Thanks,

Alexandre Neto

On Tue, Mar 17, 2020 at 4:19 PM Alexandre Neto <senhor.neto at gmail.com>
wrote:

> I will, thank you!
>
> On Tue, Mar 17, 2020 at 4:10 PM Martin Davis <mtnclimb at gmail.com> wrote:
>
>> 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.
>>
>> Even better would be to provide the dataset which is causing the problem,
>> to aid in sorting this out.
>>
>> On Mon, Mar 16, 2020 at 7:16 PM Alexandre Neto <senhor.neto at gmail.com>
>> wrote:
>>
>>> Hi,
>>>
>>> I am running a Overlay analysis, and I am following what is proposed by
>>> Paul Ramsey in this article:
>>>
>>> http://blog.cleverelephant.ca/2019/07/postgis-overlays.html
>>>
>>> I am using this query on PostgreSQL 10 and PostGIS
>>>
>>> With isolated_polygons as
>>> (
>>> select st_exteriorring((st_dumprings((st_dump(geom)).geom)).geom) as geom
>>> from OUTPUTS_FINAIS.estabelecimentos_as_400
>>> )
>>> , boundaries as (
>>> SELECT st_union(geom) AS geom
>>> FROM isolated_polygons
>>> )
>>> , poligonos as (
>>> SELECT (st_dump(st_polygonize(geom))).geom as geom
>>> FROM boundaries
>>> )
>>> , n_poligonos as (
>>> SELECT row_number() over() as id, geom
>>> FROM poligonos
>>> )
>>> SELECT p.id, count(id) as n_sobreposicoes,
>>> p.geom::geometry(polygon,4326)
>>> into temp.sobreposicoes_400m
>>> FROM n_poligonos as p
>>> join outputs_finais.estabelecimentos_as_400 as t
>>> on st_contains(t.geom,st_pointonsurface(p.geom))
>>> GROUP BY id, p.geom;
>>>
>>> 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.
>>>
>>> When I run it with the full set (around 4800 polygons)
>>>
>>> The precison of the output polygons gets truncated and instead of
>>> detailed polygons I get a "pixelied" output:
>>>
>>> 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))
>>> 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))
>>> POLYGON ((-8.54 41.02, -8.55 41.02, -8.55 41.03, -8.54 41.03, -8.54
>>> 41.02))
>>> POLYGON ((-8.39 40.17, -8.4 40.17, -8.4 40.18, -8.39 40.18, -8.39 40.17))
>>> 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))
>>>
>>> My gut feeling is that this is happening at the polygonize level, but I
>>> am not sure.
>>>
>>> I have also tried to use st_node + st_collect getting the same kind of
>>> results
>>>
>>> Any idea of what can this be?
>>>
>>> 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.
>>>
>>> I am using
>>>
>>> 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
>>>
>>> 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
>>>
>>> Thanks,
>>>
>>> Alexandre Neto
>>>
>>>
>>> _______________________________________________
>>> postgis-users mailing list
>>> postgis-users at lists.osgeo.org
>>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>>
>> _______________________________________________
>> postgis-users mailing list
>> postgis-users at lists.osgeo.org
>> https://lists.osgeo.org/mailman/listinfo/postgis-users
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200327/af770289/attachment.html>