在BigQuery中计算非美国邮政编码/地址间距离的方法

以下是针对非美国地址的几种可行方案，按需选择：

1. 用BigQuery内置GEOCODE函数（小批量场景） #

BigQuery的GIS功能提供了GEOCODE函数，可直接解析完整地址返回经纬度信息，适合小批量地址处理。

提取经纬度示例： #

WITH target_addresses AS (
  SELECT '你的非美国完整地址1' AS full_address UNION ALL
  SELECT '你的非美国完整地址2' AS full_address
)
SELECT
  full_address,
  -- 解析出经纬度并转为地理点
  ST_GEOGPOINT(
    CAST(JSON_EXTRACT_SCALAR(GEOCODE(full_address), '$.longitude') AS FLOAT64),
    CAST(JSON_EXTRACT_SCALAR(GEOCODE(full_address), '$.latitude') AS FLOAT64)
  ) AS location_point
FROM target_addresses;

计算两点距离（米为单位）示例： #

WITH address_pair AS (
  SELECT
    '地址A' AS addr_a,
    '地址B' AS addr_b
),
geocoded_points AS (
  SELECT
    ST_GEOGPOINT(
      CAST(JSON_EXTRACT_SCALAR(GEOCODE(addr_a), '$.longitude') AS FLOAT64),
      CAST(JSON_EXTRACT_SCALAR(GEOCODE(addr_a), '$.latitude') AS FLOAT64)
    ) AS point_a,
    ST_GEOGPOINT(
      CAST(JSON_EXTRACT_SCALAR(GEOCODE(addr_b), '$.longitude') AS FLOAT64),
      CAST(JSON_EXTRACT_SCALAR(GEOCODE(addr_b), '$.latitude') AS FLOAT64)
    ) AS point_b
  FROM address_pair
)
SELECT ST_DISTANCE(point_a, point_b) AS distance_meters
FROM geocoded_points;

注意：GEOCODE有调用配额限制，且并非所有地区都支持，若解析失败需换用其他方案。

2. 导入第三方邮编地理数据（批量/特定地区场景） #

如果目标地区不在GEOCODE支持范围内，或需要批量处理，可导入权威的第三方邮编-经纬度映射数据（比如各国邮政部门公开数据、OpenStreetMap导出数据集）。

步骤： #

• 准备包含邮编、纬度、经度、国家编码的CSV数据，确保数据来源可靠
• 将CSV上传至Google Cloud Storage，导入到BigQuery自定义表（比如your_project.your_dataset.zipcode_geo_map）
• 关联自己的地址表匹配经纬度，再计算距离：

WITH your_address_list AS (
  SELECT '邮编X' AS zip_code, '完整地址X' AS full_address UNION ALL
  SELECT '邮编Y' AS zip_code, '完整地址Y' AS full_address
),
zip_geo_data AS (
  SELECT
    zip_code,
    ST_GEOGPOINT(longitude, latitude) AS location_point
  FROM `your_project.your_dataset.zipcode_geo_map`
)
SELECT
  a.full_address,
  z.location_point,
  -- 计算两个地址间的距离
  ST_DISTANCE(
    (SELECT location_point FROM zip_geo_data WHERE zip_code = '邮编X'),
    (SELECT location_point FROM zip_geo_data WHERE zip_code = '邮编Y')
  ) AS distance_meters
FROM your_address_list a
JOIN zip_geo_data z ON a.zip_code = z.zip_code;

3. 自定义外部函数调用地理编码API（小众地区场景） #

若上述方案都无法覆盖，可创建BigQuery外部函数，调用第三方地理编码API（如Google Maps Geocoding API）获取经纬度。

示例（假设已创建外部函数EXTERNAL_GEOCODE）： #

WITH address_pair AS (
  SELECT
    '小众地区地址A' AS addr_a,
    '小众地区地址B' AS addr_b
)
SELECT
  ST_DISTANCE(
    ST_GEOGPOINT(EXTERNAL_GEOCODE(addr_a).longitude, EXTERNAL_GEOCODE(addr_a).latitude),
    ST_GEOGPOINT(EXTERNAL_GEOCODE(addr_b).longitude, EXTERNAL_GEOCODE(addr_b).latitude)
  ) AS distance_meters
FROM address_pair;

注意：此方案需要配置API密钥，会产生调用费用，需评估成本后使用。

内容的提问来源于stack exchange，提问作者Juan David Ossa Gomez