Counting all points in polygon where the name of polygon and point matches with SQL

Question

In QGIS I have two datasets: (1) one with points that have two fields, the unique identifier "PK" and "species" - either H, L or G (2) second with polygons of areas, again with unique "PK" and each polygon has "species" either H, L or G.

I'm not very experienced with SQLite and struggling to find the query that would return the total number of points which lie within a polygon with a matching species.

Answer 1

You can do this query :

SELECT
  area.pk,
  COUNT(*) AS nb_matching_species_within_the_area
FROM
  my_point_layer pt,
  my_area_layer area
WHERE
  ST_CONTAINS(area.geometry, pt.geometry)  -- inverse of ST_CONTAINS is ST_WITHIN
  AND pt.species = area.species
GROUP BY area.pk
;

Make a selection FROM your two datasets, make 2 conditions : area ST_CONTAINS points AND points pt.species equals area.species.

Then, GROUP BY area Primary Key and COUNT all records to have the number of matching records, so points within the area with the same species.