-
Notifications
You must be signed in to change notification settings - Fork 0
/
Level2Aggregation_AMOUNT.sql
101 lines (87 loc) · 3.73 KB
/
Level2Aggregation_AMOUNT.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
DROP TABLE IF EXISTS "TEMP_AGG_LEVEL_2";
--Add Level 2 information from mapped table (REPORTING_UNIT_AGG)
SELECT
"REPORTING_UNIT_AGG"."ORGANIZATION_ID",
"REPORTING_UNIT_AGG"."LEVEL_2_REPORTING_UNIT_ID" AS "REPORT_UNIT_ID",
"REPORTING_UNIT_AGG"."LEVEL_2_REPORTING_UNIT_NAME" AS "REPORTING_UNIT_NAME"
INTO "TEMP_AGG_LEVEL_2"
FROM
"WADE"."REPORTING_UNIT_AGG"
GROUP BY
"REPORTING_UNIT_AGG"."ORGANIZATION_ID",
"REPORTING_UNIT_AGG"."LEVEL_2_REPORTING_UNIT_ID",
"REPORTING_UNIT_AGG"."LEVEL_2_REPORTING_UNIT_NAME"
ORDER BY "LEVEL_2_REPORTING_UNIT_ID";
ALTER TABLE "TEMP_AGG_LEVEL_2"
ADD COLUMN "REPORT_ID" character varying(35) DEFAULT '2010',
ADD COLUMN "REPORTING_UNIT_TYPE" character varying(35) DEFAULT 'Planning Area',
ADD COLUMN "STATE" numeric(18,0) DEFAULT 49,
ADD COLUMN "COUNTY_FIPS" character(5),
ADD COLUMN "HUC" character varying(12);
INSERT INTO "WADE"."REPORTING_UNIT" SELECT "ORGANIZATION_ID","REPORT_ID",
"REPORT_UNIT_ID","REPORTING_UNIT_NAME","REPORTING_UNIT_TYPE","STATE",
"COUNTY_FIPS","HUC" FROM "TEMP_AGG_LEVEL_2";
DROP TABLE IF EXISTS "TEMP_LEVEL_2_SUMMARY_USE";
DROP TABLE IF EXISTS "TEMP_LEVEL_2_S_USE_AMOUNT";
--Aggregate data in a temporary table
SELECT
"REPORTING_UNIT_AGG"."ORGANIZATION_ID",
"S_USE_AMOUNT"."REPORT_ID",
"REPORTING_UNIT_AGG"."LEVEL_2_REPORTING_UNIT_ID" AS "REPORT_UNIT_ID",
"S_USE_AMOUNT"."BENEFICIAL_USE_ID",
"REPORTING_UNIT_AGG"."LEVEL_2_REPORTING_UNIT_ID" AS "WFS_FEATURE_REF"
INTO "TEMP_LEVEL_2_SUMMARY_USE"
FROM
"WADE"."S_USE_AMOUNT",
"WADE"."REPORTING_UNIT_AGG"
WHERE
"S_USE_AMOUNT"."REPORT_UNIT_ID" = "REPORTING_UNIT_AGG"."REPORT_UNIT_ID"
GROUP BY
"REPORTING_UNIT_AGG"."ORGANIZATION_ID",
"S_USE_AMOUNT"."REPORT_ID",
"REPORTING_UNIT_AGG"."LEVEL_2_REPORTING_UNIT_ID",
"S_USE_AMOUNT"."BENEFICIAL_USE_ID"
ORDER BY "LEVEL_2_REPORTING_UNIT_ID";
ALTER TABLE "TEMP_LEVEL_2_SUMMARY_USE"
ADD COLUMN "SUMMARY_SEQ" numeric(18,0) DEFAULT 1,
ADD COLUMN "FRESH_SALINE_IND" numeric(18,0) DEFAULT 1,
ADD COLUMN "SOURCE_TYPE" numeric(18,0) DEFAULT 1,
ADD COLUMN "POWER_GENERATED" numeric(18,3) DEFAULT -999.000,
ADD COLUMN "POPULATION_SERVED" numeric(18,3) DEFAULT -999.000;
INSERT INTO "WADE"."SUMMARY_USE" SELECT "ORGANIZATION_ID","REPORT_ID",
"REPORT_UNIT_ID","SUMMARY_SEQ","BENEFICIAL_USE_ID","FRESH_SALINE_IND","SOURCE_TYPE",
"POWER_GENERATED","POPULATION_SERVED" FROM "TEMP_LEVEL_2_SUMMARY_USE";
--Add aggregated amount information from temporary table to S_USE_AMOUNT
SELECT
"REPORTING_UNIT_AGG"."ORGANIZATION_ID",
"S_USE_AMOUNT"."REPORT_ID",
"REPORTING_UNIT_AGG"."LEVEL_2_REPORTING_UNIT_ID" AS "REPORT_UNIT_ID",
"S_USE_AMOUNT"."BENEFICIAL_USE_ID",
SUM("S_USE_AMOUNT"."AMOUNT") AS "AMOUNT",
"S_USE_AMOUNT"."CONSUMPTIVE_INDICATOR",
"S_USE_AMOUNT"."METHOD_ID",
"S_USE_AMOUNT"."START_DATE",
"S_USE_AMOUNT"."END_DATE"
INTO "TEMP_LEVEL_2_S_USE_AMOUNT"
FROM
"WADE"."S_USE_AMOUNT",
"WADE"."REPORTING_UNIT_AGG"
WHERE
"S_USE_AMOUNT"."REPORT_UNIT_ID" = "REPORTING_UNIT_AGG"."REPORT_UNIT_ID"
GROUP BY
"REPORTING_UNIT_AGG"."ORGANIZATION_ID",
"S_USE_AMOUNT"."REPORT_ID",
"REPORTING_UNIT_AGG"."LEVEL_2_REPORTING_UNIT_ID",
"S_USE_AMOUNT"."BENEFICIAL_USE_ID",
"S_USE_AMOUNT"."CONSUMPTIVE_INDICATOR",
"S_USE_AMOUNT"."METHOD_ID",
"S_USE_AMOUNT"."START_DATE",
"S_USE_AMOUNT"."END_DATE"
ORDER BY "LEVEL_2_REPORTING_UNIT_ID"
;
ALTER TABLE "TEMP_LEVEL_2_S_USE_AMOUNT"
ADD COLUMN "SUMMARY_SEQ" int DEFAULT 1,
ADD COLUMN "ROW_SEQ" int DEFAULT 1;
INSERT INTO "WADE"."S_USE_AMOUNT" SELECT "ORGANIZATION_ID","REPORT_ID",
"REPORT_UNIT_ID","BENEFICIAL_USE_ID","SUMMARY_SEQ","ROW_SEQ","AMOUNT",
"CONSUMPTIVE_INDICATOR","METHOD_ID","START_DATE","END_DATE" FROM "TEMP_LEVEL_2_S_USE_AMOUNT";