Revision 14108
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/schema.sql | ||
|---|---|---|
| 394 | 394 |
-- |
| 395 | 395 |
|
| 396 | 396 |
CREATE VIEW "MatchedTaxon" AS |
| 397 |
SELECT s."*batch",
|
|
| 397 |
SELECT s.batch,
|
|
| 398 | 398 |
s.match_num, |
| 399 | 399 |
s."Name_number", |
| 400 |
s."concatenatedScientificName",
|
|
| 400 |
s."Name_submitted",
|
|
| 401 | 401 |
s."Overall_score", |
| 402 |
s."matchedTaxonName",
|
|
| 403 |
s."matchedTaxonRank",
|
|
| 404 |
s."*Name_score",
|
|
| 405 |
s."matchedScientificNameAuthorship",
|
|
| 406 |
s."matchedScientificNameID",
|
|
| 402 |
s."Name_matched",
|
|
| 403 |
s."Name_matched_rank",
|
|
| 404 |
s."Name_score", |
|
| 405 |
s."Name_matched_author",
|
|
| 406 |
s."Name_matched_url",
|
|
| 407 | 407 |
s."Author_matched", |
| 408 |
s."*Author_score",
|
|
| 409 |
s.parsed_family,
|
|
| 410 |
s."matchedFamilyConfidence_fraction",
|
|
| 411 |
s."matchedFamily",
|
|
| 412 |
s."matchedGenus",
|
|
| 413 |
s."matchedGenusConfidence_fraction",
|
|
| 414 |
s."matchedSpecificEpithet",
|
|
| 415 |
s."matchedSpeciesConfidence_fraction",
|
|
| 408 |
s."Author_score", |
|
| 409 |
s."Family_matched",
|
|
| 410 |
s."Family_score",
|
|
| 411 |
s."Name_matched_accepted_family",
|
|
| 412 |
s."Genus_matched",
|
|
| 413 |
s."Genus_score",
|
|
| 414 |
s."Specific_epithet_matched",
|
|
| 415 |
s."Specific_epithet_score",
|
|
| 416 | 416 |
s."Infraspecific_rank", |
| 417 |
s."matchedInfraspecificEpithet",
|
|
| 418 |
s."*Infraspecific_epithet_score",
|
|
| 417 |
s."Infraspecific_epithet_matched",
|
|
| 418 |
s."Infraspecific_epithet_score", |
|
| 419 | 419 |
s."Infraspecific_rank_2", |
| 420 | 420 |
s."Infraspecific_epithet_2_matched", |
| 421 | 421 |
s."Infraspecific_epithet_2_score", |
| 422 |
s."identificationQualifier",
|
|
| 423 |
s."morphospeciesSuffix",
|
|
| 424 |
s."*taxonomicStatus",
|
|
| 425 |
s.accepted_taxon_name_no_author,
|
|
| 426 |
s.accepted_author,
|
|
| 427 |
s.accepted_taxon_rank,
|
|
| 428 |
s."acceptedScientificNameID",
|
|
| 429 |
s.accepted_species_binomial,
|
|
| 430 |
s.accepted_family,
|
|
| 431 |
s."*Selected",
|
|
| 432 |
s."*Source",
|
|
| 433 |
s."*Warnings",
|
|
| 434 |
s."*Accepted_name_lsid",
|
|
| 435 |
s.taxon_scrub__is_valid_match,
|
|
| 422 |
s."Annotations",
|
|
| 423 |
s."Unmatched_terms",
|
|
| 424 |
s."Taxonomic_status",
|
|
| 425 |
s."Accepted_name",
|
|
| 426 |
s."Accepted_name_author",
|
|
| 427 |
s."Accepted_name_rank",
|
|
| 428 |
s."Accepted_name_url",
|
|
| 429 |
s."Accepted_name_species",
|
|
| 430 |
s."Accepted_name_family",
|
|
| 431 |
s."Selected", |
|
| 432 |
s."Source", |
|
| 433 |
s."Warnings", |
|
| 434 |
s."Accepted_name_lsid", |
|
| 435 |
s.is_valid_match, |
|
| 436 | 436 |
s.scrubbed_unique_taxon_name, |
| 437 |
map_taxonomic_status(s."*taxonomicStatus", s.accepted_taxon_name_no_author) AS "taxonomicStatus",
|
|
| 437 |
map_taxonomic_status(s."Taxonomic_status", s."Accepted_name") AS "taxonomicStatus",
|
|
| 438 | 438 |
CASE |
| 439 |
WHEN (s.accepted_taxon_rank = 'family'::text) THEN concat_ws(' '::text, s.accepted_family, s."morphospeciesSuffix")
|
|
| 440 |
WHEN (s.accepted_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, s.accepted_taxon_name_no_author, s."morphospeciesSuffix")
|
|
| 441 |
ELSE s.accepted_species_binomial
|
|
| 439 |
WHEN (s."Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, s."Accepted_name_family", s."Unmatched_terms")
|
|
| 440 |
WHEN (s."Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, s."Accepted_name", s."Unmatched_terms")
|
|
| 441 |
ELSE s."Accepted_name_species"
|
|
| 442 | 442 |
END AS accepted_morphospecies_binomial |
| 443 |
FROM ( SELECT taxon_best_match.batch AS "*batch",
|
|
| 443 |
FROM ( SELECT taxon_best_match.batch, |
|
| 444 | 444 |
taxon_best_match.match_num, |
| 445 | 445 |
taxon_best_match."Name_number", |
| 446 |
taxon_best_match."Name_submitted" AS "concatenatedScientificName",
|
|
| 446 |
taxon_best_match."Name_submitted", |
|
| 447 | 447 |
taxon_best_match."Overall_score", |
| 448 |
taxon_best_match."Name_matched" AS "matchedTaxonName",
|
|
| 449 |
taxon_best_match."Name_matched_rank" AS "matchedTaxonRank",
|
|
| 450 |
taxon_best_match."Name_score" AS "*Name_score",
|
|
| 451 |
taxon_best_match."Name_matched_author" AS "matchedScientificNameAuthorship",
|
|
| 452 |
taxon_best_match."Name_matched_url" AS "matchedScientificNameID",
|
|
| 448 |
taxon_best_match."Name_matched", |
|
| 449 |
taxon_best_match."Name_matched_rank", |
|
| 450 |
taxon_best_match."Name_score", |
|
| 451 |
taxon_best_match."Name_matched_author", |
|
| 452 |
taxon_best_match."Name_matched_url", |
|
| 453 | 453 |
taxon_best_match."Author_matched", |
| 454 |
taxon_best_match."Author_score" AS "*Author_score",
|
|
| 455 |
taxon_best_match."Family_matched" AS parsed_family,
|
|
| 456 |
taxon_best_match."Family_score" AS "matchedFamilyConfidence_fraction",
|
|
| 457 |
taxon_best_match."Name_matched_accepted_family" AS "matchedFamily",
|
|
| 458 |
taxon_best_match."Genus_matched" AS "matchedGenus",
|
|
| 459 |
taxon_best_match."Genus_score" AS "matchedGenusConfidence_fraction",
|
|
| 460 |
taxon_best_match."Specific_epithet_matched" AS "matchedSpecificEpithet",
|
|
| 461 |
taxon_best_match."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction",
|
|
| 454 |
taxon_best_match."Author_score", |
|
| 455 |
taxon_best_match."Family_matched", |
|
| 456 |
taxon_best_match."Family_score", |
|
| 457 |
taxon_best_match."Name_matched_accepted_family", |
|
| 458 |
taxon_best_match."Genus_matched", |
|
| 459 |
taxon_best_match."Genus_score", |
|
| 460 |
taxon_best_match."Specific_epithet_matched", |
|
| 461 |
taxon_best_match."Specific_epithet_score", |
|
| 462 | 462 |
taxon_best_match."Infraspecific_rank", |
| 463 |
taxon_best_match."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet",
|
|
| 464 |
taxon_best_match."Infraspecific_epithet_score" AS "*Infraspecific_epithet_score",
|
|
| 463 |
taxon_best_match."Infraspecific_epithet_matched", |
|
| 464 |
taxon_best_match."Infraspecific_epithet_score", |
|
| 465 | 465 |
taxon_best_match."Infraspecific_rank_2", |
| 466 | 466 |
taxon_best_match."Infraspecific_epithet_2_matched", |
| 467 | 467 |
taxon_best_match."Infraspecific_epithet_2_score", |
| 468 |
taxon_best_match."Annotations" AS "identificationQualifier",
|
|
| 469 |
taxon_best_match."Unmatched_terms" AS "morphospeciesSuffix",
|
|
| 470 |
taxon_best_match."Taxonomic_status" AS "*taxonomicStatus",
|
|
| 471 |
taxon_best_match."Accepted_name" AS accepted_taxon_name_no_author,
|
|
| 472 |
taxon_best_match."Accepted_name_author" AS accepted_author,
|
|
| 473 |
taxon_best_match."Accepted_name_rank" AS accepted_taxon_rank,
|
|
| 474 |
taxon_best_match."Accepted_name_url" AS "acceptedScientificNameID",
|
|
| 475 |
taxon_best_match."Accepted_name_species" AS accepted_species_binomial,
|
|
| 476 |
taxon_best_match."Accepted_name_family" AS accepted_family,
|
|
| 477 |
taxon_best_match."Selected" AS "*Selected",
|
|
| 478 |
taxon_best_match."Source" AS "*Source",
|
|
| 479 |
taxon_best_match."Warnings" AS "*Warnings",
|
|
| 480 |
taxon_best_match."Accepted_name_lsid" AS "*Accepted_name_lsid",
|
|
| 481 |
taxon_best_match.is_valid_match AS taxon_scrub__is_valid_match,
|
|
| 468 |
taxon_best_match."Annotations", |
|
| 469 |
taxon_best_match."Unmatched_terms", |
|
| 470 |
taxon_best_match."Taxonomic_status", |
|
| 471 |
taxon_best_match."Accepted_name", |
|
| 472 |
taxon_best_match."Accepted_name_author", |
|
| 473 |
taxon_best_match."Accepted_name_rank", |
|
| 474 |
taxon_best_match."Accepted_name_url", |
|
| 475 |
taxon_best_match."Accepted_name_species", |
|
| 476 |
taxon_best_match."Accepted_name_family", |
|
| 477 |
taxon_best_match."Selected", |
|
| 478 |
taxon_best_match."Source", |
|
| 479 |
taxon_best_match."Warnings", |
|
| 480 |
taxon_best_match."Accepted_name_lsid", |
|
| 481 |
taxon_best_match.is_valid_match, |
|
| 482 | 482 |
taxon_best_match.scrubbed_unique_taxon_name |
| 483 | 483 |
FROM taxon_best_match) s; |
| 484 | 484 |
|
| ... | ... | |
| 500 | 500 |
-- |
| 501 | 501 |
|
| 502 | 502 |
CREATE VIEW "ValidMatchedTaxon" AS |
| 503 |
SELECT "MatchedTaxon"."*batch",
|
|
| 503 |
SELECT "MatchedTaxon".batch,
|
|
| 504 | 504 |
"MatchedTaxon".match_num, |
| 505 | 505 |
"MatchedTaxon"."Name_number", |
| 506 |
"MatchedTaxon"."concatenatedScientificName",
|
|
| 506 |
"MatchedTaxon"."Name_submitted",
|
|
| 507 | 507 |
"MatchedTaxon"."Overall_score", |
| 508 |
"MatchedTaxon"."matchedTaxonName",
|
|
| 509 |
"MatchedTaxon"."matchedTaxonRank",
|
|
| 510 |
"MatchedTaxon"."*Name_score",
|
|
| 511 |
"MatchedTaxon"."matchedScientificNameAuthorship",
|
|
| 512 |
"MatchedTaxon"."matchedScientificNameID",
|
|
| 508 |
"MatchedTaxon"."Name_matched",
|
|
| 509 |
"MatchedTaxon"."Name_matched_rank",
|
|
| 510 |
"MatchedTaxon"."Name_score", |
|
| 511 |
"MatchedTaxon"."Name_matched_author",
|
|
| 512 |
"MatchedTaxon"."Name_matched_url",
|
|
| 513 | 513 |
"MatchedTaxon"."Author_matched", |
| 514 |
"MatchedTaxon"."*Author_score",
|
|
| 515 |
"MatchedTaxon".parsed_family,
|
|
| 516 |
"MatchedTaxon"."matchedFamilyConfidence_fraction",
|
|
| 517 |
"MatchedTaxon"."matchedFamily",
|
|
| 518 |
"MatchedTaxon"."matchedGenus",
|
|
| 519 |
"MatchedTaxon"."matchedGenusConfidence_fraction",
|
|
| 520 |
"MatchedTaxon"."matchedSpecificEpithet",
|
|
| 521 |
"MatchedTaxon"."matchedSpeciesConfidence_fraction",
|
|
| 514 |
"MatchedTaxon"."Author_score", |
|
| 515 |
"MatchedTaxon"."Family_matched",
|
|
| 516 |
"MatchedTaxon"."Family_score",
|
|
| 517 |
"MatchedTaxon"."Name_matched_accepted_family",
|
|
| 518 |
"MatchedTaxon"."Genus_matched",
|
|
| 519 |
"MatchedTaxon"."Genus_score",
|
|
| 520 |
"MatchedTaxon"."Specific_epithet_matched",
|
|
| 521 |
"MatchedTaxon"."Specific_epithet_score",
|
|
| 522 | 522 |
"MatchedTaxon"."Infraspecific_rank", |
| 523 |
"MatchedTaxon"."matchedInfraspecificEpithet",
|
|
| 524 |
"MatchedTaxon"."*Infraspecific_epithet_score",
|
|
| 523 |
"MatchedTaxon"."Infraspecific_epithet_matched",
|
|
| 524 |
"MatchedTaxon"."Infraspecific_epithet_score", |
|
| 525 | 525 |
"MatchedTaxon"."Infraspecific_rank_2", |
| 526 | 526 |
"MatchedTaxon"."Infraspecific_epithet_2_matched", |
| 527 | 527 |
"MatchedTaxon"."Infraspecific_epithet_2_score", |
| 528 |
"MatchedTaxon"."identificationQualifier",
|
|
| 529 |
"MatchedTaxon"."morphospeciesSuffix",
|
|
| 530 |
"MatchedTaxon"."*taxonomicStatus",
|
|
| 531 |
"MatchedTaxon".accepted_taxon_name_no_author,
|
|
| 532 |
"MatchedTaxon".accepted_author,
|
|
| 533 |
"MatchedTaxon".accepted_taxon_rank,
|
|
| 534 |
"MatchedTaxon"."acceptedScientificNameID",
|
|
| 535 |
"MatchedTaxon".accepted_species_binomial,
|
|
| 536 |
"MatchedTaxon".accepted_family,
|
|
| 537 |
"MatchedTaxon"."*Selected",
|
|
| 538 |
"MatchedTaxon"."*Source",
|
|
| 539 |
"MatchedTaxon"."*Warnings",
|
|
| 540 |
"MatchedTaxon"."*Accepted_name_lsid",
|
|
| 541 |
"MatchedTaxon".taxon_scrub__is_valid_match,
|
|
| 528 |
"MatchedTaxon"."Annotations",
|
|
| 529 |
"MatchedTaxon"."Unmatched_terms",
|
|
| 530 |
"MatchedTaxon"."Taxonomic_status",
|
|
| 531 |
"MatchedTaxon"."Accepted_name",
|
|
| 532 |
"MatchedTaxon"."Accepted_name_author",
|
|
| 533 |
"MatchedTaxon"."Accepted_name_rank",
|
|
| 534 |
"MatchedTaxon"."Accepted_name_url",
|
|
| 535 |
"MatchedTaxon"."Accepted_name_species",
|
|
| 536 |
"MatchedTaxon"."Accepted_name_family",
|
|
| 537 |
"MatchedTaxon"."Selected", |
|
| 538 |
"MatchedTaxon"."Source", |
|
| 539 |
"MatchedTaxon"."Warnings", |
|
| 540 |
"MatchedTaxon"."Accepted_name_lsid", |
|
| 541 |
"MatchedTaxon".is_valid_match, |
|
| 542 | 542 |
"MatchedTaxon".scrubbed_unique_taxon_name, |
| 543 | 543 |
"MatchedTaxon"."taxonomicStatus", |
| 544 | 544 |
"MatchedTaxon".accepted_morphospecies_binomial |
| 545 | 545 |
FROM "MatchedTaxon" |
| 546 |
WHERE "MatchedTaxon".taxon_scrub__is_valid_match;
|
|
| 546 |
WHERE "MatchedTaxon".is_valid_match; |
|
| 547 | 547 |
|
| 548 | 548 |
|
| 549 | 549 |
-- |
| ... | ... | |
| 772 | 772 |
|
| 773 | 773 |
CREATE VIEW taxon_scrub AS |
| 774 | 774 |
SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name, |
| 775 |
"ValidMatchedTaxon"."*batch",
|
|
| 775 |
"ValidMatchedTaxon".batch,
|
|
| 776 | 776 |
"ValidMatchedTaxon".match_num, |
| 777 | 777 |
"ValidMatchedTaxon"."Name_number", |
| 778 |
"ValidMatchedTaxon"."concatenatedScientificName",
|
|
| 778 |
"ValidMatchedTaxon"."Name_submitted",
|
|
| 779 | 779 |
"ValidMatchedTaxon"."Overall_score", |
| 780 |
"ValidMatchedTaxon"."matchedTaxonName",
|
|
| 781 |
"ValidMatchedTaxon"."matchedTaxonRank",
|
|
| 782 |
"ValidMatchedTaxon"."*Name_score",
|
|
| 783 |
"ValidMatchedTaxon"."matchedScientificNameAuthorship",
|
|
| 784 |
"ValidMatchedTaxon"."matchedScientificNameID",
|
|
| 780 |
"ValidMatchedTaxon"."Name_matched",
|
|
| 781 |
"ValidMatchedTaxon"."Name_matched_rank",
|
|
| 782 |
"ValidMatchedTaxon"."Name_score", |
|
| 783 |
"ValidMatchedTaxon"."Name_matched_author",
|
|
| 784 |
"ValidMatchedTaxon"."Name_matched_url",
|
|
| 785 | 785 |
"ValidMatchedTaxon"."Author_matched", |
| 786 |
"ValidMatchedTaxon"."*Author_score",
|
|
| 787 |
"ValidMatchedTaxon".parsed_family,
|
|
| 788 |
"ValidMatchedTaxon"."matchedFamilyConfidence_fraction",
|
|
| 789 |
"ValidMatchedTaxon"."matchedFamily",
|
|
| 790 |
"ValidMatchedTaxon"."matchedGenus",
|
|
| 791 |
"ValidMatchedTaxon"."matchedGenusConfidence_fraction",
|
|
| 792 |
"ValidMatchedTaxon"."matchedSpecificEpithet",
|
|
| 793 |
"ValidMatchedTaxon"."matchedSpeciesConfidence_fraction",
|
|
| 786 |
"ValidMatchedTaxon"."Author_score", |
|
| 787 |
"ValidMatchedTaxon"."Family_matched",
|
|
| 788 |
"ValidMatchedTaxon"."Family_score",
|
|
| 789 |
"ValidMatchedTaxon"."Name_matched_accepted_family",
|
|
| 790 |
"ValidMatchedTaxon"."Genus_matched",
|
|
| 791 |
"ValidMatchedTaxon"."Genus_score",
|
|
| 792 |
"ValidMatchedTaxon"."Specific_epithet_matched",
|
|
| 793 |
"ValidMatchedTaxon"."Specific_epithet_score",
|
|
| 794 | 794 |
"ValidMatchedTaxon"."Infraspecific_rank", |
| 795 |
"ValidMatchedTaxon"."matchedInfraspecificEpithet",
|
|
| 796 |
"ValidMatchedTaxon"."*Infraspecific_epithet_score",
|
|
| 795 |
"ValidMatchedTaxon"."Infraspecific_epithet_matched",
|
|
| 796 |
"ValidMatchedTaxon"."Infraspecific_epithet_score", |
|
| 797 | 797 |
"ValidMatchedTaxon"."Infraspecific_rank_2", |
| 798 | 798 |
"ValidMatchedTaxon"."Infraspecific_epithet_2_matched", |
| 799 | 799 |
"ValidMatchedTaxon"."Infraspecific_epithet_2_score", |
| 800 |
"ValidMatchedTaxon"."identificationQualifier",
|
|
| 801 |
"ValidMatchedTaxon"."morphospeciesSuffix",
|
|
| 802 |
"ValidMatchedTaxon"."*taxonomicStatus",
|
|
| 803 |
"ValidMatchedTaxon".accepted_taxon_name_no_author,
|
|
| 804 |
"ValidMatchedTaxon".accepted_author,
|
|
| 805 |
"ValidMatchedTaxon".accepted_taxon_rank,
|
|
| 806 |
"ValidMatchedTaxon"."acceptedScientificNameID",
|
|
| 807 |
"ValidMatchedTaxon".accepted_species_binomial,
|
|
| 808 |
"ValidMatchedTaxon".accepted_family,
|
|
| 809 |
"ValidMatchedTaxon"."*Selected",
|
|
| 810 |
"ValidMatchedTaxon"."*Source",
|
|
| 811 |
"ValidMatchedTaxon"."*Warnings",
|
|
| 812 |
"ValidMatchedTaxon"."*Accepted_name_lsid",
|
|
| 813 |
"ValidMatchedTaxon".taxon_scrub__is_valid_match,
|
|
| 800 |
"ValidMatchedTaxon"."Annotations",
|
|
| 801 |
"ValidMatchedTaxon"."Unmatched_terms",
|
|
| 802 |
"ValidMatchedTaxon"."Taxonomic_status",
|
|
| 803 |
"ValidMatchedTaxon"."Accepted_name",
|
|
| 804 |
"ValidMatchedTaxon"."Accepted_name_author",
|
|
| 805 |
"ValidMatchedTaxon"."Accepted_name_rank",
|
|
| 806 |
"ValidMatchedTaxon"."Accepted_name_url",
|
|
| 807 |
"ValidMatchedTaxon"."Accepted_name_species",
|
|
| 808 |
"ValidMatchedTaxon"."Accepted_name_family",
|
|
| 809 |
"ValidMatchedTaxon"."Selected", |
|
| 810 |
"ValidMatchedTaxon"."Source", |
|
| 811 |
"ValidMatchedTaxon"."Warnings", |
|
| 812 |
"ValidMatchedTaxon"."Accepted_name_lsid", |
|
| 813 |
"ValidMatchedTaxon".is_valid_match, |
|
| 814 | 814 |
"ValidMatchedTaxon"."taxonomicStatus", |
| 815 | 815 |
"ValidMatchedTaxon".accepted_morphospecies_binomial, |
| 816 | 816 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank, |
| ... | ... | |
| 823 | 823 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, |
| 824 | 824 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_with_author, |
| 825 | 825 |
CASE |
| 826 |
WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'family'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, "ValidMatchedTaxon"."morphospeciesSuffix")
|
|
| 827 |
WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, "ValidMatchedTaxon"."morphospeciesSuffix")
|
|
| 826 |
WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'family'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, "ValidMatchedTaxon"."Unmatched_terms")
|
|
| 827 |
WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, "ValidMatchedTaxon"."Unmatched_terms")
|
|
| 828 | 828 |
ELSE (("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus || ' '::text) || "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet)
|
| 829 | 829 |
END AS scrubbed_morphospecies_binomial |
| 830 | 830 |
FROM ("ValidMatchedTaxon"
|
| trunk/schemas/public_.sql | ||
|---|---|---|
| 8456 | 8456 |
identifiedby.fullname AS "identifiedBy__@DwC__@vegpath.org", |
| 8457 | 8457 |
taxondetermination.determinationdate AS "dateIdentified__@DwC__@vegpath.org", |
| 8458 | 8458 |
taxondetermination.notes AS "identificationRemarks__@DwC__@vegpath.org", |
| 8459 |
taxon_scrub."matchedFamily" AS "Family_matched__@TNRS__@vegpath.org",
|
|
| 8460 |
taxon_scrub."matchedTaxonName" AS "Name_matched__@TNRS__@vegpath.org",
|
|
| 8461 |
taxon_scrub."matchedScientificNameAuthorship" AS "Name_matched_author__@TNRS__@vegpath.org",
|
|
| 8459 |
taxon_scrub."Name_matched_accepted_family" AS "Family_matched__@TNRS__@vegpath.org",
|
|
| 8460 |
taxon_scrub."Name_matched" AS "Name_matched__@TNRS__@vegpath.org",
|
|
| 8461 |
taxon_scrub."Name_matched_author" AS "Name_matched_author__@TNRS__@vegpath.org",
|
|
| 8462 | 8462 |
family_higher_plant_group.higher_plant_group AS "[higher_plant_group~]higherClassification__@DwC__@vegpath.org", |
| 8463 | 8463 |
taxon_scrub."taxonomicStatus" AS "taxonomicStatus__@DwC__@vegpath.org", |
| 8464 | 8464 |
taxon_scrub.scrubbed_family, |
| ... | ... | |
| 8503 | 8503 |
LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) |
| 8504 | 8504 |
LEFT JOIN taxonverbatim USING (taxonverbatim_id)) |
| 8505 | 8505 |
LEFT JOIN taxonlabel USING (taxonlabel_id)) |
| 8506 |
LEFT JOIN "TNRS".taxon_scrub ON ((taxon_scrub."concatenatedScientificName" = taxonlabel.taxonomicname)))
|
|
| 8506 |
LEFT JOIN "TNRS".taxon_scrub ON ((taxon_scrub."Name_submitted" = taxonlabel.taxonomicname)))
|
|
| 8507 | 8507 |
LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = taxon_scrub.scrubbed_family))) |
| 8508 | 8508 |
LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = taxon_scrub.scrubbed_family) AND (cultivated_family_locations.country = "plot.**"."country__@DwC__@vegpath.org")))); |
| 8509 | 8509 |
|
| ... | ... | |
| 9619 | 9619 |
-- |
| 9620 | 9620 |
|
| 9621 | 9621 |
CREATE VIEW iucn_red_list_view AS |
| 9622 |
SELECT DISTINCT "ValidMatchedTaxon".accepted_family, |
|
| 9623 |
"ValidMatchedTaxon".accepted_species_binomial |
|
| 9622 |
SELECT DISTINCT "ValidMatchedTaxon"."Accepted_name_family" AS accepted_family,
|
|
| 9623 |
"ValidMatchedTaxon"."Accepted_name_species" AS accepted_species_binomial
|
|
| 9624 | 9624 |
FROM (taxonlabel |
| 9625 |
JOIN "TNRS"."ValidMatchedTaxon" ON (("ValidMatchedTaxon"."concatenatedScientificName" = taxonlabel.taxonomicname)))
|
|
| 9626 |
WHERE (((taxonlabel.source_id = source_by_shortname('IUCN'::text)) AND ("ValidMatchedTaxon".accepted_family IS NOT NULL)) AND ("ValidMatchedTaxon".accepted_species_binomial IS NOT NULL));
|
|
| 9625 |
JOIN "TNRS"."ValidMatchedTaxon" ON (("ValidMatchedTaxon"."Name_submitted" = taxonlabel.taxonomicname)))
|
|
| 9626 |
WHERE (((taxonlabel.source_id = source_by_shortname('IUCN'::text)) AND ("ValidMatchedTaxon"."Accepted_name_family" IS NOT NULL)) AND ("ValidMatchedTaxon"."Accepted_name_species" IS NOT NULL));
|
|
| 9627 | 9627 |
|
| 9628 | 9628 |
|
| 9629 | 9629 |
-- |
| ... | ... | |
| 10760 | 10760 |
LEFT JOIN taxondetermination ON (((taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND taxondetermination.iscurrent))) |
| 10761 | 10761 |
LEFT JOIN taxonverbatim USING (taxonverbatim_id)) |
| 10762 | 10762 |
LEFT JOIN taxonlabel USING (taxonlabel_id)) |
| 10763 |
LEFT JOIN "TNRS".taxon_scrub ON ((taxon_scrub."concatenatedScientificName" = taxonlabel.taxonomicname)))
|
|
| 10763 |
LEFT JOIN "TNRS".taxon_scrub ON ((taxon_scrub."Name_submitted" = taxonlabel.taxonomicname)))
|
|
| 10764 | 10764 |
WHERE (taxon_scrub.scrubbed_taxon_name_no_author IS NOT NULL); |
| 10765 | 10765 |
|
| 10766 | 10766 |
|
| ... | ... | |
| 11099 | 11099 |
CREATE VIEW tnrs_input_name AS |
| 11100 | 11100 |
SELECT DISTINCT taxonlabel.taxonomicname |
| 11101 | 11101 |
FROM (taxonlabel |
| 11102 |
LEFT JOIN "TNRS"."MatchedTaxon" ON (("MatchedTaxon"."concatenatedScientificName" = taxonlabel.taxonomicname)))
|
|
| 11103 |
WHERE (((taxonlabel.taxonomicname IS NOT NULL) AND ("MatchedTaxon"."concatenatedScientificName" IS NULL)) AND "TNRS".taxon_name_is_safe(taxonlabel.taxonomicname))
|
|
| 11102 |
LEFT JOIN "TNRS"."MatchedTaxon" ON (("MatchedTaxon"."Name_submitted" = taxonlabel.taxonomicname)))
|
|
| 11103 |
WHERE (((taxonlabel.taxonomicname IS NOT NULL) AND ("MatchedTaxon"."Name_submitted" IS NULL)) AND "TNRS".taxon_name_is_safe(taxonlabel.taxonomicname))
|
|
| 11104 | 11104 |
ORDER BY taxonlabel.taxonomicname; |
| 11105 | 11105 |
|
| 11106 | 11106 |
|
| ... | ... | |
| 11670 | 11670 |
SELECT taxonverbatim.taxonomicname AS verbatim_name_with_author, |
| 11671 | 11671 |
taxonlabel.taxonomicname AS tnrs_input_name, |
| 11672 | 11672 |
taxon_scrub."taxonomicStatus" AS tnrs_taxonomic_status, |
| 11673 |
taxon_scrub.accepted_family AS tnrs_accepted_name_family,
|
|
| 11674 |
taxon_scrub.accepted_taxon_name_no_author AS tnrs_accepted_name,
|
|
| 11675 |
taxon_scrub.accepted_author AS tnrs_accepted_name_author,
|
|
| 11673 |
taxon_scrub."Accepted_name_family" AS tnrs_accepted_name_family,
|
|
| 11674 |
taxon_scrub."Accepted_name" AS tnrs_accepted_name,
|
|
| 11675 |
taxon_scrub."Accepted_name_author" AS tnrs_accepted_name_author,
|
|
| 11676 | 11676 |
taxon_scrub.accepted_morphospecies_binomial AS taxon_morphospecies |
| 11677 | 11677 |
FROM ((public.taxonverbatim |
| 11678 | 11678 |
JOIN public.taxonlabel USING (taxonlabel_id)) |
| 11679 |
JOIN "TNRS".taxon_scrub ON ((taxon_scrub."concatenatedScientificName" = taxonlabel.taxonomicname)))
|
|
| 11679 |
JOIN "TNRS".taxon_scrub ON ((taxon_scrub."Name_submitted" = taxonlabel.taxonomicname)))
|
|
| 11680 | 11680 |
WHERE (taxonverbatim.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname))
|
| 11681 | 11681 |
ORDER BY taxonlabel.taxonomicname; |
| 11682 | 11682 |
|
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 8456 | 8456 |
identifiedby.fullname AS "identifiedBy__@DwC__@vegpath.org", |
| 8457 | 8457 |
taxondetermination.determinationdate AS "dateIdentified__@DwC__@vegpath.org", |
| 8458 | 8458 |
taxondetermination.notes AS "identificationRemarks__@DwC__@vegpath.org", |
| 8459 |
taxon_scrub."matchedFamily" AS "Family_matched__@TNRS__@vegpath.org",
|
|
| 8460 |
taxon_scrub."matchedTaxonName" AS "Name_matched__@TNRS__@vegpath.org",
|
|
| 8461 |
taxon_scrub."matchedScientificNameAuthorship" AS "Name_matched_author__@TNRS__@vegpath.org",
|
|
| 8459 |
taxon_scrub."Name_matched_accepted_family" AS "Family_matched__@TNRS__@vegpath.org",
|
|
| 8460 |
taxon_scrub."Name_matched" AS "Name_matched__@TNRS__@vegpath.org",
|
|
| 8461 |
taxon_scrub."Name_matched_author" AS "Name_matched_author__@TNRS__@vegpath.org",
|
|
| 8462 | 8462 |
family_higher_plant_group.higher_plant_group AS "[higher_plant_group~]higherClassification__@DwC__@vegpath.org", |
| 8463 | 8463 |
taxon_scrub."taxonomicStatus" AS "taxonomicStatus__@DwC__@vegpath.org", |
| 8464 | 8464 |
taxon_scrub.scrubbed_family, |
| ... | ... | |
| 8503 | 8503 |
LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) |
| 8504 | 8504 |
LEFT JOIN taxonverbatim USING (taxonverbatim_id)) |
| 8505 | 8505 |
LEFT JOIN taxonlabel USING (taxonlabel_id)) |
| 8506 |
LEFT JOIN "TNRS".taxon_scrub ON ((taxon_scrub."concatenatedScientificName" = taxonlabel.taxonomicname)))
|
|
| 8506 |
LEFT JOIN "TNRS".taxon_scrub ON ((taxon_scrub."Name_submitted" = taxonlabel.taxonomicname)))
|
|
| 8507 | 8507 |
LEFT JOIN family_higher_plant_group ON ((family_higher_plant_group.family = taxon_scrub.scrubbed_family))) |
| 8508 | 8508 |
LEFT JOIN cultivated_family_locations ON (((cultivated_family_locations.family = taxon_scrub.scrubbed_family) AND (cultivated_family_locations.country = "plot.**"."country__@DwC__@vegpath.org")))); |
| 8509 | 8509 |
|
| ... | ... | |
| 9619 | 9619 |
-- |
| 9620 | 9620 |
|
| 9621 | 9621 |
CREATE VIEW iucn_red_list_view AS |
| 9622 |
SELECT DISTINCT "ValidMatchedTaxon".accepted_family, |
|
| 9623 |
"ValidMatchedTaxon".accepted_species_binomial |
|
| 9622 |
SELECT DISTINCT "ValidMatchedTaxon"."Accepted_name_family" AS accepted_family,
|
|
| 9623 |
"ValidMatchedTaxon"."Accepted_name_species" AS accepted_species_binomial
|
|
| 9624 | 9624 |
FROM (taxonlabel |
| 9625 |
JOIN "TNRS"."ValidMatchedTaxon" ON (("ValidMatchedTaxon"."concatenatedScientificName" = taxonlabel.taxonomicname)))
|
|
| 9626 |
WHERE (((taxonlabel.source_id = source_by_shortname('IUCN'::text)) AND ("ValidMatchedTaxon".accepted_family IS NOT NULL)) AND ("ValidMatchedTaxon".accepted_species_binomial IS NOT NULL));
|
|
| 9625 |
JOIN "TNRS"."ValidMatchedTaxon" ON (("ValidMatchedTaxon"."Name_submitted" = taxonlabel.taxonomicname)))
|
|
| 9626 |
WHERE (((taxonlabel.source_id = source_by_shortname('IUCN'::text)) AND ("ValidMatchedTaxon"."Accepted_name_family" IS NOT NULL)) AND ("ValidMatchedTaxon"."Accepted_name_species" IS NOT NULL));
|
|
| 9627 | 9627 |
|
| 9628 | 9628 |
|
| 9629 | 9629 |
-- |
| ... | ... | |
| 10760 | 10760 |
LEFT JOIN taxondetermination ON (((taxondetermination.taxonoccurrence_id = taxonoccurrence.taxonoccurrence_id) AND taxondetermination.iscurrent))) |
| 10761 | 10761 |
LEFT JOIN taxonverbatim USING (taxonverbatim_id)) |
| 10762 | 10762 |
LEFT JOIN taxonlabel USING (taxonlabel_id)) |
| 10763 |
LEFT JOIN "TNRS".taxon_scrub ON ((taxon_scrub."concatenatedScientificName" = taxonlabel.taxonomicname)))
|
|
| 10763 |
LEFT JOIN "TNRS".taxon_scrub ON ((taxon_scrub."Name_submitted" = taxonlabel.taxonomicname)))
|
|
| 10764 | 10764 |
WHERE (taxon_scrub.scrubbed_taxon_name_no_author IS NOT NULL); |
| 10765 | 10765 |
|
| 10766 | 10766 |
|
| ... | ... | |
| 11099 | 11099 |
CREATE VIEW tnrs_input_name AS |
| 11100 | 11100 |
SELECT DISTINCT taxonlabel.taxonomicname |
| 11101 | 11101 |
FROM (taxonlabel |
| 11102 |
LEFT JOIN "TNRS"."MatchedTaxon" ON (("MatchedTaxon"."concatenatedScientificName" = taxonlabel.taxonomicname)))
|
|
| 11103 |
WHERE (((taxonlabel.taxonomicname IS NOT NULL) AND ("MatchedTaxon"."concatenatedScientificName" IS NULL)) AND "TNRS".taxon_name_is_safe(taxonlabel.taxonomicname))
|
|
| 11102 |
LEFT JOIN "TNRS"."MatchedTaxon" ON (("MatchedTaxon"."Name_submitted" = taxonlabel.taxonomicname)))
|
|
| 11103 |
WHERE (((taxonlabel.taxonomicname IS NOT NULL) AND ("MatchedTaxon"."Name_submitted" IS NULL)) AND "TNRS".taxon_name_is_safe(taxonlabel.taxonomicname))
|
|
| 11104 | 11104 |
ORDER BY taxonlabel.taxonomicname; |
| 11105 | 11105 |
|
| 11106 | 11106 |
|
| ... | ... | |
| 11670 | 11670 |
SELECT taxonverbatim.taxonomicname AS verbatim_name_with_author, |
| 11671 | 11671 |
taxonlabel.taxonomicname AS tnrs_input_name, |
| 11672 | 11672 |
taxon_scrub."taxonomicStatus" AS tnrs_taxonomic_status, |
| 11673 |
taxon_scrub.accepted_family AS tnrs_accepted_name_family,
|
|
| 11674 |
taxon_scrub.accepted_taxon_name_no_author AS tnrs_accepted_name,
|
|
| 11675 |
taxon_scrub.accepted_author AS tnrs_accepted_name_author,
|
|
| 11673 |
taxon_scrub."Accepted_name_family" AS tnrs_accepted_name_family,
|
|
| 11674 |
taxon_scrub."Accepted_name" AS tnrs_accepted_name,
|
|
| 11675 |
taxon_scrub."Accepted_name_author" AS tnrs_accepted_name_author,
|
|
| 11676 | 11676 |
taxon_scrub.accepted_morphospecies_binomial AS taxon_morphospecies |
| 11677 | 11677 |
FROM ((public.taxonverbatim |
| 11678 | 11678 |
JOIN public.taxonlabel USING (taxonlabel_id)) |
| 11679 |
JOIN "TNRS".taxon_scrub ON ((taxon_scrub."concatenatedScientificName" = taxonlabel.taxonomicname)))
|
|
| 11679 |
JOIN "TNRS".taxon_scrub ON ((taxon_scrub."Name_submitted" = taxonlabel.taxonomicname)))
|
|
| 11680 | 11680 |
WHERE (taxonverbatim.source_id = ( SELECT public.source_by_shortname(("current_schema"())::text) AS source_by_shortname))
|
| 11681 | 11681 |
ORDER BY taxonlabel.taxonomicname; |
| 11682 | 11682 |
|
| ... | ... | |
| 19506 | 19506 |
-- |
| 19507 | 19507 |
|
| 19508 | 19508 |
CREATE VIEW "MatchedTaxon" AS |
| 19509 |
SELECT s."*batch",
|
|
| 19509 |
SELECT s.batch,
|
|
| 19510 | 19510 |
s.match_num, |
| 19511 | 19511 |
s."Name_number", |
| 19512 |
s."concatenatedScientificName",
|
|
| 19512 |
s."Name_submitted",
|
|
| 19513 | 19513 |
s."Overall_score", |
| 19514 |
s."matchedTaxonName",
|
|
| 19515 |
s."matchedTaxonRank",
|
|
| 19516 |
s."*Name_score",
|
|
| 19517 |
s."matchedScientificNameAuthorship",
|
|
| 19518 |
s."matchedScientificNameID",
|
|
| 19514 |
s."Name_matched",
|
|
| 19515 |
s."Name_matched_rank",
|
|
| 19516 |
s."Name_score", |
|
| 19517 |
s."Name_matched_author",
|
|
| 19518 |
s."Name_matched_url",
|
|
| 19519 | 19519 |
s."Author_matched", |
| 19520 |
s."*Author_score",
|
|
| 19521 |
s.parsed_family,
|
|
| 19522 |
s."matchedFamilyConfidence_fraction",
|
|
| 19523 |
s."matchedFamily",
|
|
| 19524 |
s."matchedGenus",
|
|
| 19525 |
s."matchedGenusConfidence_fraction",
|
|
| 19526 |
s."matchedSpecificEpithet",
|
|
| 19527 |
s."matchedSpeciesConfidence_fraction",
|
|
| 19520 |
s."Author_score", |
|
| 19521 |
s."Family_matched",
|
|
| 19522 |
s."Family_score",
|
|
| 19523 |
s."Name_matched_accepted_family",
|
|
| 19524 |
s."Genus_matched",
|
|
| 19525 |
s."Genus_score",
|
|
| 19526 |
s."Specific_epithet_matched",
|
|
| 19527 |
s."Specific_epithet_score",
|
|
| 19528 | 19528 |
s."Infraspecific_rank", |
| 19529 |
s."matchedInfraspecificEpithet",
|
|
| 19530 |
s."*Infraspecific_epithet_score",
|
|
| 19529 |
s."Infraspecific_epithet_matched",
|
|
| 19530 |
s."Infraspecific_epithet_score", |
|
| 19531 | 19531 |
s."Infraspecific_rank_2", |
| 19532 | 19532 |
s."Infraspecific_epithet_2_matched", |
| 19533 | 19533 |
s."Infraspecific_epithet_2_score", |
| 19534 |
s."identificationQualifier",
|
|
| 19535 |
s."morphospeciesSuffix",
|
|
| 19536 |
s."*taxonomicStatus",
|
|
| 19537 |
s.accepted_taxon_name_no_author,
|
|
| 19538 |
s.accepted_author,
|
|
| 19539 |
s.accepted_taxon_rank,
|
|
| 19540 |
s."acceptedScientificNameID",
|
|
| 19541 |
s.accepted_species_binomial,
|
|
| 19542 |
s.accepted_family,
|
|
| 19543 |
s."*Selected",
|
|
| 19544 |
s."*Source",
|
|
| 19545 |
s."*Warnings",
|
|
| 19546 |
s."*Accepted_name_lsid",
|
|
| 19547 |
s.taxon_scrub__is_valid_match,
|
|
| 19534 |
s."Annotations",
|
|
| 19535 |
s."Unmatched_terms",
|
|
| 19536 |
s."Taxonomic_status",
|
|
| 19537 |
s."Accepted_name",
|
|
| 19538 |
s."Accepted_name_author",
|
|
| 19539 |
s."Accepted_name_rank",
|
|
| 19540 |
s."Accepted_name_url",
|
|
| 19541 |
s."Accepted_name_species",
|
|
| 19542 |
s."Accepted_name_family",
|
|
| 19543 |
s."Selected", |
|
| 19544 |
s."Source", |
|
| 19545 |
s."Warnings", |
|
| 19546 |
s."Accepted_name_lsid", |
|
| 19547 |
s.is_valid_match, |
|
| 19548 | 19548 |
s.scrubbed_unique_taxon_name, |
| 19549 |
map_taxonomic_status(s."*taxonomicStatus", s.accepted_taxon_name_no_author) AS "taxonomicStatus",
|
|
| 19549 |
map_taxonomic_status(s."Taxonomic_status", s."Accepted_name") AS "taxonomicStatus",
|
|
| 19550 | 19550 |
CASE |
| 19551 |
WHEN (s.accepted_taxon_rank = 'family'::text) THEN concat_ws(' '::text, s.accepted_family, s."morphospeciesSuffix")
|
|
| 19552 |
WHEN (s.accepted_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, s.accepted_taxon_name_no_author, s."morphospeciesSuffix")
|
|
| 19553 |
ELSE s.accepted_species_binomial
|
|
| 19551 |
WHEN (s."Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, s."Accepted_name_family", s."Unmatched_terms")
|
|
| 19552 |
WHEN (s."Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, s."Accepted_name", s."Unmatched_terms")
|
|
| 19553 |
ELSE s."Accepted_name_species"
|
|
| 19554 | 19554 |
END AS accepted_morphospecies_binomial |
| 19555 |
FROM ( SELECT taxon_best_match.batch AS "*batch",
|
|
| 19555 |
FROM ( SELECT taxon_best_match.batch, |
|
| 19556 | 19556 |
taxon_best_match.match_num, |
| 19557 | 19557 |
taxon_best_match."Name_number", |
| 19558 |
taxon_best_match."Name_submitted" AS "concatenatedScientificName",
|
|
| 19558 |
taxon_best_match."Name_submitted", |
|
| 19559 | 19559 |
taxon_best_match."Overall_score", |
| 19560 |
taxon_best_match."Name_matched" AS "matchedTaxonName",
|
|
| 19561 |
taxon_best_match."Name_matched_rank" AS "matchedTaxonRank",
|
|
| 19562 |
taxon_best_match."Name_score" AS "*Name_score",
|
|
| 19563 |
taxon_best_match."Name_matched_author" AS "matchedScientificNameAuthorship",
|
|
| 19564 |
taxon_best_match."Name_matched_url" AS "matchedScientificNameID",
|
|
| 19560 |
taxon_best_match."Name_matched", |
|
| 19561 |
taxon_best_match."Name_matched_rank", |
|
| 19562 |
taxon_best_match."Name_score", |
|
| 19563 |
taxon_best_match."Name_matched_author", |
|
| 19564 |
taxon_best_match."Name_matched_url", |
|
| 19565 | 19565 |
taxon_best_match."Author_matched", |
| 19566 |
taxon_best_match."Author_score" AS "*Author_score",
|
|
| 19567 |
taxon_best_match."Family_matched" AS parsed_family,
|
|
| 19568 |
taxon_best_match."Family_score" AS "matchedFamilyConfidence_fraction",
|
|
| 19569 |
taxon_best_match."Name_matched_accepted_family" AS "matchedFamily",
|
|
| 19570 |
taxon_best_match."Genus_matched" AS "matchedGenus",
|
|
| 19571 |
taxon_best_match."Genus_score" AS "matchedGenusConfidence_fraction",
|
|
| 19572 |
taxon_best_match."Specific_epithet_matched" AS "matchedSpecificEpithet",
|
|
| 19573 |
taxon_best_match."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction",
|
|
| 19566 |
taxon_best_match."Author_score", |
|
| 19567 |
taxon_best_match."Family_matched", |
|
| 19568 |
taxon_best_match."Family_score", |
|
| 19569 |
taxon_best_match."Name_matched_accepted_family", |
|
| 19570 |
taxon_best_match."Genus_matched", |
|
| 19571 |
taxon_best_match."Genus_score", |
|
| 19572 |
taxon_best_match."Specific_epithet_matched", |
|
| 19573 |
taxon_best_match."Specific_epithet_score", |
|
| 19574 | 19574 |
taxon_best_match."Infraspecific_rank", |
| 19575 |
taxon_best_match."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet",
|
|
| 19576 |
taxon_best_match."Infraspecific_epithet_score" AS "*Infraspecific_epithet_score",
|
|
| 19575 |
taxon_best_match."Infraspecific_epithet_matched", |
|
| 19576 |
taxon_best_match."Infraspecific_epithet_score", |
|
| 19577 | 19577 |
taxon_best_match."Infraspecific_rank_2", |
| 19578 | 19578 |
taxon_best_match."Infraspecific_epithet_2_matched", |
| 19579 | 19579 |
taxon_best_match."Infraspecific_epithet_2_score", |
| 19580 |
taxon_best_match."Annotations" AS "identificationQualifier",
|
|
| 19581 |
taxon_best_match."Unmatched_terms" AS "morphospeciesSuffix",
|
|
| 19582 |
taxon_best_match."Taxonomic_status" AS "*taxonomicStatus",
|
|
| 19583 |
taxon_best_match."Accepted_name" AS accepted_taxon_name_no_author,
|
|
| 19584 |
taxon_best_match."Accepted_name_author" AS accepted_author,
|
|
| 19585 |
taxon_best_match."Accepted_name_rank" AS accepted_taxon_rank,
|
|
| 19586 |
taxon_best_match."Accepted_name_url" AS "acceptedScientificNameID",
|
|
| 19587 |
taxon_best_match."Accepted_name_species" AS accepted_species_binomial,
|
|
| 19588 |
taxon_best_match."Accepted_name_family" AS accepted_family,
|
|
| 19589 |
taxon_best_match."Selected" AS "*Selected",
|
|
| 19590 |
taxon_best_match."Source" AS "*Source",
|
|
| 19591 |
taxon_best_match."Warnings" AS "*Warnings",
|
|
| 19592 |
taxon_best_match."Accepted_name_lsid" AS "*Accepted_name_lsid",
|
|
| 19593 |
taxon_best_match.is_valid_match AS taxon_scrub__is_valid_match,
|
|
| 19580 |
taxon_best_match."Annotations", |
|
| 19581 |
taxon_best_match."Unmatched_terms", |
|
| 19582 |
taxon_best_match."Taxonomic_status", |
|
| 19583 |
taxon_best_match."Accepted_name", |
|
| 19584 |
taxon_best_match."Accepted_name_author", |
|
| 19585 |
taxon_best_match."Accepted_name_rank", |
|
| 19586 |
taxon_best_match."Accepted_name_url", |
|
| 19587 |
taxon_best_match."Accepted_name_species", |
|
| 19588 |
taxon_best_match."Accepted_name_family", |
|
| 19589 |
taxon_best_match."Selected", |
|
| 19590 |
taxon_best_match."Source", |
|
| 19591 |
taxon_best_match."Warnings", |
|
| 19592 |
taxon_best_match."Accepted_name_lsid", |
|
| 19593 |
taxon_best_match.is_valid_match, |
|
| 19594 | 19594 |
taxon_best_match.scrubbed_unique_taxon_name |
| 19595 | 19595 |
FROM taxon_best_match) s; |
| 19596 | 19596 |
|
| ... | ... | |
| 19612 | 19612 |
-- |
| 19613 | 19613 |
|
| 19614 | 19614 |
CREATE VIEW "ValidMatchedTaxon" AS |
| 19615 |
SELECT "MatchedTaxon"."*batch",
|
|
| 19615 |
SELECT "MatchedTaxon".batch,
|
|
| 19616 | 19616 |
"MatchedTaxon".match_num, |
| 19617 | 19617 |
"MatchedTaxon"."Name_number", |
| 19618 |
"MatchedTaxon"."concatenatedScientificName",
|
|
| 19618 |
"MatchedTaxon"."Name_submitted",
|
|
| 19619 | 19619 |
"MatchedTaxon"."Overall_score", |
| 19620 |
"MatchedTaxon"."matchedTaxonName",
|
|
| 19621 |
"MatchedTaxon"."matchedTaxonRank",
|
|
| 19622 |
"MatchedTaxon"."*Name_score",
|
|
| 19623 |
"MatchedTaxon"."matchedScientificNameAuthorship",
|
|
| 19624 |
"MatchedTaxon"."matchedScientificNameID",
|
|
| 19620 |
"MatchedTaxon"."Name_matched",
|
|
| 19621 |
"MatchedTaxon"."Name_matched_rank",
|
|
| 19622 |
"MatchedTaxon"."Name_score", |
|
| 19623 |
"MatchedTaxon"."Name_matched_author",
|
|
| 19624 |
"MatchedTaxon"."Name_matched_url",
|
|
| 19625 | 19625 |
"MatchedTaxon"."Author_matched", |
| 19626 |
"MatchedTaxon"."*Author_score",
|
|
| 19627 |
"MatchedTaxon".parsed_family,
|
|
| 19628 |
"MatchedTaxon"."matchedFamilyConfidence_fraction",
|
|
| 19629 |
"MatchedTaxon"."matchedFamily",
|
|
| 19630 |
"MatchedTaxon"."matchedGenus",
|
|
| 19631 |
"MatchedTaxon"."matchedGenusConfidence_fraction",
|
|
| 19632 |
"MatchedTaxon"."matchedSpecificEpithet",
|
|
| 19633 |
"MatchedTaxon"."matchedSpeciesConfidence_fraction",
|
|
| 19626 |
"MatchedTaxon"."Author_score", |
|
| 19627 |
"MatchedTaxon"."Family_matched",
|
|
| 19628 |
"MatchedTaxon"."Family_score",
|
|
| 19629 |
"MatchedTaxon"."Name_matched_accepted_family",
|
|
| 19630 |
"MatchedTaxon"."Genus_matched",
|
|
| 19631 |
"MatchedTaxon"."Genus_score",
|
|
| 19632 |
"MatchedTaxon"."Specific_epithet_matched",
|
|
| 19633 |
"MatchedTaxon"."Specific_epithet_score",
|
|
| 19634 | 19634 |
"MatchedTaxon"."Infraspecific_rank", |
| 19635 |
"MatchedTaxon"."matchedInfraspecificEpithet",
|
|
| 19636 |
"MatchedTaxon"."*Infraspecific_epithet_score",
|
|
| 19635 |
"MatchedTaxon"."Infraspecific_epithet_matched",
|
|
| 19636 |
"MatchedTaxon"."Infraspecific_epithet_score", |
|
| 19637 | 19637 |
"MatchedTaxon"."Infraspecific_rank_2", |
| 19638 | 19638 |
"MatchedTaxon"."Infraspecific_epithet_2_matched", |
| 19639 | 19639 |
"MatchedTaxon"."Infraspecific_epithet_2_score", |
| 19640 |
"MatchedTaxon"."identificationQualifier",
|
|
| 19641 |
"MatchedTaxon"."morphospeciesSuffix",
|
|
| 19642 |
"MatchedTaxon"."*taxonomicStatus",
|
|
| 19643 |
"MatchedTaxon".accepted_taxon_name_no_author,
|
|
| 19644 |
"MatchedTaxon".accepted_author,
|
|
| 19645 |
"MatchedTaxon".accepted_taxon_rank,
|
|
| 19646 |
"MatchedTaxon"."acceptedScientificNameID",
|
|
| 19647 |
"MatchedTaxon".accepted_species_binomial,
|
|
| 19648 |
"MatchedTaxon".accepted_family,
|
|
| 19649 |
"MatchedTaxon"."*Selected",
|
|
| 19650 |
"MatchedTaxon"."*Source",
|
|
| 19651 |
"MatchedTaxon"."*Warnings",
|
|
| 19652 |
"MatchedTaxon"."*Accepted_name_lsid",
|
|
| 19653 |
"MatchedTaxon".taxon_scrub__is_valid_match,
|
|
| 19640 |
"MatchedTaxon"."Annotations",
|
|
| 19641 |
"MatchedTaxon"."Unmatched_terms",
|
|
| 19642 |
"MatchedTaxon"."Taxonomic_status",
|
|
| 19643 |
"MatchedTaxon"."Accepted_name",
|
|
| 19644 |
"MatchedTaxon"."Accepted_name_author",
|
|
| 19645 |
"MatchedTaxon"."Accepted_name_rank",
|
|
| 19646 |
"MatchedTaxon"."Accepted_name_url",
|
|
| 19647 |
"MatchedTaxon"."Accepted_name_species",
|
|
| 19648 |
"MatchedTaxon"."Accepted_name_family",
|
|
| 19649 |
"MatchedTaxon"."Selected", |
|
| 19650 |
"MatchedTaxon"."Source", |
|
| 19651 |
"MatchedTaxon"."Warnings", |
|
| 19652 |
"MatchedTaxon"."Accepted_name_lsid", |
|
| 19653 |
"MatchedTaxon".is_valid_match, |
|
| 19654 | 19654 |
"MatchedTaxon".scrubbed_unique_taxon_name, |
| 19655 | 19655 |
"MatchedTaxon"."taxonomicStatus", |
| 19656 | 19656 |
"MatchedTaxon".accepted_morphospecies_binomial |
| 19657 | 19657 |
FROM "MatchedTaxon" |
| 19658 |
WHERE "MatchedTaxon".taxon_scrub__is_valid_match;
|
|
| 19658 |
WHERE "MatchedTaxon".is_valid_match; |
|
| 19659 | 19659 |
|
| 19660 | 19660 |
|
| 19661 | 19661 |
-- |
| ... | ... | |
| 19884 | 19884 |
|
| 19885 | 19885 |
CREATE VIEW taxon_scrub AS |
| 19886 | 19886 |
SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name, |
| 19887 |
"ValidMatchedTaxon"."*batch",
|
|
| 19887 |
"ValidMatchedTaxon".batch,
|
|
| 19888 | 19888 |
"ValidMatchedTaxon".match_num, |
| 19889 | 19889 |
"ValidMatchedTaxon"."Name_number", |
| 19890 |
"ValidMatchedTaxon"."concatenatedScientificName",
|
|
| 19890 |
"ValidMatchedTaxon"."Name_submitted",
|
|
| 19891 | 19891 |
"ValidMatchedTaxon"."Overall_score", |
| 19892 |
"ValidMatchedTaxon"."matchedTaxonName",
|
|
| 19893 |
"ValidMatchedTaxon"."matchedTaxonRank",
|
|
| 19894 |
"ValidMatchedTaxon"."*Name_score",
|
|
| 19895 |
"ValidMatchedTaxon"."matchedScientificNameAuthorship",
|
|
| 19896 |
"ValidMatchedTaxon"."matchedScientificNameID",
|
|
| 19892 |
"ValidMatchedTaxon"."Name_matched",
|
|
| 19893 |
"ValidMatchedTaxon"."Name_matched_rank",
|
|
| 19894 |
"ValidMatchedTaxon"."Name_score", |
|
| 19895 |
"ValidMatchedTaxon"."Name_matched_author",
|
|
| 19896 |
"ValidMatchedTaxon"."Name_matched_url",
|
|
| 19897 | 19897 |
"ValidMatchedTaxon"."Author_matched", |
| 19898 |
"ValidMatchedTaxon"."*Author_score",
|
|
| 19899 |
"ValidMatchedTaxon".parsed_family,
|
|
| 19900 |
"ValidMatchedTaxon"."matchedFamilyConfidence_fraction",
|
|
| 19901 |
"ValidMatchedTaxon"."matchedFamily",
|
|
| 19902 |
"ValidMatchedTaxon"."matchedGenus",
|
|
| 19903 |
"ValidMatchedTaxon"."matchedGenusConfidence_fraction",
|
|
| 19904 |
"ValidMatchedTaxon"."matchedSpecificEpithet",
|
|
| 19905 |
"ValidMatchedTaxon"."matchedSpeciesConfidence_fraction",
|
|
| 19898 |
"ValidMatchedTaxon"."Author_score", |
|
| 19899 |
"ValidMatchedTaxon"."Family_matched",
|
|
| 19900 |
"ValidMatchedTaxon"."Family_score",
|
|
| 19901 |
"ValidMatchedTaxon"."Name_matched_accepted_family",
|
|
| 19902 |
"ValidMatchedTaxon"."Genus_matched",
|
|
| 19903 |
"ValidMatchedTaxon"."Genus_score",
|
|
| 19904 |
"ValidMatchedTaxon"."Specific_epithet_matched",
|
|
| 19905 |
"ValidMatchedTaxon"."Specific_epithet_score",
|
|
| 19906 | 19906 |
"ValidMatchedTaxon"."Infraspecific_rank", |
| 19907 |
"ValidMatchedTaxon"."matchedInfraspecificEpithet",
|
|
| 19908 |
"ValidMatchedTaxon"."*Infraspecific_epithet_score",
|
|
| 19907 |
"ValidMatchedTaxon"."Infraspecific_epithet_matched",
|
|
| 19908 |
"ValidMatchedTaxon"."Infraspecific_epithet_score", |
|
| 19909 | 19909 |
"ValidMatchedTaxon"."Infraspecific_rank_2", |
| 19910 | 19910 |
"ValidMatchedTaxon"."Infraspecific_epithet_2_matched", |
| 19911 | 19911 |
"ValidMatchedTaxon"."Infraspecific_epithet_2_score", |
| 19912 |
"ValidMatchedTaxon"."identificationQualifier",
|
|
| 19913 |
"ValidMatchedTaxon"."morphospeciesSuffix",
|
|
| 19914 |
"ValidMatchedTaxon"."*taxonomicStatus",
|
|
| 19915 |
"ValidMatchedTaxon".accepted_taxon_name_no_author,
|
|
| 19916 |
"ValidMatchedTaxon".accepted_author,
|
|
| 19917 |
"ValidMatchedTaxon".accepted_taxon_rank,
|
|
| 19918 |
"ValidMatchedTaxon"."acceptedScientificNameID",
|
|
| 19919 |
"ValidMatchedTaxon".accepted_species_binomial,
|
|
| 19920 |
"ValidMatchedTaxon".accepted_family,
|
|
| 19921 |
"ValidMatchedTaxon"."*Selected",
|
|
| 19922 |
"ValidMatchedTaxon"."*Source",
|
|
| 19923 |
"ValidMatchedTaxon"."*Warnings",
|
|
| 19924 |
"ValidMatchedTaxon"."*Accepted_name_lsid",
|
|
| 19925 |
"ValidMatchedTaxon".taxon_scrub__is_valid_match,
|
|
| 19912 |
"ValidMatchedTaxon"."Annotations",
|
|
| 19913 |
"ValidMatchedTaxon"."Unmatched_terms",
|
|
| 19914 |
"ValidMatchedTaxon"."Taxonomic_status",
|
|
| 19915 |
"ValidMatchedTaxon"."Accepted_name",
|
|
| 19916 |
"ValidMatchedTaxon"."Accepted_name_author",
|
|
| 19917 |
"ValidMatchedTaxon"."Accepted_name_rank",
|
|
| 19918 |
"ValidMatchedTaxon"."Accepted_name_url",
|
|
| 19919 |
"ValidMatchedTaxon"."Accepted_name_species",
|
|
| 19920 |
"ValidMatchedTaxon"."Accepted_name_family",
|
|
| 19921 |
"ValidMatchedTaxon"."Selected", |
|
| 19922 |
"ValidMatchedTaxon"."Source", |
|
| 19923 |
"ValidMatchedTaxon"."Warnings", |
|
| 19924 |
"ValidMatchedTaxon"."Accepted_name_lsid", |
|
| 19925 |
"ValidMatchedTaxon".is_valid_match, |
|
| 19926 | 19926 |
"ValidMatchedTaxon"."taxonomicStatus", |
| 19927 | 19927 |
"ValidMatchedTaxon".accepted_morphospecies_binomial, |
| 19928 | 19928 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank, |
| ... | ... | |
| 19935 | 19935 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, |
| 19936 | 19936 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_with_author, |
| 19937 | 19937 |
CASE |
| 19938 |
WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'family'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, "ValidMatchedTaxon"."morphospeciesSuffix")
|
|
| 19939 |
WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, "ValidMatchedTaxon"."morphospeciesSuffix")
|
|
| 19938 |
WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'family'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, "ValidMatchedTaxon"."Unmatched_terms")
|
|
| 19939 |
WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, "ValidMatchedTaxon"."Unmatched_terms")
|
|
| 19940 | 19940 |
ELSE (("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus || ' '::text) || "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet)
|
| 19941 | 19941 |
END AS scrubbed_morphospecies_binomial |
| 19942 | 19942 |
FROM ("ValidMatchedTaxon"
|
Also available in: Unified diff
inputs/.TNRS/schema.sql: MatchedTaxon: renamed output columns to match input columns, so that the nested select would not be performing any renamings