反向地理编码:如何使用 BigQuery SQL 确定最接近 (lat,lon) 的城市? [英] Reverse- geocoding: How to determine the city closest to a (lat,lon) with BigQuery SQL?

问题描述

我收集了大量点 - 我想确定离每个点最近的城市.如何使用 BigQuery 执行此操作?

I have a huge collection of points - and I want to determine the closest city to each point. How can I do this with BigQuery?

推荐答案

这是我们迄今为止制定的性能最佳的查询:

This is the best performing query we've worked out so far:

WITH a AS (
  # a table with points around the world
  SELECT * FROM UNNEST([ST_GEOGPOINT(-70, -33), ST_GEOGPOINT(-122,37), ST_GEOGPOINT(151,-33)]) my_point
), b AS (
  # any table with cities world locations
  SELECT *, ST_GEOGPOINT(lon,lat) latlon_geo
  FROM `fh-bigquery.geocode.201806_geolite2_latlon_redux` 
)

SELECT my_point, city_name, subdivision_1_name, country_name, continent_name
FROM (
  SELECT loc.*, my_point
  FROM (
    SELECT ST_ASTEXT(my_point) my_point, ANY_VALUE(my_point) geop
      , ARRAY_AGG( # get the closest city
           STRUCT(city_name, subdivision_1_name, country_name, continent_name) 
           ORDER BY ST_DISTANCE(my_point, b.latlon_geo) LIMIT 1
        )[SAFE_OFFSET(0)] loc
    FROM a, b 
    WHERE ST_DWITHIN(my_point, b.latlon_geo, 100000)  # filter to only close cities
    GROUP BY my_point
  )
)
GROUP BY 1,2,3,4,5

这篇关于反向地理编码:如何使用 BigQuery SQL 确定最接近 (lat,lon) 的城市?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！