Revision 14111
Added by Aaron Marcuse-Kubitza over 10 years ago
| schema.sql | ||
|---|---|---|
| 117 | 117 |
DECLARE |
| 118 | 118 |
"Specific_epithet_is_plant" boolean := |
| 119 | 119 |
(CASE |
| 120 |
WHEN new."Infraspecific_epithet_matched" IS NOT NULL |
|
| 121 |
OR new."Infraspecific_epithet_2_matched" IS NOT NULL |
|
| 122 |
OR new."Specific_epithet_score" >= 0.9 -- fuzzy match |
|
| 120 |
WHEN new."*Infraspecific_epithet_matched" IS NOT NULL
|
|
| 121 |
OR new."*Infraspecific_epithet_2_matched" IS NOT NULL
|
|
| 122 |
OR new."*Specific_epithet_score" >= 0.9 -- fuzzy match
|
|
| 123 | 123 |
THEN true |
| 124 | 124 |
ELSE NULL -- ambiguous |
| 125 | 125 |
END); |
| 126 |
never_homonym boolean = COALESCE(new."Author_score" >= 0.6, false); |
|
| 126 |
never_homonym boolean = COALESCE(new."*Author_score" >= 0.6, false);
|
|
| 127 | 127 |
-- author disambiguates |
| 128 | 128 |
family_is_homonym boolean = NOT never_homonym |
| 129 |
AND "TNRS".family_is_homonym(new."Family_matched"); |
|
| 129 |
AND "TNRS".family_is_homonym(new."*Family_matched");
|
|
| 130 | 130 |
genus_is_homonym boolean = NOT never_homonym |
| 131 |
AND "TNRS".genus_is_homonym(new."Genus_matched"); |
|
| 131 |
AND "TNRS".genus_is_homonym(new."*Genus_matched");
|
|
| 132 | 132 |
BEGIN |
| 133 | 133 |
/* exclude homonyms because these are not valid matches (TNRS provides a |
| 134 | 134 |
name, but the name is not meaningful because it is not unambiguous) */ |
| 135 |
new.is_valid_match = new."Taxonomic_status" != 'Invalid' |
|
| 135 |
new.is_valid_match = new."*Taxonomic_status" != 'Invalid'
|
|
| 136 | 136 |
AND COALESCE(CASE |
| 137 |
WHEN new."Family_score" = 1 AND NOT family_is_homonym -- exact match |
|
| 137 |
WHEN new."*Family_score" = 1 AND NOT family_is_homonym -- exact match
|
|
| 138 | 138 |
THEN true |
| 139 | 139 |
ELSE -- consider genus |
| 140 | 140 |
(CASE |
| 141 |
WHEN new."Genus_score" = 1 -- exact match |
|
| 141 |
WHEN new."*Genus_score" = 1 -- exact match
|
|
| 142 | 142 |
THEN |
| 143 | 143 |
(CASE |
| 144 | 144 |
WHEN NOT genus_is_homonym THEN true |
| 145 | 145 |
ELSE "Specific_epithet_is_plant" |
| 146 | 146 |
END) |
| 147 |
WHEN new."Genus_score" >= 0.85 -- fuzzy match |
|
| 147 |
WHEN new."*Genus_score" >= 0.85 -- fuzzy match
|
|
| 148 | 148 |
THEN "Specific_epithet_is_plant" |
| 149 | 149 |
ELSE NULL -- ambiguous |
| 150 | 150 |
END) |
| ... | ... | |
| 153 | 153 |
|
| 154 | 154 |
DECLARE |
| 155 | 155 |
matched_taxon_name_with_author text = NULLIF(concat_ws(' '
|
| 156 |
, NULLIF(NULLIF(new."Name_matched_accepted_family", 'Unknown'), |
|
| 157 |
new."Name_matched") |
|
| 158 |
, NULLIF(new."Name_matched", 'No suitable matches found.') |
|
| 159 |
, new."Name_matched_author" |
|
| 156 |
, NULLIF(NULLIF(new."*Name_matched_accepted_family", 'Unknown'),
|
|
| 157 |
new."*Name_matched")
|
|
| 158 |
, NULLIF(new."*Name_matched", 'No suitable matches found.')
|
|
| 159 |
, new."*Name_matched_author"
|
|
| 160 | 160 |
), ''); |
| 161 | 161 |
accepted_taxon_name_with_author text = NULLIF(concat_ws(' '
|
| 162 |
, NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), |
|
| 163 |
new."Accepted_name") |
|
| 164 |
, new."Accepted_name" |
|
| 165 |
, new."Accepted_name_author" |
|
| 162 |
, NULLIF(NULLIF(new."*Accepted_name_family", 'Unknown'),
|
|
| 163 |
new."*Accepted_name")
|
|
| 164 |
, new."*Accepted_name"
|
|
| 165 |
, new."*Accepted_name_author"
|
|
| 166 | 166 |
), ''); |
| 167 | 167 |
BEGIN |
| 168 | 168 |
new.scrubbed_unique_taxon_name = COALESCE( |
| ... | ... | |
| 262 | 262 |
CREATE TABLE taxon_match ( |
| 263 | 263 |
batch text DEFAULT now() NOT NULL, |
| 264 | 264 |
match_num integer NOT NULL, |
| 265 |
"Name_number" integer NOT NULL, |
|
| 266 |
"Name_submitted" text NOT NULL, |
|
| 267 |
"Overall_score" double precision, |
|
| 268 |
"Name_matched" text, |
|
| 269 |
"Name_matched_rank" text, |
|
| 270 |
"Name_score" double precision, |
|
| 271 |
"Name_matched_author" text, |
|
| 272 |
"Name_matched_url" text, |
|
| 273 |
"Author_matched" text, |
|
| 274 |
"Author_score" double precision, |
|
| 275 |
"Family_matched" text, |
|
| 276 |
"Family_score" double precision, |
|
| 277 |
"Name_matched_accepted_family" text, |
|
| 278 |
"Genus_matched" text, |
|
| 279 |
"Genus_score" double precision, |
|
| 280 |
"Specific_epithet_matched" text, |
|
| 281 |
"Specific_epithet_score" double precision, |
|
| 282 |
"Infraspecific_rank" text, |
|
| 283 |
"Infraspecific_epithet_matched" text, |
|
| 284 |
"Infraspecific_epithet_score" double precision, |
|
| 285 |
"Infraspecific_rank_2" text, |
|
| 286 |
"Infraspecific_epithet_2_matched" text, |
|
| 287 |
"Infraspecific_epithet_2_score" double precision, |
|
| 288 |
"Annotations" text, |
|
| 289 |
"Unmatched_terms" text, |
|
| 290 |
"Taxonomic_status" text, |
|
| 291 |
"Accepted_name" text, |
|
| 292 |
"Accepted_name_author" text, |
|
| 293 |
"Accepted_name_rank" text, |
|
| 294 |
"Accepted_name_url" text, |
|
| 295 |
"Accepted_name_species" text, |
|
| 296 |
"Accepted_name_family" text, |
|
| 297 |
"Selected" text, |
|
| 298 |
"Source" text, |
|
| 299 |
"Warnings" text, |
|
| 300 |
"Accepted_name_lsid" text, |
|
| 265 |
"*Name_number" integer NOT NULL,
|
|
| 266 |
"*Name_submitted" text NOT NULL,
|
|
| 267 |
"*Overall_score" double precision,
|
|
| 268 |
"*Name_matched" text,
|
|
| 269 |
"*Name_matched_rank" text,
|
|
| 270 |
"*Name_score" double precision,
|
|
| 271 |
"*Name_matched_author" text,
|
|
| 272 |
"*Name_matched_url" text,
|
|
| 273 |
"*Author_matched" text,
|
|
| 274 |
"*Author_score" double precision,
|
|
| 275 |
"*Family_matched" text,
|
|
| 276 |
"*Family_score" double precision,
|
|
| 277 |
"*Name_matched_accepted_family" text,
|
|
| 278 |
"*Genus_matched" text,
|
|
| 279 |
"*Genus_score" double precision,
|
|
| 280 |
"*Specific_epithet_matched" text,
|
|
| 281 |
"*Specific_epithet_score" double precision,
|
|
| 282 |
"*Infraspecific_rank" text,
|
|
| 283 |
"*Infraspecific_epithet_matched" text,
|
|
| 284 |
"*Infraspecific_epithet_score" double precision,
|
|
| 285 |
"*Infraspecific_rank_2" text,
|
|
| 286 |
"*Infraspecific_epithet_2_matched" text,
|
|
| 287 |
"*Infraspecific_epithet_2_score" double precision,
|
|
| 288 |
"*Annotations" text,
|
|
| 289 |
"*Unmatched_terms" text,
|
|
| 290 |
"*Taxonomic_status" text,
|
|
| 291 |
"*Accepted_name" text,
|
|
| 292 |
"*Accepted_name_author" text,
|
|
| 293 |
"*Accepted_name_rank" text,
|
|
| 294 |
"*Accepted_name_url" text,
|
|
| 295 |
"*Accepted_name_species" text,
|
|
| 296 |
"*Accepted_name_family" text,
|
|
| 297 |
"*Selected" text,
|
|
| 298 |
"*Source" text,
|
|
| 299 |
"*Warnings" text,
|
|
| 300 |
"*Accepted_name_lsid" text,
|
|
| 301 | 301 |
is_valid_match boolean NOT NULL, |
| 302 | 302 |
scrubbed_unique_taxon_name text |
| 303 | 303 |
); |
| ... | ... | |
| 335 | 335 |
CREATE VIEW taxon_best_match AS |
| 336 | 336 |
SELECT taxon_match.batch, |
| 337 | 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", |
|
| 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 | 374 |
taxon_match.is_valid_match, |
| 375 | 375 |
taxon_match.scrubbed_unique_taxon_name |
| 376 | 376 |
FROM taxon_match |
| 377 |
WHERE (taxon_match."Selected" = 'true'::text); |
|
| 377 |
WHERE (taxon_match."*Selected" = 'true'::text);
|
|
| 378 | 378 |
|
| 379 | 379 |
|
| 380 | 380 |
-- |
| ... | ... | |
| 396 | 396 |
CREATE VIEW "MatchedTaxon" AS |
| 397 | 397 |
SELECT taxon_best_match.batch, |
| 398 | 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", |
|
| 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 | 435 |
taxon_best_match.is_valid_match, |
| 436 | 436 |
taxon_best_match.scrubbed_unique_taxon_name, |
| 437 |
map_taxonomic_status(taxon_best_match."Taxonomic_status", taxon_best_match."Accepted_name") AS "taxonomicStatus",
|
|
| 437 |
map_taxonomic_status(taxon_best_match."*Taxonomic_status", taxon_best_match."*Accepted_name") AS "taxonomicStatus",
|
|
| 438 | 438 |
CASE |
| 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" |
|
| 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 | 443 |
FROM taxon_best_match; |
| 444 | 444 |
|
| ... | ... | |
| 462 | 462 |
CREATE VIEW "ValidMatchedTaxon" AS |
| 463 | 463 |
SELECT "MatchedTaxon".batch, |
| 464 | 464 |
"MatchedTaxon".match_num, |
| 465 |
"MatchedTaxon"."Name_number", |
|
| 466 |
"MatchedTaxon"."Name_submitted", |
|
| 467 |
"MatchedTaxon"."Overall_score", |
|
| 468 |
"MatchedTaxon"."Name_matched", |
|
| 469 |
"MatchedTaxon"."Name_matched_rank", |
|
| 470 |
"MatchedTaxon"."Name_score", |
|
| 471 |
"MatchedTaxon"."Name_matched_author", |
|
| 472 |
"MatchedTaxon"."Name_matched_url", |
|
| 473 |
"MatchedTaxon"."Author_matched", |
|
| 474 |
"MatchedTaxon"."Author_score", |
|
| 475 |
"MatchedTaxon"."Family_matched", |
|
| 476 |
"MatchedTaxon"."Family_score", |
|
| 477 |
"MatchedTaxon"."Name_matched_accepted_family", |
|
| 478 |
"MatchedTaxon"."Genus_matched", |
|
| 479 |
"MatchedTaxon"."Genus_score", |
|
| 480 |
"MatchedTaxon"."Specific_epithet_matched", |
|
| 481 |
"MatchedTaxon"."Specific_epithet_score", |
|
| 482 |
"MatchedTaxon"."Infraspecific_rank", |
|
| 483 |
"MatchedTaxon"."Infraspecific_epithet_matched", |
|
| 484 |
"MatchedTaxon"."Infraspecific_epithet_score", |
|
| 485 |
"MatchedTaxon"."Infraspecific_rank_2", |
|
| 486 |
"MatchedTaxon"."Infraspecific_epithet_2_matched", |
|
| 487 |
"MatchedTaxon"."Infraspecific_epithet_2_score", |
|
| 488 |
"MatchedTaxon"."Annotations", |
|
| 489 |
"MatchedTaxon"."Unmatched_terms", |
|
| 490 |
"MatchedTaxon"."Taxonomic_status", |
|
| 491 |
"MatchedTaxon"."Accepted_name", |
|
| 492 |
"MatchedTaxon"."Accepted_name_author", |
|
| 493 |
"MatchedTaxon"."Accepted_name_rank", |
|
| 494 |
"MatchedTaxon"."Accepted_name_url", |
|
| 495 |
"MatchedTaxon"."Accepted_name_species", |
|
| 496 |
"MatchedTaxon"."Accepted_name_family", |
|
| 497 |
"MatchedTaxon"."Selected", |
|
| 498 |
"MatchedTaxon"."Source", |
|
| 499 |
"MatchedTaxon"."Warnings", |
|
| 500 |
"MatchedTaxon"."Accepted_name_lsid", |
|
| 465 |
"MatchedTaxon"."*Name_number",
|
|
| 466 |
"MatchedTaxon"."*Name_submitted",
|
|
| 467 |
"MatchedTaxon"."*Overall_score",
|
|
| 468 |
"MatchedTaxon"."*Name_matched",
|
|
| 469 |
"MatchedTaxon"."*Name_matched_rank",
|
|
| 470 |
"MatchedTaxon"."*Name_score",
|
|
| 471 |
"MatchedTaxon"."*Name_matched_author",
|
|
| 472 |
"MatchedTaxon"."*Name_matched_url",
|
|
| 473 |
"MatchedTaxon"."*Author_matched",
|
|
| 474 |
"MatchedTaxon"."*Author_score",
|
|
| 475 |
"MatchedTaxon"."*Family_matched",
|
|
| 476 |
"MatchedTaxon"."*Family_score",
|
|
| 477 |
"MatchedTaxon"."*Name_matched_accepted_family",
|
|
| 478 |
"MatchedTaxon"."*Genus_matched",
|
|
| 479 |
"MatchedTaxon"."*Genus_score",
|
|
| 480 |
"MatchedTaxon"."*Specific_epithet_matched",
|
|
| 481 |
"MatchedTaxon"."*Specific_epithet_score",
|
|
| 482 |
"MatchedTaxon"."*Infraspecific_rank",
|
|
| 483 |
"MatchedTaxon"."*Infraspecific_epithet_matched",
|
|
| 484 |
"MatchedTaxon"."*Infraspecific_epithet_score",
|
|
| 485 |
"MatchedTaxon"."*Infraspecific_rank_2",
|
|
| 486 |
"MatchedTaxon"."*Infraspecific_epithet_2_matched",
|
|
| 487 |
"MatchedTaxon"."*Infraspecific_epithet_2_score",
|
|
| 488 |
"MatchedTaxon"."*Annotations",
|
|
| 489 |
"MatchedTaxon"."*Unmatched_terms",
|
|
| 490 |
"MatchedTaxon"."*Taxonomic_status",
|
|
| 491 |
"MatchedTaxon"."*Accepted_name",
|
|
| 492 |
"MatchedTaxon"."*Accepted_name_author",
|
|
| 493 |
"MatchedTaxon"."*Accepted_name_rank",
|
|
| 494 |
"MatchedTaxon"."*Accepted_name_url",
|
|
| 495 |
"MatchedTaxon"."*Accepted_name_species",
|
|
| 496 |
"MatchedTaxon"."*Accepted_name_family",
|
|
| 497 |
"MatchedTaxon"."*Selected",
|
|
| 498 |
"MatchedTaxon"."*Source",
|
|
| 499 |
"MatchedTaxon"."*Warnings",
|
|
| 500 |
"MatchedTaxon"."*Accepted_name_lsid",
|
|
| 501 | 501 |
"MatchedTaxon".is_valid_match, |
| 502 | 502 |
"MatchedTaxon".scrubbed_unique_taxon_name, |
| 503 | 503 |
"MatchedTaxon"."taxonomicStatus", |
| ... | ... | |
| 611 | 611 |
-- |
| 612 | 612 |
|
| 613 | 613 |
CREATE VIEW taxon_match_input AS |
| 614 |
SELECT taxon_match."Name_number", |
|
| 615 |
taxon_match."Name_submitted", |
|
| 616 |
taxon_match."Overall_score", |
|
| 617 |
taxon_match."Name_matched", |
|
| 618 |
taxon_match."Name_matched_rank", |
|
| 619 |
taxon_match."Name_score", |
|
| 620 |
taxon_match."Name_matched_author", |
|
| 621 |
taxon_match."Name_matched_url", |
|
| 622 |
taxon_match."Author_matched", |
|
| 623 |
taxon_match."Author_score", |
|
| 624 |
taxon_match."Family_matched", |
|
| 625 |
taxon_match."Family_score", |
|
| 626 |
taxon_match."Name_matched_accepted_family", |
|
| 627 |
taxon_match."Genus_matched", |
|
| 628 |
taxon_match."Genus_score", |
|
| 629 |
taxon_match."Specific_epithet_matched", |
|
| 630 |
taxon_match."Specific_epithet_score", |
|
| 631 |
taxon_match."Infraspecific_rank", |
|
| 632 |
taxon_match."Infraspecific_epithet_matched", |
|
| 633 |
taxon_match."Infraspecific_epithet_score", |
|
| 634 |
taxon_match."Infraspecific_rank_2", |
|
| 635 |
taxon_match."Infraspecific_epithet_2_matched", |
|
| 636 |
taxon_match."Infraspecific_epithet_2_score", |
|
| 637 |
taxon_match."Annotations", |
|
| 638 |
taxon_match."Unmatched_terms", |
|
| 639 |
taxon_match."Taxonomic_status", |
|
| 640 |
taxon_match."Accepted_name", |
|
| 641 |
taxon_match."Accepted_name_author", |
|
| 642 |
taxon_match."Accepted_name_rank", |
|
| 643 |
taxon_match."Accepted_name_url", |
|
| 644 |
taxon_match."Accepted_name_species", |
|
| 645 |
taxon_match."Accepted_name_family", |
|
| 646 |
taxon_match."Selected", |
|
| 647 |
taxon_match."Source", |
|
| 648 |
taxon_match."Warnings", |
|
| 649 |
taxon_match."Accepted_name_lsid" |
|
| 614 |
SELECT taxon_match."*Name_number" AS "Name_number",
|
|
| 615 |
taxon_match."*Name_submitted" AS "Name_submitted",
|
|
| 616 |
taxon_match."*Overall_score" AS "Overall_score",
|
|
| 617 |
taxon_match."*Name_matched" AS "Name_matched",
|
|
| 618 |
taxon_match."*Name_matched_rank" AS "Name_matched_rank",
|
|
| 619 |
taxon_match."*Name_score" AS "Name_score",
|
|
| 620 |
taxon_match."*Name_matched_author" AS "Name_matched_author",
|
|
| 621 |
taxon_match."*Name_matched_url" AS "Name_matched_url",
|
|
| 622 |
taxon_match."*Author_matched" AS "Author_matched",
|
|
| 623 |
taxon_match."*Author_score" AS "Author_score",
|
|
| 624 |
taxon_match."*Family_matched" AS "Family_matched",
|
|
| 625 |
taxon_match."*Family_score" AS "Family_score",
|
|
| 626 |
taxon_match."*Name_matched_accepted_family" AS "Name_matched_accepted_family",
|
|
| 627 |
taxon_match."*Genus_matched" AS "Genus_matched",
|
|
| 628 |
taxon_match."*Genus_score" AS "Genus_score",
|
|
| 629 |
taxon_match."*Specific_epithet_matched" AS "Specific_epithet_matched",
|
|
| 630 |
taxon_match."*Specific_epithet_score" AS "Specific_epithet_score",
|
|
| 631 |
taxon_match."*Infraspecific_rank" AS "Infraspecific_rank",
|
|
| 632 |
taxon_match."*Infraspecific_epithet_matched" AS "Infraspecific_epithet_matched",
|
|
| 633 |
taxon_match."*Infraspecific_epithet_score" AS "Infraspecific_epithet_score",
|
|
| 634 |
taxon_match."*Infraspecific_rank_2" AS "Infraspecific_rank_2",
|
|
| 635 |
taxon_match."*Infraspecific_epithet_2_matched" AS "Infraspecific_epithet_2_matched",
|
|
| 636 |
taxon_match."*Infraspecific_epithet_2_score" AS "Infraspecific_epithet_2_score",
|
|
| 637 |
taxon_match."*Annotations" AS "Annotations",
|
|
| 638 |
taxon_match."*Unmatched_terms" AS "Unmatched_terms",
|
|
| 639 |
taxon_match."*Taxonomic_status" AS "Taxonomic_status",
|
|
| 640 |
taxon_match."*Accepted_name" AS "Accepted_name",
|
|
| 641 |
taxon_match."*Accepted_name_author" AS "Accepted_name_author",
|
|
| 642 |
taxon_match."*Accepted_name_rank" AS "Accepted_name_rank",
|
|
| 643 |
taxon_match."*Accepted_name_url" AS "Accepted_name_url",
|
|
| 644 |
taxon_match."*Accepted_name_species" AS "Accepted_name_species",
|
|
| 645 |
taxon_match."*Accepted_name_family" AS "Accepted_name_family",
|
|
| 646 |
taxon_match."*Selected" AS "Selected",
|
|
| 647 |
taxon_match."*Source" AS "Source",
|
|
| 648 |
taxon_match."*Warnings" AS "Warnings",
|
|
| 649 |
taxon_match."*Accepted_name_lsid" AS "Accepted_name_lsid"
|
|
| 650 | 650 |
FROM taxon_match; |
| 651 | 651 |
|
| 652 | 652 |
|
| ... | ... | |
| 699 | 699 |
-- |
| 700 | 700 |
|
| 701 | 701 |
CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS |
| 702 |
SELECT taxon_match."Name_submitted" AS scrubbed_unique_taxon_name, |
|
| 703 |
taxon_match."Name_matched_rank" AS scrubbed_taxon_rank, |
|
| 704 |
COALESCE(taxon_match."Name_matched_accepted_family", taxon_match."Family_matched") AS scrubbed_family,
|
|
| 705 |
taxon_match."Genus_matched" AS scrubbed_genus, |
|
| 706 |
taxon_match."Specific_epithet_matched" AS scrubbed_specific_epithet, |
|
| 707 |
taxon_match."Infraspecific_rank" AS scrubbed_infraspecific_rank, |
|
| 708 |
taxon_match."Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet, |
|
| 709 |
taxon_match."Name_matched_author" AS scrubbed_author, |
|
| 710 |
taxon_match."Name_matched" AS scrubbed_taxon_name_no_author, |
|
| 711 |
(taxon_match."Name_matched" || COALESCE((' '::text || taxon_match."Name_matched_author"), ''::text)) AS scrubbed_taxon_name_with_author
|
|
| 702 |
SELECT taxon_match."*Name_submitted" AS scrubbed_unique_taxon_name,
|
|
| 703 |
taxon_match."*Name_matched_rank" AS scrubbed_taxon_rank,
|
|
| 704 |
COALESCE(taxon_match."*Name_matched_accepted_family", taxon_match."*Family_matched") AS scrubbed_family,
|
|
| 705 |
taxon_match."*Genus_matched" AS scrubbed_genus,
|
|
| 706 |
taxon_match."*Specific_epithet_matched" AS scrubbed_specific_epithet,
|
|
| 707 |
taxon_match."*Infraspecific_rank" AS scrubbed_infraspecific_rank,
|
|
| 708 |
taxon_match."*Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet,
|
|
| 709 |
taxon_match."*Name_matched_author" AS scrubbed_author,
|
|
| 710 |
taxon_match."*Name_matched" AS scrubbed_taxon_name_no_author,
|
|
| 711 |
(taxon_match."*Name_matched" || COALESCE((' '::text || taxon_match."*Name_matched_author"), ''::text)) AS scrubbed_taxon_name_with_author
|
|
| 712 | 712 |
FROM taxon_match; |
| 713 | 713 |
|
| 714 | 714 |
|
| ... | ... | |
| 734 | 734 |
SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name, |
| 735 | 735 |
"ValidMatchedTaxon".batch, |
| 736 | 736 |
"ValidMatchedTaxon".match_num, |
| 737 |
"ValidMatchedTaxon"."Name_number", |
|
| 738 |
"ValidMatchedTaxon"."Name_submitted", |
|
| 739 |
"ValidMatchedTaxon"."Overall_score", |
|
| 740 |
"ValidMatchedTaxon"."Name_matched", |
|
| 741 |
"ValidMatchedTaxon"."Name_matched_rank", |
|
| 742 |
"ValidMatchedTaxon"."Name_score", |
|
| 743 |
"ValidMatchedTaxon"."Name_matched_author", |
|
| 744 |
"ValidMatchedTaxon"."Name_matched_url", |
|
| 745 |
"ValidMatchedTaxon"."Author_matched", |
|
| 746 |
"ValidMatchedTaxon"."Author_score", |
|
| 747 |
"ValidMatchedTaxon"."Family_matched", |
|
| 748 |
"ValidMatchedTaxon"."Family_score", |
|
| 749 |
"ValidMatchedTaxon"."Name_matched_accepted_family", |
|
| 750 |
"ValidMatchedTaxon"."Genus_matched", |
|
| 751 |
"ValidMatchedTaxon"."Genus_score", |
|
| 752 |
"ValidMatchedTaxon"."Specific_epithet_matched", |
|
| 753 |
"ValidMatchedTaxon"."Specific_epithet_score", |
|
| 754 |
"ValidMatchedTaxon"."Infraspecific_rank", |
|
| 755 |
"ValidMatchedTaxon"."Infraspecific_epithet_matched", |
|
| 756 |
"ValidMatchedTaxon"."Infraspecific_epithet_score", |
|
| 757 |
"ValidMatchedTaxon"."Infraspecific_rank_2", |
|
| 758 |
"ValidMatchedTaxon"."Infraspecific_epithet_2_matched", |
|
| 759 |
"ValidMatchedTaxon"."Infraspecific_epithet_2_score", |
|
| 760 |
"ValidMatchedTaxon"."Annotations", |
|
| 761 |
"ValidMatchedTaxon"."Unmatched_terms", |
|
| 762 |
"ValidMatchedTaxon"."Taxonomic_status", |
|
| 763 |
"ValidMatchedTaxon"."Accepted_name", |
|
| 764 |
"ValidMatchedTaxon"."Accepted_name_author", |
|
| 765 |
"ValidMatchedTaxon"."Accepted_name_rank", |
|
| 766 |
"ValidMatchedTaxon"."Accepted_name_url", |
|
| 767 |
"ValidMatchedTaxon"."Accepted_name_species", |
|
| 768 |
"ValidMatchedTaxon"."Accepted_name_family", |
|
| 769 |
"ValidMatchedTaxon"."Selected", |
|
| 770 |
"ValidMatchedTaxon"."Source", |
|
| 771 |
"ValidMatchedTaxon"."Warnings", |
|
| 772 |
"ValidMatchedTaxon"."Accepted_name_lsid", |
|
| 737 |
"ValidMatchedTaxon"."*Name_number",
|
|
| 738 |
"ValidMatchedTaxon"."*Name_submitted",
|
|
| 739 |
"ValidMatchedTaxon"."*Overall_score",
|
|
| 740 |
"ValidMatchedTaxon"."*Name_matched",
|
|
| 741 |
"ValidMatchedTaxon"."*Name_matched_rank",
|
|
| 742 |
"ValidMatchedTaxon"."*Name_score",
|
|
| 743 |
"ValidMatchedTaxon"."*Name_matched_author",
|
|
| 744 |
"ValidMatchedTaxon"."*Name_matched_url",
|
|
| 745 |
"ValidMatchedTaxon"."*Author_matched",
|
|
| 746 |
"ValidMatchedTaxon"."*Author_score",
|
|
| 747 |
"ValidMatchedTaxon"."*Family_matched",
|
|
| 748 |
"ValidMatchedTaxon"."*Family_score",
|
|
| 749 |
"ValidMatchedTaxon"."*Name_matched_accepted_family",
|
|
| 750 |
"ValidMatchedTaxon"."*Genus_matched",
|
|
| 751 |
"ValidMatchedTaxon"."*Genus_score",
|
|
| 752 |
"ValidMatchedTaxon"."*Specific_epithet_matched",
|
|
| 753 |
"ValidMatchedTaxon"."*Specific_epithet_score",
|
|
| 754 |
"ValidMatchedTaxon"."*Infraspecific_rank",
|
|
| 755 |
"ValidMatchedTaxon"."*Infraspecific_epithet_matched",
|
|
| 756 |
"ValidMatchedTaxon"."*Infraspecific_epithet_score",
|
|
| 757 |
"ValidMatchedTaxon"."*Infraspecific_rank_2",
|
|
| 758 |
"ValidMatchedTaxon"."*Infraspecific_epithet_2_matched",
|
|
| 759 |
"ValidMatchedTaxon"."*Infraspecific_epithet_2_score",
|
|
| 760 |
"ValidMatchedTaxon"."*Annotations",
|
|
| 761 |
"ValidMatchedTaxon"."*Unmatched_terms",
|
|
| 762 |
"ValidMatchedTaxon"."*Taxonomic_status",
|
|
| 763 |
"ValidMatchedTaxon"."*Accepted_name",
|
|
| 764 |
"ValidMatchedTaxon"."*Accepted_name_author",
|
|
| 765 |
"ValidMatchedTaxon"."*Accepted_name_rank",
|
|
| 766 |
"ValidMatchedTaxon"."*Accepted_name_url",
|
|
| 767 |
"ValidMatchedTaxon"."*Accepted_name_species",
|
|
| 768 |
"ValidMatchedTaxon"."*Accepted_name_family",
|
|
| 769 |
"ValidMatchedTaxon"."*Selected",
|
|
| 770 |
"ValidMatchedTaxon"."*Source",
|
|
| 771 |
"ValidMatchedTaxon"."*Warnings",
|
|
| 772 |
"ValidMatchedTaxon"."*Accepted_name_lsid",
|
|
| 773 | 773 |
"ValidMatchedTaxon".is_valid_match, |
| 774 | 774 |
"ValidMatchedTaxon"."taxonomicStatus", |
| 775 | 775 |
"ValidMatchedTaxon".accepted_morphospecies_binomial, |
| ... | ... | |
| 783 | 783 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, |
| 784 | 784 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_with_author, |
| 785 | 785 |
CASE |
| 786 |
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")
|
|
| 787 |
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")
|
|
| 786 |
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")
|
|
| 787 |
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")
|
|
| 788 | 788 |
ELSE (("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus || ' '::text) || "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet)
|
| 789 | 789 |
END AS scrubbed_morphospecies_binomial |
| 790 | 790 |
FROM ("ValidMatchedTaxon"
|
| ... | ... | |
| 854 | 854 |
-- Name: taxon_best_match__valid_match; Type: INDEX; Schema: TNRS; Owner: -; Tablespace: |
| 855 | 855 |
-- |
| 856 | 856 |
|
| 857 |
CREATE INDEX taxon_best_match__valid_match ON taxon_match USING btree ("Name_submitted") WHERE (("Selected" = 'true'::text) AND is_valid_match);
|
|
| 857 |
CREATE INDEX taxon_best_match__valid_match ON taxon_match USING btree ("*Name_submitted") WHERE (("*Selected" = 'true'::text) AND is_valid_match);
|
|
| 858 | 858 |
|
| 859 | 859 |
|
| 860 | 860 |
-- |
| 861 | 861 |
-- Name: taxon_match_Name_submitted_idx; Type: INDEX; Schema: TNRS; Owner: -; Tablespace: |
| 862 | 862 |
-- |
| 863 | 863 |
|
| 864 |
CREATE INDEX "taxon_match_Name_submitted_idx" ON taxon_match USING btree ("Name_submitted");
|
|
| 864 |
CREATE INDEX "taxon_match_Name_submitted_idx" ON taxon_match USING btree ("*Name_submitted");
|
|
| 865 | 865 |
|
| 866 | 866 |
|
| 867 | 867 |
-- |
| 868 | 868 |
-- Name: taxon_match__one_selected_match; Type: INDEX; Schema: TNRS; Owner: -; Tablespace: |
| 869 | 869 |
-- |
| 870 | 870 |
|
| 871 |
CREATE UNIQUE INDEX taxon_match__one_selected_match ON taxon_match USING btree (batch, "Name_number") WHERE ("Selected" = 'true'::text);
|
|
| 871 |
CREATE UNIQUE INDEX taxon_match__one_selected_match ON taxon_match USING btree (batch, "*Name_number") WHERE ("*Selected" = 'true'::text);
|
|
| 872 | 872 |
|
| 873 | 873 |
|
| 874 | 874 |
-- |
Also available in: Unified diff
fix: inputs/.TNRS/schema.sql: taxon_match: added back * prefixes on TNRS-namespace column names