1

I have a multipolygon layer with several regions (green circles) that are identified through numerical fids. Within each region are multiple points (orange dots) with individual pointIDs. See example for region 7 below: enter image description here Here is a visualization: enter image description here My problem is that the circles overlap so much that some might have the same set of pointIDs within them. Therefore, the goal is that for all two or more circles that cover the same points(/contain the same pointIDs) I only want one circle to remain while all others are deleted. Which circle stays doesn't matter. But it is important that the individual PointIDs for each circle that remains are preserved.
I have tried reaching that selection by using the "Select by expression" in QGIS but I am not familiar with the expressions and not sure how to fully implement my query.
I was thinking of something like this, but it is invalid and I doubt that array_distinct would actually find two "PointID-lists" identical if they have different fids:

with_variable('temp',
    array_agg( "PointID" , order by  "PointID" , group by "fid"),
    array_distinct(@temp)
 )

Does anyone know how to write an expression that selects all fids from the layer that have a distinct set of PointIDs?
As an alternative to "Select by expression", I think a virtual layer may be an option. I actually tried using a SQL query that I found online to create a virtual layer with the selection, but QGIS doesn't seem to recognize the array_agg function here:

WITH cte
AS
(
SELECT t.fid,
       array_agg(t.PointID ORDER BY t.PointID) PointIDs
       FROM JoinedLayer t
       GROUP BY fid
)
SELECT t1.*
       FROM JoinedLayer t1
            INNER JOIN cte c1
                       ON c1.fid = t1.fid
       WHERE EXISTS (SELECT *
                            FROM cte c2
                            WHERE c2.fid <> c1.fid
                                  AND c2.PointIDs = c1.PointIDs);
8
  • 1
    How does delete duplicate geometries not work in this case?
    – Erik
    Aug 8, 2022 at 10:17
  • Do not use <br/> for line breaks. Use simple return button to add a line break and ``` to format code.
    – Babel
    Aug 8, 2022 at 10:18
  • Did I understand correctly: you have overlapping polygons and some of the points are within several of the polygons and you want to keep just one of the polygons? Your question is not really clear - can you add a screenshot?
    – Babel
    Aug 8, 2022 at 10:20
  • As can be seen from your screenshot, the layer is the result of a join operation. Maybe the problem can be solved when joining? However, please make clear what is you exact problem. Sharing sample data could help understanding your task.
    – Babel
    Aug 8, 2022 at 10:25
  • I added a screenshot for visualization. All points are saved within a circle (because I joined the point and the circle-polygon layer). The table in the example picture shows how several PointIDs are saved in the attribute table for the circle 7. However, some circles are overlapping so much that they may contain the exact same set of points and I want to delete these kind of duplicates. I hope this clarifies my question.
    – user204024
    Aug 8, 2022 at 11:18

1 Answer 1

3

I understand you have multiple polygons and a table of point-polygon intersections, and you want to keep only one polygon out of the many that share exactly the same point-polygon intersections. That is, if a polygon has more or less or different intersected points, it should be kept as "unique".

You can create the list of polygon ID to keep via a virtual layer, and you can then join this list to the source to export them to a new layer.

Go to the menu Layer > Add Layer > Add/Edit Virtual Layer... and enter the following query.

This query is done in three steps:

  1. from the source, simply select the polygon ID (fid) and the associated points point_id. Order by point ID.
  2. for each fid, use group_concat() to aggregate the pointID, which were conveniently ordered in the previous step.
  3. for each combination of fid- group of pointID, keep one (random) record.
select fid, gp_points
From (
 select fid, group_concat( PointID,';') gp_points
 FROM (
  SELECT FID, PointID
  from JoinedLayer
  order by PointID
  ) ordered
 GROUP BY FID
 )gp_ordered
group by gp_points
1
  • Perfect, thank you, that is exactly what I was looking for!
    – user204024
    Aug 8, 2022 at 15:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.