-
Notifications
You must be signed in to change notification settings - Fork 1
/
010_county_area_acs.sql
27 lines (27 loc) · 1.16 KB
/
010_county_area_acs.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
#standardSQL
-- Join US county area and geometry data together with American Community Survey 2017 5-year data set.
CREATE
OR REPLACE VIEW public.county_area_acs AS
SELECT
county.state_fips_code,
county.county_fips_code,
CONCAT(county.state_fips_code, county.county_fips_code) as combined_fips_code,
county.county_gnis_code,
county.aff_geo_code,
county.geo_id,
county.county_name,
states.state_name,
states.state_abbreviation,
county.area_land_meters,
county.area_water_meters,
ST_X(ST_CENTROID(county.county_geom)) AS longitude,
ST_Y(ST_CENTROID(county.county_geom)) AS latitude,
county.county_geom,
acs.* except(geo_id),
election.*
FROM
`bigquery-public-data.census_bureau_acs.county_2017_5yr` acs
LEFT JOIN `bigquery-public-data.utility_us.us_county_area` county ON county.geo_id = acs.geo_id
LEFT JOIN `bigquery-public-data.utility_us.us_states_area` states ON county.state_fips_code = states.state_fips_code
LEFT JOIN `covid-project-275201.public.election_context_2018` election ON
safe_cast(county.state_fips_code AS int64) * 1000 + safe_cast(county.county_fips_code AS int64) = election.fips;