如何从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

(参考)

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