BigQuery中基于经纬度匹配所属国家的实现方案咨询

公开可用的全球国家边界公共数据集 #

BigQuery公共数据集里已经有现成的全球国家多边形边界表，不需要自行导入数据：

• bigquery-public-data.natural_earth.countries：Natural Earth维护的全球国家/地区边界数据集，geom字段为WGS84坐标系的MultiPolygon类型边界，附带国家名称、ISO编码等属性，精度满足绝大多数业务场景，是最推荐使用的表。
• bigquery-public-data.geo_openstreetmap.planet_features：OpenStreetMap开源维护的边界数据，筛选feature_type = 'boundary'、admin_level = '2'即可提取全球国家边界，边界精度更高，适合对边境、海岸线匹配精度要求高的场景，缺点是数据量更大，查询扫描成本略高。

你之前找到的bigquery-public-data.geo_us_boundaries.states仅覆盖美国次级行政边界，确实不满足全球匹配需求。

无外部API的原生匹配实现 #

直接用BigQuery内置的空间函数做关联即可，全程在BigQuery内部完成计算，不需要调用任何外部服务：

注意：构造地理点时经度在前、纬度在后，你给出的示例坐标纬度为37.8532261、经度为-119.6911924，参数顺序写反会导致点位落到错误区域。

假设你的点位表路径为[你的项目ID].[你的数据集名].[你的点位表名]，表中纬度字段为lat、经度字段为lng，匹配SQL参考如下：

WITH point_with_geo AS (
  SELECT
    *,
    ST_GEOGPOINT(lng, lat) AS point_geom
  FROM `你的项目ID.你的数据集名.你的点位表名`
)
SELECT
  p.*,
  c.admin AS country_name,
  c.iso_a3 AS country_iso3_code
FROM point_with_geo p
LEFT JOIN `bigquery-public-data.natural_earth.countries` c
ON ST_INTERSECTS(p.point_geom, c.geom)

优化提示 #

• 边境、海岸线附近的点位可能因为边界精度问题出现匹配空值，可以把关联条件替换为ST_DWITHIN(p.point_geom, c.geom, 10)，即匹配距离点位10米范围内的国家多边形，可覆盖绝大多数误差场景。
• 百万级规模的点位用该方案查询，通常几分钟内即可跑完，扫描数据量控制得当的话可以覆盖在BigQuery免费额度内。

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