在BigQuery中获取给定国家的质心坐标

Question

在BigQuery中，给定一个国家的ISO-2代码，我需要得到它的质心坐标(lat和long)。有办法这么做吗？看着地理功能的烧烤，我没有找到一个方法。

Answer 1

您可以使用来自bigquery-public-data.geo_openstreetmap.planet_features公共数据集的BigQuery表来计算国家的质心。内部查询基于堆栈溢出的这个答案。考虑下面的查询和输出。

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

上述查询的输出

​

请注意，表中没有一些国家代码。此外，BigQuery中的OSM数据集本身也是由志愿者作为公共产品生产的，而且对数据质量没有任何保证。

我还发现了一些社区维护的数据，比如这一个，这些数据可以导入到BigQuery中，可以随时使用。