| 3 |
3 |
--
|
| 4 |
4 |
|
| 5 |
5 |
SET statement_timeout = 0;
|
|
6 |
SET lock_timeout = 0;
|
| 6 |
7 |
SET client_encoding = 'UTF8';
|
| 7 |
8 |
SET standard_conforming_strings = on;
|
| 8 |
9 |
SET check_function_bodies = false;
|
| ... | ... | |
| 211 |
212 |
--
|
| 212 |
213 |
|
| 213 |
214 |
CREATE VIEW "MatchedTaxon" AS
|
| 214 |
|
SELECT tnrs.batch AS "*Name_matched.batch", tnrs."Name_submitted" AS "concatenatedScientificName", tnrs."Name_matched" AS "matchedTaxonName", tnrs."Name_matched_rank" AS "matchedTaxonRank", tnrs."Name_score" AS "*Name_matched.Name_score", tnrs."Name_matched_author" AS "matchedScientificNameAuthorship", tnrs."Name_matched_url" AS "matchedScientificNameID", tnrs."Author_score" AS "*Name_matched.Author_score", tnrs."Family_score" AS "matchedFamilyConfidence_fraction", COALESCE(tnrs."Name_matched_accepted_family", tnrs."Accepted_name_family") AS "matchedFamily", tnrs."Genus_matched" AS "matchedGenus", tnrs."Genus_score" AS "matchedGenusConfidence_fraction", tnrs."Specific_epithet_matched" AS "matchedSpecificEpithet", tnrs."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction", tnrs."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet", tnrs."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score", tnrs."Annotations" AS "identificationQualifier", tnrs."Unmatched_terms" AS "morphospeciesSuffix", tnrs."Taxonomic_status" AS "taxonomicStatus", tnrs."Accepted_name" AS "acceptedTaxonName", tnrs."Accepted_name_author" AS "acceptedScientificNameAuthorship", tnrs."Accepted_name_rank" AS "acceptedTaxonRank", tnrs."Accepted_name_url" AS "acceptedScientificNameID", tnrs."Accepted_name_species" AS "*Name_matched.Accepted_name_species", tnrs."Accepted_name_family" AS "acceptedFamily", tnrs."Selected" AS "*Name_matched.Selected", tnrs."Source" AS "*Name_matched.Source", tnrs."Warnings" AS "*Name_matched.Warnings", tnrs."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid", tnrs.is_valid_match AS taxon_scrub__is_valid_match, tnrs.scrubbed_unique_taxon_name FROM tnrs;
|
|
215 |
SELECT tnrs.batch AS "*Name_matched.batch",
|
|
216 |
tnrs."Name_submitted" AS "concatenatedScientificName",
|
|
217 |
tnrs."Name_matched" AS "matchedTaxonName",
|
|
218 |
tnrs."Name_matched_rank" AS "matchedTaxonRank",
|
|
219 |
tnrs."Name_score" AS "*Name_matched.Name_score",
|
|
220 |
tnrs."Name_matched_author" AS "matchedScientificNameAuthorship",
|
|
221 |
tnrs."Name_matched_url" AS "matchedScientificNameID",
|
|
222 |
tnrs."Author_score" AS "*Name_matched.Author_score",
|
|
223 |
tnrs."Family_score" AS "matchedFamilyConfidence_fraction",
|
|
224 |
COALESCE(tnrs."Name_matched_accepted_family", tnrs."Accepted_name_family") AS "matchedFamily",
|
|
225 |
tnrs."Genus_matched" AS "matchedGenus",
|
|
226 |
tnrs."Genus_score" AS "matchedGenusConfidence_fraction",
|
|
227 |
tnrs."Specific_epithet_matched" AS "matchedSpecificEpithet",
|
|
228 |
tnrs."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction",
|
|
229 |
tnrs."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet",
|
|
230 |
tnrs."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score",
|
|
231 |
tnrs."Annotations" AS "identificationQualifier",
|
|
232 |
tnrs."Unmatched_terms" AS "morphospeciesSuffix",
|
|
233 |
tnrs."Taxonomic_status" AS "taxonomicStatus",
|
|
234 |
tnrs."Accepted_name" AS "acceptedTaxonName",
|
|
235 |
tnrs."Accepted_name_author" AS "acceptedScientificNameAuthorship",
|
|
236 |
tnrs."Accepted_name_rank" AS "acceptedTaxonRank",
|
|
237 |
tnrs."Accepted_name_url" AS "acceptedScientificNameID",
|
|
238 |
tnrs."Accepted_name_species" AS "*Name_matched.Accepted_name_species",
|
|
239 |
tnrs."Accepted_name_family" AS "acceptedFamily",
|
|
240 |
tnrs."Selected" AS "*Name_matched.Selected",
|
|
241 |
tnrs."Source" AS "*Name_matched.Source",
|
|
242 |
tnrs."Warnings" AS "*Name_matched.Warnings",
|
|
243 |
tnrs."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid",
|
|
244 |
tnrs.is_valid_match AS taxon_scrub__is_valid_match,
|
|
245 |
tnrs.scrubbed_unique_taxon_name
|
|
246 |
FROM tnrs;
|
| 215 |
247 |
|
| 216 |
248 |
|
| 217 |
249 |
--
|
| ... | ... | |
| 219 |
251 |
--
|
| 220 |
252 |
|
| 221 |
253 |
CREATE VIEW "ValidMatchedTaxon" AS
|
| 222 |
|
SELECT "MatchedTaxon"."*Name_matched.batch", "MatchedTaxon"."concatenatedScientificName", "MatchedTaxon"."matchedTaxonName", "MatchedTaxon"."matchedTaxonRank", "MatchedTaxon"."*Name_matched.Name_score", "MatchedTaxon"."matchedScientificNameAuthorship", "MatchedTaxon"."matchedScientificNameID", "MatchedTaxon"."*Name_matched.Author_score", "MatchedTaxon"."matchedFamilyConfidence_fraction", "MatchedTaxon"."matchedFamily", "MatchedTaxon"."matchedGenus", "MatchedTaxon"."matchedGenusConfidence_fraction", "MatchedTaxon"."matchedSpecificEpithet", "MatchedTaxon"."matchedSpeciesConfidence_fraction", "MatchedTaxon"."matchedInfraspecificEpithet", "MatchedTaxon"."*Name_matched.Infraspecific_epithet_score", "MatchedTaxon"."identificationQualifier", "MatchedTaxon"."morphospeciesSuffix", "MatchedTaxon"."taxonomicStatus", "MatchedTaxon"."acceptedTaxonName", "MatchedTaxon"."acceptedScientificNameAuthorship", "MatchedTaxon"."acceptedTaxonRank", "MatchedTaxon"."acceptedScientificNameID", "MatchedTaxon"."*Name_matched.Accepted_name_species", "MatchedTaxon"."acceptedFamily", "MatchedTaxon"."*Name_matched.Selected", "MatchedTaxon"."*Name_matched.Source", "MatchedTaxon"."*Name_matched.Warnings", "MatchedTaxon"."*Name_matched.Accepted_name_lsid", "MatchedTaxon".taxon_scrub__is_valid_match, "MatchedTaxon".scrubbed_unique_taxon_name FROM "MatchedTaxon" WHERE "MatchedTaxon".taxon_scrub__is_valid_match;
|
|
254 |
SELECT "MatchedTaxon"."*Name_matched.batch",
|
|
255 |
"MatchedTaxon"."concatenatedScientificName",
|
|
256 |
"MatchedTaxon"."matchedTaxonName",
|
|
257 |
"MatchedTaxon"."matchedTaxonRank",
|
|
258 |
"MatchedTaxon"."*Name_matched.Name_score",
|
|
259 |
"MatchedTaxon"."matchedScientificNameAuthorship",
|
|
260 |
"MatchedTaxon"."matchedScientificNameID",
|
|
261 |
"MatchedTaxon"."*Name_matched.Author_score",
|
|
262 |
"MatchedTaxon"."matchedFamilyConfidence_fraction",
|
|
263 |
"MatchedTaxon"."matchedFamily",
|
|
264 |
"MatchedTaxon"."matchedGenus",
|
|
265 |
"MatchedTaxon"."matchedGenusConfidence_fraction",
|
|
266 |
"MatchedTaxon"."matchedSpecificEpithet",
|
|
267 |
"MatchedTaxon"."matchedSpeciesConfidence_fraction",
|
|
268 |
"MatchedTaxon"."matchedInfraspecificEpithet",
|
|
269 |
"MatchedTaxon"."*Name_matched.Infraspecific_epithet_score",
|
|
270 |
"MatchedTaxon"."identificationQualifier",
|
|
271 |
"MatchedTaxon"."morphospeciesSuffix",
|
|
272 |
"MatchedTaxon"."taxonomicStatus",
|
|
273 |
"MatchedTaxon"."acceptedTaxonName",
|
|
274 |
"MatchedTaxon"."acceptedScientificNameAuthorship",
|
|
275 |
"MatchedTaxon"."acceptedTaxonRank",
|
|
276 |
"MatchedTaxon"."acceptedScientificNameID",
|
|
277 |
"MatchedTaxon"."*Name_matched.Accepted_name_species",
|
|
278 |
"MatchedTaxon"."acceptedFamily",
|
|
279 |
"MatchedTaxon"."*Name_matched.Selected",
|
|
280 |
"MatchedTaxon"."*Name_matched.Source",
|
|
281 |
"MatchedTaxon"."*Name_matched.Warnings",
|
|
282 |
"MatchedTaxon"."*Name_matched.Accepted_name_lsid",
|
|
283 |
"MatchedTaxon".taxon_scrub__is_valid_match,
|
|
284 |
"MatchedTaxon".scrubbed_unique_taxon_name
|
|
285 |
FROM "MatchedTaxon"
|
|
286 |
WHERE "MatchedTaxon".taxon_scrub__is_valid_match;
|
| 223 |
287 |
|
| 224 |
288 |
|
| 225 |
289 |
--
|
| ... | ... | |
| 315 |
379 |
--
|
| 316 |
380 |
|
| 317 |
381 |
CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS
|
| 318 |
|
SELECT tnrs."Name_submitted" AS scrubbed_unique_taxon_name, tnrs."Name_matched_rank" AS scrubbed_taxon_rank, tnrs."Name_matched_accepted_family" AS scrubbed_family, tnrs."Genus_matched" AS scrubbed_genus, tnrs."Specific_epithet_matched" AS scrubbed_specific_epithet, tnrs."Infraspecific_rank" AS scrubbed_infraspecific_rank, tnrs."Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet, tnrs."Name_matched_author" AS scrubbed_author, tnrs."Name_matched" AS scrubbed_taxon_name_no_author FROM tnrs;
|
|
382 |
SELECT tnrs."Name_submitted" AS scrubbed_unique_taxon_name,
|
|
383 |
tnrs."Name_matched_rank" AS scrubbed_taxon_rank,
|
|
384 |
tnrs."Name_matched_accepted_family" AS scrubbed_family,
|
|
385 |
tnrs."Genus_matched" AS scrubbed_genus,
|
|
386 |
tnrs."Specific_epithet_matched" AS scrubbed_specific_epithet,
|
|
387 |
tnrs."Infraspecific_rank" AS scrubbed_infraspecific_rank,
|
|
388 |
tnrs."Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet,
|
|
389 |
tnrs."Name_matched_author" AS scrubbed_author,
|
|
390 |
tnrs."Name_matched" AS scrubbed_taxon_name_no_author
|
|
391 |
FROM tnrs;
|
| 319 |
392 |
|
| 320 |
393 |
|
| 321 |
394 |
--
|
| ... | ... | |
| 323 |
396 |
--
|
| 324 |
397 |
|
| 325 |
398 |
CREATE VIEW taxon_scrub AS
|
| 326 |
|
SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name, "ValidMatchedTaxon"."*Name_matched.batch", "ValidMatchedTaxon"."concatenatedScientificName", "ValidMatchedTaxon"."matchedTaxonName", "ValidMatchedTaxon"."matchedTaxonRank", "ValidMatchedTaxon"."*Name_matched.Name_score", "ValidMatchedTaxon"."matchedScientificNameAuthorship", "ValidMatchedTaxon"."matchedScientificNameID", "ValidMatchedTaxon"."*Name_matched.Author_score", "ValidMatchedTaxon"."matchedFamilyConfidence_fraction", "ValidMatchedTaxon"."matchedFamily", "ValidMatchedTaxon"."matchedGenus", "ValidMatchedTaxon"."matchedGenusConfidence_fraction", "ValidMatchedTaxon"."matchedSpecificEpithet", "ValidMatchedTaxon"."matchedSpeciesConfidence_fraction", "ValidMatchedTaxon"."matchedInfraspecificEpithet", "ValidMatchedTaxon"."*Name_matched.Infraspecific_epithet_score", "ValidMatchedTaxon"."identificationQualifier", "ValidMatchedTaxon"."morphospeciesSuffix", "ValidMatchedTaxon"."taxonomicStatus", "ValidMatchedTaxon"."acceptedTaxonName", "ValidMatchedTaxon"."acceptedScientificNameAuthorship", "ValidMatchedTaxon"."acceptedTaxonRank", "ValidMatchedTaxon"."acceptedScientificNameID", "ValidMatchedTaxon"."*Name_matched.Accepted_name_species", "ValidMatchedTaxon"."acceptedFamily", "ValidMatchedTaxon"."*Name_matched.Selected", "ValidMatchedTaxon"."*Name_matched.Source", "ValidMatchedTaxon"."*Name_matched.Warnings", "ValidMatchedTaxon"."*Name_matched.Accepted_name_lsid", "ValidMatchedTaxon".taxon_scrub__is_valid_match, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_rank, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_epithet, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_author, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author FROM ("ValidMatchedTaxon" NATURAL LEFT JOIN "taxon_scrub.scrubbed_unique_taxon_name.*");
|
|
399 |
SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name,
|
|
400 |
"ValidMatchedTaxon"."*Name_matched.batch",
|
|
401 |
"ValidMatchedTaxon"."concatenatedScientificName",
|
|
402 |
"ValidMatchedTaxon"."matchedTaxonName",
|
|
403 |
"ValidMatchedTaxon"."matchedTaxonRank",
|
|
404 |
"ValidMatchedTaxon"."*Name_matched.Name_score",
|
|
405 |
"ValidMatchedTaxon"."matchedScientificNameAuthorship",
|
|
406 |
"ValidMatchedTaxon"."matchedScientificNameID",
|
|
407 |
"ValidMatchedTaxon"."*Name_matched.Author_score",
|
|
408 |
"ValidMatchedTaxon"."matchedFamilyConfidence_fraction",
|
|
409 |
"ValidMatchedTaxon"."matchedFamily",
|
|
410 |
"ValidMatchedTaxon"."matchedGenus",
|
|
411 |
"ValidMatchedTaxon"."matchedGenusConfidence_fraction",
|
|
412 |
"ValidMatchedTaxon"."matchedSpecificEpithet",
|
|
413 |
"ValidMatchedTaxon"."matchedSpeciesConfidence_fraction",
|
|
414 |
"ValidMatchedTaxon"."matchedInfraspecificEpithet",
|
|
415 |
"ValidMatchedTaxon"."*Name_matched.Infraspecific_epithet_score",
|
|
416 |
"ValidMatchedTaxon"."identificationQualifier",
|
|
417 |
"ValidMatchedTaxon"."morphospeciesSuffix",
|
|
418 |
"ValidMatchedTaxon"."taxonomicStatus",
|
|
419 |
"ValidMatchedTaxon"."acceptedTaxonName",
|
|
420 |
"ValidMatchedTaxon"."acceptedScientificNameAuthorship",
|
|
421 |
"ValidMatchedTaxon"."acceptedTaxonRank",
|
|
422 |
"ValidMatchedTaxon"."acceptedScientificNameID",
|
|
423 |
"ValidMatchedTaxon"."*Name_matched.Accepted_name_species",
|
|
424 |
"ValidMatchedTaxon"."acceptedFamily",
|
|
425 |
"ValidMatchedTaxon"."*Name_matched.Selected",
|
|
426 |
"ValidMatchedTaxon"."*Name_matched.Source",
|
|
427 |
"ValidMatchedTaxon"."*Name_matched.Warnings",
|
|
428 |
"ValidMatchedTaxon"."*Name_matched.Accepted_name_lsid",
|
|
429 |
"ValidMatchedTaxon".taxon_scrub__is_valid_match,
|
|
430 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank,
|
|
431 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family,
|
|
432 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus,
|
|
433 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet,
|
|
434 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_rank,
|
|
435 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_epithet,
|
|
436 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_author,
|
|
437 |
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author
|
|
438 |
FROM ("ValidMatchedTaxon"
|
|
439 |
LEFT JOIN "taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name));
|
| 327 |
440 |
|
| 328 |
441 |
|
| 329 |
442 |
--
|
inputs/.TNRS/schema.sql, data.sql: updated for PostgreSQL 9.3