Skip to content

Instantly share code, notes, and snippets.

@nurtdinovadf

nurtdinovadf/0. derived_aves table

Last active July 15, 2017 09:03
Show Gist options
  • Save nurtdinovadf/345e1849cf259b6dda718487d58e68a8 to your computer and use it in GitHub Desktop.
Save nurtdinovadf/345e1849cf259b6dda718487d58e68a8 to your computer and use it in GitHub Desktop.
Download ZIP
ITIS database queries
Raw
0. derived_aves table
CREATE TABLE "public"."derived_aves" (
"complete_name" varchar(255),
"parent_tsn" int4 NOT NULL,
"rank_name" varchar(255) NOT NULL,
"tsn" int4 NOT NULL,
"rank_id" int4,
"level" int2,
"species" int4 NOT NULL,
"vernacular_names" json,
CONSTRAINT "derived_aves_pkey" PRIMARY KEY ("parent_tsn", "tsn", "species", "rank_name")
);
Raw
1. recursive aves query
with recursive parents(complete_name, parent_tsn, rank_name, tsn, rank_id, "level", species) as
(
SELECT distinct
tun.complete_name,
tun.parent_tsn,
tut.rank_name,
tun.tsn,
tut.rank_id,
h."level",
tun.tsn as species
FROM
taxonomic_units tun
join
taxon_unit_types tut
on tun.rank_id = tut.rank_id
and tut.rank_id = 220
and tun.kingdom_id = 5
and tun.name_usage = 'valid'
join
"hierarchy" h
on h.tsn = tun.tsn
and h.hierarchy_string like '%174371%'
UNION ALL
SELECT
tl.complete_name,
tl.parent_tsn,
tl.rank_name,
tl.tsn,
tl.rank_id,
tl."level",
p.species
FROM
parents p,
(
SELECT distinct
tun.complete_name,
tun.parent_tsn,
tut.rank_name,
tun.tsn,
tut.rank_id,
h."level",
tun.tsn as species
FROM
taxonomic_units tun
join
taxon_unit_types tut
on tun.rank_id = tut.rank_id
and tut.rank_id < 220
and tut.rank_id > 0
and tun.kingdom_id = 5
and tun.name_usage = 'valid'
join
"hierarchy" h
on h.tsn = tun.tsn
)
tl
WHERE
p.parent_tsn = tl.tsn
)
insert into derived_aves(complete_name, parent_tsn, rank_name, tsn, rank_id, "level", species)
select distinct p.complete_name, p.parent_tsn, p.rank_name, p.tsn, p.rank_id, p."level", p.species from parents p
Raw
2. vernacular names
update derived_aves set vernacular_names = sub1.vernacular_names from
(
select
tsn,
json_agg(vernacular_name) as vernacular_names
from
(
select distinct
dav.tsn,
v.vernacular_name
from derived_aves dav
join vernaculars v on dav.tsn = v.tsn
and (v."language" = 'English' or v."language" = 'unspecified')
) sub
group by 1
) sub1
where derived_aves.tsn = sub1.tsn
Raw
3. birds species flat pivot
SELECT
chess.species_tsn,
string_agg(kingdom, '') as kingdom,
string_agg(vkingdom, '') as vkingdom,
string_agg(subkingdom, '') as subkingdom,
string_agg(vsubkingdom, '') as vsubkingdom,
string_agg(infrakingdom, '') as infrakingdom,
string_agg(vinfrakingdom, '') as vinfrakingdom,
string_agg(division, '') as division,
string_agg(vdivision, '') as vdivision,
string_agg(subdivision, '') as subdivision,
string_agg(vsubdivision, '') as vsubdivision,
string_agg(infradivision, '') as infradivision,
string_agg(vinfradivision, '') as vinfradivision,
string_agg(superclass, '') as superclass,
string_agg(vsuperclass, '') as vsuperclass,
string_agg("class", '') as "class",
string_agg(vclass, '') as vclass,
string_agg("order", '') as "order",
string_agg(vorder, '') as vorder,
string_agg("family", '') as "family",
string_agg(vfamily, '') as vfamily,
string_agg(genus, '') as genus,
string_agg(vgenus, '') as vgenus,
string_agg(species, '') as species,
string_agg(vspecies, '') as vspecies
from
(select distinct
dav.species as species_tsn,
case when dav."level" = 0 then complete_name else '' end as kingdom,
case when dav."level" = 0 then vernacular_names::varchar else '' end as vkingdom,
case when dav."level" = 1 then complete_name else '' end as subkingdom,
case when dav."level" = 1 then vernacular_names::varchar else '' end as vsubkingdom,
case when dav."level" = 2 then complete_name else '' end as infrakingdom,
case when dav."level" = 2 then vernacular_names::varchar else '' end as vinfrakingdom,
case when dav."level" = 3 then complete_name else '' end as division,
case when dav."level" = 3 then vernacular_names::varchar else '' end as vdivision,
case when dav."level" = 4 then complete_name else '' end as subdivision,
case when dav."level" = 4 then vernacular_names::varchar else '' end as vsubdivision,
case when dav."level" = 5 then complete_name else '' end as infradivision,
case when dav."level" = 5 then vernacular_names::varchar else '' end as vinfradivision,
case when dav."level" = 6 then complete_name else '' end as superclass,
case when dav."level" = 6 then vernacular_names::varchar else '' end as vsuperclass,
case when dav."level" = 7 then complete_name else '' end as "class",
case when dav."level" = 7 then vernacular_names::varchar else '' end as vclass,
case when dav."level" = 8 then complete_name else '' end as "order",
case when dav."level" = 8 then vernacular_names::varchar else '' end as vorder,
case when dav."level" = 9 then complete_name else '' end as "family",
case when dav."level" = 9 then vernacular_names::varchar else '' end as vfamily,
case when dav."level" = 10 then complete_name else '' end as genus,
case when dav."level" = 10 then vernacular_names::varchar else '' end as vgenus,
case when dav."level" = 11 then complete_name else '' end as species,
case when dav."level" = 11 then vernacular_names::varchar else '' end as vspecies
from derived_aves dav ) as chess
group by 1
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment