Revision 14110
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,
|
|
398 |
s.match_num,
|
|
399 |
s."Name_number",
|
|
400 |
s."Name_submitted",
|
|
401 |
s."Overall_score",
|
|
402 |
s."Name_matched",
|
|
403 |
s."Name_matched_rank",
|
|
404 |
s."Name_score",
|
|
405 |
s."Name_matched_author",
|
|
406 |
s."Name_matched_url",
|
|
407 |
s."Author_matched",
|
|
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 |
s."Infraspecific_rank",
|
|
417 |
s."Infraspecific_epithet_matched",
|
|
418 |
s."Infraspecific_epithet_score",
|
|
419 |
s."Infraspecific_rank_2",
|
|
420 |
s."Infraspecific_epithet_2_matched",
|
|
421 |
s."Infraspecific_epithet_2_score",
|
|
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 |
s.scrubbed_unique_taxon_name,
|
|
437 |
map_taxonomic_status(s."Taxonomic_status", s."Accepted_name") AS "taxonomicStatus",
|
|
397 |
SELECT taxon_best_match.batch,
|
|
398 |
taxon_best_match.match_num,
|
|
399 |
taxon_best_match."Name_number",
|
|
400 |
taxon_best_match."Name_submitted",
|
|
401 |
taxon_best_match."Overall_score",
|
|
402 |
taxon_best_match."Name_matched",
|
|
403 |
taxon_best_match."Name_matched_rank",
|
|
404 |
taxon_best_match."Name_score",
|
|
405 |
taxon_best_match."Name_matched_author",
|
|
406 |
taxon_best_match."Name_matched_url",
|
|
407 |
taxon_best_match."Author_matched",
|
|
408 |
taxon_best_match."Author_score",
|
|
409 |
taxon_best_match."Family_matched",
|
|
410 |
taxon_best_match."Family_score",
|
|
411 |
taxon_best_match."Name_matched_accepted_family",
|
|
412 |
taxon_best_match."Genus_matched",
|
|
413 |
taxon_best_match."Genus_score",
|
|
414 |
taxon_best_match."Specific_epithet_matched",
|
|
415 |
taxon_best_match."Specific_epithet_score",
|
|
416 |
taxon_best_match."Infraspecific_rank",
|
|
417 |
taxon_best_match."Infraspecific_epithet_matched",
|
|
418 |
taxon_best_match."Infraspecific_epithet_score",
|
|
419 |
taxon_best_match."Infraspecific_rank_2",
|
|
420 |
taxon_best_match."Infraspecific_epithet_2_matched",
|
|
421 |
taxon_best_match."Infraspecific_epithet_2_score",
|
|
422 |
taxon_best_match."Annotations",
|
|
423 |
taxon_best_match."Unmatched_terms",
|
|
424 |
taxon_best_match."Taxonomic_status",
|
|
425 |
taxon_best_match."Accepted_name",
|
|
426 |
taxon_best_match."Accepted_name_author",
|
|
427 |
taxon_best_match."Accepted_name_rank",
|
|
428 |
taxon_best_match."Accepted_name_url",
|
|
429 |
taxon_best_match."Accepted_name_species",
|
|
430 |
taxon_best_match."Accepted_name_family",
|
|
431 |
taxon_best_match."Selected",
|
|
432 |
taxon_best_match."Source",
|
|
433 |
taxon_best_match."Warnings",
|
|
434 |
taxon_best_match."Accepted_name_lsid",
|
|
435 |
taxon_best_match.is_valid_match,
|
|
436 |
taxon_best_match.scrubbed_unique_taxon_name,
|
|
437 |
map_taxonomic_status(taxon_best_match."Taxonomic_status", taxon_best_match."Accepted_name") AS "taxonomicStatus",
|
|
438 | 438 |
CASE |
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"
|
|
439 |
WHEN (taxon_best_match."Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, taxon_best_match."Accepted_name_family", taxon_best_match."Unmatched_terms")
|
|
440 |
WHEN (taxon_best_match."Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, taxon_best_match."Accepted_name", taxon_best_match."Unmatched_terms")
|
|
441 |
ELSE taxon_best_match."Accepted_name_species"
|
|
442 | 442 |
END AS accepted_morphospecies_binomial |
443 |
FROM taxon_best_match s;
|
|
443 |
FROM taxon_best_match; |
|
444 | 444 |
|
445 | 445 |
|
446 | 446 |
-- |
trunk/schemas/vegbien.sql | ||
---|---|---|
19506 | 19506 |
-- |
19507 | 19507 |
|
19508 | 19508 |
CREATE VIEW "MatchedTaxon" AS |
19509 |
SELECT s.batch,
|
|
19510 |
s.match_num,
|
|
19511 |
s."Name_number",
|
|
19512 |
s."Name_submitted",
|
|
19513 |
s."Overall_score",
|
|
19514 |
s."Name_matched",
|
|
19515 |
s."Name_matched_rank",
|
|
19516 |
s."Name_score",
|
|
19517 |
s."Name_matched_author",
|
|
19518 |
s."Name_matched_url",
|
|
19519 |
s."Author_matched",
|
|
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 |
s."Infraspecific_rank",
|
|
19529 |
s."Infraspecific_epithet_matched",
|
|
19530 |
s."Infraspecific_epithet_score",
|
|
19531 |
s."Infraspecific_rank_2",
|
|
19532 |
s."Infraspecific_epithet_2_matched",
|
|
19533 |
s."Infraspecific_epithet_2_score",
|
|
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 |
s.scrubbed_unique_taxon_name,
|
|
19549 |
map_taxonomic_status(s."Taxonomic_status", s."Accepted_name") AS "taxonomicStatus",
|
|
19509 |
SELECT taxon_best_match.batch,
|
|
19510 |
taxon_best_match.match_num,
|
|
19511 |
taxon_best_match."Name_number",
|
|
19512 |
taxon_best_match."Name_submitted",
|
|
19513 |
taxon_best_match."Overall_score",
|
|
19514 |
taxon_best_match."Name_matched",
|
|
19515 |
taxon_best_match."Name_matched_rank",
|
|
19516 |
taxon_best_match."Name_score",
|
|
19517 |
taxon_best_match."Name_matched_author",
|
|
19518 |
taxon_best_match."Name_matched_url",
|
|
19519 |
taxon_best_match."Author_matched",
|
|
19520 |
taxon_best_match."Author_score",
|
|
19521 |
taxon_best_match."Family_matched",
|
|
19522 |
taxon_best_match."Family_score",
|
|
19523 |
taxon_best_match."Name_matched_accepted_family",
|
|
19524 |
taxon_best_match."Genus_matched",
|
|
19525 |
taxon_best_match."Genus_score",
|
|
19526 |
taxon_best_match."Specific_epithet_matched",
|
|
19527 |
taxon_best_match."Specific_epithet_score",
|
|
19528 |
taxon_best_match."Infraspecific_rank",
|
|
19529 |
taxon_best_match."Infraspecific_epithet_matched",
|
|
19530 |
taxon_best_match."Infraspecific_epithet_score",
|
|
19531 |
taxon_best_match."Infraspecific_rank_2",
|
|
19532 |
taxon_best_match."Infraspecific_epithet_2_matched",
|
|
19533 |
taxon_best_match."Infraspecific_epithet_2_score",
|
|
19534 |
taxon_best_match."Annotations",
|
|
19535 |
taxon_best_match."Unmatched_terms",
|
|
19536 |
taxon_best_match."Taxonomic_status",
|
|
19537 |
taxon_best_match."Accepted_name",
|
|
19538 |
taxon_best_match."Accepted_name_author",
|
|
19539 |
taxon_best_match."Accepted_name_rank",
|
|
19540 |
taxon_best_match."Accepted_name_url",
|
|
19541 |
taxon_best_match."Accepted_name_species",
|
|
19542 |
taxon_best_match."Accepted_name_family",
|
|
19543 |
taxon_best_match."Selected",
|
|
19544 |
taxon_best_match."Source",
|
|
19545 |
taxon_best_match."Warnings",
|
|
19546 |
taxon_best_match."Accepted_name_lsid",
|
|
19547 |
taxon_best_match.is_valid_match,
|
|
19548 |
taxon_best_match.scrubbed_unique_taxon_name,
|
|
19549 |
map_taxonomic_status(taxon_best_match."Taxonomic_status", taxon_best_match."Accepted_name") AS "taxonomicStatus",
|
|
19550 | 19550 |
CASE |
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"
|
|
19551 |
WHEN (taxon_best_match."Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, taxon_best_match."Accepted_name_family", taxon_best_match."Unmatched_terms")
|
|
19552 |
WHEN (taxon_best_match."Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, taxon_best_match."Accepted_name", taxon_best_match."Unmatched_terms")
|
|
19553 |
ELSE taxon_best_match."Accepted_name_species"
|
|
19554 | 19554 |
END AS accepted_morphospecies_binomial |
19555 |
FROM taxon_best_match s;
|
|
19555 |
FROM taxon_best_match; |
|
19556 | 19556 |
|
19557 | 19557 |
|
19558 | 19558 |
-- |
Also available in: Unified diff
inputs/.TNRS/schema.sql: MatchedTaxon: taxon_best_match: don't alias to `s` since this is no longer a nested select