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
6 Dec 2014
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