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)

--

--

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

151 Followers

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