sql - 反向地理编码:如何使用 BigQuery SQL 确定最接近 (lat,lon) 的城市?
问题描述
我有大量的点 - 我想确定离每个点最近的城市。如何使用 BigQuery 执行此操作?
解决方案
这是迄今为止我们制定的性能最好的查询:
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
推荐阅读
- reactjs - 使用 switch 函数有条件地渲染组件
- bash - 根据文件内的内容对 grep 输出进行排序
- android - 错误崩溃 RoomCaused by:android.database.sqlite.SQLiteConstraintException:唯一约束失败:products.id(代码 1555)
- python - ImportError:DLL 加载失败。找不到“imp.py”
- jquery - 无法解析 Ajax Json
- r - 在 R 中从调整后的生存曲线绘制累积事件
- javascript - 单击时更改页面的功能无缘无故地工作
- java - 我可以有多个文件夹,如 webapps 并指定访问哪个 ROOT.war 文件的域
- debezium - Debezium 不捕获“级联删除”
- java - 为什么我只能看到这个 Kafka 示例中的所有其他消息?