Revision 6464
Added by Aaron Marcuse-Kubitza over 11 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) NOT NULL, |
|
| 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) NOT NULL, |
|
| 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) NOT NULL |
|
| 668 |
); |
|
| 669 |
|
|
| 670 |
|
|
| 671 |
-- |
|
| 587 | 672 |
-- Name: address; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 588 | 673 |
-- |
| 589 | 674 |
|
| ... | ... | |
| 691 | 776 |
|
| 692 | 777 |
|
| 693 | 778 |
-- |
| 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) NOT NULL, |
|
| 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) NOT NULL, |
|
| 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) NOT NULL |
|
| 775 |
); |
|
| 776 |
|
|
| 777 |
|
|
| 778 |
-- |
|
| 779 | 779 |
-- Name: analytical_aggregate_view; Type: VIEW; Schema: public; Owner: - |
| 780 | 780 |
-- |
| 781 | 781 |
|
| ... | ... | |
| 6431 | 6431 |
|
| 6432 | 6432 |
|
| 6433 | 6433 |
-- |
| 6434 |
-- Name: address; Type: ACL; Schema: public; Owner: -
|
|
| 6434 |
-- Name: analytical_stem; Type: ACL; Schema: public; Owner: -
|
|
| 6435 | 6435 |
-- |
| 6436 | 6436 |
|
| 6437 | 6437 |
|
| ... | ... | |
| 6441 | 6441 |
|
| 6442 | 6442 |
|
| 6443 | 6443 |
-- |
| 6444 |
-- Name: aggregateoccurrence; Type: ACL; Schema: public; Owner: -
|
|
| 6444 |
-- Name: address; Type: ACL; Schema: public; Owner: -
|
|
| 6445 | 6445 |
-- |
| 6446 | 6446 |
|
| 6447 | 6447 |
|
| ... | ... | |
| 6451 | 6451 |
|
| 6452 | 6452 |
|
| 6453 | 6453 |
-- |
| 6454 |
-- Name: analytical_stem; Type: ACL; Schema: public; Owner: -
|
|
| 6454 |
-- Name: aggregateoccurrence; Type: ACL; Schema: public; Owner: -
|
|
| 6455 | 6455 |
-- |
| 6456 | 6456 |
|
| 6457 | 6457 |
|
Also available in: Unified diff
schemas/vegbien.sql: analytical_stem_view: recordNumber: Combine identifying fields in taxonoccurrence, plantobservation, and stemobservation to ensure that this field is unique within the plot and not NULL