Skip to content

Instantly share code, notes, and snippets.

@janbenetka
Created November 16, 2021 01:35
Show Gist options
  • Save janbenetka/9108d25037c3e99113e2661696880bc8 to your computer and use it in GitHub Desktop.
Save janbenetka/9108d25037c3e99113e2661696880bc8 to your computer and use it in GitHub Desktop.
[Adjacent tracts SQL] #bigquery #sql #geo
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment