Reverse US GeoCoding in BigQuery using public datasets - zipcodes

bigquery_us_reverse_geocoding_zipcodes.sql

# -------------------------------------------------------------------
# Reverse US GeoCoding in BigQuery using public datasets - zipcodes
#
# WARNING: This query uses the OLD public datasets.
# Find the NEW version at:
# https://gist.github.com/edonosotti/faa1c9dc0ffd402bff6df50ff56cfa50
#
# The NEW version also fixed the `zipcode_geom` column in the
# `zipcode_area` table, incorrectly stored as a STRING type.
# It does not require to be casted to GEOMETRY on-the-fly with the
# ST_GEOGFROMTEXT() function anymore. This was VERY slow, indeed.
# -------------------------------------------------------------------

# 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,
  zipcodes.zipcode,
  zipcodes.state_name,
  zipcodes.city

# 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.zipcode_area` zipcodes
	ON ST_CONTAINS(
		ST_GEOGFROMTEXT(zipcodes.zipcode_geom), # This "cast" is very slow
		ST_GEOGPOINT(IconicUSLocations.lon, IconicUSLocations.lat)
	)