| 977 |
977 |
|
| 978 |
978 |
|
| 979 |
979 |
--
|
| 980 |
|
-- Name: sync_analytical_aggregate_to_view(); Type: FUNCTION; Schema: public; Owner: -
|
| 981 |
|
--
|
| 982 |
|
|
| 983 |
|
CREATE FUNCTION sync_analytical_aggregate_to_view() RETURNS void
|
| 984 |
|
LANGUAGE sql
|
| 985 |
|
AS $$
|
| 986 |
|
DROP TABLE IF EXISTS analytical_aggregate;
|
| 987 |
|
CREATE TABLE analytical_aggregate AS SELECT * FROM analytical_aggregate_view LIMIT 0;
|
| 988 |
|
|
| 989 |
|
GRANT SELECT ON TABLE analytical_aggregate TO bien_read;
|
| 990 |
|
GRANT SELECT ON TABLE analytical_aggregate_view TO bien_read;
|
| 991 |
|
|
| 992 |
|
ALTER TABLE analytical_aggregate ADD FOREIGN KEY (datasource) REFERENCES source (shortname) ON UPDATE CASCADE ON DELETE CASCADE;
|
| 993 |
|
|
| 994 |
|
ALTER TABLE analytical_aggregate ALTER COLUMN "datasource" SET NOT NULL;
|
| 995 |
|
ALTER TABLE analytical_aggregate ALTER COLUMN "locationID" SET NOT NULL;
|
| 996 |
|
|
| 997 |
|
CREATE INDEX ON analytical_aggregate ("datasource", "locationID", "dateCollected" );
|
| 998 |
|
$$;
|
| 999 |
|
|
| 1000 |
|
|
| 1001 |
|
--
|
| 1002 |
980 |
-- Name: sync_analytical_stem_to_view(); Type: FUNCTION; Schema: public; Owner: -
|
| 1003 |
981 |
--
|
| 1004 |
982 |
|
| ... | ... | |
| 1006 |
984 |
LANGUAGE plpgsql
|
| 1007 |
985 |
AS $$
|
| 1008 |
986 |
DECLARE
|
| 1009 |
|
analytical_aggregate_view_def text := NULL;
|
| 1010 |
987 |
analytical_plot_def text := NULL;
|
| 1011 |
988 |
analytical_specimen_def text := NULL;
|
| 1012 |
989 |
provider_count_view_def text := NULL;
|
| 1013 |
990 |
BEGIN
|
| 1014 |
991 |
-- Save and drop dependent objects
|
| 1015 |
992 |
BEGIN
|
| 1016 |
|
analytical_aggregate_view_def :=
|
| 1017 |
|
'CREATE OR REPLACE VIEW analytical_aggregate_view AS '
|
| 1018 |
|
||pg_get_viewdef('analytical_aggregate_view');
|
| 1019 |
|
DROP VIEW analytical_aggregate_view;
|
| 1020 |
|
EXCEPTION
|
| 1021 |
|
WHEN undefined_table THEN NULL;
|
| 1022 |
|
END;
|
| 1023 |
|
BEGIN
|
| 1024 |
993 |
analytical_plot_def :=
|
| 1025 |
994 |
'CREATE OR REPLACE VIEW analytical_plot AS '
|
| 1026 |
995 |
||pg_get_viewdef('analytical_plot');
|
| ... | ... | |
| 1061 |
1030 |
CREATE INDEX ON analytical_stem ("datasource", "projectID", "locationID", "dateCollected");
|
| 1062 |
1031 |
|
| 1063 |
1032 |
-- Re-create dependent objects
|
| 1064 |
|
IF analytical_aggregate_view_def IS NOT NULL THEN
|
| 1065 |
|
EXECUTE analytical_aggregate_view_def;
|
| 1066 |
|
GRANT SELECT ON TABLE analytical_aggregate_view TO bien_read;
|
| 1067 |
|
END IF;
|
| 1068 |
1033 |
IF analytical_plot_def IS NOT NULL THEN
|
| 1069 |
1034 |
EXECUTE analytical_plot_def;
|
| 1070 |
1035 |
GRANT SELECT ON TABLE analytical_plot TO bien_read;
|
| ... | ... | |
| 1637 |
1602 |
|
| 1638 |
1603 |
|
| 1639 |
1604 |
--
|
| 1640 |
|
-- Name: analytical_aggregate; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
| 1641 |
|
--
|
| 1642 |
|
|
| 1643 |
|
CREATE TABLE analytical_aggregate (
|
| 1644 |
|
datasource text NOT NULL,
|
| 1645 |
|
country text,
|
| 1646 |
|
"stateProvince" text,
|
| 1647 |
|
county text,
|
| 1648 |
|
"decimalLatitude" double precision,
|
| 1649 |
|
"decimalLongitude" double precision,
|
| 1650 |
|
"coordinateUncertaintyInMeters" double precision,
|
| 1651 |
|
geovalid_bien integer,
|
| 1652 |
|
"isNewWorld_bien" integer,
|
| 1653 |
|
"locationID" text NOT NULL,
|
| 1654 |
|
"elevationInMeters" double precision,
|
| 1655 |
|
"plotArea_ha" double precision,
|
| 1656 |
|
"samplingProtocol" text,
|
| 1657 |
|
"dateCollected" date,
|
| 1658 |
|
"higherPlantGroup_bien" higher_plant_group,
|
| 1659 |
|
family text,
|
| 1660 |
|
genus text,
|
| 1661 |
|
"speciesBinomialWithMorphospecies" text,
|
| 1662 |
|
"taxonName" text,
|
| 1663 |
|
"scientificNameAuthorship" text,
|
| 1664 |
|
threatened_bien integer,
|
| 1665 |
|
"identifiedBy" text,
|
| 1666 |
|
"growthForm" growthform,
|
| 1667 |
|
cultivated_bien integer,
|
| 1668 |
|
"cultivatedBasis_bien" text,
|
| 1669 |
|
"coverPercent" double precision,
|
| 1670 |
|
"individualCount" bigint,
|
| 1671 |
|
"individualCount_1cm_or_more" bigint,
|
| 1672 |
|
"individualCount_2_5cm_or_more" bigint,
|
| 1673 |
|
"individualCount_10cm_or_more" bigint
|
| 1674 |
|
);
|
| 1675 |
|
|
| 1676 |
|
|
| 1677 |
|
--
|
| 1678 |
1605 |
-- Name: analytical_stem; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
| 1679 |
1606 |
--
|
| 1680 |
1607 |
|
| ... | ... | |
| 1742 |
1669 |
|
| 1743 |
1670 |
|
| 1744 |
1671 |
--
|
| 1745 |
|
-- Name: analytical_aggregate_view; Type: VIEW; Schema: public; Owner: -
|
| 1746 |
|
--
|
| 1747 |
|
|
| 1748 |
|
CREATE VIEW analytical_aggregate_view AS
|
| 1749 |
|
SELECT analytical_stem.datasource, analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."coordinateUncertaintyInMeters", analytical_stem.geovalid_bien, analytical_stem."isNewWorld_bien", analytical_stem."locationID", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem."higherPlantGroup_bien", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomialWithMorphospecies", analytical_stem."taxonName", analytical_stem."scientificNameAuthorship", analytical_stem.threatened_bien, analytical_stem."identifiedBy", analytical_stem."growthForm", analytical_stem.cultivated_bien, analytical_stem."cultivatedBasis_bien", sum(analytical_stem."coverPercent") AS "coverPercent", count(analytical_stem."diameterBreastHeight_cm") AS "individualCount", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((1)::double precision))) AS "individualCount_1cm_or_more", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((2.5)::double precision))) AS "individualCount_2_5cm_or_more", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((10)::double precision))) AS "individualCount_10cm_or_more" FROM analytical_stem GROUP BY analytical_stem.datasource, analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."coordinateUncertaintyInMeters", analytical_stem.geovalid_bien, analytical_stem."isNewWorld_bien", analytical_stem."locationID", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem."higherPlantGroup_bien", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomialWithMorphospecies", analytical_stem."taxonName", analytical_stem."scientificNameAuthorship", analytical_stem.threatened_bien, analytical_stem."identifiedBy", analytical_stem."growthForm", analytical_stem.cultivated_bien, analytical_stem."cultivatedBasis_bien";
|
| 1750 |
|
|
| 1751 |
|
|
| 1752 |
|
--
|
| 1753 |
1672 |
-- Name: analytical_plot; Type: VIEW; Schema: public; Owner: -
|
| 1754 |
1673 |
--
|
| 1755 |
1674 |
|
| ... | ... | |
| 5142 |
5061 |
|
| 5143 |
5062 |
|
| 5144 |
5063 |
--
|
| 5145 |
|
-- Data for Name: analytical_aggregate; Type: TABLE DATA; Schema: public; Owner: -
|
| 5146 |
|
--
|
| 5147 |
|
|
| 5148 |
|
|
| 5149 |
|
|
| 5150 |
|
--
|
| 5151 |
5064 |
-- Data for Name: analytical_stem; Type: TABLE DATA; Schema: public; Owner: -
|
| 5152 |
5065 |
--
|
| 5153 |
5066 |
|
| ... | ... | |
| 6294 |
6207 |
|
| 6295 |
6208 |
|
| 6296 |
6209 |
--
|
| 6297 |
|
-- Name: analytical_aggregate_datasource_locationID_dateCollected_idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
| 6298 |
|
--
|
| 6299 |
|
|
| 6300 |
|
CREATE INDEX "analytical_aggregate_datasource_locationID_dateCollected_idx" ON analytical_aggregate USING btree (datasource, "locationID", "dateCollected");
|
| 6301 |
|
|
| 6302 |
|
|
| 6303 |
|
--
|
| 6304 |
6210 |
-- Name: analytical_stem_datasource_institutionCode_collectionCode_c_idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
| 6305 |
6211 |
--
|
| 6306 |
6212 |
|
| ... | ... | |
| 7470 |
7376 |
|
| 7471 |
7377 |
|
| 7472 |
7378 |
--
|
| 7473 |
|
-- Name: analytical_aggregate_datasource_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
| 7474 |
|
--
|
| 7475 |
|
|
| 7476 |
|
ALTER TABLE ONLY analytical_aggregate
|
| 7477 |
|
ADD CONSTRAINT analytical_aggregate_datasource_fkey FOREIGN KEY (datasource) REFERENCES source(shortname) ON UPDATE CASCADE ON DELETE CASCADE;
|
| 7478 |
|
|
| 7479 |
|
|
| 7480 |
|
--
|
| 7481 |
7379 |
-- Name: analytical_stem_datasource_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
| 7482 |
7380 |
--
|
| 7483 |
7381 |
|
| ... | ... | |
| 8521 |
8419 |
|
| 8522 |
8420 |
|
| 8523 |
8421 |
--
|
| 8524 |
|
-- Name: analytical_aggregate; Type: ACL; Schema: public; Owner: -
|
| 8525 |
|
--
|
| 8526 |
|
|
| 8527 |
|
REVOKE ALL ON TABLE analytical_aggregate FROM PUBLIC;
|
| 8528 |
|
REVOKE ALL ON TABLE analytical_aggregate FROM bien;
|
| 8529 |
|
GRANT ALL ON TABLE analytical_aggregate TO bien;
|
| 8530 |
|
GRANT SELECT ON TABLE analytical_aggregate TO bien_read;
|
| 8531 |
|
|
| 8532 |
|
|
| 8533 |
|
--
|
| 8534 |
8422 |
-- Name: analytical_stem; Type: ACL; Schema: public; Owner: -
|
| 8535 |
8423 |
--
|
| 8536 |
8424 |
|
| ... | ... | |
| 8541 |
8429 |
|
| 8542 |
8430 |
|
| 8543 |
8431 |
--
|
| 8544 |
|
-- Name: analytical_aggregate_view; Type: ACL; Schema: public; Owner: -
|
| 8545 |
|
--
|
| 8546 |
|
|
| 8547 |
|
REVOKE ALL ON TABLE analytical_aggregate_view FROM PUBLIC;
|
| 8548 |
|
REVOKE ALL ON TABLE analytical_aggregate_view FROM bien;
|
| 8549 |
|
GRANT ALL ON TABLE analytical_aggregate_view TO bien;
|
| 8550 |
|
GRANT SELECT ON TABLE analytical_aggregate_view TO bien_read;
|
| 8551 |
|
|
| 8552 |
|
|
| 8553 |
|
--
|
| 8554 |
8432 |
-- Name: analytical_plot; Type: ACL; Schema: public; Owner: -
|
| 8555 |
8433 |
--
|
| 8556 |
8434 |
|
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.