Revision 9929
Added by Aaron Marcuse-Kubitza over 11 years ago
| schemas/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 |
|
| schemas/vegbien.sql | ||
|---|---|---|
| 1496 | 1496 |
|
| 1497 | 1497 |
|
| 1498 | 1498 |
-- |
| 1499 |
-- Name: analytical_stem; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1500 |
-- |
|
| 1501 |
|
|
| 1502 |
CREATE TABLE analytical_stem ( |
|
| 1503 |
datasource text NOT NULL, |
|
| 1504 |
"institutionCode" text, |
|
| 1505 |
"collectionCode" text, |
|
| 1506 |
"catalogNumber" text, |
|
| 1507 |
"occurrenceID" text, |
|
| 1508 |
country text, |
|
| 1509 |
"stateProvince" text, |
|
| 1510 |
county text, |
|
| 1511 |
locality text, |
|
| 1512 |
"decimalLatitude" double precision, |
|
| 1513 |
"decimalLongitude" double precision, |
|
| 1514 |
"coordinateUncertaintyInMeters" double precision, |
|
| 1515 |
"coordinateSource_bien" coordinatesource, |
|
| 1516 |
"georeferenceProtocol_bien" text, |
|
| 1517 |
geovalid_bien integer, |
|
| 1518 |
"isNewWorld_bien" integer, |
|
| 1519 |
"projectID" text, |
|
| 1520 |
"locationID" text NOT NULL, |
|
| 1521 |
"locationName" text, |
|
| 1522 |
subplot text, |
|
| 1523 |
"individualCode" text, |
|
| 1524 |
"elevationInMeters" double precision, |
|
| 1525 |
"plotArea_ha" double precision, |
|
| 1526 |
"samplingProtocol" text, |
|
| 1527 |
"temperature_C" double precision, |
|
| 1528 |
precipitation_m double precision, |
|
| 1529 |
"recordedBy" text, |
|
| 1530 |
"recordNumber" text, |
|
| 1531 |
"dateCollected" date, |
|
| 1532 |
family_verbatim text, |
|
| 1533 |
"scientificName_verbatim" text, |
|
| 1534 |
"identifiedBy" text, |
|
| 1535 |
"dateIdentified" date, |
|
| 1536 |
"identificationRemarks" text, |
|
| 1537 |
family_matched text, |
|
| 1538 |
"taxonName_matched" text, |
|
| 1539 |
"scientificNameAuthorship_matched" text, |
|
| 1540 |
"higherPlantGroup_bien" higher_plant_group, |
|
| 1541 |
family text, |
|
| 1542 |
genus text, |
|
| 1543 |
"speciesBinomialWithMorphospecies" text, |
|
| 1544 |
"taxonName" text, |
|
| 1545 |
"scientificNameAuthorship" text, |
|
| 1546 |
"growthForm" growthform, |
|
| 1547 |
"reproductiveCondition" text, |
|
| 1548 |
threatened_bien integer, |
|
| 1549 |
cultivated_bien integer, |
|
| 1550 |
"cultivatedBasis_bien" text, |
|
| 1551 |
"occurrenceRemarks" text, |
|
| 1552 |
"coverPercent" double precision, |
|
| 1553 |
"diameterBreastHeight_cm" double precision, |
|
| 1554 |
height_m double precision, |
|
| 1555 |
tag text, |
|
| 1556 |
"organismX_m" double precision, |
|
| 1557 |
"organismY_m" double precision, |
|
| 1558 |
"taxonOccurrenceID" text, |
|
| 1559 |
"authorTaxonCode" text, |
|
| 1560 |
"individualObservationID" text, |
|
| 1561 |
"authorStemCode" text |
|
| 1562 |
); |
|
| 1563 |
|
|
| 1564 |
|
|
| 1565 |
-- |
|
| 1566 |
-- Name: provider_count; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1567 |
-- |
|
| 1568 |
|
|
| 1569 |
CREATE TABLE provider_count ( |
|
| 1570 |
dataprovider text NOT NULL, |
|
| 1571 |
dataset text NOT NULL, |
|
| 1572 |
records bigint, |
|
| 1573 |
sourcetype sourcetype, |
|
| 1574 |
observationtype observationtype |
|
| 1575 |
); |
|
| 1576 |
|
|
| 1577 |
|
|
| 1578 |
-- |
|
| 1579 |
-- Name: taxon_trait; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1580 |
-- |
|
| 1581 |
|
|
| 1582 |
CREATE TABLE taxon_trait ( |
|
| 1583 |
"scientificName" text NOT NULL, |
|
| 1584 |
"measurementType" text NOT NULL, |
|
| 1585 |
"measurementValue" text, |
|
| 1586 |
"measurementUnit" text |
|
| 1587 |
); |
|
| 1588 |
|
|
| 1589 |
|
|
| 1590 |
-- |
|
| 1499 | 1591 |
-- Name: address; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 1500 | 1592 |
-- |
| 1501 | 1593 |
|
| ... | ... | |
| 1613 | 1705 |
|
| 1614 | 1706 |
|
| 1615 | 1707 |
-- |
| 1616 |
-- Name: analytical_stem; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1617 |
-- |
|
| 1618 |
|
|
| 1619 |
CREATE TABLE analytical_stem ( |
|
| 1620 |
datasource text NOT NULL, |
|
| 1621 |
"institutionCode" text, |
|
| 1622 |
"collectionCode" text, |
|
| 1623 |
"catalogNumber" text, |
|
| 1624 |
"occurrenceID" text, |
|
| 1625 |
country text, |
|
| 1626 |
"stateProvince" text, |
|
| 1627 |
county text, |
|
| 1628 |
locality text, |
|
| 1629 |
"decimalLatitude" double precision, |
|
| 1630 |
"decimalLongitude" double precision, |
|
| 1631 |
"coordinateUncertaintyInMeters" double precision, |
|
| 1632 |
"coordinateSource_bien" coordinatesource, |
|
| 1633 |
"georeferenceProtocol_bien" text, |
|
| 1634 |
geovalid_bien integer, |
|
| 1635 |
"isNewWorld_bien" integer, |
|
| 1636 |
"projectID" text, |
|
| 1637 |
"locationID" text NOT NULL, |
|
| 1638 |
"locationName" text, |
|
| 1639 |
subplot text, |
|
| 1640 |
"individualCode" text, |
|
| 1641 |
"elevationInMeters" double precision, |
|
| 1642 |
"plotArea_ha" double precision, |
|
| 1643 |
"samplingProtocol" text, |
|
| 1644 |
"temperature_C" double precision, |
|
| 1645 |
precipitation_m double precision, |
|
| 1646 |
"recordedBy" text, |
|
| 1647 |
"recordNumber" text, |
|
| 1648 |
"dateCollected" date, |
|
| 1649 |
family_verbatim text, |
|
| 1650 |
"scientificName_verbatim" text, |
|
| 1651 |
"identifiedBy" text, |
|
| 1652 |
"dateIdentified" date, |
|
| 1653 |
"identificationRemarks" text, |
|
| 1654 |
family_matched text, |
|
| 1655 |
"taxonName_matched" text, |
|
| 1656 |
"scientificNameAuthorship_matched" text, |
|
| 1657 |
"higherPlantGroup_bien" higher_plant_group, |
|
| 1658 |
family text, |
|
| 1659 |
genus text, |
|
| 1660 |
"speciesBinomialWithMorphospecies" text, |
|
| 1661 |
"taxonName" text, |
|
| 1662 |
"scientificNameAuthorship" text, |
|
| 1663 |
"growthForm" growthform, |
|
| 1664 |
"reproductiveCondition" text, |
|
| 1665 |
threatened_bien integer, |
|
| 1666 |
cultivated_bien integer, |
|
| 1667 |
"cultivatedBasis_bien" text, |
|
| 1668 |
"occurrenceRemarks" text, |
|
| 1669 |
"coverPercent" double precision, |
|
| 1670 |
"diameterBreastHeight_cm" double precision, |
|
| 1671 |
height_m double precision, |
|
| 1672 |
tag text, |
|
| 1673 |
"organismX_m" double precision, |
|
| 1674 |
"organismY_m" double precision, |
|
| 1675 |
"taxonOccurrenceID" text, |
|
| 1676 |
"authorTaxonCode" text, |
|
| 1677 |
"individualObservationID" text, |
|
| 1678 |
"authorStemCode" text |
|
| 1679 |
); |
|
| 1680 |
|
|
| 1681 |
|
|
| 1682 |
-- |
|
| 1683 | 1708 |
-- Name: analytical_plot; Type: VIEW; Schema: public; Owner: - |
| 1684 | 1709 |
-- |
| 1685 | 1710 |
|
| ... | ... | |
| 3076 | 3101 |
|
| 3077 | 3102 |
|
| 3078 | 3103 |
-- |
| 3104 |
-- Name: geoscrub_input_new; Type: VIEW; Schema: public; Owner: - |
|
| 3105 |
-- |
|
| 3106 |
|
|
| 3107 |
CREATE VIEW geoscrub_input_new AS |
|
| 3108 |
SELECT geoscrub_input.country, geoscrub_input."stateProvince", geoscrub_input.county, geoscrub_input."decimalLatitude", geoscrub_input."decimalLongitude" FROM (geoscrub_input LEFT JOIN geoscrub.geoscrub_output ON ((((((geoscrub_output.decimallatitude = geoscrub_input."decimalLatitude") AND (geoscrub_output.decimallongitude = geoscrub_input."decimalLongitude")) AND (geoscrub_output.country = geoscrub_input.country)) AND (geoscrub_output.stateprovince = geoscrub_input."stateProvince")) AND (geoscrub_output.county = geoscrub_input.county)))) WHERE (geoscrub_output.row_num IS NULL); |
|
| 3109 |
|
|
| 3110 |
|
|
| 3111 |
-- |
|
| 3079 | 3112 |
-- Name: geoscrub_input_view; Type: VIEW; Schema: public; Owner: - |
| 3080 | 3113 |
-- |
| 3081 | 3114 |
|
| 3082 | 3115 |
CREATE VIEW geoscrub_input_view AS |
| 3083 |
SELECT DISTINCT place.country, place.stateprovince AS "stateProvince", place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude" FROM ((place JOIN coordinates USING (coordinates_id)) LEFT JOIN geoscrub.geoscrub_output ON ((((((geoscrub_output.decimallatitude = coordinates.latitude_deg) AND (geoscrub_output.decimallongitude = coordinates.longitude_deg)) AND (geoscrub_output.country = place.country)) AND (geoscrub_output.stateprovince = place.stateprovince)) AND (geoscrub_output.county = place.county)))) WHERE ((place.country IS NOT NULL) AND (geoscrub_output.row_num IS NULL));
|
|
| 3116 |
SELECT DISTINCT place.country, place.stateprovince AS "stateProvince", place.county, coordinates.latitude_deg AS "decimalLatitude", coordinates.longitude_deg AS "decimalLongitude" FROM (place JOIN coordinates USING (coordinates_id)) WHERE (place.country IS NOT NULL);
|
|
| 3084 | 3117 |
|
| 3085 | 3118 |
|
| 3086 | 3119 |
-- |
| ... | ... | |
| 3762 | 3795 |
|
| 3763 | 3796 |
|
| 3764 | 3797 |
-- |
| 3765 |
-- Name: provider_count; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 3766 |
-- |
|
| 3767 |
|
|
| 3768 |
CREATE TABLE provider_count ( |
|
| 3769 |
dataprovider text NOT NULL, |
|
| 3770 |
dataset text NOT NULL, |
|
| 3771 |
records bigint, |
|
| 3772 |
sourcetype sourcetype, |
|
| 3773 |
observationtype observationtype |
|
| 3774 |
); |
|
| 3775 |
|
|
| 3776 |
|
|
| 3777 |
-- |
|
| 3778 | 3798 |
-- Name: sourcename; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 3779 | 3799 |
-- |
| 3780 | 3800 |
|
| ... | ... | |
| 4159 | 4179 |
|
| 4160 | 4180 |
|
| 4161 | 4181 |
-- |
| 4162 |
-- Name: taxon_trait; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 4163 |
-- |
|
| 4164 |
|
|
| 4165 |
CREATE TABLE taxon_trait ( |
|
| 4166 |
"scientificName" text NOT NULL, |
|
| 4167 |
"measurementType" text NOT NULL, |
|
| 4168 |
"measurementValue" text, |
|
| 4169 |
"measurementUnit" text |
|
| 4170 |
); |
|
| 4171 |
|
|
| 4172 |
|
|
| 4173 |
-- |
|
| 4174 | 4182 |
-- Name: trait; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 4175 | 4183 |
-- |
| 4176 | 4184 |
|
| ... | ... | |
| 8423 | 8431 |
|
| 8424 | 8432 |
|
| 8425 | 8433 |
-- |
| 8434 |
-- Name: analytical_stem; Type: ACL; Schema: public; Owner: - |
|
| 8435 |
-- |
|
| 8436 |
|
|
| 8437 |
REVOKE ALL ON TABLE analytical_stem FROM PUBLIC; |
|
| 8438 |
REVOKE ALL ON TABLE analytical_stem FROM bien; |
|
| 8439 |
GRANT ALL ON TABLE analytical_stem TO bien; |
|
| 8440 |
GRANT SELECT ON TABLE analytical_stem TO bien_read; |
|
| 8441 |
|
|
| 8442 |
|
|
| 8443 |
-- |
|
| 8444 |
-- Name: provider_count; Type: ACL; Schema: public; Owner: - |
|
| 8445 |
-- |
|
| 8446 |
|
|
| 8447 |
REVOKE ALL ON TABLE provider_count FROM PUBLIC; |
|
| 8448 |
REVOKE ALL ON TABLE provider_count FROM bien; |
|
| 8449 |
GRANT ALL ON TABLE provider_count TO bien; |
|
| 8450 |
GRANT SELECT ON TABLE provider_count TO bien_read; |
|
| 8451 |
GRANT SELECT ON TABLE provider_count TO public_; |
|
| 8452 |
|
|
| 8453 |
|
|
| 8454 |
-- |
|
| 8455 |
-- Name: taxon_trait; Type: ACL; Schema: public; Owner: - |
|
| 8456 |
-- |
|
| 8457 |
|
|
| 8458 |
REVOKE ALL ON TABLE taxon_trait FROM PUBLIC; |
|
| 8459 |
REVOKE ALL ON TABLE taxon_trait FROM bien; |
|
| 8460 |
GRANT ALL ON TABLE taxon_trait TO bien; |
|
| 8461 |
GRANT SELECT ON TABLE taxon_trait TO bien_read; |
|
| 8462 |
|
|
| 8463 |
|
|
| 8464 |
-- |
|
| 8426 | 8465 |
-- Name: address; Type: ACL; Schema: public; Owner: - |
| 8427 | 8466 |
-- |
| 8428 | 8467 |
|
| ... | ... | |
| 8443 | 8482 |
|
| 8444 | 8483 |
|
| 8445 | 8484 |
-- |
| 8446 |
-- Name: analytical_stem; Type: ACL; Schema: public; Owner: - |
|
| 8447 |
-- |
|
| 8448 |
|
|
| 8449 |
REVOKE ALL ON TABLE analytical_stem FROM PUBLIC; |
|
| 8450 |
REVOKE ALL ON TABLE analytical_stem FROM bien; |
|
| 8451 |
GRANT ALL ON TABLE analytical_stem TO bien; |
|
| 8452 |
GRANT SELECT ON TABLE analytical_stem TO bien_read; |
|
| 8453 |
|
|
| 8454 |
|
|
| 8455 |
-- |
|
| 8456 | 8485 |
-- Name: analytical_plot; Type: ACL; Schema: public; Owner: - |
| 8457 | 8486 |
-- |
| 8458 | 8487 |
|
| ... | ... | |
| 8805 | 8834 |
|
| 8806 | 8835 |
|
| 8807 | 8836 |
-- |
| 8837 |
-- Name: geoscrub_input_new; Type: ACL; Schema: public; Owner: - |
|
| 8838 |
-- |
|
| 8839 |
|
|
| 8840 |
REVOKE ALL ON TABLE geoscrub_input_new FROM PUBLIC; |
|
| 8841 |
REVOKE ALL ON TABLE geoscrub_input_new FROM bien; |
|
| 8842 |
GRANT ALL ON TABLE geoscrub_input_new TO bien; |
|
| 8843 |
GRANT SELECT ON TABLE geoscrub_input_new TO bien_read; |
|
| 8844 |
|
|
| 8845 |
|
|
| 8846 |
-- |
|
| 8808 | 8847 |
-- Name: geoscrub_input_view; Type: ACL; Schema: public; Owner: - |
| 8809 | 8848 |
-- |
| 8810 | 8849 |
|
| ... | ... | |
| 8946 | 8985 |
|
| 8947 | 8986 |
|
| 8948 | 8987 |
-- |
| 8949 |
-- Name: provider_count; Type: ACL; Schema: public; Owner: - |
|
| 8950 |
-- |
|
| 8951 |
|
|
| 8952 |
REVOKE ALL ON TABLE provider_count FROM PUBLIC; |
|
| 8953 |
REVOKE ALL ON TABLE provider_count FROM bien; |
|
| 8954 |
GRANT ALL ON TABLE provider_count TO bien; |
|
| 8955 |
GRANT SELECT ON TABLE provider_count TO bien_read; |
|
| 8956 |
GRANT SELECT ON TABLE provider_count TO public_; |
|
| 8957 |
|
|
| 8958 |
|
|
| 8959 |
-- |
|
| 8960 | 8988 |
-- Name: sourcename; Type: ACL; Schema: public; Owner: - |
| 8961 | 8989 |
-- |
| 8962 | 8990 |
|
| ... | ... | |
| 9049 | 9077 |
|
| 9050 | 9078 |
|
| 9051 | 9079 |
-- |
| 9052 |
-- Name: taxon_trait; Type: ACL; Schema: public; Owner: - |
|
| 9053 |
-- |
|
| 9054 |
|
|
| 9055 |
REVOKE ALL ON TABLE taxon_trait FROM PUBLIC; |
|
| 9056 |
REVOKE ALL ON TABLE taxon_trait FROM bien; |
|
| 9057 |
GRANT ALL ON TABLE taxon_trait TO bien; |
|
| 9058 |
GRANT SELECT ON TABLE taxon_trait TO bien_read; |
|
| 9059 |
|
|
| 9060 |
|
|
| 9061 |
-- |
|
| 9062 | 9080 |
-- Name: trait; Type: ACL; Schema: public; Owner: - |
| 9063 | 9081 |
-- |
| 9064 | 9082 |
|
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.)