mdoering

// 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

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]

mdoering

mdoering

   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 |    

mdoering

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

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

// 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

-- 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

-- 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

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