查找两个表之间最近的地理点bigquery

rur96b6h 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(227)

我有两张有经纬度的table。我想创建一个新表，其中包含两个表的信息，基于查找表之间的最近点。这类似于之前提出的问题；但是其中一个表有数组。前面问题的解决方案似乎不适用于数组。
表a

|--------|-------------|-------------|-------------|
|   id   |  latitude   |  longitude  |   address   |
|--------|-------------|-------------|-------------|
|   1    |     39.79   |     86.03   | 123 Vine St |
|--------|-------------|-------------|-------------|
|   2    |     39.89   |     84.01   | 123 Oak St  |
|--------|-------------|-------------|-------------|

表b

|-------------|-------------|-------------|--------------|
|  latitude   |  longitude  |  parameter1 |  parameter2  |
|-------------|-------------|-------------|--------------|
|     39.74   |     86.33   |  [1, 2, 3]  | [.1, .2, .3] |
|-------------|-------------|-------------|--------------|
|     39.81   |     83.90   |  [4, 5, 6]  | [.4, .5, .6] |
|-------------|-------------|-------------|--------------|

我想创建一个新表，表c，它包含表a中的所有行，并添加表b中的信息。表b中的信息是基于表b中最接近表a中特定行的点添加的。
表c

|------|-------------|-------------|--------------|
| id_A |   address   |  parameter1 |  parameter2  |
|------|-------------|-------------|--------------|
|  1   | 123 Vine St |  [1, 2, 3]  | [.1, .2, .3] |
|------|-------------|-------------|--------------|
|  2   |  123 Oak St |  [4, 5, 6]  | [.4, .5, .6] |
|------|-------------|-------------|--------------|

提前谢谢！

sql google-bigquery gis

来源：https://stackoverflow.com/questions/62547456/finding-the-closest-geographic-points-between-two-tables-bigquery

1条答案

按热度按时间

jhdbpxl91#

下面是bigquery标准sql


# standardSQL

SELECT AS VALUE 
  ARRAY_AGG(STRUCT(id, address, parameter1, parameter2) ORDER BY ST_DISTANCE(a.point, b.point) LIMIT 1)[OFFSET(0)]
FROM (SELECT *, ST_GEOGPOINT(longitude, latitude) point FROM `project.dataset.tableA`) a,
(SELECT *, ST_GEOGPOINT(longitude, latitude) point FROM `project.dataset.tableB`) b
GROUP BY id

如果要应用于您问题中的样本数据

WITH `project.dataset.tableA` AS (
  SELECT 1 id, 39.79 latitude, 86.03 longitude, '123 Vine St' address UNION ALL
  SELECT 2, 39.89, 84.01, '123 Oak St' 
), `project.dataset.tableB` AS (
  SELECT 39.74 latitude, 86.33 longitude, [1, 2, 3] parameter1, [.1, .2, .3] parameter2 UNION ALL
  SELECT 39.81, 83.90, [4, 5, 6], [.4, .5, .6] 
)

输出为

赞(0）回复(0）举报 2021-07-24

我来回答

查找两个表之间最近的地理点bigquery

1条答案

相关问题

热门标签

最新问答