Revision 5596
Added by Aaron Marcuse-Kubitza over 11 years ago
vegbien.sql | ||
---|---|---|
502 | 502 |
matched_concept_id integer, |
503 | 503 |
matched_concept_fit_fraction double precision, |
504 | 504 |
parent_id integer, |
505 |
taxonname text,
|
|
505 |
taxonepithet text,
|
|
506 | 506 |
rank taxonrank, |
507 | 507 |
verbatimrank text, |
508 | 508 |
identifyingtaxonomicname text, |
... | ... | |
515 | 515 |
description text, |
516 | 516 |
accessioncode text, |
517 | 517 |
CONSTRAINT taxonconcept_matched_concept_fit_fraction_range CHECK (((matched_concept_fit_fraction >= (0)::double precision) AND (matched_concept_fit_fraction <= (1)::double precision))), |
518 |
CONSTRAINT taxonconcept_required_key CHECK (((((((sourceaccessioncode IS NOT NULL) OR (taxonname IS NOT NULL)) OR (identifyingtaxonomicname IS NOT NULL)) OR (taxonomicname IS NOT NULL)) OR (taxonomicnamewithauthor IS NOT NULL)) OR ((parent_id IS NOT NULL) AND ((creationdate IS NOT NULL) OR (author IS NOT NULL)))))
|
|
518 |
CONSTRAINT taxonconcept_required_key CHECK (((((((sourceaccessioncode IS NOT NULL) OR (taxonepithet IS NOT NULL)) OR (identifyingtaxonomicname IS NOT NULL)) OR (taxonomicname IS NOT NULL)) OR (taxonomicnamewithauthor IS NOT NULL)) OR ((parent_id IS NOT NULL) AND ((creationdate IS NOT NULL) OR (author IS NOT NULL)))))
|
|
519 | 519 |
); |
520 | 520 |
|
521 | 521 |
|
... | ... | |
527 | 527 |
|
528 | 528 |
"A taxon (plural: taxa) is a group of one (or more) populations of organism(s), which a taxonomist adjudges to be a unit" (http://en.wikipedia.org/wiki/Taxon) |
529 | 529 |
|
530 |
Note that taxonname stores only one rank (e.g. family) of the full taxonomic name. The higher-level ranks are stored in the taxon concept''s chain of parent_id ancestors.
|
|
530 |
Note that taxonepithet stores only one rank (e.g. family) of the full taxonomic name. The higher-level ranks are stored in the taxon concept''s chain of parent_id ancestors.
|
|
531 | 531 |
|
532 | 532 |
To include a taxon name at a rank with no explicit column, create a parent taxonconcept for it and point to it using parent_id. To include multiple such names, chain the taxonconcepts together using parent_id. Note that lower-level taxa should point to higher-level taxa. |
533 | 533 |
|
... | ... | |
590 | 590 |
|
591 | 591 |
|
592 | 592 |
-- |
593 |
-- Name: COLUMN taxonconcept.taxonname; Type: COMMENT; Schema: public; Owner: -
|
|
593 |
-- Name: COLUMN taxonconcept.taxonepithet; Type: COMMENT; Schema: public; Owner: -
|
|
594 | 594 |
-- |
595 | 595 |
|
596 |
COMMENT ON COLUMN taxonconcept.taxonname IS 'The name of the taxon within its parent taxon. This is the lowest-rank portion of this taxonconcept''s full taxonomic name, if it has one.
|
|
596 |
COMMENT ON COLUMN taxonconcept.taxonepithet IS 'The epithet of the taxon within its parent taxon. This is the lowest-rank portion of this taxonconcept''s full taxonomic name, if it has one.
|
|
597 | 597 |
|
598 | 598 |
The morphospecies suffix goes in this field.'; |
599 | 599 |
|
... | ... | |
1503 | 1503 |
-- |
1504 | 1504 |
|
1505 | 1505 |
CREATE VIEW analytical_db_view AS |
1506 |
SELECT datasource.organizationname AS "dataSourceName", accepted_taxonconcept.family, accepted_taxonconcept.genus, accepted_taxonconcept.species, COALESCE(accepted_taxonconcept.taxonomicnamewithauthor, accepted_taxonconcept.taxonomicname) AS taxon, accepted_taxonconcept.author AS "taxonAuthor", accepted_taxonconcept.taxonname AS "taxonMorphospecies", placepath.country, placepath.stateprovince AS "stateProvince", placepath.county AS "countyParish", taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS "collectionNumber", array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text) AS "identifiedBy", aggregateoccurrence.collectiondate AS "observationDate", location.authorlocationcode AS "plotCode", functions._m2_to_ha(location.area_m2) AS "plotAreaHa", method.name AS "plotMethod", locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS "isCultivated", taxonoccurrence.cultivatedbasis AS "isCultivatedReason", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "pctCover" FROM ((((((((((((((location JOIN party datasource ON (((datasource.party_id = location.creator_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonconcept datasource_taxonconcept USING (taxonconcept_id)) JOIN taxonconcept accepted_taxonconcept ON ((accepted_taxonconcept.taxonconcept_id = datasource_taxonconcept.accepted_concept_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));
|
|
1506 |
SELECT datasource.organizationname AS "dataSourceName", accepted_taxonconcept.family, accepted_taxonconcept.genus, accepted_taxonconcept.species, COALESCE(accepted_taxonconcept.taxonomicnamewithauthor, accepted_taxonconcept.taxonomicname) AS taxon, accepted_taxonconcept.author AS "taxonAuthor", accepted_taxonconcept.taxonepithet AS "taxonMorphospecies", placepath.country, placepath.stateprovince AS "stateProvince", placepath.county AS "countyParish", taxonoccurrence.verbatimcollectorname AS collector, plantobservation.collectionnumber AS "collectionNumber", array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text) AS "identifiedBy", aggregateoccurrence.collectiondate AS "observationDate", location.authorlocationcode AS "plotCode", functions._m2_to_ha(location.area_m2) AS "plotAreaHa", method.name AS "plotMethod", locationcoords.latitude_deg AS latitude, locationcoords.longitude_deg AS longitude, location.elevation_m, taxonoccurrence.iscultivated AS "isCultivated", taxonoccurrence.cultivatedbasis AS "isCultivatedReason", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "pctCover" FROM ((((((((((((((location JOIN party datasource ON (((datasource.party_id = location.creator_id) AND (datasource.organizationname IS NOT NULL)))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonconcept datasource_taxonconcept USING (taxonconcept_id)) JOIN taxonconcept accepted_taxonconcept ON ((accepted_taxonconcept.taxonconcept_id = datasource_taxonconcept.accepted_concept_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN specimenreplicate USING (plantobservation_id));
|
|
1507 | 1507 |
|
1508 | 1508 |
|
1509 | 1509 |
-- |
... | ... | |
4758 | 4758 |
-- Name: taxonconcept_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
4759 | 4759 |
-- |
4760 | 4760 |
|
4761 |
CREATE UNIQUE INDEX taxonconcept_unique ON taxonconcept USING btree ((COALESCE(parent_id, 2147483647)), (COALESCE(taxonname, '\\N'::text)), (COALESCE(rank, 'unknown'::taxonrank)), creator_id, (COALESCE(sourceaccessioncode, '\\N'::text)), (COALESCE(creationdate, 'infinity'::date)), (COALESCE(identifyingtaxonomicname, '\\N'::text)), (COALESCE(taxonomicname, '\\N'::text)), (COALESCE(author, '\\N'::text)), (COALESCE(taxonomicnamewithauthor, '\\N'::text)));
|
|
4761 |
CREATE UNIQUE INDEX taxonconcept_unique ON taxonconcept USING btree ((COALESCE(parent_id, 2147483647)), (COALESCE(taxonepithet, '\\N'::text)), (COALESCE(rank, 'unknown'::taxonrank)), creator_id, (COALESCE(sourceaccessioncode, '\\N'::text)), (COALESCE(creationdate, 'infinity'::date)), (COALESCE(identifyingtaxonomicname, '\\N'::text)), (COALESCE(taxonomicname, '\\N'::text)), (COALESCE(author, '\\N'::text)), (COALESCE(taxonomicnamewithauthor, '\\N'::text)));
|
|
4762 | 4762 |
|
4763 | 4763 |
|
4764 | 4764 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: taxonconcept: Renamed taxonname to taxonepithet for clarity and to be consistent with TCS's use of "epithet" to denote what the taxonname was intended to be (http://www.tdwg.org/standards/117/download/#/UserGuidev_1.3.pdf)