Bigquery GIS , how to group coordinates per country using Openstreetmap dataset

Question

I am working with a openstreetmap dataset in Bigquery, I made a query that return a list like this

here is the query I am using

SELECT
  ar.key,
  ar.value,
  osm_id,
  osm_way_id,
  ST_CENTROID(geometry) AS center_location
FROM
  `bigquery-public-data.geo_openstreetmap.planet_features`,
  UNNEST(all_tags) AS ar
WHERE
 ('amenity') in (
  SELECT
    (key)
  FROM
    UNNEST(all_tags))
    or
     (('suburb') in (
  SELECT
    (value)
  FROM
    UNNEST(all_tags)) or
    ('city') in (
  SELECT
    (value)
  FROM
    UNNEST(all_tags))
    or ('town') in (
  SELECT
    (value)
  FROM
    UNNEST(all_tags))
or ('village') in (
  SELECT
    (value)
  FROM
    UNNEST(all_tags)))

my question is let's say I have another table with Country, geometry as a polygon

how I join the field center location to return the name country ( if the point is inside a polygon, then it belong to a country)

Answer 1

let's say I have another table with Country, geometry as a polygon how I join the field center location to return the name country ( if the point is inside a polygon, then it belong to a country)

Spatial JOINs are joins of two tables with a predicate geographic function in the WHERE clause. For example:

-- how many stations within 1 mile range of each zip code?
SELECT
  zip_code AS zip,
  ANY_VALUE(zip_code_geom) AS polygon,
  COUNT(*) AS bike_stations
FROM
  `bigquery-public-data.new_york.citibike_stations` AS bike_stations,
  `bigquery-public-data.geo_us_boundaries.zip_codes` AS zip_codes
WHERE ST_DWithin(
  zip_codes.zip_code_geom,
  ST_GeogPoint(bike_stations.longitude, bike_stations.latitude),
  1609.34)
GROUP BY zip
ORDER BY bike_stations DESC   

you can see more here - Using JOINs with spatial data