With BigQuery GIS, this is easy.

First, we need to define what exactly does “bordering” mean? Technically, zip codes are not polygons, but rather mail delivery points and routes. Of course, everybody reduces them to much more convenient polygons. Still, they don’t have to ‘touch’. Let’s start with guesstimate and consider all zip codes within 100 meters of each other as neighbors.

Next, let’s build connection table, by finding all zip codes within 100 meters of each other. Run this query and save the result to a table data.zip_n.



from `

`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 zip.zipcode zip, neighbors.zipcode neighborfrom ` bigquery-public-data.utility_us.zipcode_area ` zip,`bigquery-public-data.utility_us.zipcode_area` neighborswhere st_dwithin(st_geogfromtext(neighbors.zipcode_geom),st_geogfromtext(zip.zipcode_geom), 100) andzip.zipcode <> neighbors.zipcode;

With this table we can quickly find all neighbor zip codes with query like

select neighbor from `data.zip_n` where zip = '98033' order by 1

Finally, let’s validate if the 100 meters was a good choice for threshold. Let’s find all pairs that are within 100 meters, but further away from each other than 1 meter:

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)

Run this query in BigQuery Geo Viz, select ‘my_geo’ as geography visualization column, and examine zip code pairs. You’ll find many pairs like below. It is up to your business logic whether to consider these neighbors and how to choose specific threshold.