在BigQuery中使用HAVERSINE距离算法？

Question

我正在寻找在BigQuery中获取HAVERSINE()函数的方法。例如，如何获取离任意点最近的气象站？

- Felipe Hoffa

---

参考：https://twitter.com/joaocorreia/status/827638555035840512 - Felipe Hoffa

Answer 1

2019更新：BigQuery现在拥有本地ST_DISTANCE()函数，比Haversine更准确。

例如：

#standardSQL
CREATE TEMP FUNCTION RADIANS(x FLOAT64) AS (
  ACOS(-1) * x / 180
);
CREATE TEMP FUNCTION RADIANS_TO_KM(x FLOAT64) AS (
  111.045 * 180 * x / ACOS(-1)
);
CREATE TEMP FUNCTION HAVERSINE(lat1 FLOAT64, long1 FLOAT64,
                               lat2 FLOAT64, long2 FLOAT64) AS (
  RADIANS_TO_KM(
    ACOS(COS(RADIANS(lat1)) * COS(RADIANS(lat2)) *
         COS(RADIANS(long1) - RADIANS(long2)) +
         SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))))
);

SELECT
  lat,
  lon,
  name,
  HAVERSINE(40.73943, -73.99585, lat, lon) *1000 AS haversine_distance
  , ST_DISTANCE(
      ST_GEOGPOINT(-73.99585, 40.73943)
      , ST_GEOGPOINT(lon,lat)) bqgis_distance
FROM `bigquery-public-data.noaa_gsod.stations`
WHERE lat IS NOT NULL AND lon IS NOT NULL
ORDER BY 1 DESC
LIMIT 4;

---

使用标准 SQL，您可以定义一个SQL函数来封装逻辑。例如：

#standardSQL
CREATE TEMP FUNCTION RADIANS(x FLOAT64) AS (
  ACOS(-1) * x / 180
);
CREATE TEMP FUNCTION RADIANS_TO_KM(x FLOAT64) AS (
  111.045 * 180 * x / ACOS(-1)
);
CREATE TEMP FUNCTION HAVERSINE(lat1 FLOAT64, long1 FLOAT64,
                               lat2 FLOAT64, long2 FLOAT64) AS (
  RADIANS_TO_KM(
    ACOS(COS(RADIANS(lat1)) * COS(RADIANS(lat2)) *
         COS(RADIANS(long1) - RADIANS(long2)) +
         SIN(RADIANS(lat1)) * SIN(RADIANS(lat2))))
);

SELECT
  lat,
  lon,
  name,
  HAVERSINE(40.73943, -73.99585, lat, lon) AS distance_in_km
FROM `bigquery-public-data.noaa_gsod.stations`
WHERE lat IS NOT NULL AND lon IS NOT NULL
ORDER BY distance_in_km
LIMIT 4;

- Elliott Brossard

---

我不得不在HAVERSINE中添加一个CASE WHEN lat1 = lat2 AND long1 = long2 THEN 0，以避免计算完全相同的位置时出现错误。 - Tim Swast

Answer 2

2018年更新：BigQuery现在支持本地地理函数。

ST_DISTANCE：返回两个非空GEOGRAPHY之间的最短距离（以米为单位）。

纽约和西雅图之间的距离：

#standardSQL
WITH geopoints AS (
  SELECT ST_GEOGPOINT(lon,lat) p, name, state
  FROM `bigquery-public-data.noaa_gsod.stations`  
)

SELECT ST_DISTANCE(
  (SELECT p FROM geopoints WHERE name='PORT AUTH DOWNTN MANHATTAN WA'),
  (SELECT p FROM geopoints WHERE name='SEATTLE')
)

3866381.55

• https://cloud.google.com/bigquery/docs/reference/standard-sql/geography_functions

---

传统SQL解决方案（标准待定）：

SELECT lat, lon, name,
  (111.045 * DEGREES(ACOS(COS(RADIANS(40.73943)) * COS(RADIANS(lat)) * COS(RADIANS(-73.99585) - RADIANS(lon)) + SIN(RADIANS(40.73943)) * SIN(RADIANS(lat))))) AS distance
FROM [bigquery-public-data:noaa_gsod.stations]
HAVING distance>0
ORDER BY distance
LIMIT 4

(基于http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/)

- Felipe Hoffa

---

使用标准SQL，您可以将逻辑放在SQL UDF中，而不必直接放入查询体中。 - Elliott Brossard

我知道！我尝试了一下，但是我缺少DEGREES()和RADIANS()。我把查询留着，直到我找出等效的转换，包括PI()的缺失。但我会回来的 :) - Felipe Hoffa

顺便提一下 - 这将给出公里数的距离。 - Afanasii Kurakin