Revision 5917
Added by Aaron Marcuse-Kubitza over 11 years ago
| public.test_taxonomic_names.sql | ||
|---|---|---|
| 89 | 89 |
|
| 90 | 90 |
CREATE TYPE placerank AS ENUM ( |
| 91 | 91 |
'continent', |
| 92 |
'waterBody', |
|
| 93 |
'islandGroup', |
|
| 94 |
'island', |
|
| 92 | 95 |
'country', |
| 96 |
'territory', |
|
| 97 |
'region', |
|
| 93 | 98 |
'stateProvince', |
| 94 | 99 |
'county', |
| 95 | 100 |
'municipality', |
| 96 | 101 |
'village', |
| 97 |
'site', |
|
| 98 |
'territory', |
|
| 99 |
'region', |
|
| 100 |
'waterBody', |
|
| 101 |
'island', |
|
| 102 |
'islandGroup' |
|
| 102 |
'site' |
|
| 103 | 103 |
); |
| 104 | 104 |
|
| 105 | 105 |
|
| ... | ... | |
| 111 | 111 |
|
| 112 | 112 |
COMMENT ON TYPE placerank IS 'county = parish, canton |
| 113 | 113 |
municipality = city |
| 114 |
'; |
|
| 115 | 114 |
|
| 115 |
From <http://rs.tdwg.org/dwc/terms/#dcindex#dcterms:Location>, <http://vegbank.org/vegbank/views/dba_fielddescription_detail.jsp?view=detail&wparam=1415&entity=dba_fielddescription¶ms=1415>'; |
|
| 116 | 116 |
|
| 117 |
|
|
| 117 | 118 |
-- |
| 118 | 119 |
-- Name: rankedplacename; Type: TYPE; Schema: public.test_taxonomic_names; Owner: bien |
| 119 | 120 |
-- |
| ... | ... | |
| 321 | 322 |
|
| 322 | 323 |
|
| 323 | 324 |
-- |
| 325 |
-- Name: _cm_to_m(double precision); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien |
|
| 326 |
-- |
|
| 327 |
|
|
| 328 |
CREATE FUNCTION _cm_to_m(value double precision) RETURNS double precision |
|
| 329 |
LANGUAGE sql IMMUTABLE STRICT |
|
| 330 |
AS $_$ |
|
| 331 |
SELECT $1/100. |
|
| 332 |
$_$; |
|
| 333 |
|
|
| 334 |
|
|
| 335 |
ALTER FUNCTION "public.test_taxonomic_names"._cm_to_m(value double precision) OWNER TO bien; |
|
| 336 |
|
|
| 337 |
-- |
|
| 324 | 338 |
-- Name: _fraction_to_percent(double precision); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien |
| 325 | 339 |
-- |
| 326 | 340 |
|
| ... | ... | |
| 334 | 348 |
ALTER FUNCTION "public.test_taxonomic_names"._fraction_to_percent(value double precision) OWNER TO bien; |
| 335 | 349 |
|
| 336 | 350 |
-- |
| 351 |
-- Name: _ft_to_m(double precision); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien |
|
| 352 |
-- |
|
| 353 |
|
|
| 354 |
CREATE FUNCTION _ft_to_m(value double precision) RETURNS double precision |
|
| 355 |
LANGUAGE sql IMMUTABLE STRICT |
|
| 356 |
AS $_$ |
|
| 357 |
SELECT _in_to_m($1*12) |
|
| 358 |
$_$; |
|
| 359 |
|
|
| 360 |
|
|
| 361 |
ALTER FUNCTION "public.test_taxonomic_names"._ft_to_m(value double precision) OWNER TO bien; |
|
| 362 |
|
|
| 363 |
-- |
|
| 364 |
-- Name: _ha_to_m2(double precision); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien |
|
| 365 |
-- |
|
| 366 |
|
|
| 367 |
CREATE FUNCTION _ha_to_m2(value double precision) RETURNS double precision |
|
| 368 |
LANGUAGE sql IMMUTABLE STRICT |
|
| 369 |
AS $_$ |
|
| 370 |
SELECT $1*10000. |
|
| 371 |
$_$; |
|
| 372 |
|
|
| 373 |
|
|
| 374 |
ALTER FUNCTION "public.test_taxonomic_names"._ha_to_m2(value double precision) OWNER TO bien; |
|
| 375 |
|
|
| 376 |
-- |
|
| 337 | 377 |
-- Name: _has_taxonomic_name(taxonrank); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien |
| 338 | 378 |
-- |
| 339 | 379 |
|
| ... | ... | |
| 347 | 387 |
ALTER FUNCTION "public.test_taxonomic_names"._has_taxonomic_name(rank taxonrank) OWNER TO bien; |
| 348 | 388 |
|
| 349 | 389 |
-- |
| 390 |
-- Name: _in_to_m(double precision); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien |
|
| 391 |
-- |
|
| 392 |
|
|
| 393 |
CREATE FUNCTION _in_to_m(value double precision) RETURNS double precision |
|
| 394 |
LANGUAGE sql IMMUTABLE STRICT |
|
| 395 |
AS $_$ |
|
| 396 |
SELECT $1*2.54/100. |
|
| 397 |
$_$; |
|
| 398 |
|
|
| 399 |
|
|
| 400 |
ALTER FUNCTION "public.test_taxonomic_names"._in_to_m(value double precision) OWNER TO bien; |
|
| 401 |
|
|
| 402 |
-- |
|
| 403 |
-- Name: _m2_to_ha(double precision); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien |
|
| 404 |
-- |
|
| 405 |
|
|
| 406 |
CREATE FUNCTION _m2_to_ha(value double precision) RETURNS double precision |
|
| 407 |
LANGUAGE sql IMMUTABLE STRICT |
|
| 408 |
AS $_$ |
|
| 409 |
SELECT $1/10000. |
|
| 410 |
$_$; |
|
| 411 |
|
|
| 412 |
|
|
| 413 |
ALTER FUNCTION "public.test_taxonomic_names"._m2_to_ha(value double precision) OWNER TO bien; |
|
| 414 |
|
|
| 415 |
-- |
|
| 416 |
-- Name: _m_to_cm(double precision); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien |
|
| 417 |
-- |
|
| 418 |
|
|
| 419 |
CREATE FUNCTION _m_to_cm(value double precision) RETURNS double precision |
|
| 420 |
LANGUAGE sql IMMUTABLE STRICT |
|
| 421 |
AS $_$ |
|
| 422 |
SELECT $1*100. |
|
| 423 |
$_$; |
|
| 424 |
|
|
| 425 |
|
|
| 426 |
ALTER FUNCTION "public.test_taxonomic_names"._m_to_cm(value double precision) OWNER TO bien; |
|
| 427 |
|
|
| 428 |
-- |
|
| 429 |
-- Name: _percent_to_fraction(double precision); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien |
|
| 430 |
-- |
|
| 431 |
|
|
| 432 |
CREATE FUNCTION _percent_to_fraction(value double precision) RETURNS double precision |
|
| 433 |
LANGUAGE sql IMMUTABLE STRICT |
|
| 434 |
AS $_$ |
|
| 435 |
SELECT $1/100. |
|
| 436 |
$_$; |
|
| 437 |
|
|
| 438 |
|
|
| 439 |
ALTER FUNCTION "public.test_taxonomic_names"._percent_to_fraction(value double precision) OWNER TO bien; |
|
| 440 |
|
|
| 441 |
-- |
|
| 350 | 442 |
-- Name: _taxon_family_require_std(text); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien |
| 351 | 443 |
-- |
| 352 | 444 |
|
| ... | ... | |
| 364 | 456 |
-- |
| 365 | 457 |
|
| 366 | 458 |
CREATE FUNCTION _taxonlabel_set_matched_label_id(taxonlabel_id integer, matched_label_id integer, matched_label_fit_fraction double precision DEFAULT NULL::double precision) RETURNS integer |
| 367 |
LANGUAGE sql |
|
| 368 |
AS $_$ |
|
| 369 |
UPDATE taxonlabel SET |
|
| 370 |
matched_label_id = $2 |
|
| 371 |
, matched_label_fit_fraction = $3 |
|
| 372 |
WHERE taxonlabel_id = $1 |
|
| 373 |
RETURNING taxonlabel_id |
|
| 374 |
$_$; |
|
| 459 |
LANGUAGE plpgsql |
|
| 460 |
AS $$ |
|
| 461 |
BEGIN |
|
| 462 |
-- Use EXECUTE to avoid caching query plan |
|
| 463 |
EXECUTE ' |
|
| 464 |
UPDATE taxonlabel SET |
|
| 465 |
matched_label_id = '||quote_nullable(matched_label_id)||' |
|
| 466 |
, matched_label_fit_fraction = ' |
|
| 467 |
||quote_nullable(matched_label_fit_fraction)||' |
|
| 468 |
WHERE taxonlabel_id = '||quote_nullable(taxonlabel_id)||' |
|
| 469 |
'; |
|
| 470 |
|
|
| 471 |
RETURN taxonlabel_id; |
|
| 472 |
END; |
|
| 473 |
$$; |
|
| 375 | 474 |
|
| 376 | 475 |
|
| 377 | 476 |
ALTER FUNCTION "public.test_taxonomic_names"._taxonlabel_set_matched_label_id(taxonlabel_id integer, matched_label_id integer, matched_label_fit_fraction double precision) OWNER TO bien; |
| ... | ... | |
| 381 | 480 |
-- |
| 382 | 481 |
|
| 383 | 482 |
CREATE FUNCTION _taxonlabel_set_parent_id(taxonlabel_id integer, parent_id integer) RETURNS integer |
| 384 |
LANGUAGE sql |
|
| 385 |
AS $_$ |
|
| 386 |
UPDATE taxonlabel SET parent_id = $2 WHERE taxonlabel_id = $1 |
|
| 387 |
RETURNING taxonlabel_id |
|
| 388 |
$_$; |
|
| 483 |
LANGUAGE plpgsql |
|
| 484 |
AS $$ |
|
| 485 |
BEGIN |
|
| 486 |
-- Use EXECUTE to avoid caching query plan |
|
| 487 |
EXECUTE ' |
|
| 488 |
UPDATE taxonlabel SET parent_id = '||quote_nullable(parent_id)||' |
|
| 489 |
WHERE taxonlabel_id = '||quote_nullable(taxonlabel_id)||' |
|
| 490 |
'; |
|
| 491 |
|
|
| 492 |
RETURN taxonlabel_id; |
|
| 493 |
END; |
|
| 494 |
$$; |
|
| 389 | 495 |
|
| 390 | 496 |
|
| 391 | 497 |
ALTER FUNCTION "public.test_taxonomic_names"._taxonlabel_set_parent_id(taxonlabel_id integer, parent_id integer) OWNER TO bien; |
| ... | ... | |
| 404 | 510 |
ALTER FUNCTION "public.test_taxonomic_names"._taxonomic_name_is_epithet(rank taxonrank) OWNER TO bien; |
| 405 | 511 |
|
| 406 | 512 |
-- |
| 407 |
-- Name: make_analytical_db(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 513 |
-- Name: make_analytical_stem(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 408 | 514 |
-- |
| 409 | 515 |
|
| 410 |
CREATE FUNCTION make_analytical_db() RETURNS void
|
|
| 516 |
CREATE FUNCTION make_analytical_stem() RETURNS void
|
|
| 411 | 517 |
LANGUAGE sql |
| 412 |
AS $$CREATE TABLE analytical_db AS SELECT * FROM analytical_db_view$$; |
|
| 518 |
AS $$ |
|
| 519 |
INSERT INTO analytical_stem SELECT * FROM analytical_stem_view; |
|
| 520 |
INSERT INTO analytical_aggregate SELECT * FROM analytical_aggregate_view; |
|
| 521 |
$$; |
|
| 413 | 522 |
|
| 414 | 523 |
|
| 415 |
ALTER FUNCTION "public.test_taxonomic_names".make_analytical_db() OWNER TO bien;
|
|
| 524 |
ALTER FUNCTION "public.test_taxonomic_names".make_analytical_stem() OWNER TO bien;
|
|
| 416 | 525 |
|
| 417 | 526 |
-- |
| 418 | 527 |
-- Name: party_creator_id_self_ref(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien |
| ... | ... | |
| 436 | 545 |
ALTER FUNCTION "public.test_taxonomic_names".party_creator_id_self_ref() OWNER TO bien; |
| 437 | 546 |
|
| 438 | 547 |
-- |
| 439 |
-- Name: place_update_ancestors(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 548 |
-- Name: place_matched_place_id_self_ref(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 440 | 549 |
-- |
| 441 | 550 |
|
| 442 |
CREATE FUNCTION place_update_ancestors() RETURNS trigger
|
|
| 551 |
CREATE FUNCTION place_matched_place_id_self_ref() RETURNS trigger
|
|
| 443 | 552 |
LANGUAGE plpgsql |
| 444 | 553 |
AS $$ |
| 445 | 554 |
BEGIN |
| 555 |
IF new.place_id IS NULL THEN -- prepopulate place_id |
|
| 556 |
new.place_id = nextval('place_place_id_seq'::regclass);
|
|
| 557 |
END IF; |
|
| 558 |
IF new.matched_place_id = 0 THEN -- make self-reference |
|
| 559 |
new.matched_place_id = new.place_id; |
|
| 560 |
END IF; |
|
| 561 |
RETURN new; |
|
| 562 |
END; |
|
| 563 |
$$; |
|
| 564 |
|
|
| 565 |
|
|
| 566 |
ALTER FUNCTION "public.test_taxonomic_names".place_matched_place_id_self_ref() OWNER TO bien; |
|
| 567 |
|
|
| 568 |
-- |
|
| 569 |
-- Name: placename_update_ancestors(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien |
|
| 570 |
-- |
|
| 571 |
|
|
| 572 |
CREATE FUNCTION placename_update_ancestors() RETURNS trigger |
|
| 573 |
LANGUAGE plpgsql |
|
| 574 |
AS $$ |
|
| 575 |
BEGIN |
|
| 446 | 576 |
-- Delete existing ancestors |
| 447 |
DELETE FROM place_ancestor WHERE place_id = new.place_id;
|
|
| 577 |
DELETE FROM placename_ancestor WHERE placename_id = new.placename_id;
|
|
| 448 | 578 |
|
| 449 | 579 |
IF new.parent_id IS NOT NULL THEN |
| 450 | 580 |
-- Copy parent's ancestors to this node's ancestors |
| 451 | 581 |
INSERT |
| 452 |
INTO place_ancestor |
|
| 453 |
(place_id, ancestor_id) |
|
| 582 |
INTO placename_ancestor
|
|
| 583 |
(placename_id, ancestor_id)
|
|
| 454 | 584 |
SELECT |
| 455 |
new.place_id, ancestor_id |
|
| 456 |
FROM place_ancestor |
|
| 457 |
WHERE place_id = new.parent_id |
|
| 585 |
new.placename_id, ancestor_id
|
|
| 586 |
FROM placename_ancestor
|
|
| 587 |
WHERE placename_id = new.parent_id
|
|
| 458 | 588 |
; |
| 459 | 589 |
END IF; |
| 460 | 590 |
|
| ... | ... | |
| 463 | 593 |
the leaf node is the one you're looking for, in addition to that leaf node's |
| 464 | 594 |
ancestors. */ |
| 465 | 595 |
INSERT |
| 466 |
INTO place_ancestor |
|
| 467 |
(place_id, ancestor_id) |
|
| 468 |
VALUES (new.place_id, new.place_id)
|
|
| 596 |
INTO placename_ancestor
|
|
| 597 |
(placename_id, ancestor_id)
|
|
| 598 |
VALUES (new.placename_id, new.placename_id)
|
|
| 469 | 599 |
; |
| 470 | 600 |
|
| 471 | 601 |
-- Tell immediate children to update their ancestors lists, which will |
| 472 | 602 |
-- recursively tell all descendants |
| 473 |
UPDATE place |
|
| 474 |
SET place_id = place_id -- need at least one SET statement
|
|
| 475 |
-- Add COALESCE() to enable using place_unique index for lookup |
|
| 476 |
WHERE COALESCE(parent_id, 2147483647) = new.place_id |
|
| 477 |
AND place_id != new.place_id -- avoid infinite recursion
|
|
| 603 |
UPDATE placename
|
|
| 604 |
SET placename_id = placename_id -- need at least one SET statement
|
|
| 605 |
-- Add COALESCE() to enable using placename_unique index for lookup
|
|
| 606 |
WHERE COALESCE(parent_id, 2147483647) = new.placename_id
|
|
| 607 |
AND placename_id != new.placename_id -- avoid infinite recursion
|
|
| 478 | 608 |
; |
| 479 | 609 |
|
| 480 | 610 |
/* Note: We don't need an ON DELETE trigger to update the descendants' |
| 481 |
ancestors when a node is deleted, because the place.place_parent_id
|
|
| 611 |
ancestors when a node is deleted, because the placename.placename_parent_id
|
|
| 482 | 612 |
foreign key is set to ON DELETE CASCADE, which just removes all the |
| 483 | 613 |
descendants anyway. */ |
| 484 | 614 |
|
| ... | ... | |
| 487 | 617 |
$$; |
| 488 | 618 |
|
| 489 | 619 |
|
| 490 |
ALTER FUNCTION "public.test_taxonomic_names".place_update_ancestors() OWNER TO bien; |
|
| 620 |
ALTER FUNCTION "public.test_taxonomic_names".placename_update_ancestors() OWNER TO bien;
|
|
| 491 | 621 |
|
| 492 | 622 |
-- |
| 493 |
-- Name: placepath_matched_placepath_id_self_ref(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 623 |
-- Name: plantobservation_aggregateoccurrence_count_1(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 494 | 624 |
-- |
| 495 | 625 |
|
| 496 |
CREATE FUNCTION placepath_matched_placepath_id_self_ref() RETURNS trigger
|
|
| 626 |
CREATE FUNCTION plantobservation_aggregateoccurrence_count_1() RETURNS trigger
|
|
| 497 | 627 |
LANGUAGE plpgsql |
| 498 | 628 |
AS $$ |
| 499 | 629 |
BEGIN |
| 500 |
IF new.placepath_id IS NULL THEN -- prepopulate placepath_id |
|
| 501 |
new.placepath_id = nextval('placepath_placepath_id_seq'::regclass);
|
|
| 502 |
END IF; |
|
| 503 |
IF new.matched_placepath_id = 0 THEN -- make self-reference |
|
| 504 |
new.matched_placepath_id = new.placepath_id; |
|
| 505 |
END IF; |
|
| 630 |
UPDATE aggregateoccurrence |
|
| 631 |
SET count = GREATEST(COALESCE(count, 0), 1) -- at least 1 |
|
| 632 |
WHERE aggregateoccurrence_id = new.aggregateoccurrence_id |
|
| 633 |
; |
|
| 506 | 634 |
RETURN new; |
| 507 | 635 |
END; |
| 508 | 636 |
$$; |
| 509 | 637 |
|
| 510 | 638 |
|
| 511 |
ALTER FUNCTION "public.test_taxonomic_names".placepath_matched_placepath_id_self_ref() OWNER TO bien;
|
|
| 639 |
ALTER FUNCTION "public.test_taxonomic_names".plantobservation_aggregateoccurrence_count_1() OWNER TO bien;
|
|
| 512 | 640 |
|
| 513 | 641 |
-- |
| 514 |
-- Name: plantobservation_aggregateoccurrence_count_1(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 642 |
-- Name: sync_analytical_aggregate_to_view(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 515 | 643 |
-- |
| 516 | 644 |
|
| 517 |
CREATE FUNCTION plantobservation_aggregateoccurrence_count_1() RETURNS trigger
|
|
| 518 |
LANGUAGE plpgsql
|
|
| 645 |
CREATE FUNCTION sync_analytical_aggregate_to_view() RETURNS void
|
|
| 646 |
LANGUAGE sql |
|
| 519 | 647 |
AS $$ |
| 520 |
BEGIN |
|
| 521 |
UPDATE aggregateoccurrence |
|
| 522 |
SET count = GREATEST(COALESCE(count, 0), 1) -- at least 1 |
|
| 523 |
WHERE aggregateoccurrence_id = new.aggregateoccurrence_id |
|
| 524 |
; |
|
| 525 |
RETURN new; |
|
| 526 |
END; |
|
| 648 |
DROP TABLE IF EXISTS analytical_aggregate; |
|
| 649 |
CREATE TABLE analytical_aggregate AS SELECT * FROM analytical_aggregate_view; |
|
| 527 | 650 |
$$; |
| 528 | 651 |
|
| 529 | 652 |
|
| 530 |
ALTER FUNCTION "public.test_taxonomic_names".plantobservation_aggregateoccurrence_count_1() OWNER TO bien;
|
|
| 653 |
ALTER FUNCTION "public.test_taxonomic_names".sync_analytical_aggregate_to_view() OWNER TO bien;
|
|
| 531 | 654 |
|
| 532 | 655 |
-- |
| 656 |
-- Name: sync_analytical_stem_to_view(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien |
|
| 657 |
-- |
|
| 658 |
|
|
| 659 |
CREATE FUNCTION sync_analytical_stem_to_view() RETURNS void |
|
| 660 |
LANGUAGE sql |
|
| 661 |
AS $$ |
|
| 662 |
DROP TABLE IF EXISTS analytical_stem; |
|
| 663 |
CREATE TABLE analytical_stem AS SELECT * FROM analytical_stem_view; |
|
| 664 |
$$; |
|
| 665 |
|
|
| 666 |
|
|
| 667 |
ALTER FUNCTION "public.test_taxonomic_names".sync_analytical_stem_to_view() OWNER TO bien; |
|
| 668 |
|
|
| 669 |
-- |
|
| 533 | 670 |
-- Name: taxonlabel_0_matched_label_id_self_ref(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien |
| 534 | 671 |
-- |
| 535 | 672 |
|
| ... | ... | |
| 578 | 715 |
LANGUAGE plpgsql |
| 579 | 716 |
AS $$ |
| 580 | 717 |
BEGIN |
| 581 |
new.canon_label_id = new.taxonlabel_id; -- make self-reference |
|
| 718 |
new := taxonlabel_set_canon_label_id(new); |
|
| 719 |
|
|
| 582 | 720 |
RETURN new; |
| 583 | 721 |
END; |
| 584 | 722 |
$$; |
| ... | ... | |
| 594 | 732 |
LANGUAGE plpgsql |
| 595 | 733 |
AS $$ |
| 596 | 734 |
BEGIN |
| 597 |
IF new.matched_label_id IS DISTINCT FROM old.matched_label_id THEN |
|
| 598 |
IF new.matched_label_id IS NOT NULL THEN |
|
| 599 |
IF new.matched_label_id = new.taxonlabel_id THEN -- self-reference |
|
| 600 |
new.canon_label_id = new.taxonlabel_id; -- make self-reference |
|
| 601 |
ELSE -- propagate from matched label |
|
| 602 |
new.canon_label_id = ( |
|
| 603 |
SELECT canon_label_id |
|
| 604 |
FROM taxonlabel |
|
| 605 |
WHERE taxonlabel_id = new.matched_label_id |
|
| 606 |
); |
|
| 607 |
END IF; |
|
| 608 |
|
|
| 609 |
-- Update canon_label_id on labels that resolve to this label |
|
| 610 |
UPDATE taxonlabel |
|
| 611 |
SET canon_label_id = new.canon_label_id |
|
| 612 |
WHERE matched_label_id = new.taxonlabel_id |
|
| 613 |
AND taxonlabel_id != new.taxonlabel_id -- avoid infinite recursion |
|
| 614 |
; |
|
| 615 |
ELSE -- no matched taxonlabel |
|
| 616 |
new.canon_label_id = new.taxonlabel_id; -- make self-reference |
|
| 617 |
END IF; |
|
| 618 |
END IF; |
|
| 735 |
new := taxonlabel_set_canon_label_id(new, old.matched_label_id, true); |
|
| 736 |
|
|
| 619 | 737 |
RETURN new; |
| 620 | 738 |
END; |
| 621 | 739 |
$$; |
| ... | ... | |
| 662 | 780 |
taxonomicname text, |
| 663 | 781 |
accessioncode text, |
| 664 | 782 |
CONSTRAINT taxonlabel_matched_label_fit_fraction_range CHECK (((matched_label_fit_fraction >= (0)::double precision) AND (matched_label_fit_fraction <= (1)::double precision))), |
| 665 |
CONSTRAINT taxonlabel_required_key CHECK (((taxonepithet IS NOT NULL) OR (taxonomicname IS NOT NULL)))
|
|
| 783 |
CONSTRAINT taxonlabel_required_key CHECK ((((sourceaccessioncode IS NOT NULL) OR (taxonepithet IS NOT NULL)) OR (taxonomicname IS NOT NULL)))
|
|
| 666 | 784 |
); |
| 667 | 785 |
|
| 668 | 786 |
|
| ... | ... | |
| 758 | 876 |
|
| 759 | 877 |
|
| 760 | 878 |
-- |
| 761 |
-- Name: taxonlabel_update_ancestors(taxonlabel, integer); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 879 |
-- Name: taxonlabel_set_canon_label_id(taxonlabel, integer, boolean); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 762 | 880 |
-- |
| 763 | 881 |
|
| 764 |
CREATE FUNCTION taxonlabel_update_ancestors(new taxonlabel, old_parent_id integer DEFAULT NULL::integer) RETURNS void
|
|
| 882 |
CREATE FUNCTION taxonlabel_set_canon_label_id(new taxonlabel, old_matched_label_id integer DEFAULT NULL::integer, is_update boolean DEFAULT false) RETURNS taxonlabel
|
|
| 765 | 883 |
LANGUAGE plpgsql |
| 766 | 884 |
AS $$ |
| 767 |
DECLARE |
|
| 768 |
-- Use matched_label_id's ancestors instead if available |
|
| 769 |
parent_id_ taxonlabel.taxonlabel_id%TYPE := COALESCE( |
|
| 770 |
NULLIF(new.matched_label_id, new.taxonlabel_id), new.parent_id); |
|
| 771 | 885 |
BEGIN |
| 886 |
IF new.matched_label_id IS DISTINCT FROM old_matched_label_id THEN |
|
| 887 |
IF new.matched_label_id IS NOT NULL THEN |
|
| 888 |
IF new.matched_label_id = new.taxonlabel_id THEN -- self-reference |
|
| 889 |
new.canon_label_id := new.taxonlabel_id; -- make self-reference |
|
| 890 |
ELSE -- propagate from matched label |
|
| 891 |
new.canon_label_id := ( |
|
| 892 |
SELECT canon_label_id |
|
| 893 |
FROM taxonlabel |
|
| 894 |
WHERE taxonlabel_id = new.matched_label_id |
|
| 895 |
); |
|
| 896 |
END IF; |
|
| 897 |
|
|
| 898 |
IF is_update THEN |
|
| 899 |
-- Update canon_label_id on labels that resolve to this label |
|
| 900 |
UPDATE taxonlabel |
|
| 901 |
SET canon_label_id = new.canon_label_id |
|
| 902 |
WHERE matched_label_id = new.taxonlabel_id |
|
| 903 |
AND taxonlabel_id != new.taxonlabel_id -- no infinite recursion |
|
| 904 |
; |
|
| 905 |
END IF; |
|
| 906 |
ELSE -- no matched taxonlabel |
|
| 907 |
new.canon_label_id := new.taxonlabel_id; -- make self-reference |
|
| 908 |
END IF; |
|
| 909 |
END IF; |
|
| 910 |
|
|
| 911 |
RETURN new; |
|
| 912 |
END; |
|
| 913 |
$$; |
|
| 914 |
|
|
| 915 |
|
|
| 916 |
ALTER FUNCTION "public.test_taxonomic_names".taxonlabel_set_canon_label_id(new taxonlabel, old_matched_label_id integer, is_update boolean) OWNER TO bien; |
|
| 917 |
|
|
| 918 |
-- |
|
| 919 |
-- Name: taxonlabel_update_ancestors(taxonlabel, integer, integer); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien |
|
| 920 |
-- |
|
| 921 |
|
|
| 922 |
CREATE FUNCTION taxonlabel_update_ancestors(new taxonlabel, parent_id_ integer, old_parent_id integer DEFAULT NULL::integer) RETURNS void |
|
| 923 |
LANGUAGE plpgsql |
|
| 924 |
AS $$ |
|
| 925 |
BEGIN |
|
| 772 | 926 |
IF parent_id_ IS DISTINCT FROM old_parent_id THEN |
| 773 | 927 |
DECLARE |
| 774 | 928 |
-- These include the parent itself |
| ... | ... | |
| 819 | 973 |
$$; |
| 820 | 974 |
|
| 821 | 975 |
|
| 822 |
ALTER FUNCTION "public.test_taxonomic_names".taxonlabel_update_ancestors(new taxonlabel, old_parent_id integer) OWNER TO bien; |
|
| 976 |
ALTER FUNCTION "public.test_taxonomic_names".taxonlabel_update_ancestors(new taxonlabel, parent_id_ integer, old_parent_id integer) OWNER TO bien;
|
|
| 823 | 977 |
|
| 824 | 978 |
-- |
| 825 | 979 |
-- Name: taxonlabel_update_ancestors_on_insert(); Type: FUNCTION; Schema: public.test_taxonomic_names; Owner: bien |
| ... | ... | |
| 838 | 992 |
VALUES (new.taxonlabel_id, new.taxonlabel_id) |
| 839 | 993 |
; |
| 840 | 994 |
|
| 841 |
PERFORM taxonlabel_update_ancestors(new); |
|
| 995 |
PERFORM taxonlabel_update_ancestors(new, new.parent_id); |
|
| 996 |
PERFORM taxonlabel_update_ancestors(new, |
|
| 997 |
NULLIF(new.matched_label_id, new.taxonlabel_id)); |
|
| 842 | 998 |
|
| 843 | 999 |
RETURN new; |
| 844 | 1000 |
END; |
| ... | ... | |
| 855 | 1011 |
LANGUAGE plpgsql |
| 856 | 1012 |
AS $$ |
| 857 | 1013 |
BEGIN |
| 858 |
PERFORM taxonlabel_update_ancestors(new, COALESCE( |
|
| 859 |
NULLIF(old.matched_label_id, old.taxonlabel_id), old.parent_id)); |
|
| 1014 |
PERFORM taxonlabel_update_ancestors(new, new.parent_id, old.parent_id); |
|
| 1015 |
PERFORM taxonlabel_update_ancestors(new, |
|
| 1016 |
NULLIF(new.matched_label_id, new.taxonlabel_id), |
|
| 1017 |
NULLIF(old.matched_label_id, old.taxonlabel_id)); |
|
| 860 | 1018 |
|
| 861 | 1019 |
RETURN new; |
| 862 | 1020 |
END; |
| ... | ... | |
| 1006 | 1164 |
|
| 1007 | 1165 |
|
| 1008 | 1166 |
-- |
| 1167 |
-- Name: analytical_aggregate; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: |
|
| 1168 |
-- |
|
| 1169 |
|
|
| 1170 |
CREATE TABLE analytical_aggregate ( |
|
| 1171 |
"institutionCode" text, |
|
| 1172 |
country text, |
|
| 1173 |
"stateProvince" text, |
|
| 1174 |
county text, |
|
| 1175 |
"decimalLatitude" double precision, |
|
| 1176 |
"decimalLongitude" double precision, |
|
| 1177 |
"plotName" text, |
|
| 1178 |
"elevationInMeters" double precision, |
|
| 1179 |
"plotArea_ha" double precision, |
|
| 1180 |
"samplingProtocol" text, |
|
| 1181 |
"dateCollected" date, |
|
| 1182 |
family text, |
|
| 1183 |
genus text, |
|
| 1184 |
"speciesBinomial" text, |
|
| 1185 |
"scientificName" text, |
|
| 1186 |
"scientificNameAuthorship" text, |
|
| 1187 |
"scientificNameWithMorphospecies" text, |
|
| 1188 |
"coverPercent" double precision, |
|
| 1189 |
"individualCount" bigint, |
|
| 1190 |
"individualCount_1cm_or_more" bigint, |
|
| 1191 |
"individualCount_2_5cm_or_more" bigint, |
|
| 1192 |
"individualCount_10cm_or_more" bigint |
|
| 1193 |
); |
|
| 1194 |
|
|
| 1195 |
|
|
| 1196 |
ALTER TABLE "public.test_taxonomic_names".analytical_aggregate OWNER TO bien; |
|
| 1197 |
|
|
| 1198 |
-- |
|
| 1199 |
-- Name: analytical_stem; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: |
|
| 1200 |
-- |
|
| 1201 |
|
|
| 1202 |
CREATE TABLE analytical_stem ( |
|
| 1203 |
"institutionCode" text, |
|
| 1204 |
country text, |
|
| 1205 |
"stateProvince" text, |
|
| 1206 |
county text, |
|
| 1207 |
"decimalLatitude" double precision, |
|
| 1208 |
"decimalLongitude" double precision, |
|
| 1209 |
"plotName" text, |
|
| 1210 |
"elevationInMeters" double precision, |
|
| 1211 |
"plotArea_ha" double precision, |
|
| 1212 |
"samplingProtocol" text, |
|
| 1213 |
"dateCollected" date, |
|
| 1214 |
family text, |
|
| 1215 |
genus text, |
|
| 1216 |
"speciesBinomial" text, |
|
| 1217 |
"scientificName" text, |
|
| 1218 |
"scientificNameAuthorship" text, |
|
| 1219 |
"scientificNameWithMorphospecies" text, |
|
| 1220 |
"identifiedBy" text, |
|
| 1221 |
"growthForm" growthform, |
|
| 1222 |
cultivated boolean, |
|
| 1223 |
"cultivatedBasis" text, |
|
| 1224 |
"coverPercent" double precision, |
|
| 1225 |
"diameterBreastHeight_cm" double precision, |
|
| 1226 |
height_m double precision, |
|
| 1227 |
tag text, |
|
| 1228 |
"organismX_m" double precision, |
|
| 1229 |
"organismY_m" double precision, |
|
| 1230 |
"recordedBy" text, |
|
| 1231 |
"recordNumber" text |
|
| 1232 |
); |
|
| 1233 |
|
|
| 1234 |
|
|
| 1235 |
ALTER TABLE "public.test_taxonomic_names".analytical_stem OWNER TO bien; |
|
| 1236 |
|
|
| 1237 |
-- |
|
| 1238 |
-- Name: analytical_aggregate_view; Type: VIEW; Schema: public.test_taxonomic_names; Owner: bien |
|
| 1239 |
-- |
|
| 1240 |
|
|
| 1241 |
CREATE VIEW analytical_aggregate_view AS |
|
| 1242 |
SELECT analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."plotName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", sum(analytical_stem."coverPercent") AS "coverPercent", count(analytical_stem."diameterBreastHeight_cm") AS "individualCount", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((1)::double precision))) AS "individualCount_1cm_or_more", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((2.5)::double precision))) AS "individualCount_2_5cm_or_more", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((10)::double precision))) AS "individualCount_10cm_or_more" FROM analytical_stem GROUP BY analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."plotName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies"; |
|
| 1243 |
|
|
| 1244 |
|
|
| 1245 |
ALTER TABLE "public.test_taxonomic_names".analytical_aggregate_view OWNER TO bien; |
|
| 1246 |
|
|
| 1247 |
-- |
|
| 1248 |
-- Name: coordinates; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: |
|
| 1249 |
-- |
|
| 1250 |
|
|
| 1251 |
CREATE TABLE coordinates ( |
|
| 1252 |
coordinates_id integer NOT NULL, |
|
| 1253 |
creator_id integer NOT NULL, |
|
| 1254 |
latitude_deg double precision NOT NULL, |
|
| 1255 |
longitude_deg double precision NOT NULL, |
|
| 1256 |
verbatimlatitude text, |
|
| 1257 |
verbatimlongitude text, |
|
| 1258 |
verbatimcoordinates text, |
|
| 1259 |
footprintgeometry_dwc text, |
|
| 1260 |
coordsaccuracy_deg double precision |
|
| 1261 |
); |
|
| 1262 |
|
|
| 1263 |
|
|
| 1264 |
ALTER TABLE "public.test_taxonomic_names".coordinates OWNER TO bien; |
|
| 1265 |
|
|
| 1266 |
-- |
|
| 1267 |
-- Name: COLUMN coordinates.footprintgeometry_dwc; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien |
|
| 1268 |
-- |
|
| 1269 |
|
|
| 1270 |
COMMENT ON COLUMN coordinates.footprintgeometry_dwc IS 'DwC''s footprintWKT field. "A Well-Known Text (WKT) representation of the shape (footprint, geometry) that defines the Location" (http://rs.tdwg.org/dwc/terms/#footprintWKT).'; |
|
| 1271 |
|
|
| 1272 |
|
|
| 1273 |
-- |
|
| 1274 |
-- Name: COLUMN coordinates.coordsaccuracy_deg; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien |
|
| 1275 |
-- |
|
| 1276 |
|
|
| 1277 |
COMMENT ON COLUMN coordinates.coordsaccuracy_deg IS 'Accuracy of latitude/longitude or footprint geometry, in meters. This should generally be at least 1 m, which is the accuracy of the best GPSes.'; |
|
| 1278 |
|
|
| 1279 |
|
|
| 1280 |
-- |
|
| 1009 | 1281 |
-- Name: location; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: |
| 1010 | 1282 |
-- |
| 1011 | 1283 |
|
| ... | ... | |
| 1067 | 1339 |
|
| 1068 | 1340 |
|
| 1069 | 1341 |
-- |
| 1070 |
-- Name: locationcoords; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: |
|
| 1071 |
-- |
|
| 1072 |
|
|
| 1073 |
CREATE TABLE locationcoords ( |
|
| 1074 |
locationcoords_id integer NOT NULL, |
|
| 1075 |
location_id integer NOT NULL, |
|
| 1076 |
latitude_deg double precision, |
|
| 1077 |
longitude_deg double precision, |
|
| 1078 |
verbatimlatitude text, |
|
| 1079 |
verbatimlongitude text, |
|
| 1080 |
verbatimcoordinates text, |
|
| 1081 |
footprintgeometry_dwc text, |
|
| 1082 |
coordsaccuracy_deg double precision, |
|
| 1083 |
identifier_id integer, |
|
| 1084 |
determinationdate date, |
|
| 1085 |
isoriginal boolean DEFAULT false NOT NULL, |
|
| 1086 |
iscurrent boolean DEFAULT false NOT NULL, |
|
| 1087 |
calculated boolean |
|
| 1088 |
); |
|
| 1089 |
|
|
| 1090 |
|
|
| 1091 |
ALTER TABLE "public.test_taxonomic_names".locationcoords OWNER TO bien; |
|
| 1092 |
|
|
| 1093 |
-- |
|
| 1094 |
-- Name: COLUMN locationcoords.footprintgeometry_dwc; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien |
|
| 1095 |
-- |
|
| 1096 |
|
|
| 1097 |
COMMENT ON COLUMN locationcoords.footprintgeometry_dwc IS 'DwC''s footprintWKT field. "A Well-Known Text (WKT) representation of the shape (footprint, geometry) that defines the Location" (http://rs.tdwg.org/dwc/terms/#footprintWKT).'; |
|
| 1098 |
|
|
| 1099 |
|
|
| 1100 |
-- |
|
| 1101 |
-- Name: COLUMN locationcoords.coordsaccuracy_deg; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien |
|
| 1102 |
-- |
|
| 1103 |
|
|
| 1104 |
COMMENT ON COLUMN locationcoords.coordsaccuracy_deg IS 'Accuracy of latitude/longitude or footprint geometry, in meters. This should generally be at least 1 m, which is the accuracy of the best GPSes.'; |
|
| 1105 |
|
|
| 1106 |
|
|
| 1107 |
-- |
|
| 1108 | 1342 |
-- Name: locationevent; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: |
| 1109 | 1343 |
-- |
| 1110 | 1344 |
|
| ... | ... | |
| 1205 | 1439 |
CREATE TABLE locationplace ( |
| 1206 | 1440 |
locationplace_id integer NOT NULL, |
| 1207 | 1441 |
location_id integer NOT NULL, |
| 1208 |
placepath_id integer NOT NULL,
|
|
| 1442 |
place_id integer NOT NULL, |
|
| 1209 | 1443 |
identifier_id integer |
| 1210 | 1444 |
); |
| 1211 | 1445 |
|
| ... | ... | |
| 1444 | 1678 |
|
| 1445 | 1679 |
|
| 1446 | 1680 |
-- |
| 1447 |
-- Name: placepath; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace:
|
|
| 1681 |
-- Name: place; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: |
|
| 1448 | 1682 |
-- |
| 1449 | 1683 |
|
| 1450 |
CREATE TABLE placepath (
|
|
| 1451 |
placepath_id integer NOT NULL,
|
|
| 1684 |
CREATE TABLE place ( |
|
| 1685 |
place_id integer NOT NULL, |
|
| 1452 | 1686 |
creator_id integer NOT NULL, |
| 1453 | 1687 |
placecode text, |
| 1454 |
matched_placepath_id integer, |
|
| 1455 |
place_id integer, |
|
| 1688 |
matched_place_id integer, |
|
| 1689 |
coordinates_id integer, |
|
| 1690 |
placename_id integer, |
|
| 1456 | 1691 |
continent text, |
| 1457 | 1692 |
country text, |
| 1458 | 1693 |
stateprovince text, |
| 1459 | 1694 |
county text, |
| 1460 |
municipality text, |
|
| 1461 |
site text, |
|
| 1462 |
otherranks rankedplacename[], |
|
| 1463 |
CONSTRAINT placepath_required_key CHECK (((((((placecode IS NOT NULL) OR (continent IS NOT NULL)) OR (country IS NOT NULL)) OR (stateprovince IS NOT NULL)) OR (county IS NOT NULL)) OR (otherranks IS NOT NULL))) |
|
| 1695 |
description text, |
|
| 1696 |
CONSTRAINT place_required_key CHECK (((((((placecode IS NOT NULL) OR (coordinates_id IS NOT NULL)) OR (continent IS NOT NULL)) OR (country IS NOT NULL)) OR (stateprovince IS NOT NULL)) OR (county IS NOT NULL))) |
|
| 1464 | 1697 |
); |
| 1465 | 1698 |
|
| 1466 | 1699 |
|
| 1467 |
ALTER TABLE "public.test_taxonomic_names".placepath OWNER TO bien;
|
|
| 1700 |
ALTER TABLE "public.test_taxonomic_names".place OWNER TO bien; |
|
| 1468 | 1701 |
|
| 1469 | 1702 |
-- |
| 1470 |
-- Name: TABLE placepath; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 1703 |
-- Name: TABLE place; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien |
|
| 1471 | 1704 |
-- |
| 1472 | 1705 |
|
| 1473 |
COMMENT ON TABLE placepath IS 'The full path to a place. Can be either verbatim or accepted. For accepted names, points to the identified place.
|
|
| 1706 |
COMMENT ON TABLE place IS 'The full path to a place. Can be either verbatim or accepted. For accepted names, points to the identified place. |
|
| 1474 | 1707 |
|
| 1475 |
To include a placename at a rank with no explicit column, add it to the otherranks array.';
|
|
| 1708 |
To use a custom hierarchy of placenames with no explicit column, point to the lowest-rank placename in placename_id.';
|
|
| 1476 | 1709 |
|
| 1477 | 1710 |
|
| 1478 | 1711 |
-- |
| 1479 |
-- Name: COLUMN placepath.matched_placepath_id; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 1712 |
-- Name: COLUMN place.matched_place_id; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 1480 | 1713 |
-- |
| 1481 | 1714 |
|
| 1482 |
COMMENT ON COLUMN placepath.matched_placepath_id IS 'The placepath containing the accepted name of this verbatim place path. placepaths should be linked in a two-level hierarchy of datasource name -> accepted name.
|
|
| 1715 |
COMMENT ON COLUMN place.matched_place_id IS 'The closest match to this place. Places should be linked in a three-level hierarchy of datasource place -> verbatim place -> accepted place.
|
|
| 1483 | 1716 |
|
| 1484 |
A accepted name should point to itself in this field. This will happen automatically by setting it to the special value 0.';
|
|
| 1717 |
An accepted place should point to itself in this field. This will happen automatically by setting it to the special value 0.';
|
|
| 1485 | 1718 |
|
| 1486 | 1719 |
|
| 1487 | 1720 |
-- |
| 1488 |
-- Name: COLUMN placepath.otherranks; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien |
|
| 1489 |
-- |
|
| 1490 |
|
|
| 1491 |
COMMENT ON COLUMN placepath.otherranks IS 'Additional ranks which do not have a named column. Put ranks in path order, so that lower-level places come after higher-level places.'; |
|
| 1492 |
|
|
| 1493 |
|
|
| 1494 |
-- |
|
| 1495 | 1721 |
-- Name: plantobservation; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: |
| 1496 | 1722 |
-- |
| 1497 | 1723 |
|
| ... | ... | |
| 1784 | 2010 |
|
| 1785 | 2011 |
|
| 1786 | 2012 |
-- |
| 1787 |
-- Name: analytical_db_view; Type: VIEW; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 2013 |
-- Name: analytical_stem_view; Type: VIEW; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 1788 | 2014 |
-- |
| 1789 | 2015 |
|
| 1790 |
CREATE VIEW analytical_db_view AS
|
|
| 1791 |
SELECT datasource.organizationname AS "institutionCode", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.binomial) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[accepted_taxonverbatim.taxonomicname, parsed_taxonverbatim.morphospecies], ' '::text), ''::text) AS "scientificNameWithMorphospecies", placepath.country, placepath.stateprovince AS "stateProvince", placepath.county, taxonoccurrence.verbatimcollectorname AS "recordedBy", plantobservation.collectionnumber AS "recordNumber", NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text), ''::text) AS "identifiedBy", aggregateoccurrence.collectiondate AS "dateCollected", location.sourceaccessioncode AS "plotName", functions._m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", locationcoords.latitude_deg AS "decimalLatitude", locationcoords.longitude_deg AS "decimalLongitude", location.elevation_m AS "elevationInMeters", taxonoccurrence.iscultivated AS cultivated, taxonoccurrence.cultivatedbasis AS "cultivatedBasis", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", taxonoccurrence.growthform AS "growthForm", stemobservation.tag, stemobservation.xposition_m AS "organismX_m", stemobservation.yposition_m AS "organismY_m", stemobservation.height_m FROM (((((((((((((((((((party datasource JOIN location ON ((location.creator_id = datasource.party_id))) LEFT JOIN locationcoords USING (location_id)) LEFT JOIN locationplace USING (location_id)) LEFT JOIN placepath USING (placepath_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) WHERE (datasource.organizationname IS NOT NULL);
|
|
| 2016 |
CREATE VIEW analytical_stem_view AS
|
|
| 2017 |
SELECT datasource.organizationname AS "institutionCode", place.country, place.stateprovince AS "stateProvince", place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude", location.sourceaccessioncode AS "plotName", location.elevation_m AS "elevationInMeters", _m2_to_ha(location.area_m2) AS "plotArea_ha", method.name AS "samplingProtocol", aggregateoccurrence.collectiondate AS "dateCollected", accepted_taxonverbatim.family, accepted_taxonverbatim.genus, ((accepted_taxonverbatim.genus || ' '::text) || accepted_taxonverbatim.specific_epithet) AS "speciesBinomial", COALESCE(accepted_taxonverbatim.taxonomicname, accepted_taxonverbatim.binomial) AS "scientificName", accepted_taxonverbatim.author AS "scientificNameAuthorship", NULLIF(array_to_string(ARRAY[accepted_taxonlabel.taxonomicname, parsed_taxonverbatim.morphospecies], ' '::text), ''::text) AS "scientificNameWithMorphospecies", NULLIF(array_to_string(ARRAY[identifiedby.givenname, identifiedby.middlename, identifiedby.surname], ' '::text), ''::text) AS "identifiedBy", taxonoccurrence.growthform AS "growthForm", taxonoccurrence.iscultivated AS cultivated, taxonoccurrence.cultivatedbasis AS "cultivatedBasis", _fraction_to_percent(aggregateoccurrence.cover_fraction) AS "coverPercent", _m_to_cm(stemobservation.diameterbreastheight_m) AS "diameterBreastHeight_cm", stemobservation.height_m, stemobservation.tag, stemobservation.xposition_m AS "organismX_m", stemobservation.yposition_m AS "organismY_m", taxonoccurrence.verbatimcollectorname AS "recordedBy", plantobservation.collectionnumber AS "recordNumber" FROM (((((((((((((((((((party datasource JOIN location ON ((location.creator_id = datasource.party_id))) LEFT JOIN locationplace USING (location_id)) LEFT JOIN place USING (place_id)) LEFT JOIN coordinates USING (coordinates_id)) JOIN locationevent USING (location_id)) LEFT JOIN method USING (method_id)) JOIN taxonoccurrence USING (locationevent_id)) JOIN taxondetermination USING (taxonoccurrence_id)) LEFT JOIN party identifiedby ON ((identifiedby.party_id = taxondetermination.party_id))) JOIN taxonverbatim datasource_taxonverbatim USING (taxonverbatim_id)) JOIN taxonlabel datasource_taxonlabel USING (taxonlabel_id)) JOIN taxonlabel parsed_taxonlabel ON ((parsed_taxonlabel.taxonlabel_id = datasource_taxonlabel.matched_label_id))) LEFT JOIN taxonverbatim parsed_taxonverbatim ON ((parsed_taxonverbatim.taxonlabel_id = parsed_taxonlabel.taxonlabel_id))) JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) JOIN taxonverbatim accepted_taxonverbatim ON ((accepted_taxonverbatim.taxonlabel_id = accepted_taxonlabel.taxonlabel_id))) LEFT JOIN aggregateoccurrence USING (taxonoccurrence_id)) LEFT JOIN plantobservation USING (aggregateoccurrence_id)) LEFT JOIN stemobservation USING (plantobservation_id)) LEFT JOIN specimenreplicate USING (plantobservation_id)) WHERE ((datasource.creator_id = datasource.party_id) AND (NOT taxondetermination.isoriginal));
|
|
| 1792 | 2018 |
|
| 1793 | 2019 |
|
| 1794 |
ALTER TABLE "public.test_taxonomic_names".analytical_db_view OWNER TO bien;
|
|
| 2020 |
ALTER TABLE "public.test_taxonomic_names".analytical_stem_view OWNER TO bien;
|
|
| 1795 | 2021 |
|
| 1796 | 2022 |
-- |
| 1797 | 2023 |
-- Name: classcontributor; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: |
| ... | ... | |
| 2208 | 2434 |
|
| 2209 | 2435 |
|
| 2210 | 2436 |
-- |
| 2437 |
-- Name: coordinates_coordinates_id_seq; Type: SEQUENCE; Schema: public.test_taxonomic_names; Owner: bien |
|
| 2438 |
-- |
|
| 2439 |
|
|
| 2440 |
CREATE SEQUENCE coordinates_coordinates_id_seq |
|
| 2441 |
START WITH 1 |
|
| 2442 |
INCREMENT BY 1 |
|
| 2443 |
NO MINVALUE |
|
| 2444 |
NO MAXVALUE |
|
| 2445 |
CACHE 1; |
|
| 2446 |
|
|
| 2447 |
|
|
| 2448 |
ALTER TABLE "public.test_taxonomic_names".coordinates_coordinates_id_seq OWNER TO bien; |
|
| 2449 |
|
|
| 2450 |
-- |
|
| 2451 |
-- Name: coordinates_coordinates_id_seq; Type: SEQUENCE OWNED BY; Schema: public.test_taxonomic_names; Owner: bien |
|
| 2452 |
-- |
|
| 2453 |
|
|
| 2454 |
ALTER SEQUENCE coordinates_coordinates_id_seq OWNED BY coordinates.coordinates_id; |
|
| 2455 |
|
|
| 2456 |
|
|
| 2457 |
-- |
|
| 2458 |
-- Name: coordinates_coordinates_id_seq; Type: SEQUENCE SET; Schema: public.test_taxonomic_names; Owner: bien |
|
| 2459 |
-- |
|
| 2460 |
|
|
| 2461 |
SELECT pg_catalog.setval('coordinates_coordinates_id_seq', 1, false);
|
|
| 2462 |
|
|
| 2463 |
|
|
| 2464 |
-- |
|
| 2211 | 2465 |
-- Name: coverindex; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: |
| 2212 | 2466 |
-- |
| 2213 | 2467 |
|
| ... | ... | |
| 2475 | 2729 |
-- Name: location_location_id_seq; Type: SEQUENCE SET; Schema: public.test_taxonomic_names; Owner: bien |
| 2476 | 2730 |
-- |
| 2477 | 2731 |
|
| 2478 |
SELECT pg_catalog.setval('location_location_id_seq', 82, true);
|
|
| 2732 |
SELECT pg_catalog.setval('location_location_id_seq', 78, true);
|
|
| 2479 | 2733 |
|
| 2480 | 2734 |
|
| 2481 | 2735 |
-- |
| 2482 |
-- Name: locationcoords_locationcoords_id_seq; Type: SEQUENCE; Schema: public.test_taxonomic_names; Owner: bien |
|
| 2483 |
-- |
|
| 2484 |
|
|
| 2485 |
CREATE SEQUENCE locationcoords_locationcoords_id_seq |
|
| 2486 |
START WITH 1 |
|
| 2487 |
INCREMENT BY 1 |
|
| 2488 |
NO MINVALUE |
|
| 2489 |
NO MAXVALUE |
|
| 2490 |
CACHE 1; |
|
| 2491 |
|
|
| 2492 |
|
|
| 2493 |
ALTER TABLE "public.test_taxonomic_names".locationcoords_locationcoords_id_seq OWNER TO bien; |
|
| 2494 |
|
|
| 2495 |
-- |
|
| 2496 |
-- Name: locationcoords_locationcoords_id_seq; Type: SEQUENCE OWNED BY; Schema: public.test_taxonomic_names; Owner: bien |
|
| 2497 |
-- |
|
| 2498 |
|
|
| 2499 |
ALTER SEQUENCE locationcoords_locationcoords_id_seq OWNED BY locationcoords.locationcoords_id; |
|
| 2500 |
|
|
| 2501 |
|
|
| 2502 |
-- |
|
| 2503 |
-- Name: locationcoords_locationcoords_id_seq; Type: SEQUENCE SET; Schema: public.test_taxonomic_names; Owner: bien |
|
| 2504 |
-- |
|
| 2505 |
|
|
| 2506 |
SELECT pg_catalog.setval('locationcoords_locationcoords_id_seq', 1, false);
|
|
| 2507 |
|
|
| 2508 |
|
|
| 2509 |
-- |
|
| 2510 | 2736 |
-- Name: locationevent_locationevent_id_seq; Type: SEQUENCE; Schema: public.test_taxonomic_names; Owner: bien |
| 2511 | 2737 |
-- |
| 2512 | 2738 |
|
| ... | ... | |
| 2531 | 2757 |
-- Name: locationevent_locationevent_id_seq; Type: SEQUENCE SET; Schema: public.test_taxonomic_names; Owner: bien |
| 2532 | 2758 |
-- |
| 2533 | 2759 |
|
| 2534 |
SELECT pg_catalog.setval('locationevent_locationevent_id_seq', 82, true);
|
|
| 2760 |
SELECT pg_catalog.setval('locationevent_locationevent_id_seq', 78, true);
|
|
| 2535 | 2761 |
|
| 2536 | 2762 |
|
| 2537 | 2763 |
-- |
| ... | ... | |
| 2852 | 3078 |
-- Name: party_party_id_seq; Type: SEQUENCE SET; Schema: public.test_taxonomic_names; Owner: bien |
| 2853 | 3079 |
-- |
| 2854 | 3080 |
|
| 2855 |
SELECT pg_catalog.setval('party_party_id_seq', 69, true);
|
|
| 3081 |
SELECT pg_catalog.setval('party_party_id_seq', 66, true);
|
|
| 2856 | 3082 |
|
| 2857 | 3083 |
|
| 2858 | 3084 |
-- |
| ... | ... | |
| 2900 | 3126 |
|
| 2901 | 3127 |
|
| 2902 | 3128 |
-- |
| 2903 |
-- Name: place; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: |
|
| 2904 |
-- |
|
| 2905 |
|
|
| 2906 |
CREATE TABLE place ( |
|
| 2907 |
place_id integer NOT NULL, |
|
| 2908 |
parent_id integer, |
|
| 2909 |
rank placerank NOT NULL, |
|
| 2910 |
placename text NOT NULL, |
|
| 2911 |
placecode text, |
|
| 2912 |
placedescription text, |
|
| 2913 |
accessioncode text |
|
| 2914 |
); |
|
| 2915 |
|
|
| 2916 |
|
|
| 2917 |
ALTER TABLE "public.test_taxonomic_names".place OWNER TO bien; |
|
| 2918 |
|
|
| 2919 |
-- |
|
| 2920 |
-- Name: TABLE place; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien |
|
| 2921 |
-- |
|
| 2922 |
|
|
| 2923 |
COMMENT ON TABLE place IS 'An official, named place. Can be at any level in the geographic hierarchy. Note that the placename stores only one rank (e.g. country) of the full path to the place. The higher-level ranks are stored in the place''s chain of parent_id ancestors. |
|
| 2924 |
|
|
| 2925 |
Equivalent to VegBank''s namedPlace table.'; |
|
| 2926 |
|
|
| 2927 |
|
|
| 2928 |
-- |
|
| 2929 |
-- Name: place_ancestor; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: |
|
| 2930 |
-- |
|
| 2931 |
|
|
| 2932 |
CREATE TABLE place_ancestor ( |
|
| 2933 |
place_id integer NOT NULL, |
|
| 2934 |
ancestor_id integer NOT NULL |
|
| 2935 |
); |
|
| 2936 |
|
|
| 2937 |
|
|
| 2938 |
ALTER TABLE "public.test_taxonomic_names".place_ancestor OWNER TO bien; |
|
| 2939 |
|
|
| 2940 |
-- |
|
| 2941 |
-- Name: TABLE place_ancestor; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien |
|
| 2942 |
-- |
|
| 2943 |
|
|
| 2944 |
COMMENT ON TABLE place_ancestor IS 'place''s ancestor cross link table.'; |
|
| 2945 |
|
|
| 2946 |
|
|
| 2947 |
-- |
|
| 2948 | 3129 |
-- Name: place_place_id_seq; Type: SEQUENCE; Schema: public.test_taxonomic_names; Owner: bien |
| 2949 | 3130 |
-- |
| 2950 | 3131 |
|
| ... | ... | |
| 3015 | 3196 |
|
| 3016 | 3197 |
|
| 3017 | 3198 |
-- |
| 3018 |
-- Name: placepath_placepath_id_seq; Type: SEQUENCE; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 3199 |
-- Name: placename; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace:
|
|
| 3019 | 3200 |
-- |
| 3020 | 3201 |
|
| 3021 |
CREATE SEQUENCE placepath_placepath_id_seq |
|
| 3202 |
CREATE TABLE placename ( |
|
| 3203 |
placename_id integer NOT NULL, |
|
| 3204 |
parent_id integer, |
|
| 3205 |
rank placerank NOT NULL, |
|
| 3206 |
placename text NOT NULL |
|
| 3207 |
); |
|
| 3208 |
|
|
| 3209 |
|
|
| 3210 |
ALTER TABLE "public.test_taxonomic_names".placename OWNER TO bien; |
|
| 3211 |
|
|
| 3212 |
-- |
|
| 3213 |
-- Name: TABLE placename; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien |
|
| 3214 |
-- |
|
| 3215 |
|
|
| 3216 |
COMMENT ON TABLE placename IS 'An official, named placename. Can be at any level in the geographic hierarchy. Note that the placename stores only one rank (e.g. country) of the full path to the placename. The higher-level ranks are stored in the placename''s chain of parent_id ancestors. |
|
| 3217 |
|
|
| 3218 |
Equivalent to VegBank''s namedPlace table.'; |
|
| 3219 |
|
|
| 3220 |
|
|
| 3221 |
-- |
|
| 3222 |
-- Name: placename_ancestor; Type: TABLE; Schema: public.test_taxonomic_names; Owner: bien; Tablespace: |
|
| 3223 |
-- |
|
| 3224 |
|
|
| 3225 |
CREATE TABLE placename_ancestor ( |
|
| 3226 |
placename_id integer NOT NULL, |
|
| 3227 |
ancestor_id integer NOT NULL |
|
| 3228 |
); |
|
| 3229 |
|
|
| 3230 |
|
|
| 3231 |
ALTER TABLE "public.test_taxonomic_names".placename_ancestor OWNER TO bien; |
|
| 3232 |
|
|
| 3233 |
-- |
|
| 3234 |
-- Name: TABLE placename_ancestor; Type: COMMENT; Schema: public.test_taxonomic_names; Owner: bien |
|
| 3235 |
-- |
|
| 3236 |
|
|
| 3237 |
COMMENT ON TABLE placename_ancestor IS 'placename''s ancestor cross link table.'; |
|
| 3238 |
|
|
| 3239 |
|
|
| 3240 |
-- |
|
| 3241 |
-- Name: placename_placename_id_seq; Type: SEQUENCE; Schema: public.test_taxonomic_names; Owner: bien |
|
| 3242 |
-- |
|
| 3243 |
|
|
| 3244 |
CREATE SEQUENCE placename_placename_id_seq |
|
| 3022 | 3245 |
START WITH 1 |
| 3023 | 3246 |
INCREMENT BY 1 |
| 3024 | 3247 |
NO MINVALUE |
| ... | ... | |
| 3026 | 3249 |
CACHE 1; |
| 3027 | 3250 |
|
| 3028 | 3251 |
|
| 3029 |
ALTER TABLE "public.test_taxonomic_names".placepath_placepath_id_seq OWNER TO bien;
|
|
| 3252 |
ALTER TABLE "public.test_taxonomic_names".placename_placename_id_seq OWNER TO bien;
|
|
| 3030 | 3253 |
|
| 3031 | 3254 |
-- |
| 3032 |
-- Name: placepath_placepath_id_seq; Type: SEQUENCE OWNED BY; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 3255 |
-- Name: placename_placename_id_seq; Type: SEQUENCE OWNED BY; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 3033 | 3256 |
-- |
| 3034 | 3257 |
|
| 3035 |
ALTER SEQUENCE placepath_placepath_id_seq OWNED BY placepath.placepath_id;
|
|
| 3258 |
ALTER SEQUENCE placename_placename_id_seq OWNED BY placename.placename_id;
|
|
| 3036 | 3259 |
|
| 3037 | 3260 |
|
| 3038 | 3261 |
-- |
| 3039 |
-- Name: placepath_placepath_id_seq; Type: SEQUENCE SET; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 3262 |
-- Name: placename_placename_id_seq; Type: SEQUENCE SET; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 3040 | 3263 |
-- |
| 3041 | 3264 |
|
| 3042 |
SELECT pg_catalog.setval('placepath_placepath_id_seq', 1, false);
|
|
| 3265 |
SELECT pg_catalog.setval('placename_placename_id_seq', 1, false);
|
|
| 3043 | 3266 |
|
| 3044 | 3267 |
|
| 3045 | 3268 |
-- |
| ... | ... | |
| 3920 | 4143 |
-- Name: taxondetermination_taxondetermination_id_seq; Type: SEQUENCE SET; Schema: public.test_taxonomic_names; Owner: bien |
| 3921 | 4144 |
-- |
| 3922 | 4145 |
|
| 3923 |
SELECT pg_catalog.setval('taxondetermination_taxondetermination_id_seq', 41, true);
|
|
| 4146 |
SELECT pg_catalog.setval('taxondetermination_taxondetermination_id_seq', 39, true);
|
|
| 3924 | 4147 |
|
| 3925 | 4148 |
|
| 3926 | 4149 |
-- |
| ... | ... | |
| 3975 | 4198 |
-- Name: taxonlabel_taxonlabel_id_seq; Type: SEQUENCE SET; Schema: public.test_taxonomic_names; Owner: bien |
| 3976 | 4199 |
-- |
| 3977 | 4200 |
|
| 3978 |
SELECT pg_catalog.setval('taxonlabel_taxonlabel_id_seq', 1085, true);
|
|
| 4201 |
SELECT pg_catalog.setval('taxonlabel_taxonlabel_id_seq', 621, true);
|
|
| 3979 | 4202 |
|
| 3980 | 4203 |
|
| 3981 | 4204 |
-- |
| ... | ... | |
| 4044 | 4267 |
-- Name: taxonoccurrence_taxonoccurrence_id_seq; Type: SEQUENCE SET; Schema: public.test_taxonomic_names; Owner: bien |
| 4045 | 4268 |
-- |
| 4046 | 4269 |
|
| 4047 |
SELECT pg_catalog.setval('taxonoccurrence_taxonoccurrence_id_seq', 82, true);
|
|
| 4270 |
SELECT pg_catalog.setval('taxonoccurrence_taxonoccurrence_id_seq', 78, true);
|
|
| 4048 | 4271 |
|
| 4049 | 4272 |
|
| 4050 | 4273 |
-- |
| ... | ... | |
| 4119 | 4342 |
-- Name: taxonverbatim_taxonverbatim_id_seq; Type: SEQUENCE SET; Schema: public.test_taxonomic_names; Owner: bien |
| 4120 | 4343 |
-- |
| 4121 | 4344 |
|
| 4122 |
SELECT pg_catalog.setval('taxonverbatim_taxonverbatim_id_seq', 145, true);
|
|
| 4345 |
SELECT pg_catalog.setval('taxonverbatim_taxonverbatim_id_seq', 111, true);
|
|
| 4123 | 4346 |
|
| 4124 | 4347 |
|
| 4125 | 4348 |
-- |
| ... | ... | |
| 4380 | 4603 |
|
| 4381 | 4604 |
|
| 4382 | 4605 |
-- |
| 4606 |
-- Name: coordinates_id; Type: DEFAULT; Schema: public.test_taxonomic_names; Owner: bien |
|
| 4607 |
-- |
|
| 4608 |
|
|
| 4609 |
ALTER TABLE coordinates ALTER COLUMN coordinates_id SET DEFAULT nextval('coordinates_coordinates_id_seq'::regclass);
|
|
| 4610 |
|
|
| 4611 |
|
|
| 4612 |
-- |
|
| 4383 | 4613 |
-- Name: coverindex_id; Type: DEFAULT; Schema: public.test_taxonomic_names; Owner: bien |
| 4384 | 4614 |
-- |
| 4385 | 4615 |
|
| ... | ... | |
| 4422 | 4652 |
|
| 4423 | 4653 |
|
| 4424 | 4654 |
-- |
| 4425 |
-- Name: locationcoords_id; Type: DEFAULT; Schema: public.test_taxonomic_names; Owner: bien |
|
| 4426 |
-- |
|
| 4427 |
|
|
| 4428 |
ALTER TABLE locationcoords ALTER COLUMN locationcoords_id SET DEFAULT nextval('locationcoords_locationcoords_id_seq'::regclass);
|
|
| 4429 |
|
|
| 4430 |
|
|
| 4431 |
-- |
|
| 4432 | 4655 |
-- Name: locationevent_id; Type: DEFAULT; Schema: public.test_taxonomic_names; Owner: bien |
| 4433 | 4656 |
-- |
| 4434 | 4657 |
|
| ... | ... | |
| 4513 | 4736 |
|
| 4514 | 4737 |
|
| 4515 | 4738 |
-- |
| 4516 |
-- Name: placepath_id; Type: DEFAULT; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 4739 |
-- Name: placename_id; Type: DEFAULT; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 4517 | 4740 |
-- |
| 4518 | 4741 |
|
| 4519 |
ALTER TABLE placepath ALTER COLUMN placepath_id SET DEFAULT nextval('placepath_placepath_id_seq'::regclass);
|
|
| 4742 |
ALTER TABLE placename ALTER COLUMN placename_id SET DEFAULT nextval('placename_placename_id_seq'::regclass);
|
|
| 4520 | 4743 |
|
| 4521 | 4744 |
|
| 4522 | 4745 |
-- |
| ... | ... | |
| 4732 | 4955 |
|
| 4733 | 4956 |
|
| 4734 | 4957 |
-- |
| 4958 |
-- Data for Name: analytical_aggregate; Type: TABLE DATA; Schema: public.test_taxonomic_names; Owner: bien |
|
| 4959 |
-- |
|
| 4960 |
|
|
| 4961 |
COPY analytical_aggregate ("institutionCode", country, "stateProvince", county, "decimalLatitude", "decimalLongitude", "plotName", "elevationInMeters", "plotArea_ha", "samplingProtocol", "dateCollected", family, genus, "speciesBinomial", "scientificName", "scientificNameAuthorship", "scientificNameWithMorphospecies", "coverPercent", "individualCount", "individualCount_1cm_or_more", "individualCount_2_5cm_or_more", "individualCount_10cm_or_more") FROM stdin;
|
|
| 4962 |
\. |
|
| 4963 |
|
|
| 4964 |
|
|
| 4965 |
-- |
|
| 4966 |
-- Data for Name: analytical_stem; Type: TABLE DATA; Schema: public.test_taxonomic_names; Owner: bien |
|
| 4967 |
-- |
|
| 4968 |
|
|
| 4969 |
COPY analytical_stem ("institutionCode", country, "stateProvince", county, "decimalLatitude", "decimalLongitude", "plotName", "elevationInMeters", "plotArea_ha", "samplingProtocol", "dateCollected", family, genus, "speciesBinomial", "scientificName", "scientificNameAuthorship", "scientificNameWithMorphospecies", "identifiedBy", "growthForm", cultivated, "cultivatedBasis", "coverPercent", "diameterBreastHeight_cm", height_m, tag, "organismX_m", "organismY_m", "recordedBy", "recordNumber") FROM stdin;
|
|
| 4970 |
\. |
|
| 4971 |
|
|
| 4972 |
|
|
| 4973 |
-- |
|
| 4735 | 4974 |
-- Data for Name: classcontributor; Type: TABLE DATA; Schema: public.test_taxonomic_names; Owner: bien |
| 4736 | 4975 |
-- |
| 4737 | 4976 |
|
| ... | ... | |
| 4804 | 5043 |
|
| 4805 | 5044 |
|
| 4806 | 5045 |
-- |
| 5046 |
-- Data for Name: coordinates; Type: TABLE DATA; Schema: public.test_taxonomic_names; Owner: bien |
|
| 5047 |
-- |
|
| 5048 |
|
|
| 5049 |
COPY coordinates (coordinates_id, creator_id, latitude_deg, longitude_deg, verbatimlatitude, verbatimlongitude, verbatimcoordinates, footprintgeometry_dwc, coordsaccuracy_deg) FROM stdin; |
|
| 5050 |
\. |
|
| 5051 |
|
|
| 5052 |
|
|
| 5053 |
-- |
|
| 4807 | 5054 |
-- Data for Name: coverindex; Type: TABLE DATA; Schema: public.test_taxonomic_names; Owner: bien |
| 4808 | 5055 |
-- |
| 4809 | 5056 |
|
| ... | ... | |
| 4852 | 5099 |
|
| 4853 | 5100 |
|
| 4854 | 5101 |
-- |
| 4855 |
-- Data for Name: locationcoords; Type: TABLE DATA; Schema: public.test_taxonomic_names; Owner: bien |
|
| 4856 |
-- |
|
| 4857 |
|
|
| 4858 |
COPY locationcoords (locationcoords_id, location_id, latitude_deg, longitude_deg, verbatimlatitude, verbatimlongitude, verbatimcoordinates, footprintgeometry_dwc, coordsaccuracy_deg, identifier_id, determinationdate, isoriginal, iscurrent, calculated) FROM stdin; |
|
| 4859 |
\. |
|
| 4860 |
|
|
| 4861 |
|
|
| 4862 |
-- |
|
| 4863 | 5102 |
-- Data for Name: locationevent; Type: TABLE DATA; Schema: public.test_taxonomic_names; Owner: bien |
| 4864 | 5103 |
-- |
| 4865 | 5104 |
|
| ... | ... | |
| 4887 | 5126 |
-- Data for Name: locationplace; Type: TABLE DATA; Schema: public.test_taxonomic_names; Owner: bien |
| 4888 | 5127 |
-- |
| 4889 | 5128 |
|
| 4890 |
COPY locationplace (locationplace_id, location_id, placepath_id, identifier_id) FROM stdin;
|
|
| 5129 |
COPY locationplace (locationplace_id, location_id, place_id, identifier_id) FROM stdin; |
|
| 4891 | 5130 |
\. |
| 4892 | 5131 |
|
| 4893 | 5132 |
|
| ... | ... | |
| 4930 | 5169 |
COPY party (party_id, creator_id, fullname, salutation, givenname, middlename, surname, organizationname, currentname_id, contactinstructions, email, partytype, partypublic, d_obscount, accessioncode) FROM stdin; |
| 4931 | 5170 |
1 1 \N \N \N \N \N TNRS \N \N \N \N t \N \N |
| 4932 | 5171 |
2 2 \N \N \N \N \N NCBI \N \N \N \N t \N \N |
| 4933 |
54 54 \N \N \N \N \N test_taxonomic_names \N \N \N \N t \N \N
|
|
| 5172 |
51 51 \N \N \N \N \N test_taxonomic_names \N \N \N \N t \N \N
|
|
| 4934 | 5173 |
\. |
| 4935 | 5174 |
|
| 4936 | 5175 |
|
| ... | ... | |
| 4946 | 5185 |
-- Data for Name: place; Type: TABLE DATA; Schema: public.test_taxonomic_names; Owner: bien |
| 4947 | 5186 |
-- |
| 4948 | 5187 |
|
| 4949 |
COPY place (place_id, parent_id, rank, placename, placecode, placedescription, accessioncode) FROM stdin;
|
|
| 5188 |
COPY place (place_id, creator_id, placecode, matched_place_id, coordinates_id, placename_id, continent, country, stateprovince, county, description) FROM stdin;
|
|
| 4950 | 5189 |
\. |
| 4951 | 5190 |
|
| 4952 | 5191 |
|
| 4953 | 5192 |
-- |
| 4954 |
-- Data for Name: place_ancestor; Type: TABLE DATA; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 5193 |
-- Data for Name: placecorrelation; Type: TABLE DATA; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 4955 | 5194 |
-- |
| 4956 | 5195 |
|
| 4957 |
COPY place_ancestor (place_id, ancestor_id) FROM stdin;
|
|
| 5196 |
COPY placecorrelation (placecorrelation_id, parentplace_id, childplace_id, placeconvergence) FROM stdin;
|
|
| 4958 | 5197 |
\. |
| 4959 | 5198 |
|
| 4960 | 5199 |
|
| 4961 | 5200 |
-- |
| 4962 |
-- Data for Name: placecorrelation; Type: TABLE DATA; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 5201 |
-- Data for Name: placename; Type: TABLE DATA; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 4963 | 5202 |
-- |
| 4964 | 5203 |
|
| 4965 |
COPY placecorrelation (placecorrelation_id, parentplace_id, childplace_id, placeconvergence) FROM stdin;
|
|
| 5204 |
COPY placename (placename_id, parent_id, rank, placename) FROM stdin;
|
|
| 4966 | 5205 |
\. |
| 4967 | 5206 |
|
| 4968 | 5207 |
|
| 4969 | 5208 |
-- |
| 4970 |
-- Data for Name: placepath; Type: TABLE DATA; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 5209 |
-- Data for Name: placename_ancestor; Type: TABLE DATA; Schema: public.test_taxonomic_names; Owner: bien
|
|
| 4971 | 5210 |
-- |
| 4972 | 5211 |
|
| 4973 |
COPY placepath (placepath_id, creator_id, placecode, matched_placepath_id, place_id, continent, country, stateprovince, county, municipality, site, otherranks) FROM stdin;
|
|
| 5212 |
COPY placename_ancestor (placename_id, ancestor_id) FROM stdin;
|
|
| 4974 | 5213 |
\. |
| 4975 | 5214 |
|
| 4976 | 5215 |
|
| ... | ... | |
| 5139 | 5378 |
-- |
| 5140 | 5379 |
|
| 5141 | 5380 |
COPY taxonlabel (taxonlabel_id, creator_id, sourceaccessioncode, creationdate, taxonstatus, canon_label_id, matched_label_id, matched_label_fit_fraction, parent_id, taxonepithet, rank, taxonomicname, accessioncode) FROM stdin; |
| 5142 |
2 2 \N \N \N 2 \N \N \N \N \N Asteraceae \N
|
|
| 5143 |
4 1 \N \N \N 4 2 \N \N Asteraceae family \N \N
|
|
| 5381 |
2 2 \N \N \N \N \N \N \N \N \N Asteraceae \N
|
|
| 5382 |
4 1 \N \N \N \N 2 \N \N Asteraceae family \N \N
|
|
| 5144 | 5383 |
20 1 http://www.tropicos.org/Name/50307371 \N Accepted 20 20 \N 4 \N family Asteraceae Asteraceae Bercht. & J. Presl \N |
| 5145 |
41 2 \N \N \N 41 \N \N \N \N \N Poa \N |
|
| 5146 |
42 2 \N \N \N 42 \N \N \N \N \N Poaceae \N |
|
| 5147 |
44 1 \N \N \N 44 42 \N \N Poaceae family \N \N |
|
| 5148 |
46 1 \N \N \N 46 41 \N 44 Poa genus \N \N |
|
| 5149 |
48 1 \N \N \N 48 \N \N 46 annua species \N \N |
|
| 5150 |
58 1 \N \N \N 58 \N \N 48 annua variety \N \N |
|
| 5151 |
60 1 http://www.tropicos.org/Name/25517736 \N Accepted 60 60 \N 58 \N variety Poaceae Poa annua var. annua \N |
|
| 5152 |
88 1 \N \N \N 88 \N \N 46 infirma species \N \N |
|
| 5153 |
100 1 http://www.tropicos.org/Name/25514158 \N Accepted 100 100 \N 88 \N species Poaceae Poa infirma Kunth \N |
|
| 5154 |
122 2 \N \N \N 122 \N \N \N \N \N Caryophyllaceae \N |
|
| 5155 |
124 1 \N \N \N 124 122 \N \N Caryophyllaceae family \N \N |
|
| 5156 |
140 1 http://www.tropicos.org/Name/42000077 \N Accepted 140 140 \N 124 \N family Caryophyllaceae Caryophyllaceae Juss. \N |
|
| 5157 |
160 1 \N \N \N 160 140 1 \N \N \N Caryophyllaceae \N |
|
| 5158 |
162 2 \N \N \N 162 \N \N \N \N \N Compositae \N |
|
| 5159 |
164 1 \N \N \N 164 162 \N \N Compositae family \N \N |
|
| 5160 |
180 1 http://www.tropicos.org/Name/50255940 \N Synonym 20 20 \N 164 \N family Compositae Giseke \N |
|
| 5161 |
200 1 \N \N \N 200 180 1 \N \N \N Compositae \N |
|
| 5162 |
240 1 \N \N \N 240 180 1 \N indet. sp.1 \N Compositae indet. sp.1 \N |
|
| 5163 |
242 2 \N \N \N 242 \N \N \N \N \N Fabaceae \N |
|
| 5164 |
244 1 \N \N \N 244 242 \N \N Fabaceae family \N \N |
|
| 5165 |
260 1 http://www.tropicos.org/Name/42000184 \N Accepted 260 260 \N 244 \N family Fabaceae Fabaceae Lindl. \N |
|
| 5166 |
280 1 \N \N \N 280 260 1 \N \N \N Fabaceae \N |
|
| 5167 |
320 1 \N \N \N 320 260 1 \N Boyle#6500 \N Fabaceae Boyle#6500 \N |
|
| 5168 |
321 2 \N \N \N 321 \N \N \N \N \N Inga \N |
|
| 5169 |
326 1 \N \N \N 326 321 \N 244 Inga genus \N \N |
|
| 5170 |
340 1 http://www.tropicos.org/Name/40031040 \N Accepted 340 340 \N 326 \N genus Fabaceae Inga Mill. \N |
|
| 5171 |
360 1 \N \N \N 360 340 1 \N "fuzzy leaf" \N Fabaceae Inga "fuzzy leaf" \N |
|
| 5172 |
400 1 \N \N \N 400 340 1 \N sp.3 \N Fabaceae Inga sp.3 \N |
|
| 5173 |
440 1 \N \N \N 440 260 1 \N unknown #2 \N Fabaceae unknown #2 \N |
|
| 5174 |
480 1 \N \N \N 480 340 1 \N \N \N Inga \N |
|
| 5175 |
500 1 http://www.tropicos.org/Name/40012319 \N Accepted 500 500 \N 46 \N genus Poaceae Poa L. \N |
|
| 5176 |
520 1 \N \N \N 520 500 1 \N \N \N Poa \N |
|
| 5177 |
540 1 http://www.tropicos.org/Name/25509881 \N Accepted 540 540 \N 48 \N species Poaceae Poa annua L. \N |
|
| 5178 |
560 1 \N \N \N 560 540 1 \N \N \N Poa annua \N |
|
| 5179 |
600 1 \N \N \N 600 540 1 \N \N \N Poa annua L. \N |
|
| 5180 |
618 1 \N \N \N 618 \N \N 48 lanuginosa forma \N \N |
|
| 5181 |
620 1 http://www.tropicos.org/Name/50267771 \N Synonym 60 60 \N 618 \N forma Poaceae Poa annua fo. lanuginosa Sennen \N |
|
| 5182 |
640 1 \N \N \N 640 620 1 \N \N \N Poa annua fo. lanuginosa \N |
|
| 5183 |
680 1 \N \N \N 680 500 1 \N annua ssp. exilis \N Poa annua ssp. exilis \N |
|
| 5184 |
698 1 \N \N \N 698 \N \N 48 exilis subspecies \N \N |
|
| 5185 |
700 1 http://www.tropicos.org/Name/50063800 \N Synonym 100 100 \N 698 \N subspecies Poaceae Poa annua subsp. exilis (Tomm. ex Freyn) Asch. & Graebn. \N |
|
| 5186 |
720 1 \N \N \N 720 700 1 \N \N \N Poa annua subsp. exilis \N |
|
| 5187 |
738 1 \N \N \N 738 \N \N 48 minima subvariety \N \N |
|
| 5188 |
740 1 http://www.tropicos.org/Name/50158097 \N Accepted 740 740 \N 738 \N subvariety Poaceae Poa annua subvar. minima (Schur) Asch. & Graebn. \N |
|
| 5189 |
760 1 \N \N \N 760 740 1 \N \N \N Poa annua subvar. minima \N |
|
| 5190 |
778 1 \N \N \N 778 \N \N 48 eriolepis variety \N \N |
|
| 5191 |
780 1 http://www.tropicos.org/Name/50119145 \N Synonym 540 540 \N 778 \N variety Poaceae Poa annua var. eriolepis E. Desv. \N |
|
| 5192 |
800 1 \N \N \N 800 780 1 \N \N \N Poa annua var. eriolepis \N |
|
| 5193 |
820 1 http://www.tropicos.org/Name/42000357 \N Accepted 820 820 \N 44 \N family Poaceae Poaceae Barnhart \N |
|
| 5194 |
840 1 \N \N \N 840 820 1 \N \N \N Poaceae \N |
|
| 5195 |
841 2 \N \N \N 841 \N \N \N \N \N Silene \N |
|
| 5196 |
846 1 \N \N \N 846 841 \N 124 Silene genus \N \N |
|
| 5197 |
860 1 http://www.tropicos.org/Name/40000236 \N Accepted 860 860 \N 846 \N genus Caryophyllaceae Silene L. \N |
|
| 5198 |
880 1 \N \N \N 880 860 1 \N \N \N Silene \N |
|
| 5199 |
888 1 \N \N \N 888 \N \N 846 scouleri species \N \N |
|
| 5200 |
898 1 \N \N \N 898 \N \N 888 pringlei subspecies \N \N |
|
| 5201 |
900 1 http://www.tropicos.org/Name/6303627 \N Accepted 900 900 \N 898 \N subspecies Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire \N |
|
| 5202 |
920 1 \N \N \N 920 900 1 \N var. grisea \N Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire \N |
|
| 5203 |
960 1 \N \N \N 960 900 1 \N var. grisea \N Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire \N |
|
| 5204 |
980 1 \N \N \N 980 \N \N \N \N \N No suitable matches found. \N |
|
| 5205 |
1000 1 \N \N \N 1000 \N \N \N \N \N Fam_indet. Boyle#6501 \N |
|
| 5206 |
1043 54 1 \N \N 1043 560 \N \N \N \N Poa annua \N |
|
| 5207 |
1046 54 2 \N \N 1046 600 \N \N \N \N Poa annua L. \N |
|
| 5208 |
1049 54 3 \N \N 1049 800 \N \N \N \N Poa annua var. eriolepis \N |
|
| 5209 |
1052 54 4 \N \N 1052 720 \N \N \N \N Poa annua subsp. exilis \N |
|
| 5210 |
1055 54 5 \N \N 1055 680 \N \N \N \N Poa annua ssp. exilis \N |
|
| 5211 |
1058 54 6 \N \N 1058 760 \N \N \N \N Poa annua subvar. minima \N |
|
| 5212 |
1061 54 7 \N \N 1061 640 \N \N \N \N Poa annua fo. lanuginosa \N |
|
| 5213 |
1064 54 8 \N \N 1064 960 \N \N \N \N Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire \N |
|
| 5214 |
1067 54 9 \N \N 1067 920 \N \N \N \N Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire \N |
|
| 5215 |
1070 54 10 \N \N 1070 400 \N \N \N \N Fabaceae Inga sp.3 \N |
|
| 5216 |
1073 54 11 \N \N 1073 360 \N \N \N \N Fabaceae Inga "fuzzy leaf" \N |
|
| 5217 |
1076 54 12 \N \N 1076 440 \N \N \N \N Fabaceae unknown #2 \N |
|
| 5218 |
1079 54 13 \N \N 1079 320 \N \N \N \N Fabaceae Boyle#6500 \N |
|
| 5219 |
1082 54 14 \N \N 1082 1000 \N \N \N \N Fam_indet. Boyle#6501 \N |
|
| 5220 |
1085 54 15 \N \N 1085 240 \N \N \N \N Compositae indet. sp.1 \N |
|
| 5384 |
25 2 \N \N \N \N \N \N \N \N \N Poa \N |
|
| 5385 |
26 2 \N \N \N \N \N \N \N \N \N Poaceae \N |
|
| 5386 |
28 1 \N \N \N \N 26 \N \N Poaceae family \N \N |
|
| 5387 |
30 1 \N \N \N \N 25 \N 28 Poa genus \N \N |
|
| 5388 |
32 1 \N \N \N \N \N \N 30 annua species \N \N |
|
| 5389 |
42 1 \N \N \N \N \N \N 32 annua variety \N \N |
|
| 5390 |
44 1 http://www.tropicos.org/Name/25517736 \N Accepted 44 44 \N 42 \N variety Poaceae Poa annua var. annua \N |
|
| 5391 |
56 1 \N \N \N \N \N \N 30 infirma species \N \N |
|
| 5392 |
68 1 http://www.tropicos.org/Name/25514158 \N Accepted 68 68 \N 56 \N species Poaceae Poa infirma Kunth \N |
|
| 5393 |
74 2 \N \N \N \N \N \N \N \N \N Caryophyllaceae \N |
|
| 5394 |
76 1 \N \N \N \N 74 \N \N Caryophyllaceae family \N \N |
|
| 5395 |
92 1 http://www.tropicos.org/Name/42000077 \N Accepted 92 92 \N 76 \N family Caryophyllaceae Caryophyllaceae Juss. \N |
|
| 5396 |
96 1 \N \N \N 92 92 1 \N \N \N Caryophyllaceae \N |
|
| 5397 |
98 2 \N \N \N \N \N \N \N \N \N Compositae \N |
|
| 5398 |
100 1 \N \N \N \N 98 \N \N Compositae family \N \N |
|
| 5399 |
116 1 http://www.tropicos.org/Name/50255940 \N Synonym 20 20 \N 100 \N family Compositae Giseke \N |
|
| 5400 |
120 1 \N \N \N 20 116 1 \N \N \N Compositae \N |
|
| 5401 |
144 1 \N \N \N 20 116 1 \N indet. sp.1 \N Compositae indet. sp.1 \N |
|
| 5402 |
146 2 \N \N \N \N \N \N \N \N \N Fabaceae \N |
|
| 5403 |
148 1 \N \N \N \N 146 \N \N Fabaceae family \N \N |
|
| 5404 |
164 1 http://www.tropicos.org/Name/42000184 \N Accepted 164 164 \N 148 \N family Fabaceae Fabaceae Lindl. \N |
|
| 5405 |
168 1 \N \N \N 164 164 1 \N \N \N Fabaceae \N |
|
| 5406 |
192 1 \N \N \N 164 164 1 \N Boyle#6500 \N Fabaceae Boyle#6500 \N |
|
| 5407 |
193 2 \N \N \N \N \N \N \N \N \N Inga \N |
|
| 5408 |
198 1 \N \N \N \N 193 \N 148 Inga genus \N \N |
|
| 5409 |
212 1 http://www.tropicos.org/Name/40031040 \N Accepted 212 212 \N 198 \N genus Fabaceae Inga Mill. \N |
|
| 5410 |
216 1 \N \N \N 212 212 1 \N "fuzzy leaf" \N Fabaceae Inga "fuzzy leaf" \N |
|
| 5411 |
240 1 \N \N \N 212 212 1 \N sp.3 \N Fabaceae Inga sp.3 \N |
|
| 5412 |
264 1 \N \N \N 164 164 1 \N unknown #2 \N Fabaceae unknown #2 \N |
|
| 5413 |
288 1 \N \N \N 212 212 1 \N \N \N Inga \N |
|
| 5414 |
308 1 http://www.tropicos.org/Name/40012319 \N Accepted 308 308 \N 30 \N genus Poaceae Poa L. \N |
|
| 5415 |
312 1 \N \N \N 308 308 1 \N \N \N Poa \N |
|
| 5416 |
332 1 http://www.tropicos.org/Name/25509881 \N Accepted 332 332 \N 32 \N species Poaceae Poa annua L. \N |
|
| 5417 |
336 1 \N \N \N 332 332 1 \N \N \N Poa annua \N |
|
| 5418 |
360 1 \N \N \N 332 332 1 \N \N \N Poa annua L. \N |
|
| 5419 |
378 1 \N \N \N \N \N \N 32 lanuginosa forma \N \N |
|
| 5420 |
380 1 http://www.tropicos.org/Name/50267771 \N Synonym 44 44 \N 378 \N forma Poaceae Poa annua fo. lanuginosa Sennen \N |
|
| 5421 |
384 1 \N \N \N 44 380 1 \N \N \N Poa annua fo. lanuginosa \N |
|
| 5422 |
408 1 \N \N \N 308 308 1 \N annua ssp. exilis \N Poa annua ssp. exilis \N |
|
| 5423 |
426 1 \N \N \N \N \N \N 32 exilis subspecies \N \N |
|
| 5424 |
428 1 http://www.tropicos.org/Name/50063800 \N Synonym 68 68 \N 426 \N subspecies Poaceae Poa annua subsp. exilis (Tomm. ex Freyn) Asch. & Graebn. \N |
|
| 5425 |
432 1 \N \N \N 68 428 1 \N \N \N Poa annua subsp. exilis \N |
|
| 5426 |
450 1 \N \N \N \N \N \N 32 minima subvariety \N \N |
|
| 5427 |
452 1 http://www.tropicos.org/Name/50158097 \N Accepted 452 452 \N 450 \N subvariety Poaceae Poa annua subvar. minima (Schur) Asch. & Graebn. \N |
|
| 5428 |
456 1 \N \N \N 452 452 1 \N \N \N Poa annua subvar. minima \N |
|
| 5429 |
474 1 \N \N \N \N \N \N 32 eriolepis variety \N \N |
|
| 5430 |
476 1 http://www.tropicos.org/Name/50119145 \N Synonym 332 332 \N 474 \N variety Poaceae Poa annua var. eriolepis E. Desv. \N |
|
| 5431 |
480 1 \N \N \N 332 476 1 \N \N \N Poa annua var. eriolepis \N |
|
| 5432 |
500 1 http://www.tropicos.org/Name/42000357 \N Accepted 500 500 \N 28 \N family Poaceae Poaceae Barnhart \N |
|
| 5433 |
504 1 \N \N \N 500 500 1 \N \N \N Poaceae \N |
|
| 5434 |
505 2 \N \N \N \N \N \N \N \N \N Silene \N |
|
| 5435 |
510 1 \N \N \N \N 505 \N 76 Silene genus \N \N |
|
| 5436 |
524 1 http://www.tropicos.org/Name/40000236 \N Accepted 524 524 \N 510 \N genus Caryophyllaceae Silene L. \N |
|
| 5437 |
528 1 \N \N \N 524 524 1 \N \N \N Silene \N |
|
| 5438 |
536 1 \N \N \N \N \N \N 510 scouleri species \N \N |
|
| 5439 |
546 1 \N \N \N \N \N \N 536 pringlei subspecies \N \N |
|
| 5440 |
548 1 http://www.tropicos.org/Name/6303627 \N Accepted 548 548 \N 546 \N subspecies Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire \N |
|
| 5441 |
552 1 \N \N \N 548 548 1 \N var. grisea \N Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire \N |
|
| 5442 |
576 1 \N \N \N 548 548 1 \N var. grisea \N Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire \N |
|
| 5443 |
579 51 1 \N \N 332 336 \N \N \N \N Poa annua \N |
|
| 5444 |
582 51 2 \N \N 332 360 \N \N \N \N Poa annua L. \N |
|
| 5445 |
585 51 3 \N \N 332 480 \N \N \N \N Poa annua var. eriolepis \N |
|
| 5446 |
588 51 4 \N \N 68 432 \N \N \N \N Poa annua subsp. exilis \N |
|
| 5447 |
591 51 5 \N \N 308 408 \N \N \N \N Poa annua ssp. exilis \N |
|
| 5448 |
594 51 6 \N \N 452 456 \N \N \N \N Poa annua subvar. minima \N |
|
| 5449 |
597 51 7 \N \N 44 384 \N \N \N \N Poa annua fo. lanuginosa \N |
|
| 5450 |
600 51 8 \N \N 548 576 \N \N \N \N Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire \N |
|
| 5451 |
603 51 9 \N \N 548 552 \N \N \N \N Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire \N |
|
| 5452 |
606 51 10 \N \N 212 240 \N \N \N \N Fabaceae Inga sp.3 \N |
|
| 5453 |
609 51 11 \N \N 212 216 \N \N \N \N Fabaceae Inga "fuzzy leaf" \N |
|
| 5454 |
612 51 12 \N \N 164 264 \N \N \N \N Fabaceae unknown #2 \N |
|
| 5455 |
615 51 13 \N \N 164 192 \N \N \N \N Fabaceae Boyle#6500 \N |
|
| 5456 |
616 1 \N \N \N \N \N \N \N \N \N Fam_indet. Boyle#6501 \N |
|
| 5457 |
618 51 14 \N \N \N 616 \N \N \N \N Fam_indet. Boyle#6501 \N |
|
| 5458 |
621 51 15 \N \N 20 144 \N \N \N \N Compositae indet. sp.1 \N |
|
| 5221 | 5459 |
\. |
| 5222 | 5460 |
|
| 5223 | 5461 |
|
| ... | ... | |
| 5232 | 5470 |
20 20 is included in |
| 5233 | 5471 |
20 4 is included in |
| 5234 | 5472 |
20 2 is included in |
| 5235 |
41 41 is included in |
|
| 5473 |
25 25 is included in |
|
| 5474 |
26 26 is included in |
|
| 5475 |
28 28 is included in |
|
| 5476 |
28 26 is included in |
|
| 5477 |
30 30 is included in |
|
| 5478 |
30 28 is included in |
|
| 5479 |
30 26 is included in |
|
| 5480 |
30 25 is included in |
|
| 5481 |
32 32 is included in |
|
| 5482 |
32 30 is included in |
|
| 5483 |
32 28 is included in |
|
| 5484 |
32 26 is included in |
|
| 5485 |
32 25 is included in |
|
| 5236 | 5486 |
42 42 is included in |
| 5487 |
42 32 is included in |
|
| 5488 |
42 30 is included in |
|
| 5489 |
42 28 is included in |
|
| 5490 |
42 26 is included in |
|
| 5491 |
42 25 is included in |
|
| 5237 | 5492 |
44 44 is included in |
| 5238 | 5493 |
44 42 is included in |
| 5239 |
46 46 is included in |
|
| 5240 |
46 41 is included in |
|
| 5241 |
48 48 is included in |
|
| 5242 |
48 46 is included in |
|
| 5243 |
48 41 is included in |
|
| 5244 |
58 58 is included in |
|
| 5245 |
58 48 is included in |
|
| 5246 |
58 46 is included in |
|
| 5247 |
58 41 is included in |
|
| 5248 |
60 60 is included in |
|
| 5249 |
60 58 is included in |
|
| 5250 |
60 48 is included in |
|
| 5251 |
60 46 is included in |
|
| 5252 |
60 41 is included in |
|
| 5253 |
88 88 is included in |
|
| 5254 |
88 46 is included in |
|
| 5255 |
88 41 is included in |
|
| 5494 |
44 32 is included in |
|
| 5495 |
44 30 is included in |
|
| 5496 |
44 28 is included in |
|
| 5497 |
44 26 is included in |
|
| 5498 |
44 25 is included in |
|
| 5499 |
56 56 is included in |
|
| 5500 |
56 30 is included in |
|
| 5501 |
56 28 is included in |
|
| 5502 |
56 26 is included in |
|
| 5503 |
56 25 is included in |
|
| 5504 |
68 68 is included in |
|
| 5505 |
68 56 is included in |
|
| 5506 |
68 30 is included in |
|
| 5507 |
68 28 is included in |
|
| 5508 |
68 26 is included in |
|
| 5509 |
68 25 is included in |
|
| 5510 |
74 74 is included in |
|
| 5511 |
76 76 is included in |
|
| 5512 |
76 74 is included in |
|
| 5513 |
92 92 is included in |
|
| 5514 |
92 76 is included in |
|
| 5515 |
92 74 is included in |
|
| 5516 |
96 96 is included in |
|
| 5517 |
96 92 is included in |
|
| 5518 |
96 76 is included in |
|
| 5519 |
96 74 is included in |
|
| 5520 |
98 98 is included in |
|
| 5256 | 5521 |
100 100 is included in |
| 5257 |
100 88 is included in |
|
| 5258 |
100 46 is included in |
|
| 5259 |
100 41 is included in |
|
| 5260 |
122 122 is included in |
|
| 5261 |
124 124 is included in |
|
| 5262 |
124 122 is included in |
|
| 5263 |
140 140 is included in |
|
| 5264 |
140 124 is included in |
|
| 5265 |
140 122 is included in |
|
| 5266 |
160 160 is included in |
|
| 5267 |
160 140 is included in |
|
| 5268 |
160 124 is included in |
|
| 5269 |
160 122 is included in |
|
| 5270 |
162 162 is included in |
|
| 5522 |
100 98 is included in |
|
| 5523 |
116 116 is included in |
|
| 5524 |
116 100 is included in |
|
| 5525 |
116 98 is included in |
|
| 5526 |
116 20 is included in |
|
| 5527 |
116 4 is included in |
|
| 5528 |
116 2 is included in |
|
| 5529 |
120 120 is included in |
|
| 5530 |
120 116 is included in |
|
| 5531 |
120 100 is included in |
|
| 5532 |
120 98 is included in |
|
| 5533 |
120 20 is included in |
|
| 5534 |
120 4 is included in |
|
| 5535 |
120 2 is included in |
|
| 5536 |
144 144 is included in |
|
| 5537 |
144 116 is included in |
|
| 5538 |
144 100 is included in |
|
| 5539 |
144 98 is included in |
|
| 5540 |
144 20 is included in |
|
| 5541 |
144 4 is included in |
|
| 5542 |
144 2 is included in |
|
| 5543 |
146 146 is included in |
|
| 5544 |
148 148 is included in |
|
| 5545 |
148 146 is included in |
|
| 5271 | 5546 |
164 164 is included in |
| 5272 |
164 162 is included in |
|
| 5273 |
180 180 is included in |
|
| 5274 |
180 20 is included in |
|
| 5275 |
180 4 is included in |
|
| 5276 |
180 2 is included in |
|
| 5277 |
200 200 is included in |
|
| 5278 |
200 180 is included in |
|
| 5279 |
200 20 is included in |
|
| 5280 |
200 4 is included in |
|
| 5281 |
200 2 is included in |
|
| 5547 |
164 148 is included in |
|
| 5548 |
164 146 is included in |
|
| 5549 |
168 168 is included in |
|
| 5550 |
168 164 is included in |
|
Also available in: Unified diff
Calls to `make inputs/.TNRS/cleanup`: Do `make inputs/.TNRS/tnrs_accepted/reinstall; make inputs/.TNRS/tnrs_other/reinstall` instead to use new split TNRS tables