QspatiaLite Use Case: Query for Species Richness within Search-Radius

Following up my previous blogpost on using SpatiaLite for the calculation of diversity metrics from spatial data, I'll add this SQL-query which counts unique species-names from the intersection of species polygons and a circle-buffer around centroids of an input grid. The species number within the bufferarea are joined to a newly created grid. I use a subquery which grabs only those cells from the rectangular input grid, for which the condition that the buffer-area around the grid-cell's centroid covers the species unioned polygons at least to 80%.

Example data is HERE. You can use the shipped qml-stylefile for the newly generated grid. It labels three grid-cells with the species counts for illustration.

Import grid- and Sp_distr-layers with QspatiaLite Plugin

Run query and choose option "Create spatial table and load in QGIS", mind to set "geom" as geometry column

select 
    g1.PKUID as gID,
    count (distinct s.species) as sp_num_inbu, 
    g1.Geometry AS geom
from (
 select g.*
 from(select Gunion(geometry) as geom
           from Sp_distr) as u, grid as g
 where area(intersection(buffer(centroid(g.geometry), 500), u.geom)) > pow(500, 2)*pi()*0.8
) as g1 join Sp_distr as s on intersects(buffer(centroid( g1.Geometry), 500), s.Geometry)
group by gID