Open knutole opened 8 years ago
Hi @strk . Working on this little problem, quite difficult for me, perhaps very simple for you?
So, to explain the query that I need to make:
We have a set of rasters (aka datasets in a cube) for which I need to query the snow cover fraction
. The rasters are Byte
type, and the classification is shown below. The snow cover fraction classified between values 100-200 in the raster. So I need to query the raster (either the whole thing, or within a GeoJSON mask) and get the average snow cover fraction of all pixels (ie. the value between 100 and 200).
The problem with querying ST_SummaryStats
on the raster, is that it includes values below 100 and above 200, while we only want to take into consideration the pixels with values within the 100-200 range.
I'm currently experimenting with this:
SELECT rid, band, (stats).* FROM (SELECT rid, band, ST_SummaryStats(rast, band) As stats FROM file_pszjkuicfjvxvizbygbq CROSS JOIN generate_series(1,1) As band ) As foo;
Another problem with this approach, is that the raster is cut (due to pretiling) into 640 rows. So I'm getting results per row, and not for the whole.
The end result should simply be the SCF
, ie. the average value of all pixels within 100-200 range (subtracted by 100 to get %).
If you have time, @strk, could you help point me in the right direction? (By the way, will get back to you in the end of the week with some exciting developments with Systemapic...)
Byte
type classification of Snow Cover Fraction rasters (100-200 is area for calculating scf
):0 Night
1 UNC
2 UNC
3 UNC
4 UNC
5 UNC
6 UNC
7 UNC
8 UNC
9 UNC
10 Outside/Masked
11 Outside/Masked
12 Outside/Masked
13 Outside/Masked
14 Outside/Masked
15 Outside/Masked
16 Outside/Masked
17 Outside/Masked
18 Outside/Masked
19 Outside/Masked
20 Water
21 Water
22 Water
23 Water
24 Water
25 Water
26 Water
27 Water
28 Water
29 Water
30 Clouds
31 Clouds
32 Clouds
33 Clouds
34 Clouds
35 Clouds
36 Clouds
37 Clouds
38 Clouds
39 Clouds
40 Outside/Masked
41 Outside/Masked
42 Outside/Masked
43 Outside/Masked
44 Outside/Masked
45 Outside/Masked
46 Outside/Masked
47 Outside/Masked
48 Outside/Masked
49 Outside/Masked
50 Bare Ground
51 Bare Ground
52 Bare Ground
53 Bare Ground
54 Bare Ground
55 Bare Ground
56 Bare Ground
57 Bare Ground
58 Bare Ground
59 Bare Ground
60 Forest
61 Forest
62 Forest
63 Forest
64 Forest
65 Forest
66 Forest
67 Forest
68 Forest
69 Forest
70 Glacier
71 Glacier
72 Glacier
73 Glacier
74 Glacier
75 Glacier
76 Glacier
77 Glacier
78 Glacier
79 Glacier
80 UNC
81 UNC
82 UNC
83 UNC
84 UNC
85 UNC
86 UNC
87 UNC
88 UNC
89 UNC
90 UNC
91 UNC
92 UNC
93 UNC
94 UNC
95 UNC
96 UNC
97 UNC
98 UNC
99 UNC
100 Bare Ground
101 0-10% Snow
102 0-10% Snow
103 0-10% Snow
104 0-10% Snow
105 0-10% Snow
106 0-10% Snow
107 0-10% Snow
108 0-10% Snow
109 0-10% Snow
110 10-20% Snow
111 10-20% Snow
112 10-20% Snow
113 10-20% Snow
114 10-20% Snow
115 10-20% Snow
116 10-20% Snow
117 10-20% Snow
118 10-20% Snow
119 10-20% Snow
120 20-35% Snow
121 20-35% Snow
122 20-35% Snow
123 20-35% Snow
124 20-35% Snow
125 20-35% Snow
126 20-35% Snow
127 20-35% Snow
128 20-35% Snow
129 20-35% Snow
130 20-35% Snow
131 20-35% Snow
132 20-35% Snow
133 20-35% Snow
134 20-35% Snow
135 35-50% Snow
136 35-50% Snow
137 35-50% Snow
138 35-50% Snow
139 35-50% Snow
140 35-50% Snow
141 35-50% Snow
142 35-50% Snow
143 35-50% Snow
144 35-50% Snow
145 35-50% Snow
146 35-50% Snow
147 35-50% Snow
148 35-50% Snow
149 35-50% Snow
150 50-65% Snow
151 50-65% Snow
152 50-65% Snow
153 50-65% Snow
154 50-65% Snow
155 50-65% Snow
156 50-65% Snow
157 50-65% Snow
158 50-65% Snow
159 50-65% Snow
160 50-65% Snow
161 50-65% Snow
162 50-65% Snow
163 50-65% Snow
164 50-65% Snow
165 65-80% Snow
166 65-80% Snow
167 65-80% Snow
168 65-80% Snow
169 65-80% Snow
170 65-80% Snow
171 65-80% Snow
172 65-80% Snow
173 65-80% Snow
174 65-80% Snow
175 65-80% Snow
176 65-80% Snow
177 65-80% Snow
178 65-80% Snow
179 65-80% Snow
180 80-90% Snow
181 80-90% Snow
182 80-90% Snow
183 80-90% Snow
184 80-90% Snow
185 80-90% Snow
186 80-90% Snow
187 80-90% Snow
188 80-90% Snow
189 80-90% Snow
190 90-99% Snow
191 90-99% Snow
192 90-99% Snow
193 90-99% Snow
194 90-99% Snow
195 90-99% Snow
196 90-99% Snow
197 90-99% Snow
198 90-99% Snow
199 90-99% Snow
200 100% Snow
201 UNC
202 UNC
203 UNC
204 UNC
205 Wet snow
206 UNC
207 UNC
208 UNC
209 UNC
210 Dry Snow
211 UNC
212 UNC
213 UNC
214 UNC
215 UNC
216 UNC
217 UNC
218 UNC
219 UNC
220 UNC
221 UNC
222 UNC
223 UNC
224 UNC
225 UNC
226 UNC
227 UNC
228 UNC
229 UNC
230 UNC
231 UNC
232 UNC
233 UNC
234 UNC
235 UNC
236 UNC
237 UNC
238 UNC
239 UNC
240 UNC
241 UNC
242 UNC
243 UNC
244 UNC
245 UNC
246 UNC
247 UNC
248 UNC
249 UNC
250 UNC
251 UNC
252 UNC
253 UNC
254 UNC
255 Ice on Lake
Making progress on this, so no worries. :)
We need to implement a few raster calculations:
1. min, max, avg per raster
In order to create this graph, we need to pull
Also need to create a js wrapper fn to concat all raster stats into one array (for each year, or for give range).
min, max, avg
of each day (ie. each raster):2. min, max, avg per raster, by geojson mask
Same as above, but instead of whole raster, only over an area (geojson mask).