如何从 BigQuery 的 Openstreet 地图数据集中提取所有国家的几何图形 [英] how to extract all countries geometry from Openstreet map dataset in BigQuery

问题描述

我正在使用此查询来提取所有使用 OSM 的国家/地区的几何图形，它可以正常工作，但我确信它正在创建一个重复项，因为我使用标志作为参考，有些地方有一个标志，但它们是不是真正的国家

I am using this query to extract the geometry of all countries using OSM, it works ok, but I am sure, it is creating a duplicated as I am using flag as a reference, some places have a flag, but they are not really countries

SELECT feature_type, osm_id, osm_timestamp, geometry,ar.key,ar.value,
  FROM `bigquery-public-data.geo_openstreetmap.planet_features`,UNNEST(all_tags) ar
   where ('boundary', 'administrative') IN (SELECT (key, value) FROM UNNEST(all_tags))
   and(feature_type="polygon" or feature_type= "multipolygon")
   AND ('flag') IN (SELECT (key) FROM UNNEST(all_tags)) and ar.key="name" order by st_area(geometry) desc

抱歉忘了说，有些标签丢失了，例如如果你选择admin_level = 2，像美国这样的国家会被丢弃

sorry forget to say, some tags are missing, for example if you select admin_level = 2, countries like the USA get dropped

推荐答案

我们可以通过合并 2 个表格来列出所有国家及其几何形状:

We can a list of all countries and their geometries by merging 2 tables:

SELECT features.feature_type, features.osm_id
  , ARRAY(
     SELECT DISTINCT AS STRUCT * FROM UNNEST(features.all_tags||relations.all_tags)
     WHERE key IN('int_name', 'name')
     ORDER BY 1 LIMIT 1 
  ) features
 , ROUND(ST_AREA(geometry)/1e6,1) area
FROM
  `bigquery-public-data.geo_openstreetmap.planet_features` AS features,
  `bigquery-public-data.geo_openstreetmap.planet_relations` AS relations
WHERE ('boundary','administrative') IN (SELECT (key,value) FROM UNNEST(features.all_tags))
AND ('admin_level','2') IN (SELECT (key,value) FROM UNNEST(relations.all_tags))
AND feature_type = 'multipolygon'
AND relations.id=SAFE_CAST(features.osm_id AS INT64)
ORDER BY area

(ref)

这篇关于如何从 BigQuery 的 Openstreet 地图数据集中提取所有国家的几何图形的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！