Given a zip code, find zip codes immediately bordering it

select zip.zipcode zip, neighbors.zipcode neighbor
from `bigquery-public-data.utility_us.zipcode_area` zip,
`bigquery-public-data.utility_us.zipcode_area` neighbors
where st_dwithin(st_geogfromtext(neighbors.zipcode_geom),
st_geogfromtext(zip.zipcode_geom), 100) and
zip.zipcode <> neighbors.zipcode;
select neighbor from `data.zip_n` where zip = '98033' order by 1
select zip.zipcode zip, neighbors.zipcode neighbor,
st_geogfromtext(zip.zipcode_geom) my_geo,
st_geogfromtext(neighbors.zipcode_geom) nb_geo
from `bigquery-public-data.utility_us.zipcode_area` zip,
`bigquery-public-data.utility_us.zipcode_area` neighbors
where st_dwithin(st_geogfromtext(neighbors.zipcode_geom),
st_geogfromtext(zip.zipcode_geom), 100) and
zip.zipcode <> neighbors.zipcode and
not st_dwithin(st_geogfromtext(neighbors.zipcode_geom),
st_geogfromtext(zip.zipcode_geom), 1)

--

--

--

Hi, I'm TL of BigQuery Geospatial project. Posting small recipes and various notes for BQ Geospatial users.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Predicting the Future of the Economy with Machine Learning

Digital Analytics Scholarship @ CXL Institute — 6th Week Review

Why software teams need to look beyond “user-centered” when referring to ML or AI-driven data…

Why I decided to pursue a career in Data Science

Viziflu 2 release

Top 10 Visuals in Power BI for July 2020

Automating Mundane Web-Based Tasks With Selenium and Heroku

Introducing Watson Machine Learning Server 1.0

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Michael Entin

Michael Entin

Hi, I'm TL of BigQuery Geospatial project. Posting small recipes and various notes for BQ Geospatial users.

More from Medium

Leveraging Geospatial Framework in Big Data Analytics

Optimising the Location of Food Delivery Couriers Using Geopandas and OpenStreetMap

Mapping changes in Water Bodies of Bengaluru City with Sentinel-1 Data

Introduction to PostGIS