## 8 Apr 2015

### 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
```