Here is working example of Martin's suggestion, for a job that sounds fairly similar: https://github.com/bcgov/harvest-restrictions/blob/main/sql/overlay.sql
> On Jul 7, 2025, at 4:45 PM, Martin Davis <mtncl...@gmail.com> wrote: > > 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.cel...@gmail.com > <mailto:laurent.cel...@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.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. >>
