HAVERSINE distance in BigQuery?

Question

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

Answer 1

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/)