Qgis/Postgis : Multi Union (25 polygonal layers)

[Martin Davis]

I'd characterize your use case as "Overlay of overlapping polygonal
datasets".  The basic state-of-the art for solving this using PostGIS is
still the solution Paul outlined in
https://blog.cleverelephant.ca/2019/07/postgis-overlays.html (or see
https://dr-jts.github.io/postgis-patterns/overlay/overlay.html#count-overlap-depth-in-set-of-polygons
for more ideas).

Basically, you node and polygonize to make a flat coverage, and then join
back to the parent layers to determine attribution (including counts).

Doing this in a single query might be slow for very large datasets like
yours, though.  You might be able to partition your large dataset and run
smaller queries, possibly in parallel.  Also, it might be better to overlay
the small layers first, and then overlay that with the big layer.  And if
you don't care about overlaps in the big layer (or if there are none), that
makes it much easier, since you can process each big-layer polygon
independently (and ideally in parallel).

On Mon, Jul 7, 2025 at 1:16 PM celati Laurent <laurent.celati at gmail.com>
wrote:

> Dear all,
> I'm working with QGIS and PostGIS. As input, I have 25 polygonal layers
> covering a large area (multicities area). One of these data is a very large
> dataset (1 million objects). The other 24 are much smaller (a maximum of a
> hundred objects).
> For information, I should point out that some of these polygonal datasets
> are in "multi-part features" mode and others in "single-part features"
> mode. I imagine this may ultimately have a slight impact on the
> method/result. These 25 polygonal .shp files have highly variable,
> non-homogeneous/non-harmonized data structures. Each layer has a "data_id"
> field that allows  to define/link/reference, for each feature, its
> membership in the layer. For example, all values in the "data_id" field for
> the first layer have a value of '1'. For the second layer, the field values
> are '2', etc.
>
> My goal would be to be able to apply/adapt the existing QGIS geoprocessing
> tool called "Multiple Union":
>
> https://docs.qgis.org/3.40/en/docs/user_manual/processing_algs/qgis/vectoroverlay.html#union-multiple
>
> Below a screenshot from the QGIS documentation :
>
> [image: image.png]
>
> My goal would be to have an output file:
>
>
>    -  Which would be the result of the union/overlay of the 25 input
>    data. To use the terms of the QGIS documentation, the processing should
>    check for overlaps between features within the 25 layers and create
>    separate features for the overlapping and non-overlapping parts. This
>    "multiple union" geoprocessing seems interesting for my goal where there is
>    no overlap (a, NULL; b, NULL; c, NULL).
>
>
>    - For areas where there is an overlap, the QGIS union geoprocessing
>    creates as many identical overlapping features as there are features
>    participating in this overlap. This doesn't bother me. But since,
>    ultimately, I'd like a field in the result/output file to allow, for each
>    feature, to retrieve the list of input layers that participate/contribute
>    to this result feature (in order to retrieve the origin/source of the
>    data). I was wondering/thinking it might be better if only one feature was
>    created per overlapping area?
>
>
>    -  I'd like a field in the result file to allow, for each feature, to
>    retrieve the list of input layers that participate/contribute to this
>    result feature. In order to retrieve the origin/source of the data.
>
>
>    - Ideally, a field that allows you to retrieve the number (COUNT) of
>    layers that contribute to this feature (at least 1 layer, at most 25
>    layers).
>
>
>    - Regarding the non-geometric attributes/fields, I would like to be
>    able to specify the selection/define the list of fields I ultimately want
>    to keep. I don't want to keep all of the fields, but rather just some of
>    the fields for each of the 25 input layers.
>
>
> I imagine it's recommended to do this processing in PostGIS rather than
> QGIS? I can, if necessary, import my 25 SHP files into a PostGIS database.
> I also imagine it's important to keep in mind that the "multi-part
> features" / "single-part pieces/features" mode of the input layers can
> affect the result. If I'm using a PostGIS query, I was thinking it might be
> helpful to force all features to be in single-part mode (using the PostGIS
> 'st_dump' function?).
>
> In advance, Thanks so much for your help, guidance.
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20250707/9c457841/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image.png
Type: image/png
Size: 72276 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20250707/9c457841/attachment.png>