| 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
|
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