Reverse US GeoCoding in BigQuery using NEW public datasets - cities

bigquery_us_reverse_geocoding_cities_NEW.sql

# ---------------------------------------------------------------
# Reverse US GeoCoding in BigQuery using public datasets - cities
# ---------------------------------------------------------------

# Create a temporary, in memory table
WITH IconicUSLocations AS (
	SELECT 1 AS id, 'Empire State Building' AS name, 40.748170 AS lat, -73.985000 AS lon UNION ALL
	SELECT 2, 'Golden Gate Bridge', 37.810181, -122.477318 UNION ALL
	SELECT 3, 'Yosemite National Park', 37.8532261, -119.6911924 UNION ALL
	SELECT 4, 'Grand Canyon', 36.0997623, -112.1212394 UNION ALL
	SELECT 5, 'Seafood Sam\'s - Sandwich', 41.7721543, -70.5016754
)

# Select the desired fields from the joined tables
SELECT
  IconicUSLocations.name,
  cities.name

# Base table with the list of the iconic places
FROM IconicUSLocations

# Join the table with state data from the public datasets,
# using the ST_CONTAINS() function to match each location
# with the territory which contains its coordinates
JOIN `bigquery-public-data.geo_us_boundaries.urban_areas` cities
	ON ST_CONTAINS(
		cities.urban_area_geom,
		ST_GEOGPOINT(IconicUSLocations.lon, IconicUSLocations.lat)
	)