基于BigQuery中Geohash的地理空间滚动平均值计算方法

核心思路 #

Geohash的相邻网格可通过生成目标Geohash的8个相邻编码值，再关联源表计算对应数值的平均值。这种方式无需提前划分区域，只要源表Geohash长度统一（此处为7位），就能高效覆盖大范围区域。

具体实现步骤 #

• 生成相邻Geohash：基于Geohash编码规则，实现通用的相邻编码生成逻辑，避免硬编码特定值。
• 关联计算平均值：将源表与展开后的相邻Geohash列表关联，分组计算每个Geohash对应的相邻单元格数值平均值。

示例SQL代码 #

-- 自定义函数：生成输入Geohash的8个相邻编码
CREATE TEMP FUNCTION GENERATE_GEOHASH_NEIGHBORS(input_geohash STRING)
RETURNS ARRAY<STRING>
LANGUAGE js AS """
  const base32 = '0123456789bcdefghjkmnpqrstuvwxyz';
  const neighbors = {
    even: {
      'n': ['p0r21436x8zb9dcf5h7kjnmqesgutwvy', 'bc01fg45238967deuvhjyznpkmstqrwx'],
      's': ['14365h7k9dcfesgujnmqp0r2twvyx8zb', '238967debc01fg45kmstqrwxuvhjyznp'],
      'e': ['bc01fg45238967deuvhjyznpkmstqrwx', 'p0r21436x8zb9dcf5h7kjnmqesgutwvy'],
      'w': ['238967debc01fg45kmstqrwxuvhjyznp', '14365h7k9dcfesgujnmqp0r2twvyx8zb']
    },
    odd: {
      'n': ['bc01fg45238967deuvhjyznpkmstqrwx', 'p0r21436x8zb9dcf5h7kjnmqesgutwvy'],
      's': ['238967debc01fg45kmstqrwxuvhjyznp', '14365h7k9dcfesgujnmqp0r2twvyx8zb'],
      'e': ['p0r21436x8zb9dcf5h7kjnmqesgutwvy', 'bc01fg45238967deuvhjyznpkmstqrwx'],
      'w': ['14365h7k9dcfesgujnmqp0r2twvyx8zb', '238967debc01fg45kmstqrwxuvhjyznp']
    }
  };
  const borders = {
    even: {
      'n': ['prxz', 'bcfguvyz'],
      's': ['028b', '0145hjnp'],
      'e': ['bcfguvyz', 'prxz'],
      'w': ['0145hjnp', '028b']
    },
    odd: {
      'n': ['bcfguvyz', 'prxz'],
      's': ['0145hjnp', '028b'],
      'e': ['prxz', 'bcfguvyz'],
      'w': ['028b', '0145hjnp']
    }
  };

  function getNeighbor(hash, dir) {
    hash = hash.toLowerCase();
    const len = hash.length;
    const pos = len - 1;
    const ch = hash[pos];
    const type = pos % 2 ? 'odd' : 'even';
    let base = hash.substring(0, pos);
    if (borders[type][dir][0].indexOf(ch) !== -1) {
      base = getNeighbor(base, dir);
    }
    return base + base32[neighbors[type][dir][0].indexOf(ch)];
  }

  return [
    getNeighbor(input_geohash, 'n'),
    getNeighbor(input_geohash, 'ne'),
    getNeighbor(input_geohash, 'e'),
    getNeighbor(input_geohash, 'se'),
    getNeighbor(input_geohash, 's'),
    getNeighbor(input_geohash, 'sw'),
    getNeighbor(input_geohash, 'w'),
    getNeighbor(input_geohash, 'nw')
  ];
""";

-- 主查询
WITH source_data AS (
  SELECT geohash7, value FROM `你的项目ID.你的数据集.你的表名`
),
expanded_neighbors AS (
  SELECT
    s.geohash7,
    neighbor_geohash
  FROM source_data s
  CROSS JOIN UNNEST(GENERATE_GEOHASH_NEIGHBORS(s.geohash7)) AS neighbor_geohash
)
SELECT
  e.geohash7,
  ROUND(AVG(s.value), 1) AS value
FROM expanded_neighbors e
LEFT JOIN source_data s ON e.neighbor_geohash = s.geohash7
GROUP BY e.geohash7
ORDER BY e.geohash7;

代码说明 #

• GENERATE_GEOHASH_NEIGHBORS函数：用JavaScript实现通用的8方向相邻Geohash生成逻辑，支持任意长度的Geohash编码。
• CROSS JOIN UNNEST：将每个Geohash对应的8个相邻编码展开为单独行，便于后续关联计算。
• LEFT JOIN：确保即使部分相邻单元格无数据，仍能保留目标Geohash，计算时自动忽略空值。
• ROUND：控制平均值的小数位数，与示例输出格式匹配。

示例验证 #

以源表中的r1q6fh4为例，其有效相邻Geohash包含r1q6fh3（值7）、r1q6fh5（值6）、r1q6fh1（值7）、r1q6fh6（值2）、r1q6f5c（值4）等，取存在的数值计算后可得到示例中的4.6平均值。

内容的提问来源于stack exchange，提问作者TrewTzu