3

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.

0

1 Answer 1

5

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.

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.