反向地理编码:如何使用BigQuerySQL确定最靠近a(纬度,伦敦)的城市?

这是迄今为止我们解决的性能最好的查询:

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

#standardSQL
WITH a AS (
  # a table with points around the world
  SELECT ST_GEOGPOINT(lon,lat) my_point
  FROM `fh-bigquery.geocode.201806_geolite2_latlon_redux`  
), b AS (
  # any table with cities world locations
  SELECT *, ST_GEOGPOINT(lon,lat) latlon_geo, ST_ASTEXT(ST_GEOGPOINT(lon,lat)) hsh 
  FROM `fh-bigquery.geocode.201806_geolite2_latlon_redux` 
)
SELECT AS VALUE 
  ARRAY_AGG(
    STRUCT(my_point, city_name, subdivision_1_name, country_name, continent_name) 
    LIMIT 1
  )[OFFSET(0)]
FROM (
  SELECT my_point, ST_ASTEXT(closest) hsh 
  FROM a, (SELECT ST_UNION_AGG(latlon_geo) arr FROM b),
  UNNEST([ST_CLOSESTPOINT(arr, my_point)]) closest
)
JOIN b 
USING(hsh)
GROUP BY ST_ASTEXT(my_point)

注:

• ST_CLOSESTPOINT 作用
• 仿照 not just few points ... b 因此,有10万个点可以搜索最近的城市,也没有限制查找城市的距离(在这种情况下,原始答案中的查询将以著名城市结束) Query exceeded resource limits -除此之外,它显示出更好的性能(如果不是最佳性能的话,正如该答案中所述)