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

    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
    

  • No comments :

    Post a Comment