Project

General

Profile

Home » Database development » VegBIEN » Derived columns »

TaxonMorphospecies creation

Sample plots

Brad's plots

For SALVIAS plot 309 subplot 1 (in Ecuador):

Original taxon Original family TNRS matched name TNRS matched name family TNRS unmatched terms TNRS taxonomic status TNRS accepted name TNRS accepted name family taxonMorphospecies
Alchornea sp.2 Euphorbiaceae Alchornea Euphorbiaceae sp.2 Accepted Alchornea Euphorbiaceae Euphorbiaceae Alchornea Sw. sp.2
Besleria solanoides Gesneriaceae Besleria solanoides Gesneriaceae Accepted Besleria solanoides Gesneriaceae Gesneriaceae Besleria solanoides Kunth
Billia columbiana Hippocastanaceae Billia columbiana Sapindaceae No opinion Billia columbiana Sapindaceae Sapindaceae Billia columbiana Planch. & Linden
Chionanthus sp.1 Oleaceae Chionanthus Oleaceae sp.1 Accepted Chionanthus Oleaceae Oleaceae Chionanthus L. sp.1
Clusia decuoides Clusiaceae Clusia decuoides Clusiaceae Clusiaceae Clusia decuoides Engl.
Cyathea sp.8 PTERIDOPHYTA Cyathea Cyatheaceae sp.8 Accepted Cyathea Cyatheaceae Cyatheaceae Cyathea Sm. sp.8
Hedyosmum bonplandianum Chloranthaceae Hedyosmum bonplandianum Chloranthaceae Accepted Hedyosmum bonplandianum Chloranthaceae Chloranthaceae Hedyosmum bonplandianum Kunth
Indet. sp.1 Asteraceae Asteraceae Asteraceae Indet. sp.1 Accepted Asteraceae Asteraceae Asteraceae Asteraceae Bercht. & J. Presl Indet. sp.1
Indet. sp.17 Melastomataceae Melastomataceae Melastomataceae Indet. sp.17 Accepted Melastomataceae Melastomataceae Melastomataceae Melastomataceae Juss. Indet. sp.17
Indet. sp.20 Melastomataceae Melastomataceae Melastomataceae Indet. sp.20 Accepted Melastomataceae Melastomataceae Melastomataceae Melastomataceae Juss. Indet. sp.20
Munnozia senecionidis Asteraceae Munnozia senecionidis Asteraceae Accepted Munnozia senecionidis Asteraceae Asteraceae Munnozia senecionidis Benth.
Ocotea sp.6 Lauraceae Ocotea Lauraceae sp.6 Accepted Ocotea Lauraceae Lauraceae Ocotea Aubl. sp.6
Schefflera sp.2 Araliaceae Schefflera Araliaceae sp.2 Accepted Schefflera Araliaceae Araliaceae Schefflera J.R. Forst. & G. Forst. sp.2
Sphaeradenia sp.3 Cyclanthaceae Sphaeradenia Cyclanthaceae sp.3 Accepted Sphaeradenia Cyclanthaceae Cyclanthaceae Sphaeradenia Harling sp.3
Sphaeradenia sp.4 Cyclanthaceae Sphaeradenia Cyclanthaceae sp.4 Accepted Sphaeradenia Cyclanthaceae Cyclanthaceae Sphaeradenia Harling sp.4

query:

SET search_path TO "public.2012-11-08-21-59-04.r6098";
SELECT DISTINCT
  NULLIF(array_to_string(ARRAY[datasource_taxonverbatim.genus, datasource_taxonverbatim.specific_epithet], ' '), '')
  AS "Original taxon" 
, datasource_taxonverbatim.family AS "Original family" 
, matched_taxonverbatim.taxonname AS "TNRS matched name" 
, matched_taxonverbatim.family AS "TNRS matched name family" 
, parsed_taxonverbatim.morphospecies AS "TNRS unmatched terms" 
, matched_taxonlabel.taxonstatus AS "TNRS taxonomic status" 
, accepted_taxonverbatim.taxonname AS "TNRS accepted name" 
, accepted_taxonverbatim.family AS "TNRS accepted name family" 
, NULLIF(array_to_string(ARRAY[accepted_taxonlabel.taxonomicname, parsed_taxonverbatim.morphospecies], ' '), '')
  AS "taxonMorphospecies" 
FROM location
JOIN locationevent USING (location_id)
JOIN taxonoccurrence USING (locationevent_id)
JOIN taxondetermination USING (taxonoccurrence_id)
JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)
JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)
LEFT JOIN taxonlabel parsed_taxonlabel ON parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id
LEFT JOIN taxonverbatim parsed_taxonverbatim ON parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id
LEFT JOIN taxonlabel matched_taxonlabel ON matched_taxonlabel.taxonlabel_id = parsed_taxonlabel.matched_label_id
LEFT JOIN taxonverbatim matched_taxonverbatim ON matched_taxonverbatim.taxonlabel_id = matched_taxonlabel.taxonlabel_id
LEFT JOIN taxonlabel accepted_taxonlabel ON accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id
LEFT JOIN taxonverbatim accepted_taxonverbatim ON accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id
WHERE
NOT taxondetermination.isoriginal
AND location.reference_id = (SELECT reference_id FROM reference WHERE shortname = 'SALVIAS')
AND location.sourceaccessioncode = '309; 1' -- in Ecuador

First plot

For SALVIAS plot 1 subplot 1 (in Ecuador):

