Revision 14111
Added by Aaron Marcuse-Kubitza over 10 years ago
| trunk/inputs/.TNRS/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 |
-- |
| trunk/schemas/vegbien.my.sql | ||
|---|---|---|
| 15411 | 15411 |
CREATE TABLE taxon_match ( |
| 15412 | 15412 |
batch varchar(255) NOT NULL, |
| 15413 | 15413 |
match_num int(11) NOT NULL, |
| 15414 |
`Name_number` int(11) NOT NULL, |
|
| 15415 |
`Name_submitted` varchar(255) NOT NULL, |
|
| 15416 |
`Overall_score` double, |
|
| 15417 |
`Name_matched` varchar(255), |
|
| 15418 |
`Name_matched_rank` varchar(255), |
|
| 15419 |
`Name_score` double, |
|
| 15420 |
`Name_matched_author` varchar(255), |
|
| 15421 |
`Name_matched_url` varchar(255), |
|
| 15422 |
`Author_matched` varchar(255), |
|
| 15423 |
`Author_score` double, |
|
| 15424 |
`Family_matched` varchar(255), |
|
| 15425 |
`Family_score` double, |
|
| 15426 |
`Name_matched_accepted_family` varchar(255), |
|
| 15427 |
`Genus_matched` varchar(255), |
|
| 15428 |
`Genus_score` double, |
|
| 15429 |
`Specific_epithet_matched` varchar(255), |
|
| 15430 |
`Specific_epithet_score` double, |
|
| 15431 |
`Infraspecific_rank` varchar(255), |
|
| 15432 |
`Infraspecific_epithet_matched` varchar(255), |
|
| 15433 |
`Infraspecific_epithet_score` double, |
|
| 15434 |
`Infraspecific_rank_2` varchar(255), |
|
| 15435 |
`Infraspecific_epithet_2_matched` varchar(255), |
|
| 15436 |
`Infraspecific_epithet_2_score` double, |
|
| 15437 |
`Annotations` varchar(255), |
|
| 15438 |
`Unmatched_terms` varchar(255), |
|
| 15439 |
`Taxonomic_status` varchar(255), |
|
| 15440 |
`Accepted_name` varchar(255), |
|
| 15441 |
`Accepted_name_author` varchar(255), |
|
| 15442 |
`Accepted_name_rank` varchar(255), |
|
| 15443 |
`Accepted_name_url` varchar(255), |
|
| 15444 |
`Accepted_name_species` varchar(255), |
|
| 15445 |
`Accepted_name_family` varchar(255), |
|
| 15446 |
`Selected` varchar(255), |
|
| 15447 |
`Source` varchar(255), |
|
| 15448 |
`Warnings` varchar(255), |
|
| 15449 |
`Accepted_name_lsid` varchar(255), |
|
| 15414 |
`*Name_number` int(11) NOT NULL,
|
|
| 15415 |
`*Name_submitted` varchar(255) NOT NULL,
|
|
| 15416 |
`*Overall_score` double,
|
|
| 15417 |
`*Name_matched` varchar(255),
|
|
| 15418 |
`*Name_matched_rank` varchar(255),
|
|
| 15419 |
`*Name_score` double,
|
|
| 15420 |
`*Name_matched_author` varchar(255),
|
|
| 15421 |
`*Name_matched_url` varchar(255),
|
|
| 15422 |
`*Author_matched` varchar(255),
|
|
| 15423 |
`*Author_score` double,
|
|
| 15424 |
`*Family_matched` varchar(255),
|
|
| 15425 |
`*Family_score` double,
|
|
| 15426 |
`*Name_matched_accepted_family` varchar(255),
|
|
| 15427 |
`*Genus_matched` varchar(255),
|
|
| 15428 |
`*Genus_score` double,
|
|
| 15429 |
`*Specific_epithet_matched` varchar(255),
|
|
| 15430 |
`*Specific_epithet_score` double,
|
|
| 15431 |
`*Infraspecific_rank` varchar(255),
|
|
| 15432 |
`*Infraspecific_epithet_matched` varchar(255),
|
|
| 15433 |
`*Infraspecific_epithet_score` double,
|
|
| 15434 |
`*Infraspecific_rank_2` varchar(255),
|
|
| 15435 |
`*Infraspecific_epithet_2_matched` varchar(255),
|
|
| 15436 |
`*Infraspecific_epithet_2_score` double,
|
|
| 15437 |
`*Annotations` varchar(255),
|
|
| 15438 |
`*Unmatched_terms` varchar(255),
|
|
| 15439 |
`*Taxonomic_status` varchar(255),
|
|
| 15440 |
`*Accepted_name` varchar(255),
|
|
| 15441 |
`*Accepted_name_author` varchar(255),
|
|
| 15442 |
`*Accepted_name_rank` varchar(255),
|
|
| 15443 |
`*Accepted_name_url` varchar(255),
|
|
| 15444 |
`*Accepted_name_species` varchar(255),
|
|
| 15445 |
`*Accepted_name_family` varchar(255),
|
|
| 15446 |
`*Selected` varchar(255),
|
|
| 15447 |
`*Source` varchar(255),
|
|
| 15448 |
`*Warnings` varchar(255),
|
|
| 15449 |
`*Accepted_name_lsid` varchar(255),
|
|
| 15450 | 15450 |
is_valid_match int(1) NOT NULL, |
| 15451 | 15451 |
scrubbed_unique_taxon_name varchar(255) |
| 15452 | 15452 |
); |
| ... | ... | |
| 15783 | 15783 |
-- Name: taxon_match_Name_submitted_idx; Type: INDEX; Schema: TNRS; Owner: -; Tablespace: |
| 15784 | 15784 |
-- |
| 15785 | 15785 |
|
| 15786 |
CREATE INDEX `taxon_match_Name_submitted_idx` ON taxon_match (`Name_submitted`); |
|
| 15786 |
CREATE INDEX `taxon_match_Name_submitted_idx` ON taxon_match (`*Name_submitted`);
|
|
| 15787 | 15787 |
|
| 15788 | 15788 |
|
| 15789 | 15789 |
-- |
| 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."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", |
|
| 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."Name_submitted" = 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_name_family" AS accepted_family, |
|
| 9623 |
"ValidMatchedTaxon"."Accepted_name_species" AS 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"."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));
|
|
| 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."Name_submitted" = 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"."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))
|
|
| 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_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, |
|
| 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."Name_submitted" = 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."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", |
|
| 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."Name_submitted" = 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_name_family" AS accepted_family, |
|
| 9623 |
"ValidMatchedTaxon"."Accepted_name_species" AS 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"."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));
|
|
| 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."Name_submitted" = 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"."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))
|
|
| 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_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, |
|
| 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."Name_submitted" = 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 |
|
| ... | ... | |
| 19187 | 19187 |
DECLARE |
| 19188 | 19188 |
"Specific_epithet_is_plant" boolean := |
| 19189 | 19189 |
(CASE |
| 19190 |
WHEN new."Infraspecific_epithet_matched" IS NOT NULL |
|
| 19191 |
OR new."Infraspecific_epithet_2_matched" IS NOT NULL |
|
| 19192 |
OR new."Specific_epithet_score" >= 0.9 -- fuzzy match |
|
| 19190 |
WHEN new."*Infraspecific_epithet_matched" IS NOT NULL
|
|
| 19191 |
OR new."*Infraspecific_epithet_2_matched" IS NOT NULL
|
|
| 19192 |
OR new."*Specific_epithet_score" >= 0.9 -- fuzzy match
|
|
| 19193 | 19193 |
THEN true |
| 19194 | 19194 |
ELSE NULL -- ambiguous |
| 19195 | 19195 |
END); |
| 19196 |
never_homonym boolean = COALESCE(new."Author_score" >= 0.6, false); |
|
| 19196 |
never_homonym boolean = COALESCE(new."*Author_score" >= 0.6, false);
|
|
| 19197 | 19197 |
-- author disambiguates |
| 19198 | 19198 |
family_is_homonym boolean = NOT never_homonym |
| 19199 |
AND "TNRS".family_is_homonym(new."Family_matched"); |
|
| 19199 |
AND "TNRS".family_is_homonym(new."*Family_matched");
|
|
| 19200 | 19200 |
genus_is_homonym boolean = NOT never_homonym |
| 19201 |
AND "TNRS".genus_is_homonym(new."Genus_matched"); |
|
| 19201 |
AND "TNRS".genus_is_homonym(new."*Genus_matched");
|
|
| 19202 | 19202 |
BEGIN |
| 19203 | 19203 |
/* exclude homonyms because these are not valid matches (TNRS provides a |
| 19204 | 19204 |
name, but the name is not meaningful because it is not unambiguous) */ |
| 19205 |
new.is_valid_match = new."Taxonomic_status" != 'Invalid' |
|
| 19205 |
new.is_valid_match = new."*Taxonomic_status" != 'Invalid'
|
|
| 19206 | 19206 |
AND COALESCE(CASE |
| 19207 |
WHEN new."Family_score" = 1 AND NOT family_is_homonym -- exact match |
|
| 19207 |
WHEN new."*Family_score" = 1 AND NOT family_is_homonym -- exact match
|
|
| 19208 | 19208 |
THEN true |
| 19209 | 19209 |
ELSE -- consider genus |
| 19210 | 19210 |
(CASE |
| 19211 |
WHEN new."Genus_score" = 1 -- exact match |
|
| 19211 |
WHEN new."*Genus_score" = 1 -- exact match
|
|
| 19212 | 19212 |
THEN |
| 19213 | 19213 |
(CASE |
| 19214 | 19214 |
WHEN NOT genus_is_homonym THEN true |
| 19215 | 19215 |
ELSE "Specific_epithet_is_plant" |
| 19216 | 19216 |
END) |
| 19217 |
WHEN new."Genus_score" >= 0.85 -- fuzzy match |
|
| 19217 |
WHEN new."*Genus_score" >= 0.85 -- fuzzy match
|
|
| 19218 | 19218 |
THEN "Specific_epithet_is_plant" |
| 19219 | 19219 |
ELSE NULL -- ambiguous |
| 19220 | 19220 |
END) |
| ... | ... | |
| 19223 | 19223 |
|
| 19224 | 19224 |
DECLARE |
| 19225 | 19225 |
matched_taxon_name_with_author text = NULLIF(concat_ws(' '
|
| 19226 |
, NULLIF(NULLIF(new."Name_matched_accepted_family", 'Unknown'), |
|
| 19227 |
new."Name_matched") |
|
| 19228 |
, NULLIF(new."Name_matched", 'No suitable matches found.') |
|
| 19229 |
, new."Name_matched_author" |
|
| 19226 |
, NULLIF(NULLIF(new."*Name_matched_accepted_family", 'Unknown'),
|
|
| 19227 |
new."*Name_matched")
|
|
| 19228 |
, NULLIF(new."*Name_matched", 'No suitable matches found.')
|
|
| 19229 |
, new."*Name_matched_author"
|
|
| 19230 | 19230 |
), ''); |
| 19231 | 19231 |
accepted_taxon_name_with_author text = NULLIF(concat_ws(' '
|
| 19232 |
, NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'), |
|
| 19233 |
new."Accepted_name") |
|
| 19234 |
, new."Accepted_name" |
|
| 19235 |
, new."Accepted_name_author" |
|
| 19232 |
, NULLIF(NULLIF(new."*Accepted_name_family", 'Unknown'),
|
|
| 19233 |
new."*Accepted_name")
|
|
| 19234 |
, new."*Accepted_name"
|
|
| 19235 |
, new."*Accepted_name_author"
|
|
| 19236 | 19236 |
), ''); |
| 19237 | 19237 |
BEGIN |
| 19238 | 19238 |
new.scrubbed_unique_taxon_name = COALESCE( |
| ... | ... | |
| 19374 | 19374 |
CREATE TABLE taxon_match ( |
| 19375 | 19375 |
batch text DEFAULT now() NOT NULL, |
| 19376 | 19376 |
match_num integer NOT NULL, |
| 19377 |
"Name_number" integer NOT NULL, |
|
| 19378 |
"Name_submitted" text NOT NULL, |
|
| 19379 |
"Overall_score" double precision, |
|
| 19380 |
"Name_matched" text, |
|
| 19381 |
"Name_matched_rank" text, |
|
| 19382 |
"Name_score" double precision, |
|
| 19383 |
"Name_matched_author" text, |
|
| 19384 |
"Name_matched_url" text, |
|
| 19385 |
"Author_matched" text, |
|
| 19386 |
"Author_score" double precision, |
|
| 19387 |
"Family_matched" text, |
|
| 19388 |
"Family_score" double precision, |
|
| 19389 |
"Name_matched_accepted_family" text, |
|
| 19390 |
"Genus_matched" text, |
|
| 19391 |
"Genus_score" double precision, |
|
| 19392 |
"Specific_epithet_matched" text, |
|
| 19393 |
"Specific_epithet_score" double precision, |
|
| 19394 |
"Infraspecific_rank" text, |
|
| 19395 |
"Infraspecific_epithet_matched" text, |
|
| 19396 |
"Infraspecific_epithet_score" double precision, |
|
| 19397 |
"Infraspecific_rank_2" text, |
|
| 19398 |
"Infraspecific_epithet_2_matched" text, |
|
| 19399 |
"Infraspecific_epithet_2_score" double precision, |
|
| 19400 |
"Annotations" text, |
|
| 19401 |
"Unmatched_terms" text, |
|
| 19402 |
"Taxonomic_status" text, |
|
| 19403 |
"Accepted_name" text, |
|
| 19404 |
"Accepted_name_author" text, |
|
| 19405 |
"Accepted_name_rank" text, |
|
| 19406 |
"Accepted_name_url" text, |
|
| 19407 |
"Accepted_name_species" text, |
|
| 19408 |
"Accepted_name_family" text, |
|
| 19409 |
"Selected" text, |
|
| 19410 |
"Source" text, |
|
| 19411 |
"Warnings" text, |
|
| 19412 |
"Accepted_name_lsid" text, |
|
| 19377 |
"*Name_number" integer NOT NULL,
|
|
| 19378 |
"*Name_submitted" text NOT NULL,
|
|
| 19379 |
"*Overall_score" double precision,
|
|
| 19380 |
"*Name_matched" text,
|
|
| 19381 |
"*Name_matched_rank" text,
|
|
| 19382 |
"*Name_score" double precision,
|
|
| 19383 |
"*Name_matched_author" text,
|
|
| 19384 |
"*Name_matched_url" text,
|
|
| 19385 |
"*Author_matched" text,
|
|
| 19386 |
"*Author_score" double precision,
|
|
| 19387 |
"*Family_matched" text,
|
|
| 19388 |
"*Family_score" double precision,
|
|
| 19389 |
"*Name_matched_accepted_family" text,
|
|
| 19390 |
"*Genus_matched" text,
|
|
| 19391 |
"*Genus_score" double precision,
|
|
| 19392 |
"*Specific_epithet_matched" text,
|
|
| 19393 |
"*Specific_epithet_score" double precision,
|
|
| 19394 |
"*Infraspecific_rank" text,
|
|
| 19395 |
"*Infraspecific_epithet_matched" text,
|
|
| 19396 |
"*Infraspecific_epithet_score" double precision,
|
|
| 19397 |
"*Infraspecific_rank_2" text,
|
|
| 19398 |
"*Infraspecific_epithet_2_matched" text,
|
|
| 19399 |
"*Infraspecific_epithet_2_score" double precision,
|
|
| 19400 |
"*Annotations" text,
|
|
| 19401 |
"*Unmatched_terms" text,
|
|
| 19402 |
"*Taxonomic_status" text,
|
|
| 19403 |
"*Accepted_name" text,
|
|
| 19404 |
"*Accepted_name_author" text,
|
|
| 19405 |
"*Accepted_name_rank" text,
|
|
| 19406 |
"*Accepted_name_url" text,
|
|
| 19407 |
"*Accepted_name_species" text,
|
|
| 19408 |
"*Accepted_name_family" text,
|
|
| 19409 |
"*Selected" text,
|
|
| 19410 |
"*Source" text,
|
|
| 19411 |
"*Warnings" text,
|
|
| 19412 |
"*Accepted_name_lsid" text,
|
|
| 19413 | 19413 |
is_valid_match boolean NOT NULL, |
| 19414 | 19414 |
scrubbed_unique_taxon_name text |
| 19415 | 19415 |
); |
| ... | ... | |
| 19447 | 19447 |
CREATE VIEW taxon_best_match AS |
| 19448 | 19448 |
SELECT taxon_match.batch, |
| 19449 | 19449 |
taxon_match.match_num, |
| 19450 |
taxon_match."Name_number", |
|
| 19451 |
taxon_match."Name_submitted", |
|
| 19452 |
taxon_match."Overall_score", |
|
| 19453 |
taxon_match."Name_matched", |
|
| 19454 |
taxon_match."Name_matched_rank", |
|
| 19455 |
taxon_match."Name_score", |
|
| 19456 |
taxon_match."Name_matched_author", |
|
| 19457 |
taxon_match."Name_matched_url", |
|
| 19458 |
taxon_match."Author_matched", |
|
| 19459 |
taxon_match."Author_score", |
|
| 19460 |
taxon_match."Family_matched", |
|
| 19461 |
taxon_match."Family_score", |
|
| 19462 |
taxon_match."Name_matched_accepted_family", |
|
| 19463 |
taxon_match."Genus_matched", |
|
| 19464 |
taxon_match."Genus_score", |
|
| 19465 |
taxon_match."Specific_epithet_matched", |
|
| 19466 |
taxon_match."Specific_epithet_score", |
|
| 19467 |
taxon_match."Infraspecific_rank", |
|
| 19468 |
taxon_match."Infraspecific_epithet_matched", |
|
| 19469 |
taxon_match."Infraspecific_epithet_score", |
|
| 19470 |
taxon_match."Infraspecific_rank_2", |
|
| 19471 |
taxon_match."Infraspecific_epithet_2_matched", |
|
| 19472 |
taxon_match."Infraspecific_epithet_2_score", |
|
| 19473 |
taxon_match."Annotations", |
|
| 19474 |
taxon_match."Unmatched_terms", |
|
| 19475 |
taxon_match."Taxonomic_status", |
|
| 19476 |
taxon_match."Accepted_name", |
|
| 19477 |
taxon_match."Accepted_name_author", |
|
| 19478 |
taxon_match."Accepted_name_rank", |
|
| 19479 |
taxon_match."Accepted_name_url", |
|
| 19480 |
taxon_match."Accepted_name_species", |
|
| 19481 |
taxon_match."Accepted_name_family", |
|
| 19482 |
taxon_match."Selected", |
|
| 19483 |
taxon_match."Source", |
|
| 19484 |
taxon_match."Warnings", |
|
| 19485 |
taxon_match."Accepted_name_lsid", |
|
| 19450 |
taxon_match."*Name_number",
|
|
| 19451 |
taxon_match."*Name_submitted",
|
|
| 19452 |
taxon_match."*Overall_score",
|
|
| 19453 |
taxon_match."*Name_matched",
|
|
| 19454 |
taxon_match."*Name_matched_rank",
|
|
| 19455 |
taxon_match."*Name_score",
|
|
| 19456 |
taxon_match."*Name_matched_author",
|
|
| 19457 |
taxon_match."*Name_matched_url",
|
|
| 19458 |
taxon_match."*Author_matched",
|
|
| 19459 |
taxon_match."*Author_score",
|
|
| 19460 |
taxon_match."*Family_matched",
|
|
| 19461 |
taxon_match."*Family_score",
|
|
| 19462 |
taxon_match."*Name_matched_accepted_family",
|
|
| 19463 |
taxon_match."*Genus_matched",
|
|
| 19464 |
taxon_match."*Genus_score",
|
|
| 19465 |
taxon_match."*Specific_epithet_matched",
|
|
| 19466 |
taxon_match."*Specific_epithet_score",
|
|
| 19467 |
taxon_match."*Infraspecific_rank",
|
|
| 19468 |
taxon_match."*Infraspecific_epithet_matched",
|
|
| 19469 |
taxon_match."*Infraspecific_epithet_score",
|
|
| 19470 |
taxon_match."*Infraspecific_rank_2",
|
|
| 19471 |
taxon_match."*Infraspecific_epithet_2_matched",
|
|
| 19472 |
taxon_match."*Infraspecific_epithet_2_score",
|
|
| 19473 |
taxon_match."*Annotations",
|
|
| 19474 |
taxon_match."*Unmatched_terms",
|
|
| 19475 |
taxon_match."*Taxonomic_status",
|
|
| 19476 |
taxon_match."*Accepted_name",
|
|
| 19477 |
taxon_match."*Accepted_name_author",
|
|
| 19478 |
taxon_match."*Accepted_name_rank",
|
|
| 19479 |
taxon_match."*Accepted_name_url",
|
|
| 19480 |
taxon_match."*Accepted_name_species",
|
|
| 19481 |
taxon_match."*Accepted_name_family",
|
|
| 19482 |
taxon_match."*Selected",
|
|
| 19483 |
taxon_match."*Source",
|
|
| 19484 |
taxon_match."*Warnings",
|
|
| 19485 |
taxon_match."*Accepted_name_lsid",
|
|
| 19486 | 19486 |
taxon_match.is_valid_match, |
| 19487 | 19487 |
taxon_match.scrubbed_unique_taxon_name |
| 19488 | 19488 |
FROM taxon_match |
| 19489 |
WHERE (taxon_match."Selected" = 'true'::text); |
|
| 19489 |
WHERE (taxon_match."*Selected" = 'true'::text);
|
|
| 19490 | 19490 |
|
| 19491 | 19491 |
|
| 19492 | 19492 |
-- |
| ... | ... | |
| 19508 | 19508 |
CREATE VIEW "MatchedTaxon" AS |
| 19509 | 19509 |
SELECT taxon_best_match.batch, |
| 19510 | 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", |
|
| 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 | 19547 |
taxon_best_match.is_valid_match, |
| 19548 | 19548 |
taxon_best_match.scrubbed_unique_taxon_name, |
| 19549 |
map_taxonomic_status(taxon_best_match."Taxonomic_status", taxon_best_match."Accepted_name") AS "taxonomicStatus",
|
|
| 19549 |
map_taxonomic_status(taxon_best_match."*Taxonomic_status", taxon_best_match."*Accepted_name") AS "taxonomicStatus",
|
|
| 19550 | 19550 |
CASE |
| 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" |
|
| 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 | 19555 |
FROM taxon_best_match; |
| 19556 | 19556 |
|
| ... | ... | |
| 19574 | 19574 |
CREATE VIEW "ValidMatchedTaxon" AS |
| 19575 | 19575 |
SELECT "MatchedTaxon".batch, |
| 19576 | 19576 |
"MatchedTaxon".match_num, |
| 19577 |
"MatchedTaxon"."Name_number", |
|
| 19578 |
"MatchedTaxon"."Name_submitted", |
|
| 19579 |
"MatchedTaxon"."Overall_score", |
|
| 19580 |
"MatchedTaxon"."Name_matched", |
|
| 19581 |
"MatchedTaxon"."Name_matched_rank", |
|
| 19582 |
"MatchedTaxon"."Name_score", |
|
| 19583 |
"MatchedTaxon"."Name_matched_author", |
|
| 19584 |
"MatchedTaxon"."Name_matched_url", |
|
| 19585 |
"MatchedTaxon"."Author_matched", |
|
| 19586 |
"MatchedTaxon"."Author_score", |
|
| 19587 |
"MatchedTaxon"."Family_matched", |
|
| 19588 |
"MatchedTaxon"."Family_score", |
|
| 19589 |
"MatchedTaxon"."Name_matched_accepted_family", |
|
| 19590 |
"MatchedTaxon"."Genus_matched", |
|
| 19591 |
"MatchedTaxon"."Genus_score", |
|
| 19592 |
"MatchedTaxon"."Specific_epithet_matched", |
|
| 19593 |
"MatchedTaxon"."Specific_epithet_score", |
|
| 19594 |
"MatchedTaxon"."Infraspecific_rank", |
|
| 19595 |
"MatchedTaxon"."Infraspecific_epithet_matched", |
|
| 19596 |
"MatchedTaxon"."Infraspecific_epithet_score", |
|
| 19597 |
"MatchedTaxon"."Infraspecific_rank_2", |
|
| 19598 |
"MatchedTaxon"."Infraspecific_epithet_2_matched", |
|
| 19599 |
"MatchedTaxon"."Infraspecific_epithet_2_score", |
|
| 19600 |
"MatchedTaxon"."Annotations", |
|
| 19601 |
"MatchedTaxon"."Unmatched_terms", |
|
| 19602 |
"MatchedTaxon"."Taxonomic_status", |
|
| 19603 |
"MatchedTaxon"."Accepted_name", |
|
| 19604 |
"MatchedTaxon"."Accepted_name_author", |
|
| 19605 |
"MatchedTaxon"."Accepted_name_rank", |
|
| 19606 |
"MatchedTaxon"."Accepted_name_url", |
|
| 19607 |
"MatchedTaxon"."Accepted_name_species", |
|
| 19608 |
"MatchedTaxon"."Accepted_name_family", |
|
| 19609 |
"MatchedTaxon"."Selected", |
|
| 19610 |
"MatchedTaxon"."Source", |
|
| 19611 |
"MatchedTaxon"."Warnings", |
|
| 19612 |
"MatchedTaxon"."Accepted_name_lsid", |
|
| 19577 |
"MatchedTaxon"."*Name_number",
|
|
| 19578 |
"MatchedTaxon"."*Name_submitted",
|
|
| 19579 |
"MatchedTaxon"."*Overall_score",
|
|
| 19580 |
"MatchedTaxon"."*Name_matched",
|
|
| 19581 |
"MatchedTaxon"."*Name_matched_rank",
|
|
| 19582 |
"MatchedTaxon"."*Name_score",
|
|
| 19583 |
"MatchedTaxon"."*Name_matched_author",
|
|
| 19584 |
"MatchedTaxon"."*Name_matched_url",
|
|
| 19585 |
"MatchedTaxon"."*Author_matched",
|
|
| 19586 |
"MatchedTaxon"."*Author_score",
|
|
| 19587 |
"MatchedTaxon"."*Family_matched",
|
|
| 19588 |
"MatchedTaxon"."*Family_score",
|
|
| 19589 |
"MatchedTaxon"."*Name_matched_accepted_family",
|
|
| 19590 |
"MatchedTaxon"."*Genus_matched",
|
|
| 19591 |
"MatchedTaxon"."*Genus_score",
|
|
| 19592 |
"MatchedTaxon"."*Specific_epithet_matched",
|
|
| 19593 |
"MatchedTaxon"."*Specific_epithet_score",
|
|
| 19594 |
"MatchedTaxon"."*Infraspecific_rank",
|
|
| 19595 |
"MatchedTaxon"."*Infraspecific_epithet_matched",
|
|
| 19596 |
"MatchedTaxon"."*Infraspecific_epithet_score",
|
|
| 19597 |
"MatchedTaxon"."*Infraspecific_rank_2",
|
|
| 19598 |
"MatchedTaxon"."*Infraspecific_epithet_2_matched",
|
|
| 19599 |
"MatchedTaxon"."*Infraspecific_epithet_2_score",
|
|
| 19600 |
"MatchedTaxon"."*Annotations",
|
|
| 19601 |
"MatchedTaxon"."*Unmatched_terms",
|
|
| 19602 |
"MatchedTaxon"."*Taxonomic_status",
|
|
| 19603 |
"MatchedTaxon"."*Accepted_name",
|
|
| 19604 |
"MatchedTaxon"."*Accepted_name_author",
|
|
| 19605 |
"MatchedTaxon"."*Accepted_name_rank",
|
|
| 19606 |
"MatchedTaxon"."*Accepted_name_url",
|
|
| 19607 |
"MatchedTaxon"."*Accepted_name_species",
|
|
| 19608 |
"MatchedTaxon"."*Accepted_name_family",
|
|
| 19609 |
"MatchedTaxon"."*Selected",
|
|
| 19610 |
"MatchedTaxon"."*Source",
|
|
| 19611 |
"MatchedTaxon"."*Warnings",
|
|
| 19612 |
"MatchedTaxon"."*Accepted_name_lsid",
|
|
| 19613 | 19613 |
"MatchedTaxon".is_valid_match, |
| 19614 | 19614 |
"MatchedTaxon".scrubbed_unique_taxon_name, |
| 19615 | 19615 |
"MatchedTaxon"."taxonomicStatus", |
| ... | ... | |
| 19723 | 19723 |
-- |
| 19724 | 19724 |
|
| 19725 | 19725 |
CREATE VIEW taxon_match_input AS |
| 19726 |
SELECT taxon_match."Name_number", |
|
| 19727 |
taxon_match."Name_submitted", |
|
| 19728 |
taxon_match."Overall_score", |
|
| 19729 |
taxon_match."Name_matched", |
|
| 19730 |
taxon_match."Name_matched_rank", |
|
| 19731 |
taxon_match."Name_score", |
|
| 19732 |
taxon_match."Name_matched_author", |
|
| 19733 |
taxon_match."Name_matched_url", |
|
| 19734 |
taxon_match."Author_matched", |
|
| 19735 |
taxon_match."Author_score", |
|
| 19736 |
taxon_match."Family_matched", |
|
| 19737 |
taxon_match."Family_score", |
|
| 19738 |
taxon_match."Name_matched_accepted_family", |
|
| 19739 |
taxon_match."Genus_matched", |
|
| 19740 |
taxon_match."Genus_score", |
|
| 19741 |
taxon_match."Specific_epithet_matched", |
|
| 19742 |
taxon_match."Specific_epithet_score", |
|
| 19743 |
taxon_match."Infraspecific_rank", |
|
| 19744 |
taxon_match."Infraspecific_epithet_matched", |
|
| 19745 |
taxon_match."Infraspecific_epithet_score", |
|
| 19746 |
taxon_match."Infraspecific_rank_2", |
|
| 19747 |
taxon_match."Infraspecific_epithet_2_matched", |
|
| 19748 |
taxon_match."Infraspecific_epithet_2_score", |
|
| 19749 |
taxon_match."Annotations", |
|
| 19750 |
taxon_match."Unmatched_terms", |
|
| 19751 |
taxon_match."Taxonomic_status", |
|
| 19752 |
taxon_match."Accepted_name", |
|
| 19753 |
taxon_match."Accepted_name_author", |
|
| 19754 |
taxon_match."Accepted_name_rank", |
|
| 19755 |
taxon_match."Accepted_name_url", |
|
| 19756 |
taxon_match."Accepted_name_species", |
|
| 19757 |
taxon_match."Accepted_name_family", |
|
| 19758 |
taxon_match."Selected", |
|
| 19759 |
taxon_match."Source", |
|
| 19760 |
taxon_match."Warnings", |
|
| 19761 |
taxon_match."Accepted_name_lsid" |
|
| 19726 |
SELECT taxon_match."*Name_number" AS "Name_number",
|
|
| 19727 |
taxon_match."*Name_submitted" AS "Name_submitted",
|
|
| 19728 |
taxon_match."*Overall_score" AS "Overall_score",
|
|
| 19729 |
taxon_match."*Name_matched" AS "Name_matched",
|
|
| 19730 |
taxon_match."*Name_matched_rank" AS "Name_matched_rank",
|
|
| 19731 |
taxon_match."*Name_score" AS "Name_score",
|
|
| 19732 |
taxon_match."*Name_matched_author" AS "Name_matched_author",
|
|
| 19733 |
taxon_match."*Name_matched_url" AS "Name_matched_url",
|
|
| 19734 |
taxon_match."*Author_matched" AS "Author_matched",
|
|
| 19735 |
taxon_match."*Author_score" AS "Author_score",
|
|
| 19736 |
taxon_match."*Family_matched" AS "Family_matched",
|
|
| 19737 |
taxon_match."*Family_score" AS "Family_score",
|
|
| 19738 |
taxon_match."*Name_matched_accepted_family" AS "Name_matched_accepted_family",
|
|
| 19739 |
taxon_match."*Genus_matched" AS "Genus_matched",
|
|
| 19740 |
taxon_match."*Genus_score" AS "Genus_score",
|
|
| 19741 |
taxon_match."*Specific_epithet_matched" AS "Specific_epithet_matched",
|
|
| 19742 |
taxon_match."*Specific_epithet_score" AS "Specific_epithet_score",
|
|
| 19743 |
taxon_match."*Infraspecific_rank" AS "Infraspecific_rank",
|
|
| 19744 |
taxon_match."*Infraspecific_epithet_matched" AS "Infraspecific_epithet_matched",
|
|
| 19745 |
taxon_match."*Infraspecific_epithet_score" AS "Infraspecific_epithet_score",
|
|
| 19746 |
taxon_match."*Infraspecific_rank_2" AS "Infraspecific_rank_2",
|
|
| 19747 |
taxon_match."*Infraspecific_epithet_2_matched" AS "Infraspecific_epithet_2_matched",
|
|
| 19748 |
taxon_match."*Infraspecific_epithet_2_score" AS "Infraspecific_epithet_2_score",
|
|
| 19749 |
taxon_match."*Annotations" AS "Annotations",
|
|
| 19750 |
taxon_match."*Unmatched_terms" AS "Unmatched_terms",
|
|
| 19751 |
taxon_match."*Taxonomic_status" AS "Taxonomic_status",
|
|
| 19752 |
taxon_match."*Accepted_name" AS "Accepted_name",
|
|
| 19753 |
taxon_match."*Accepted_name_author" AS "Accepted_name_author",
|
|
| 19754 |
taxon_match."*Accepted_name_rank" AS "Accepted_name_rank",
|
|
| 19755 |
taxon_match."*Accepted_name_url" AS "Accepted_name_url",
|
|
| 19756 |
taxon_match."*Accepted_name_species" AS "Accepted_name_species",
|
|
| 19757 |
taxon_match."*Accepted_name_family" AS "Accepted_name_family",
|
|
| 19758 |
taxon_match."*Selected" AS "Selected",
|
|
| 19759 |
taxon_match."*Source" AS "Source",
|
|
| 19760 |
taxon_match."*Warnings" AS "Warnings",
|
|
| 19761 |
taxon_match."*Accepted_name_lsid" AS "Accepted_name_lsid"
|
|
| 19762 | 19762 |
FROM taxon_match; |
| 19763 | 19763 |
|
| 19764 | 19764 |
|
| ... | ... | |
| 19811 | 19811 |
-- |
| 19812 | 19812 |
|
| 19813 | 19813 |
CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS |
| 19814 |
SELECT taxon_match."Name_submitted" AS scrubbed_unique_taxon_name, |
|
| 19815 |
taxon_match."Name_matched_rank" AS scrubbed_taxon_rank, |
|
| 19816 |
COALESCE(taxon_match."Name_matched_accepted_family", taxon_match."Family_matched") AS scrubbed_family,
|
|
| 19817 |
taxon_match."Genus_matched" AS scrubbed_genus, |
|
| 19818 |
taxon_match."Specific_epithet_matched" AS scrubbed_specific_epithet, |
|
| 19819 |
taxon_match."Infraspecific_rank" AS scrubbed_infraspecific_rank, |
|
| 19820 |
taxon_match."Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet, |
|
| 19821 |
taxon_match."Name_matched_author" AS scrubbed_author, |
|
| 19822 |
taxon_match."Name_matched" AS scrubbed_taxon_name_no_author, |
|
| 19823 |
(taxon_match."Name_matched" || COALESCE((' '::text || taxon_match."Name_matched_author"), ''::text)) AS scrubbed_taxon_name_with_author
|
|
| 19814 |
SELECT taxon_match."*Name_submitted" AS scrubbed_unique_taxon_name,
|
|
| 19815 |
taxon_match."*Name_matched_rank" AS scrubbed_taxon_rank,
|
|
| 19816 |
COALESCE(taxon_match."*Name_matched_accepted_family", taxon_match."*Family_matched") AS scrubbed_family,
|
|
| 19817 |
taxon_match."*Genus_matched" AS scrubbed_genus,
|
|
| 19818 |
taxon_match."*Specific_epithet_matched" AS scrubbed_specific_epithet,
|
|
| 19819 |
taxon_match."*Infraspecific_rank" AS scrubbed_infraspecific_rank,
|
|
| 19820 |
taxon_match."*Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet,
|
|
| 19821 |
taxon_match."*Name_matched_author" AS scrubbed_author,
|
|
| 19822 |
taxon_match."*Name_matched" AS scrubbed_taxon_name_no_author,
|
|
| 19823 |
(taxon_match."*Name_matched" || COALESCE((' '::text || taxon_match."*Name_matched_author"), ''::text)) AS scrubbed_taxon_name_with_author
|
|
| 19824 | 19824 |
FROM taxon_match; |
| 19825 | 19825 |
|
| 19826 | 19826 |
|
| ... | ... | |
| 19846 | 19846 |
SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name, |
| 19847 | 19847 |
"ValidMatchedTaxon".batch, |
| 19848 | 19848 |
"ValidMatchedTaxon".match_num, |
| 19849 |
"ValidMatchedTaxon"."Name_number", |
|
| 19850 |
"ValidMatchedTaxon"."Name_submitted", |
|
| 19851 |
"ValidMatchedTaxon"."Overall_score", |
|
| 19852 |
"ValidMatchedTaxon"."Name_matched", |
|
| 19853 |
"ValidMatchedTaxon"."Name_matched_rank", |
|
| 19854 |
"ValidMatchedTaxon"."Name_score", |
|
| 19855 |
"ValidMatchedTaxon"."Name_matched_author", |
|
| 19856 |
"ValidMatchedTaxon"."Name_matched_url", |
|
| 19857 |
"ValidMatchedTaxon"."Author_matched", |
|
| 19858 |
"ValidMatchedTaxon"."Author_score", |
|
| 19859 |
"ValidMatchedTaxon"."Family_matched", |
|
| 19860 |
"ValidMatchedTaxon"."Family_score", |
|
| 19861 |
"ValidMatchedTaxon"."Name_matched_accepted_family", |
|
| 19862 |
"ValidMatchedTaxon"."Genus_matched", |
|
| 19863 |
"ValidMatchedTaxon"."Genus_score", |
|
| 19864 |
"ValidMatchedTaxon"."Specific_epithet_matched", |
|
| 19865 |
"ValidMatchedTaxon"."Specific_epithet_score", |
|
| 19866 |
"ValidMatchedTaxon"."Infraspecific_rank", |
|
| 19867 |
"ValidMatchedTaxon"."Infraspecific_epithet_matched", |
|
| 19868 |
"ValidMatchedTaxon"."Infraspecific_epithet_score", |
|
| 19869 |
"ValidMatchedTaxon"."Infraspecific_rank_2", |
|
| 19870 |
"ValidMatchedTaxon"."Infraspecific_epithet_2_matched", |
|
| 19871 |
"ValidMatchedTaxon"."Infraspecific_epithet_2_score", |
|
| 19872 |
"ValidMatchedTaxon"."Annotations", |
|
| 19873 |
"ValidMatchedTaxon"."Unmatched_terms", |
|
| 19874 |
"ValidMatchedTaxon"."Taxonomic_status", |
|
| 19875 |
"ValidMatchedTaxon"."Accepted_name", |
|
| 19876 |
"ValidMatchedTaxon"."Accepted_name_author", |
|
| 19877 |
"ValidMatchedTaxon"."Accepted_name_rank", |
|
| 19878 |
"ValidMatchedTaxon"."Accepted_name_url", |
|
| 19879 |
"ValidMatchedTaxon"."Accepted_name_species", |
|
| 19880 |
"ValidMatchedTaxon"."Accepted_name_family", |
|
| 19881 |
"ValidMatchedTaxon"."Selected", |
|
| 19882 |
"ValidMatchedTaxon"."Source", |
|
| 19883 |
"ValidMatchedTaxon"."Warnings", |
|
| 19884 |
"ValidMatchedTaxon"."Accepted_name_lsid", |
|
| 19849 |
"ValidMatchedTaxon"."*Name_number",
|
|
| 19850 |
"ValidMatchedTaxon"."*Name_submitted",
|
|
| 19851 |
"ValidMatchedTaxon"."*Overall_score",
|
|
| 19852 |
"ValidMatchedTaxon"."*Name_matched",
|
|
| 19853 |
"ValidMatchedTaxon"."*Name_matched_rank",
|
|
| 19854 |
"ValidMatchedTaxon"."*Name_score",
|
|
| 19855 |
"ValidMatchedTaxon"."*Name_matched_author",
|
|
| 19856 |
"ValidMatchedTaxon"."*Name_matched_url",
|
|
| 19857 |
"ValidMatchedTaxon"."*Author_matched",
|
|
| 19858 |
"ValidMatchedTaxon"."*Author_score",
|
|
| 19859 |
"ValidMatchedTaxon"."*Family_matched",
|
|
| 19860 |
"ValidMatchedTaxon"."*Family_score",
|
|
| 19861 |
"ValidMatchedTaxon"."*Name_matched_accepted_family",
|
|
| 19862 |
"ValidMatchedTaxon"."*Genus_matched",
|
|
| 19863 |
"ValidMatchedTaxon"."*Genus_score",
|
|
| 19864 |
"ValidMatchedTaxon"."*Specific_epithet_matched",
|
|
| 19865 |
"ValidMatchedTaxon"."*Specific_epithet_score",
|
|
| 19866 |
"ValidMatchedTaxon"."*Infraspecific_rank",
|
|
| 19867 |
"ValidMatchedTaxon"."*Infraspecific_epithet_matched",
|
|
| 19868 |
"ValidMatchedTaxon"."*Infraspecific_epithet_score",
|
|
| 19869 |
"ValidMatchedTaxon"."*Infraspecific_rank_2",
|
|
| 19870 |
"ValidMatchedTaxon"."*Infraspecific_epithet_2_matched",
|
|
| 19871 |
"ValidMatchedTaxon"."*Infraspecific_epithet_2_score",
|
|
| 19872 |
"ValidMatchedTaxon"."*Annotations",
|
|
| 19873 |
"ValidMatchedTaxon"."*Unmatched_terms",
|
|
| 19874 |
"ValidMatchedTaxon"."*Taxonomic_status",
|
|
| 19875 |
"ValidMatchedTaxon"."*Accepted_name",
|
|
| 19876 |
"ValidMatchedTaxon"."*Accepted_name_author",
|
|
| 19877 |
"ValidMatchedTaxon"."*Accepted_name_rank",
|
|
| 19878 |
"ValidMatchedTaxon"."*Accepted_name_url",
|
|
| 19879 |
"ValidMatchedTaxon"."*Accepted_name_species",
|
|
| 19880 |
"ValidMatchedTaxon"."*Accepted_name_family",
|
|
| 19881 |
"ValidMatchedTaxon"."*Selected",
|
|
| 19882 |
"ValidMatchedTaxon"."*Source",
|
|
| 19883 |
"ValidMatchedTaxon"."*Warnings",
|
|
| 19884 |
"ValidMatchedTaxon"."*Accepted_name_lsid",
|
|
| 19885 | 19885 |
"ValidMatchedTaxon".is_valid_match, |
| 19886 | 19886 |
"ValidMatchedTaxon"."taxonomicStatus", |
| 19887 | 19887 |
"ValidMatchedTaxon".accepted_morphospecies_binomial, |
| ... | ... | |
| 19895 | 19895 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, |
| 19896 | 19896 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_with_author, |
| 19897 | 19897 |
CASE |
| 19898 |
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")
|
|
| 19899 |
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")
|
|
| 19898 |
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")
|
|
| 19899 |
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")
|
|
| 19900 | 19900 |
ELSE (("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus || ' '::text) || "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet)
|
| 19901 | 19901 |
END AS scrubbed_morphospecies_binomial |
| 19902 | 19902 |
FROM ("ValidMatchedTaxon"
|
| ... | ... | |
| 20032 | 20032 |
-- Name: taxon_best_match__valid_match; Type: INDEX; Schema: TNRS; Owner: -; Tablespace: |
| 20033 | 20033 |
-- |
| 20034 | 20034 |
|
| 20035 |
CREATE INDEX taxon_best_match__valid_match ON taxon_match USING btree ("Name_submitted") WHERE (("Selected" = 'true'::text) AND is_valid_match);
|
|
| 20035 |
CREATE INDEX taxon_best_match__valid_match ON taxon_match USING btree ("*Name_submitted") WHERE (("*Selected" = 'true'::text) AND is_valid_match);
|
|
| 20036 | 20036 |
|
| 20037 | 20037 |
|
| 20038 | 20038 |
-- |
| 20039 | 20039 |
-- Name: taxon_match_Name_submitted_idx; Type: INDEX; Schema: TNRS; Owner: -; Tablespace: |
| 20040 | 20040 |
-- |
| 20041 | 20041 |
|
| 20042 |
CREATE INDEX "taxon_match_Name_submitted_idx" ON taxon_match USING btree ("Name_submitted");
|
|
| 20042 |
CREATE INDEX "taxon_match_Name_submitted_idx" ON taxon_match USING btree ("*Name_submitted");
|
|
| 20043 | 20043 |
|
| 20044 | 20044 |
|
| 20045 | 20045 |
-- |
| 20046 | 20046 |
-- Name: taxon_match__one_selected_match; Type: INDEX; Schema: TNRS; Owner: -; Tablespace: |
| 20047 | 20047 |
-- |
| 20048 | 20048 |
|
Also available in: Unified diff
fix: inputs/.TNRS/schema.sql: taxon_match: added back * prefixes on TNRS-namespace column names