如何使用自定义GeoJSON筛选BigQuery表中地理范围内的行

要筛选出落在自定义GeoJSON范围内的数据行，核心是利用BigQuery的地理空间函数完成坐标转换与空间关系判断，具体步骤如下：

1. 将表中经纬度转换为地理点对象 #

BigQuery的空间函数依赖GEOGRAPHY类型，需要先把数值型的latitude（纬度）和longitude（经度）转换成地理点。注意必须以经度在前、纬度在后的顺序传入ST_GeogPoint函数，和GeoJSON的坐标规则保持一致：

ST_GeogPoint(longitude, latitude)

2. 解析自定义GeoJSON为地理区域 #

使用ST_GeogFromGeoJSON函数，将你的GeoJSON字符串直接转换成BigQuery可识别的地理区域对象，支持Polygon、MultiPolygon等常见GeoJSON类型：

ST_GeogFromGeoJSON('你的GeoJSON字符串')

3. 用空间函数筛选符合条件的数据 #

通过ST_Within或ST_Contains判断点是否落在区域内，两者逻辑等价，任选其一即可：

• ST_Within(点对象, 区域对象)：判断点是否包含在区域内
• ST_Contains(区域对象, 点对象)：判断区域是否包含点

完整SQL示例 #

假设你的表为your-project.your-dataset.your-table，自定义GeoJSON是一个多边形，查询语句如下：

SELECT *
FROM `your-project.your-dataset.your-table`
WHERE ST_Within(
  ST_GeogPoint(longitude, latitude),
  ST_GeogFromGeoJSON('{
    "type": "Polygon",
    "coordinates": [
      [
        [116.3972, 39.9075],
        [116.4072, 39.9075],
        [116.4072, 39.9175],
        [116.3972, 39.9175],
        [116.3972, 39.9075]
      ]
    ]
  }')
)

复杂GeoJSON的优化写法 #

如果GeoJSON内容较长，可通过WITH子句将区域对象单独提取，让SQL更简洁：

WITH geojson_area AS (
  SELECT ST_GeogFromGeoJSON('{
    "type": "Polygon",
    "coordinates": [
      [
        [116.3972, 39.9075],
        [116.4072, 39.9075],
        [116.4072, 39.9175],
        [116.3972, 39.9175],
        [116.3972, 39.9075]
      ]
    ]
  }') AS area
)
SELECT t.*
FROM `your-project.your-dataset.your-table` t, geojson_area ga
WHERE ST_Within(ST_GeogPoint(t.longitude, t.latitude), ga.area)

注意事项 #

• 确保GeoJSON格式合法：比如Polygon必须是闭合的（首尾坐标一致），避免解析失败。
• 大数据量场景：建议给ST_GeogPoint(longitude, latitude)创建地理空间索引，大幅提升查询效率。

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