Revision 7144
Added by Aaron Marcuse-Kubitza over 11 years ago
| schema.sql | ||
|---|---|---|
| 75 | 75 |
EXECUTE PROCEDURE tnrs_populate_accepted_scientific_name(); |
| 76 | 76 |
|
| 77 | 77 |
|
| 78 |
CREATE OR REPLACE VIEW tnrs_canon AS |
|
| 79 |
SELECT ( |
|
| 80 |
CASE |
|
| 81 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 82 |
ELSE tnrs.* |
|
| 83 |
END)."Time_submitted" AS "Time_submitted", ( |
|
| 84 |
CASE |
|
| 85 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 86 |
ELSE tnrs.* |
|
| 87 |
END)."Name_number" AS "Name_number", ( |
|
| 88 |
CASE |
|
| 89 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 90 |
ELSE tnrs.* |
|
| 91 |
END)."Name_submitted" AS "Name_submitted", tnrs."Overall_score", ( |
|
| 92 |
CASE |
|
| 93 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 94 |
ELSE tnrs.* |
|
| 95 |
END)."Name_matched" AS "Name_matched", ( |
|
| 96 |
CASE |
|
| 97 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 98 |
ELSE tnrs.* |
|
| 99 |
END)."Name_matched_rank" AS "Name_matched_rank", tnrs."Name_score", ( |
|
| 100 |
CASE |
|
| 101 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 102 |
ELSE tnrs.* |
|
| 103 |
END)."Name_matched_author" AS "Name_matched_author", ( |
|
| 104 |
CASE |
|
| 105 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 106 |
ELSE tnrs.* |
|
| 107 |
END)."Name_matched_url" AS "Name_matched_url", ( |
|
| 108 |
CASE |
|
| 109 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 110 |
ELSE tnrs.* |
|
| 111 |
END)."Author_matched" AS "Author_matched", tnrs."Author_score", ( |
|
| 112 |
CASE |
|
| 113 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 114 |
ELSE tnrs.* |
|
| 115 |
END)."Family_matched" AS "Family_matched", tnrs."Family_score", ( |
|
| 116 |
CASE |
|
| 117 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 118 |
ELSE tnrs.* |
|
| 119 |
END)."Name_matched_accepted_family" AS "Name_matched_accepted_family", ( |
|
| 120 |
CASE |
|
| 121 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 122 |
ELSE tnrs.* |
|
| 123 |
END)."Genus_matched" AS "Genus_matched", tnrs."Genus_score", ( |
|
| 124 |
CASE |
|
| 125 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 126 |
ELSE tnrs.* |
|
| 127 |
END)."Specific_epithet_matched" AS "Specific_epithet_matched", tnrs."Specific_epithet_score", ( |
|
| 128 |
CASE |
|
| 129 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 130 |
ELSE tnrs.* |
|
| 131 |
END)."Infraspecific_rank" AS "Infraspecific_rank", ( |
|
| 132 |
CASE |
|
| 133 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 134 |
ELSE tnrs.* |
|
| 135 |
END)."Infraspecific_epithet_matched" AS "Infraspecific_epithet_matched", tnrs."Infraspecific_epithet_score", ( |
|
| 136 |
CASE |
|
| 137 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 138 |
ELSE tnrs.* |
|
| 139 |
END)."Infraspecific_rank_2" AS "Infraspecific_rank_2", ( |
|
| 140 |
CASE |
|
| 141 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 142 |
ELSE tnrs.* |
|
| 143 |
END)."Infraspecific_epithet_2_matched" AS "Infraspecific_epithet_2_matched", tnrs."Infraspecific_epithet_2_score", ( |
|
| 144 |
CASE |
|
| 145 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 146 |
ELSE tnrs.* |
|
| 147 |
END)."Annotations" AS "Annotations", ( |
|
| 148 |
CASE |
|
| 149 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 150 |
ELSE tnrs.* |
|
| 151 |
END)."Unmatched_terms" AS "Unmatched_terms", ( |
|
| 152 |
CASE |
|
| 153 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 154 |
ELSE tnrs.* |
|
| 155 |
END)."Taxonomic_status" AS "Taxonomic_status", ( |
|
| 156 |
CASE |
|
| 157 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 158 |
ELSE tnrs.* |
|
| 159 |
END)."Selected" AS "Selected", ( |
|
| 160 |
CASE |
|
| 161 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 162 |
ELSE tnrs.* |
|
| 163 |
END)."Source" AS "Source", ( |
|
| 164 |
CASE |
|
| 165 |
WHEN tnrs_accepted.* IS NOT NULL THEN tnrs_accepted.* |
|
| 166 |
ELSE tnrs.* |
|
| 167 |
END)."Warnings" AS "Warnings" |
|
| 168 |
FROM tnrs |
|
| 169 |
LEFT JOIN tnrs tnrs_accepted ON tnrs_accepted."Name_submitted" = tnrs."Accepted_scientific_name"; |
|
| 170 |
COMMENT ON VIEW tnrs_canon |
|
| 171 |
IS 'The most canonicalized name output by TNRS. This will be the accepted name if available, and the matched name otherwise. In either case, the match scores are always from the matched name, not the accepted name (whose match scores should always be close to 1).'; |
|
| 172 |
|
|
| 173 |
|
|
| 78 | 174 |
CREATE OR REPLACE VIEW "tnrs+accepted" AS |
| 79 | 175 |
SELECT |
| 80 | 176 |
tnrs.* |
Also available in: Unified diff
inputs/.TNRS/schema.sql: Added tnrs_canon, which stores the most canonicalized name output by TNRS