Skip to content

Instantly share code, notes, and snippets.

@ramiroaznar
Created January 23, 2017 11:01
Show Gist options
  • Save ramiroaznar/16ac1bb567c0e8919cf890f21422737c to your computer and use it in GitHub Desktop.
Save ramiroaznar/16ac1bb567c0e8919cf890f21422737c to your computer and use it in GitHub Desktop.
How to calculate Shannon Diversity Index with PostgreSQL, PostGIS and CARTO

Made by @AbelVM.

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment