-
Notifications
You must be signed in to change notification settings - Fork 0
/
largest_use_per_fid.sql
191 lines (184 loc) · 7.67 KB
/
largest_use_per_fid.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
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
-- create table to rank land_uses based on intensity
DROP TABLE IF EXISTS LU_rank;
CREATE TEMP TABLE lu_rank
(land_use VARCHAR(300), rank INTEGER);
INSERT INTO lu_rank (land_use , rank)
VALUES
('ALF', 1),('AMCP', 1),('ARTC', 1),('ASPG', 1),('ASSF', 1),
('BFLO', 1),('BFT', 1),('BKB', 1),('BLB', 1),('BLR-OPEN', 1),
('BLR-POLY', 1),('BLU-OPEN', 1),('BPP', 1),('BRT', 1),('BSP', 1),
('BW', 1),('CABB', 1),('CALA', 1),('CANS', 1),('CARR', 1),
('CAUL', 1),('CEL', 1),('CHIC', 1),('CHP', 1),('CLO', 1),
('CRB', 1),('ENG-B', 1),('GAR', 1),('GSB', 1),('HS', 1),
('LEEK', 1),('LEN', 1),('LETT', 1),('LGB', 1),('LIN', 1),
('MAIZ', 1),('MBSF', 1),('MSC', 1),('MU', 1),('NU-FS', 1),
('NU-OT', 1),('NU-SH', 1),('OCS-B', 1),('OCS-K', 1),('ONI', 1),
('ONU', 1),('OSFRT', 1),('OVEG', 1),('PAR', 1),('PUM', 1),
('RASP-OPEN', 1),('RASP-POLY', 1),('RAST', 1),('RHB', 1),('RRC', 1),
('SB', 1),('SBEAN', 1),('SFB', 1),('SL', 1),('SO', 1),('SOSR', 1),
('SPEAS', 1),('SPOT', 1),('SPP', 1),('SRYE', 1),('SSF', 1),
('STRB-OPEN', 1),('STRB-POLY', 1),('STRIT', 1),('STS', 1),('SUN', 1),
('SW', 1),('SWS', 1),('TFRT', 1),('TSF', 1),('WB', 1),
('WBEAN', 1),('WBS', 1),('WFB', 1),('WO', 1),('WOSR', 1),('WPEAS', 1),
('WPOT', 1),('WPP', 1),('WRYE', 1),('WTRIT', 1),('WW', 1),('EX-SS', 2),
('FALW', 2),('FALW-5', 2),('GCM', 2),('NETR-A', 2),('NETR-NA', 2),('PHA', 2),
('SRC', 2),('UCAA', 2),('WDG', 2),('WFM', 2),('TGRS', 3),('TGRS1', 3),('TGRS2', 3),
('TGRS3', 3),('TGRS4', 3),('TGRS5', 3),('VET', 3),('PC', 4),('PGRS', 4),
('RGR', 5),('BRA', 99),('BUI', 99),('EXCL', 99),('FSE', 99),('GOR', 99),
('MAR', 99),('RASP-GLS', 99),('ROAD', 99),('ROK', 99),('SCB', 99),
('SCE', 99),('STRB-GLS', 99),('TOM-GLS', 99),('TREES', 99),
('TURF', 99),('WAT', 99);
-- find max claimed land_use
DROP TABLE IF EXISTS ladss.saf_iacs_2018_largest_claim_per_fid;
WITH cte AS
(SELECT hapar_id,
land_use,
sum(claimed_area) AS claimed_area
FROM
(SELECT hapar_id,
CASE
WHEN SUBSTRING(land_use
FROM 1
FOR 4) = 'TGRS' THEN 'TGRS'
ELSE land_use
END AS land_use,
claimed_area
FROM
(SELECT hapar_id,
land_use,
sum(owner_bps_claimed_area) + sum(user_bps_claimed_area) AS claimed_area
FROM
(SELECT hapar_id_v2 AS hapar_id,
CASE
WHEN user_land_use IS NULL
OR user_land_use = 'NON_SAF' THEN owner_land_use
ELSE user_land_use
END AS land_use,
CASE
WHEN owner_bps_claimed_area IS NULL THEN 0
ELSE owner_bps_claimed_area
END AS owner_bps_claimed_area,
CASE
WHEN user_bps_claimed_area IS NULL THEN 0
ELSE user_bps_claimed_area
END AS user_bps_claimed_area
FROM ladss.saf_iacs_2018_processed) foo
GROUP BY hapar_id,
land_use
HAVING sum(owner_bps_claimed_area) + sum(user_bps_claimed_area) <> 0
AND land_use NOT IN (SELECT land_use FROM excl)) foorbar) foobar2
GROUP BY hapar_id,
land_use)
SELECT hapar_id,
land_use,
max_claimed --INTO ladss.saf_iacs_2018_largest_claim_per_fid
FROM
(SELECT cte.hapar_id,
land_use,
max_claimed,
ROW_NUMBER() OVER (PARTITION BY cte.hapar_id,
land_use
ORDER BY rank)
FROM
(SELECT hapar_id,
max(claimed_area) AS max_claimed
FROM cte
GROUP BY hapar_id
HAVING MAX(claimed_area) <> 0) foo
JOIN cte ON cte.hapar_id = foo.hapar_id
AND max_claimed = claimed_area
JOIN lu_rank USING (land_use)) foobar
WHERE ROW_NUMBER = 1;
-- find max land_use - not based on claims
WITH cte AS
(SELECT hapar_id,
land_use,
land_parcel_area,
CASE
WHEN bps_claimed_area <> 0 THEN bps_claimed_area
ELSE land_use_area
END AS used_area
FROM
(SELECT hapar_id,
land_use,
land_parcel_area,
owner_bps_claimed_area + user_bps_claimed_area AS bps_claimed_area,
owner_land_use_area + user_land_use_area AS land_use_area
FROM
(SELECT hapar_id,
CASE
WHEN user_land_use IS NULL
OR user_land_use = 'NON_SAF' THEN owner_land_use
ELSE user_land_use
END AS land_use,
land_parcel_area,
CASE
WHEN owner_bps_claimed_area IS NULL THEN 0
ELSE owner_bps_claimed_area
END AS owner_bps_claimed_area,
CASE
WHEN user_bps_claimed_area IS NULL THEN 0
ELSE user_bps_claimed_area
END AS user_bps_claimed_area,
CASE
WHEN owner_land_use_area IS NULL THEN 0
ELSE owner_land_use_area
END AS owner_land_use_area,
CASE
WHEN user_land_use_area IS NULL THEN 0
ELSE user_land_use_area
END AS user_land_use_area
FROM ladss.saf_iacs_2018_processed
WHERE hapar_id NOT IN
(SELECT hapar_id
FROM ladss.saf_iacs_2018_largest_claim_per_fid)
AND (owner_land_use NOT IN
(SELECT land_use
FROM excl)
OR user_land_use NOT IN
(SELECT land_use
FROM excl))) foo) bar
ORDER BY hapar_id)
SELECT hapar_id,
land_use,
used_area INTO ladss.saf_iacs_2018_largest_use_per_fid_exclusive
FROM (
SELECT hapar_id,
land_use,
sum(used_area) AS used_area,
ROW_NUMBER() OVER (PARTITION BY hapar_id,
sum(used_area)
ORDER BY sum(used_area) DESC,
land_use)
FROM cte
GROUP BY hapar_id,
land_use) foobar
WHERE row_number = 1
-- COMMONS
----------------------------------------------------------------------------------
WITH cte AS
(SELECT cg_hahol_id,
land_use,
sum(bps_claimed_area) AS sum_bps
FROM ladss.saf_commons_2016_2017_2018
WHERE YEAR = 2018
GROUP BY cg_hahol_id,
land_use)
SELECT cg_hahol_id,
land_use,
max_bps_claimed INTO ladss.saf_commons_2018_largest_use_per_fid
FROM
(SELECT foo.cg_hahol_id,
land_use,
max_bps_claimed,
ROW_NUMBER() OVER (PARTITION BY foo.cg_hahol_id
ORDER BY rank) AS rn
FROM
(SELECT cg_hahol_id,
MAX(sum_bps) AS max_bps_claimed
FROM cte
GROUP BY cg_hahol_id) foo
JOIN cte AS self ON foo.cg_hahol_id = self.cg_hahol_id
AND sum_bps = max_bps_claimed
JOIN lu_rank USING (land_use)) foobar
WHERE rn = 1