CODEWITHSUNDEEP
google-bigquerygeolocationgeography
Ire00
Ire00

Reputation: 131

Get centroid coordinates of a given country in BigQuery

In BigQuery, given a country in ISO-2 code I need to get its centroids coordinates (lat and long). There is a way to do this? Looking into the geography functions of BQ I did not find a way.

Upvotes: 1

Views: 810

Answers (1)

Kabilan Mohanraj
Kabilan Mohanraj

Reputation: 1906

You can use the bigquery-public-data.geo_openstreetmap.planet_features table from BigQuery public datasets to calculate the centroids of countries. The inner query is based on this answer from Stack Overflow. Consider the below query and output.

SELECT -- Extract country code, lat and long
 CountryISO2Code,
 ST_X(centroid) AS longitude,
 ST_Y(centroid) AS latitude
FROM (SELECT (SELECT value FROM UNNEST(all_tags)
   WHERE key = 'ISO3166-1:alpha2') AS CountryISO2Code,
     ST_CENTROID(geometry) centroid -- calculate the centroid with the geometry values
   FROM `bigquery-public-data.geo_openstreetmap.planet_features`
   WHERE EXISTS (SELECT 1 FROM UNNEST(all_tags) WHERE key='boundary' AND value= 'administrative')
     AND EXISTS (SELECT 1 FROM UNNEST(all_tags) WHERE key='admin_level' AND value = '2')
     AND EXISTS (SELECT 1 FROM UNNEST(all_tags) WHERE key='ISO3166-1:alpha2'))
WHERE CountryISO2Code="IN" -- country code to filter the output

Output of the above query enter image description here

Please note that there are some country codes that are not available in the table. Also, OSM dataset in BigQuery itself is produced as a public good by volunteers, and there are no guarantees about data quality.

I also found some community-maintained data from Github like this one which can be imported into BigQuery and is ready-to-use.

Upvotes: 1

Related Questions