Revision 8131
Added by Aaron Marcuse-Kubitza almost 12 years ago
| vegbien.my.sql | ||
|---|---|---|
| 430 | 430 |
|
| 431 | 431 |
|
| 432 | 432 |
-- |
| 433 |
-- Name: sync_analytical_aggregate_to_view(); Type: FUNCTION; Schema: public; Owner: - |
|
| 434 |
-- |
|
| 435 |
|
|
| 436 |
|
|
| 437 |
|
|
| 438 |
|
|
| 439 |
-- |
|
| 440 | 433 |
-- Name: sync_analytical_stem_to_view(); Type: FUNCTION; Schema: public; Owner: - |
| 441 | 434 |
-- |
| 442 | 435 |
|
| ... | ... | |
| 736 | 729 |
|
| 737 | 730 |
|
| 738 | 731 |
-- |
| 739 |
-- Name: analytical_aggregate; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 740 |
-- |
|
| 741 |
|
|
| 742 |
CREATE TABLE analytical_aggregate ( |
|
| 743 |
datasource varchar(255) NOT NULL, |
|
| 744 |
country varchar(255), |
|
| 745 |
`stateProvince` varchar(255), |
|
| 746 |
county varchar(255), |
|
| 747 |
`decimalLatitude` double precision, |
|
| 748 |
`decimalLongitude` double precision, |
|
| 749 |
`coordinateUncertaintyInMeters` double precision, |
|
| 750 |
geovalid_bien int(11), |
|
| 751 |
`isNewWorld_bien` int(11), |
|
| 752 |
`locationID` varchar(255) NOT NULL, |
|
| 753 |
`elevationInMeters` double precision, |
|
| 754 |
`plotArea_ha` double precision, |
|
| 755 |
`samplingProtocol` varchar(255), |
|
| 756 |
`dateCollected` date, |
|
| 757 |
`higherPlantGroup_bien` varchar(255), |
|
| 758 |
family varchar(255), |
|
| 759 |
genus varchar(255), |
|
| 760 |
`speciesBinomialWithMorphospecies` varchar(255), |
|
| 761 |
`taxonName` varchar(255), |
|
| 762 |
`scientificNameAuthorship` varchar(255), |
|
| 763 |
threatened_bien int(11), |
|
| 764 |
`identifiedBy` varchar(255), |
|
| 765 |
`growthForm` varchar(255), |
|
| 766 |
cultivated_bien int(11), |
|
| 767 |
`cultivatedBasis_bien` varchar(255), |
|
| 768 |
`coverPercent` double precision, |
|
| 769 |
`individualCount` varchar(255), |
|
| 770 |
`individualCount_1cm_or_more` varchar(255), |
|
| 771 |
`individualCount_2_5cm_or_more` varchar(255), |
|
| 772 |
`individualCount_10cm_or_more` varchar(255) |
|
| 773 |
); |
|
| 774 |
|
|
| 775 |
|
|
| 776 |
-- |
|
| 777 | 732 |
-- Name: analytical_stem; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 778 | 733 |
-- |
| 779 | 734 |
|
| ... | ... | |
| 841 | 796 |
|
| 842 | 797 |
|
| 843 | 798 |
-- |
| 844 |
-- Name: analytical_aggregate_view; Type: VIEW; Schema: public; Owner: - |
|
| 845 |
-- |
|
| 846 |
|
|
| 847 |
|
|
| 848 |
|
|
| 849 |
|
|
| 850 |
-- |
|
| 851 | 799 |
-- Name: analytical_plot; Type: VIEW; Schema: public; Owner: - |
| 852 | 800 |
-- |
| 853 | 801 |
|
| ... | ... | |
| 3905 | 3853 |
|
| 3906 | 3854 |
|
| 3907 | 3855 |
-- |
| 3908 |
-- Data for Name: analytical_aggregate; Type: TABLE DATA; Schema: public; Owner: - |
|
| 3909 |
-- |
|
| 3910 |
|
|
| 3911 |
|
|
| 3912 |
|
|
| 3913 |
-- |
|
| 3914 | 3856 |
-- Data for Name: analytical_stem; Type: TABLE DATA; Schema: public; Owner: - |
| 3915 | 3857 |
-- |
| 3916 | 3858 |
|
| ... | ... | |
| 5057 | 4999 |
|
| 5058 | 5000 |
|
| 5059 | 5001 |
-- |
| 5060 |
-- Name: analytical_aggregate_datasource_locationID_dateCollected_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 5061 |
-- |
|
| 5062 |
|
|
| 5063 |
CREATE INDEX `analytical_aggregate_datasource_locationID_dateCollected_idx` ON analytical_aggregate (datasource, `locationID`, `dateCollected`); |
|
| 5064 |
|
|
| 5065 |
|
|
| 5066 |
-- |
|
| 5067 | 5002 |
-- Name: analytical_stem_datasource_institutionCode_collectionCode_c_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 5068 | 5003 |
-- |
| 5069 | 5004 |
|
| ... | ... | |
| 6229 | 6164 |
|
| 6230 | 6165 |
|
| 6231 | 6166 |
-- |
| 6232 |
-- Name: analytical_aggregate_datasource_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6233 |
-- |
|
| 6234 |
|
|
| 6235 |
|
|
| 6236 |
|
|
| 6237 |
|
|
| 6238 |
-- |
|
| 6239 | 6167 |
-- Name: analytical_stem_datasource_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 6240 | 6168 |
-- |
| 6241 | 6169 |
|
| ... | ... | |
| 7232 | 7160 |
|
| 7233 | 7161 |
|
| 7234 | 7162 |
-- |
| 7235 |
-- Name: analytical_aggregate; Type: ACL; Schema: public; Owner: - |
|
| 7236 |
-- |
|
| 7237 |
|
|
| 7238 |
|
|
| 7239 |
|
|
| 7240 |
|
|
| 7241 |
|
|
| 7242 |
|
|
| 7243 |
|
|
| 7244 |
-- |
|
| 7245 | 7163 |
-- Name: analytical_stem; Type: ACL; Schema: public; Owner: - |
| 7246 | 7164 |
-- |
| 7247 | 7165 |
|
| ... | ... | |
| 7252 | 7170 |
|
| 7253 | 7171 |
|
| 7254 | 7172 |
-- |
| 7255 |
-- Name: analytical_aggregate_view; Type: ACL; Schema: public; Owner: - |
|
| 7256 |
-- |
|
| 7257 |
|
|
| 7258 |
|
|
| 7259 |
|
|
| 7260 |
|
|
| 7261 |
|
|
| 7262 |
|
|
| 7263 |
|
|
| 7264 |
-- |
|
| 7265 | 7173 |
-- Name: analytical_plot; Type: ACL; Schema: public; Owner: - |
| 7266 | 7174 |
-- |
| 7267 | 7175 |
|
Also available in: Unified diff
schemas/vegbien.sql: Removed unused analytical_aggregate table, because analytical_stem provides much more detailed, higher-quality data, both in terms of the number or of rows and the number of columns. analytical_aggregate has also long been out of sync with the analytical DB schema, and it doesn't make sense to spend processing time in make_analytical_db to perform the DISTINCT ON if the table isn't being used. We may revisit analytical_aggregate later once we have ID fields for each entity in the DISTINCT ON and can avoid DISTINCTing on all analytical_aggregate columns.