Revision 6482
Added by Aaron Marcuse-Kubitza about 12 years ago
| vegbien.my.sql | ||
|---|---|---|
| 584 | 584 |
|
| 585 | 585 |
|
| 586 | 586 |
-- |
| 587 |
-- Name: analytical_aggregate; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 588 |
-- |
|
| 589 |
|
|
| 590 |
CREATE TABLE analytical_aggregate ( |
|
| 591 |
`institutionCode` varchar(255) NOT NULL, |
|
| 592 |
country varchar(255), |
|
| 593 |
`stateProvince` varchar(255), |
|
| 594 |
county varchar(255), |
|
| 595 |
`decimalLatitude` double precision, |
|
| 596 |
`decimalLongitude` double precision, |
|
| 597 |
`coordinateUncertaintyInMeters` double precision, |
|
| 598 |
geovalid int(1), |
|
| 599 |
`isNewWorld` int(1), |
|
| 600 |
`distanceToCountry_km` double precision, |
|
| 601 |
`distanceToStateProvince_km` double precision, |
|
| 602 |
`plotName` varchar(255) NOT NULL, |
|
| 603 |
`elevationInMeters` double precision, |
|
| 604 |
`plotArea_ha` double precision, |
|
| 605 |
`samplingProtocol` varchar(255), |
|
| 606 |
`dateCollected` date NOT NULL, |
|
| 607 |
`higherPlantGroup` varchar(255), |
|
| 608 |
family varchar(255), |
|
| 609 |
genus varchar(255), |
|
| 610 |
`speciesBinomial` varchar(255), |
|
| 611 |
`scientificName` varchar(255), |
|
| 612 |
`scientificNameAuthorship` varchar(255), |
|
| 613 |
`scientificNameWithMorphospecies` varchar(255), |
|
| 614 |
threatened int(1), |
|
| 615 |
`identifiedBy` varchar(255), |
|
| 616 |
`growthForm` varchar(255), |
|
| 617 |
cultivated int(1), |
|
| 618 |
`cultivatedBasis` varchar(255), |
|
| 619 |
`coverPercent` double precision, |
|
| 620 |
`individualCount` varchar(255), |
|
| 621 |
`individualCount_1cm_or_more` varchar(255), |
|
| 622 |
`individualCount_2_5cm_or_more` varchar(255), |
|
| 623 |
`individualCount_10cm_or_more` varchar(255) |
|
| 624 |
); |
|
| 625 |
|
|
| 626 |
|
|
| 627 |
-- |
|
| 628 |
-- Name: analytical_stem; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 629 |
-- |
|
| 630 |
|
|
| 631 |
CREATE TABLE analytical_stem ( |
|
| 632 |
`institutionCode` varchar(255) NOT NULL, |
|
| 633 |
country varchar(255), |
|
| 634 |
`stateProvince` varchar(255), |
|
| 635 |
county varchar(255), |
|
| 636 |
`decimalLatitude` double precision, |
|
| 637 |
`decimalLongitude` double precision, |
|
| 638 |
`coordinateUncertaintyInMeters` double precision, |
|
| 639 |
geovalid int(1), |
|
| 640 |
`isNewWorld` int(1), |
|
| 641 |
`distanceToCountry_km` double precision, |
|
| 642 |
`distanceToStateProvince_km` double precision, |
|
| 643 |
`plotName` varchar(255) NOT NULL, |
|
| 644 |
`elevationInMeters` double precision, |
|
| 645 |
`plotArea_ha` double precision, |
|
| 646 |
`samplingProtocol` varchar(255), |
|
| 647 |
`dateCollected` date NOT NULL, |
|
| 648 |
`higherPlantGroup` varchar(255), |
|
| 649 |
family varchar(255), |
|
| 650 |
genus varchar(255), |
|
| 651 |
`speciesBinomial` varchar(255), |
|
| 652 |
`scientificName` varchar(255), |
|
| 653 |
`scientificNameAuthorship` varchar(255), |
|
| 654 |
`scientificNameWithMorphospecies` varchar(255), |
|
| 655 |
threatened int(1), |
|
| 656 |
`identifiedBy` varchar(255), |
|
| 657 |
`growthForm` varchar(255), |
|
| 658 |
cultivated int(1), |
|
| 659 |
`cultivatedBasis` varchar(255), |
|
| 660 |
`coverPercent` double precision, |
|
| 661 |
`diameterBreastHeight_cm` double precision, |
|
| 662 |
height_m double precision, |
|
| 663 |
tag varchar(255), |
|
| 664 |
`organismX_m` double precision, |
|
| 665 |
`organismY_m` double precision, |
|
| 666 |
`recordedBy` varchar(255), |
|
| 667 |
`recordNumber` varchar(255) |
|
| 668 |
); |
|
| 669 |
|
|
| 670 |
|
|
| 671 |
-- |
|
| 672 | 587 |
-- Name: address; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 673 | 588 |
-- |
| 674 | 589 |
|
| ... | ... | |
| 776 | 691 |
|
| 777 | 692 |
|
| 778 | 693 |
-- |
| 694 |
-- Name: analytical_aggregate; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 695 |
-- |
|
| 696 |
|
|
| 697 |
CREATE TABLE analytical_aggregate ( |
|
| 698 |
`institutionCode` varchar(255) NOT NULL, |
|
| 699 |
country varchar(255), |
|
| 700 |
`stateProvince` varchar(255), |
|
| 701 |
county varchar(255), |
|
| 702 |
`decimalLatitude` double precision, |
|
| 703 |
`decimalLongitude` double precision, |
|
| 704 |
`coordinateUncertaintyInMeters` double precision, |
|
| 705 |
geovalid int(1), |
|
| 706 |
`isNewWorld` int(1), |
|
| 707 |
`distanceToCountry_km` double precision, |
|
| 708 |
`distanceToStateProvince_km` double precision, |
|
| 709 |
`plotName` varchar(255) NOT NULL, |
|
| 710 |
`elevationInMeters` double precision, |
|
| 711 |
`plotArea_ha` double precision, |
|
| 712 |
`samplingProtocol` varchar(255), |
|
| 713 |
`dateCollected` date NOT NULL, |
|
| 714 |
`higherPlantGroup` varchar(255), |
|
| 715 |
family varchar(255), |
|
| 716 |
genus varchar(255), |
|
| 717 |
`speciesBinomial` varchar(255), |
|
| 718 |
`scientificName` varchar(255), |
|
| 719 |
`scientificNameAuthorship` varchar(255), |
|
| 720 |
`scientificNameWithMorphospecies` varchar(255), |
|
| 721 |
threatened int(1), |
|
| 722 |
`identifiedBy` varchar(255), |
|
| 723 |
`growthForm` varchar(255), |
|
| 724 |
cultivated int(1), |
|
| 725 |
`cultivatedBasis` varchar(255), |
|
| 726 |
`coverPercent` double precision, |
|
| 727 |
`individualCount` varchar(255), |
|
| 728 |
`individualCount_1cm_or_more` varchar(255), |
|
| 729 |
`individualCount_2_5cm_or_more` varchar(255), |
|
| 730 |
`individualCount_10cm_or_more` varchar(255) |
|
| 731 |
); |
|
| 732 |
|
|
| 733 |
|
|
| 734 |
-- |
|
| 735 |
-- Name: analytical_stem; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 736 |
-- |
|
| 737 |
|
|
| 738 |
CREATE TABLE analytical_stem ( |
|
| 739 |
`institutionCode` varchar(255) NOT NULL, |
|
| 740 |
country varchar(255), |
|
| 741 |
`stateProvince` varchar(255), |
|
| 742 |
county varchar(255), |
|
| 743 |
`decimalLatitude` double precision, |
|
| 744 |
`decimalLongitude` double precision, |
|
| 745 |
`coordinateUncertaintyInMeters` double precision, |
|
| 746 |
geovalid int(1), |
|
| 747 |
`isNewWorld` int(1), |
|
| 748 |
`distanceToCountry_km` double precision, |
|
| 749 |
`distanceToStateProvince_km` double precision, |
|
| 750 |
`plotName` varchar(255) NOT NULL, |
|
| 751 |
`elevationInMeters` double precision, |
|
| 752 |
`plotArea_ha` double precision, |
|
| 753 |
`samplingProtocol` varchar(255), |
|
| 754 |
`dateCollected` date NOT NULL, |
|
| 755 |
`higherPlantGroup` varchar(255), |
|
| 756 |
family varchar(255), |
|
| 757 |
genus varchar(255), |
|
| 758 |
`speciesBinomial` varchar(255), |
|
| 759 |
`scientificName` varchar(255), |
|
| 760 |
`scientificNameAuthorship` varchar(255), |
|
| 761 |
`scientificNameWithMorphospecies` varchar(255), |
|
| 762 |
threatened int(1), |
|
| 763 |
`identifiedBy` varchar(255), |
|
| 764 |
`growthForm` varchar(255), |
|
| 765 |
cultivated int(1), |
|
| 766 |
`cultivatedBasis` varchar(255), |
|
| 767 |
`coverPercent` double precision, |
|
| 768 |
`diameterBreastHeight_cm` double precision, |
|
| 769 |
height_m double precision, |
|
| 770 |
tag varchar(255), |
|
| 771 |
`organismX_m` double precision, |
|
| 772 |
`organismY_m` double precision, |
|
| 773 |
`recordedBy` varchar(255), |
|
| 774 |
`recordNumber` varchar(255) |
|
| 775 |
); |
|
| 776 |
|
|
| 777 |
|
|
| 778 |
-- |
|
| 779 | 779 |
-- Name: analytical_aggregate_view; Type: VIEW; Schema: public; Owner: - |
| 780 | 780 |
-- |
| 781 | 781 |
|
| ... | ... | |
| 2628 | 2628 |
|
| 2629 | 2629 |
|
| 2630 | 2630 |
-- |
| 2631 |
-- Name: soilobs; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2631 |
-- Name: soilsample; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2632 | 2632 |
-- |
| 2633 | 2633 |
|
| 2634 |
CREATE TABLE soilobs (
|
|
| 2635 |
soilobs_id int(11) NOT NULL,
|
|
| 2634 |
CREATE TABLE soilsample (
|
|
| 2635 |
soilsample_id int(11) NOT NULL,
|
|
| 2636 | 2636 |
locationevent_id int(11) NOT NULL, |
| 2637 | 2637 |
horizon varchar(255) DEFAULT 'unknown' NOT NULL, |
| 2638 | 2638 |
description varchar(255), |
| ... | ... | |
| 2661 | 2661 |
|
| 2662 | 2662 |
|
| 2663 | 2663 |
-- |
| 2664 |
-- Name: soilobs_soilobs_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
| 2664 |
-- Name: soilsample_soilsample_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
| 2665 | 2665 |
-- |
| 2666 | 2666 |
|
| 2667 | 2667 |
|
| 2668 | 2668 |
|
| 2669 | 2669 |
|
| 2670 | 2670 |
-- |
| 2671 |
-- Name: soilobs_soilobs_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
| 2671 |
-- Name: soilsample_soilsample_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
| 2672 | 2672 |
-- |
| 2673 | 2673 |
|
| 2674 | 2674 |
|
| 2675 | 2675 |
|
| 2676 | 2676 |
|
| 2677 | 2677 |
-- |
| 2678 |
-- Name: soilobs_soilobs_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
|
|
| 2678 |
-- Name: soilsample_soilsample_id_seq; Type: SEQUENCE SET; Schema: public; Owner: -
|
|
| 2679 | 2679 |
-- |
| 2680 | 2680 |
|
| 2681 | 2681 |
|
| ... | ... | |
| 3633 | 3633 |
|
| 3634 | 3634 |
|
| 3635 | 3635 |
-- |
| 3636 |
-- Name: soilobs_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3636 |
-- Name: soilsample_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3637 | 3637 |
-- |
| 3638 | 3638 |
|
| 3639 | 3639 |
|
| ... | ... | |
| 4111 | 4111 |
|
| 4112 | 4112 |
|
| 4113 | 4113 |
-- |
| 4114 |
-- Data for Name: soilobs; Type: TABLE DATA; Schema: public; Owner: -
|
|
| 4114 |
-- Data for Name: soilsample; Type: TABLE DATA; Schema: public; Owner: -
|
|
| 4115 | 4115 |
-- |
| 4116 | 4116 |
|
| 4117 | 4117 |
|
| ... | ... | |
| 4623 | 4623 |
|
| 4624 | 4624 |
|
| 4625 | 4625 |
-- |
| 4626 |
-- Name: soilobs_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 4626 |
-- Name: soilsample_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 4627 | 4627 |
-- |
| 4628 | 4628 |
|
| 4629 |
ALTER TABLE soilobs
|
|
| 4630 |
ADD CONSTRAINT soilobs_pkey PRIMARY KEY (soilobs_id);
|
|
| 4629 |
ALTER TABLE soilsample
|
|
| 4630 |
ADD CONSTRAINT soilsample_pkey PRIMARY KEY (soilsample_id);
|
|
| 4631 | 4631 |
|
| 4632 | 4632 |
|
| 4633 | 4633 |
-- |
| 4634 |
-- Name: soilobs_unique; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 4634 |
-- Name: soilsample_unique; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 4635 | 4635 |
-- |
| 4636 | 4636 |
|
| 4637 |
ALTER TABLE soilobs
|
|
| 4638 |
ADD CONSTRAINT soilobs_unique UNIQUE (locationevent_id);
|
|
| 4637 |
ALTER TABLE soilsample
|
|
| 4638 |
ADD CONSTRAINT soilsample_unique UNIQUE (locationevent_id);
|
|
| 4639 | 4639 |
|
| 4640 | 4640 |
|
| 4641 | 4641 |
-- |
| ... | ... | |
| 6047 | 6047 |
|
| 6048 | 6048 |
|
| 6049 | 6049 |
-- |
| 6050 |
-- Name: soilobs_locationevent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 6050 |
-- Name: soilsample_locationevent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 6051 | 6051 |
-- |
| 6052 | 6052 |
|
| 6053 |
ALTER TABLE soilobs
|
|
| 6054 |
ADD CONSTRAINT soilobs_locationevent_id_fkey FOREIGN KEY (locationevent_id) REFERENCES locationevent(locationevent_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 6053 |
ALTER TABLE soilsample
|
|
| 6054 |
ADD CONSTRAINT soilsample_locationevent_id_fkey FOREIGN KEY (locationevent_id) REFERENCES locationevent(locationevent_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 6055 | 6055 |
|
| 6056 | 6056 |
|
| 6057 | 6057 |
-- |
| ... | ... | |
| 6429 | 6429 |
|
| 6430 | 6430 |
|
| 6431 | 6431 |
-- |
| 6432 |
-- Name: analytical_stem; Type: ACL; Schema: public; Owner: -
|
|
| 6432 |
-- Name: address; Type: ACL; Schema: public; Owner: -
|
|
| 6433 | 6433 |
-- |
| 6434 | 6434 |
|
| 6435 | 6435 |
|
| ... | ... | |
| 6439 | 6439 |
|
| 6440 | 6440 |
|
| 6441 | 6441 |
-- |
| 6442 |
-- Name: address; Type: ACL; Schema: public; Owner: -
|
|
| 6442 |
-- Name: aggregateoccurrence; Type: ACL; Schema: public; Owner: -
|
|
| 6443 | 6443 |
-- |
| 6444 | 6444 |
|
| 6445 | 6445 |
|
| ... | ... | |
| 6449 | 6449 |
|
| 6450 | 6450 |
|
| 6451 | 6451 |
-- |
| 6452 |
-- Name: aggregateoccurrence; Type: ACL; Schema: public; Owner: -
|
|
| 6452 |
-- Name: analytical_stem; Type: ACL; Schema: public; Owner: -
|
|
| 6453 | 6453 |
-- |
| 6454 | 6454 |
|
| 6455 | 6455 |
|
| ... | ... | |
| 6929 | 6929 |
|
| 6930 | 6930 |
|
| 6931 | 6931 |
-- |
| 6932 |
-- Name: soilobs; Type: ACL; Schema: public; Owner: -
|
|
| 6932 |
-- Name: soilsample; Type: ACL; Schema: public; Owner: -
|
|
| 6933 | 6933 |
-- |
| 6934 | 6934 |
|
| 6935 | 6935 |
|
Also available in: Unified diff
schemas/vegbien.sql: Renamed soilobs to soilsample per working group discussion