-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathdb_queries.sql
371 lines (345 loc) · 9.57 KB
/
db_queries.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
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
DROP SCHEMA pa_restoration_monitoring_network CASCADE;
CREATE SCHEMA pa_restoration_monitoring_network;
CREATE TABLE pa_restoration_monitoring_network.desk_study (
rmn_id VARCHAR(10),
grant_id VARCHAR(10),
visit VARCHAR(50),
site VARCHAR(255),
grant_notes VARCHAR,
condition_category VARCHAR(255),
aerial_imagery_notes VARCHAR,
land_use VARCHAR(255),
land_use_notes VARCHAR,
land_use_change_notes VARCHAR,
deer_density_notes VARCHAR,
herbivore_impact_notes VARCHAR,
general_notes VARCHAR,
importe_by VARCHAR(255),
updated_by VARCHAR(255),
updated_date DATE,
import_date DATE
);
CREATE TABLE pa_restoration_monitoring_network.quadrat_information (
rmn_id VARCHAR(10),
grant_id VARCHAR(10),
visit VARCHAR(50),
sampling_point VARCHAR(255),
date DATE,
surveyors VARCHAR(255),
markers_found VARCHAR(255),
feature_type VARCHAR(255),
quadrat_type VARCHAR(255),
aspect VARCHAR(255),
peat_depth INT,
bare_peat DECIMAL(5,2),
bare_mineral DECIMAL(5,2),
open_water DECIMAL(5,2),
litter DECIMAL(5,2),
trampling VARCHAR(255),
dung VARCHAR(255),
trees VARCHAR(255),
veg_height_1 INT,
veg_height_2 INT,
veg_height_3 INT,
veg_height_4 INT,
veg_height_5 INT,
disturbance_notes VARCHAR,
quadrat_notes VARCHAR,
importe_by VARCHAR(255),
updated_by VARCHAR(255),
updated_date DATE,
import_date DATE
);
CREATE TABLE pa_restoration_monitoring_network.vegetation (
rmn_id VARCHAR(10),
grant_id VARCHAR(10),
visit VARCHAR(50),
sampling_point VARCHAR(255),
species VARCHAR(255),
cover DECIMAL(5,2),
notes VARCHAR,
importe_by VARCHAR(255),
updated_by VARCHAR(255),
updated_date DATE,
import_date DATE
);
CREATE TABLE pa_restoration_monitoring_network.feature_status_drains (
rmn_id VARCHAR(10),
grant_id VARCHAR(10),
visit VARCHAR(50),
sampling_point VARCHAR(255),
drain_point VARCHAR(255),
drain_identifiable BOOLEAN,
depth VARCHAR(255),
width VARCHAR(255),
flow VARCHAR(255),
bare_peat VARCHAR(255),
bare_mineral VARCHAR(255),
open_water VARCHAR(255),
litter VARCHAR(255),
vegetation VARCHAR(255),
dwarf_shrub VARCHAR(255),
eriophorum VARCHAR(255),
trichophorum VARCHAR(255),
molinia VARCHAR(255),
other_poaceae VARCHAR(255),
juncus VARCHAR(255),
sphagnum VARCHAR(255),
other_moss VARCHAR(255),
lichen VARCHAR(255),
block_present VARCHAR(255),
erosion_around VARCHAR(255),
erosion_over VARCHAR(255),
block_trampling VARCHAR(255),
water_retained VARCHAR(255),
water_retained_sphagnum VARCHAR(255),
sediment_retained VARCHAR(255),
block_vegetation_establishing VARCHAR(255),
donor BOOLEAN,
donor_vegetation_establishing VARCHAR(255),
score VARCHAR(255),
notes VARCHAR,
importe_by VARCHAR(255),
updated_by VARCHAR(255),
updated_date DATE,
import_date DATE
);
CREATE TABLE pa_restoration_monitoring_network.photos (
rmn_id VARCHAR(10),
grant_id VARCHAR(10),
visit VARCHAR(50),
title VARCHAR(255),
date DATE,
photographer VARCHAR(255),
bearing INT,
notes VARCHAR,
dams_link VARCHAR(255),
importe_by VARCHAR(255),
updated_by VARCHAR(255),
updated_date DATE,
import_date DATE
);
CREATE TABLE pa_restoration_monitoring_network.area_level_assessment (
rmn_id VARCHAR(10),
grant_id VARCHAR(10),
visit VARCHAR(50),
site VARCHAR(255),
survey_dates VARCHAR(255),
surveyors VARCHAR(255),
weather VARCHAR(255),
ground_conditions VARCHAR(255),
nvc_approximate VARCHAR(255),
nvc_approximate_notes VARCHAR,
bare_peat VARCHAR(255),
bare_peat_notes VARCHAR,
dwarf_shrub VARCHAR(255),
dwarf_shrub_notes VARCHAR,
eriophorum VARCHAR(255),
eriophorum_notes VARCHAR,
trichophorum VARCHAR(255),
trichophorum_notes VARCHAR,
molinia VARCHAR(255),
molinia_notes VARCHAR,
other_poaceae VARCHAR(255),
other_poaceae_notes VARCHAR,
sphagnum VARCHAR(255),
sphagnum_notes VARCHAR,
trees VARCHAR(255),
trees_notes VARCHAR,
inns VARCHAR(255),
inns_notes VARCHAR,
restoration_activities VARCHAR(255),
restoration_activities_notes VARCHAR,
bare_peat_impact VARCHAR(255),
bare_peat_impact_notes VARCHAR,
drain_intensity VARCHAR(255),
drain_intensity_notes VARCHAR,
drain_status VARCHAR(255),
drain_status_notes VARCHAR,
burning VARCHAR(255),
burning_notes VARCHAR,
herbivore_ground_disturbance VARCHAR(255),
herbivore_ground_disturbance_notes VARCHAR,
herbivore_grazing VARCHAR(255),
herbivore_grazing_notes VARCHAR,
trees_impact VARCHAR(255),
trees_impact_notes VARCHAR,
peat_extraction VARCHAR(255),
peat_extraction_notes VARCHAR,
human_infrastructure VARCHAR(255),
human_infrastructure_notes VARCHAR,
other_damage VARCHAR(255),
other_damage_notes VARCHAR,
importe_by VARCHAR(255),
updated_by VARCHAR(255),
updated_date DATE,
import_date DATE
);
CREATE TABLE pa_restoration_monitoring_network.monitoring_area (
rmn_id VARCHAR(10),
grant_id VARCHAR(10),
visit VARCHAR(50),
geometry GEOMETRY,
importe_by VARCHAR(255),
updated_by VARCHAR(255),
updated_date DATE,
import_date DATE
);
CREATE TABLE pa_restoration_monitoring_network.sampling_points (
rmn_id VARCHAR(10),
grant_id VARCHAR(10),
visit VARCHAR(50),
sampling_point_id VARCHAR(50),
easting DECIMAL(10,6),
northing DECIMAL(10,6),
horizontal_accuracy DECIMAL(5,2),
vertical_accuracy DECIMAL(5,2),
date DATE,
elevation DECIMAL(5,2),
satellites INT,
source VARCHAR(255),
gnss_height DECIMAL(5,2),
feature_type VARCHAR(255),
corner VARCHAR(255),
importe_by VARCHAR(255),
updated_by VARCHAR(255),
updated_date DATE,
import_date DATE
);
CREATE TABLE pa_restoration_monitoring_network.feature_status_gullies (
rmn_id VARCHAR(255),
grant_id VARCHAR(255),
visit VARCHAR(255),
sampling_point VARCHAR(255),
depth VARCHAR(255),
width VARCHAR(255),
side VARCHAR(255),
angle VARCHAR(255),
vegetation_establishing BOOLEAN,
trampling BOOLEAN,
flow BOOLEAN,
bare_peat VARCHAR(255),
bare_mineral VARCHAR(255),
open_water VARCHAR(255),
litter VARCHAR(255),
vegetation VARCHAR(255),
dwarf_shrub VARCHAR(255),
eriophorum VARCHAR(255),
trichophorum VARCHAR(255),
molinia VARCHAR(255),
other_poaceae VARCHAR(255),
juncus VARCHAR(255),
sphagnum VARCHAR(255),
other_moss VARCHAR(255),
lichen VARCHAR(255),
block_present BOOLEAN,
erosion_around VARCHAR(255),
erosion_over VARCHAR(255),
block_trampling VARCHAR(255),
water_retained VARCHAR(255),
water_retained_sphagnum VARCHAR(255),
sediment_retained VARCHAR(255),
block_vegetation_establishing VARCHAR(255),
donor BOOLEAN,
donor_vegetation_establishing VARCHAR(255),
score VARCHAR(255),
notes VARCHAR(255),
importe_by VARCHAR(255),
updated_by VARCHAR(255),
updated_date DATE,
import_date DATE
);
CREATE TABLE pa_restoration_monitoring_network.feature_status_hags (
rmn_id VARCHAR(255),
grant_id VARCHAR(255),
visit VARCHAR(255),
sampling_point VARCHAR(255),
height VARCHAR(255),
angle VARCHAR(255),
vegetation_establishing BOOLEAN,
trampling BOOLEAN,
erosion BOOLEAN,
donor BOOLEAN,
donor_vegetation_establishing VARCHAR(255),
score VARCHAR(255),
notes VARCHAR(255),
importe_by VARCHAR(255),
updated_by VARCHAR(255),
updated_date DATE,
import_date DATE
);
CREATE TABLE pa_restoration_monitoring_network.feature_status_bare_peat (
rmn_id VARCHAR(255),
grant_id VARCHAR(255),
visit VARCHAR(255),
sampling_point VARCHAR(255),
area VARCHAR(255),
vegetation_establishing BOOLEAN,
trampling BOOLEAN,
erosion BOOLEAN,
bund_present BOOLEAN,
bund_erosion VARCHAR(255),
water_retained VARCHAR(255),
water_retained_sphagnum VARCHAR(255),
bund_vegetation_establishing VARCHAR(255),
donor BOOLEAN,
donor_vegetation_establishing VARCHAR(255),
score VARCHAR(255),
notes VARCHAR(255),
importe_by VARCHAR(255),
updated_by VARCHAR(255),
updated_date DATE,
import_date DATE
);
CREATE TABLE pa_restoration_monitoring_network.feature_status_forest_to_bog (
rmn_id VARCHAR(255),
grant_id VARCHAR(255),
visit VARCHAR(255),
sampling_point VARCHAR(255),
tree_cover VARCHAR(255),
tree_height VARCHAR(255),
mulch VARCHAR(255),
bare_peat VARCHAR(255),
tree_regen BOOLEAN,
ground_level BOOLEAN,
peat_cracking BOOLEAN,
score VARCHAR(255),
notes VARCHAR(255),
importe_by VARCHAR(255),
updated_by VARCHAR(255),
updated_date DATE,
import_date DATE
);
CREATE TABLE pa_restoration_monitoring_network.drain_points (
rmn_id VARCHAR(10),
grant_id VARCHAR(10),
visit VARCHAR(50),
sampling_point_id VARCHAR(50),
easting DECIMAL(10,6),
northing DECIMAL(10,6),
horizontal_accuracy DECIMAL(5,2),
vertical_accuracy DECIMAL(5,2),
date DATE,
elevation DECIMAL(5,2),
satellites INT,
drain_point_id VARCHAR(255),
gnss_height DECIMAL(5,2),
importe_by VARCHAR(255),
updated_by VARCHAR(255),
updated_date DATE,
import_date DATE
);
CREATE TABLE pa_restoration_monitoring_network.fpp_points (
rmn_id VARCHAR(10),
grant_id VARCHAR(10),
visit VARCHAR(50),
easting DECIMAL(10,6),
northing DECIMAL(10,6),
bearing INT,
vertical_accuracy DECIMAL(5,2),
camera_height INT,
importe_by VARCHAR(255),
updated_by VARCHAR(255),
updated_date DATE,
import_date DATE
);