This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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", |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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] |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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", |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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) | |
) |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- 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 |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |