get total precipitation within a date range for a geohash polygon

geohash-precipitation-noaa_gsod.sql

#standardSQL
CREATE TEMPORARY FUNCTION geohashDecode(geohash STRING)
RETURNS STRUCT<bbox ARRAY<FLOAT64>, lat FLOAT64, lng FLOAT64> 
  LANGUAGE js AS """
    if (!geohash) return null;
    var base32 = '0123456789bcdefghjkmnpqrstuvwxyz';
    geohash = geohash.toLowerCase();
    var evenBit = true;
    var latMin =  -90, latMax =  90;
    var lonMin = -180, lonMax = 180;
    for (var i=0; i<geohash.length; i++) {
        var chr = geohash.charAt(i);
        var idx = base32.indexOf(chr);
        if (idx == -1) throw new Error('Invalid geohash');
        for (var n=4; n>=0; n--) {
            var bitN = idx >> n & 1;
            if (evenBit) {
                // longitude
                var lonMid = (lonMin+lonMax) / 2;
                if (bitN == 1) {
                    lonMin = lonMid;
                } else {
                    lonMax = lonMid;
                }
            } else {
                // latitude
                var latMid = (latMin+latMax) / 2;
                if (bitN == 1) {
                    latMin = latMid;
                } else {
                    latMax = latMid;
                }
            }
            evenBit = !evenBit;
        }
    }
    var new_struct = new Object();
    new_struct.bbox = [latMin, lonMin, latMax, lonMax]; // Bounding Box
    new_struct.lat  = (latMax + latMin) / 2; // Center Latitude
    new_struct.lng  = (lonMax + lonMin) / 2; // Center Longitude
  
  return new_struct;
""";
WITH
geohash AS (
  SELECT geohashDecode(geohash) as doubles
  FROM 
  UNNEST(['u2ffwc9']) AS geohash --,'u342633','u34263','u3426','u342','u34','u3','u']) AS geohash --null
),
stations AS (
  SELECT usaf, ST_GEOGFROMTEXT(CONCAT('Point(',lon,' ',lat,')')) AS geog FROM `bigquery-public-data.noaa_gsod.stations` AS stations
),
polygons AS (
  SELECT ST_GEOGFROMTEXT(CONCAT('Polygon((', 
      geohash.doubles.bbox[OFFSET(1)], ' ', geohash.doubles.bbox[OFFSET(0)], ',',
      geohash.doubles.bbox[OFFSET(1)], ' ', geohash.doubles.bbox[OFFSET(1)], ',',
      geohash.doubles.bbox[OFFSET(0)], ' ', geohash.doubles.bbox[OFFSET(1)], ',',
      geohash.doubles.bbox[OFFSET(0)], ' ', geohash.doubles.bbox[OFFSET(0)], ',',
      geohash.doubles.bbox[OFFSET(1)], ' ', geohash.doubles.bbox[OFFSET(0)], '))'
    )) AS geog
  FROM geohash --, `bigquery-public-data.noaa_gsod` AS gsod
)

SELECT SUM(gsod.prcp)/COUNT(1) AS sum_precipitation
FROM stations, polygons, `bigquery-public-data.noaa_gsod.gsod2021` AS gsod
WHERE TRUE
  AND ST_WITHIN(stations.geog, polygons.geog)
  AND gsod.date BETWEEN '2021-04-01' AND '2021-04-30'
  AND stations.usaf = gsod.stn