How to get time zone boundaries in BigQuery?

Question

Giving the following GCP services:

• BigQuery
• Cloud Storage
• Cloud Shell

What is the easiest way to access a BigQuery table with the following 2-columns structure ?

Column	Description	Type	Primary key
tzid	Time zone identifier	STRING	x
bndr	Boundaries	GEOGRAPHY

For example:

tzid	bndr
Africa/Abidjan	POLYGON((-5.440683 4.896553, -5.303699 4.912035, -5.183637 4.923927, ...))
Africa/Accra	POLYGON((-0.136231 11.13951, -0.15175 11.142384, -0.161168 11.14698, ...))
Pacific/Wallis	MULTIPOLYGON(((-178.350043 -14.384951, -178.344628 -14.394109, ...)))

Answer 1

1. Download and unzip timezones.geojson.zip from @evan-siroky repository on your computer. Coordinates are structured as follows (geojson format):

{
  "type": "FeatureCollection",
  "features":
  [
   {
     "type":"Feature",
     "properties":
     {
      "tzid":"Africa/Abidjan"
     },
     "geometry":
     {
        "type":"Polygon",
        "coordinates":[[[-5.440683,4.896553],[-5.303699,4.912035], ...]]]
     }
   },
   {
     "type":"Feature",
     "properties": ...
   }
  ]
}

BigQuery does not accept geojson but jsonl (new line delimited json) format to load tables. Steps 3 to 5 aim to convert to jsonl format.

2. Upload the file timezones_geojson.json to Cloud Storage gs://your-bucket/.
3. Move the file in the Cloud Shell Virtual Machine

gsutil mv gs://your-bucket/timezones_geojson.json .

4. Parse the file timezones_geojson.json, filter on "features" and return one line per element (see jq command):

cat timezones_geojson.json | jq -c ".features[]" > timezones_jsonl.json

The previous format will be transformed to:

{
 "type":"Feature",
 "properties":
 {
  "tzid":"Africa/Abidjan"
 },
 "geometry":
 {
  "type":"Polygon",
  "coordinates":[[[-5.440683,4.896553],[-5.303699,4.912035], ... ]]]
 }
}
{
 "type":"Feature",
 "properties":...
 "geometry":...
}

5. Move the jsonl on Cloud Storage

gsutil mv timezones_jsonl.json gs://your-bucket/

6. Load the jsonl to BigQuery

bq load --autodetect --source_format=NEWLINE_DELIMITED_JSON --json_extension=GEOJSON your_dataset.timezones gs://your-bucket/timezones_jsonl.json