Revision 13503
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/schema.sql | ||
|---|---|---|
| 80 | 80 |
-- Name: map_taxonomic_status(text, text); Type: FUNCTION; Schema: TNRS; Owner: - |
| 81 | 81 |
-- |
| 82 | 82 |
|
| 83 |
CREATE FUNCTION map_taxonomic_status(taxonomic_status_verbatim text, scrubbed_unique_taxon_name text) RETURNS text
|
|
| 83 |
CREATE FUNCTION map_taxonomic_status(taxonomic_status_verbatim text, accepted_taxon_name_no_author text) RETURNS text
|
|
| 84 | 84 |
LANGUAGE sql IMMUTABLE |
| 85 | 85 |
AS $_$ |
| 86 |
/* from http://wiki.vegpath.org/2013-11-14_conference_call#taxonomic-fields: |
|
| 87 |
"taxonomic_status should be accepted instead of synonym when an accepted name is |
|
| 88 |
available (this is not always the case when a name is marked as a synonym)" */ |
|
| 86 | 89 |
SELECT CASE WHEN $2 IS NOT NULL THEN 'accepted' ELSE lower($1) END |
| 87 | 90 |
$_$; |
| 88 | 91 |
|
| trunk/schemas/public_.sql | ||
|---|---|---|
| 6728 | 6728 |
taxon_scrub."matchedTaxonName" AS "taxonName_matched", |
| 6729 | 6729 |
taxon_scrub."matchedScientificNameAuthorship" AS "scientificNameAuthorship_matched", |
| 6730 | 6730 |
family_higher_plant_group.higher_plant_group AS "higherPlantGroup_bien", |
| 6731 |
"TNRS".map_taxonomic_status(taxon_scrub."taxonomicStatus", taxon_scrub.scrubbed_unique_taxon_name) AS taxonomic_status,
|
|
| 6731 |
"TNRS".map_taxonomic_status(taxon_scrub."taxonomicStatus", taxon_scrub.accepted_taxon_name_no_author) AS taxonomic_status,
|
|
| 6732 | 6732 |
taxon_scrub.scrubbed_family, |
| 6733 | 6733 |
taxon_scrub.scrubbed_genus, |
| 6734 | 6734 |
NULLIF(concat_ws(' '::text, COALESCE(taxon_scrub.scrubbed_genus, taxon_scrub.scrubbed_family), COALESCE(taxon_scrub.scrubbed_specific_epithet, taxon_scrub."morphospeciesSuffix")), ''::text) AS scrubbed_morphospecies_binomial,
|
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 6728 | 6728 |
taxon_scrub."matchedTaxonName" AS "taxonName_matched", |
| 6729 | 6729 |
taxon_scrub."matchedScientificNameAuthorship" AS "scientificNameAuthorship_matched", |
| 6730 | 6730 |
family_higher_plant_group.higher_plant_group AS "higherPlantGroup_bien", |
| 6731 |
"TNRS".map_taxonomic_status(taxon_scrub."taxonomicStatus", taxon_scrub.scrubbed_unique_taxon_name) AS taxonomic_status,
|
|
| 6731 |
"TNRS".map_taxonomic_status(taxon_scrub."taxonomicStatus", taxon_scrub.accepted_taxon_name_no_author) AS taxonomic_status,
|
|
| 6732 | 6732 |
taxon_scrub.scrubbed_family, |
| 6733 | 6733 |
taxon_scrub.scrubbed_genus, |
| 6734 | 6734 |
NULLIF(concat_ws(' '::text, COALESCE(taxon_scrub.scrubbed_genus, taxon_scrub.scrubbed_family), COALESCE(taxon_scrub.scrubbed_specific_epithet, taxon_scrub."morphospeciesSuffix")), ''::text) AS scrubbed_morphospecies_binomial,
|
| ... | ... | |
| 16843 | 16843 |
-- Name: map_taxonomic_status(text, text); Type: FUNCTION; Schema: TNRS; Owner: - |
| 16844 | 16844 |
-- |
| 16845 | 16845 |
|
| 16846 |
CREATE FUNCTION map_taxonomic_status(taxonomic_status_verbatim text, scrubbed_unique_taxon_name text) RETURNS text
|
|
| 16846 |
CREATE FUNCTION map_taxonomic_status(taxonomic_status_verbatim text, accepted_taxon_name_no_author text) RETURNS text
|
|
| 16847 | 16847 |
LANGUAGE sql IMMUTABLE |
| 16848 | 16848 |
AS $_$ |
| 16849 |
/* from http://wiki.vegpath.org/2013-11-14_conference_call#taxonomic-fields: |
|
| 16850 |
"taxonomic_status should be accepted instead of synonym when an accepted name is |
|
| 16851 |
available (this is not always the case when a name is marked as a synonym)" */ |
|
| 16849 | 16852 |
SELECT CASE WHEN $2 IS NOT NULL THEN 'accepted' ELSE lower($1) END |
| 16850 | 16853 |
$_$; |
| 16851 | 16854 |
|
Also available in: Unified diff
bugfix: inputs/.TNRS/schema.sql: map_taxonomic_status(): need to use accepted name instead of scrubbed name (which also includes no-opinion names), as described at http://wiki.vegpath.org/2013-11-14_conference_call#taxonomic-fields. this used to be the accepted name, but got switched when the concatenated name was also used to store the matched name for no-opinion names.