Reverse US GeoCoding in BigQuery using public datasets - counties

bigquery_us_reverse_geocoding_counties.sql

# -------------------------------------------------------------------
# Reverse US GeoCoding in BigQuery using public datasets - counties
#
# WARNING: This query uses the OLD public datasets.
# Find the NEW version at:
# https://gist.github.com/edonosotti/f842c0868e663f3beb85133a75bdecca
# -------------------------------------------------------------------

# 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,
  counties.county_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.utility_us.us_county_area` counties
	ON ST_CONTAINS(
		counties.county_geom,
		ST_GEOGPOINT(IconicUSLocations.lon, IconicUSLocations.lat)
	)