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

问题描述

我正在寻找一种在 BigQuery 中获取 HAVERSINE() 的方法.例如，如何获得最近的气象站到任意点?

解决方案

2018 年更新:BigQuery 现在支持原生地理功能.

<块引用>

ST_DISTANCE:返回两个非空 GEOGRAPHY 之间的最短距离(以米为单位).

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

#standardSQL使用地理点 AS (SELECT ST_GEOGPOINT(lon,lat) p、名称、状态从`bigquery-public-data.noaa_gsod.stations`)选择 ST_DISTANCE((SELECT p FROM geopoints WHERE name='PORT AUTH DOWNTN MANHATTAN WA'),(SELECT p FROM geopoints WHERE name='西雅图'))3866381.55

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

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

  解决方案

  2018 update: BigQuery now supports native geo functions.

  ST_DISTANCE: Returns the shortest distance in meters between two non-empty GEOGRAPHYs.

  Distance between NY and Seattle:

  #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

  ---

  Legacy SQL solution (standard pending):

  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
  

  (based on http://www.plumislandmedia.net/mysql/haversine-mysql-nearest-loc/)

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