CODEWITHSUNDEEP
google-cloud-platformgoogle-bigquerytimestamptimezonecoordinates
Maxime Oriol
Maxime Oriol

Reputation: 143

How to get the time zone from latitude and longitude coordinates with BigQuery?

Given latitudes and longitudes, how can I know what time zone is in effect in that location with BigQuery?

Suppose I have a table with 100 coordinates:

SELECT (RAND()-0.5)*90 as latitude, (RAND()-0.5)*180 as longitude FROM UNNEST(GENERATE_ARRAY(1, 100))
Row latitude longitude
1 -24.66121614027115 31.92125752510383
2 -17.060823106878654 30.070053747514052
3 1.5425276266633903 32.30384245018918

I search the easiest way to get:

timezone
Africa/Maputo
Africa/Harare
Africa/Kampala

Upvotes: 1

Views: 2150

Answers (1)

Michael Entin
Michael Entin

Reputation: 7724

I would start by uploading time zone polygon boundaries.

E.g. you can get them from here https://github.com/evansiroky/timezone-boundary-builder. This project offers shapefile and geojson file formats, you'll need to convert to one of the formats supported by BigQuery, e.g. new-line-delimited geojson, or CSV, and upload to BigQuery. Open source ogr2ogr tool can do this, as well as many proprietary tools.

Once you have the data in BigQuery, you can join the locations with time zone polygons, like

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)

Upvotes: 2

Related Questions