Revision 9929
Added by Aaron Marcuse-Kubitza over 11 years ago
| vegbien.my.sql | ||
|---|---|---|
| 630 | 630 |
|
| 631 | 631 |
|
| 632 | 632 |
-- |
| 633 |
-- Name: analytical_stem; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 634 |
-- |
|
| 635 |
|
|
| 636 |
CREATE TABLE analytical_stem ( |
|
| 637 |
datasource varchar(255) NOT NULL, |
|
| 638 |
`institutionCode` varchar(255), |
|
| 639 |
`collectionCode` varchar(255), |
|
| 640 |
`catalogNumber` varchar(255), |
|
| 641 |
`occurrenceID` varchar(255), |
|
| 642 |
country varchar(255), |
|
| 643 |
`stateProvince` varchar(255), |
|
| 644 |
county varchar(255), |
|
| 645 |
locality varchar(255), |
|
| 646 |
`decimalLatitude` double precision, |
|
| 647 |
`decimalLongitude` double precision, |
|
| 648 |
`coordinateUncertaintyInMeters` double precision, |
|
| 649 |
`coordinateSource_bien` varchar(255), |
|
| 650 |
`georeferenceProtocol_bien` varchar(255), |
|
| 651 |
geovalid_bien int(11), |
|
| 652 |
`isNewWorld_bien` int(11), |
|
| 653 |
`projectID` varchar(255), |
|
| 654 |
`locationID` varchar(255) NOT NULL, |
|
| 655 |
`locationName` varchar(255), |
|
| 656 |
subplot varchar(255), |
|
| 657 |
`individualCode` varchar(255), |
|
| 658 |
`elevationInMeters` double precision, |
|
| 659 |
`plotArea_ha` double precision, |
|
| 660 |
`samplingProtocol` varchar(255), |
|
| 661 |
`temperature_C` double precision, |
|
| 662 |
precipitation_m double precision, |
|
| 663 |
`recordedBy` varchar(255), |
|
| 664 |
`recordNumber` varchar(255), |
|
| 665 |
`dateCollected` date, |
|
| 666 |
family_verbatim varchar(255), |
|
| 667 |
`scientificName_verbatim` varchar(255), |
|
| 668 |
`identifiedBy` varchar(255), |
|
| 669 |
`dateIdentified` date, |
|
| 670 |
`identificationRemarks` varchar(255), |
|
| 671 |
family_matched varchar(255), |
|
| 672 |
`taxonName_matched` varchar(255), |
|
| 673 |
`scientificNameAuthorship_matched` varchar(255), |
|
| 674 |
`higherPlantGroup_bien` varchar(255), |
|
| 675 |
family varchar(255), |
|
| 676 |
genus varchar(255), |
|
| 677 |
`speciesBinomialWithMorphospecies` varchar(255), |
|
| 678 |
`taxonName` varchar(255), |
|
| 679 |
`scientificNameAuthorship` varchar(255), |
|
| 680 |
`growthForm` varchar(255), |
|
| 681 |
`reproductiveCondition` varchar(255), |
|
| 682 |
threatened_bien int(11), |
|
| 683 |
cultivated_bien int(11), |
|
| 684 |
`cultivatedBasis_bien` varchar(255), |
|
| 685 |
`occurrenceRemarks` varchar(255), |
|
| 686 |
`coverPercent` double precision, |
|
| 687 |
`diameterBreastHeight_cm` double precision, |
|
| 688 |
height_m double precision, |
|
| 689 |
tag varchar(255), |
|
| 690 |
`organismX_m` double precision, |
|
| 691 |
`organismY_m` double precision, |
|
| 692 |
`taxonOccurrenceID` varchar(255), |
|
| 693 |
`authorTaxonCode` varchar(255), |
|
| 694 |
`individualObservationID` varchar(255), |
|
| 695 |
`authorStemCode` varchar(255) |
|
| 696 |
); |
|
| 697 |
|
|
| 698 |
|
|
| 699 |
-- |
|
| 700 |
-- Name: provider_count; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 701 |
-- |
|
| 702 |
|
|
| 703 |
CREATE TABLE provider_count ( |
|
| 704 |
dataprovider varchar(255) NOT NULL, |
|
| 705 |
dataset varchar(255) NOT NULL, |
|
| 706 |
records varchar(255), |
|
| 707 |
sourcetype varchar(255), |
|
| 708 |
observationtype varchar(255) |
|
| 709 |
); |
|
| 710 |
|
|
| 711 |
|
|
| 712 |
-- |
|
| 713 |
-- Name: taxon_trait; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 714 |
-- |
|
| 715 |
|
|
| 716 |
CREATE TABLE taxon_trait ( |
|
| 717 |
`scientificName` varchar(255) NOT NULL, |
|
| 718 |
`measurementType` varchar(255) NOT NULL, |
|
| 719 |
`measurementValue` varchar(255), |
|
| 720 |
`measurementUnit` varchar(255) |
|
| 721 |
); |
|
| 722 |
|
|
| 723 |
|
|
| 724 |
-- |
|
| 633 | 725 |
-- Name: address; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 634 | 726 |
-- |
| 635 | 727 |
|
| ... | ... | |
| 736 | 828 |
|
| 737 | 829 |
|
| 738 | 830 |
-- |
| 739 |
-- Name: analytical_stem; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 740 |
-- |
|
| 741 |
|
|
| 742 |
CREATE TABLE analytical_stem ( |
|
| 743 |
datasource varchar(255) NOT NULL, |
|
| 744 |
`institutionCode` varchar(255), |
|
| 745 |
`collectionCode` varchar(255), |
|
| 746 |
`catalogNumber` varchar(255), |
|
| 747 |
`occurrenceID` varchar(255), |
|
| 748 |
country varchar(255), |
|
| 749 |
`stateProvince` varchar(255), |
|
| 750 |
county varchar(255), |
|
| 751 |
locality varchar(255), |
|
| 752 |
`decimalLatitude` double precision, |
|
| 753 |
`decimalLongitude` double precision, |
|
| 754 |
`coordinateUncertaintyInMeters` double precision, |
|
| 755 |
`coordinateSource_bien` varchar(255), |
|
| 756 |
`georeferenceProtocol_bien` varchar(255), |
|
| 757 |
geovalid_bien int(11), |
|
| 758 |
`isNewWorld_bien` int(11), |
|
| 759 |
`projectID` varchar(255), |
|
| 760 |
`locationID` varchar(255) NOT NULL, |
|
| 761 |
`locationName` varchar(255), |
|
| 762 |
subplot varchar(255), |
|
| 763 |
`individualCode` varchar(255), |
|
| 764 |
`elevationInMeters` double precision, |
|
| 765 |
`plotArea_ha` double precision, |
|
| 766 |
`samplingProtocol` varchar(255), |
|
| 767 |
`temperature_C` double precision, |
|
| 768 |
precipitation_m double precision, |
|
| 769 |
`recordedBy` varchar(255), |
|
| 770 |
`recordNumber` varchar(255), |
|
| 771 |
`dateCollected` date, |
|
| 772 |
family_verbatim varchar(255), |
|
| 773 |
`scientificName_verbatim` varchar(255), |
|
| 774 |
`identifiedBy` varchar(255), |
|
| 775 |
`dateIdentified` date, |
|
| 776 |
`identificationRemarks` varchar(255), |
|
| 777 |
family_matched varchar(255), |
|
| 778 |
`taxonName_matched` varchar(255), |
|
| 779 |
`scientificNameAuthorship_matched` varchar(255), |
|
| 780 |
`higherPlantGroup_bien` varchar(255), |
|
| 781 |
family varchar(255), |
|
| 782 |
genus varchar(255), |
|
| 783 |
`speciesBinomialWithMorphospecies` varchar(255), |
|
| 784 |
`taxonName` varchar(255), |
|
| 785 |
`scientificNameAuthorship` varchar(255), |
|
| 786 |
`growthForm` varchar(255), |
|
| 787 |
`reproductiveCondition` varchar(255), |
|
| 788 |
threatened_bien int(11), |
|
| 789 |
cultivated_bien int(11), |
|
| 790 |
`cultivatedBasis_bien` varchar(255), |
|
| 791 |
`occurrenceRemarks` varchar(255), |
|
| 792 |
`coverPercent` double precision, |
|
| 793 |
`diameterBreastHeight_cm` double precision, |
|
| 794 |
height_m double precision, |
|
| 795 |
tag varchar(255), |
|
| 796 |
`organismX_m` double precision, |
|
| 797 |
`organismY_m` double precision, |
|
| 798 |
`taxonOccurrenceID` varchar(255), |
|
| 799 |
`authorTaxonCode` varchar(255), |
|
| 800 |
`individualObservationID` varchar(255), |
|
| 801 |
`authorStemCode` varchar(255) |
|
| 802 |
); |
|
| 803 |
|
|
| 804 |
|
|
| 805 |
-- |
|
| 806 | 831 |
-- Name: analytical_plot; Type: VIEW; Schema: public; Owner: - |
| 807 | 832 |
-- |
| 808 | 833 |
|
| ... | ... | |
| 2084 | 2109 |
|
| 2085 | 2110 |
|
| 2086 | 2111 |
-- |
| 2112 |
-- Name: geoscrub_input_new; Type: VIEW; Schema: public; Owner: - |
|
| 2113 |
-- |
|
| 2114 |
|
|
| 2115 |
|
|
| 2116 |
|
|
| 2117 |
|
|
| 2118 |
-- |
|
| 2087 | 2119 |
-- Name: geoscrub_input_view; Type: VIEW; Schema: public; Owner: - |
| 2088 | 2120 |
-- |
| 2089 | 2121 |
|
| ... | ... | |
| 2671 | 2703 |
|
| 2672 | 2704 |
|
| 2673 | 2705 |
-- |
| 2674 |
-- Name: provider_count; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 2675 |
-- |
|
| 2676 |
|
|
| 2677 |
CREATE TABLE provider_count ( |
|
| 2678 |
dataprovider varchar(255) NOT NULL, |
|
| 2679 |
dataset varchar(255) NOT NULL, |
|
| 2680 |
records varchar(255), |
|
| 2681 |
sourcetype varchar(255), |
|
| 2682 |
observationtype varchar(255) |
|
| 2683 |
); |
|
| 2684 |
|
|
| 2685 |
|
|
| 2686 |
-- |
|
| 2687 | 2706 |
-- Name: sourcename; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 2688 | 2707 |
-- |
| 2689 | 2708 |
|
| ... | ... | |
| 3016 | 3035 |
|
| 3017 | 3036 |
|
| 3018 | 3037 |
-- |
| 3019 |
-- Name: taxon_trait; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 3020 |
-- |
|
| 3021 |
|
|
| 3022 |
CREATE TABLE taxon_trait ( |
|
| 3023 |
`scientificName` varchar(255) NOT NULL, |
|
| 3024 |
`measurementType` varchar(255) NOT NULL, |
|
| 3025 |
`measurementValue` varchar(255), |
|
| 3026 |
`measurementUnit` varchar(255) |
|
| 3027 |
); |
|
| 3028 |
|
|
| 3029 |
|
|
| 3030 |
-- |
|
| 3031 | 3038 |
-- Name: trait; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 3032 | 3039 |
-- |
| 3033 | 3040 |
|
| ... | ... | |
| 7159 | 7166 |
|
| 7160 | 7167 |
|
| 7161 | 7168 |
-- |
| 7162 |
-- Name: address; Type: ACL; Schema: public; Owner: -
|
|
| 7169 |
-- Name: analytical_stem; Type: ACL; Schema: public; Owner: -
|
|
| 7163 | 7170 |
-- |
| 7164 | 7171 |
|
| 7165 | 7172 |
|
| ... | ... | |
| 7169 | 7176 |
|
| 7170 | 7177 |
|
| 7171 | 7178 |
-- |
| 7172 |
-- Name: aggregateoccurrence; Type: ACL; Schema: public; Owner: -
|
|
| 7179 |
-- Name: provider_count; Type: ACL; Schema: public; Owner: -
|
|
| 7173 | 7180 |
-- |
| 7174 | 7181 |
|
| 7175 | 7182 |
|
| ... | ... | |
| 7178 | 7185 |
|
| 7179 | 7186 |
|
| 7180 | 7187 |
|
| 7188 |
|
|
| 7181 | 7189 |
-- |
| 7182 |
-- Name: analytical_stem; Type: ACL; Schema: public; Owner: -
|
|
| 7190 |
-- Name: taxon_trait; Type: ACL; Schema: public; Owner: -
|
|
| 7183 | 7191 |
-- |
| 7184 | 7192 |
|
| 7185 | 7193 |
|
| ... | ... | |
| 7189 | 7197 |
|
| 7190 | 7198 |
|
| 7191 | 7199 |
-- |
| 7200 |
-- Name: address; Type: ACL; Schema: public; Owner: - |
|
| 7201 |
-- |
|
| 7202 |
|
|
| 7203 |
|
|
| 7204 |
|
|
| 7205 |
|
|
| 7206 |
|
|
| 7207 |
|
|
| 7208 |
|
|
| 7209 |
-- |
|
| 7210 |
-- Name: aggregateoccurrence; Type: ACL; Schema: public; Owner: - |
|
| 7211 |
-- |
|
| 7212 |
|
|
| 7213 |
|
|
| 7214 |
|
|
| 7215 |
|
|
| 7216 |
|
|
| 7217 |
|
|
| 7218 |
|
|
| 7219 |
-- |
|
| 7192 | 7220 |
-- Name: analytical_plot; Type: ACL; Schema: public; Owner: - |
| 7193 | 7221 |
-- |
| 7194 | 7222 |
|
| ... | ... | |
| 7541 | 7569 |
|
| 7542 | 7570 |
|
| 7543 | 7571 |
-- |
| 7572 |
-- Name: geoscrub_input_new; Type: ACL; Schema: public; Owner: - |
|
| 7573 |
-- |
|
| 7574 |
|
|
| 7575 |
|
|
| 7576 |
|
|
| 7577 |
|
|
| 7578 |
|
|
| 7579 |
|
|
| 7580 |
|
|
| 7581 |
-- |
|
| 7544 | 7582 |
-- Name: geoscrub_input_view; Type: ACL; Schema: public; Owner: - |
| 7545 | 7583 |
-- |
| 7546 | 7584 |
|
| ... | ... | |
| 7682 | 7720 |
|
| 7683 | 7721 |
|
| 7684 | 7722 |
-- |
| 7685 |
-- Name: provider_count; Type: ACL; Schema: public; Owner: - |
|
| 7686 |
-- |
|
| 7687 |
|
|
| 7688 |
|
|
| 7689 |
|
|
| 7690 |
|
|
| 7691 |
|
|
| 7692 |
|
|
| 7693 |
|
|
| 7694 |
|
|
| 7695 |
-- |
|
| 7696 | 7723 |
-- Name: sourcename; Type: ACL; Schema: public; Owner: - |
| 7697 | 7724 |
-- |
| 7698 | 7725 |
|
| ... | ... | |
| 7785 | 7812 |
|
| 7786 | 7813 |
|
| 7787 | 7814 |
-- |
| 7788 |
-- Name: taxon_trait; Type: ACL; Schema: public; Owner: - |
|
| 7789 |
-- |
|
| 7790 |
|
|
| 7791 |
|
|
| 7792 |
|
|
| 7793 |
|
|
| 7794 |
|
|
| 7795 |
|
|
| 7796 |
|
|
| 7797 |
-- |
|
| 7798 | 7815 |
-- Name: trait; Type: ACL; Schema: public; Owner: - |
| 7799 | 7816 |
-- |
| 7800 | 7817 |
|
Also available in: Unified diff
schemas/vegbien.sql: split geoscrub_input_view's new-row-only filtering into separate view geoscrub_input_new, so that the full geoscrub_input rows are still available. the reduction in geoscrub_input from eliminating the already-scrubbed rows was only 280,000 (5076500 - 4799173) out of a possible 1.7 million (1707970), so it makes sense to just run geoscrubbing on the full input. (the lower-than-expected reduction is most likely due to rows from pre-refresh data being present in the original geoscrub_output table, which have been replaced by different, post-refresh input rows.)