[postgis-users] Overlap analysis / polygonize precision problem

[William Roper]

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.

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))'.

On Tue, 17 Mar 2020 at 02:16, 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20200317/b3edaa8a/attachment.html>