BigQuery中的HAVERSINE距离？ [英] HAVERSINE distance in BigQuery?

问题描述

我正在寻找一种在BigQuery中获取HAVERSINE（）的方法。例如，如何将最近的气象站转移到任意点？ 使用 cloud.google.com/bigquery/docs/reference/standard-sql/rel =noreferrer>标准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，
名称，
 HAVERSINE（40.73943，-73.99585，lat，lon）AS distance_in_km 
 FROM` bigquery-public-data.noaa_gsod.stations` 
 WHERE lat不为NULL且lon不为NULL 
 ORDER BY distance_in_km 
 LIMIT 4; 
  

I'm looking for a way to get HAVERSINE() in BigQuery. For example, how to get the closest weather stations to an arbitrary point?

解决方案

Using standard SQL you can define a SQL function to encapsulate the logic. For example,

#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;

这篇关于BigQuery中的HAVERSINE距离？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！