1/// AIS navigational status codes. 2const SHIPS_NAV_STATUS = { 3 0: 'under way (engine)', 1: 'at anchor', 2: 'not under command', 4 3: 'restricted maneuverability', 4: 'constrained by draught', 5 5: 'moored', 6: 'aground', 7: 'fishing', 8: 'under way (sailing)', 6 9: 'code 9 (hsc)', 10: 'code 10 (wig)', 11: 'code 11', 7 12: 'code 12', 13: 'code 13', 14: 'AIS-SART', 15: 'not defined' 8}; 9 10const datasets = { 11 "Planes": { 12 notice: "© adsb.lol (ODbL v1.0), © airplanes.live, © adsbexchange.com", 13 endpoints: [ 14 { 15 name: "Cloud (Real-Time)", 16 urls: [ 17 { 18 url: "https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 19 sticky: "https://{hash}.sticky.kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 20 } 21 ] 22 }, 23 ], 24 levels: [ 25 { table: 'planes_mercator_sample100', sample: 100, priority: 1 }, 26 { table: 'planes_mercator_sample10', sample: 10, priority: 2 }, 27 { table: 'planes_mercator', sample: 1, priority: 3 }, 28 ], 29 time: { column: 'time' }, 30 report_total: { 31 query: (condition => ` 32 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 33 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 34 SELECT 35 count() AS traces, 36 uniq(r) AS aircrafts, 37 uniq(t) AS types, 38 uniqIf(aircraft_flight, 39 aircraft_flight != '') AS flights, 40 min(time) AS first, max(time) AS last 41 FROM {table:Identifier} 42 WHERE ${condition}`), 43 content: (json => { 44 let row = json.data[0]; 45 let text = `Total ${Number(row.traces).toLocaleString()} traces, ${Number(row.aircrafts).toLocaleString()} aircrafts of ${Number(row.types).toLocaleString()} types, ${Number(row.aircrafts).toLocaleString()} flight nums.`; 46 47 if (row.traces > 0) { 48 text += ` Time: ${row.first} â ${row.last}.`; 49 } 50 51 if (json.statistics.rows_read > 1) { 52 text += ` Processed ${Number(json.statistics.rows_read).toLocaleString()} rows.`; 53 } 54 55 return text; 56 }), 57 }, 58 reports: [ 59 { 60 query: (condition => ` 61 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 62 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 63 SELECT aircraft_flight, count() AS c 64 FROM {table:Identifier} 65 WHERE aircraft_flight != '' AND NOT startsWith(aircraft_flight, '@@@') AND ${condition} 66 GROUP BY aircraft_flight 67 ORDER BY c DESC 68 LIMIT 100`), 69 field: 'aircraft_flight', 70 id: 'report_flights', 71 title: 'Flights: ', 72 separator: ', ', 73 content: (row => row.aircraft_flight) 74 }, 75 { 76 query: (condition => ` 77 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 78 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 79 SELECT t, anyIf(desc, desc != '') AS desc, count() AS c 80 FROM {table:Identifier} 81 WHERE t != '' AND ${condition} 82 GROUP BY t 83 ORDER BY c DESC 84 LIMIT 100`), 85 field: 't', 86 wiki_field: 'desc', 87 id: 'report_types', 88 title: 'Types:\n', 89 separator: ',\n', 90 content: (row => `${row.t} (${row.desc})`) 91 }, 92 { 93 query: (condition => ` 94 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 95 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 96 SELECT r, count() AS c 97 FROM {table:Identifier} 98 WHERE r != '' AND ${condition} 99 GROUP BY r 100 ORDER BY c DESC 101 LIMIT 100`), 102 field: 'r', 103 id: 'report_regs', 104 title: 'Registration: ', 105 separator: ', ', 106 content: (row => row.r) 107 }, 108 { 109 query: (condition => ` 110 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 111 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 112 SELECT ownOp, count() AS c 113 FROM {table:Identifier} 114 WHERE ownOp != '' AND ${condition} 115 GROUP BY ownOp 116 ORDER BY c DESC 117 LIMIT 100`), 118 field: 'ownOp', 119 id: 'report_owners', 120 title: 'Owner:\n', 121 separator: ',\n', 122 content: (row => row.ownOp) 123 }, 124 ], 125 queries: { 126"Altitude & Velocity": `WITH 127 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 128 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 129 130 tile_size * {x:UInt32} AS tile_x_begin, 131 tile_size * ({x:UInt32} + 1) AS tile_x_end, 132 133 tile_size * {y:UInt32} AS tile_y_begin, 134 tile_size * ({y:UInt32} + 1) AS tile_y_end, 135 136 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 137 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 138 139 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 140 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 141 142 y * 1024 + x AS pos, 143 144 count() AS total, 145 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 146
147 pow(total / max_total, 1/5) AS transparency, 148 greatest(0, least(avg(altitude), 5000)) / 5000 AS color1, 149 greatest(0, least(avg(altitude), 50000)) / 50000 AS color3, 150 greatest(0, least(avg(ground_speed), 700)) / 700 AS color2, 151 152 255 AS alpha, 153 (1 + transparency) / 2 * (1 - color3) * 255 AS red, 154 transparency * color1 * 255 AS green, 155 color2 * 255 AS blue 156 157SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 158FROM {table:Identifier} 159WHERE in_tile 160GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 161 162"Boeing vs. Airbus": `WITH 163 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 164 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 165 166 tile_size * {x:UInt32} AS tile_x_begin, 167 tile_size * ({x:UInt32} + 1) AS tile_x_end, 168 169 tile_size * {y:UInt32} AS tile_y_begin, 170 tile_size * ({y:UInt32} + 1) AS tile_y_end, 171 172 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 173 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 174 175 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 176 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 177 178 y * 1024 + x AS pos, 179 180 count() AS total, 181 sum(desc LIKE 'BOEING%') AS boeing, 182 sum(desc LIKE 'AIRBUS%') AS airbus, 183 sum(NOT (desc LIKE 'BOEING%' OR desc LIKE 'AIRBUS%')) AS other, 184 185 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, total) AS max_total, 186 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, boeing) AS max_boeing, 187 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, airbus) AS max_airbus, 188 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, other) AS max_other, 189 190 pow(total / max_total, 1/5) AS transparency, 191 192 255 * (1 + transparency) / 2 AS alpha, 193 pow(boeing, 1/5) * 256 DIV (1 + pow(max_boeing, 1/5)) AS red, 194 pow(airbus, 1/5) * 256 DIV (1 + pow(max_airbus, 1/5)) AS green, 195 pow(other, 1/5) * 256 DIV (1 + pow(max_other, 1/5)) AS blue 196 197SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 198FROM {table:Identifier} 199WHERE in_tile 200GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 201 202"Helicopters": `WITH 203 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 204 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 205 206 tile_size * {x:UInt32} AS tile_x_begin, 207 tile_size * ({x:UInt32} + 1) AS tile_x_end, 208 209 tile_size * {y:UInt32} AS tile_y_begin, 210 tile_size * ({y:UInt32} + 1) AS tile_y_end, 211 212 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 213 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 214 215 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 216 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 217 218 y * 1024 + x AS pos, 219 220 count() AS total, 221 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 222 223 pow(total / max_total, 1/5) AS transparency, 224 greatest(0, least(avg(altitude), 500)) / 500 AS color1, 225 greatest(0, least(avg(altitude), 5000)) / 5000 AS color3, 226 greatest(0, least(avg(ground_speed), 200)) / 200 AS color2, 227 228 255 AS alpha, 229 (1 + transparency) / 2 * (1 - color3) * 255 AS red, 230 transparency * color1 * 255 AS green, 231 color2 * 255 AS blue 232 233SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 234FROM {table:Identifier} 235WHERE in_tile AND aircraft_category = 'A7' AND ground_speed < 200 236GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 237 238"Hi-Performance": `WITH 239 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 240 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 241 242 tile_size * {x:UInt32} AS tile_x_begin, 243 tile_size * ({x:UInt32} + 1) AS tile_x_end, 244 245 tile_size * {y:UInt32} AS tile_y_begin, 246 tile_size * ({y:UInt32} + 1) AS tile_y_end, 247 248 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 249 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 250 251 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 252 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 253 254 y * 1024 + x AS pos, 255 256 count() AS total, 257 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, total) AS max_total, 258
259 pow(total / max_total, 1/5) AS transparency, 260 261 0 AS red, 262 255 AS green, 263 255 AS blue, 264 265 255 * transparency AS alpha 266 267SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 268FROM {table:Identifier} 269WHERE in_tile AND aircraft_category = 'A6' 270GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 271 272"Light": `WITH 273 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 274 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 275 276 tile_size * {x:UInt32} AS tile_x_begin, 277 tile_size * ({x:UInt32} + 1) AS tile_x_end, 278 279 tile_size * {y:UInt32} AS tile_y_begin, 280 tile_size * ({y:UInt32} + 1) AS tile_y_end, 281 282 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 283 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 284 285 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 286 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 287 288 y * 1024 + x AS pos, 289 290 count() AS total, 291 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, total) AS max_total, 292 pow(total / max_total, 1/5) AS transparency, 293 294 greatest(0, least(avg(altitude), 50000)) / 50000 AS color1, 295 greatest(0, least(avg(ground_speed), 700)) / 700 AS color2, 296 297 255 * transparency AS red, 298 255 * color2 AS green, 299 255 * color1 AS blue, 300 255 * (1/4 + 3/4 * transparency) AS alpha 301 302SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 303FROM {table:Identifier} 304WHERE in_tile AND aircraft_category = 'A1' 305GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 306 307"Vertical Speed": `WITH 308 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 309 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 310 311 tile_size * {x:UInt32} AS tile_x_begin, 312 tile_size * ({x:UInt32} + 1) AS tile_x_end, 313 314 tile_size * {y:UInt32} AS tile_y_begin, 315 tile_size * ({y:UInt32} + 1) AS tile_y_end, 316 317 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 318 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 319 320 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 321 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 322 323 y * 1024 + x AS pos, 324 325 least(255, 2 * greatest(red, green)) AS alpha, 326 255 * least(1, avg(greatest(0, vertical_rate)) / 5000) AS green, 327 255 * least(1, avg(least(0, vertical_rate)) / -5000) AS red, 328 0 AS blue 329 330SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 331FROM {table:Identifier} 332WHERE in_tile 333GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 334 335"Roll Angle": `WITH 336 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 337 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 338 339 tile_size * {x:UInt32} AS tile_x_begin, 340 tile_size * ({x:UInt32} + 1) AS tile_x_end, 341 342 tile_size * {y:UInt32} AS tile_y_begin, 343 tile_size * ({y:UInt32} + 1) AS tile_y_end, 344 345 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 346 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 347 348 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 349 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 350 351 y * 1024 + x AS pos, 352 353 255 * least(1, avg(abs(roll_angle)) / 10) AS alpha, 354 255 * avg(max2(0, roll_angle)) / 21 AS red, 355 255 * avg(min2(0, roll_angle)) / -21 AS green, 356 (1 - alpha) AS blue 357 358SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 359FROM {table:Identifier} 360WHERE in_tile 361GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 362 363"Year": `WITH 364 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 365 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 366 367 tile_size * {x:UInt32} AS tile_x_begin, 368 tile_size * ({x:UInt32} + 1) AS tile_x_end, 369 370 tile_size * {y:UInt32} AS tile_y_begin, 371 tile_size * ({y:UInt32} + 1) AS tile_y_end, 372 373 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 374 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 375 376 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 377 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 378 379 y * 1024 + x AS pos, 380 381 count() AS total, 382 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, total) AS max_total, 383
384 pow(total / max_total, 1/5) AS transparency, 385 386 255 * transparency AS alpha, 387 255 * avg(year < 2000) AS red, 388 255 * avg(year >= 2010) AS green, 389 alpha AS blue 390 391SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 392FROM {table:Identifier} 393WHERE in_tile AND year != 0 394GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 395 396"A380": `WITH 397 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 398 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 399 400 tile_size * {x:UInt32} AS tile_x_begin, 401 tile_size * ({x:UInt32} + 1) AS tile_x_end, 402 403 tile_size * {y:UInt32} AS tile_y_begin, 404 tile_size * ({y:UInt32} + 1) AS tile_y_end, 405 406 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 407 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 408 409 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 410 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 411 412 y * 1024 + x AS pos, 413 414 count() AS total, 415 greatest(100000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 416 417 pow(total / max_total, 1/5) AS transparency, 418 greatest(0, least(avg(altitude), 5000)) / 5000 AS color1, 419 greatest(0, least(avg(altitude), 50000)) / 50000 AS color3, 420 greatest(0, least(avg(ground_speed), 700)) / 700 AS color2, 421 422 255 AS alpha, 423 (1 + transparency) / 2 * (1 - color3) * 255 AS red, 424 transparency * color1 * 255 AS green, 425 color2 * 255 AS blue 426 427SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 428FROM {table:Identifier} 429WHERE in_tile AND t = 'A388' 430GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 431 432"IL-76": `WITH 433 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 434 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 435 436 tile_size * {x:UInt32} AS tile_x_begin, 437 tile_size * ({x:UInt32} + 1) AS tile_x_end, 438 439 tile_size * {y:UInt32} AS tile_y_begin, 440 tile_size * ({y:UInt32} + 1) AS tile_y_end, 441 442 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 443 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 444 445 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 446 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 447 448 y * 1024 + x AS pos, 449 450 greatest(0, least(avg(altitude), 50000)) / 50000 AS color1, 451 greatest(0, least(avg(ground_speed), 700)) / 700 AS color2, 452 453 255 AS alpha, 454 255 AS red, 455 color1 * 255 AS green, 456 color2 * 255 AS blue 457 458SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 459FROM {table:Identifier} 460WHERE in_tile AND t = 'IL76' 461GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 462 463"F-16": `WITH 464 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 465 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 466 467 tile_size * {x:UInt32} AS tile_x_begin, 468 tile_size * ({x:UInt32} + 1) AS tile_x_end, 469 470 tile_size * {y:UInt32} AS tile_y_begin, 471 tile_size * ({y:UInt32} + 1) AS tile_y_end, 472 473 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 474 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 475 476 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 477 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 478 479 y * 1024 + x AS pos, 480 481 count() AS total, 482 greatest(1000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 483 pow(total / max_total, 1/5) AS transparency, 484 485 greatest(0, least(avg(altitude), 50000)) / 50000 AS color1, 486 greatest(0, least(avg(ground_speed), 700)) / 700 AS color2, 487 488 transparency * 255 AS alpha, 489 255 AS red, 490 color1 * 255 AS green, 491 color2 * 255 AS blue 492 493SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 494FROM {table:Identifier} 495WHERE in_tile AND t = 'F16' 496GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 497 498"KLM": `WITH 499 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 500 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 501 502 tile_size * {x:UInt32} AS tile_x_begin, 503 tile_size * ({x:UInt32} + 1) AS tile_x_end, 504 505 tile_size * {y:UInt32} AS tile_y_begin, 506 tile_size * ({y:UInt32} + 1) AS tile_y_end, 507 508 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 509 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 510 511 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 512 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 513 514 y * 1024 + x AS pos, 515 516 count() AS total, 517 greatest(100000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 518
519 pow(total / max_total, 1/5) AS transparency, 520 greatest(0, least(avg(altitude), 5000)) / 5000 AS color1, 521 greatest(0, least(avg(altitude), 50000)) / 50000 AS color3, 522 greatest(0, least(avg(ground_speed), 700)) / 700 AS color2, 523 524 255 AS alpha, 525 (1 + transparency) / 2 * (1 - color3) * 255 AS red, 526 transparency * color1 * 255 AS green, 527 color2 * 255 AS blue 528 529SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 530FROM {table:Identifier} AS t 531WHERE in_tile AND aircraft_flight LIKE 'KLM%' 532GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 533 534"N2163J": `WITH 535 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 536 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 537 538 tile_size * {x:UInt32} AS tile_x_begin, 539 tile_size * ({x:UInt32} + 1) AS tile_x_end, 540 541 tile_size * {y:UInt32} AS tile_y_begin, 542 tile_size * ({y:UInt32} + 1) AS tile_y_end, 543 544 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 545 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 546 547 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 548 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 549 550 y * 1024 + x AS pos, 551 552 count() AS total, 553 max(total) OVER () AS max_total, 554 555 pow(total / max_total, 1/5) AS transparency, 556 greatest(0, least(avg(altitude), 5000)) / 5000 AS color1, 557 greatest(0, least(avg(ground_speed), 100)) / 100 AS color2, 558 559 255 AS alpha, 560 transparency * 255 AS red, 561 transparency * color1 * 255 AS green, 562 color2 * 255 AS blue 563 564SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 565FROM {table:Identifier} AS t 566WHERE in_tile AND r = 'N2163J' 567GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 568 569"Gliders": `WITH 570 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 571 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 572 573 tile_size * {x:UInt32} AS tile_x_begin, 574 tile_size * ({x:UInt32} + 1) AS tile_x_end, 575 576 tile_size * {y:UInt32} AS tile_y_begin, 577 tile_size * ({y:UInt32} + 1) AS tile_y_end, 578 579 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 580 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 581 582 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 583 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 584 585 y * 1024 + x AS pos, 586 587 count() AS total, 588 greatest(100000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 589 pow(total / max_total, 1/5) AS transparency, 590 591 greatest(0, least(avg(altitude), 5000)) / 5000 AS color1, 592 greatest(0, least(avg(ground_speed), 100)) / 100 AS color2, 593 594 255 * color2 AS blue, 595 255 * transparency * (color1 + color2) / 2 AS green, 596 255 * (1 - color1) AS red, 597 255 * (1 + transparency) / 2 AS alpha 598 599SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 600FROM {table:Identifier} 601WHERE in_tile AND aircraft_category = 'B1' 602GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 603 604"Ultralight": `WITH 605 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 606 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 607 608 tile_size * {x:UInt32} AS tile_x_begin, 609 tile_size * ({x:UInt32} + 1) AS tile_x_end, 610 611 tile_size * {y:UInt32} AS tile_y_begin, 612 tile_size * ({y:UInt32} + 1) AS tile_y_end, 613 614 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 615 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 616 617 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 618 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 619 620 y * 1024 + x AS pos, 621 622 count() AS total, 623 greatest(100000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 624 pow(total / max_total, 1/5) AS transparency, 625 626 greatest(0, least(avg(altitude), 5000)) / 5000 AS color1, 627 greatest(0, least(avg(ground_speed), 100)) / 100 AS color2, 628 629 255 * color2 AS blue, 630 255 * transparency * (color1 + color2) / 2 AS green, 631 255 * (1 - color1) AS red, 632 255 * (1 + transparency) / 2 AS alpha 633 634SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 635FROM {table:Identifier} 636WHERE in_tile AND aircraft_category = 'B4' 637GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 638 639"Event Time": `WITH 640 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 641 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 642 643 tile_size * {x:UInt32} AS tile_x_begin, 644 tile_size * ({x:UInt32} + 1) AS tile_x_end, 645 646 tile_size * {y:UInt32} AS tile_y_begin, 647 tile_size * ({y:UInt32} + 1) AS tile_y_end, 648 649 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 650 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 651 652 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 653 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 654 655 y * 1024 + x AS pos, 656 657 avg(time::Float64) AS offset, 658 min(offset) OVER () AS min_offset, 659 max(offset) OVER () AS max_offset, 660 661 (1 + offset - min_offset) / (1 + max_offset - min_offset) AS rel_time, 662 663 255 AS alpha, 664 255 * rel_time AS green, 665 255 * (1 - rel_time) AS red, 666 0 AS blue 667 668SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 669FROM {table:Identifier} 670WHERE in_tile 671GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 672 673"Weekends": `WITH 674 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 675 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 676 677 tile_size * {x:UInt32} AS tile_x_begin, 678 tile_size * ({x:UInt32} + 1) AS tile_x_end, 679 680 tile_size * {y:UInt32} AS tile_y_begin, 681 tile_size * ({y:UInt32} + 1) AS tile_y_end, 682 683 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 684 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 685 686 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 687 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 688 689 y * 1024 + x AS pos, 690 691 count() AS total, 692 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total,
693 pow(total / max_total, 1/5) AS transparency, 694 695 toDayOfWeek(date + INTERVAL lon / 15 HOUR) > 5 AS weekend, 696 avg(weekend) AS c_weekend, 697 avg(NOT weekend) AS c_weekday, 698 699 c_weekend * 2.5 > c_weekday AS mostly_weekends, 700 701 255 * transparency AS alpha, 702 255 * c_weekend * mostly_weekends AS red, 703 red / 2 AS green, 704 255 * c_weekday * (NOT mostly_weekends) AS blue 705 706SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 707FROM {table:Identifier} 708WHERE in_tile 709GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 710 711"Elon Musk": `WITH 712 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 713 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 714 715 tile_size * {x:UInt32} AS tile_x_begin, 716 tile_size * ({x:UInt32} + 1) AS tile_x_end, 717 718 tile_size * {y:UInt32} AS tile_y_begin, 719 tile_size * ({y:UInt32} + 1) AS tile_y_end, 720 721 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 722 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 723 724 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 725 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 726 727 y * 1024 + x AS pos, 728 729 count() AS total, 730 transform(r, ['N628TS', 'N272BG', 'N502SX', 'N140FJ'], [0xFF8888, 0x88FF88, 0xAAAAFF, 0xFFFF00], 0) AS color, 731 732 255 AS alpha, 733 avg(color DIV 0x10000) AS red, 734 avg(color DIV 0x100 MOD 0x100) AS green, 735 avg(color MOD 0x100) AS blue 736 737SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 738FROM {table:Identifier} AS t 739WHERE in_tile AND r IN ('N628TS', 'N272BG', 'N502SX', 'N140FJ') 740GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 741 742"Military": `WITH 743 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 744 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 745 746 tile_size * {x:UInt32} AS tile_x_begin, 747 tile_size * ({x:UInt32} + 1) AS tile_x_end, 748 749 tile_size * {y:UInt32} AS tile_y_begin, 750 tile_size * ({y:UInt32} + 1) AS tile_y_end, 751 752 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 753 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 754 755 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 756 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 757 758 y * 1024 + x AS pos, 759 760 count() AS total, 761 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 762 763 pow(total / max_total, 1/5) AS transparency, 764 greatest(0, least(avg(altitude), 5000)) / 5000 AS color1, 765 greatest(0, least(avg(altitude), 50000)) / 50000 AS color3, 766 greatest(0, least(avg(ground_speed), 700)) / 700 AS color2, 767 768 255 AS alpha, 769 (1 + transparency) / 2 * (1 - color3) * 255 AS red, 770 transparency * color1 * 255 AS green, 771 color2 * 255 AS blue 772 773SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 774FROM {table:Identifier} 775WHERE in_tile AND dbFlags = 1 776GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 777 778"Steep": `WITH 779 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 780 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 781 782 tile_size * {x:UInt32} AS tile_x_begin, 783 tile_size * ({x:UInt32} + 1) AS tile_x_end, 784 785 tile_size * {y:UInt32} AS tile_y_begin, 786 tile_size * ({y:UInt32} + 1) AS tile_y_end, 787 788 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 789 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 790 791 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 792 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 793 794 y * 1024 + x AS pos, 795 796 count() AS total, 797 greatest(100000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 798 799 pow(total / max_total, 1/5) AS transparency, 800 greatest(0, least(avg(altitude), 5000)) / 5000 AS color1, 801 greatest(0, least(avg(altitude), 20000)) / 20000 AS color3, 802 least(avg(abs(vertical_rate)), 10000) / 10000 AS color2, 803 804 (1 + transparency) / 2 * 255 AS alpha, 805 (1 + transparency) / 2 * (1 - color3) * 255 AS red, 806 transparency * color1 * 255 AS green, 807 color2 * 255 AS blue 808 809SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 810FROM {table:Identifier} 811WHERE in_tile AND ground_speed > 0 AND ground_speed < 50 AND abs(vertical_rate) > 5000 812GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 813 814"Emergency": `WITH 815 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 816 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 817 818 tile_size * {x:UInt32} AS tile_x_begin, 819 tile_size * ({x:UInt32} + 1) AS tile_x_end, 820 821 tile_size * {y:UInt32} AS tile_y_begin, 822 tile_size * ({y:UInt32} + 1) AS tile_y_end, 823 824 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 825 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 826 827 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 828 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 829 830 y * 1024 + x AS pos, 831 832 transform(aircraft_emergency, 833 ['general', 'nordo', 'downed', 'lifeguard', 'reserved', 'unlawful', 'minfuel'], 834 [0x0000FF, 0xFF0000, 0xFFFF00, 0x00FF00, 0x00FFFF, 0xFF00FF, 0xFFFFFF], 0) AS color, 835 836 255 AS alpha, 837 avg(color DIV 0x10000) AS red,
838 avg(color DIV 0x100 MOD 0x100) AS green, 839 avg(color MOD 0x100) AS blue 840 841SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 842FROM {table:Identifier} AS t 843WHERE in_tile AND aircraft_emergency NOT IN ('', 'none') 844GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 845 846"Balloons": `WITH 847 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 848 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 849 850 tile_size * {x:UInt32} AS tile_x_begin, 851 tile_size * ({x:UInt32} + 1) AS tile_x_end, 852 853 tile_size * {y:UInt32} AS tile_y_begin, 854 tile_size * ({y:UInt32} + 1) AS tile_y_end, 855 856 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 857 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 858 859 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 860 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 861 862 y * 1024 + x AS pos, 863 864 count() AS total, 865 greatest(1000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 866 pow(total / max_total, 1/5) AS transparency, 867 868 greatest(0, least(avg(altitude), 10000)) / 10000 AS color1, 869 greatest(0, least(avg(ground_speed), 100)) / 100 AS color2, 870 871 255 * color2 AS blue, 872 255 * color1 AS red, 873 255 * (1 - color1) AS green, 874 255 * transparency AS alpha 875 876SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 877FROM {table:Identifier} 878WHERE in_tile AND aircraft_category = 'B2' 879GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 880 881"Ground Vehicles": `WITH 882 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 883 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 884 885 tile_size * {x:UInt32} AS tile_x_begin, 886 tile_size * ({x:UInt32} + 1) AS tile_x_end, 887 888 tile_size * {y:UInt32} AS tile_y_begin, 889 tile_size * ({y:UInt32} + 1) AS tile_y_end, 890 891 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 892 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 893 894 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 895 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 896 897 y * 1024 + x AS pos, 898 899 count() AS total, 900 greatest(1000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 901 pow(total / max_total, 1/5) AS transparency, 902 903 greatest(0, least(avg(ground_speed), 50)) / 50 AS color, 904 905 255 * transparency * color AS green, 906 255 * (1 - color) AS red, 907 255 * color AS blue, 908 255 AS alpha 909 910SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 911FROM {table:Identifier} 912WHERE in_tile AND aircraft_category IN ('C1', 'C2') 913GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 914 915"All Airlines": `WITH 916 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 917 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 918 919 tile_size * {x:UInt32} AS tile_x_begin, 920 tile_size * ({x:UInt32} + 1) AS tile_x_end, 921 922 tile_size * {y:UInt32} AS tile_y_begin, 923 tile_size * ({y:UInt32} + 1) AS tile_y_end, 924 925 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 926 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 927 928 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 929 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 930 931 y * 1024 + x AS pos, 932 933 count() AS total, 934 greatest(100000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 935 pow(total / max_total, 1/5) AS transparency, 936 937 cityHash64(substring(aircraft_flight, 1, 3)) AS hash, 938 939 transparency * 255 AS alpha, 940 avg(hash MOD 256) AS red, 941 avg(hash DIV 256 MOD 256) AS green, 942 avg(hash DIV 65536 MOD 256) AS blue 943 944SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 945FROM {table:Identifier} 946WHERE in_tile AND aircraft_flight != '' 947GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024` 948 } 949 }, 950 951 "Places": { 952 notice: "© Foursquare Labs, Inc., Apache 2.0", 953 endpoints: [ 954 { 955 name: "Any", 956 urls: [ 957 { 958 url: "https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 959 sticky: "https://{hash}.sticky.kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 960 }, 961 { 962 url: "https://fly-selfhosted-backend-3.clickhouse.com", 963 } 964 ] 965 }, 966 { 967 name: "Cloud (Real-Time)", 968 urls: [ 969 { 970 url: "https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 971 sticky: "https://{hash}.sticky.kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 972 } 973 ] 974 }, 975 { 976 name: "Self-hosted (Snapshot)", 977 urls: [ 978 { 979 url: "https://fly-selfhosted-backend-3.clickhouse.com", 980 } 981 ] 982 }, 983 ], 984 levels: [ 985 { table: 'foursquare_mercator', sample: 1, priority: 1 }, 986 ], 987 time: { column: 'date_created', exclude: 'date_created IS NOT NULL' }, 988 report_total: { 989 query: (condition => ` 990 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 991 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 992 SELECT 993 count() AS places 994 FROM {table:Identifier} 995 WHERE ${condition}`), 996 content: (json => { 997 let row = json.data[0]; 998 let text = `Total ${Number(row.places).toLocaleStr
998ing()} places.`; 999 1000 if (json.statistics.rows_read > 1) { 1001 text += ` Processed ${Number(json.statistics.rows_read).toLocaleString()} rows.`; 1002 } 1003 1004 return text; 1005 }), 1006 }, 1007 reports: [ 1008 { 1009 query: (condition => ` 1010 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 1011 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 1012 SELECT name, count() AS c 1013 FROM {table:Identifier} 1014 WHERE name != '' AND ${condition} 1015 GROUP BY name 1016 ORDER BY c DESC 1017 LIMIT 100`), 1018 field: 'name', 1019 id: 'report_names', 1020 title: 'Places: ', 1021 separator: ', ', 1022 content: (row => `${row.name}${row.c > 1 ? ` (${row.c})` : ''}`) 1023 }, 1024 { 1025 query: (condition => ` 1026 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 1027 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 1028 SELECT category, count() AS c 1029 FROM {table:Identifier} 1030 WHERE category != '' AND ${condition} 1031 GROUP BY category 1032 ORDER BY c DESC 1033 LIMIT 25`), 1034 field: 'category', 1035 id: 'report_categories', 1036 title: 'Categories: ', 1037 separator: '\n', 1038 content: (row => `${row.category} (${row.c})`) 1039 }, 1040 ], 1041 queries: { 1042 "Density": `WITH 1043 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1044 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1045 1046 tile_size * {x:UInt32} AS tile_x_begin, 1047 tile_size * ({x:UInt32} + 1) AS tile_x_end, 1048 1049 tile_size * {y:UInt32} AS tile_y_begin, 1050 tile_size * ({y:UInt32} + 1) AS tile_y_end, 1051 1052 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1053 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1054 1055 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1056 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1057 1058 y * 1024 + x AS pos, 1059 1060 count() AS total, 1061 1062 pow(least(1, total / 100 * zoom_factor), 1/5) AS color1, 1063 pow(least(1, total / 10000 * zoom_factor), 1/5) AS color2, 1064 pow(least(1, total / 1000000 * zoom_factor), 1/5) AS color3, 1065 1066 255 AS alpha, 1067 color1 * 255 AS red, 1068 color2 * 255 AS green, 1069 color3 * 255 AS blue 1070 1071 SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1072 FROM {table:Identifier} 1073 WHERE in_tile 1074 GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1075 1076 "Old vs New": `WITH 1077 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1078 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1079 1080 tile_size * {x:UInt32} AS tile_x_begin, 1081 tile_size * ({x:UInt32} + 1) AS tile_x_end, 1082 1083 tile_size * {y:UInt32} AS tile_y_begin, 1084 tile_size * ({y:UInt32} + 1) AS tile_y_end, 1085 1086 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1087 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1088 1089 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1090 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1091 1092 y * 1024 + x AS pos, 1093 1094 count() AS total, 1095 1096 greatest(0, avg(date_created::Int32 - '2009-01-01'::Date::Int32) / (today()::Int32 - '2009-01-01'::Date::Int32)) AS color1, 1097 pow(least(1, total / 10000 * zoom_factor), 1/5) AS color2, 1098 1099 255 AS alpha, 1100 color1 * 255 AS red, 1101 color2 * 255 AS green, 1102 color2 * 255 AS blue 1103 1104 SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1105 FROM {table:Identifier} 1106 WHERE in_tile AND date_created IS NOT NULL 1107 GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1108 1109 "Countries": `WITH 1110 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1111 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1112 1113 tile_size * {x:UInt32} AS tile_x_begin, 1114 tile_size * ({x:UInt32} + 1) AS tile_x_end, 1115 1116 tile_size * {y:UInt32} AS tile_y_begin, 1117 tile_size * ({y:UInt32} + 1) AS tile_y_end, 1118 1119 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1120 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1121 1122 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1123 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1124 1125 y * 1024 + x AS pos, 1126 1127 count() AS total, 1128 1129 cityHash64(country) MOD 256 AS color1, 1130 cityHash64(country) DIV 256 MOD 256 AS color2, 1131 cityHash64(country) DIV 65536 MOD 256 AS color3, 1132 1133 pow(least(1, total / 1000 * zoom_factor), 1/5) AS transparency, 1134
1135 transparency * 255 AS alpha, 1136 avg(color1) AS red, 1137 avg(color2) AS green, 1138 avg(color3) AS blue 1139 1140 SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1141 FROM {table:Identifier} 1142 WHERE in_tile 1143 GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1144 1145 "Coffeeshops": `WITH 1146 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1147 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1148 1149 tile_size * {x:UInt32} AS tile_x_begin, 1150 tile_size * ({x:UInt32} + 1) AS tile_x_end, 1151 1152 tile_size * {y:UInt32} AS tile_y_begin, 1153 tile_size * ({y:UInt32} + 1) AS tile_y_end, 1154 1155 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1156 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1157 1158 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1159 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1160 1161 y * 1024 + x AS pos, 1162 1163 count() AS total, 1164 1165 pow(least(1, total / 100 * zoom_factor), 1/5) AS color1, 1166 pow(least(1, total / 10000 * zoom_factor), 1/5) AS color2, 1167 pow(least(1, total / 1000000 * zoom_factor), 1/5) AS color3, 1168 1169 255 AS alpha, 1170 color1 * 255 AS red, 1171 color2 * 255 AS green, 1172 color3 * 255 AS blue 1173 1174 SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1175 FROM {table:Identifier} 1176 WHERE in_tile AND category = 'Retail > Marijuana Dispensary' 1177 GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1178 1179 "Casinos": `WITH 1180 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1181 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1182 1183 tile_size * {x:UInt32} AS tile_x_begin, 1184 tile_size * ({x:UInt32} + 1) AS tile_x_end, 1185 1186 tile_size * {y:UInt32} AS tile_y_begin, 1187 tile_size * ({y:UInt32} + 1) AS tile_y_end, 1188 1189 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1190 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1191 1192 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1193 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1194 1195 y * 1024 + x AS pos, 1196 1197 count() AS total, 1198 1199 greatest(0, avg(date_created::Int32 - '2009-01-01'::Date::Int32) / (today()::Int32 - '2009-01-01'::Date::Int32)) AS color1, 1200 pow(least(1, total / 10000 * zoom_factor), 1/5) AS color2, 1201 1202 255 AS alpha, 1203 color1 * 255 AS red, 1204 color2 * 255 AS green, 1205 color2 * 255 AS blue 1206 1207 SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1208 FROM {table:Identifier} 1209 WHERE in_tile AND date_created IS NOT NULL AND category LIKE '%Casino%' 1210 GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1211 1212 "Boats": `WITH 1213 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1214 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1215 1216 tile_size * {x:UInt32} AS tile_x_begin, 1217 tile_size * ({x:UInt32} + 1) AS tile_x_end, 1218 1219 tile_size * {y:UInt32} AS tile_y_begin, 1220 tile_size * ({y:UInt32} + 1) AS tile_y_end, 1221 1222 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1223 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1224 1225 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1226 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1227 1228 y * 1024 + x AS pos, 1229 1230 count() AS total, 1231 1232 pow(least(1, total / 100 * zoom_factor), 1/5) AS color1, 1233 pow(least(1, total / 10000 * zoom_factor), 1/5) AS color2, 1234 pow(least(1, total / 1000000 * zoom_factor), 1/5) AS color3, 1235 1236 255 AS alpha, 1237 color1 * 255 AS red, 1238 color2 * 255 AS green, 1239 color3 * 255 AS blue 1240 1241 SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1242 FROM {table:Identifier} 1243 WHERE in_tile AND date_created IS NOT NULL AND category = 'Travel and Transportation > Boat or Ferry' 1244 GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024` 1245 }, 1246 }, 1247 1248 "Birds": { 1249 notice: "© Cornell Lab of Ornithology. eBird Observation Dataset. CC BY 4.0", 1250 endpoints: [ 1251 { 1252 name: "Cloud (Real-Time)", 1253 urls: [ 1254 { 1255 url: "https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 1256 sticky: "https://{hash}.sticky.kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 1257 } 1258 ] 1259 }, 1260 ], 1261 levels: [ 1262 { table: 'birds_mercator', sample: 1, priority: 1 }, 1263 ], 1264 time: { column: 'date', exclude: "date != '1970-01-01'" }, 1265 report_total: { 1266 query: (condition => ` 1267 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 1268 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 1269 SELECT 1270 count() AS traces, minIf(date, date != '1970-01-01') AS first, maxIf(date, date != '1970-01-01') AS last 1271 FROM {table:Identifier} 1272 WHERE ${condition}`), 1273 content: (json => { 1274 let row = json.data[0]; 1275 let text = `Total ${Number(row.traces).toLocaleStr
1275ing()} traces.`; 1276 1277 if (json.statistics.rows_read > 1) { 1278 text += ` Processed ${Number(json.statistics.rows_read).toLocaleString()} rows.`; 1279 } 1280 1281 if (row.traces > 0) { 1282 text += ` Time: ${row.first} â ${row.last}.`; 1283 } 1284 1285 return text; 1286 }), 1287 }, 1288 reports: [ 1289 { 1290 query: (condition => ` 1291 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 1292 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 1293 SELECT vernacularname, count() AS c 1294 FROM {table:Identifier} 1295 WHERE vernacularname != '' AND ${condition} 1296 GROUP BY vernacularname 1297 ORDER BY c DESC 1298 LIMIT 100`), 1299 field: 'vernacularname', 1300 wiki_field: 'vernacularname', 1301 id: 'report_names', 1302 title: 'Name: ', 1303 separator: ', ', 1304 content: (row => `${row.vernacularname}${row.c > 1 ? `\u00a0(${row.c})` : ''}`) 1305 }, 1306 { 1307 query: (condition => ` 1308 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 1309 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 1310 SELECT order, count() AS c 1311 FROM {table:Identifier} 1312 WHERE order != '' AND ${condition} 1313 GROUP BY order 1314 ORDER BY c DESC 1315 LIMIT 100`), 1316 field: 'order', 1317 wiki_field: 'order', 1318 id: 'report_orders', 1319 title: 'Order: ', 1320 separator: ', ', 1321 content: (row => `${row.order}${row.c > 1 ? `\u00a0(${row.c})` : ''}`) 1322 }, 1323 { 1324 query: (condition => ` 1325 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 1326 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 1327 SELECT family, count() AS c 1328 FROM {table:Identifier} 1329 WHERE family != '' AND ${condition} 1330 GROUP BY family 1331 ORDER BY c DESC 1332 LIMIT 100`), 1333 field: 'family', 1334 wiki_field: 'family', 1335 id: 'report_families', 1336 title: 'Family: ', 1337 separator: ', ', 1338 content: (row => `${row.family}${row.c > 1 ? `\u00a0(${row.c})` : ''}`) 1339 }, 1340 ], 1341 queries: { 1342 "Density": `WITH 1343bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1344bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1345 1346tile_size * {x:UInt32} AS tile_x_begin, 1347tile_size * ({x:UInt32} + 1) AS tile_x_end, 1348 1349tile_size * {y:UInt32} AS tile_y_begin, 1350tile_size * ({y:UInt32} + 1) AS tile_y_end, 1351 1352mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1353AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1354 1355bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1356bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1357 1358y * 1024 + x AS pos, 1359 1360count() AS total, 1361 1362pow(least(1, total / 100 * zoom_factor), 1/5) AS color1, 1363pow(least(1, total / 10000 * zoom_factor), 1/5) AS color2, 1364pow(least(1, total / 1000000 * zoom_factor), 1/5) AS color3, 1365 1366255 AS alpha, 1367color3 * 255 AS red, 1368color2 * 255 AS green, 1369color1 * 255 AS blue 1370 1371SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1372FROM {table:Identifier} 1373WHERE in_tile 1374GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1375 1376 "Order": `WITH 1377bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1378bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1379 1380tile_size * {x:UInt32} AS tile_x_begin, 1381tile_size * ({x:UInt32} + 1) AS tile_x_end, 1382 1383tile_size * {y:UInt32} AS tile_y_begin, 1384tile_size * ({y:UInt32} + 1) AS tile_y_end, 1385 1386mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1387AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1388 1389bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1390bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1391 1392y * 1024 + x AS pos, 1393 1394count() AS total, 1395 1396cityHash64(order) AS hash, 1397hash MOD 256 AS h1, 1398hash DIV 256 MOD 256 AS h2, 1399 1400pow(least(1, total / 100 * zoom_factor), 1/5) AS color1, 1401pow(least(1, total / 10000 * zoom_factor), 1/5) AS color2, 1402pow(least(1, total / 1000000 * zoom_factor), 1/5) AS color3, 1403
1404(0.5 + 0.5 * color2) * 255 AS alpha, 1405avg(h1) AS red, 1406avg(h2) AS green, 1407avg(least(255, greatest(0, 255 - (h1 + h2) / 2))) AS blue 1408 1409SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1410FROM {table:Identifier} 1411WHERE in_tile 1412GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1413 1414 "Family": `WITH 1415bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1416bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1417 1418tile_size * {x:UInt32} AS tile_x_begin, 1419tile_size * ({x:UInt32} + 1) AS tile_x_end, 1420 1421tile_size * {y:UInt32} AS tile_y_begin, 1422tile_size * ({y:UInt32} + 1) AS tile_y_end, 1423 1424mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1425AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1426 1427bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1428bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1429 1430y * 1024 + x AS pos, 1431 1432count() AS total, 1433 1434cityHash64(family) AS hash, 1435hash MOD 256 AS h1, 1436hash DIV 256 MOD 256 AS h2, 1437 1438pow(least(1, total / 100 * zoom_factor), 1/5) AS color1, 1439pow(least(1, total / 10000 * zoom_factor), 1/5) AS color2, 1440pow(least(1, total / 1000000 * zoom_factor), 1/5) AS color3, 1441 1442(0.5 + 0.5 * color2) * 255 AS alpha, 1443avg(h1) AS red, 1444avg(h2) AS green, 1445avg(least(255, greatest(0, 255 - (h1 + h2) / 2))) AS blue 1446 1447SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1448FROM {table:Identifier} 1449WHERE in_tile 1450GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1451 1452 "Genus": `WITH 1453bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1454bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1455 1456tile_size * {x:UInt32} AS tile_x_begin, 1457tile_size * ({x:UInt32} + 1) AS tile_x_end, 1458 1459tile_size * {y:UInt32} AS tile_y_begin, 1460tile_size * ({y:UInt32} + 1) AS tile_y_end, 1461 1462mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1463AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1464 1465bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1466bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1467 1468y * 1024 + x AS pos, 1469 1470count() AS total, 1471 1472cityHash64(genus) AS hash, 1473hash MOD 256 AS h1, 1474hash DIV 256 MOD 256 AS h2, 1475 1476pow(least(1, total / 100 * zoom_factor), 1/5) AS color1, 1477pow(least(1, total / 10000 * zoom_factor), 1/5) AS color2, 1478pow(least(1, total / 1000000 * zoom_factor), 1/5) AS color3, 1479 1480(0.5 + 0.5 * color2) * 255 AS alpha, 1481avg(h1) AS red, 1482avg(h2) AS green, 1483avg(least(255, greatest(0, 255 - (h1 + h2) / 2))) AS blue 1484 1485SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1486FROM {table:Identifier} 1487WHERE in_tile 1488GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1489 1490 "Epithet": `WITH 1491bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1492bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1493 1494tile_size * {x:UInt32} AS tile_x_begin, 1495tile_size * ({x:UInt32} + 1) AS tile_x_end, 1496 1497tile_size * {y:UInt32} AS tile_y_begin, 1498tile_size * ({y:UInt32} + 1) AS tile_y_end, 1499 1500mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1501AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1502 1503bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1504bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1505 1506y * 1024 + x AS pos, 1507 1508count() AS total, 1509 1510cityHash64(specificepithet) AS hash, 1511hash MOD 256 AS h1, 1512hash DIV 256 MOD 256 AS h2, 1513 1514pow(least(1, total / 100 * zoom_factor), 1/5) AS color1, 1515pow(least(1, total / 10000 * zoom_factor), 1/5) AS color2, 1516pow(least(1, total / 1000000 * zoom_factor), 1/5) AS color3, 1517 1518(0.5 + 0.5 * color2) * 255 AS alpha, 1519avg(h1) AS red, 1520avg(h2) AS green, 1521avg(least(255, greatest(0, 255 - (h1 + h2) / 2))) AS blue 1522 1523SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1524FROM {table:Identifier} 1525WHERE in_tile 1526GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1527 1528 "Name": `WITH 1529bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1530bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1531 1532tile_size * {x:UInt32} AS tile_x_begin, 1533tile_size * ({x:UInt32} + 1) AS tile_x_end, 1534 1535tile_size * {y:UInt32} AS tile_y_begin, 1536tile_size * ({y:UInt32} + 1) AS tile_y_end, 1537 1538mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1539AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1540 1541bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1542bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1543 1544y * 1024 + x AS pos, 1545 1546count() AS total, 1547 1548cityHash64(vernacularname) AS hash, 1549hash MOD 256 AS h1, 1550hash DIV 256 MOD 256 AS h2, 1551 1552pow(least(1, total / 100 * zoom_factor), 1/5) AS color1, 1553pow(least(1, total / 10000 * zoom_factor), 1/5) AS color2, 1554pow(least(1, total / 1000000 * zoom_factor), 1/5) AS color3, 1555
1556(0.5 + 0.5 * color2) * 255 AS alpha, 1557avg(h1) AS red, 1558avg(h2) AS green, 1559avg(least(255, greatest(0, 255 - (h1 + h2) / 2))) AS blue 1560 1561SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1562FROM {table:Identifier} 1563WHERE in_tile 1564GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1565 1566 "Flocks": `WITH 1567bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1568bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1569 1570tile_size * {x:UInt32} AS tile_x_begin, 1571tile_size * ({x:UInt32} + 1) AS tile_x_end, 1572 1573tile_size * {y:UInt32} AS tile_y_begin, 1574tile_size * ({y:UInt32} + 1) AS tile_y_end, 1575 1576mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1577AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1578 1579bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1580bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1581 1582y * 1024 + x AS pos, 1583 1584255 AS alpha, 1585max(least(255, individualcount)) AS blue, 1586max(least(255, individualcount / 256)) AS green, 1587max(least(255, individualcount / 65536)) AS red 1588 1589SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1590FROM {table:Identifier} 1591WHERE in_tile 1592GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1593 1594 "Time": `WITH 1595bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1596bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1597 1598tile_size * {x:UInt32} AS tile_x_begin, 1599tile_size * ({x:UInt32} + 1) AS tile_x_end, 1600 1601tile_size * {y:UInt32} AS tile_y_begin, 1602tile_size * ({y:UInt32} + 1) AS tile_y_end, 1603 1604mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1605AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1606 1607bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1608bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1609 1610y * 1024 + x AS pos, 1611 1612count() AS total, 1613 1614pow(greatest(0, avg(dateDiff('day', '1970-01-01'::Date, date) / dateDiff('day', '1970-01-01'::Date, '2024-01-01'::Date))), 3) AS days1, 1615pow(greatest(0, avg(dateDiff('day', '2000-01-01'::Date, date) / dateDiff('day', '2000-01-01'::Date, '2024-01-01'::Date))), 3) AS days2, 1616 1617pow(least(1, total / 100 * zoom_factor), 1/5) AS color1, 1618pow(least(1, total / 10000 * zoom_factor), 1/5) AS color2, 1619pow(least(1, total / 1000000 * zoom_factor), 1/5) AS color3, 1620 1621color2 * 255 AS alpha, 1622color1 * 255 AS red, 1623days1 * 255 AS green, 1624days2 * 255 AS blue 1625 1626SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1627FROM {table:Identifier} 1628WHERE in_tile AND date != '1970-01-01' 1629GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1630 }, 1631 }, 1632 1633 "Photos": { 1634 notice: "ODC-By v1.0, https://huggingface.co/datasets/bigdata-pw/Flickr", 1635 endpoints: [ 1636 { 1637 name: "Cloud (Real-Time)", 1638 urls: [ 1639 { 1640 url: "https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 1641 sticky: "https://{hash}.sticky.kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 1642 } 1643 ] 1644 }, 1645 ], 1646 levels: [ 1647 { table: 'flickr_mercator', sample: 1, priority: 1 }, 1648 ], 1649 time: { column: 'datetaken', exclude: "datetaken >= '2000-01-01' AND datetaken < '2026-01-01'" }, 1650 report_total: { 1651 query: (condition => ` 1652 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 1653 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 1654 SELECT
1655 count() AS photos 1656 FROM {table:Identifier} 1657 WHERE ${condition}`), 1658 content: (json => { 1659 let row = json.data[0]; 1660 let text = `Total ${Number(row.photos).toLocaleString()} photos.`; 1661 1662 if (json.statistics.rows_read > 1) { 1663 text += ` Processed ${Number(json.statistics.rows_read).toLocaleString()} rows.`; 1664 } 1665 1666 return text; 1667 }), 1668 }, 1669 reports: [ 1670 { 1671 query: (condition => ` 1672 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 1673 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 1674 SELECT url_sq, url_k AS url 1675 FROM {table:Identifier} 1676 WHERE ${condition} AND has(sizes, 'k') 1677 ORDER BY count_faves DESC, count_views DESC 1678 LIMIT 15`), 1679 id: 'report_thumbs', 1680 title: '', 1681 separator: '', 1682 html: (row => `<a target="_blank" href="${row.url}"><img src="${row.url_sq}"></img></a>`) 1683 }, 1684 { 1685 query: (condition => ` 1686 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 1687 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 1688 SELECT arrayJoin(tags) AS tag, count() AS c 1689 FROM {table:Identifier} 1690 WHERE ${condition} 1691 GROUP BY tag 1692 ORDER BY c DESC 1693 LIMIT 25`), 1694 field: 'tag', 1695 filter_expr: (value => `has(tags, ${value})`), 1696 id: 'report_tags', 1697 title: 'Tags: ', 1698 separator: ', ', 1699 content: (row => `${row.tag}${row.c > 1 ? `\u00a0(${row.c})` : ''}`) 1700 }, 1701 ], 1702 queries: { 1703 "Density": `WITH 1704bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1705bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1706 1707tile_size * {x:UInt32} AS tile_x_begin, 1708tile_size * ({x:UInt32} + 1) AS tile_x_end, 1709 1710tile_size * {y:UInt32} AS tile_y_begin, 1711tile_size * ({y:UInt32} + 1) AS tile_y_end, 1712 1713mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1714AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1715 1716bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1717bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1718 1719y * 1024 + x AS pos, 1720 1721count() AS total, 1722 1723pow(least(1, total / 100 * zoom_factor), 1/5) AS color1, 1724least(max(count_views), 1000) / 1000 AS color2, 1725least(max(count_faves), 100) / 100 AS color3, 1726 1727255 AS alpha, 1728color1 * 255 AS red, 1729color2 * 255 AS green, 1730color3 * 255 AS blue 1731 1732SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1733FROM {table:Identifier} 1734WHERE in_tile 1735GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1736 1737 "Tags": `WITH 1738bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1739bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1740 1741tile_size * {x:UInt32} AS tile_x_begin, 1742tile_size * ({x:UInt32} + 1) AS tile_x_end, 1743 1744tile_size * {y:UInt32} AS tile_y_begin, 1745tile_size * ({y:UInt32} + 1) AS tile_y_end, 1746 1747mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1748AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1749 1750bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1751bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1752 1753y * 1024 + x AS pos, 1754 1755count() AS total, 1756cityHash64(tags[1]) AS hash, 1757 1758avg(hash MOD 256) AS color1, 1759avg(hash DIV 256 MOD 256) AS color2, 1760avg(hash DIV 65536 MOD 256) AS color3, 1761 1762255 AS alpha, 1763color1 AS red, 1764color2 AS green, 1765color3 AS blue 1766 1767SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1768FROM {table:Identifier} 1769WHERE in_tile 1770GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1771 }, 1772 }, 1773
1774 "Ships": { 1775 notice: "© Konstantin Bogdanov, ClickHouse, Inc., data: © aishub.net", 1776 endpoints: [ 1777 { 1778 name: "Cloud (Real-Time)", 1779 urls: [ 1780 { 1781 url: "https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 1782 sticky: "https://{hash}.sticky.kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 1783 } 1784 ] 1785 }, 1786 ], 1787 levels: [ 1788 { table: 'ais_mercator_sample100', sample: 100, priority: 1 }, 1789 { table: 'ais_mercator_sample10', sample: 10, priority: 2 }, 1790 { table: 'ais_mercator', sample: 1, priority: 3 }, 1791 ], 1792 time: { column: 'timestamp' }, 1793 report_total: { 1794 query: (condition => ` 1795 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 1796 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 1797 SELECT 1798 count() AS traces, 1799 uniq(mmsi) AS vessels, 1800 round(avgIf(sog, sog < 60), 1) AS avg_sog, 1801 min(timestamp) AS first, max(timestamp) AS last 1802 FROM {table:Identifier} 1803 WHERE ${condition}`), 1804 content: (json => { 1805 let row = json.data[0]; 1806 let text = `Total ${Number(row.traces).toLocaleString()} traces from ${Number(row.vessels).toLocaleString()} vessels.`; 1807 1808 if (row.traces > 0) { 1809 text += ` Avg speed: ${row.avg_sog} kn. Time: ${row.first} â ${row.last}.`; 1810 } 1811 1812 if (json.statistics.rows_read > 1) { 1813 text += ` Processed ${Number(json.statistics.rows_read).toLocaleString()} rows.`; 1814 } 1815 1816 return text; 1817 }), 1818 }, 1819 reports: [ 1820 { 1821 query: (condition => ` 1822 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 1823 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 1824 SELECT toString(mmsi) AS mmsi_str, count() AS c, round(avgIf(sog, sog < 60), 1) AS avg_sog 1825 FROM {table:Identifier} 1826 WHERE ${condition} 1827 GROUP BY mmsi 1828 ORDER BY c DESC 1829 LIMIT 100`), 1830 field: 'mmsi_str', 1831 id: 'report_mmsi', 1832 title: 'Vessels:\n', 1833 separator: ',\n', 1834 filter_expr: (value => `mmsi = ${value.replaceAll("'", "")}`), 1835 content: (row => `${row.mmsi_str} (${Number(row.c).toLocaleString()} pts, ${row.avg_sog} kn)`), 1836 enrich: { 1837 query: (keys => { 1838 const mmsi = keys.map(key => Number(key)).filter(value => 1839 Number.isInteger(value) && value >= 0 && value <= 0xFFFFFFFF); 1840 return ` 1841 SELECT toString(mmsi) AS key, argMax(name, timestamp) AS value 1842 FROM ais_vessel_names 1843 WHERE mmsi IN (${mmsi.join(', ') || 'NULL'}) 1844 GROUP BY mmsi`; 1845 }), 1846 content: ((row, name) => 1847 `${name} â ${row.mmsi_str} (${Number(row.c).toLocaleString()} pts, ${row.avg_sog} kn)`), 1848 }, 1849 }, 1850 { 1851 query: (condition => ` 1852 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 1853 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 1854 SELECT toString(nav_status) AS nav_status_str, count() AS c 1855 FROM {table:Identifier} 1856 WHERE ${condition} 1857 GROUP BY nav_status 1858 ORDER BY c DESC 1859 LIMIT 100`), 1860 field: 'nav_status_str', 1861 id: 'report_navstatus', 1862 title: 'Nav status:\n', 1863 separator: ',\n', 1864 filter_expr: (value => `nav_status = ${value.replaceAll("'", "")}`), 1865 content: (row => `${SHIPS_NAV_STATUS[row.nav_status_str] || `code ${row.nav_status_str}`}
1865 (${Number(row.c).toLocaleString()})`) 1866 }, 1867 { 1868 query: (condition => ` 1869 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 1870 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 1871 SELECT data_source, count() AS c 1872 FROM {table:Identifier} 1873 WHERE ${condition} 1874 GROUP BY data_source 1875 ORDER BY c DESC 1876 LIMIT 100`), 1877 field: 'data_source', 1878 id: 'report_datasource', 1879 title: 'Source: ', 1880 separator: ', ', 1881 content: (row => `${row.data_source} (${Number(row.c).toLocaleString()})`) 1882 }, 1883 ], 1884 queries: { 1885"Speed": `WITH 1886 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1887 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1888 1889 tile_size * {x:UInt32} AS tile_x_begin, 1890 tile_size * ({x:UInt32} + 1) AS tile_x_end, 1891 1892 tile_size * {y:UInt32} AS tile_y_begin, 1893 tile_size * ({y:UInt32} + 1) AS tile_y_end, 1894 1895 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1896 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1897 1898 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1899 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1900 1901 y * 1024 + x AS pos, 1902 1903 count() AS total, 1904 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 1905 1906 pow(total / max_total, 1/5) AS transparency, 1907 greatest(0, least(avg(sog), 105)) / 105 AS color2, 1908 1909 255 AS alpha, 1910 (1 + transparency) / 2 * 0.9 * 255 AS red, 1911 transparency * 255 AS green, 1912 color2 * 255 AS blue 1913 1914SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1915FROM {table:Identifier} 1916WHERE in_tile 1917 AND intDiv(mmsi, 1000000) NOT IN (111, 970, 972, 974, 979) 1918 AND mmsi >= 100000000 1919 AND sog < 60 1920GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1921 1922"Fishing": `WITH 1923 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1924 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1925 1926 tile_size * {x:UInt32} AS tile_x_begin, 1927 tile_size * ({x:UInt32} + 1) AS tile_x_end, 1928 1929 tile_size * {y:UInt32} AS tile_y_begin, 1930 tile_size * ({y:UInt32} + 1) AS tile_y_end, 1931 1932 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1933 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1934 1935 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1936 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1937 1938 y * 1024 + x AS pos, 1939 1940 count() AS total, 1941 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 1942 1943 pow(total / max_total, 1/5) AS transparency, 1944 greatest(0, least(avg(sog), 15)) / 15 AS color2, 1945 1946 255 AS alpha, 1947 (1 + transparency) / 2 * 0.9 * 255 AS red, 1948 transparency * 255 AS green, 1949 color2 * 255 AS blue 1950 1951SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1952FROM {table:Identifier} 1953WHERE in_tile 1954 AND intDiv(mmsi, 1000000) NOT IN (111, 970, 972, 974, 979) 1955 AND mmsi >= 100000000 1956 AND sog < 60 1957 AND nav_status = 7 1958GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1959 1960"Anchored": `WITH 1961 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 1962 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 1963 1964 tile_size * {x:UInt32} AS tile_x_begin, 1965 tile_size * ({x:UInt32} + 1) AS tile_x_end, 1966 1967 tile_size * {y:UInt32} AS tile_y_begin, 1968 tile_size * ({y:UInt32} + 1) AS tile_y_end, 1969 1970 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 1971 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 1972 1973 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 1974 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 1975 1976 y * 1024 + x AS pos, 1977 1978 count() AS total, 1979 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 1980 1981 pow(total / max_total, 1/5) AS transparency, 1982 greatest(0, least(avg(sog), 5)) / 5 AS color2, 1983 1984 255 AS alpha, 1985 (1 + transparency) / 2 * 0.9 * 255 AS red,
1986 transparency * 255 AS green, 1987 color2 * 255 AS blue 1988 1989SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 1990FROM {table:Identifier} 1991WHERE in_tile 1992 AND intDiv(mmsi, 1000000) NOT IN (111, 970, 972, 974, 979) 1993 AND mmsi >= 100000000 1994 AND nav_status IN (1, 5) 1995 AND sog < 1 1996GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 1997 1998"Stopped": `WITH 1999 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2000 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2001 2002 tile_size * {x:UInt32} AS tile_x_begin, 2003 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2004 2005 tile_size * {y:UInt32} AS tile_y_begin, 2006 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2007 2008 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2009 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2010 2011 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2012 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2013 2014 y * 1024 + x AS pos, 2015 2016 count() AS total, 2017 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 2018 2019 pow(total / max_total, 1/5) AS transparency, 2020 greatest(0, least(avg(sog), 1)) AS color2, 2021 2022 255 AS alpha, 2023 (1 + transparency) / 2 * 0.9 * 255 AS red, 2024 transparency * 255 AS green, 2025 color2 * 255 AS blue 2026 2027SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2028FROM {table:Identifier} 2029WHERE in_tile 2030 AND intDiv(mmsi, 1000000) NOT IN (111, 970, 972, 974, 979) 2031 AND mmsi >= 100000000 2032 AND sog < 0.5 2033GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2034 2035"High-speed": `WITH 2036 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2037 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2038 2039 tile_size * {x:UInt32} AS tile_x_begin, 2040 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2041 2042 tile_size * {y:UInt32} AS tile_y_begin, 2043 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2044 2045 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2046 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2047 2048 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2049 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2050 2051 y * 1024 + x AS pos, 2052 2053 count() AS total, 2054 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 2055 2056 pow(total / max_total, 1/5) AS transparency, 2057 greatest(0, least(avg(sog) - 25, 25)) / 25 AS color2, 2058 2059 255 AS alpha, 2060 (1 + transparency) / 2 * 0.9 * 255 AS red, 2061 transparency * 255 AS green, 2062 color2 * 255 AS blue 2063 2064SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2065FROM {table:Identifier} 2066WHERE in_tile 2067 AND intDiv(mmsi, 1000000) NOT IN (111, 970, 972, 974, 979) 2068 AND mmsi >= 100000000 2069 AND sog > 25 AND sog < 60 2070GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2071 2072"Emergency": `WITH 2073 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2074 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2075 2076 tile_size * {x:UInt32} AS tile_x_begin, 2077 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2078 2079 tile_size * {y:UInt32} AS tile_y_begin, 2080 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2081 2082 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2083 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2084 2085 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2086 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2087 2088 y * 1024 + x AS pos, 2089 2090 count() AS total, 2091 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 2092 2093 pow(total / max_total, 1/5) AS transparency, 2094 greatest(0, least(avg(sog), 200)) / 200 AS color2, 2095 2096 255 AS alpha, 2097 (1 + transparency) / 2 * 0.9 * 255 AS red, 2098 transparency * 255 AS green, 2099 color2 * 255 AS blue 2100 2101SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2102FROM {table:Identifier} 2103WHERE in_tile 2104 AND (nav_status IN (2, 6, 14) 2105 OR intDiv(mmsi, 1000000) IN (111, 970, 972, 974, 979)) 2106GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2107 2108"Aircraft": `WITH 2109 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2110 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2111 2112 tile_size * {x:UInt32} AS tile_x_begin, 2113 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2114 2115 tile_size * {y:UInt32} AS tile_y_begin, 2116 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2117 2118 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2119 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2120 2121 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2122 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2123 2124 y * 1024 + x AS pos, 2125 2126 count() AS total, 2127 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 2128
2129 pow(total / max_total, 1/5) AS transparency, 2130 greatest(0, least(avg(sog), 500)) / 500 AS color2, 2131 2132 255 AS alpha, 2133 (1 + transparency) / 2 * 0.9 * 255 AS red, 2134 transparency * 255 AS green, 2135 color2 * 255 AS blue 2136 2137SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2138FROM {table:Identifier} 2139WHERE in_tile 2140 AND intDiv(mmsi, 1000000) = 111 2141GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2142 2143"MarineCadastre": `WITH 2144 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2145 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2146 2147 tile_size * {x:UInt32} AS tile_x_begin, 2148 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2149 2150 tile_size * {y:UInt32} AS tile_y_begin, 2151 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2152 2153 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2154 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2155 2156 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2157 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2158 2159 y * 1024 + x AS pos, 2160 2161 count() AS total, 2162 greatest(1000000 DIV {sampling:UInt32} DIV zoom_factor, count()) AS max_total, 2163 2164 pow(total / max_total, 1/5) AS transparency, 2165 greatest(0, least(avg(sog), 30)) / 30 AS color2, 2166 2167 255 AS alpha, 2168 (1 + transparency) / 2 * 0.9 * 255 AS red, 2169 transparency * 255 AS green, 2170 color2 * 255 AS blue 2171 2172SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2173FROM {table:Identifier} 2174WHERE in_tile AND data_source = 'marinecadastre' 2175GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024` 2176 } 2177 }, 2178 2179 "OSM": { 2180 notice: "© OpenStreetMap contributors, ODbL v1.0", 2181 endpoints: [ 2182 { 2183 name: "Cloud (Real-Time)", 2184 urls: [ 2185 { 2186 url: "https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 2187 sticky: "https://{hash}.sticky.kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 2188 } 2189 ] 2190 }, 2191 ], 2192 levels: [ 2193 { table: 'osm_mercator_sample100', sample: 100, priority: 1 }, 2194 { table: 'osm_mercator_sample10', sample: 10, priority: 2 }, 2195 { table: 'osm_mercator', sample: 1, priority: 3 }, 2196 ], 2197 time: { column: 'timestamp' }, 2198 report_total: { 2199 query: (condition => ` 2200 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 2201 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 2202 SELECT 2203 count() AS nodes, 2204 uniq(uid) AS mappers, 2205 min(timestamp) AS first, max(timestamp) AS last 2206 FROM {table:Identifier} 2207 WHERE ${condition}`), 2208 content: (json => { 2209 let row = json.data[0]; 2210 let text = `Total ${Number(row.nodes).toLocaleString()} nodes by ${Number(row.mappers).toLocaleString()} mappers.`; 2211 2212 if (row.nodes > 0) { 2213 text += ` Edited: ${row.first} â ${row.last}.`; 2214 } 2215 2216 if (json.statistics.rows_read > 1) { 2217 text += ` Processed ${Number(json.statistics.rows_read).toLocaleString()} rows.`; 2218 } 2219 2220 return text; 2221 }), 2222 }, 2223 reports: [ 2224 { 2225 query: (condition => ` 2226 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 2227 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 2228 SELECT name, count() AS c 2229 FROM {table:Identifier} 2230 WHERE name != '' AND ${condition} 2231 GROUP BY name 2232 ORDER BY c DESC 2233 LIMIT 100`), 2234 field: 'name', 2235 id: 'report_names', 2236 title: 'Names: ', 2237 separator: ', ', 2238 content: (row => `${row.name}${row.c > 1 ? ` (${row.c})` : ''}`) 2239 }, 2240 { 2241 query: (condition => ` 2242 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 2243 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 2244 SELECT amenity, count() AS c 2245 FROM {table:Identifier} 2246 WHERE amenity != '' AND ${condition} 2247 GROUP BY amenity 2248 ORDER BY c DESC 2249 LIMIT 50`), 2250 field: 'amenity', 2251 id: 'report_amenities', 2252 title: 'Amenities: ', 2253 separator: ', ', 2254 content: (row => `${row.amenity}
2254 (${Number(row.c).toLocaleString()})`) 2255 }, 2256 { 2257 query: (condition => ` 2258 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 2259 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 2260 SELECT arrayJoin(mapKeys(tags)) AS tag_key, count() AS c 2261 FROM {table:Identifier} 2262 WHERE ${condition} 2263 GROUP BY tag_key 2264 ORDER BY c DESC 2265 LIMIT 50`), 2266 field: 'tag_key', 2267 filter_expr: (value => `mapContains(tags, ${value})`), 2268 id: 'report_tagkeys', 2269 title: 'Tags: ', 2270 separator: ', ', 2271 content: (row => `${row.tag_key} (${Number(row.c).toLocaleString()})`) 2272 }, 2273 { 2274 query: (condition => ` 2275 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 2276 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 2277 SELECT user, count() AS c 2278 FROM {table:Identifier} 2279 WHERE user != '' AND ${condition} 2280 GROUP BY user 2281 ORDER BY c DESC 2282 LIMIT 100`), 2283 field: 'user', 2284 id: 'report_users', 2285 title: 'Mappers: ', 2286 separator: ', ', 2287 content: (row => `${row.user}Â (${Number(row.c).toLocaleString()})`) 2288 }, 2289 ], 2290 queries: { 2291"Mappers": `WITH 2292 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2293 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2294 2295 tile_size * {x:UInt32} AS tile_x_begin, 2296 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2297 2298 tile_size * {y:UInt32} AS tile_y_begin, 2299 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2300 2301 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2302 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2303 2304 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2305 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2306 2307 y * 1024 + x AS pos, 2308 2309 count() * {sampling:UInt32} AS total, 2310 pow(least(1, total / 20000 * zoom_factor), 1/5) AS transparency, 2311 2312 cityHash64(user) AS hash, 2313 hash MOD 256 AS h1, 2314 hash DIV 256 MOD 256 AS h2, 2315 2316 (0.5 + 0.5 * transparency) * 255 AS alpha, 2317 avg(h1) AS red, 2318 avg(h2) AS green, 2319 avg(least(255, greatest(0, 255 - (h1 + h2) / 2))) AS blue 2320 2321SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2322FROM {table:Identifier} 2323WHERE in_tile 2324GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2325 2326"Density": `WITH 2327 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2328 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2329 2330 tile_size * {x:UInt32} AS tile_x_begin, 2331 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2332 2333 tile_size * {y:UInt32} AS tile_y_begin, 2334 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2335 2336 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2337 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2338 2339 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2340 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2341 2342 y * 1024 + x AS pos, 2343 2344 count() * {sampling:UInt32} AS total, 2345 2346 pow(least(1, total / 20000 * zoom_factor), 1/5) AS color1, 2347 pow(least(1, total / 1000000 * zoom_factor), 1/5) AS color2, 2348 pow(least(1, total / 20000000 * zoom_factor), 1/5) AS color3, 2349 2350 255 AS alpha, 2351 color3 * 255 AS red, 2352 color2 * 255 AS green, 2353 color1 * 255 AS blue 2354 2355SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2356FROM {table:Identifier} 2357WHERE in_tile 2358GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2359 2360"Freshness": `WITH 2361 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2362 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2363 2364 tile_size * {x:UInt32} AS tile_x_begin, 2365 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2366 2367 tile_size * {y:UInt32} AS tile_y_begin, 2368 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2369 2370 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2371 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2372 2373 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2374 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2375 2376 y * 1024 + x AS pos, 2377 2378 count() * {sampling:UInt32} AS total, 2379 pow(least(1, total / 20000 * zoom_factor), 1/5) AS transparency, 2380 2381 greatest(0, avg(timestamp::Int64 - '2007-01-01'::DateTime::Int64) 2382 / (now()::Int64 - '2007-01-01'::DateTime::Int64)) AS rel_time, 2383 2384 255 * transparency AS alpha, 2385 255 * (1 - rel_time) AS red, 2386 255 * rel_time AS green, 2387 0 AS blue 2388 2389SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2390FROM {table:Identifier} 2391WHERE in_tile 2392GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2393 2394"Trees": `WITH 2395 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2396 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2397 2398 tile_size * {x:UInt32} AS tile_x_begin, 2399 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2400 2401 tile_size * {y:UInt32} AS tile_y_begin, 2402 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2403 2404 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2405 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2406 2407 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2408 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2409 2410 y * 1024 + x AS pos, 2411 2412 count() * {sampling:UInt32} AS total, 2413 pow(least(1, total / 500 * zoom_factor), 1/5) AS transparency, 2414 2415 255 * transparency AS alpha, 2416 64 * transparency AS red, 2417 255 * (0.3 + 0.7 * transparency) AS green, 2418 32 AS blue 2419 2420SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2421FROM {table:Identifier} 2422WHERE in_tile AND "natural" = 'tree' 2423GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2424 2425"Street Lamps": `WITH 2426 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2427 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2428 2429 tile_size * {x:UInt32} AS tile_x_begin, 2430 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2431 2432 tile_size * {y:UInt32} AS tile_y_begin, 2433 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2434 2435 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2436 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2437 2438 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2439 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2440 2441 y * 1024 + x AS pos, 2442 2443 count() * {sampling:UInt32} AS total, 2444 pow(least(1, total / 100 * zoom_factor), 1/5) AS transparency, 2445 2446 255 * transparency AS alpha, 2447 255 AS red, 2448 200 * (0.5 + 0.5 * transparency) AS green, 2449 64 AS blue 2450 2451SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2452FROM {table:Identifier} 2453WHERE in_tile AND highway = 'street_lamp' 2454GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2455 2456"Power Grid": `WITH 2457 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2458 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2459 2460 tile_size * {x:UInt32} AS tile_x_begin, 2461 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2462 2463 tile_size * {y:UInt32} AS tile_y_begin, 2464 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2465 2466 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2467 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2468 2469 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2470 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2471 2472 y * 1024 + x AS pos, 2473 2474 count() * {sampling:UInt32} AS total, 2475 pow(least(1, total / 100 * zoom_factor), 1/5) AS transparency, 2476 2477 transform(power, 2478 ['tower', 'pole', 'substation', 'generator', 'transformer'], 2479 [0x00FFFF, 0x00CC66, 0xFF00FF, 0xFFFF00, 0xFF8800], 0x8888FF) AS color, 2480 2481 255 * (0.25 + 0.75 * transparency) AS alpha, 2482 avg(color DIV 0x10000) AS red,
2483 avg(color DIV 0x100 MOD 0x100) AS green, 2484 avg(color MOD 0x100) AS blue 2485 2486SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2487FROM {table:Identifier} 2488WHERE in_tile AND power != '' 2489GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2490 2491"Amenities": `WITH 2492 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2493 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2494 2495 tile_size * {x:UInt32} AS tile_x_begin, 2496 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2497 2498 tile_size * {y:UInt32} AS tile_y_begin, 2499 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2500 2501 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2502 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2503 2504 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2505 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2506 2507 y * 1024 + x AS pos, 2508 2509 count() * {sampling:UInt32} AS total, 2510 pow(least(1, total / 200 * zoom_factor), 1/5) AS transparency, 2511 2512 cityHash64(amenity) AS hash, 2513 2514 (0.25 + 0.75 * transparency) * 255 AS alpha, 2515 avg(hash MOD 256) AS red, 2516 avg(hash DIV 256 MOD 256) AS green, 2517 avg(hash DIV 65536 MOD 256) AS blue 2518 2519SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2520FROM {table:Identifier} 2521WHERE in_tile AND amenity != '' 2522GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2523 2524"Shops": `WITH 2525 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2526 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2527 2528 tile_size * {x:UInt32} AS tile_x_begin, 2529 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2530 2531 tile_size * {y:UInt32} AS tile_y_begin, 2532 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2533 2534 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2535 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2536 2537 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2538 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2539 2540 y * 1024 + x AS pos, 2541 2542 count() * {sampling:UInt32} AS total, 2543 pow(least(1, total / 200 * zoom_factor), 1/5) AS transparency, 2544 2545 cityHash64(shop) AS hash, 2546 2547 (0.25 + 0.75 * transparency) * 255 AS alpha, 2548 avg(hash MOD 256) AS red, 2549 avg(hash DIV 256 MOD 256) AS green, 2550 avg(hash DIV 65536 MOD 256) AS blue 2551 2552SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2553FROM {table:Identifier} 2554WHERE in_tile AND shop != '' 2555GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2556 2557"Railways": `WITH 2558 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2559 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2560 2561 tile_size * {x:UInt32} AS tile_x_begin, 2562 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2563 2564 tile_size * {y:UInt32} AS tile_y_begin, 2565 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2566 2567 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2568 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2569 2570 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2571 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2572 2573 y * 1024 + x AS pos, 2574 2575 count() * {sampling:UInt32} AS total, 2576 pow(least(1, total / 50 * zoom_factor), 1/5) AS transparency, 2577 2578 255 * (0.25 + 0.75 * transparency) AS alpha, 2579 255 * transparency AS red, 2580 64 * transparency AS green, 2581 16 AS blue 2582 2583SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2584FROM {table:Identifier} 2585WHERE in_tile AND railway != '' 2586GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2587 2588"Versions": `WITH 2589 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2590 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2591 2592 tile_size * {x:UInt32} AS tile_x_begin, 2593 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2594 2595 tile_size * {y:UInt32} AS tile_y_begin, 2596 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2597 2598 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2599 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2600 2601 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2602 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2603 2604 y * 1024 + x AS pos, 2605 2606 count() * {sampling:UInt32} AS total, 2607 pow(least(1, total / 20000 * zoom_factor), 1/5) AS transparency, 2608 2609 least(1, (avg(version) - 1) / 3) AS hot, 2610 2611 255 * transparency AS alpha, 2612 255 * hot AS red, 2613 64 * hot AS green, 2614 255 * (1 - hot) AS blue 2615 2616SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2617FROM {table:Identifier} 2618WHERE in_tile 2619GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024` 2620 } 2621 }, 2622 2623 "GBIF": { 2624 notice: "© GBIF.org, occurrence data (CC0 & CC-BY), https://www.gbif.org/", 2625 endpoints: [ 2626 { 2627 name: "Cloud (Real-Time)", 2628 urls: [ 2629 { 2630 url: "https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 2631 sticky: "https://{hash}.sticky.kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 2632 } 2633 ] 2634 }, 2635 ], 2636 levels: [ 2637 { table: 'gbif_mercator_sample10', sample: 10, priority: 1 }, 2638 { table: 'gbif_mercator', sample: 1, priority: 2 }, 2639 ], 2640 time: { column: 'eventdate', exclude: "eventdate > '1970-01-01'" }, 2641 report_total: { 2642 query: (condition => ` 2643 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 2644 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 2645 SELECT count() AS occurrences, uniq(species) AS species, 2646 minIf(eventdate, eventdate > '1900-01-01') AS first, max(eventdate) AS last 2647 FROM {table:Identifier} WHERE ${condition}`), 2648 content: (json => { 2649 let row = json.data[0]; 2650 let text = `Total ${Number(row.occurrences).toLocaleStr
2650ing()} occurrences of ${Number(row.species).toLocaleString()} species.`; 2651 if (row.occurrences > 0) text += ` Dates: ${row.first} â ${row.last}.`; 2652 if (json.statistics.rows_read > 1) text += ` Processed ${Number(json.statistics.rows_read).toLocaleString()} rows.`; 2653 return text; 2654 }), 2655 }, 2656 reports: [ 2657 { 2658 query: (condition => ` 2659 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 2660 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 2661 SELECT scientificname, species, count() AS c 2662 FROM {table:Identifier} 2663 WHERE species != '' AND ${condition} 2664 GROUP BY scientificname, species ORDER BY c DESC LIMIT 100`), 2665 field: 'species', 2666 wiki_field: 'species', 2667 id: 'report_species', 2668 title: 'Species: ', 2669 separator: ', ', 2670 content: (row => `${row.species}${row.c > 1 ? `\u00a0(${row.c})` : ''}`) 2671 }, 2672 { 2673 query: (condition => ` 2674 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 2675 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 2676 SELECT class, count() AS c FROM {table:Identifier} 2677 WHERE class != '' AND ${condition} 2678 GROUP BY class ORDER BY c DESC LIMIT 50`), 2679 field: 'class', 2680 wiki_field: 'class', 2681 id: 'report_classes', 2682 title: 'Class: ', 2683 separator: ', ', 2684 content: (row => `${row.class} (${Number(row.c).toLocaleString()})`) 2685 }, 2686 { 2687 query: (condition => ` 2688 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 2689 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 2690 SELECT kingdom, count() AS c FROM {table:Identifier} 2691 WHERE kingdom != '' AND ${condition} 2692 GROUP BY kingdom ORDER BY c DESC LIMIT 20`), 2693 field: 'kingdom', 2694 wiki_field: 'kingdom', 2695 id: 'report_kingdoms', 2696 title: 'Kingdom: ', 2697 separator: ', ', 2698 content: (row => `${row.kingdom} (${Number(row.c).toLocaleString()})`) 2699 }, 2700 ], 2701 queries: { 2702"Density": `WITH 2703 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2704 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2705 tile_size * {x:UInt32} AS tile_x_begin, 2706 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2707 tile_size * {y:UInt32} AS tile_y_begin, 2708 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2709 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2710 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2711 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2712 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2713 y * 1024 + x AS pos, 2714 2715 count() * {sampling:UInt32} AS total, 2716 pow(least(1, total / 100 * zoom_factor), 1/5) AS color1, 2717 pow(least(1, total / 10000 * zoom_factor), 1/5) AS color2, 2718 pow(least(1, total / 1000000 * zoom_factor), 1/5) AS color3, 2719 255 AS alpha, 2720 color3 * 255 AS red, 2721 color2 * 255 AS green, 2722 color1 * 255 AS blue 2723 2724SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2725FROM {table:Identifier} 2726WHERE in_tile 2727GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2728"Kingdoms": `WITH 2729 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2730 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2731 tile_size * {x:UInt32} AS tile_x_begin, 2732 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2733 tile_size * {y:UInt32} AS tile_y_begin, 2734 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2735 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2736 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2737 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2738 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2739 y * 1024 + x AS pos, 2740 2741 count() * {sampling:UInt32} AS total, 2742 cityHash64(kingdom) AS hash, 2743 pow(least(1, total / 1000 * zoom_factor), 1/5) AS transparency, 2744 (0.35 + 0.65 * transparency) * 255 AS alpha, 2745 avg(hash MOD 256) AS red, 2746 avg(hash DIV 256 MOD 256) AS green, 2747 avg(hash DIV 65536 MOD 256) AS blue 2748 2749SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2750FROM {table:Identifier} 2751WHERE in_tile 2752GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2753"Classes": `WITH 2754 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2755 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2756 tile_size * {x:UInt32} AS tile_x_begin, 2757 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2758 tile_size * {y:UInt32} AS tile_y_begin, 2759 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2760 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2761 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2762 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2763 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2764 y * 1024 + x AS pos, 2765 2766 count() * {sampling:UInt32} AS total, 2767 cityHash64(class) AS hash, 2768 pow(least(1, total / 1000 * zoom_factor), 1/5) AS transparency, 2769 (0.35 + 0.65 * transparency) * 255 AS alpha, 2770 avg(hash MOD 256) AS red, 2771 avg(hash DIV 256 MOD 256) AS green, 2772 avg(hash DIV 65536 MOD 256) AS blue 2773 2774SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2775FROM {table:Identifier} 2776WHERE in_tile AND class != '' 2777GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2778"Birds": `WITH 2779 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2780 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2781 tile_size * {x:UInt32} AS tile_x_begin, 2782 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2783 tile_size * {y:UInt32} AS tile_y_begin, 2784 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2785 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2786 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2787 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2788 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2789 y * 1024 + x AS pos, 2790 2791 count() * {sampling:UInt32} AS total, 2792 cityHash64(family) AS hash, 2793 pow(least(1, total / 300 * zoom_factor), 1/5) AS transparency, 2794 (0.35 + 0.65 * transparency) * 255 AS alpha, 2795 avg(hash MOD 256) AS red, 2796 avg(hash DIV 256 MOD 256) AS green, 2797 avg(hash DIV 65536 MOD 256) AS blue 2798 2799SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2800FROM {table:Identifier} 2801WHERE in_tile AND class = 'Aves' 2802GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2803"Mammals": `WITH 2804 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2805 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2806 tile_size * {x:UInt32} AS tile_x_begin, 2807 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2808 tile_size * {y:UInt32} AS tile_y_begin, 2809 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2810 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2811 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2812 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2813 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2814 y * 1024 + x AS pos, 2815 2816 count() * {sampling:UInt32} AS total, 2817 cityHash64(family) AS hash, 2818 pow(least(1, total / 100 * zoom_factor), 1/5) AS transparency, 2819 (0.35 + 0.65 * transparency) * 255 AS alpha, 2820 avg(hash MOD 256) AS red, 2821 avg(hash DIV 256 MOD 256) AS green, 2822 avg(hash DIV 65536 MOD 256) AS blue 2823 2824SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2825FROM {table:Identifier} 2826WHERE in_tile AND class = 'Mammalia' 2827GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2828"Insects": `WITH 2829 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2830 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2831 tile_size * {x:UInt32} AS tile_x_begin, 2832 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2833 tile_size * {y:UInt32} AS tile_y_begin, 2834 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2835 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2836 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2837 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2838 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2839 y * 1024 + x AS pos, 2840 2841 count() * {sampling:UInt32} AS total, 2842 cityHash64(family) AS hash, 2843 pow(least(1, total / 300 * zoom_factor), 1/5) AS transparency, 2844 (0.35 + 0.65 * transparency) * 255 AS alpha, 2845 avg(hash MOD 256) AS red, 2846 avg(hash DIV 256 MOD 256) AS green, 2847 avg(hash DIV 65536 MOD 256) AS blue 2848 2849SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2850FROM {table:Identifier} 2851WHERE in_tile AND class = 'Insecta' 2852GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2853"Plants": `WITH 2854 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2855 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2856 tile_size * {x:UInt32} AS tile_x_begin, 2857 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2858 tile_size * {y:UInt32} AS tile_y_begin, 2859 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2860 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2861 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2862 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2863 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2864 y * 1024 + x AS pos, 2865 2866 count() * {sampling:UInt32} AS total, 2867 cityHash64(family) AS hash, 2868 pow(least(1, total / 500 * zoom_factor), 1/5) AS transparency, 2869 (0.35 + 0.65 * transparency) * 255 AS alpha, 2870 avg(hash MOD 256) AS red, 2871 avg(hash DIV 256 MOD 256) AS green, 2872 avg(hash DIV 65536 MOD 256) AS blue 2873 2874SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2875FROM {table:Identifier} 2876WHERE in_tile AND kingdom = 'Plantae' 2877GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2878"Fungi": `WITH 2879 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2880 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2881 tile_size * {x:UInt32} AS tile_x_begin, 2882 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2883 tile_size * {y:UInt32} AS tile_y_begin, 2884 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2885 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2886 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2887 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2888 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2889 y * 1024 + x AS pos, 2890 2891 count() * {sampling:UInt32} AS total, 2892 cityHash64(family) AS hash, 2893 pow(least(1, total / 50 * zoom_factor), 1/5) AS transparency, 2894 (0.35 + 0.65 * transparency) * 255 AS alpha, 2895 avg(hash MOD 256) AS red, 2896 avg(hash DIV 256 MOD 256) AS green, 2897 avg(hash DIV 65536 MOD 256) AS blue 2898 2899SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2900FROM {table:Identifier} 2901WHERE in_tile AND kingdom = 'Fungi' 2902GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 2903"Time": `WITH 2904 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2905 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2906 tile_size * {x:UInt32} AS tile_x_begin, 2907 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2908 tile_size * {y:UInt32} AS tile_y_begin, 2909 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2910 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2911 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2912 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2913 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2914 y * 1024 + x AS pos, 2915 2916 count() * {sampling:UInt32} AS total, 2917 pow(least(1, total / 100 * zoom_factor), 1/5) AS transparency, 2918 greatest(0, avg(toYear(eventdate) - 1950) / (2025 - 1950)) AS rel, 2919 255 * (0.35 + 0.65 * transparency) AS alpha, 2920 255 * (1 - rel) AS red, 2921 255 * rel AS green, 2922 64 AS blue 2923 2924SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 2925FROM {table:Identifier} 2926WHERE in_tile AND eventdate > '1950-01-01' 2927GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024` 2928 } 2929 }, 2930 2931 2932 "OSM History": { 2933 notice: "© OpenStreetMap contributors, ODbL v1.0 (full history)", 2934 endpoints: [ 2935 { 2936 name: "Cloud (Real-Time)", 2937 urls: [ 2938 { 2939 url: "https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 2940 sticky: "https://{hash}.sticky.kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 2941 } 2942 ] 2943 }, 2944 ], 2945 levels: [ 2946 { table: 'osm_history_mercator_sample10', sample: 10, priority: 1 }, 2947 { table: 'osm_history_mercator', sample: 1, priority: 2 }, 2948 ], 2949 time: { column: 'timestamp' }, 2950 report_total: { 2951 query: (condition => ` 2952 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 2953 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 2954 SELECT count() AS versions, uniq(uid) AS mappers, min(timestamp) AS first, max(timestamp) AS last 2955 FROM {table:Identifier} WHERE ${condition}`), 2956 content: (json => { let row = json.data[0];
2956 let text = `Total ${Number(row.versions).toLocaleString()} node versions by ${Number(row.mappers).toLocaleString()} mappers.`; if (row.versions>0) text += ` Edited: ${row.first} â ${row.last}.`; if (json.statistics.rows_read>1) text += ` Processed ${Number(json.statistics.rows_read).toLocaleString()} rows.`; return text; }), 2957 }, 2958 reports: [ 2959 { 2960 query: (condition => ` 2961 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 2962 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 2963 SELECT name, count() AS c FROM {table:Identifier} 2964 WHERE name != '' AND ${condition} 2965 GROUP BY name ORDER BY c DESC LIMIT 100`), 2966 field: 'name', 2967 id: 'report_name', 2968 title: 'Names: ', 2969 separator: ', ', 2970 content: (row => `${row.name}${row.c > 1 ? ` (${Number(row.c).toLocaleString()})` : ''}`) 2971 }, 2972 { 2973 query: (condition => ` 2974 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 2975 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 2976 SELECT user, count() AS c FROM {table:Identifier} 2977 WHERE user != '' AND ${condition} 2978 GROUP BY user ORDER BY c DESC LIMIT 100`), 2979 field: 'user', 2980 id: 'report_user', 2981 title: 'Mappers: ', 2982 separator: ', ', 2983 content: (row => `${row.user}${row.c > 1 ? ` (${Number(row.c).toLocaleString()})` : ''}`) 2984 }, 2985 ], 2986 queries: { 2987"Density": `WITH 2988 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 2989 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 2990 tile_size * {x:UInt32} AS tile_x_begin, 2991 tile_size * ({x:UInt32} + 1) AS tile_x_end, 2992 tile_size * {y:UInt32} AS tile_y_begin, 2993 tile_size * ({y:UInt32} + 1) AS tile_y_end, 2994 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 2995 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 2996 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 2997 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 2998 y * 1024 + x AS pos, 2999 3000 count() * {sampling:UInt32} AS total, 3001 pow(least(1, total / 20000 * zoom_factor), 1/5) AS color1, 3002 pow(least(1, total / 1000000 * zoom_factor), 1/5) AS color2, 3003 pow(least(1, total / 20000000 * zoom_factor), 1/5) AS color3, 3004 255 AS alpha, 3005 color3 * 255 AS red, color2 * 255 AS green, color1 * 255 AS blue 3006 3007SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 3008FROM {table:Identifier} 3009WHERE in_tile 3010GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 3011"Freshness": `WITH 3012 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 3013 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3014 tile_size * {x:UInt32} AS tile_x_begin, 3015 tile_size * ({x:UInt32} + 1) AS tile_x_end, 3016 tile_size * {y:UInt32} AS tile_y_begin, 3017 tile_size * ({y:UInt32} + 1) AS tile_y_end, 3018 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3019 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3020 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 3021 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 3022 y * 1024 + x AS pos, 3023 3024 count() * {sampling:UInt32} AS total, 3025 pow(least(1, total / 20000 * zoom_factor), 1/5) AS transparency, 3026 greatest(0, avg(timestamp::Int64 - '2005-01-01'::DateTime::Int64) / (now()::Int64 - '2005-01-01'::DateTime::Int64)) AS rel, 3027 255 * (0.35 + 0.65 * transparency) AS alpha, 3028 255 * (1 - rel) AS red, 255 * rel AS green, 0 AS blue 3029 3030SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 3031FROM {table:Identifier} 3032WHERE in_tile 3033GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 3034"Mappers": `WITH 3035 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 3036 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3037 tile_size * {x:UInt32} AS tile_x_begin, 3038 tile_size * ({x:UInt32} + 1) AS tile_x_end, 3039 tile_size * {y:UInt32} AS tile_y_begin, 3040 tile_size * ({y:UInt32} + 1) AS tile_y_end, 3041 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3042 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3043 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 3044 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 3045 y * 1024 + x AS pos, 3046 3047 count() * {sampling:UInt32} AS total, 3048 pow(least(1, total / 20000 * zoom_factor), 1/5) AS transparency, 3049 cityHash64(user) AS hash, hash MOD 256 AS h1, hash DIV 256 MOD 256 AS h2, 3050 (0.5 + 0.5 * transparency) * 255 AS alpha, 3051 avg(h1) AS red, avg(h2) AS green, avg(least(255, greatest(0, 255 - (h1 + h2) / 2))) AS blue 3052 3053SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 3054FROM {table:Identifier} 3055WHERE in_tile 3056GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 3057"Versions": `WITH 3058 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 3059 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3060 tile_size * {x:UInt32} AS tile_x_begin, 3061 tile_size * ({x:UInt32} + 1) AS tile_x_end, 3062 tile_size * {y:UInt32} AS tile_y_begin, 3063 tile_size * ({y:UInt32} + 1) AS tile_y_end, 3064 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3065 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3066 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 3067 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 3068 y * 1024 + x AS pos, 3069 3070 count() * {sampling:UInt32} AS total, 3071 pow(least(1, total / 20000 * zoom_factor), 1/5) AS transparency, 3072 least(1, (avg(version) - 1) / 5) AS hot, 3073 255 * (0.35 + 0.65 * transparency) AS alpha, 3074 255 * hot AS red, 64 * hot AS green, 255 * (1 - hot) AS blue 3075 3076SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 3077FROM {table:Identifier} 3078WHERE in_tile 3079GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 3080"Edits per node": `WITH 3081 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 3082 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3083 tile_size * {x:UInt32} AS tile_x_begin, 3084 tile_size * ({x:UInt32} + 1) AS tile_x_end, 3085 tile_size * {y:UInt32} AS tile_y_begin, 3086 tile_size * ({y:UInt32} + 1) AS tile_y_end, 3087 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3088 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3089 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 3090 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 3091 y * 1024 + x AS pos, 3092 3093 count() * {sampling:UInt32} AS total, 3094 pow(least(1, total / 20000 * zoom_factor), 1/5) AS transparency, 3095 least(1, uniq(id) > 0 ? count() / uniq(id) / 6 : 0) AS churn, 3096 255 * (0.35 + 0.65 * transparency) AS alpha, 3097 255 * churn AS red, 128 * (0.35 + 0.65 * transparency) AS green, 255 * (1 - churn) AS blue 3098 3099SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 3100FROM {table:Identifier} 3101WHERE in_tile 3102GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024` 3103 } 3104 }, 3105 3106 "Buildings": { 3107 notice: "© Overture Maps Foundation, https://overturemaps.org/", 3108 endpoints: [ 3109 { 3110 name: "Cloud (Real-Time)", 3111 urls: [ 3112 { 3113 url: "https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 3114 sticky: "https://{hash}.sticky.kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 3115 } 3116 ] 3117 }, 3118 ], 3119 levels: [ 3120 { table: 'overture_mercator_sample10', sample: 10, priority: 1 }, 3121 { table: 'overture_mercator', sample: 1, priority: 2 }, 3122 ], 3123 report_total: { 3124 query: (condition => ` 3125 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 3126 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 3127 SELECT count() AS buildings, uniqIf(name, name != '') AS named 3128 FROM {table:Identifier} WHERE ${condition}`), 3129 content: (json => { let row = json.data[0];
3129 let text = `Total ${Number(row.buildings).toLocaleString()} buildings, ${Number(row.named).toLocaleString()} named.`; if (json.statistics.rows_read>1) text += ` Processed ${Number(json.statistics.rows_read).toLocaleString()} rows.`; return text; }), 3130 }, 3131 reports: [ 3132 { 3133 query: (condition => ` 3134 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 3135 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 3136 SELECT name, count() AS c FROM {table:Identifier} 3137 WHERE name != '' AND ${condition} 3138 GROUP BY name ORDER BY c DESC LIMIT 100`), 3139 field: 'name', 3140 id: 'report_name', 3141 title: 'Names: ', 3142 separator: ', ', 3143 content: (row => `${row.name}${row.c > 1 ? ` (${Number(row.c).toLocaleString()})` : ''}`) 3144 }, 3145 { 3146 query: (condition => ` 3147 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 3148 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 3149 SELECT class, count() AS c FROM {table:Identifier} 3150 WHERE class != '' AND ${condition} 3151 GROUP BY class ORDER BY c DESC LIMIT 100`), 3152 field: 'class', 3153 id: 'report_class', 3154 title: 'Class: ', 3155 separator: ', ', 3156 content: (row => `${row.class}${row.c > 1 ? ` (${Number(row.c).toLocaleString()})` : ''}`) 3157 }, 3158 { 3159 query: (condition => ` 3160 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 3161 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 3162 SELECT subtype, count() AS c FROM {table:Identifier} 3163 WHERE subtype != '' AND ${condition} 3164 GROUP BY subtype ORDER BY c DESC LIMIT 100`), 3165 field: 'subtype', 3166 id: 'report_subtype', 3167 title: 'Subtype: ', 3168 separator: ', ', 3169 content: (row => `${row.subtype}${row.c > 1 ? ` (${Number(row.c).toLocaleString()})` : ''}`) 3170 }, 3171 ], 3172 queries: { 3173"Density": `WITH 3174 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 3175 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3176 tile_size * {x:UInt32} AS tile_x_begin, 3177 tile_size * ({x:UInt32} + 1) AS tile_x_end, 3178 tile_size * {y:UInt32} AS tile_y_begin, 3179 tile_size * ({y:UInt32} + 1) AS tile_y_end, 3180 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3181 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3182 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 3183 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 3184 y * 1024 + x AS pos, 3185 3186 count() * {sampling:UInt32} AS total, 3187 pow(least(1, total / 500 * zoom_factor), 1/5) AS color1, 3188 pow(least(1, total / 50000 * zoom_factor), 1/5) AS color2, 3189 pow(least(1, total / 5000000 * zoom_factor), 1/5) AS color3, 3190 255 AS alpha, 3191 color1 * 255 AS red, color2 * 255 AS green, color3 * 255 AS blue 3192 3193SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 3194FROM {table:Identifier} 3195WHERE in_tile 3196GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 3197"Height": `WITH 3198 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 3199 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3200 tile_size * {x:UInt32} AS tile_x_begin, 3201 tile_size * ({x:UInt32} + 1) AS tile_x_end, 3202 tile_size * {y:UInt32} AS tile_y_begin, 3203 tile_size * ({y:UInt32} + 1) AS tile_y_end, 3204 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3205 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3206 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 3207 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 3208 y * 1024 + x AS pos, 3209 3210 count() * {sampling:UInt32} AS total, 3211 pow(least(1, total / 500 * zoom_factor), 1/5) AS transparency, 3212 least(1, avgIf(height, height > 0) / 50) AS tall, 3213 255 * (0.3 + 0.7 * transparency) AS alpha, 3214 255 * tall AS red, 255 * (1 - tall) * transparency AS green, 255 * (1 - tall) AS blue 3215 3216SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 3217FROM {table:Identifier} 3218WHERE in_tile 3219GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 3220"Floors": `WITH 3221 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 3222 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3223 tile_size * {x:UInt32} AS tile_x_begin, 3224 tile_size * ({x:UInt32} + 1) AS tile_x_end, 3225 tile_size * {y:UInt32} AS tile_y_begin, 3226 tile_size * ({y:UInt32} + 1) AS tile_y_end, 3227 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3228 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3229 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 3230 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 3231 y * 1024 + x AS pos, 3232 3233 count() * {sampling:UInt32} AS total, 3234 pow(least(1, total / 500 * zoom_factor), 1/5) AS transparency, 3235 least(1, avgIf(num_floors, num_floors > 0) / 20) AS tall, 3236 255 * (0.3 + 0.7 * transparency) AS alpha, 3237 255 * tall AS red, 128 * transparency AS green, 255 * (1 - tall) AS blue 3238 3239SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 3240FROM {table:Identifier} 3241WHERE in_tile 3242GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 3243"Roof Shape": `WITH 3244 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 3245 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3246 tile_size * {x:UInt32} AS tile_x_begin, 3247 tile_size * ({x:UInt32} + 1) AS tile_x_end, 3248 tile_size * {y:UInt32} AS tile_y_begin, 3249 tile_size * ({y:UInt32} + 1) AS tile_y_end, 3250 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3251 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3252 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 3253 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 3254 y * 1024 + x AS pos, 3255 3256 count() * {sampling:UInt32} AS total, 3257 cityHash64(roof_shape) AS hash, 3258 pow(least(1, total / 500 * zoom_factor), 1/5) AS transparency, 3259 (0.3 + 0.7 * transparency) * 255 AS alpha, 3260 avg(hash MOD 256) AS red, avg(hash DIV 256 MOD 256) AS green, avg(hash DIV 65536 MOD 256) AS blue 3261 3262SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 3263FROM {table:Identifier} 3264WHERE in_tile AND roof_shape != '' 3265GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 3266"Named": `WITH 3267 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 3268 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3269 tile_size * {x:UInt32} AS tile_x_begin, 3270 tile_size * ({x:UInt32} + 1) AS tile_x_end, 3271 tile_size * {y:UInt32} AS tile_y_begin, 3272 tile_size * ({y:UInt32} + 1) AS tile_y_end, 3273 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3274 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3275 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 3276 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 3277 y * 1024 + x AS pos, 3278 3279 count() * {sampling:UInt32} AS total, 3280 pow(least(1, total / 500 * zoom_factor), 1/5) AS transparency, 3281 avg(name != '') AS named, 3282 255 * transparency AS alpha,
3283 255 * (1 - named) AS red, 255 * named AS green, 64 AS blue 3284 3285SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 3286FROM {table:Identifier} 3287WHERE in_tile 3288GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024` 3289 } 3290 }, 3291 3292 "Weather": { 3293 notice: "NOAA GHCNh (Global Historical Climatology Network - hourly), current, public domain", 3294 endpoints: [ 3295 { 3296 name: "Cloud (Real-Time)", 3297 urls: [ 3298 { 3299 url: "https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 3300 sticky: "https://{hash}.sticky.kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 3301 } 3302 ] 3303 }, 3304 ], 3305 levels: [ 3306 { table: 'ghcnh_mercator', sample: 1, priority: 1 }, 3307 ], 3308 time: { column: 'timestamp' }, 3309 report_total: { 3310 query: (condition => ` 3311 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 3312 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile, 3313 (SELECT max(toInt32(toDate32(timestamp))) - min(toInt32(toDate32(timestamp))) FROM {table:Identifier} WHERE ${condition}) AS span, 3314 multiIf(span <= 400, 1, span <= 2800, 7, span <= 12000, 30, span <= 36000, 91, 365) AS bsize, 3315 intDiv(toInt32(toDate32(timestamp)), bsize) * bsize AS bucket 3316 SELECT bucket AS day, count() AS obs, 3317 round(quantileIf(0.01)(temperature, temperature BETWEEN -95 AND 65), 1) AS lo_temperature, round(quantileIf(0.99)(temperature, temperature BETWEEN -95 AND 65), 1) AS hi_temperature, round(avgIf(temperature, temperature BETWEEN -95 AND 65), 1) AS avg_temperature, 3318 round(quantileIf(0.01)(dew_point, dew_point BETWEEN -100 AND 45), 1) AS lo_dew_point, round(quantileIf(0.99)(dew_point, dew_point BETWEEN -100 AND 45), 1) AS hi_dew_point, round(avgIf(dew_point, dew_point BETWEEN -100 AND 45), 1) AS avg_dew_point, 3319 round(quantileIf(0.01)(relative_humidity, relative_humidity BETWEEN 0 AND 100), 0) AS lo_relative_humidity, round(quantileIf(0.99)(relative_humidity, relative_humidity BETWEEN 0 AND 100), 0) AS hi_relative_humidity, round(avgIf(relative_humidity, relative_humidity BETWEEN 0 AND 100), 0) AS avg_relative_humidity, 3320 round(quantileIf(0.01)(wet_bulb, wet_bulb BETWEEN -95 AND 45), 1) AS lo_wet_bulb, round(quantileIf(0.99)(wet_bulb, wet_bulb BETWEEN -95 AND 45), 1) AS hi_wet_bulb, round(avgIf(wet_bulb, wet_bulb BETWEEN -95 AND 45), 1) AS avg_wet_bulb, 3321 round(quantileIf(0.01)(wind_speed, wind_speed BETWEEN 0 AND 120), 1) AS lo_wind_speed, round(quantileIf(0.99)(wind_speed, wind_speed BETWEEN 0 AND 120), 1) AS hi_wind_speed, round(avgIf(wind_speed, wind_speed BETWEEN 0 AND 120), 1) AS avg_wind_speed, 3322 round(quantileIf(0.01)(wind_gust, wind_gust BETWEEN 0 AND 160), 1) AS lo_wind_gust, round(quantileIf(0.99)(wind_gust, wind_gust BETWEEN 0 AND 160), 1) AS hi_wind_gust, round(avgIf(wind_gust, wind_gust BETWEEN 0 AND 160), 1) AS avg_wind_gust, 3323 round(quantileIf(0.01)(pressure, pressure BETWEEN 850 AND 1090), 0) AS lo_pressure, round(quantileIf(0.99)(pressure, pressure BETWEEN 850 AND 1090), 0) AS hi_pressure, round(avgIf(pressure, pressure BETWEEN 850 AND 1090), 0) AS avg_pressure, 3324 round(quantileIf(0.01)(visibility, visibility BETWEEN 0 AND 100), 1) AS lo_visibility, round(quantileIf(0.99)(visibility, visibility BETWEEN 0 AND 100), 1) AS hi_visibility, round(avgIf(visibility, visibility BETWEEN 0 AND 100), 1) AS avg_visibility, 3325 round(quantileIf(0.01)(ceiling, ceiling BETWEEN 0 AND 30000), 0) AS lo_ceiling, round(quantileIf(0.99)(ceiling, ceiling BETWEEN 0 AND 30000), 0) AS hi_ceiling, round(avgIf(ceiling, ceiling BETWEEN 0 AND 30000), 0) AS avg_ceiling, 3326 round(quantileIf(0.01)(cloud_cover, cloud_cover BETWEEN 0 AND 100), 0) AS lo_cloud_cover, round(quantileIf(0.99)(cloud_cover, cloud_cover BETWEEN 0 AND 100), 0) AS hi_cloud_cover, round(avgIf(cloud_cover, cloud_cover BETWEEN 0 AND 100), 0) AS avg_cloud_cover, 3327 round(quantileIf(0.01)(precipitation, precipitation BETWEEN 0 AND 50), 2) AS lo_precipitation, round(quantileIf(0.99)(precipitation, precipitation BETWEEN 0 AND 50), 2) AS hi_precipitation, round(avgIf(precipitation, precipitation BETWEEN 0 AND 50), 2) AS avg_precipitation, 3328 round(quantileIf(0.01)(snow_depth, snow_depth BETWEEN 0 AND 12000 AND (temperature IS NULL OR temperature < 20)), 1) AS lo_snow_depth, roun
3328d(quantileIf(0.99)(snow_depth, snow_depth BETWEEN 0 AND 12000 AND (temperature IS NULL OR temperature < 20)), 1) AS hi_snow_depth, round(avgIf(snow_depth, snow_depth BETWEEN 0 AND 12000 AND (temperature IS NULL OR temperature < 20)), 1) AS avg_snow_depth 3329 FROM {table:Identifier} WHERE ${condition} 3330 GROUP BY bucket ORDER BY bucket`), 3331 html: (json => { 3332 const M = [{"lo": "lo_temperature", "hi": "hi_temperature", "avg": "avg_temperature", "l": "Temperature", "u": "°C", "col": "#e0552f", "d": 1}, {"lo": "lo_dew_point", "hi": "hi_dew_point", "avg": "avg_dew_point", "l": "Dew point", "u": "°C", "col": "#37a25a", "d": 1}, {"lo": "lo_relative_humidity", "hi": "hi_relative_humidity", "avg": "avg_relative_humidity", "l": "Humidity", "u": "%", "col": "#1f9ec4", "d": 0}, {"lo": "lo_wet_bulb", "hi": "hi_wet_bulb", "avg": "avg_wet_bulb", "l": "Wet bulb", "u": "°C", "col": "#7a5ad0", "d": 1}, {"lo": "lo_wind_speed", "hi": "hi_wind_speed", "avg": "avg_wind_speed", "l": "Wind", "u": "m/s", "col": "#e0902a", "d": 1}, {"lo": "lo_wind_gust", "hi": "hi_wind_gust", "avg": "avg_wind_gust", "l": "Gust", "u": "m/s", "col": "#c25a12", "d": 1}, {"lo": "lo_pressure", "hi": "hi_pressure", "avg": "avg_pressure", "l": "Sea-level P", "u": "hPa", "col": "#6a5acd", "d": 0}, {"lo": "lo_visibility", "hi": "hi_visibility", "avg": "avg_visibility", "l": "Visibility", "u": "km", "col": "#7f8c99", "d": 1}, {"lo": "lo_ceiling", "hi": "hi_ceiling", "avg": "avg_ceiling", "l": "Ceiling", "u": "m", "col": "#8a99a8", "d": 0}, {"lo": "lo_cloud_cover", "hi": "hi_cloud_cover", "avg": "avg_cloud_cover", "l": "Cloud", "u": "%", "col": "#788696", "d": 0}, {"lo": "lo_precipitation", "hi": "hi_precipitation", "avg": "avg_precipitation", "l": "Precip", "u": "mm", "col": "#2a6ad0", "d": 2}, {"lo": "lo_snow_depth", "hi": "hi_snow_depth", "avg": "avg_snow_depth", "l": "Snow", "u": "mm", "col": "#8ab6e0", "d": 1}]; 3333 const rows = json.data || []; 3334 if (!rows.length) return 'No data in the selected area.'; 3335 let obs = 0, dmin = Infinity, dmax = -Infinity; 3336 for (const r of rows) { obs += Number(r.obs) || 0; const d = +r.day; if (d < dmin) dmin = d; if (d > dmax) dmax = d; } 3337 const span = (dmax - dmin) || 1, W = 400, H = 26; 3338 const fmt = d => new Date(d * 86400000).toISOString().slice(0, 10); 3339 const f = (v, d) => (+v.toFixed(d)).toString(); 3340 let step = Infinity; 3341 for (let i = 1; i < rows.length; i++) { const dd = (+rows[i].day) - (+rows[i-1].day); if (dd > 0 && dd < step) step = dd; } 3342 if (!isFinite(step)) step = 1; 3343 const bkt = span <= 400 ? 'daily' : span <= 2800 ? 'weekly' : span <= 12000 ? 'monthly' : span <= 36000 ? 'quarterly' : 'yearly'; 3344 const hdr = `${obs.toLocaleString()} observations · ${bkt} 1â99% + avg · ${fmt(dmin)} â ${fmt(dmax)}`; 3345 const days = rows.map(r => +r.day); 3346 const series = { days, bsize: step, hdr, metrics: [] }; 3347 let body = ''; 3348 for (const m of M) { 3349 let lo = Infinity, hi = -Infinity; const pres = [], v = []; 3350 for (const r of rows) { const a = r[m.lo]; 3351 if (a == null) { v.push(null); continue; } 3352 const la = +a, hb = +r[m.hi], av = +r[m.avg]; 3353 if (la < lo) lo = la; if (hb > hi) hi = hb; 3354 pres.push([+r.day, la, hb, av]); v.push([la, av, hb]); } 3355 if (pres.length < 1) continue; 3356 const rng = (hi - lo) || 1, X = d => ((d-dmin)/span*W).toFixed(1), Y = q => (H-2 - (q-lo)/rng*(H-4)).toFixed(1); 3357 let band = '', line = '', seg = []; 3358 const flush = () => { 3359 if (!seg.length) return; 3360 if (seg.length === 1) { const p = seg[0]; band += `M${X(p[0])},${Y(p[2])}L${X(p[0])},${Y(p[1])}`; } 3361 else { band += 'M' + seg.map(p => X(p[0])+','+Y(p[2])).join('L') + 'L' + seg.slice().reverse().map(p => X(p[0])+','+Y(p[1])).join('L') + 'Z'; 3362 line += 'M' + seg.map(p => X(p[0])+','+Y(p[3])).join('L'); } 3363 seg = []; 3364 }; 3365 for (let i = 0; i < pres.length; i++) { if (i > 0 && pres[i][0] - pres[i-1][0] > step * 1.5) flush(); seg.push(pres[i]); } 3366 flush(); 3367 series.metrics.push({ label: m.l, unit: m.u, dec: m.d, range: `${f(lo,m.d)}â${f(hi,m.d)} ${m.u}`, v }); 3368 body += `<div class="wrow" style="display:flex;align-items:center;gap:6px;font-size:11px;margin:1px 0">` 3369 + `<span style="flex:0 0 74px;text-align:right;opacity:.85">${m.l}</span>` 3370 + `<svg class="wspark" viewBox="0 0 ${W} ${H}" preserveAspectRatio="none" style="flex:1 1 auto;min-width:0;height:${H}px">` 3371 + `<path d="${band}" fill="${m.col}" fill-opacity="0.2" stroke="${m.col}" stroke-opacity="0.35" stroke-width="0.5" vector-effect="non-scaling-stroke"/>` 3372 + `<path d="${line}" fill="none" stroke="${m.col}" stroke-width="1" vector-effect="non-scaling-stroke"/></svg>` 3373 + `<span class="wval" style="flex:0 0 88px;text-align:right;opacity:.7;font-variant-numeric:tabular-nums">${f(lo,m.d)}â${f(hi,m.d)} ${m.u}</span></div>`; 3374 }
3375 const attr = JSON.stringify(series).replace(/&/g,'&').replace(/</g,'<').replace(/>/g,'>').replace(/'/g,'''); 3376 return `<div id="wcharts" style="position:relative" data-series='${attr}' onmousemove="weatherHover(event)" onmouseleave="weatherHover()">` 3377 + `<div id="wline" style="position:absolute;width:1px;background:currentColor;opacity:.4;display:none;pointer-events:none"></div>` 3378 + `<div id="whdr" style="margin:2px 0 6px;opacity:.75">${hdr}</div>` 3379 + body + `</div>`; 3380 }), 3381 }, 3382 reports: [ 3383 { 3384 query: (condition => ` 3385 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 3386 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 3387 SELECT name, round(avgIf(temperature, temperature BETWEEN -95 AND 65), 1) AS t, count() AS c 3388 FROM {table:Identifier} 3389 WHERE name != '' AND ${condition} 3390 GROUP BY name ORDER BY c DESC LIMIT 100`), 3391 field: 'name', id: 'report_stations', title: 'Stations: ', separator: ', ', 3392 content: (row => `${row.name}` + (row.t != null ? ` (${row.t}°C)` : '')) 3393 }, 3394 ], 3395 queries: { 3396"Temperature": `WITH 3397 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3398 tile_size * {x:UInt32} AS tile_x_begin, tile_size * ({x:UInt32} + 1) AS tile_x_end, 3399 tile_size * {y:UInt32} AS tile_y_begin, tile_size * ({y:UInt32} + 1) AS tile_y_end, 3400 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3401 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3402 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 65536)((s, c), cell) 3403 FROM ( 3404 SELECT (bitShiftRight(mercator_y - tile_y_begin, 24 - {z:UInt8}) * 256 3405 + bitShiftRight(mercator_x - tile_x_begin, 24 - {z:UInt8}))::UInt32 AS cell, 3406 ifNull(sumIf(temperature, temperature BETWEEN -95 AND 65), 0.0) AS s, countIf(temperature BETWEEN -95 AND 65) AS c 3407 FROM {table:Identifier} WHERE in_tile GROUP BY cell ) ) AS g0, 3408 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 16384)((bs, bc), blk) 3409 FROM ( SELECT (((idx DIV 256) DIV 2) * 128 + ((idx % 256) DIV 2))::UInt32 AS blk, 3410 sum(e.1) AS bs, sum(e.2) AS bc 3411 FROM ( SELECT number - 1 AS idx, g0[number] AS e FROM numbers(1, 65536) ) GROUP BY blk ) ) AS g1, 3412 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 4096)((bs, bc), blk) 3413 FROM ( SELECT (((idx DIV 128) DIV 2) * 64 + ((idx % 128) DIV 2))::UInt32 AS blk, 3414 sum(e.1) AS bs, sum(e.2) AS bc 3415 FROM ( SELECT number - 1 AS idx, g1[number] AS e FROM numbers(1, 16384) ) GROUP BY blk ) ) AS g2, 3416 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 1024)((bs, bc), blk) 3417 FROM ( SELECT (((idx DIV 64) DIV 2) * 32 + ((idx % 64) DIV 2))::UInt32 AS blk, 3418 sum(e.1) AS bs, sum(e.2) AS bc 3419 FROM ( SELECT number - 1 AS idx, g2[number] AS e FROM numbers(1, 4096) ) GROUP BY blk ) ) AS g3, 3420 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 256)((bs, bc), blk) 3421 FROM ( SELECT (((idx DIV 32) DIV 2) * 16 + ((idx % 32) DIV 2))::UInt32 AS blk, 3422 sum(e.1) AS bs, sum(e.2) AS bc 3423 FROM ( SELECT number - 1 AS idx, g3[number] AS e FROM numbers(1, 1024) ) GROUP BY blk ) ) AS g4, 3424 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),256)( 3425 if(g4[i+1].2 > 0, (toNullable(g4[i+1].1 / g4[i+1].2), toUInt8(4), g4[i+1].2), (CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))), i) 3426 FROM ( SELECT number::UInt32 AS i FROM numbers(256) ) ) AS L4, 3427 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),1024)( 3428 if(g3[i+1].2 > 0, (toNullable(g3[i+1].1 / g3[i+1].2), toUInt8(3), g3[i+1].2), L4[((i DIV 32) DIV 2) * 16 + ((i % 32) DIV 2) + 1]), i) 3429 FROM ( SELECT number::UInt32 AS i FROM numbers(1024) ) ) AS L3, 3430 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),4096)( 3431 if(g2[i+1].2 > 0, (toNullable(g2[i+1].1 / g2[i+1].2), toUInt8(2), g2[i+1].2), L3[((i DIV 64) DIV 2) * 32 + ((i % 64) DIV 2) + 1]), i) 3432 FROM ( SELECT number::UInt32 AS i FROM numbers(4096) ) ) AS L2, 3433 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),16384)( 3434 if(g1[i+1].2 > 0, (toNullable(g1[i+1].1 / g1[i+1].2), toUInt8(1), g1[i+1].2), L2[((i DIV 128) DIV 2) * 64 + ((i % 128) DIV 2) + 1]), i) 3435 FROM ( SELECT number::UInt32 AS i FROM numbers(16384) ) ) AS L1, 3436 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),65536)( 3437 if(g0[i+1].2 > 0, (toNullable(g0[i+1].1 / g0[i+1].2), toUInt8(0), g0[i+1].2), L1[((i DIV 256) DIV 2) * 128 + ((i % 256) DIV 2) + 1]), i) 3438 FROM ( SELECT number::UInt32 AS i FROM numbers(65536) ) ) AS L0, 3439 ( SELECT groupArrayInsertAt(0::UInt32, 65536)(( t.1 AS v, ifNull(v, 0) AS val, greatest(0, least(1, (val + 30) / 70)) AS m, if(isNull(v), 0, toUInt32(round(255*m)) + bitShiftLeft(toUInt32(round(255*(1-abs(m-0.5)*2))), 8) + bitShiftLeft(toUInt32(round(255*(1-m))), 16) + bitShiftLeft(toUInt32(round([255,178,125,87,61,43,30,21,15][t.2 + 1] * least(1.0, log2(1 + t.3) / 7.6511))), 24)) ).4, i) 3440 FROM ( SELECT number::UInt32 AS i, L0[number + 1] AS t FROM numbers(65536) ) ) AS px 3441SELECT 3442 toUInt8(rgba % 256) AS red, toUInt8(rgba DIV 256 % 256) AS green, 3443 toUInt8(rgba DIV 65536 % 256) AS blue, toUInt8(rgba DIV 16777216 % 256) AS alpha
3444FROM ( SELECT number AS n, px[(number DIV 1024 DIV 4) * 256 + (number % 1024 DIV 4) + 1] AS rgba FROM numbers(1024 * 1024) ) 3445ORDER BY n`, 3446"Dew Point": `WITH 3447 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3448 tile_size * {x:UInt32} AS tile_x_begin, tile_size * ({x:UInt32} + 1) AS tile_x_end, 3449 tile_size * {y:UInt32} AS tile_y_begin, tile_size * ({y:UInt32} + 1) AS tile_y_end, 3450 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3451 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3452 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 65536)((s, c), cell) 3453 FROM ( 3454 SELECT (bitShiftRight(mercator_y - tile_y_begin, 24 - {z:UInt8}) * 256 3455 + bitShiftRight(mercator_x - tile_x_begin, 24 - {z:UInt8}))::UInt32 AS cell, 3456 ifNull(sumIf(dew_point, dew_point BETWEEN -100 AND 45), 0.0) AS s, countIf(dew_point BETWEEN -100 AND 45) AS c 3457 FROM {table:Identifier} WHERE in_tile GROUP BY cell ) ) AS g0, 3458 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 16384)((bs, bc), blk) 3459 FROM ( SELECT (((idx DIV 256) DIV 2) * 128 + ((idx % 256) DIV 2))::UInt32 AS blk, 3460 sum(e.1) AS bs, sum(e.2) AS bc 3461 FROM ( SELECT number - 1 AS idx, g0[number] AS e FROM numbers(1, 65536) ) GROUP BY blk ) ) AS g1, 3462 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 4096)((bs, bc), blk) 3463 FROM ( SELECT (((idx DIV 128) DIV 2) * 64 + ((idx % 128) DIV 2))::UInt32 AS blk, 3464 sum(e.1) AS bs, sum(e.2) AS bc 3465 FROM ( SELECT number - 1 AS idx, g1[number] AS e FROM numbers(1, 16384) ) GROUP BY blk ) ) AS g2, 3466 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 1024)((bs, bc), blk) 3467 FROM ( SELECT (((idx DIV 64) DIV 2) * 32 + ((idx % 64) DIV 2))::UInt32 AS blk, 3468 sum(e.1) AS bs, sum(e.2) AS bc 3469 FROM ( SELECT number - 1 AS idx, g2[number] AS e FROM numbers(1, 4096) ) GROUP BY blk ) ) AS g3, 3470 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 256)((bs, bc), blk) 3471 FROM ( SELECT (((idx DIV 32) DIV 2) * 16 + ((idx % 32) DIV 2))::UInt32 AS blk, 3472 sum(e.1) AS bs, sum(e.2) AS bc 3473 FROM ( SELECT number - 1 AS idx, g3[number] AS e FROM numbers(1, 1024) ) GROUP BY blk ) ) AS g4, 3474 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),256)( 3475 if(g4[i+1].2 > 0, (toNullable(g4[i+1].1 / g4[i+1].2), toUInt8(4), g4[i+1].2), (CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))), i) 3476 FROM ( SELECT number::UInt32 AS i FROM numbers(256) ) ) AS L4, 3477 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),1024)( 3478 if(g3[i+1].2 > 0, (toNullable(g3[i+1].1 / g3[i+1].2), toUInt8(3), g3[i+1].2), L4[((i DIV 32) DIV 2) * 16 + ((i % 32) DIV 2) + 1]), i) 3479 FROM ( SELECT number::UInt32 AS i FROM numbers(1024) ) ) AS L3, 3480 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),4096)( 3481 if(g2[i+1].2 > 0, (toNullable(g2[i+1].1 / g2[i+1].2), toUInt8(2), g2[i+1].2), L3[((i DIV 64) DIV 2) * 32 + ((i % 64) DIV 2) + 1]), i) 3482 FROM ( SELECT number::UInt32 AS i FROM numbers(4096) ) ) AS L2, 3483 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),16384)( 3484 if(g1[i+1].2 > 0, (toNullable(g1[i+1].1 / g1[i+1].2), toUInt8(1), g1[i+1].2), L2[((i DIV 128) DIV 2) * 64 + ((i % 128) DIV 2) + 1]), i) 3485 FROM ( SELECT number::UInt32 AS i FROM numbers(16384) ) ) AS L1, 3486 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),65536)( 3487 if(g0[i+1].2 > 0, (toNullable(g0[i+1].1 / g0[i+1].2), toUInt8(0), g0[i+1].2), L1[((i DIV 256) DIV 2) * 128 + ((i % 256) DIV 2) + 1]), i) 3488 FROM ( SELECT number::UInt32 AS i FROM numbers(65536) ) ) AS L0, 3489 ( SELECT groupArrayInsertAt(0::UInt32, 65536)(( t.1 AS v, ifNull(v, 0) AS val, greatest(0, least(1, (val + 30) / 60)) AS m, if(isNull(v), 0, toUInt32(round(255*m)) + bitShiftLeft(toUInt32(round(255*(1-abs(m-0.5)*2))), 8) + bitShiftLeft(toUInt32(round(255*(1-m))), 16) + bitShiftLeft(toUInt32(round([255,178,125,87,61,43,30,21,15][t.2 + 1] * least(1.0, log2(1 + t.3) / 7.6511))), 24)) ).4, i) 3490 FROM ( SELECT number::UInt32 AS i, L0[number + 1] AS t FROM numbers(65536) ) ) AS px 3491SELECT 3492 toUInt8(rgba % 256) AS red, toUInt8(rgba DIV 256 % 256) AS green, 3493 toUInt8(rgba DIV 65536 % 256) AS blue, toUInt8(rgba DIV 16777216 % 256) AS alpha
3494FROM ( SELECT number AS n, px[(number DIV 1024 DIV 4) * 256 + (number % 1024 DIV 4) + 1] AS rgba FROM numbers(1024 * 1024) ) 3495ORDER BY n`, 3496"Wind Speed": `WITH 3497 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3498 tile_size * {x:UInt32} AS tile_x_begin, tile_size * ({x:UInt32} + 1) AS tile_x_end, 3499 tile_size * {y:UInt32} AS tile_y_begin, tile_size * ({y:UInt32} + 1) AS tile_y_end, 3500 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3501 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3502 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 65536)((s, c), cell) 3503 FROM ( 3504 SELECT (bitShiftRight(mercator_y - tile_y_begin, 24 - {z:UInt8}) * 256 3505 + bitShiftRight(mercator_x - tile_x_begin, 24 - {z:UInt8}))::UInt32 AS cell, 3506 ifNull(sumIf(wind_speed, wind_speed BETWEEN 0 AND 120), 0.0) AS s, countIf(wind_speed BETWEEN 0 AND 120) AS c 3507 FROM {table:Identifier} WHERE in_tile GROUP BY cell ) ) AS g0, 3508 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 16384)((bs, bc), blk) 3509 FROM ( SELECT (((idx DIV 256) DIV 2) * 128 + ((idx % 256) DIV 2))::UInt32 AS blk, 3510 sum(e.1) AS bs, sum(e.2) AS bc 3511 FROM ( SELECT number - 1 AS idx, g0[number] AS e FROM numbers(1, 65536) ) GROUP BY blk ) ) AS g1, 3512 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 4096)((bs, bc), blk) 3513 FROM ( SELECT (((idx DIV 128) DIV 2) * 64 + ((idx % 128) DIV 2))::UInt32 AS blk, 3514 sum(e.1) AS bs, sum(e.2) AS bc 3515 FROM ( SELECT number - 1 AS idx, g1[number] AS e FROM numbers(1, 16384) ) GROUP BY blk ) ) AS g2, 3516 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 1024)((bs, bc), blk) 3517 FROM ( SELECT (((idx DIV 64) DIV 2) * 32 + ((idx % 64) DIV 2))::UInt32 AS blk, 3518 sum(e.1) AS bs, sum(e.2) AS bc 3519 FROM ( SELECT number - 1 AS idx, g2[number] AS e FROM numbers(1, 4096) ) GROUP BY blk ) ) AS g3, 3520 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 256)((bs, bc), blk) 3521 FROM ( SELECT (((idx DIV 32) DIV 2) * 16 + ((idx % 32) DIV 2))::UInt32 AS blk, 3522 sum(e.1) AS bs, sum(e.2) AS bc 3523 FROM ( SELECT number - 1 AS idx, g3[number] AS e FROM numbers(1, 1024) ) GROUP BY blk ) ) AS g4, 3524 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),256)( 3525 if(g4[i+1].2 > 0, (toNullable(g4[i+1].1 / g4[i+1].2), toUInt8(4), g4[i+1].2), (CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))), i) 3526 FROM ( SELECT number::UInt32 AS i FROM numbers(256) ) ) AS L4, 3527 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),1024)( 3528 if(g3[i+1].2 > 0, (toNullable(g3[i+1].1 / g3[i+1].2), toUInt8(3), g3[i+1].2), L4[((i DIV 32) DIV 2) * 16 + ((i % 32) DIV 2) + 1]), i) 3529 FROM ( SELECT number::UInt32 AS i FROM numbers(1024) ) ) AS L3, 3530 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),4096)( 3531 if(g2[i+1].2 > 0, (toNullable(g2[i+1].1 / g2[i+1].2), toUInt8(2), g2[i+1].2), L3[((i DIV 64) DIV 2) * 32 + ((i % 64) DIV 2) + 1]), i) 3532 FROM ( SELECT number::UInt32 AS i FROM numbers(4096) ) ) AS L2, 3533 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),16384)( 3534 if(g1[i+1].2 > 0, (toNullable(g1[i+1].1 / g1[i+1].2), toUInt8(1), g1[i+1].2), L2[((i DIV 128) DIV 2) * 64 + ((i % 128) DIV 2) + 1]), i) 3535 FROM ( SELECT number::UInt32 AS i FROM numbers(16384) ) ) AS L1, 3536 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),65536)( 3537 if(g0[i+1].2 > 0, (toNullable(g0[i+1].1 / g0[i+1].2), toUInt8(0), g0[i+1].2), L1[((i DIV 256) DIV 2) * 128 + ((i % 256) DIV 2) + 1]), i) 3538 FROM ( SELECT number::UInt32 AS i FROM numbers(65536) ) ) AS L0, 3539 ( SELECT groupArrayInsertAt(0::UInt32, 65536)(( t.1 AS v, ifNull(v, 0) AS val, least(1, val / 15) AS m, if(isNull(v), 0, toUInt32(round(255*m)) + bitShiftLeft(toUInt32(round(120*(1-m))), 8) + bitShiftLeft(toUInt32(round(255*(1-m))), 16) + bitShiftLeft(toUInt32(round([255,178,125,87,61,43,30,21,15][t.2 + 1] * least(1.0, log2(1 + t.3) / 7.6511))), 24)) ).4, i) 3540 FROM ( SELECT number::UInt32 AS i, L0[number + 1] AS t FROM numbers(65536) ) ) AS px 3541SELECT 3542 toUInt8(rgba % 256) AS red, toUInt8(rgba DIV 256 % 256) AS green, 3543 toUInt8(rgba DIV 65536 % 256) AS blue, toUInt8(rgba DIV 16777216 % 256) AS alpha
3544FROM ( SELECT number AS n, px[(number DIV 1024 DIV 4) * 256 + (number % 1024 DIV 4) + 1] AS rgba FROM numbers(1024 * 1024) ) 3545ORDER BY n`, 3546"Wind Gust": `WITH 3547 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3548 tile_size * {x:UInt32} AS tile_x_begin, tile_size * ({x:UInt32} + 1) AS tile_x_end, 3549 tile_size * {y:UInt32} AS tile_y_begin, tile_size * ({y:UInt32} + 1) AS tile_y_end, 3550 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3551 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3552 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 65536)((s, c), cell) 3553 FROM ( 3554 SELECT (bitShiftRight(mercator_y - tile_y_begin, 24 - {z:UInt8}) * 256 3555 + bitShiftRight(mercator_x - tile_x_begin, 24 - {z:UInt8}))::UInt32 AS cell, 3556 ifNull(sumIf(wind_gust, wind_gust BETWEEN 0 AND 160), 0.0) AS s, countIf(wind_gust BETWEEN 0 AND 160) AS c 3557 FROM {table:Identifier} WHERE in_tile GROUP BY cell ) ) AS g0, 3558 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 16384)((bs, bc), blk) 3559 FROM ( SELECT (((idx DIV 256) DIV 2) * 128 + ((idx % 256) DIV 2))::UInt32 AS blk, 3560 sum(e.1) AS bs, sum(e.2) AS bc 3561 FROM ( SELECT number - 1 AS idx, g0[number] AS e FROM numbers(1, 65536) ) GROUP BY blk ) ) AS g1, 3562 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 4096)((bs, bc), blk) 3563 FROM ( SELECT (((idx DIV 128) DIV 2) * 64 + ((idx % 128) DIV 2))::UInt32 AS blk, 3564 sum(e.1) AS bs, sum(e.2) AS bc 3565 FROM ( SELECT number - 1 AS idx, g1[number] AS e FROM numbers(1, 16384) ) GROUP BY blk ) ) AS g2, 3566 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 1024)((bs, bc), blk) 3567 FROM ( SELECT (((idx DIV 64) DIV 2) * 32 + ((idx % 64) DIV 2))::UInt32 AS blk, 3568 sum(e.1) AS bs, sum(e.2) AS bc 3569 FROM ( SELECT number - 1 AS idx, g2[number] AS e FROM numbers(1, 4096) ) GROUP BY blk ) ) AS g3, 3570 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 256)((bs, bc), blk) 3571 FROM ( SELECT (((idx DIV 32) DIV 2) * 16 + ((idx % 32) DIV 2))::UInt32 AS blk, 3572 sum(e.1) AS bs, sum(e.2) AS bc 3573 FROM ( SELECT number - 1 AS idx, g3[number] AS e FROM numbers(1, 1024) ) GROUP BY blk ) ) AS g4, 3574 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),256)( 3575 if(g4[i+1].2 > 0, (toNullable(g4[i+1].1 / g4[i+1].2), toUInt8(4), g4[i+1].2), (CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))), i) 3576 FROM ( SELECT number::UInt32 AS i FROM numbers(256) ) ) AS L4, 3577 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),1024)( 3578 if(g3[i+1].2 > 0, (toNullable(g3[i+1].1 / g3[i+1].2), toUInt8(3), g3[i+1].2), L4[((i DIV 32) DIV 2) * 16 + ((i % 32) DIV 2) + 1]), i) 3579 FROM ( SELECT number::UInt32 AS i FROM numbers(1024) ) ) AS L3, 3580 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),4096)( 3581 if(g2[i+1].2 > 0, (toNullable(g2[i+1].1 / g2[i+1].2), toUInt8(2), g2[i+1].2), L3[((i DIV 64) DIV 2) * 32 + ((i % 64) DIV 2) + 1]), i) 3582 FROM ( SELECT number::UInt32 AS i FROM numbers(4096) ) ) AS L2, 3583 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),16384)( 3584 if(g1[i+1].2 > 0, (toNullable(g1[i+1].1 / g1[i+1].2), toUInt8(1), g1[i+1].2), L2[((i DIV 128) DIV 2) * 64 + ((i % 128) DIV 2) + 1]), i) 3585 FROM ( SELECT number::UInt32 AS i FROM numbers(16384) ) ) AS L1, 3586 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),65536)( 3587 if(g0[i+1].2 > 0, (toNullable(g0[i+1].1 / g0[i+1].2), toUInt8(0), g0[i+1].2), L1[((i DIV 256) DIV 2) * 128 + ((i % 256) DIV 2) + 1]), i) 3588 FROM ( SELECT number::UInt32 AS i FROM numbers(65536) ) ) AS L0, 3589 ( SELECT groupArrayInsertAt(0::UInt32, 65536)(( t.1 AS v, ifNull(v, 0) AS val, least(1, val / 30) AS m, if(isNull(v), 0, toUInt32(round(255*m)) + bitShiftLeft(toUInt32(round(140*(1-m))), 8) + bitShiftLeft(toUInt32(round(60*(1-m))), 16) + bitShiftLeft(toUInt32(round([255,178,125,87,61,43,30,21,15][t.2 + 1] * least(1.0, log2(1 + t.3) / 7.6511))), 24)) ).4, i) 3590 FROM ( SELECT number::UInt32 AS i, L0[number + 1] AS t FROM numbers(65536) ) ) AS px 3591SELECT 3592 toUInt8(rgba % 256) AS red, toUInt8(rgba DIV 256 % 256) AS green, 3593 toUInt8(rgba DIV 65536 % 256) AS blue, toUInt8(rgba DIV 16777216 % 256) AS alpha
3594FROM ( SELECT number AS n, px[(number DIV 1024 DIV 4) * 256 + (number % 1024 DIV 4) + 1] AS rgba FROM numbers(1024 * 1024) ) 3595ORDER BY n`, 3596"Pressure": `WITH 3597 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3598 tile_size * {x:UInt32} AS tile_x_begin, tile_size * ({x:UInt32} + 1) AS tile_x_end, 3599 tile_size * {y:UInt32} AS tile_y_begin, tile_size * ({y:UInt32} + 1) AS tile_y_end, 3600 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3601 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3602 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 65536)((s, c), cell) 3603 FROM ( 3604 SELECT (bitShiftRight(mercator_y - tile_y_begin, 24 - {z:UInt8}) * 256 3605 + bitShiftRight(mercator_x - tile_x_begin, 24 - {z:UInt8}))::UInt32 AS cell, 3606 ifNull(sumIf(pressure, pressure BETWEEN 850 AND 1090), 0.0) AS s, countIf(pressure BETWEEN 850 AND 1090) AS c 3607 FROM {table:Identifier} WHERE in_tile GROUP BY cell ) ) AS g0, 3608 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 16384)((bs, bc), blk) 3609 FROM ( SELECT (((idx DIV 256) DIV 2) * 128 + ((idx % 256) DIV 2))::UInt32 AS blk, 3610 sum(e.1) AS bs, sum(e.2) AS bc 3611 FROM ( SELECT number - 1 AS idx, g0[number] AS e FROM numbers(1, 65536) ) GROUP BY blk ) ) AS g1, 3612 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 4096)((bs, bc), blk) 3613 FROM ( SELECT (((idx DIV 128) DIV 2) * 64 + ((idx % 128) DIV 2))::UInt32 AS blk, 3614 sum(e.1) AS bs, sum(e.2) AS bc 3615 FROM ( SELECT number - 1 AS idx, g1[number] AS e FROM numbers(1, 16384) ) GROUP BY blk ) ) AS g2, 3616 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 1024)((bs, bc), blk) 3617 FROM ( SELECT (((idx DIV 64) DIV 2) * 32 + ((idx % 64) DIV 2))::UInt32 AS blk, 3618 sum(e.1) AS bs, sum(e.2) AS bc 3619 FROM ( SELECT number - 1 AS idx, g2[number] AS e FROM numbers(1, 4096) ) GROUP BY blk ) ) AS g3, 3620 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 256)((bs, bc), blk) 3621 FROM ( SELECT (((idx DIV 32) DIV 2) * 16 + ((idx % 32) DIV 2))::UInt32 AS blk, 3622 sum(e.1) AS bs, sum(e.2) AS bc 3623 FROM ( SELECT number - 1 AS idx, g3[number] AS e FROM numbers(1, 1024) ) GROUP BY blk ) ) AS g4, 3624 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),256)( 3625 if(g4[i+1].2 > 0, (toNullable(g4[i+1].1 / g4[i+1].2), toUInt8(4), g4[i+1].2), (CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))), i) 3626 FROM ( SELECT number::UInt32 AS i FROM numbers(256) ) ) AS L4, 3627 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),1024)( 3628 if(g3[i+1].2 > 0, (toNullable(g3[i+1].1 / g3[i+1].2), toUInt8(3), g3[i+1].2), L4[((i DIV 32) DIV 2) * 16 + ((i % 32) DIV 2) + 1]), i) 3629 FROM ( SELECT number::UInt32 AS i FROM numbers(1024) ) ) AS L3, 3630 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),4096)( 3631 if(g2[i+1].2 > 0, (toNullable(g2[i+1].1 / g2[i+1].2), toUInt8(2), g2[i+1].2), L3[((i DIV 64) DIV 2) * 32 + ((i % 64) DIV 2) + 1]), i) 3632 FROM ( SELECT number::UInt32 AS i FROM numbers(4096) ) ) AS L2, 3633 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),16384)( 3634 if(g1[i+1].2 > 0, (toNullable(g1[i+1].1 / g1[i+1].2), toUInt8(1), g1[i+1].2), L2[((i DIV 128) DIV 2) * 64 + ((i % 128) DIV 2) + 1]), i) 3635 FROM ( SELECT number::UInt32 AS i FROM numbers(16384) ) ) AS L1, 3636 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),65536)( 3637 if(g0[i+1].2 > 0, (toNullable(g0[i+1].1 / g0[i+1].2), toUInt8(0), g0[i+1].2), L1[((i DIV 256) DIV 2) * 128 + ((i % 256) DIV 2) + 1]), i) 3638 FROM ( SELECT number::UInt32 AS i FROM numbers(65536) ) ) AS L0, 3639 ( SELECT groupArrayInsertAt(0::UInt32, 65536)(( t.1 AS v, ifNull(v, 0) AS val, greatest(0, least(1, (val - 985) / 55)) AS m, if(isNull(v), 0, toUInt32(round(255*m)) + bitShiftLeft(toUInt32(round(80+60*(1-abs(m-0.5)*2))), 8) + bitShiftLeft(toUInt32(round(255*(1-m))), 16) + bitShiftLeft(toUInt32(round([255,178,125,87,61,43,30,21,15][t.2 + 1] * least(1.0, log2(1 + t.3) / 7.6511))), 24)) ).4, i) 3640 FROM ( SELECT number::UInt32 AS i, L0[number + 1] AS t FROM numbers(65536) ) ) AS px 3641SELECT 3642 toUInt8(rgba % 256) AS red, toUInt8(rgba DIV 256 % 256) AS green, 3643 toUInt8(rgba DIV 65536 % 256) AS blue, toUInt8(rgba DIV 16777216 % 256) AS alpha
3644FROM ( SELECT number AS n, px[(number DIV 1024 DIV 4) * 256 + (number % 1024 DIV 4) + 1] AS rgba FROM numbers(1024 * 1024) ) 3645ORDER BY n`, 3646"Visibility": `WITH 3647 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3648 tile_size * {x:UInt32} AS tile_x_begin, tile_size * ({x:UInt32} + 1) AS tile_x_end, 3649 tile_size * {y:UInt32} AS tile_y_begin, tile_size * ({y:UInt32} + 1) AS tile_y_end, 3650 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3651 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3652 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 65536)((s, c), cell) 3653 FROM ( 3654 SELECT (bitShiftRight(mercator_y - tile_y_begin, 24 - {z:UInt8}) * 256 3655 + bitShiftRight(mercator_x - tile_x_begin, 24 - {z:UInt8}))::UInt32 AS cell, 3656 ifNull(sumIf(visibility, visibility BETWEEN 0 AND 100), 0.0) AS s, countIf(visibility BETWEEN 0 AND 100) AS c 3657 FROM {table:Identifier} WHERE in_tile GROUP BY cell ) ) AS g0, 3658 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 16384)((bs, bc), blk) 3659 FROM ( SELECT (((idx DIV 256) DIV 2) * 128 + ((idx % 256) DIV 2))::UInt32 AS blk, 3660 sum(e.1) AS bs, sum(e.2) AS bc 3661 FROM ( SELECT number - 1 AS idx, g0[number] AS e FROM numbers(1, 65536) ) GROUP BY blk ) ) AS g1, 3662 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 4096)((bs, bc), blk) 3663 FROM ( SELECT (((idx DIV 128) DIV 2) * 64 + ((idx % 128) DIV 2))::UInt32 AS blk, 3664 sum(e.1) AS bs, sum(e.2) AS bc 3665 FROM ( SELECT number - 1 AS idx, g1[number] AS e FROM numbers(1, 16384) ) GROUP BY blk ) ) AS g2, 3666 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 1024)((bs, bc), blk) 3667 FROM ( SELECT (((idx DIV 64) DIV 2) * 32 + ((idx % 64) DIV 2))::UInt32 AS blk, 3668 sum(e.1) AS bs, sum(e.2) AS bc 3669 FROM ( SELECT number - 1 AS idx, g2[number] AS e FROM numbers(1, 4096) ) GROUP BY blk ) ) AS g3, 3670 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 256)((bs, bc), blk) 3671 FROM ( SELECT (((idx DIV 32) DIV 2) * 16 + ((idx % 32) DIV 2))::UInt32 AS blk, 3672 sum(e.1) AS bs, sum(e.2) AS bc 3673 FROM ( SELECT number - 1 AS idx, g3[number] AS e FROM numbers(1, 1024) ) GROUP BY blk ) ) AS g4, 3674 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),256)( 3675 if(g4[i+1].2 > 0, (toNullable(g4[i+1].1 / g4[i+1].2), toUInt8(4), g4[i+1].2), (CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))), i) 3676 FROM ( SELECT number::UInt32 AS i FROM numbers(256) ) ) AS L4, 3677 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),1024)( 3678 if(g3[i+1].2 > 0, (toNullable(g3[i+1].1 / g3[i+1].2), toUInt8(3), g3[i+1].2), L4[((i DIV 32) DIV 2) * 16 + ((i % 32) DIV 2) + 1]), i) 3679 FROM ( SELECT number::UInt32 AS i FROM numbers(1024) ) ) AS L3, 3680 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),4096)( 3681 if(g2[i+1].2 > 0, (toNullable(g2[i+1].1 / g2[i+1].2), toUInt8(2), g2[i+1].2), L3[((i DIV 64) DIV 2) * 32 + ((i % 64) DIV 2) + 1]), i) 3682 FROM ( SELECT number::UInt32 AS i FROM numbers(4096) ) ) AS L2, 3683 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),16384)( 3684 if(g1[i+1].2 > 0, (toNullable(g1[i+1].1 / g1[i+1].2), toUInt8(1), g1[i+1].2), L2[((i DIV 128) DIV 2) * 64 + ((i % 128) DIV 2) + 1]), i) 3685 FROM ( SELECT number::UInt32 AS i FROM numbers(16384) ) ) AS L1, 3686 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),65536)( 3687 if(g0[i+1].2 > 0, (toNullable(g0[i+1].1 / g0[i+1].2), toUInt8(0), g0[i+1].2), L1[((i DIV 256) DIV 2) * 128 + ((i % 256) DIV 2) + 1]), i) 3688 FROM ( SELECT number::UInt32 AS i FROM numbers(65536) ) ) AS L0, 3689 ( SELECT groupArrayInsertAt(0::UInt32, 65536)(( t.1 AS v, ifNull(v, 0) AS val, least(1, val / 20) AS m, if(isNull(v), 0, toUInt32(round(20+230*(1-m))) + bitShiftLeft(toUInt32(round(120+40*m)), 8) + bitShiftLeft(toUInt32(round(40+195*m)), 16) + bitShiftLeft(toUInt32(round([255,178,125,87,61,43,30,21,15][t.2 + 1] * least(1.0, log2(1 + t.3) / 7.6511))), 24)) ).4, i) 3690 FROM ( SELECT number::UInt32 AS i, L0[number + 1] AS t FROM numbers(65536) ) ) AS px 3691SELECT 3692 toUInt8(rgba % 256) AS red, toUInt8(rgba DIV 256 % 256) AS green, 3693 toUInt8(rgba DIV 65536 % 256) AS blue, toUInt8(rgba DIV 16777216 % 256) AS alpha
3694FROM ( SELECT number AS n, px[(number DIV 1024 DIV 4) * 256 + (number % 1024 DIV 4) + 1] AS rgba FROM numbers(1024 * 1024) ) 3695ORDER BY n`, 3696"Cloud Cover": `WITH 3697 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3698 tile_size * {x:UInt32} AS tile_x_begin, tile_size * ({x:UInt32} + 1) AS tile_x_end, 3699 tile_size * {y:UInt32} AS tile_y_begin, tile_size * ({y:UInt32} + 1) AS tile_y_end, 3700 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3701 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3702 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 65536)((s, c), cell) 3703 FROM ( 3704 SELECT (bitShiftRight(mercator_y - tile_y_begin, 24 - {z:UInt8}) * 256 3705 + bitShiftRight(mercator_x - tile_x_begin, 24 - {z:UInt8}))::UInt32 AS cell, 3706 ifNull(sumIf(cloud_cover, cloud_cover BETWEEN 0 AND 100), 0.0) AS s, countIf(cloud_cover BETWEEN 0 AND 100) AS c 3707 FROM {table:Identifier} WHERE in_tile GROUP BY cell ) ) AS g0, 3708 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 16384)((bs, bc), blk) 3709 FROM ( SELECT (((idx DIV 256) DIV 2) * 128 + ((idx % 256) DIV 2))::UInt32 AS blk, 3710 sum(e.1) AS bs, sum(e.2) AS bc 3711 FROM ( SELECT number - 1 AS idx, g0[number] AS e FROM numbers(1, 65536) ) GROUP BY blk ) ) AS g1, 3712 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 4096)((bs, bc), blk) 3713 FROM ( SELECT (((idx DIV 128) DIV 2) * 64 + ((idx % 128) DIV 2))::UInt32 AS blk, 3714 sum(e.1) AS bs, sum(e.2) AS bc 3715 FROM ( SELECT number - 1 AS idx, g1[number] AS e FROM numbers(1, 16384) ) GROUP BY blk ) ) AS g2, 3716 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 1024)((bs, bc), blk) 3717 FROM ( SELECT (((idx DIV 64) DIV 2) * 32 + ((idx % 64) DIV 2))::UInt32 AS blk, 3718 sum(e.1) AS bs, sum(e.2) AS bc 3719 FROM ( SELECT number - 1 AS idx, g2[number] AS e FROM numbers(1, 4096) ) GROUP BY blk ) ) AS g3, 3720 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 256)((bs, bc), blk) 3721 FROM ( SELECT (((idx DIV 32) DIV 2) * 16 + ((idx % 32) DIV 2))::UInt32 AS blk, 3722 sum(e.1) AS bs, sum(e.2) AS bc 3723 FROM ( SELECT number - 1 AS idx, g3[number] AS e FROM numbers(1, 1024) ) GROUP BY blk ) ) AS g4, 3724 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),256)( 3725 if(g4[i+1].2 > 0, (toNullable(g4[i+1].1 / g4[i+1].2), toUInt8(4), g4[i+1].2), (CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))), i) 3726 FROM ( SELECT number::UInt32 AS i FROM numbers(256) ) ) AS L4, 3727 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),1024)( 3728 if(g3[i+1].2 > 0, (toNullable(g3[i+1].1 / g3[i+1].2), toUInt8(3), g3[i+1].2), L4[((i DIV 32) DIV 2) * 16 + ((i % 32) DIV 2) + 1]), i) 3729 FROM ( SELECT number::UInt32 AS i FROM numbers(1024) ) ) AS L3, 3730 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),4096)( 3731 if(g2[i+1].2 > 0, (toNullable(g2[i+1].1 / g2[i+1].2), toUInt8(2), g2[i+1].2), L3[((i DIV 64) DIV 2) * 32 + ((i % 64) DIV 2) + 1]), i) 3732 FROM ( SELECT number::UInt32 AS i FROM numbers(4096) ) ) AS L2, 3733 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),16384)( 3734 if(g1[i+1].2 > 0, (toNullable(g1[i+1].1 / g1[i+1].2), toUInt8(1), g1[i+1].2), L2[((i DIV 128) DIV 2) * 64 + ((i % 128) DIV 2) + 1]), i) 3735 FROM ( SELECT number::UInt32 AS i FROM numbers(16384) ) ) AS L1, 3736 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),65536)( 3737 if(g0[i+1].2 > 0, (toNullable(g0[i+1].1 / g0[i+1].2), toUInt8(0), g0[i+1].2), L1[((i DIV 256) DIV 2) * 128 + ((i % 256) DIV 2) + 1]), i) 3738 FROM ( SELECT number::UInt32 AS i FROM numbers(65536) ) ) AS L0, 3739 ( SELECT groupArrayInsertAt(0::UInt32, 65536)(( t.1 AS v, ifNull(v, 0) AS val, greatest(0, least(1, val / 100)) AS m, if(isNull(v), 0, toUInt32(round(70+185*m)) + bitShiftLeft(toUInt32(round(70+185*m)), 8) + bitShiftLeft(toUInt32(round(80+175*m)), 16) + bitShiftLeft(toUInt32(round([255,178,125,87,61,43,30,21,15][t.2 + 1] * least(1.0, log2(1 + t.3) / 7.6511))), 24)) ).4, i) 3740 FROM ( SELECT number::UInt32 AS i, L0[number + 1] AS t FROM numbers(65536) ) ) AS px 3741SELECT 3742 toUInt8(rgba % 256) AS red, toUInt8(rgba DIV 256 % 256) AS green, 3743 toUInt8(rgba DIV 65536 % 256) AS blue, toUInt8(rgba DIV 16777216 % 256) AS alpha
3744FROM ( SELECT number AS n, px[(number DIV 1024 DIV 4) * 256 + (number % 1024 DIV 4) + 1] AS rgba FROM numbers(1024 * 1024) ) 3745ORDER BY n`, 3746"Precipitation": `WITH 3747 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3748 tile_size * {x:UInt32} AS tile_x_begin, tile_size * ({x:UInt32} + 1) AS tile_x_end, 3749 tile_size * {y:UInt32} AS tile_y_begin, tile_size * ({y:UInt32} + 1) AS tile_y_end, 3750 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3751 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3752 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 65536)((s, c), cell) 3753 FROM ( 3754 SELECT (bitShiftRight(mercator_y - tile_y_begin, 24 - {z:UInt8}) * 256 3755 + bitShiftRight(mercator_x - tile_x_begin, 24 - {z:UInt8}))::UInt32 AS cell, 3756 ifNull(sumIf(precipitation, precipitation BETWEEN 0 AND 50), 0.0) AS s, countIf(precipitation BETWEEN 0 AND 50) AS c 3757 FROM {table:Identifier} WHERE in_tile GROUP BY cell ) ) AS g0, 3758 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 16384)((bs, bc), blk) 3759 FROM ( SELECT (((idx DIV 256) DIV 2) * 128 + ((idx % 256) DIV 2))::UInt32 AS blk, 3760 sum(e.1) AS bs, sum(e.2) AS bc 3761 FROM ( SELECT number - 1 AS idx, g0[number] AS e FROM numbers(1, 65536) ) GROUP BY blk ) ) AS g1, 3762 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 4096)((bs, bc), blk) 3763 FROM ( SELECT (((idx DIV 128) DIV 2) * 64 + ((idx % 128) DIV 2))::UInt32 AS blk, 3764 sum(e.1) AS bs, sum(e.2) AS bc 3765 FROM ( SELECT number - 1 AS idx, g1[number] AS e FROM numbers(1, 16384) ) GROUP BY blk ) ) AS g2, 3766 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 1024)((bs, bc), blk) 3767 FROM ( SELECT (((idx DIV 64) DIV 2) * 32 + ((idx % 64) DIV 2))::UInt32 AS blk, 3768 sum(e.1) AS bs, sum(e.2) AS bc 3769 FROM ( SELECT number - 1 AS idx, g2[number] AS e FROM numbers(1, 4096) ) GROUP BY blk ) ) AS g3, 3770 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 256)((bs, bc), blk) 3771 FROM ( SELECT (((idx DIV 32) DIV 2) * 16 + ((idx % 32) DIV 2))::UInt32 AS blk, 3772 sum(e.1) AS bs, sum(e.2) AS bc 3773 FROM ( SELECT number - 1 AS idx, g3[number] AS e FROM numbers(1, 1024) ) GROUP BY blk ) ) AS g4, 3774 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),256)( 3775 if(g4[i+1].2 > 0, (toNullable(g4[i+1].1 / g4[i+1].2), toUInt8(4), g4[i+1].2), (CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))), i) 3776 FROM ( SELECT number::UInt32 AS i FROM numbers(256) ) ) AS L4, 3777 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),1024)( 3778 if(g3[i+1].2 > 0, (toNullable(g3[i+1].1 / g3[i+1].2), toUInt8(3), g3[i+1].2), L4[((i DIV 32) DIV 2) * 16 + ((i % 32) DIV 2) + 1]), i) 3779 FROM ( SELECT number::UInt32 AS i FROM numbers(1024) ) ) AS L3, 3780 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),4096)( 3781 if(g2[i+1].2 > 0, (toNullable(g2[i+1].1 / g2[i+1].2), toUInt8(2), g2[i+1].2), L3[((i DIV 64) DIV 2) * 32 + ((i % 64) DIV 2) + 1]), i) 3782 FROM ( SELECT number::UInt32 AS i FROM numbers(4096) ) ) AS L2, 3783 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),16384)( 3784 if(g1[i+1].2 > 0, (toNullable(g1[i+1].1 / g1[i+1].2), toUInt8(1), g1[i+1].2), L2[((i DIV 128) DIV 2) * 64 + ((i % 128) DIV 2) + 1]), i) 3785 FROM ( SELECT number::UInt32 AS i FROM numbers(16384) ) ) AS L1, 3786 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),65536)( 3787 if(g0[i+1].2 > 0, (toNullable(g0[i+1].1 / g0[i+1].2), toUInt8(0), g0[i+1].2), L1[((i DIV 256) DIV 2) * 128 + ((i % 256) DIV 2) + 1]), i) 3788 FROM ( SELECT number::UInt32 AS i FROM numbers(65536) ) ) AS L0, 3789 ( SELECT groupArrayInsertAt(0::UInt32, 65536)(( t.1 AS v, ifNull(v, 0) AS val, least(1, val / 2) AS m, if(isNull(v), 0, toUInt32(round(40*(1-m))) + bitShiftLeft(toUInt32(round(40+120*(1-m))), 8) + bitShiftLeft(toUInt32(round(120+135*m)), 16) + bitShiftLeft(toUInt32(round([255,178,125,87,61,43,30,21,15][t.2 + 1] * least(1.0, val / 1.0) * least(1.0, log2(1 + t.3) / 7.6511))), 24)) ).4, i) 3790 FROM ( SELECT number::UInt32 AS i, L0[number + 1] AS t FROM numbers(65536) ) ) AS px 3791SELECT 3792 toUInt8(rgba % 256) AS red, toUInt8(rgba DIV 256 % 256) AS green, 3793 toUInt8(rgba DIV 65536 % 256) AS blue, toUInt8(rgba DIV 16777216 % 256) AS alpha
3794FROM ( SELECT number AS n, px[(number DIV 1024 DIV 4) * 256 + (number % 1024 DIV 4) + 1] AS rgba FROM numbers(1024 * 1024) ) 3795ORDER BY n`, 3796"Snow Depth": `WITH 3797 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3798 tile_size * {x:UInt32} AS tile_x_begin, tile_size * ({x:UInt32} + 1) AS tile_x_end, 3799 tile_size * {y:UInt32} AS tile_y_begin, tile_size * ({y:UInt32} + 1) AS tile_y_end, 3800 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3801 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3802 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 65536)((s, c), cell) 3803 FROM ( 3804 SELECT (bitShiftRight(mercator_y - tile_y_begin, 24 - {z:UInt8}) * 256 3805 + bitShiftRight(mercator_x - tile_x_begin, 24 - {z:UInt8}))::UInt32 AS cell, 3806 ifNull(sumIf(snow_depth, snow_depth BETWEEN 0 AND 12000 AND (temperature IS NULL OR temperature < 20)), 0.0) AS s, countIf(snow_depth BETWEEN 0 AND 12000 AND (temperature IS NULL OR temperature < 20)) AS c 3807 FROM {table:Identifier} WHERE in_tile GROUP BY cell ) ) AS g0, 3808 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 16384)((bs, bc), blk) 3809 FROM ( SELECT (((idx DIV 256) DIV 2) * 128 + ((idx % 256) DIV 2))::UInt32 AS blk, 3810 sum(e.1) AS bs, sum(e.2) AS bc 3811 FROM ( SELECT number - 1 AS idx, g0[number] AS e FROM numbers(1, 65536) ) GROUP BY blk ) ) AS g1, 3812 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 4096)((bs, bc), blk) 3813 FROM ( SELECT (((idx DIV 128) DIV 2) * 64 + ((idx % 128) DIV 2))::UInt32 AS blk, 3814 sum(e.1) AS bs, sum(e.2) AS bc 3815 FROM ( SELECT number - 1 AS idx, g1[number] AS e FROM numbers(1, 16384) ) GROUP BY blk ) ) AS g2, 3816 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 1024)((bs, bc), blk) 3817 FROM ( SELECT (((idx DIV 64) DIV 2) * 32 + ((idx % 64) DIV 2))::UInt32 AS blk, 3818 sum(e.1) AS bs, sum(e.2) AS bc 3819 FROM ( SELECT number - 1 AS idx, g2[number] AS e FROM numbers(1, 4096) ) GROUP BY blk ) ) AS g3, 3820 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 256)((bs, bc), blk) 3821 FROM ( SELECT (((idx DIV 32) DIV 2) * 16 + ((idx % 32) DIV 2))::UInt32 AS blk, 3822 sum(e.1) AS bs, sum(e.2) AS bc 3823 FROM ( SELECT number - 1 AS idx, g3[number] AS e FROM numbers(1, 1024) ) GROUP BY blk ) ) AS g4, 3824 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),256)( 3825 if(g4[i+1].2 > 0, (toNullable(g4[i+1].1 / g4[i+1].2), toUInt8(4), g4[i+1].2), (CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))), i) 3826 FROM ( SELECT number::UInt32 AS i FROM numbers(256) ) ) AS L4, 3827 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),1024)( 3828 if(g3[i+1].2 > 0, (toNullable(g3[i+1].1 / g3[i+1].2), toUInt8(3), g3[i+1].2), L4[((i DIV 32) DIV 2) * 16 + ((i % 32) DIV 2) + 1]), i) 3829 FROM ( SELECT number::UInt32 AS i FROM numbers(1024) ) ) AS L3, 3830 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),4096)( 3831 if(g2[i+1].2 > 0, (toNullable(g2[i+1].1 / g2[i+1].2), toUInt8(2), g2[i+1].2), L3[((i DIV 64) DIV 2) * 32 + ((i % 64) DIV 2) + 1]), i) 3832 FROM ( SELECT number::UInt32 AS i FROM numbers(4096) ) ) AS L2, 3833 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),16384)( 3834 if(g1[i+1].2 > 0, (toNullable(g1[i+1].1 / g1[i+1].2), toUInt8(1), g1[i+1].2), L2[((i DIV 128) DIV 2) * 64 + ((i % 128) DIV 2) + 1]), i) 3835 FROM ( SELECT number::UInt32 AS i FROM numbers(16384) ) ) AS L1, 3836 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),65536)( 3837 if(g0[i+1].2 > 0, (toNullable(g0[i+1].1 / g0[i+1].2), toUInt8(0), g0[i+1].2), L1[((i DIV 256) DIV 2) * 128 + ((i % 256) DIV 2) + 1]), i) 3838 FROM ( SELECT number::UInt32 AS i FROM numbers(65536) ) ) AS L0, 3839 ( SELECT groupArrayInsertAt(0::UInt32, 65536)(( t.1 AS v, ifNull(v, 0) AS val, least(1, val / 50) AS m, if(isNull(v), 0, toUInt32(round(205-55*m)) + bitShiftLeft(toUInt32(round(224-24*m)), 8) + bitShiftLeft(toUInt32(round(246-6*m)), 16) + bitShiftLeft(toUInt32(round([255,178,125,87,61,43,30,21,15][t.2 + 1] * least(1.0, val / 15.0) * least(1.0, log2(1 + t.3) / 7.6511))), 24)) ).4, i) 3840 FROM ( SELECT number::UInt32 AS i, L0[number + 1] AS t FROM numbers(65536) ) ) AS px 3841SELECT 3842 toUInt8(rgba % 256) AS red, toUInt8(rgba DIV 256 % 256) AS green, 3843 toUInt8(rgba DIV 65536 % 256) AS blue, toUInt8(rgba DIV 16777216 % 256) AS alpha
3844FROM ( SELECT number AS n, px[(number DIV 1024 DIV 4) * 256 + (number % 1024 DIV 4) + 1] AS rgba FROM numbers(1024 * 1024) ) 3845ORDER BY n`, 3846"Num Observations": `WITH 3847 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3848 tile_size * {x:UInt32} AS tile_x_begin, tile_size * ({x:UInt32} + 1) AS tile_x_end, 3849 tile_size * {y:UInt32} AS tile_y_begin, tile_size * ({y:UInt32} + 1) AS tile_y_end, 3850 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3851 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3852 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 65536)((s, c), cell) 3853 FROM ( 3854 SELECT (bitShiftRight(mercator_y - tile_y_begin, 24 - {z:UInt8}) * 256 3855 + bitShiftRight(mercator_x - tile_x_begin, 24 - {z:UInt8}))::UInt32 AS cell, 3856 toFloat64(count()) AS s, toUInt64(1) AS c 3857 FROM {table:Identifier} WHERE in_tile GROUP BY cell ) ) AS g0, 3858 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 16384)((bs, bc), blk) 3859 FROM ( SELECT (((idx DIV 256) DIV 2) * 128 + ((idx % 256) DIV 2))::UInt32 AS blk, 3860 sum(e.1) AS bs, sum(e.2) AS bc 3861 FROM ( SELECT number - 1 AS idx, g0[number] AS e FROM numbers(1, 65536) ) GROUP BY blk ) ) AS g1, 3862 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 4096)((bs, bc), blk) 3863 FROM ( SELECT (((idx DIV 128) DIV 2) * 64 + ((idx % 128) DIV 2))::UInt32 AS blk, 3864 sum(e.1) AS bs, sum(e.2) AS bc 3865 FROM ( SELECT number - 1 AS idx, g1[number] AS e FROM numbers(1, 16384) ) GROUP BY blk ) ) AS g2, 3866 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 1024)((bs, bc), blk) 3867 FROM ( SELECT (((idx DIV 64) DIV 2) * 32 + ((idx % 64) DIV 2))::UInt32 AS blk, 3868 sum(e.1) AS bs, sum(e.2) AS bc 3869 FROM ( SELECT number - 1 AS idx, g2[number] AS e FROM numbers(1, 4096) ) GROUP BY blk ) ) AS g3, 3870 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 256)((bs, bc), blk) 3871 FROM ( SELECT (((idx DIV 32) DIV 2) * 16 + ((idx % 32) DIV 2))::UInt32 AS blk, 3872 sum(e.1) AS bs, sum(e.2) AS bc 3873 FROM ( SELECT number - 1 AS idx, g3[number] AS e FROM numbers(1, 1024) ) GROUP BY blk ) ) AS g4, 3874 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),256)( 3875 if(g4[i+1].2 > 0, (toNullable(g4[i+1].1 / g4[i+1].2), toUInt8(4), g4[i+1].2), (CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))), i) 3876 FROM ( SELECT number::UInt32 AS i FROM numbers(256) ) ) AS L4, 3877 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),1024)( 3878 if(g3[i+1].2 > 0, (toNullable(g3[i+1].1 / g3[i+1].2), toUInt8(3), g3[i+1].2), L4[((i DIV 32) DIV 2) * 16 + ((i % 32) DIV 2) + 1]), i) 3879 FROM ( SELECT number::UInt32 AS i FROM numbers(1024) ) ) AS L3, 3880 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),4096)( 3881 if(g2[i+1].2 > 0, (toNullable(g2[i+1].1 / g2[i+1].2), toUInt8(2), g2[i+1].2), L3[((i DIV 64) DIV 2) * 32 + ((i % 64) DIV 2) + 1]), i) 3882 FROM ( SELECT number::UInt32 AS i FROM numbers(4096) ) ) AS L2, 3883 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),16384)( 3884 if(g1[i+1].2 > 0, (toNullable(g1[i+1].1 / g1[i+1].2), toUInt8(1), g1[i+1].2), L2[((i DIV 128) DIV 2) * 64 + ((i % 128) DIV 2) + 1]), i) 3885 FROM ( SELECT number::UInt32 AS i FROM numbers(16384) ) ) AS L1, 3886 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),65536)( 3887 if(g0[i+1].2 > 0, (toNullable(g0[i+1].1 / g0[i+1].2), toUInt8(0), g0[i+1].2), L1[((i DIV 256) DIV 2) * 128 + ((i % 256) DIV 2) + 1]), i) 3888 FROM ( SELECT number::UInt32 AS i FROM numbers(65536) ) ) AS L0, 3889 ( SELECT groupArrayInsertAt(0::UInt32, 65536)(( t.1 AS v, ifNull(v, 0) AS val, least(1, log2(1 + val) / 17) AS m, if(isNull(v), 0, toUInt32(round(60+195*m)) + bitShiftLeft(toUInt32(round(30+200*m*m)), 8) + bitShiftLeft(toUInt32(round(130*(1-m))), 16) + bitShiftLeft(toUInt32(round([255,178,125,87,61,43,30,21,15][t.2 + 1] * m)), 24)) ).4, i) 3890 FROM ( SELECT number::UInt32 AS i, L0[number + 1] AS t FROM numbers(65536) ) ) AS px 3891SELECT 3892 toUInt8(rgba % 256) AS red, toUInt8(rgba DIV 256 % 256) AS green, 3893 toUInt8(rgba DIV 65536 % 256) AS blue, toUInt8(rgba DIV 16777216 % 256) AS alpha
3894FROM ( SELECT number AS n, px[(number DIV 1024 DIV 4) * 256 + (number % 1024 DIV 4) + 1] AS rgba FROM numbers(1024 * 1024) ) 3895ORDER BY n`, 3896"Relative Humidity": `WITH 3897 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3898 tile_size * {x:UInt32} AS tile_x_begin, tile_size * ({x:UInt32} + 1) AS tile_x_end, 3899 tile_size * {y:UInt32} AS tile_y_begin, tile_size * ({y:UInt32} + 1) AS tile_y_end, 3900 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3901 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3902 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 65536)((s, c), cell) 3903 FROM ( 3904 SELECT (bitShiftRight(mercator_y - tile_y_begin, 24 - {z:UInt8}) * 256 3905 + bitShiftRight(mercator_x - tile_x_begin, 24 - {z:UInt8}))::UInt32 AS cell, 3906 ifNull(sumIf(relative_humidity, relative_humidity BETWEEN 0 AND 100), 0.0) AS s, countIf(relative_humidity BETWEEN 0 AND 100) AS c 3907 FROM {table:Identifier} WHERE in_tile GROUP BY cell ) ) AS g0, 3908 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 16384)((bs, bc), blk) 3909 FROM ( SELECT (((idx DIV 256) DIV 2) * 128 + ((idx % 256) DIV 2))::UInt32 AS blk, 3910 sum(e.1) AS bs, sum(e.2) AS bc 3911 FROM ( SELECT number - 1 AS idx, g0[number] AS e FROM numbers(1, 65536) ) GROUP BY blk ) ) AS g1, 3912 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 4096)((bs, bc), blk) 3913 FROM ( SELECT (((idx DIV 128) DIV 2) * 64 + ((idx % 128) DIV 2))::UInt32 AS blk, 3914 sum(e.1) AS bs, sum(e.2) AS bc 3915 FROM ( SELECT number - 1 AS idx, g1[number] AS e FROM numbers(1, 16384) ) GROUP BY blk ) ) AS g2, 3916 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 1024)((bs, bc), blk) 3917 FROM ( SELECT (((idx DIV 64) DIV 2) * 32 + ((idx % 64) DIV 2))::UInt32 AS blk, 3918 sum(e.1) AS bs, sum(e.2) AS bc 3919 FROM ( SELECT number - 1 AS idx, g2[number] AS e FROM numbers(1, 4096) ) GROUP BY blk ) ) AS g3, 3920 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 256)((bs, bc), blk) 3921 FROM ( SELECT (((idx DIV 32) DIV 2) * 16 + ((idx % 32) DIV 2))::UInt32 AS blk, 3922 sum(e.1) AS bs, sum(e.2) AS bc 3923 FROM ( SELECT number - 1 AS idx, g3[number] AS e FROM numbers(1, 1024) ) GROUP BY blk ) ) AS g4, 3924 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),256)( 3925 if(g4[i+1].2 > 0, (toNullable(g4[i+1].1 / g4[i+1].2), toUInt8(4), g4[i+1].2), (CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))), i) 3926 FROM ( SELECT number::UInt32 AS i FROM numbers(256) ) ) AS L4, 3927 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),1024)( 3928 if(g3[i+1].2 > 0, (toNullable(g3[i+1].1 / g3[i+1].2), toUInt8(3), g3[i+1].2), L4[((i DIV 32) DIV 2) * 16 + ((i % 32) DIV 2) + 1]), i) 3929 FROM ( SELECT number::UInt32 AS i FROM numbers(1024) ) ) AS L3, 3930 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),4096)( 3931 if(g2[i+1].2 > 0, (toNullable(g2[i+1].1 / g2[i+1].2), toUInt8(2), g2[i+1].2), L3[((i DIV 64) DIV 2) * 32 + ((i % 64) DIV 2) + 1]), i) 3932 FROM ( SELECT number::UInt32 AS i FROM numbers(4096) ) ) AS L2, 3933 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),16384)( 3934 if(g1[i+1].2 > 0, (toNullable(g1[i+1].1 / g1[i+1].2), toUInt8(1), g1[i+1].2), L2[((i DIV 128) DIV 2) * 64 + ((i % 128) DIV 2) + 1]), i) 3935 FROM ( SELECT number::UInt32 AS i FROM numbers(16384) ) ) AS L1, 3936 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),65536)( 3937 if(g0[i+1].2 > 0, (toNullable(g0[i+1].1 / g0[i+1].2), toUInt8(0), g0[i+1].2), L1[((i DIV 256) DIV 2) * 128 + ((i % 256) DIV 2) + 1]), i) 3938 FROM ( SELECT number::UInt32 AS i FROM numbers(65536) ) ) AS L0, 3939 ( SELECT groupArrayInsertAt(0::UInt32, 65536)(( t.1 AS v, ifNull(v, 0) AS val, greatest(0, least(1, val / 100)) AS m, if(isNull(v), 0, toUInt32(round(210-180*m)) + bitShiftLeft(toUInt32(round(170-10*m)), 8) + bitShiftLeft(toUInt32(round(90+150*m)), 16) + bitShiftLeft(toUInt32(round([255,178,125,87,61,43,30,21,15][t.2 + 1] * least(1.0, log2(1 + t.3) / 7.6511))), 24)) ).4, i) 3940 FROM ( SELECT number::UInt32 AS i, L0[number + 1] AS t FROM numbers(65536) ) ) AS px 3941SELECT 3942 toUInt8(rgba % 256) AS red, toUInt8(rgba DIV 256 % 256) AS green, 3943 toUInt8(rgba DIV 65536 % 256) AS blue, toUInt8(rgba DIV 16777216 % 256) AS alpha
3944FROM ( SELECT number AS n, px[(number DIV 1024 DIV 4) * 256 + (number % 1024 DIV 4) + 1] AS rgba FROM numbers(1024 * 1024) ) 3945ORDER BY n`, 3946"Wet Bulb": `WITH 3947 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 3948 tile_size * {x:UInt32} AS tile_x_begin, tile_size * ({x:UInt32} + 1) AS tile_x_end, 3949 tile_size * {y:UInt32} AS tile_y_begin, tile_size * ({y:UInt32} + 1) AS tile_y_end, 3950 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 3951 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 3952 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 65536)((s, c), cell) 3953 FROM ( 3954 SELECT (bitShiftRight(mercator_y - tile_y_begin, 24 - {z:UInt8}) * 256 3955 + bitShiftRight(mercator_x - tile_x_begin, 24 - {z:UInt8}))::UInt32 AS cell, 3956 ifNull(sumIf(wet_bulb, wet_bulb BETWEEN -95 AND 45), 0.0) AS s, countIf(wet_bulb BETWEEN -95 AND 45) AS c 3957 FROM {table:Identifier} WHERE in_tile GROUP BY cell ) ) AS g0, 3958 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 16384)((bs, bc), blk) 3959 FROM ( SELECT (((idx DIV 256) DIV 2) * 128 + ((idx % 256) DIV 2))::UInt32 AS blk, 3960 sum(e.1) AS bs, sum(e.2) AS bc 3961 FROM ( SELECT number - 1 AS idx, g0[number] AS e FROM numbers(1, 65536) ) GROUP BY blk ) ) AS g1, 3962 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 4096)((bs, bc), blk) 3963 FROM ( SELECT (((idx DIV 128) DIV 2) * 64 + ((idx % 128) DIV 2))::UInt32 AS blk, 3964 sum(e.1) AS bs, sum(e.2) AS bc 3965 FROM ( SELECT number - 1 AS idx, g1[number] AS e FROM numbers(1, 16384) ) GROUP BY blk ) ) AS g2, 3966 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 1024)((bs, bc), blk) 3967 FROM ( SELECT (((idx DIV 64) DIV 2) * 32 + ((idx % 64) DIV 2))::UInt32 AS blk, 3968 sum(e.1) AS bs, sum(e.2) AS bc 3969 FROM ( SELECT number - 1 AS idx, g2[number] AS e FROM numbers(1, 4096) ) GROUP BY blk ) ) AS g3, 3970 ( SELECT groupArrayInsertAt((0.0, 0)::Tuple(Float64, UInt64), 256)((bs, bc), blk) 3971 FROM ( SELECT (((idx DIV 32) DIV 2) * 16 + ((idx % 32) DIV 2))::UInt32 AS blk, 3972 sum(e.1) AS bs, sum(e.2) AS bc 3973 FROM ( SELECT number - 1 AS idx, g3[number] AS e FROM numbers(1, 1024) ) GROUP BY blk ) ) AS g4, 3974 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),256)( 3975 if(g4[i+1].2 > 0, (toNullable(g4[i+1].1 / g4[i+1].2), toUInt8(4), g4[i+1].2), (CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))), i) 3976 FROM ( SELECT number::UInt32 AS i FROM numbers(256) ) ) AS L4, 3977 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),1024)( 3978 if(g3[i+1].2 > 0, (toNullable(g3[i+1].1 / g3[i+1].2), toUInt8(3), g3[i+1].2), L4[((i DIV 32) DIV 2) * 16 + ((i % 32) DIV 2) + 1]), i) 3979 FROM ( SELECT number::UInt32 AS i FROM numbers(1024) ) ) AS L3, 3980 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),4096)( 3981 if(g2[i+1].2 > 0, (toNullable(g2[i+1].1 / g2[i+1].2), toUInt8(2), g2[i+1].2), L3[((i DIV 64) DIV 2) * 32 + ((i % 64) DIV 2) + 1]), i) 3982 FROM ( SELECT number::UInt32 AS i FROM numbers(4096) ) ) AS L2, 3983 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),16384)( 3984 if(g1[i+1].2 > 0, (toNullable(g1[i+1].1 / g1[i+1].2), toUInt8(1), g1[i+1].2), L2[((i DIV 128) DIV 2) * 64 + ((i % 128) DIV 2) + 1]), i) 3985 FROM ( SELECT number::UInt32 AS i FROM numbers(16384) ) ) AS L1, 3986 ( SELECT groupArrayInsertAt((CAST(NULL AS Nullable(Float64)), toUInt8(9), toUInt64(0))::Tuple(Nullable(Float64), UInt8, UInt64),65536)( 3987 if(g0[i+1].2 > 0, (toNullable(g0[i+1].1 / g0[i+1].2), toUInt8(0), g0[i+1].2), L1[((i DIV 256) DIV 2) * 128 + ((i % 256) DIV 2) + 1]), i) 3988 FROM ( SELECT number::UInt32 AS i FROM numbers(65536) ) ) AS L0, 3989 ( SELECT groupArrayInsertAt(0::UInt32, 65536)(( t.1 AS v, ifNull(v, 0) AS val, greatest(0, least(1, (val + 30) / 70)) AS m, if(isNull(v), 0, toUInt32(round(255*m)) + bitShiftLeft(toUInt32(round(255*(1-abs(m-0.5)*2))), 8) + bitShiftLeft(toUInt32(round(255*(1-m))), 16) + bitShiftLeft(toUInt32(round([255,178,125,87,61,43,30,21,15][t.2 + 1] * least(1.0, log2(1 + t.3) / 7.6511))), 24)) ).4, i) 3990 FROM ( SELECT number::UInt32 AS i, L0[number + 1] AS t FROM numbers(65536) ) ) AS px 3991SELECT 3992 toUInt8(rgba % 256) AS red, toUInt8(rgba DIV 256 % 256) AS green, 3993 toUInt8(rgba DIV 65536 % 256) AS blue, toUInt8(rgba DIV 16777216 % 256) AS alpha
3994FROM ( SELECT number AS n, px[(number DIV 1024 DIV 4) * 256 + (number % 1024 DIV 4) + 1] AS rgba FROM numbers(1024 * 1024) ) 3995ORDER BY n` 3996 } 3997 }, 3998 3999 "Taxi": { 4000 notice: "NYC TLC trip records (coordinate-bearing archive), public domain", 4001 bounds: [[40.55, -74.10], [40.90, -73.70]], 4002 endpoints: [ 4003 { 4004 name: "Cloud (Real-Time)", 4005 urls: [ 4006 { 4007 url: "https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 4008 sticky: "https://{hash}.sticky.kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 4009 } 4010 ] 4011 }, 4012 ], 4013 levels: [ 4014 { table: 'taxi_mercator', sample: 1, priority: 1 }, 4015 ], 4016 time: { column: 'pickup_datetime' }, 4017 report_total: { 4018 query: (condition => ` 4019 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 4020 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 4021 SELECT count() AS trips, round(avg(trip_distance),2) AS dist, round(avgIf(fare_amount, fare_amount>0),2) AS fare, min(pickup_datetime) AS first, max(pickup_datetime) AS last 4022 FROM {table:Identifier} WHERE ${condition}`), 4023 content: (json => { let row = json.data[0]; let text = `Total ${Number(row.trips).toLocaleString()} trips. Avg ${row.dist} mi, $${row.fare}.`; if (row.trips>0) text += ` ${row.first} â ${row.last}.`; if (json.statistics.rows_read>1) text += ` Processed ${Number(json.statistics.rows_read).toLocaleString()} rows.`; return text; }), 4024 }, 4025 reports: [], 4026 queries: { 4027"Density": `WITH 4028 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 4029 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 4030 tile_size * {x:UInt32} AS tile_x_begin, 4031 tile_size * ({x:UInt32} + 1) AS tile_x_end, 4032 tile_size * {y:UInt32} AS tile_y_begin, 4033 tile_size * ({y:UInt32} + 1) AS tile_y_end, 4034 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 4035 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 4036 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 4037 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 4038 y * 1024 + x AS pos, 4039 4040 count() * {sampling:UInt32} AS total, 4041 pow(least(1, total / 500 * zoom_factor), 1/5) AS color1, 4042 pow(least(1, total / 50000 * zoom_factor), 1/5) AS color2, 4043 pow(least(1, total / 5000000 * zoom_factor), 1/5) AS color3, 4044 255 AS alpha, 255 * color3 AS red, 255 * color2 AS green, 255 * color1 AS blue 4045 4046SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 4047FROM {table:Identifier} 4048WHERE in_tile 4049GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 4050"Tips": `WITH 4051 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 4052 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 4053 tile_size * {x:UInt32} AS tile_x_begin, 4054 tile_size * ({x:UInt32} + 1) AS tile_x_end, 4055 tile_size * {y:UInt32} AS tile_y_begin, 4056 tile_size * ({y:UInt32} + 1) AS tile_y_end, 4057 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 4058 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 4059 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 4060 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 4061 y * 1024 + x AS pos, 4062 4063 count() * {sampling:UInt32} AS total, 4064 pow(least(1, total / greatest(1, 60000000 DIV zoom_factor)), 1/5) AS conf, 4065 least(1, greatest(0, avgIf(tip_amount / fare_amount, fare_amount > 0) / 0.3)) AS m, 4066 4067 255 * conf AS alpha, 4068 255 * (1 - m) AS red, 4069 255 * m AS green, 4070 40 AS blue 4071 4072SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 4073FROM {table:Identifier} 4074WHERE in_tile AND fare_amount > 0 4075GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 4076"Trip distance": `WITH 4077 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 4078 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 4079 tile_size * {x:UInt32} AS tile_x_begin, 4080 tile_size * ({x:UInt32} + 1) AS tile_x_end, 4081 tile_size * {y:UInt32} AS tile_y_begin, 4082 tile_size * ({y:UInt32} + 1) AS tile_y_end, 4083 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 4084 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 4085 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 4086 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 4087 y * 1024 + x AS pos, 4088 4089 count() * {sampling:UInt32} AS total, 4090 pow(least(1, total / greatest(1, 60000000 DIV zoom_factor)), 1/5) AS conf, 4091 least(1, avg(trip_distance) / 10) AS m, 4092 4093 255 * conf AS alpha, 4094 255 * m AS red, 4095 255 * m AS green, 4096 255 * (1 - m) AS blue 4097 4098SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 4099FROM {table:Identifier} 4100WHERE in_tile AND trip_distance > 0 4101GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 4102"Fare": `WITH 4103 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 4104 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 4105 tile_size * {x:UInt32} AS tile_x_begin, 4106 tile_size * ({x:UInt32} + 1) AS tile_x_end, 4107 tile_size * {y:UInt32} AS tile_y_begin, 4108 tile_size * ({y:UInt32} + 1) AS tile_y_end, 4109 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 4110 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 4111 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 4112 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 4113 y * 1024 + x AS pos, 4114 4115 count() * {sampling:UInt32} AS total, 4116 pow(least(1, total / greatest(1, 60000000 DIV zoom_factor)), 1/5) AS conf, 4117 least(1, avgIf(fare_amount, fare_amount > 0) / 60) AS m, 4118 4119 255 * conf AS alpha, 4120 255 * m AS red, 4121 255 * (1 - m) AS green, 4122 40 AS blue 4123 4124SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 4125FROM {table:Identifier} 4126WHERE in_tile AND fare_amount > 0 4127GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 4128"Night rides": `WITH 4129 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 4130 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 4131 tile_size * {x:UInt32} AS tile_x_begin, 4132 tile_size * ({x:UInt32} + 1) AS tile_x_end, 4133 tile_size * {y:UInt32} AS tile_y_begin, 4134 tile_size * ({y:UInt32} + 1) AS tile_y_end, 4135 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 4136 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 4137 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 4138 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 4139 y * 1024 + x AS pos, 4140 4141 count() * {sampling:UInt32} AS total, 4142 pow(least(1, total / greatest(1, 60000000 DIV zoom_factor)), 1/5) AS conf, 4143 avg(toHour(pickup_datetime) < 6 OR toHour(pickup_datetime) >= 22) AS m, 4144 4145 255 * conf AS alpha, 4146 255 * (1 - m) AS red, 4147 180 * (1 - m) AS green, 4148 255 * m AS blue 4149 4150SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 4151FROM {table:Identifier} 4152WHERE in_tile 4153GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024` 4154 } 4155 }, 4156 4157 "Fires": { 4158 notice: "NASA FIRMS (VIIRS S-NPP active fire), attribution required", 4159 endpoints: [ 4160 { 4161 name: "Cloud (Real-Time)", 4162 urls: [ 4163 { 4164 url: "https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 4165 sticky: "https://{hash}.sticky.kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 4166 } 4167 ] 4168 }, 4169 ], 4170 levels: [ 4171 { table: 'firms_mercator', sample: 1, priority: 1 }, 4172 ], 4173 time: { column: 'acq_date' }, 4174 report_total: { 4175 query: (condition => ` 4176 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 4177 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 4178 SELECT count() AS fires, round(avg(frp),1) AS frp, min(acq_date) AS first, max(acq_date) AS last 4179 FROM {table:Identifier} WHERE ${condition}`), 4180 content: (json => { let row = json.data[0];
4180 let text = `Total ${Number(row.fires).toLocaleString()} fire detections, avg FRP ${row.frp} MW.`; if (row.fires>0) text += ` ${row.first} â ${row.last}.`; if (json.statistics.rows_read>1) text += ` Processed ${Number(json.statistics.rows_read).toLocaleString()} rows.`; return text; }), 4181 }, 4182 reports: [ 4183 { 4184 query: (condition => ` 4185 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 4186 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 4187 SELECT country, count() AS c FROM {table:Identifier} 4188 WHERE country != '' AND ${condition} 4189 GROUP BY country ORDER BY c DESC LIMIT 100`), 4190 field: 'country', 4191 id: 'report_country', 4192 title: 'Country: ', 4193 separator: ', ', 4194 content: (row => `${row.country} (${Number(row.c).toLocaleString()})`) 4195 }, 4196 ], 4197 queries: { 4198"Density": `WITH 4199 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 4200 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 4201 tile_size * {x:UInt32} AS tile_x_begin, 4202 tile_size * ({x:UInt32} + 1) AS tile_x_end, 4203 tile_size * {y:UInt32} AS tile_y_begin, 4204 tile_size * ({y:UInt32} + 1) AS tile_y_end, 4205 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 4206 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 4207 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 4208 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 4209 y * 1024 + x AS pos, 4210 4211 count() * {sampling:UInt32} AS total, 4212 pow(least(1, total / 3000 * zoom_factor), 1/5) AS t, 4213 255 * (0.2 + 0.8 * t) AS alpha, 255 AS red, 200 * t AS green, 0 AS blue 4214 4215SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 4216FROM {table:Identifier} 4217WHERE in_tile 4218GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 4219"Intensity": `WITH 4220 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 4221 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 4222 tile_size * {x:UInt32} AS tile_x_begin, 4223 tile_size * ({x:UInt32} + 1) AS tile_x_end, 4224 tile_size * {y:UInt32} AS tile_y_begin, 4225 tile_size * ({y:UInt32} + 1) AS tile_y_end, 4226 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 4227 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 4228 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 4229 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 4230 y * 1024 + x AS pos, 4231 4232 count() * {sampling:UInt32} AS total, 4233 pow(least(1, total / 3000 * zoom_factor), 1/5) AS t, 4234 least(1, avg(frp) / 100) AS frp, 4235 255 * (0.2 + 0.8 * t) AS alpha, 255 AS red, 255 * (1 - frp) AS green, 0 AS blue 4236 4237SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 4238FROM {table:Identifier} 4239WHERE in_tile 4240GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 4241"Day / Night": `WITH 4242 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 4243 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 4244 tile_size * {x:UInt32} AS tile_x_begin, 4245 tile_size * ({x:UInt32} + 1) AS tile_x_end, 4246 tile_size * {y:UInt32} AS tile_y_begin, 4247 tile_size * ({y:UInt32} + 1) AS tile_y_end, 4248 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 4249 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 4250 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 4251 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 4252 y * 1024 + x AS pos, 4253 4254 count() * {sampling:UInt32} AS total, 4255 pow(least(1, total / 3000 * zoom_factor), 1/5) AS t, 4256 avg(daynight = 'N') AS night, 4257 255 * (0.2 + 0.8 * t) AS alpha, 255 * (1 - night) AS red, 128 * t AS green, 255 * night AS blue 4258 4259SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 4260FROM {table:Identifier} 4261WHERE in_tile 4262GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 4263"Time": `WITH 4264 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 4265 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 4266 tile_size * {x:UInt32} AS tile_x_begin, 4267 tile_size * ({x:UInt32} + 1) AS tile_x_end, 4268 tile_size * {y:UInt32} AS tile_y_begin, 4269 tile_size * ({y:UInt32} + 1) AS tile_y_end, 4270 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 4271 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 4272 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 4273 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 4274 y * 1024 + x AS pos, 4275 4276 count() * {sampling:UInt32} AS total, 4277 pow(least(1, total / 3000 * zoom_factor), 1/5) AS t, 4278 avg(toDayOfYear(acq_date)) / 366 AS doy, 4279 255 * (0.2 + 0.8 * t) AS alpha, 255 * doy AS red, 255 * (1 - doy) AS green, 128 * t AS blue 4280 4281SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 4282FROM {table:Identifier} 4283WHERE in_tile 4284GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024` 4285 } 4286 }, 4287 4288 "Lightning": { 4289 notice: "NOAA GOES-16 GLM lightning, public domain", 4290 endpoints: [ 4291 { 4292 name: "Cloud (Real-Time)", 4293 urls: [ 4294 { 4295 url: "https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 4296 sticky: "https://{hash}.sticky.kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 4297 } 4298 ] 4299 }, 4300 ], 4301 levels: [ 4302 { table: 'glm_mercator', sample: 1, priority: 1 }, 4303 ], 4304 time: { column: 'timestamp' }, 4305 report_total: { 4306 query: (condition => ` 4307 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 4308 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 4309 SELECT count() AS flashes, min(timestamp) AS first, max(timestamp) AS last 4310 FROM {table:Identifier} WHERE ${condition}`), 4311 content: (json => { let row = json.data[0];
4311 let text = `Total ${Number(row.flashes).toLocaleString()} lightning flashes.`; if (row.flashes>0) text += ` ${row.first} â ${row.last}.`; if (json.statistics.rows_read>1) text += ` Processed ${Number(json.statistics.rows_read).toLocaleString()} rows.`; return text; }), 4312 }, 4313 reports: [], 4314 queries: { 4315"Density": `WITH 4316 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 4317 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 4318 tile_size * {x:UInt32} AS tile_x_begin, 4319 tile_size * ({x:UInt32} + 1) AS tile_x_end, 4320 tile_size * {y:UInt32} AS tile_y_begin, 4321 tile_size * ({y:UInt32} + 1) AS tile_y_end, 4322 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 4323 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 4324 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 4325 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 4326 y * 1024 + x AS pos, 4327 4328 count() * {sampling:UInt32} AS total, 4329 pow(least(1, total / 50 * zoom_factor), 1/5) AS t, 4330 255 * (0.2 + 0.8 * t) AS alpha, 200 * t AS red, 200 * t AS green, 255 AS blue 4331 4332SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 4333FROM {table:Identifier} 4334WHERE in_tile 4335GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 4336"Energy": `WITH 4337 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 4338 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 4339 tile_size * {x:UInt32} AS tile_x_begin, 4340 tile_size * ({x:UInt32} + 1) AS tile_x_end, 4341 tile_size * {y:UInt32} AS tile_y_begin, 4342 tile_size * ({y:UInt32} + 1) AS tile_y_end, 4343 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 4344 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 4345 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 4346 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 4347 y * 1024 + x AS pos, 4348 4349 count() * {sampling:UInt32} AS total, 4350 pow(least(1, total / 50 * zoom_factor), 1/5) AS t, 4351 least(1, avg(energy) / 2e-14) AS e, 4352 255 * (0.2 + 0.8 * t) AS alpha, 255 * e AS red, 255 * e AS green, 255 AS blue 4353 4354SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 4355FROM {table:Identifier} 4356WHERE in_tile 4357GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 4358"Flash area": `WITH 4359 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 4360 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 4361 tile_size * {x:UInt32} AS tile_x_begin, 4362 tile_size * ({x:UInt32} + 1) AS tile_x_end, 4363 tile_size * {y:UInt32} AS tile_y_begin, 4364 tile_size * ({y:UInt32} + 1) AS tile_y_end, 4365 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 4366 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 4367 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 4368 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 4369 y * 1024 + x AS pos, 4370 4371 count() * {sampling:UInt32} AS total, 4372 pow(least(1, total / 50 * zoom_factor), 1/5) AS t, 4373 least(1, avg(area) / 3e8) AS a, 4374 255 * (0.2 + 0.8 * t) AS alpha, 255 * a AS red, 128 AS green, 255 * (1 - a) AS blue 4375 4376SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 4377FROM {table:Identifier} 4378WHERE in_tile 4379GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 4380"Time": `WITH 4381 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 4382 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 4383 tile_size * {x:UInt32} AS tile_x_begin, 4384 tile_size * ({x:UInt32} + 1) AS tile_x_end, 4385 tile_size * {y:UInt32} AS tile_y_begin, 4386 tile_size * ({y:UInt32} + 1) AS tile_y_end, 4387 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 4388 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 4389 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 4390 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 4391 y * 1024 + x AS pos, 4392 4393 count() * {sampling:UInt32} AS total, 4394 pow(least(1, total / 50 * zoom_factor), 1/5) AS t, 4395 avg(toHour(timestamp)) / 24 AS h, 4396 255 * (0.2 + 0.8 * t) AS alpha, 255 * h AS red, 128 AS green, 255 * (1 - h) AS blue 4397 4398SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 4399FROM {table:Identifier} 4400WHERE in_tile 4401GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024` 4402 } 4403 }, 4404 4405 "Population": { 4406 notice: "Kontur Population 2023 (H3), CC BY 4.0", 4407 endpoints: [ 4408 { 4409 name: "Cloud (Real-Time)", 4410 urls: [ 4411 { 4412 url: "https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 4413 sticky: "https://{hash}.sticky.kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 4414 } 4415 ] 4416 }, 4417 ], 4418 levels: [ 4419 { table: 'population_mercator', sample: 1, priority: 1 }, 4420 ], 4421 report_total: { 4422 query: (condition => ` 4423 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 4424 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 4425 SELECT round(sum(population)) AS pop, count() AS cells 4426 FROM {table:Identifier} WHERE ${condition}`), 4427 content: (json => { let row = json.data[0];
4427 let text = `Total population ${Number(row.pop).toLocaleString()} in ${Number(row.cells).toLocaleString()} cells.`; if (json.statistics.rows_read>1) text += ` Processed ${Number(json.statistics.rows_read).toLocaleString()} rows.`; return text; }), 4428 }, 4429 reports: [], 4430 queries: { 4431"Density": `WITH 4432 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 4433 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 4434 tile_size * {x:UInt32} AS tile_x_begin, 4435 tile_size * ({x:UInt32} + 1) AS tile_x_end, 4436 tile_size * {y:UInt32} AS tile_y_begin, 4437 tile_size * ({y:UInt32} + 1) AS tile_y_end, 4438 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 4439 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 4440 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 4441 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 4442 y * 1024 + x AS pos, 4443 4444 sum(population) AS total, 4445 pow(least(1, total / 5000 * zoom_factor), 1/5) AS color1, 4446 pow(least(1, total / 500000 * zoom_factor), 1/5) AS color2, 4447 pow(least(1, total / 50000000 * zoom_factor), 1/5) AS color3, 4448 255 AS alpha, 255 * color3 AS red, 255 * color2 AS green, 255 * color1 AS blue 4449 4450SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 4451FROM {table:Identifier} 4452WHERE in_tile 4453GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 4454"Log density": `WITH 4455 bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 4456 bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 4457 tile_size * {x:UInt32} AS tile_x_begin, 4458 tile_size * ({x:UInt32} + 1) AS tile_x_end, 4459 tile_size * {y:UInt32} AS tile_y_begin, 4460 tile_size * ({y:UInt32} + 1) AS tile_y_end, 4461 mercator_x >= tile_x_begin AND mercator_x < tile_x_end 4462 AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 4463 bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 4464 bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 4465 y * 1024 + x AS pos, 4466 4467 sum(population) AS total, 4468 least(1, log10(1 + total) / 7) AS l, 4469 255 AS alpha, 255 * l AS red, 255 * (1 - abs(l - 0.5) * 2) AS green, 255 * (1 - l) AS blue 4470 4471SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 4472FROM {table:Identifier} 4473WHERE in_tile 4474GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024` 4475 } 4476 }, 4477 4478 "You": { 4479 notice: "this website", 4480 endpoints: [ 4481 { 4482 name: "Cloud (Real-Time)", 4483 urls: [ 4484 { 4485 url: "https://kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 4486 sticky: "https://{hash}.sticky.kvzqttvc2n.eu-west-1.aws.clickhouse-staging.com", 4487 } 4488 ] 4489 }, 4490 ], 4491 levels: [ 4492 { table: 'stats', sample: 1, priority: 1 }, 4493 ], 4494 time: { column: 'time' }, 4495 disable_cache: true, 4496 report_total: { 4497 query: (condition => ` 4498 WITH mercator_x >= {left:UInt32} AND mercator_x < {right:UInt32} 4499 AND mercator_y >= {top:UInt32} AND mercator_y < {bottom:UInt32} AS in_tile 4500 SELECT 4501 count() AS traces 4502 FROM {table:Identifier} 4503 WHERE ${condition}`), 4504 content: (json => { 4505 let row = json.data[0]; 4506 let text = `Total ${Number(row.traces).toLocaleString()} traces.`; 4507 4508 if (json.statistics.rows_read > 1) { 4509 text += ` Processed ${Number(json.statistics.rows_read).toLocaleString()} rows.`; 4510 } 4511 4512 return text; 4513 }), 4514 }, 4515 reports: [], 4516 queries: { 4517 "Density": `WITH 4518bitShiftLeft(1::UInt64, {z:UInt8}) AS zoom_factor, 4519bitShiftLeft(1::UInt64, 32 - {z:UInt8}) AS tile_size, 4520 4521tile_size * {x:UInt32} AS tile_x_begin, 4522tile_size * ({x:UInt32} + 1) AS tile_x_end, 4523 4524tile_size * {y:UInt32} AS tile_y_begin, 4525tile_size * ({y:UInt32} + 1) AS tile_y_end, 4526 4527mercator_x >= tile_x_begin AND mercator_x < tile_x_end 4528AND mercator_y >= tile_y_begin AND mercator_y < tile_y_end AS in_tile, 4529 4530bitShiftRight(mercator_x - tile_x_begin, 32 - 10 - {z:UInt8}) AS x, 4531bitShiftRight(mercator_y - tile_y_begin, 32 - 10 - {z:UInt8}) AS y, 4532 4533y * 1024 + x AS pos, 4534 4535count() AS total, 4536 4537pow(least(1, total / 100 * zoom_factor), 1/5) AS color1, 4538avg(least(greatest(0, zoom - 3), 6) / 6) AS color2, 4539avg(least(1, greatest(0, zoom - 6) / 6)) AS color3, 4540 4541255 AS alpha, 4542(1 - greatest(color2, color3)) * 255 AS blue, 4543least(1, color2 + color3) * 255 AS red, 4544(color3) * 255 AS green 4545 4546SELECT round(red)::UInt8, round(green)::UInt8, round(blue)::UInt8, round(alpha)::UInt8 4547FROM {table:Identifier} 4548WHERE in_tile 4549GROUP BY pos ORDER BY pos WITH FILL FROM 0 TO 1024*1024`, 4550 }, 4551 }, 4552};
Line numbers count LF bytes from the start of the resource, as the search results do. Vendor segments are library code the classifier recognised; they are stored but not indexed. Bytes are shown as Latin1 characters, one per byte.