| 445 |
445 |
LANGUAGE sql
|
| 446 |
446 |
AS $$
|
| 447 |
447 |
INSERT INTO analytical_stem SELECT * FROM analytical_stem_view;
|
| 448 |
|
INSERT INTO aggregated_analytical_db SELECT * FROM aggregated_analytical_db_view;
|
|
448 |
INSERT INTO analytical_aggregate SELECT * FROM analytical_aggregate_view;
|
| 449 |
449 |
$$;
|
| 450 |
450 |
|
| 451 |
451 |
|
| ... | ... | |
| 557 |
557 |
|
| 558 |
558 |
|
| 559 |
559 |
--
|
| 560 |
|
-- Name: sync_aggregated_analytical_db_to_view(); Type: FUNCTION; Schema: public; Owner: -
|
|
560 |
-- Name: sync_analytical_aggregate_to_view(); Type: FUNCTION; Schema: public; Owner: -
|
| 561 |
561 |
--
|
| 562 |
562 |
|
| 563 |
|
CREATE FUNCTION sync_aggregated_analytical_db_to_view() RETURNS void
|
|
563 |
CREATE FUNCTION sync_analytical_aggregate_to_view() RETURNS void
|
| 564 |
564 |
LANGUAGE sql
|
| 565 |
565 |
AS $$
|
| 566 |
|
DROP TABLE IF EXISTS aggregated_analytical_db;
|
| 567 |
|
CREATE TABLE aggregated_analytical_db AS SELECT * FROM aggregated_analytical_db_view;
|
|
566 |
DROP TABLE IF EXISTS analytical_aggregate;
|
|
567 |
CREATE TABLE analytical_aggregate AS SELECT * FROM analytical_aggregate_view;
|
| 568 |
568 |
$$;
|
| 569 |
569 |
|
| 570 |
570 |
|
| ... | ... | |
| 968 |
968 |
|
| 969 |
969 |
|
| 970 |
970 |
--
|
| 971 |
|
-- Name: aggregated_analytical_db; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
971 |
-- Name: aggregateoccurrence; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
| 972 |
972 |
--
|
| 973 |
973 |
|
| 974 |
|
CREATE TABLE aggregated_analytical_db (
|
|
974 |
CREATE TABLE aggregateoccurrence (
|
|
975 |
aggregateoccurrence_id integer NOT NULL,
|
|
976 |
creator_id integer NOT NULL,
|
|
977 |
sourceaccessioncode text,
|
|
978 |
taxonoccurrence_id integer,
|
|
979 |
collectiondate date,
|
|
980 |
cover_fraction double precision,
|
|
981 |
linecover_m double precision,
|
|
982 |
basalarea_m2 double precision,
|
|
983 |
biomass_kg_m2 double precision,
|
|
984 |
inferencearea_m2 double precision,
|
|
985 |
count integer,
|
|
986 |
stratum_id integer,
|
|
987 |
coverindex_id integer,
|
|
988 |
occurrencestatus_dwc occurrencestatus_dwc DEFAULT 'present'::occurrencestatus_dwc NOT NULL,
|
|
989 |
method_id integer,
|
|
990 |
notes text,
|
|
991 |
accessioncode text,
|
|
992 |
CONSTRAINT aggregateoccurrence_required_key CHECK (((sourceaccessioncode IS NOT NULL) OR (taxonoccurrence_id IS NOT NULL)))
|
|
993 |
);
|
|
994 |
|
|
995 |
|
|
996 |
--
|
|
997 |
-- Name: TABLE aggregateoccurrence; Type: COMMENT; Schema: public; Owner: -
|
|
998 |
--
|
|
999 |
|
|
1000 |
COMMENT ON TABLE aggregateoccurrence IS 'Equivalent to VegBank''s taxonimportance table.';
|
|
1001 |
|
|
1002 |
|
|
1003 |
--
|
|
1004 |
-- Name: COLUMN aggregateoccurrence.linecover_m; Type: COMMENT; Schema: public; Owner: -
|
|
1005 |
--
|
|
1006 |
|
|
1007 |
COMMENT ON COLUMN aggregateoccurrence.linecover_m IS 'The distance in m along which this occurrence intercepts a line subplot.';
|
|
1008 |
|
|
1009 |
|
|
1010 |
--
|
|
1011 |
-- Name: COLUMN aggregateoccurrence.occurrencestatus_dwc; Type: COMMENT; Schema: public; Owner: -
|
|
1012 |
--
|
|
1013 |
|
|
1014 |
COMMENT ON COLUMN aggregateoccurrence.occurrencestatus_dwc IS 'The extent to which the taxon is present. See <http://code.google.com/p/darwincore/wiki/Occurrence#occurrenceStatus>.';
|
|
1015 |
|
|
1016 |
|
|
1017 |
--
|
|
1018 |
-- Name: aggregateoccurrence_aggregateoccurrence_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
1019 |
--
|
|
1020 |
|
|
1021 |
CREATE SEQUENCE aggregateoccurrence_aggregateoccurrence_id_seq
|
|
1022 |
START WITH 1
|
|
1023 |
INCREMENT BY 1
|
|
1024 |
NO MINVALUE
|
|
1025 |
NO MAXVALUE
|
|
1026 |
CACHE 1;
|
|
1027 |
|
|
1028 |
|
|
1029 |
--
|
|
1030 |
-- Name: aggregateoccurrence_aggregateoccurrence_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
1031 |
--
|
|
1032 |
|
|
1033 |
ALTER SEQUENCE aggregateoccurrence_aggregateoccurrence_id_seq OWNED BY aggregateoccurrence.aggregateoccurrence_id;
|
|
1034 |
|
|
1035 |
|
|
1036 |
--
|
|
1037 |
-- Name: analytical_aggregate; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
1038 |
--
|
|
1039 |
|
|
1040 |
CREATE TABLE analytical_aggregate (
|
| 975 |
1041 |
"institutionCode" text,
|
| 976 |
1042 |
country text,
|
| 977 |
1043 |
"stateProvince" text,
|
| ... | ... | |
| 1034 |
1100 |
|
| 1035 |
1101 |
|
| 1036 |
1102 |
--
|
| 1037 |
|
-- Name: aggregated_analytical_db_view; Type: VIEW; Schema: public; Owner: -
|
|
1103 |
-- Name: analytical_aggregate_view; Type: VIEW; Schema: public; Owner: -
|
| 1038 |
1104 |
--
|
| 1039 |
1105 |
|
| 1040 |
|
CREATE VIEW aggregated_analytical_db_view AS
|
|
1106 |
CREATE VIEW analytical_aggregate_view AS
|
| 1041 |
1107 |
SELECT analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."plotName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies", count(analytical_stem."diameterBreastHeight_cm") AS "individualCount", count(((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((1)::double precision)) AND (analytical_stem."diameterBreastHeight_cm" < _cm_to_m((2.5)::double precision)))) AS "individualCount_1_to_2_5cm", count(((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((2.5)::double precision)) AND (analytical_stem."diameterBreastHeight_cm" < _cm_to_m((10)::double precision)))) AS "individualCount_2_5_to_10cm", count((analytical_stem."diameterBreastHeight_cm" >= _cm_to_m((10)::double precision))) AS "individualCount_10cm_or_more" FROM analytical_stem WHERE (analytical_stem."diameterBreastHeight_cm" IS NOT NULL) GROUP BY analytical_stem."institutionCode", analytical_stem.country, analytical_stem."stateProvince", analytical_stem.county, analytical_stem."decimalLatitude", analytical_stem."decimalLongitude", analytical_stem."plotName", analytical_stem."elevationInMeters", analytical_stem."plotArea_ha", analytical_stem."samplingProtocol", analytical_stem."dateCollected", analytical_stem.family, analytical_stem.genus, analytical_stem."speciesBinomial", analytical_stem."scientificName", analytical_stem."scientificNameAuthorship", analytical_stem."scientificNameWithMorphospecies";
|
| 1042 |
1108 |
|
| 1043 |
1109 |
|
| 1044 |
1110 |
--
|
| 1045 |
|
-- Name: aggregateoccurrence; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
| 1046 |
|
--
|
| 1047 |
|
|
| 1048 |
|
CREATE TABLE aggregateoccurrence (
|
| 1049 |
|
aggregateoccurrence_id integer NOT NULL,
|
| 1050 |
|
creator_id integer NOT NULL,
|
| 1051 |
|
sourceaccessioncode text,
|
| 1052 |
|
taxonoccurrence_id integer,
|
| 1053 |
|
collectiondate date,
|
| 1054 |
|
cover_fraction double precision,
|
| 1055 |
|
linecover_m double precision,
|
| 1056 |
|
basalarea_m2 double precision,
|
| 1057 |
|
biomass_kg_m2 double precision,
|
| 1058 |
|
inferencearea_m2 double precision,
|
| 1059 |
|
count integer,
|
| 1060 |
|
stratum_id integer,
|
| 1061 |
|
coverindex_id integer,
|
| 1062 |
|
occurrencestatus_dwc occurrencestatus_dwc DEFAULT 'present'::occurrencestatus_dwc NOT NULL,
|
| 1063 |
|
method_id integer,
|
| 1064 |
|
notes text,
|
| 1065 |
|
accessioncode text,
|
| 1066 |
|
CONSTRAINT aggregateoccurrence_required_key CHECK (((sourceaccessioncode IS NOT NULL) OR (taxonoccurrence_id IS NOT NULL)))
|
| 1067 |
|
);
|
| 1068 |
|
|
| 1069 |
|
|
| 1070 |
|
--
|
| 1071 |
|
-- Name: TABLE aggregateoccurrence; Type: COMMENT; Schema: public; Owner: -
|
| 1072 |
|
--
|
| 1073 |
|
|
| 1074 |
|
COMMENT ON TABLE aggregateoccurrence IS 'Equivalent to VegBank''s taxonimportance table.';
|
| 1075 |
|
|
| 1076 |
|
|
| 1077 |
|
--
|
| 1078 |
|
-- Name: COLUMN aggregateoccurrence.linecover_m; Type: COMMENT; Schema: public; Owner: -
|
| 1079 |
|
--
|
| 1080 |
|
|
| 1081 |
|
COMMENT ON COLUMN aggregateoccurrence.linecover_m IS 'The distance in m along which this occurrence intercepts a line subplot.';
|
| 1082 |
|
|
| 1083 |
|
|
| 1084 |
|
--
|
| 1085 |
|
-- Name: COLUMN aggregateoccurrence.occurrencestatus_dwc; Type: COMMENT; Schema: public; Owner: -
|
| 1086 |
|
--
|
| 1087 |
|
|
| 1088 |
|
COMMENT ON COLUMN aggregateoccurrence.occurrencestatus_dwc IS 'The extent to which the taxon is present. See <http://code.google.com/p/darwincore/wiki/Occurrence#occurrenceStatus>.';
|
| 1089 |
|
|
| 1090 |
|
|
| 1091 |
|
--
|
| 1092 |
|
-- Name: aggregateoccurrence_aggregateoccurrence_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
| 1093 |
|
--
|
| 1094 |
|
|
| 1095 |
|
CREATE SEQUENCE aggregateoccurrence_aggregateoccurrence_id_seq
|
| 1096 |
|
START WITH 1
|
| 1097 |
|
INCREMENT BY 1
|
| 1098 |
|
NO MINVALUE
|
| 1099 |
|
NO MAXVALUE
|
| 1100 |
|
CACHE 1;
|
| 1101 |
|
|
| 1102 |
|
|
| 1103 |
|
--
|
| 1104 |
|
-- Name: aggregateoccurrence_aggregateoccurrence_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
| 1105 |
|
--
|
| 1106 |
|
|
| 1107 |
|
ALTER SEQUENCE aggregateoccurrence_aggregateoccurrence_id_seq OWNED BY aggregateoccurrence.aggregateoccurrence_id;
|
| 1108 |
|
|
| 1109 |
|
|
| 1110 |
|
--
|
| 1111 |
1111 |
-- Name: coordinates; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
| 1112 |
1112 |
--
|
| 1113 |
1113 |
|
schemas/vegbien.sql: Renamed aggregated_analytical_db to analytical_aggregate to match the name of analytical_stem