Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save edonosotti/f842c0868e663f3beb85133a75bdecca to your computer and use it in GitHub Desktop.
Save edonosotti/f842c0868e663f3beb85133a75bdecca to your computer and use it in GitHub Desktop.
Reverse US GeoCoding in BigQuery using NEW public datasets - counties
# -----------------------------------------------------------------
# Reverse US GeoCoding in BigQuery using public datasets - counties
# -----------------------------------------------------------------
# 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.geo_us_boundaries.counties` counties
ON ST_CONTAINS(
counties.county_geom,
ST_GEOGPOINT(IconicUSLocations.lon, IconicUSLocations.lat)
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment