Revision 13878
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/schema.sql | ||
|---|---|---|
| 329 | 329 |
|
| 330 | 330 |
|
| 331 | 331 |
-- |
| 332 |
-- Name: taxon_best_match; Type: VIEW; Schema: TNRS; Owner: - |
|
| 333 |
-- |
|
| 334 |
|
|
| 335 |
CREATE VIEW taxon_best_match AS |
|
| 336 |
SELECT taxon_match.batch, |
|
| 337 |
taxon_match.match_num, |
|
| 338 |
taxon_match."Name_number", |
|
| 339 |
taxon_match."Name_submitted", |
|
| 340 |
taxon_match."Overall_score", |
|
| 341 |
taxon_match."Name_matched", |
|
| 342 |
taxon_match."Name_matched_rank", |
|
| 343 |
taxon_match."Name_score", |
|
| 344 |
taxon_match."Name_matched_author", |
|
| 345 |
taxon_match."Name_matched_url", |
|
| 346 |
taxon_match."Author_matched", |
|
| 347 |
taxon_match."Author_score", |
|
| 348 |
taxon_match."Family_matched", |
|
| 349 |
taxon_match."Family_score", |
|
| 350 |
taxon_match."Name_matched_accepted_family", |
|
| 351 |
taxon_match."Genus_matched", |
|
| 352 |
taxon_match."Genus_score", |
|
| 353 |
taxon_match."Specific_epithet_matched", |
|
| 354 |
taxon_match."Specific_epithet_score", |
|
| 355 |
taxon_match."Infraspecific_rank", |
|
| 356 |
taxon_match."Infraspecific_epithet_matched", |
|
| 357 |
taxon_match."Infraspecific_epithet_score", |
|
| 358 |
taxon_match."Infraspecific_rank_2", |
|
| 359 |
taxon_match."Infraspecific_epithet_2_matched", |
|
| 360 |
taxon_match."Infraspecific_epithet_2_score", |
|
| 361 |
taxon_match."Annotations", |
|
| 362 |
taxon_match."Unmatched_terms", |
|
| 363 |
taxon_match."Taxonomic_status", |
|
| 364 |
taxon_match."Accepted_name", |
|
| 365 |
taxon_match."Accepted_name_author", |
|
| 366 |
taxon_match."Accepted_name_rank", |
|
| 367 |
taxon_match."Accepted_name_url", |
|
| 368 |
taxon_match."Accepted_name_species", |
|
| 369 |
taxon_match."Accepted_name_family", |
|
| 370 |
taxon_match."Selected", |
|
| 371 |
taxon_match."Source", |
|
| 372 |
taxon_match."Warnings", |
|
| 373 |
taxon_match."Accepted_name_lsid", |
|
| 374 |
taxon_match.is_valid_match, |
|
| 375 |
taxon_match.scrubbed_unique_taxon_name |
|
| 376 |
FROM taxon_match |
|
| 377 |
WHERE (taxon_match."Selected" = 'true'::text); |
|
| 378 |
|
|
| 379 |
|
|
| 380 |
-- |
|
| 381 |
-- Name: VIEW taxon_best_match; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 382 |
-- |
|
| 383 |
|
|
| 384 |
COMMENT ON VIEW taxon_best_match IS ' |
|
| 385 |
to modify: |
|
| 386 |
SELECT util.recreate_view(''"TNRS"."taxon_best_match"'', $$
|
|
| 387 |
SELECT __ |
|
| 388 |
$$); |
|
| 389 |
'; |
|
| 390 |
|
|
| 391 |
|
|
| 392 |
-- |
|
| 332 | 393 |
-- Name: MatchedTaxon; Type: VIEW; Schema: TNRS; Owner: - |
| 333 | 394 |
-- |
| 334 | 395 |
|
| ... | ... | |
| 369 | 430 |
WHEN (s.accepted_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, s.accepted_taxon_name_no_author, s."morphospeciesSuffix")
|
| 370 | 431 |
ELSE s.accepted_species_binomial |
| 371 | 432 |
END AS accepted_morphospecies_binomial |
| 372 |
FROM ( SELECT taxon_match.batch AS "*Name_matched.batch", |
|
| 373 |
taxon_match."Name_submitted" AS "concatenatedScientificName", |
|
| 374 |
taxon_match."Name_matched" AS "matchedTaxonName", |
|
| 375 |
taxon_match."Name_matched_rank" AS "matchedTaxonRank", |
|
| 376 |
taxon_match."Name_score" AS "*Name_matched.Name_score", |
|
| 377 |
taxon_match."Name_matched_author" AS "matchedScientificNameAuthorship", |
|
| 378 |
taxon_match."Name_matched_url" AS "matchedScientificNameID", |
|
| 379 |
taxon_match."Author_score" AS "*Name_matched.Author_score", |
|
| 380 |
taxon_match."Family_score" AS "matchedFamilyConfidence_fraction", |
|
| 381 |
COALESCE(taxon_match."Name_matched_accepted_family", taxon_match."Accepted_name_family") AS "matchedFamily",
|
|
| 382 |
taxon_match."Genus_matched" AS "matchedGenus", |
|
| 383 |
taxon_match."Genus_score" AS "matchedGenusConfidence_fraction", |
|
| 384 |
taxon_match."Specific_epithet_matched" AS "matchedSpecificEpithet", |
|
| 385 |
taxon_match."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction", |
|
| 386 |
taxon_match."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet", |
|
| 387 |
taxon_match."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score", |
|
| 388 |
taxon_match."Annotations" AS "identificationQualifier", |
|
| 389 |
taxon_match."Unmatched_terms" AS "morphospeciesSuffix", |
|
| 390 |
map_taxonomic_status(taxon_match."Taxonomic_status", taxon_match."Accepted_name") AS "taxonomicStatus",
|
|
| 391 |
taxon_match."Accepted_name" AS accepted_taxon_name_no_author, |
|
| 392 |
taxon_match."Accepted_name_author" AS accepted_author, |
|
| 393 |
taxon_match."Accepted_name_rank" AS accepted_taxon_rank, |
|
| 394 |
taxon_match."Accepted_name_url" AS "acceptedScientificNameID", |
|
| 395 |
taxon_match."Accepted_name_species" AS accepted_species_binomial, |
|
| 396 |
taxon_match."Accepted_name_family" AS accepted_family, |
|
| 397 |
taxon_match."Selected" AS "*Name_matched.Selected", |
|
| 398 |
taxon_match."Source" AS "*Name_matched.Source", |
|
| 399 |
taxon_match."Warnings" AS "*Name_matched.Warnings", |
|
| 400 |
taxon_match."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid", |
|
| 401 |
taxon_match.is_valid_match AS taxon_scrub__is_valid_match, |
|
| 402 |
taxon_match.scrubbed_unique_taxon_name |
|
| 403 |
FROM taxon_match) s; |
|
| 433 |
FROM ( SELECT taxon_best_match.batch AS "*Name_matched.batch",
|
|
| 434 |
taxon_best_match."Name_submitted" AS "concatenatedScientificName",
|
|
| 435 |
taxon_best_match."Name_matched" AS "matchedTaxonName",
|
|
| 436 |
taxon_best_match."Name_matched_rank" AS "matchedTaxonRank",
|
|
| 437 |
taxon_best_match."Name_score" AS "*Name_matched.Name_score",
|
|
| 438 |
taxon_best_match."Name_matched_author" AS "matchedScientificNameAuthorship",
|
|
| 439 |
taxon_best_match."Name_matched_url" AS "matchedScientificNameID",
|
|
| 440 |
taxon_best_match."Author_score" AS "*Name_matched.Author_score",
|
|
| 441 |
taxon_best_match."Family_score" AS "matchedFamilyConfidence_fraction",
|
|
| 442 |
COALESCE(taxon_best_match."Name_matched_accepted_family", taxon_best_match."Accepted_name_family") AS "matchedFamily",
|
|
| 443 |
taxon_best_match."Genus_matched" AS "matchedGenus",
|
|
| 444 |
taxon_best_match."Genus_score" AS "matchedGenusConfidence_fraction",
|
|
| 445 |
taxon_best_match."Specific_epithet_matched" AS "matchedSpecificEpithet",
|
|
| 446 |
taxon_best_match."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction",
|
|
| 447 |
taxon_best_match."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet",
|
|
| 448 |
taxon_best_match."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score",
|
|
| 449 |
taxon_best_match."Annotations" AS "identificationQualifier",
|
|
| 450 |
taxon_best_match."Unmatched_terms" AS "morphospeciesSuffix",
|
|
| 451 |
map_taxonomic_status(taxon_best_match."Taxonomic_status", taxon_best_match."Accepted_name") AS "taxonomicStatus",
|
|
| 452 |
taxon_best_match."Accepted_name" AS accepted_taxon_name_no_author,
|
|
| 453 |
taxon_best_match."Accepted_name_author" AS accepted_author,
|
|
| 454 |
taxon_best_match."Accepted_name_rank" AS accepted_taxon_rank,
|
|
| 455 |
taxon_best_match."Accepted_name_url" AS "acceptedScientificNameID",
|
|
| 456 |
taxon_best_match."Accepted_name_species" AS accepted_species_binomial,
|
|
| 457 |
taxon_best_match."Accepted_name_family" AS accepted_family,
|
|
| 458 |
taxon_best_match."Selected" AS "*Name_matched.Selected",
|
|
| 459 |
taxon_best_match."Source" AS "*Name_matched.Source",
|
|
| 460 |
taxon_best_match."Warnings" AS "*Name_matched.Warnings",
|
|
| 461 |
taxon_best_match."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid",
|
|
| 462 |
taxon_best_match.is_valid_match AS taxon_scrub__is_valid_match,
|
|
| 463 |
taxon_best_match.scrubbed_unique_taxon_name
|
|
| 464 |
FROM taxon_best_match) s;
|
|
| 404 | 465 |
|
| 405 | 466 |
|
| 406 | 467 |
-- |
| ... | ... | |
| 557 | 618 |
|
| 558 | 619 |
|
| 559 | 620 |
-- |
| 560 |
-- Name: taxon_best_match; Type: VIEW; Schema: TNRS; Owner: - |
|
| 561 |
-- |
|
| 562 |
|
|
| 563 |
CREATE VIEW taxon_best_match AS |
|
| 564 |
SELECT taxon_match.batch, |
|
| 565 |
taxon_match.match_num, |
|
| 566 |
taxon_match."Name_number", |
|
| 567 |
taxon_match."Name_submitted", |
|
| 568 |
taxon_match."Overall_score", |
|
| 569 |
taxon_match."Name_matched", |
|
| 570 |
taxon_match."Name_matched_rank", |
|
| 571 |
taxon_match."Name_score", |
|
| 572 |
taxon_match."Name_matched_author", |
|
| 573 |
taxon_match."Name_matched_url", |
|
| 574 |
taxon_match."Author_matched", |
|
| 575 |
taxon_match."Author_score", |
|
| 576 |
taxon_match."Family_matched", |
|
| 577 |
taxon_match."Family_score", |
|
| 578 |
taxon_match."Name_matched_accepted_family", |
|
| 579 |
taxon_match."Genus_matched", |
|
| 580 |
taxon_match."Genus_score", |
|
| 581 |
taxon_match."Specific_epithet_matched", |
|
| 582 |
taxon_match."Specific_epithet_score", |
|
| 583 |
taxon_match."Infraspecific_rank", |
|
| 584 |
taxon_match."Infraspecific_epithet_matched", |
|
| 585 |
taxon_match."Infraspecific_epithet_score", |
|
| 586 |
taxon_match."Infraspecific_rank_2", |
|
| 587 |
taxon_match."Infraspecific_epithet_2_matched", |
|
| 588 |
taxon_match."Infraspecific_epithet_2_score", |
|
| 589 |
taxon_match."Annotations", |
|
| 590 |
taxon_match."Unmatched_terms", |
|
| 591 |
taxon_match."Taxonomic_status", |
|
| 592 |
taxon_match."Accepted_name", |
|
| 593 |
taxon_match."Accepted_name_author", |
|
| 594 |
taxon_match."Accepted_name_rank", |
|
| 595 |
taxon_match."Accepted_name_url", |
|
| 596 |
taxon_match."Accepted_name_species", |
|
| 597 |
taxon_match."Accepted_name_family", |
|
| 598 |
taxon_match."Selected", |
|
| 599 |
taxon_match."Source", |
|
| 600 |
taxon_match."Warnings", |
|
| 601 |
taxon_match."Accepted_name_lsid", |
|
| 602 |
taxon_match.is_valid_match, |
|
| 603 |
taxon_match.scrubbed_unique_taxon_name |
|
| 604 |
FROM taxon_match |
|
| 605 |
WHERE (taxon_match."Selected" = 'true'::text); |
|
| 606 |
|
|
| 607 |
|
|
| 608 |
-- |
|
| 609 |
-- Name: VIEW taxon_best_match; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 610 |
-- |
|
| 611 |
|
|
| 612 |
COMMENT ON VIEW taxon_best_match IS ' |
|
| 613 |
to modify: |
|
| 614 |
SELECT util.recreate_view(''"TNRS"."taxon_best_match"'', $$
|
|
| 615 |
SELECT __ |
|
| 616 |
$$); |
|
| 617 |
'; |
|
| 618 |
|
|
| 619 |
|
|
| 620 |
-- |
|
| 621 | 621 |
-- Name: taxon_match_input; Type: VIEW; Schema: TNRS; Owner: - |
| 622 | 622 |
-- |
| 623 | 623 |
|
| ... | ... | |
| 952 | 952 |
|
| 953 | 953 |
|
| 954 | 954 |
-- |
| 955 |
-- Name: taxon_best_match; Type: ACL; Schema: TNRS; Owner: - |
|
| 956 |
-- |
|
| 957 |
|
|
| 958 |
REVOKE ALL ON TABLE taxon_best_match FROM PUBLIC; |
|
| 959 |
REVOKE ALL ON TABLE taxon_best_match FROM bien; |
|
| 960 |
GRANT ALL ON TABLE taxon_best_match TO bien; |
|
| 961 |
GRANT SELECT ON TABLE taxon_best_match TO bien_read; |
|
| 962 |
|
|
| 963 |
|
|
| 964 |
-- |
|
| 955 | 965 |
-- Name: MatchedTaxon; Type: ACL; Schema: TNRS; Owner: - |
| 956 | 966 |
-- |
| 957 | 967 |
|
| ... | ... | |
| 972 | 982 |
|
| 973 | 983 |
|
| 974 | 984 |
-- |
| 975 |
-- Name: taxon_best_match; Type: ACL; Schema: TNRS; Owner: - |
|
| 976 |
-- |
|
| 977 |
|
|
| 978 |
REVOKE ALL ON TABLE taxon_best_match FROM PUBLIC; |
|
| 979 |
REVOKE ALL ON TABLE taxon_best_match FROM bien; |
|
| 980 |
GRANT ALL ON TABLE taxon_best_match TO bien; |
|
| 981 |
GRANT SELECT ON TABLE taxon_best_match TO bien_read; |
|
| 982 |
|
|
| 983 |
|
|
| 984 |
-- |
|
| 985 | 985 |
-- Name: taxon_match_input; Type: ACL; Schema: TNRS; Owner: - |
| 986 | 986 |
-- |
| 987 | 987 |
|
| trunk/schemas/vegbien.my.sql | ||
|---|---|---|
| 15410 | 15410 |
|
| 15411 | 15411 |
|
| 15412 | 15412 |
-- |
| 15413 |
-- Name: taxon_best_match; Type: VIEW; Schema: TNRS; Owner: - |
|
| 15414 |
-- |
|
| 15415 |
|
|
| 15416 |
|
|
| 15417 |
|
|
| 15418 |
|
|
| 15419 |
-- |
|
| 15420 |
-- Name: VIEW taxon_best_match; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 15421 |
-- |
|
| 15422 |
|
|
| 15423 |
|
|
| 15424 |
|
|
| 15425 |
|
|
| 15426 |
-- |
|
| 15413 | 15427 |
-- Name: MatchedTaxon; Type: VIEW; Schema: TNRS; Owner: - |
| 15414 | 15428 |
-- |
| 15415 | 15429 |
|
| ... | ... | |
| 15519 | 15533 |
|
| 15520 | 15534 |
|
| 15521 | 15535 |
-- |
| 15522 |
-- Name: taxon_best_match; Type: VIEW; Schema: TNRS; Owner: - |
|
| 15523 |
-- |
|
| 15524 |
|
|
| 15525 |
|
|
| 15526 |
|
|
| 15527 |
|
|
| 15528 |
-- |
|
| 15529 |
-- Name: VIEW taxon_best_match; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 15530 |
-- |
|
| 15531 |
|
|
| 15532 |
|
|
| 15533 |
|
|
| 15534 |
|
|
| 15535 |
-- |
|
| 15536 | 15536 |
-- Name: taxon_match_input; Type: VIEW; Schema: TNRS; Owner: - |
| 15537 | 15537 |
-- |
| 15538 | 15538 |
|
| ... | ... | |
| 15834 | 15834 |
|
| 15835 | 15835 |
|
| 15836 | 15836 |
-- |
| 15837 |
-- Name: MatchedTaxon; Type: ACL; Schema: TNRS; Owner: -
|
|
| 15837 |
-- Name: taxon_best_match; Type: ACL; Schema: TNRS; Owner: -
|
|
| 15838 | 15838 |
-- |
| 15839 | 15839 |
|
| 15840 | 15840 |
|
| ... | ... | |
| 15844 | 15844 |
|
| 15845 | 15845 |
|
| 15846 | 15846 |
-- |
| 15847 |
-- Name: ValidMatchedTaxon; Type: ACL; Schema: TNRS; Owner: -
|
|
| 15847 |
-- Name: MatchedTaxon; Type: ACL; Schema: TNRS; Owner: - |
|
| 15848 | 15848 |
-- |
| 15849 | 15849 |
|
| 15850 | 15850 |
|
| ... | ... | |
| 15854 | 15854 |
|
| 15855 | 15855 |
|
| 15856 | 15856 |
-- |
| 15857 |
-- Name: taxon_best_match; Type: ACL; Schema: TNRS; Owner: -
|
|
| 15857 |
-- Name: ValidMatchedTaxon; Type: ACL; Schema: TNRS; Owner: -
|
|
| 15858 | 15858 |
-- |
| 15859 | 15859 |
|
| 15860 | 15860 |
|
| trunk/schemas/vegbien.sql | ||
|---|---|---|
| 19320 | 19320 |
|
| 19321 | 19321 |
|
| 19322 | 19322 |
-- |
| 19323 |
-- Name: taxon_best_match; Type: VIEW; Schema: TNRS; Owner: - |
|
| 19324 |
-- |
|
| 19325 |
|
|
| 19326 |
CREATE VIEW taxon_best_match AS |
|
| 19327 |
SELECT taxon_match.batch, |
|
| 19328 |
taxon_match.match_num, |
|
| 19329 |
taxon_match."Name_number", |
|
| 19330 |
taxon_match."Name_submitted", |
|
| 19331 |
taxon_match."Overall_score", |
|
| 19332 |
taxon_match."Name_matched", |
|
| 19333 |
taxon_match."Name_matched_rank", |
|
| 19334 |
taxon_match."Name_score", |
|
| 19335 |
taxon_match."Name_matched_author", |
|
| 19336 |
taxon_match."Name_matched_url", |
|
| 19337 |
taxon_match."Author_matched", |
|
| 19338 |
taxon_match."Author_score", |
|
| 19339 |
taxon_match."Family_matched", |
|
| 19340 |
taxon_match."Family_score", |
|
| 19341 |
taxon_match."Name_matched_accepted_family", |
|
| 19342 |
taxon_match."Genus_matched", |
|
| 19343 |
taxon_match."Genus_score", |
|
| 19344 |
taxon_match."Specific_epithet_matched", |
|
| 19345 |
taxon_match."Specific_epithet_score", |
|
| 19346 |
taxon_match."Infraspecific_rank", |
|
| 19347 |
taxon_match."Infraspecific_epithet_matched", |
|
| 19348 |
taxon_match."Infraspecific_epithet_score", |
|
| 19349 |
taxon_match."Infraspecific_rank_2", |
|
| 19350 |
taxon_match."Infraspecific_epithet_2_matched", |
|
| 19351 |
taxon_match."Infraspecific_epithet_2_score", |
|
| 19352 |
taxon_match."Annotations", |
|
| 19353 |
taxon_match."Unmatched_terms", |
|
| 19354 |
taxon_match."Taxonomic_status", |
|
| 19355 |
taxon_match."Accepted_name", |
|
| 19356 |
taxon_match."Accepted_name_author", |
|
| 19357 |
taxon_match."Accepted_name_rank", |
|
| 19358 |
taxon_match."Accepted_name_url", |
|
| 19359 |
taxon_match."Accepted_name_species", |
|
| 19360 |
taxon_match."Accepted_name_family", |
|
| 19361 |
taxon_match."Selected", |
|
| 19362 |
taxon_match."Source", |
|
| 19363 |
taxon_match."Warnings", |
|
| 19364 |
taxon_match."Accepted_name_lsid", |
|
| 19365 |
taxon_match.is_valid_match, |
|
| 19366 |
taxon_match.scrubbed_unique_taxon_name |
|
| 19367 |
FROM taxon_match |
|
| 19368 |
WHERE (taxon_match."Selected" = 'true'::text); |
|
| 19369 |
|
|
| 19370 |
|
|
| 19371 |
-- |
|
| 19372 |
-- Name: VIEW taxon_best_match; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 19373 |
-- |
|
| 19374 |
|
|
| 19375 |
COMMENT ON VIEW taxon_best_match IS ' |
|
| 19376 |
to modify: |
|
| 19377 |
SELECT util.recreate_view(''"TNRS"."taxon_best_match"'', $$
|
|
| 19378 |
SELECT __ |
|
| 19379 |
$$); |
|
| 19380 |
'; |
|
| 19381 |
|
|
| 19382 |
|
|
| 19383 |
-- |
|
| 19323 | 19384 |
-- Name: MatchedTaxon; Type: VIEW; Schema: TNRS; Owner: - |
| 19324 | 19385 |
-- |
| 19325 | 19386 |
|
| ... | ... | |
| 19360 | 19421 |
WHEN (s.accepted_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, s.accepted_taxon_name_no_author, s."morphospeciesSuffix")
|
| 19361 | 19422 |
ELSE s.accepted_species_binomial |
| 19362 | 19423 |
END AS accepted_morphospecies_binomial |
| 19363 |
FROM ( SELECT taxon_match.batch AS "*Name_matched.batch", |
|
| 19364 |
taxon_match."Name_submitted" AS "concatenatedScientificName", |
|
| 19365 |
taxon_match."Name_matched" AS "matchedTaxonName", |
|
| 19366 |
taxon_match."Name_matched_rank" AS "matchedTaxonRank", |
|
| 19367 |
taxon_match."Name_score" AS "*Name_matched.Name_score", |
|
| 19368 |
taxon_match."Name_matched_author" AS "matchedScientificNameAuthorship", |
|
| 19369 |
taxon_match."Name_matched_url" AS "matchedScientificNameID", |
|
| 19370 |
taxon_match."Author_score" AS "*Name_matched.Author_score", |
|
| 19371 |
taxon_match."Family_score" AS "matchedFamilyConfidence_fraction", |
|
| 19372 |
COALESCE(taxon_match."Name_matched_accepted_family", taxon_match."Accepted_name_family") AS "matchedFamily",
|
|
| 19373 |
taxon_match."Genus_matched" AS "matchedGenus", |
|
| 19374 |
taxon_match."Genus_score" AS "matchedGenusConfidence_fraction", |
|
| 19375 |
taxon_match."Specific_epithet_matched" AS "matchedSpecificEpithet", |
|
| 19376 |
taxon_match."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction", |
|
| 19377 |
taxon_match."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet", |
|
| 19378 |
taxon_match."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score", |
|
| 19379 |
taxon_match."Annotations" AS "identificationQualifier", |
|
| 19380 |
taxon_match."Unmatched_terms" AS "morphospeciesSuffix", |
|
| 19381 |
map_taxonomic_status(taxon_match."Taxonomic_status", taxon_match."Accepted_name") AS "taxonomicStatus",
|
|
| 19382 |
taxon_match."Accepted_name" AS accepted_taxon_name_no_author, |
|
| 19383 |
taxon_match."Accepted_name_author" AS accepted_author, |
|
| 19384 |
taxon_match."Accepted_name_rank" AS accepted_taxon_rank, |
|
| 19385 |
taxon_match."Accepted_name_url" AS "acceptedScientificNameID", |
|
| 19386 |
taxon_match."Accepted_name_species" AS accepted_species_binomial, |
|
| 19387 |
taxon_match."Accepted_name_family" AS accepted_family, |
|
| 19388 |
taxon_match."Selected" AS "*Name_matched.Selected", |
|
| 19389 |
taxon_match."Source" AS "*Name_matched.Source", |
|
| 19390 |
taxon_match."Warnings" AS "*Name_matched.Warnings", |
|
| 19391 |
taxon_match."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid", |
|
| 19392 |
taxon_match.is_valid_match AS taxon_scrub__is_valid_match, |
|
| 19393 |
taxon_match.scrubbed_unique_taxon_name |
|
| 19394 |
FROM taxon_match) s; |
|
| 19424 |
FROM ( SELECT taxon_best_match.batch AS "*Name_matched.batch",
|
|
| 19425 |
taxon_best_match."Name_submitted" AS "concatenatedScientificName",
|
|
| 19426 |
taxon_best_match."Name_matched" AS "matchedTaxonName",
|
|
| 19427 |
taxon_best_match."Name_matched_rank" AS "matchedTaxonRank",
|
|
| 19428 |
taxon_best_match."Name_score" AS "*Name_matched.Name_score",
|
|
| 19429 |
taxon_best_match."Name_matched_author" AS "matchedScientificNameAuthorship",
|
|
| 19430 |
taxon_best_match."Name_matched_url" AS "matchedScientificNameID",
|
|
| 19431 |
taxon_best_match."Author_score" AS "*Name_matched.Author_score",
|
|
| 19432 |
taxon_best_match."Family_score" AS "matchedFamilyConfidence_fraction",
|
|
| 19433 |
COALESCE(taxon_best_match."Name_matched_accepted_family", taxon_best_match."Accepted_name_family") AS "matchedFamily",
|
|
| 19434 |
taxon_best_match."Genus_matched" AS "matchedGenus",
|
|
| 19435 |
taxon_best_match."Genus_score" AS "matchedGenusConfidence_fraction",
|
|
| 19436 |
taxon_best_match."Specific_epithet_matched" AS "matchedSpecificEpithet",
|
|
| 19437 |
taxon_best_match."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction",
|
|
| 19438 |
taxon_best_match."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet",
|
|
| 19439 |
taxon_best_match."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score",
|
|
| 19440 |
taxon_best_match."Annotations" AS "identificationQualifier",
|
|
| 19441 |
taxon_best_match."Unmatched_terms" AS "morphospeciesSuffix",
|
|
| 19442 |
map_taxonomic_status(taxon_best_match."Taxonomic_status", taxon_best_match."Accepted_name") AS "taxonomicStatus",
|
|
| 19443 |
taxon_best_match."Accepted_name" AS accepted_taxon_name_no_author,
|
|
| 19444 |
taxon_best_match."Accepted_name_author" AS accepted_author,
|
|
| 19445 |
taxon_best_match."Accepted_name_rank" AS accepted_taxon_rank,
|
|
| 19446 |
taxon_best_match."Accepted_name_url" AS "acceptedScientificNameID",
|
|
| 19447 |
taxon_best_match."Accepted_name_species" AS accepted_species_binomial,
|
|
| 19448 |
taxon_best_match."Accepted_name_family" AS accepted_family,
|
|
| 19449 |
taxon_best_match."Selected" AS "*Name_matched.Selected",
|
|
| 19450 |
taxon_best_match."Source" AS "*Name_matched.Source",
|
|
| 19451 |
taxon_best_match."Warnings" AS "*Name_matched.Warnings",
|
|
| 19452 |
taxon_best_match."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid",
|
|
| 19453 |
taxon_best_match.is_valid_match AS taxon_scrub__is_valid_match,
|
|
| 19454 |
taxon_best_match.scrubbed_unique_taxon_name
|
|
| 19455 |
FROM taxon_best_match) s;
|
|
| 19395 | 19456 |
|
| 19396 | 19457 |
|
| 19397 | 19458 |
-- |
| ... | ... | |
| 19548 | 19609 |
|
| 19549 | 19610 |
|
| 19550 | 19611 |
-- |
| 19551 |
-- Name: taxon_best_match; Type: VIEW; Schema: TNRS; Owner: - |
|
| 19552 |
-- |
|
| 19553 |
|
|
| 19554 |
CREATE VIEW taxon_best_match AS |
|
| 19555 |
SELECT taxon_match.batch, |
|
| 19556 |
taxon_match.match_num, |
|
| 19557 |
taxon_match."Name_number", |
|
| 19558 |
taxon_match."Name_submitted", |
|
| 19559 |
taxon_match."Overall_score", |
|
| 19560 |
taxon_match."Name_matched", |
|
| 19561 |
taxon_match."Name_matched_rank", |
|
| 19562 |
taxon_match."Name_score", |
|
| 19563 |
taxon_match."Name_matched_author", |
|
| 19564 |
taxon_match."Name_matched_url", |
|
| 19565 |
taxon_match."Author_matched", |
|
| 19566 |
taxon_match."Author_score", |
|
| 19567 |
taxon_match."Family_matched", |
|
| 19568 |
taxon_match."Family_score", |
|
| 19569 |
taxon_match."Name_matched_accepted_family", |
|
| 19570 |
taxon_match."Genus_matched", |
|
| 19571 |
taxon_match."Genus_score", |
|
| 19572 |
taxon_match."Specific_epithet_matched", |
|
| 19573 |
taxon_match."Specific_epithet_score", |
|
| 19574 |
taxon_match."Infraspecific_rank", |
|
| 19575 |
taxon_match."Infraspecific_epithet_matched", |
|
| 19576 |
taxon_match."Infraspecific_epithet_score", |
|
| 19577 |
taxon_match."Infraspecific_rank_2", |
|
| 19578 |
taxon_match."Infraspecific_epithet_2_matched", |
|
| 19579 |
taxon_match."Infraspecific_epithet_2_score", |
|
| 19580 |
taxon_match."Annotations", |
|
| 19581 |
taxon_match."Unmatched_terms", |
|
| 19582 |
taxon_match."Taxonomic_status", |
|
| 19583 |
taxon_match."Accepted_name", |
|
| 19584 |
taxon_match."Accepted_name_author", |
|
| 19585 |
taxon_match."Accepted_name_rank", |
|
| 19586 |
taxon_match."Accepted_name_url", |
|
| 19587 |
taxon_match."Accepted_name_species", |
|
| 19588 |
taxon_match."Accepted_name_family", |
|
| 19589 |
taxon_match."Selected", |
|
| 19590 |
taxon_match."Source", |
|
| 19591 |
taxon_match."Warnings", |
|
| 19592 |
taxon_match."Accepted_name_lsid", |
|
| 19593 |
taxon_match.is_valid_match, |
|
| 19594 |
taxon_match.scrubbed_unique_taxon_name |
|
| 19595 |
FROM taxon_match |
|
| 19596 |
WHERE (taxon_match."Selected" = 'true'::text); |
|
| 19597 |
|
|
| 19598 |
|
|
| 19599 |
-- |
|
| 19600 |
-- Name: VIEW taxon_best_match; Type: COMMENT; Schema: TNRS; Owner: - |
|
| 19601 |
-- |
|
| 19602 |
|
|
| 19603 |
COMMENT ON VIEW taxon_best_match IS ' |
|
| 19604 |
to modify: |
|
| 19605 |
SELECT util.recreate_view(''"TNRS"."taxon_best_match"'', $$
|
|
| 19606 |
SELECT __ |
|
| 19607 |
$$); |
|
| 19608 |
'; |
|
| 19609 |
|
|
| 19610 |
|
|
| 19611 |
-- |
|
| 19612 | 19612 |
-- Name: taxon_match_input; Type: VIEW; Schema: TNRS; Owner: - |
| 19613 | 19613 |
-- |
| 19614 | 19614 |
|
| ... | ... | |
| 20020 | 20020 |
|
| 20021 | 20021 |
|
| 20022 | 20022 |
-- |
| 20023 |
-- Name: taxon_best_match; Type: ACL; Schema: TNRS; Owner: - |
|
| 20024 |
-- |
|
| 20025 |
|
|
| 20026 |
REVOKE ALL ON TABLE taxon_best_match FROM PUBLIC; |
|
| 20027 |
REVOKE ALL ON TABLE taxon_best_match FROM bien; |
|
| 20028 |
GRANT ALL ON TABLE taxon_best_match TO bien; |
|
| 20029 |
GRANT SELECT ON TABLE taxon_best_match TO bien_read; |
|
| 20030 |
|
|
| 20031 |
|
|
| 20032 |
-- |
|
| 20023 | 20033 |
-- Name: MatchedTaxon; Type: ACL; Schema: TNRS; Owner: - |
| 20024 | 20034 |
-- |
| 20025 | 20035 |
|
| ... | ... | |
| 20040 | 20050 |
|
| 20041 | 20051 |
|
| 20042 | 20052 |
-- |
| 20043 |
-- Name: taxon_best_match; Type: ACL; Schema: TNRS; Owner: - |
|
| 20044 |
-- |
|
| 20045 |
|
|
| 20046 |
REVOKE ALL ON TABLE taxon_best_match FROM PUBLIC; |
|
| 20047 |
REVOKE ALL ON TABLE taxon_best_match FROM bien; |
|
| 20048 |
GRANT ALL ON TABLE taxon_best_match TO bien; |
|
| 20049 |
GRANT SELECT ON TABLE taxon_best_match TO bien_read; |
|
| 20050 |
|
|
| 20051 |
|
|
| 20052 |
-- |
|
| 20053 | 20053 |
-- Name: taxon_match_input; Type: ACL; Schema: TNRS; Owner: - |
| 20054 | 20054 |
-- |
| 20055 | 20055 |
|
Also available in: Unified diff
fix: inputs/.TNRS/schema.sql: MatchedTaxon: use taxon_best_match instead of taxon_match because this should provide only one match per taxon