[SQL] 리버스 지오 코딩 : 어떻게 BigQuery의 SQL과 (위도, 경도)에 도시 가까운을 결정하기 위해?

리버스 지오 코딩 : 어떻게 BigQuery의 SQL과 (위도, 경도)에 도시 가까운을 결정하기 위해?

나는 점의 거대한 컬렉션이 - 나는 각 지점에 가장 가까운 도시를 결정합니다. 어떻게 BigQuery를 함께 할 수 있습니까?

해결법

1. ==============================

   1.이것은 우리가 지금까지 밖으로 일 한 최고 성능의 쿼리입니다 :

   이것은 우리가 지금까지 밖으로 일 한 최고 성능의 쿼리입니다 :

   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
   

2. ==============================

   2.펠리페의 솔루션은 여러 가지면에서 완벽하지만 당신은 가장 가까운 도시 검색 할뿐만 아니라 몇 가지 포인트를 가지고 솔루션을 훨씬 더 잘 작동 아래 60 마일 거리에 자신을 제한 할 수 없을 때 나는 경우에 그 발견

   펠리페의 솔루션은 여러 가지면에서 완벽하지만 당신은 가장 가까운 도시 검색 할뿐만 아니라 몇 가지 포인트를 가지고 솔루션을 훨씬 더 잘 작동 아래 60 마일 거리에 자신을 제한 할 수 없을 때 나는 경우에 그 발견

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

   노트 :

from https://stackoverflow.com/questions/53678306/reverse-geocoding-how-to-determine-the-city-closest-to-a-lat-lon-with-bigque by cc-by-sa and MIT license