How to calculate Shannon Diversity Index with PostgreSQL, PostGIS and CARTO

readme.md

Made by @AbelVM.

query.sql

WITH
trees as(
 SELECT * FROM tree_eu_sp
),
polys as(
 SELECT * FROM eu_units
),
step_0 as( -- add polygon id to the tres
   SELECT
       t.*,
       p.cartodb_id as pid
   FROM trees t right join polys p
   on st_intersects(t.the_geom, p.the_geom)
),
step_1 as ( -- calc pi per polygon and species and assign to each tree
   SELECT
        pid,
        species_na,
       (count(1) over(partition by pid, species_na))::numeric / (count(1) over(partition by pid))::numeric as pi
    FROM step_0
),
step_2 as( -- filter 1 pi per polygon, specie
    SELECT
        pid,
        species_na,
        MAX(pi) as pi
    FROM step_1
    group by pid, species_na
)  -- finally, calc the Shannon index
select 
    pid,
    -1*sum(pi*(ln(pi))) as sh
FROM step_1
group by pid