如何使用BigQuery从经纬度坐标获取时区?

Question

给定纬度和经度，如何使用BigQuery知道该位置的有效时区?

?假设我有一个有100个坐标的表:

SELECT (RAND()-0.5)*90 as latitude, (RAND()-0.5)*180 as longitude FROM UNNEST(GENERATE_ARRAY(1, 100))

tbody> <<tr>2

行	纬度	经度
1	-24.66121614027115	31.92125752510383
	-17.060823106878654	30.070053747514052
3	1.5425276266633903	32.30384245018918

我将从上传时区多边形边界开始。

。你可以从这里获得https://github.com/evansiroky/timezone-boundary-builder。这个项目提供了shapefile和geojson两种文件格式，你需要转换成BigQuery支持的一种格式，比如新行分隔的geojson，或者CSV，然后上传到BigQuery。开源的ogr2ogr工具可以做到这一点，以及许多专有工具。

一旦在BigQuery中有了数据，就可以用时区多边形连接位置，如

WITH points AS (
SELECT (RAND()-0.5)*90 as latitude, (RAND()-0.5)*180 as longitude 
FROM UNNEST(GENERATE_ARRAY(1, 100))
)
SELECT * FROM points p CROSS JOIN time_zones tz
ON ST_Intersects(ST_GeogPoint(p.longitude, p.latitude), tz.geometry)