Counting points in each polygon so that points do not repeat QGIS

Question

I have a points layer and a polygon layer. I need to count points in each polygon/buffer so that points do not repeat. For instance, here is an example with 4 polygons and 8 points inside in total.

What I get from ArcGIS Summarize Within, as well as from QGIS Count Points in Polygon is that:

1. polygon consist of 2 points;
2. polygon - 4 points;
3. polygon - 2 points;
4. polygon - 2 points.

However, the count of 1. and 2. polygon is incorrect because results show 6 points in total even though there are only 4, as you can see in the picture. The result should look something like this:

1. polygon consist of 2 points;
2. polygon - 2 points;
3. polygon - 2 points;
4. polygon - 2 points, so that points do not overlap in several polygons.

If I had only a few polygons, this would be quite easy, but as I have several polygons and points, manual work is not a real solution.

I'm pretty sure that this could be done very easily with some expression, however, I could not figure it out.

Answer 1

With a simple SQL query, you can get what you want. Under QGIS, Database menu > DB Manager > Virtual Layers :

WITH poly_pts AS (
  SELECT
    MIN(polygons.id) AS poly_id,
    points.id AS pt_id
  FROM polygons, points
  WHERE ST_WITHIN(points.geometry, polygons.geometry)
  GROUP BY points.id
)

SELECT poly_pts.poly_id, COUNT(*) AS pts_count
FROM poly_pts
GROUP BY poly_pts.poly_id

• You need two layers named polygons and points, each must have an id field unique
• poly_pts selects the lowest polygons ID that contains each points feature
• The second query just count the number of points for each polygons ID.

It gives :

poly_id	pts_count
1	2
2	2
3	2
4	2

Answer 2

For QGIS 3.16+ you can use the overlay_nearest expression to achieve this. The idea is to find for each point the nearest centroid of any of the polygons and assign thus each point to one polygon. Than you can use count the numbers of points assigned to the same polygon (see screenshot below).

In the following expression, change the values of polygon, points and id to the layer and field names you use:

1. On the point layer, use the field calculator to create a field in_polygon with this expression: array_first ( overlay_nearest( 'polygon', id)) (= small black labels in the screenshot)

2. On the polygon layer, create a new field counting the no. of points belonging to it:

with_variable (
    'id',
    $id,
    aggregate( 
        'points', 
        'count', 
        in_polygon, 
        filter:= in_polygon=  @id
    )
)

Screenshot: each point (white) is connected (for visualization purpose only) to the nearest polygon-centroid - thus "assigning" each point to one (and only one) polygon. Based on this, for each polygon count only the points "attached" to itself:

Edit: Reaction to @J. Monticolo's comment

If you have points outside the polygon, the solution need another addition: you don't want to count points that are outside the polygon. In this case, in step one, replace the expression from above with this one here (step 2 remains the same):

if (
    within( 
        $geometry,    
        buffer (
            aggregate( 
                'polygon', 
                'collect', 
                $geometry
            ),
            0
        )
    ), 
    array_first ( 
        overlay_nearest( 
            'polygon', 
            id
        )
    ),
    NULL
)

See the result: points outside the polygons are not assigned to any polygon and thus they are not counted: