Reverse US GeoCoding in BigQuery using public datasets - states

bigquery_us_reverse_geocoding_states.sql

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

# 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,
  states.state_name,
  states.state_abbreviation

# 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_states_area` states
	ON ST_CONTAINS(
		states.state_geom,
		ST_GEOGPOINT(IconicUSLocations.lon, IconicUSLocations.lat)
	)