反向地理编码:如何使用BigQuery SQL确定最接近(纬度，经度)的城市? [英] 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确定最接近(纬度，经度)的城市?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！