[Adjacent tracts SQL] #bigquery #sql #geo

find_adjacent_tracts.sql

CREATE OR REPLACE TABLE `uc-prox-core-dev.z_jan_retained.adjacent_tracts` AS

WITH adjacent_tracts AS (
  SELECT a.fips, b.fips as fips_adjacent, a.state_name, b.state_name as state_name_adjacent, a.county_name, b.county_name as county_name_adjacent, a.geog, b.geog as geog_adjacent,
  FROM `uc-atlas.maps_us.census_tracts` a, `uc-atlas.maps_us.census_tracts` b
  WHERE ST_INTERSECTS(ST_BUFFER(a.geog, 25), b.geog)
)

SELECT * FROM adjacent_tracts