Skip to content

Instantly share code, notes, and snippets.

// 20181022130930
// https://raw.githubusercontent.com/CatalogueOfLife/datapackage-specs/master/datapackage.json
{
"name": "coldp-specs-example",
"title": "CoL Data Package example",
"abstract": "Data Package example taken from the CoL Data Package specification",
"contributors": Array[1][
{
"name": "Markus Döring",
@mdoering
mdoering / tree1.txt
Last active February 28, 2019 12:34
textTree diff examples
Plantae [kingdom]
*Viridae [kingdom]
Asteraceae [family]
*Compositae [family]
Cichorioideae [subfamily]
Cichorieae [tribe]
Crepis L. [genus]
Crepis bakeri Greene [species]
Crepis bakeri subsp. bakeri [subspecies]
Crepis bakeri subsp. cusickii (Eastw.) Babc. & Stebbins [subspecies]
aber --
about 1 Gephyroneura cosmia Turner, 1921 AU. See [About maps]
after 3 Dasypogon after Wiedemann, 1828
!!! IN NAME: Dasypogon after Wiedemann, 1828
again 1 Aegilops ovata L., nom. rej. to be proposed again.
all 5397 AlHV-2
!!! IN NAME: all
allow --
already --
also 7 also many names at infraspecific levels
@mdoering
mdoering / nub.txt
Last active August 13, 2019 09:46
Nub homonym families
homonym | rank | id | status | source | size | accepted | family | order | class | phylum | kingdom | canonical_name | accepted_key | family_key | order
----------------------+--------+----------+---------------------+-----------+-----------------+----------------------+----------------------+-----------------+-----------------+-----------------+-----------+----------------------+--------------+------------+------
Admetidae | FAMILY | 10672254 | PROPARTE_SYNONYM | 106411888 | 0 | Cancellariidae | Cancellariidae | Neogastropoda | Gastropoda | Mollusca | Animalia | Admetidae | 2303085 | 2303085 |
Admetidae | FAMILY | 9639753 | PROPARTE_SYNONYM | 106411888 | 0 | Phrynidae | Phrynidae | Amblypygi | Arachnida | Arthropoda | Animalia | Admetidae | 7399 | 7399 |
This file has been truncated, but you can view the full file.
269a270
> Aaroniella badonneli Aaroniella andrei
1218d1218
< ? abacta Toechorychus abactus
1226a1227
> ? abactus Toechorychus abactus
1433,1435c1434,1436
< Abanchogastra debilis Spolas hawaiiensis
< Abanchogastra ferruginea Spolas hawaiiensis
< Abanchogastra hawaiiensis Spolas hawaiiensis
@mdoering
mdoering / homonym-comparison.txt
Last active August 27, 2019 12:50
CoL accepted homonyms
SELECT n1.rank, n1.scientific_name, u1.status,
f1n.scientific_name as family, o1n.scientific_name as order, c1n.scientific_name as class, p1n.scientific_name as phylum, k1n.scientific_name as kingdom
FROM name_usage u1 join name n1 on n1.id=u1.name_fk join name n2 on n1.scientific_name=n2.scientific_name and n1.rank=n2.rank join name_usage u2 on u2.name_fk=n2.id
LEFT JOIN name_usage f1 ON u1.family_fk=f1.id LEFT JOIN name f1n ON f1n.id=f1.name_fk
LEFT JOIN name_usage o1 ON u1.order_fk =o1.id LEFT JOIN name o1n ON o1n.id=o1.name_fk
LEFT JOIN name_usage c1 ON u1.class_fk =c1.id LEFT JOIN name c1n ON c1n.id=c1.name_fk
LEFT JOIN name_usage p1 ON u1.phylum_fk=p1.id LEFT JOIN name p1n ON p1n.id=p1.name_fk
LEFT JOIN name_usage k1 ON u1.kingdom_fk=k1.id LEFT JOIN name k1n ON k1n.id=k1.name_fk
WHERE u1.dataset_key=colKey() and u2.dataset_key=colKey() and n1.rank <='GENUS'::rank and not u1.is_synonym and not u2.is_synonym and u1.id!=u2.id and u1.kingdom_fk=u2.kingdom_fk
ORDER by n1.rank, n1
@mdoering
mdoering / parents.json
Created September 3, 2019 10:29
col tree api parents with placeholders
// 20190903122905
// http://localhost:8080/dataset/3/tree/f55812e8-5422-402e-b071-b67a9cdf481f--incertae-sedis--FAMILY
[
{
"datasetKey": 3,
"id": "f55812e8-5422-402e-b071-b67a9cdf481f",
"parentId": "0",
"name": "<i>Viruses</i>",
"rank": "kingdom",
@mdoering
mdoering / doubtful-genus-homonyms.txt
Last active September 18, 2019 12:07
nub genus homonyms
-- GENUS HOMONYMS WITH THE SAME CLASSIFICATION, ALL DOUBTFUL AND AT LEAST ONE MISSING AUTHORSHIP
-- BUT RETURNING ALL USAGES WITHIN THAT KINGDOM WITH THAT NAME REGARDLESS THEIR CLASSIFICATION
WITH homs AS (
SELECT u.rank, n.canonical_name, u.kingdom_fk, u.phylum_fk, u.class_fk, u.order_fk, u.family_fk
FROM name_usage u JOIN name n ON u.name_fk = n.id
WHERE u.dataset_key = nubkey() AND u.deleted IS NULL AND NOT u.is_synonym AND u.rank='GENUS'::rank
GROUP BY u.rank, n.canonical_name, u.kingdom_fk, u.phylum_fk, u.class_fk, u.order_fk, u.family_fk
HAVING count(*) > 1 AND count(distinct status)=1 AND bool_or(n.canonical_name=n.scientific_name)
)
@mdoering
mdoering / GENUS_HOMONYMS.sql
Created September 18, 2019 10:55
Backbon Homonym SQL snippets
-- ALL GENUS HOMONYMS
WITH homs AS (
SELECT u.rank, n.canonical_name
FROM name_usage u JOIN name n ON u.name_fk = n.id
WHERE u.dataset_key = nubkey() AND u.deleted IS NULL AND NOT u.is_synonym AND u.rank='GENUS'::rank
GROUP BY u.rank, n.canonical_name
HAVING count(*) > 1
)
SELECT u.id, n.canonical_name, u.rank, n.scientific_name, u.is_synonym, u.status,
u.kingdom_fk, u.phylum_fk, u.class_fk, u.order_fk, u.family_fk, u.genus_fk
@mdoering
mdoering / int-sizes.txt
Created November 15, 2019 12:02
Disk use of int, smallint and enum (10 values) in Postgres11 on OSX and Linux
MAC
table_name | row_estimate | total_bytes | index_bytes | toast_bytes | table_bytes | total | index | toast | table
-------------+--------------+-------------+-------------+-------------+-------------+--------+---------+------------+--------
test-int4 | 5e+06 | 323534848 | 142221312 | 8192 | 181305344 | 309 MB | 136 MB | 8192 bytes | 173 MB
test-int2 | 5e+06 | 323993600 | 142680064 | 8192 | 181305344 | 309 MB | 136 MB | 8192 bytes | 173 MB
test-enum | 5e+06 | 293650432 | 112336896 | 8192 | 181305344 | 280 MB | 107 MB | 8192 bytes | 173 MB
test-int4[] | 4.99999e+06 | 306806784 | 5505024 | 8192 | 301293568 | 293 MB | 5376 kB | 8192 bytes | 287 MB
test-int2[] | 4.99999e+06 | 266493952 | 5505024 | 8192 | 260980736 | 254 MB | 5376 kB | 8192 bytes | 249 MB
test-enum[] | 4.99998e+06 | 346955776 | 5505024 | 8192 | 341442560 | 331 MB | 5376 kB | 8192 bytes | 326 MB