Original taxon Original family TNRS matched name TNRS matched name family TNRS unmatched terms TNRS taxonomic status TNRS accepted name TNRS accepted name family taxonMorphospecies
Alchornea pearcei Euphorbiaceae Alchornea pearcei Euphorbiaceae Accepted Alchornea pearcei Euphorbiaceae Euphorbiaceae Alchornea pearcei Britton ex Rusby
Bonnetia M1 Theaceae Bonnetia Bonnetiaceae Theaceae M1 Accepted Bonnetia Bonnetiaceae Bonnetiaceae Bonnetia Mart. Theaceae M1
Clusia M1 Clusiaceae Clusia Clusiaceae M1 Accepted Clusia Clusiaceae Clusiaceae Clusia L. M1
Clusia M2 Clusiaceae Clusia Clusiaceae M2 Accepted Clusia Clusiaceae Clusiaceae Clusia L. M2
Clusia M3 Clusiaceae Clusia Clusiaceae M3 Accepted Clusia Clusiaceae Clusiaceae Clusia L. M3
Clusia M4 Clusiaceae Clusia Clusiaceae M4 Accepted Clusia Clusiaceae Clusiaceae Clusia L. M4
Cyathea M1 Pteridophyta Cyathea Cyatheaceae M1 Accepted Cyathea Cyatheaceae Cyatheaceae Cyathea Sm. M1
Drimys M1 Winteraceae Drimys Winteraceae M1 Accepted Drimys Winteraceae Winteraceae Drimys J.R. Forst. & G. Forst. M1
Epidendrum M1 Orchidaceae Epidendrum Orchidaceae M1 Accepted Epidendrum Orchidaceae Orchidaceae Epidendrum L. M1
Geonoma M1 Arecaceae Geonoma Arecaceae M1 Accepted Geonoma Arecaceae Arecaceae Geonoma Willd. M1
Hedyosmum M1 Chloranthaceae Hedyosmum Chloranthaceae M1 Accepted Hedyosmum Chloranthaceae Chloranthaceae Hedyosmum Sw. M1
Miconia M1 Melastomataceae Miconia Melastomataceae M1 Accepted Miconia Melastomataceae Melastomataceae Miconia Ruiz & Pav. M1
Miconia M2 Melastomataceae Miconia Melastomataceae M2 Accepted Miconia Melastomataceae Melastomataceae Miconia Ruiz & Pav. M2
Myrica M1 Myricaceae Myrica Myricaceae M1 Synonym Morella Myricaceae Myricaceae Morella Lour. M1
Podocarpus M1 Podocarpaceae Podocarpus Podocarpaceae M1 Accepted Podocarpus Podocarpaceae Podocarpaceae Podocarpus L'Hér. ex Pers. M1
Prunus M1 Rosaceae Prunus Rosaceae M1 Accepted Prunus Rosaceae Rosaceae Prunus L. M1
Psychotria M1 Rubiaceae Psychotria Rubiaceae M1 Accepted Psychotria Rubiaceae Rubiaceae Psychotria L. M1
Schefflera M1 Araliaceae Schefflera Araliaceae M1 Accepted Schefflera Araliaceae Araliaceae Schefflera J.R. Forst. & G. Forst. M1
Schefflera M2 Araliaceae Schefflera Araliaceae M2 Accepted Schefflera Araliaceae Araliaceae Schefflera J.R. Forst. & G. Forst. M2
Vismia M1 Clusiaceae Vismia Hypericaceae M1 Accepted Vismia Hypericaceae Hypericaceae Vismia Vand. M1
Weinmannia M1 Cunoniaceae Weinmannia Cunoniaceae M1 Accepted Weinmannia Cunoniaceae Cunoniaceae Weinmannia L. M1

query:

SET search_path TO "public.2012-11-08-21-59-04.r6098";
SELECT DISTINCT
  NULLIF(array_to_string(ARRAY[datasource_taxonverbatim.genus, datasource_taxonverbatim.specific_epithet], ' '), '')
  AS "Original taxon" 
, datasource_taxonverbatim.family AS "Original family" 
, matched_taxonverbatim.taxonname AS "TNRS matched name" 
, matched_taxonverbatim.family AS "TNRS matched name family" 
, parsed_taxonverbatim.morphospecies AS "TNRS unmatched terms" 
, matched_taxonlabel.taxonstatus AS "TNRS taxonomic status" 
, accepted_taxonverbatim.taxonname AS "TNRS accepted name" 
, accepted_taxonverbatim.family AS "TNRS accepted name family" 
, NULLIF(array_to_string(ARRAY[accepted_taxonlabel.taxonomicname, parsed_taxonverbatim.morphospecies], ' '), '')
  AS "taxonMorphospecies" 
FROM location
JOIN locationevent USING (location_id)
JOIN taxonoccurrence USING (locationevent_id)
JOIN taxondetermination USING (taxonoccurrence_id)
JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)
JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)
LEFT JOIN taxonlabel parsed_taxonlabel ON parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id
LEFT JOIN taxonverbatim parsed_taxonverbatim ON parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id
LEFT JOIN taxonlabel matched_taxonlabel ON matched_taxonlabel.taxonlabel_id = parsed_taxonlabel.matched_label_id
LEFT JOIN taxonverbatim matched_taxonverbatim ON matched_taxonverbatim.taxonlabel_id = matched_taxonlabel.taxonlabel_id
LEFT JOIN taxonlabel accepted_taxonlabel ON accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id
LEFT JOIN taxonverbatim accepted_taxonverbatim ON accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id
WHERE
NOT taxondetermination.isoriginal
AND location.reference_id = (SELECT reference_id FROM reference WHERE shortname = 'SALVIAS')
AND location.sourceaccessioncode = '1; 1' -- in Ecuador