QspatiaLite Use Case: Find Number of Species from Point Data

Here's a short follow up on some previous posting about the use of QspatiaLite for the aggregation of species distribution data. In this case the species data comes as a point layer. For each cell of a 1000 x 1000 m grid (1) the number of individuals per species, (2) the total number of individuals and (3) the number of different species should be calculated.


There is a layer with 10 different species (variabel name is "sp") across the whole extent with names "1", "2", "3", .. , "10" and a layer with the grid cells (variable name = "id") numbered consecutively, from 1 to 150.

In the attribute table of the below screenshot you see that I selected the grid cell with id=1 and the points (=species) within this cell. There are 8 individuals - "4", "5", "6" and "10" occure once, whereas "2" and "8" occure twice.

The query table in the screenshot is the result for (3).

For (1) you have to query for points/species within grid cells and group over grid cells and species and take the count from that aggregation

SELECT
 t.gID AS gID,
 t.sp AS Sp,
 count(*) AS NrIndSp
FROM (SELECT
  g.id AS gID, 
  s.sp AS sp
FROM grid AS g JOIN Sp_distr AS s 
ON within(s.Geometry, g.Geometry)
) as t GROUP BY t.gId, t.sp

For (2) you simple need to query for points/species within grid cells and aggregate over grid cells:

Select 
 t.gID,
 count(*) as NrInd
From (SELECT
  g.id AS gID, 
  s.sp AS sp
FROM grid AS g JOIN Sp_distr AS s 
ON within(s.Geometry, g.Geometry)
) as t 
GROUP BY t.gID 
ORDER BY t.gID

For (3) you'll first need to aggregate over grid cells and points/species, and then again aggregate over this query table by grid cells which will finally give you the distinct species!

SELECT 
 v.gID,
 count(*) AS SpNr
FROM (SELECT 
 t.gID,
 t.sp
FROM (SELECT
  g.id AS gID, 
  s.sp AS sp
FROM grid AS g JOIN Sp_distr AS s 
ON within(s.Geometry, g.Geometry)
) as t GROUP BY t.gId, t.sp
) as v GROUP BY v.gId