在BigQuery中如何通过经纬度查询大洲、国家、城市及郊区？

要实现从经纬度关联获取大洲、国家、城市及郊区信息，核心是利用BigQuery的空间地理函数结合公共地理边界数据集完成空间连接，以下是具体步骤和示例：

1. 预处理你的经纬度表 #

首先需要将表中的经纬度数值转换为BigQuery支持的GEOGRAPHY类型，空间连接依赖该类型进行地理计算。假设你的表为your_project.your_dataset.your_locations，包含id、latitude、longitude字段：

WITH your_geolocations AS (
  SELECT
    id,
    latitude,
    longitude,
    -- 注意顺序：经度在前，纬度在后
    ST_GEOGPOINT(longitude, latitude) AS point_geo
  FROM `your_project.your_dataset.your_locations`
)

2. 选择合适的公共地理数据集 #

BigQuery提供了多个免费的公共地理边界数据集，推荐使用以下两个：

• 大洲/国家层级：bigquery-public-data.geo_boundaries.world_boundaries，包含全球国家、大洲的边界及名称
• 城市/郊区层级：bigquery-public-data.geo_openstreetmap.planet_osm_polygon，基于OpenStreetMap数据，通过admin_level字段区分地理层级（数值越大，区域越细分）

3. 完整关联SQL示例 #

以下SQL会一次性获取大洲、国家、城市、郊区信息，并处理一个点匹配多个区域的情况（优先取最精准的层级）：

WITH your_geolocations AS (
  SELECT
    id,
    latitude,
    longitude,
    ST_GEOGPOINT(longitude, latitude) AS point_geo
  FROM `your_project.your_dataset.your_locations`
),
-- 整理OpenStreetMap的地理层级数据
osm_geo_levels AS (
  SELECT
    geography,
    tags['name'] AS area_name,
    CAST(admin_level AS INT64) AS admin_level,
    -- 定义优先级：层级越细分，优先级越高
    CASE CAST(admin_level AS INT64)
      WHEN 10 THEN 4  -- 郊区
      WHEN 8 THEN 3   -- 城市
      WHEN 4 THEN 2   -- 州/省（可选）
      WHEN 2 THEN 1   -- 国家
      ELSE 0
    END AS priority
  FROM `bigquery-public-data.geo_openstreetmap.planet_osm_polygon`
  WHERE admin_level IN ('2','4','8','10')
    AND tags['name'] IS NOT NULL  -- 过滤无名称的无效区域
),
-- 对每个点的匹配区域按优先级排序，取最优匹配
ranked_matches AS (
  SELECT
    g.id,
    o.area_name,
    o.admin_level,
    ROW_NUMBER() OVER (PARTITION BY g.id, o.admin_level ORDER BY o.priority DESC) AS rn
  FROM your_geolocations g
  JOIN osm_geo_levels o
  ON ST_CONTAINS(o.geography, g.point_geo)
),
-- 获取大洲信息
continent_country AS (
  SELECT
    g.id,
    b.continent AS continent_name,
    b.name AS country_name
  FROM your_geolocations g
  JOIN `bigquery-public-data.geo_boundaries.world_boundaries` b
  ON ST_CONTAINS(b.geography, g.point_geo)
)
-- 汇总所有地理层级信息
SELECT
  g.id,
  g.latitude,
  g.longitude,
  cc.continent_name,
  cc.country_name,
  MAX(CASE WHEN r.admin_level = 8 THEN r.area_name END) AS city_name,
  MAX(CASE WHEN r.admin_level = 10 THEN r.area_name END) AS suburb_name
FROM your_geolocations g
LEFT JOIN continent_country cc ON g.id = cc.id
LEFT JOIN ranked_matches r ON g.id = r.id AND r.rn = 1
GROUP BY g.id, g.latitude, g.longitude, cc.continent_name, cc.country_name

关键注意事项 #

• admin_level调整：不同国家的地理层级定义可能有差异，比如部分地区用admin_level=7表示城市，建议先小范围测试调整数值。
• 性能优化：如果数据量较大，建议在your_geolocations的point_geo字段创建空间索引，或对表进行分区/分桶处理，提升连接效率。
• 边界点处理：位于区域边界的点可能匹配多个区域，通过ROW_NUMBER()和优先级排序可以确保取到最精准的层级信息。

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