Search code examples
google-bigquerygisopenstreetmap

US geometry is missing from openstreet map dataset in Bigquery

I am using this query to return all countries geometry, strangely, the USA is missing

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 ('admin_level', '2') IN (SELECT (key, value) FROM UNNEST(all_tags)) and ar.key="name"

am I missing something ?

Solution

  • The US is there. It just doesn't have all the tags you expect it to have:

    SELECT feature_type, osm_id, osm_way_id, osm_timestamp
  , ARRAY(SELECT AS STRUCT * FROM UNNEST(all_tags) WHERE key NOT LIKE 'name:%')
FROM `bigquery-public-data.geo_openstreetmap.planet_features`
WHERE true
AND ('boundary') IN (SELECT (key ) FROM UNNEST(all_tags)) 
AND feature_type != 'line'
ORDER BY ST_AREA(geometry) DESC 
LIMIT 100

    enter image description here

    I filed a bug on the issue tracker: