Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
# create a CTE with our county data
WITH
counties AS (
SELECT
a.median_income,
b.*
FROM
`bigquery-public-data.census_bureau_acs.county_2018_5yr` a
JOIN
`bigquery-public-data.geo_us_boundaries.counties` b
USING
(geo_id) )
# first we set up our aggregations
SELECT
a.county_fips_code,
a.county_name,
AVG(b.median_income) as neighbor_county_median_income,
a.median_income as target_county_median_income
FROM
counties a
# using a cross join we can join each row of the counties table to itself, using a spatial relationship
CROSS JOIN
counties b
# using st_touches, we can then see which counties touch the target county in each row
WHERE
st_touches(a.county_geom,
b.county_geom)
# make sure to exlcude the target county from the cross join
AND a.county_fips_code != b.county_fips_code
GROUP BY
a.county_fips_code,
a.median_income,
a.county_name
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment