SQL:在Google BigQuery上找到最接近的纬度/经度记录 [英] SQL: Finding the closest Lat/Lon record on Google BigQuery

问题描述

我在Google BigQuery上有一个带有位置的数据表，称为TABLE_A.

I have a data table on Google BigQuery with locations, call it TABLE_A.

这是TABLE_A的样子:

This is how TABLE_A looks like:

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

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

There's a second table with different items, call it TABLE_B. TABLE_B has the same schema as TABLE_A. This is a sample from TABLE_B:

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

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

and I want to create a new table, TABLE_C, in which every row has items from TABLE_A and TABLE_B such that the items are the closest (i.e. the distance between the lat/lon pair is the minimal distance when joining the tables). This would be an example of TABLE_C with the above sample data:

ID_A,ID_B
1,a
2,b

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

My actual data is a table of properties with lat/lon pairs on one hand and bigquery-public-data.noaa_gsod.stations on the other hand (I'm looking to find the closest weather station per property).

推荐答案

以下是BigQuery标准SQL

Below is for BigQuery Standard 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

您可以使用问题中的伪数据作为

you can test, play with it using dummy data from your question as

#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    

这篇关于SQL:在Google BigQuery上找到最接近的纬度/经度记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！