在BigQuery中通过GPS坐标匹配芝加哥交叉街道的可行性及指导

这个需求完全可行，以下是具体操作步骤：

1. 获取芝加哥街道地理边界数据 #

BigQuery提供了公开的芝加哥地理数据集，比如bigquery-public-data.chicago_boundaries.streets，这类表包含街道的几何形状（geometry字段）和街道名称等信息，可用于坐标匹配。

2. 用空间连接匹配坐标与街道 #

借助BigQuery的空间函数ST_CONTAINS或ST_INTERSECTS，将你的GPS点与街道地理边界做连接，找到包含该点的街道信息。

示例查询 #

SELECT
  original.start_station_name,
  original.point,
  streets.street_name,
  streets.segment_id -- 根据实际表结构选择需要的字段
FROM (
  -- 你的初始查询作为子查询
  SELECT 
    start_station_name, 
    ST_GEOGPOINT(start_lng, start_lat) AS point
  FROM `coursera-practice-356200.Cyclistic_data.Chi_metered`
  WHERE start_station_name IS NULL
) AS original
-- 空间关联，找到包含该点的街道
JOIN `bigquery-public-data.chicago_boundaries.streets` AS streets
ON ST_CONTAINS(streets.geometry, original.point)

3. 精准匹配交叉街道 #

如果需要获取两条街道的交叉名称：

• 若有公开的芝加哥路口地理表（记录路口区域及对应交叉街道），直接关联该表匹配；
• 若没有路口表，可找到该点所属的多条相邻街道，组合成交叉街道名称（比如同时匹配到Michigan Ave和Lake St，生成"Michigan Ave & Lake St"）。

4. 优化查询性能 #

• 对point字段创建空间索引，提升匹配速度；
• 先过滤掉芝加哥范围外的无效坐标，减少待匹配数据量。

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