SQL:在 Google BigQuery 上查找最接近的纬度/经度记录

Question

我在 Google BigQuery 上有一个包含位置的数据表，称之为 TABLE_A。

这是 TABLE_A 的样子:

ID,Lat,Lon
1,32.95,65.567
2,33.95,65.566

还有一个包含不同项目的第二个表，将其称为 TABLE_B。 TABLE_B 具有与 TABLE_A 相同的架构。这是来自 TABLE_B 的示例:

ID,Lat,Lon
a,32.96,65.566
b,33.96,65.566

我想创建一个新表，TABLE_C，其中每一行都有来自 TABLE_A 和 TABLE_B 的项目，这样这些项目是最接近的(即纬度/经度对之间的距离是连接表时的最小距离) .这将是具有上述示例数据的 TABLE_C 示例:

ID_A,ID_B
1,a
2,b

我的实际数据是一个属性表，一方面是经纬度对，另一方面是 bigquery-public-data.noaa_gsod.stations(我正在寻找最接近的天气每个属性站)。

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
SELECT AS VALUE ARRAY_AGG(STRUCT<id_a INT64, id_b STRING>(a.id, b.id) ORDER BY ST_DISTANCE(a.point, b.point) LIMIT 1)[OFFSET(0)] 
FROM (SELECT id, ST_GEOGPOINT(lon, lat) point FROM `project.dataset.table_a`) a
CROSS JOIN (SELECT id, ST_GEOGPOINT(lon, lat) point FROM `project.dataset.table_b`) b 
GROUP BY a.id

您可以使用您问题中的虚拟数据来测试和使用它

#standardSQL
WITH `project.dataset.table_a` AS (
  SELECT 1 id, 32.95 lat, 65.567 lon UNION ALL
  SELECT 2, 33.95, 65.566
), `project.dataset.table_b` AS (
  SELECT 'a' id, 32.96 lat, 65.566 lon UNION ALL
  SELECT 'b', 33.96, 65.566
)
SELECT AS VALUE ARRAY_AGG(STRUCT<id_a INT64, id_b STRING>(a.id, b.id) ORDER BY ST_DISTANCE(a.point, b.point) LIMIT 1)[OFFSET(0)] 
FROM (SELECT id, ST_GEOGPOINT(lon, lat) point FROM `project.dataset.table_a`) a
CROSS JOIN (SELECT id, ST_GEOGPOINT(lon, lat) point FROM `project.dataset.table_b`) b 
GROUP BY a.id   

结果

Row id_a    id_b     
1   1       a    
2   2       b