UM Geospatial Digital Special Collections

Citation: Department of Commerce, U.S. Census Bureau (2022). 2021 Miami-Dade ACS 5 Year Estimates - Block Group Level - CRA [gis dataset]. Department of Commerce. https://www.census.gov/data/developers/data-sets/acs-5year.html. last updated: 2024-10-28 (update frequency: Never). Abstract: A special collection of aggregated columns from the 2021 5-year ACS variables at the census tract level selected for use with the Climate Resilience Academy. Please see the column metadata for the description of the columns. Key words: Miami-Dade County, Census, ACS, 2021, 5-Year Estimates, Tract, Census Tract, Demographic, Population, Climate Resilience, CRA Open Geospatial Consortium Standards: dynamic vector tiles | | WMS (coming soon) | WFS (coming soon) Analytical Functions: gdsc_get_attributes(tablename=mdc_2021_acs_5yr_bg_cra,lat=25.718,lng=-80.279,identifier=geoid,attrs=[total_population,age_17_years_and_younger,persons_between_the_age_of_18_and_64,persons_65_years_old_and_over,households_with_someone_over_65,households_with_someone_18_or_under,limited_english_speaking_households,housing_units_with_no_vehicle_available,households_with_income_less_than_15000_per_year,households_with_income_15000_34999_per_year,households_with_income_35000_49999_per_year,percentage_persons_living_below_poverty_line,total_housing_units,occupied_housing_units,owner_occupied_housing_units,renter_occupied_housing_units,vacant_housing_units,owner_occupied_housing_units_with_mortgage,overcrowded,severely_overcrowded,overcrowded__owner_occupied,overcrowded__renter_occupied,lacking_complete_plumbing_facilities,lacking_complete_kitchen_facilities,median_monthly_housing_costs,median_household_income,median_monthly_owner_costs,median_monthly_renter_costs,median_monthly_owner_costs__percent_household_income,median_monthly_renter_costs__percent_household_income,cost_burdened_owner_occupied_housing_units,cost_burdened_owner_occupied_housing_units_with_mortgage,cost_burdened_owner_occupied_housing_units_without_mortgage,cost_burdened_renter_housing_units,high_school_degree__ged_or_lower,some_college__no_degree,associates_degree,bachelors_degree,graduate_professional_degree],normal='') ETL documentation

ETL for mdc_2021_acs_5yr_bg_cra

######### # GDSC ETL is performed in two steps: shell script to ETL data into postgres # and then additional transformations with SQL in postGIS (if needed). #### ######### # Step 1 - bash script (pseudo code) #### # create directory structure and move into it mkdir -p /data/mdc_2021_acs_5yr_bg_cra/{download,etl} && cd /data/mdc_2021_acs_5yr_bg_cra # set update flag based on last update and update frequency do_update=0 if date() < last_update + update_frequency else do_update = 1 # download specific variables from the census API, reformat census json to csv, # and load as individual tables into postGIS with ogr2ogr. if do_update = 1 then wget -O download/total_population.json "https://api.census.gov/data/2021/acs/acs5? get=B01001_001E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/total_population.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/total_population.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln total_population if do_update = 1 then wget -O download/age_17_years_and_younger.json "https://api.census.gov/data/2021/acs/acs5? get=B01001_003E%2CB01001_004E%2CB01001_005E%2CB01001_006E%2CB01001_027E%2CB01001_028E%2CB01001_029E%2CB01001_030E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/age_17_years_and_younger.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/age_17_years_and_younger.json | sed -e "s/\",\"//9g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln age_17_years_and_younger if do_update = 1 then wget -O download/persons_between_the_age_of_18_and_64.json "https://api.census.gov/data/2021/acs/acs5? get=B01001_007E%2CB01001_008E%2CB01001_009E%2CB01001_010E%2CB01001_011E%2CB01001_012E%2CB01001_013E%2CB01001_014E%2CB01001_015E%2CB01001_016E%2CB01001_017E%2CB01001_018E%2CB01001_019E%2CB01001_031E%2CB01001_032E%2CB01001_033E%2CB01001_034E%2CB01001_035E%2CB01001_036E%2CB01001_037E%2CB01001_038E%2CB01001_039E%2CB01001_040E%2CB01001_041E%2CB01001_042E%2CB01001_043E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/persons_between_the_age_of_18_and_64.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/persons_between_the_age_of_18_and_64.json | sed -e "s/\",\"//27g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln persons_between_the_age_of_18_and_64 if do_update = 1 then wget -O download/persons_65_years_old_and_over.json "https://api.census.gov/data/2021/acs/acs5? get=B01001_020E%2CB01001_021E%2CB01001_022E%2CB01001_023E%2CB01001_024E%2CB01001_025E%2CB01001_044E%2CB01001_045E%2CB01001_046E%2CB01001_047E%2CB01001_048E%2CB01001_049E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/persons_65_years_old_and_over.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/persons_65_years_old_and_over.json | sed -e "s/\",\"//13g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln persons_65_years_old_and_over if do_update = 1 then wget -O download/households_with_someone_over_65.json "https://api.census.gov/data/2021/acs/acs5? get=B09020_002E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/households_with_someone_over_65.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/households_with_someone_over_65.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln households_with_someone_over_65 if do_update = 1 then wget -O download/households_with_someone_18_or_under.json "https://api.census.gov/data/2021/acs/acs5? get=B09002_001E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/households_with_someone_18_or_under.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/households_with_someone_18_or_under.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln households_with_someone_18_or_under if do_update = 1 then wget -O download/limited_english_speaking_households.json "https://api.census.gov/data/2021/acs/acs5? get=C16002_004E%2CC16002_007E%2CC16002_010E%2CC16002_013E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/limited_english_speaking_households.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/limited_english_speaking_households.json | sed -e "s/\",\"//5g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln limited_english_speaking_households if do_update = 1 then wget -O download/housing_units_with_no_vehicle_available.json "https://api.census.gov/data/2021/acs/acs5? get=B25044_003E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/housing_units_with_no_vehicle_available.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/housing_units_with_no_vehicle_available.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln housing_units_with_no_vehicle_available if do_update = 1 then wget -O download/households_with_income_less_than_15000_per_year.json "https://api.census.gov/data/2021/acs/acs5? get=B19001_002E%2CB19001_003E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/households_with_income_less_than_15000_per_year.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/households_with_income_less_than_15000_per_year.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln households_with_income_less_than_15000_per_year if do_update = 1 then wget -O download/households_with_income_15000_34999_per_year.json "https://api.census.gov/data/2021/acs/acs5? get=B19001_004E%2CB19001_005E%2CB19001_006E%2CB19001_007E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/households_with_income_15000_34999_per_year.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/households_with_income_15000_34999_per_year.json | sed -e "s/\",\"//5g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln households_with_income_15000_34999_per_year if do_update = 1 then wget -O download/households_with_income_35000_49999_per_year.json "https://api.census.gov/data/2021/acs/acs5? get=B19001_008E%2CB19001_009E%2CB19001_010E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/households_with_income_35000_49999_per_year.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/households_with_income_35000_49999_per_year.json | sed -e "s/\",\"//4g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln households_with_income_35000_49999_per_year if do_update = 1 then wget -O download/percentage_persons_living_below_poverty_line.json "https://api.census.gov/data/2021/acs/acs5? get=B17001_001E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/percentage_persons_living_below_poverty_line.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/percentage_persons_living_below_poverty_line.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln percentage_persons_living_below_poverty_line if do_update = 1 then wget -O download/total_housing_units.json "https://api.census.gov/data/2021/acs/acs5? get=B25001_001E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/total_housing_units.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/total_housing_units.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln total_housing_units if do_update = 1 then wget -O download/occupied_housing_units.json "https://api.census.gov/data/2021/acs/acs5? get=B25002_002E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/occupied_housing_units.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/occupied_housing_units.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln occupied_housing_units if do_update = 1 then wget -O download/owner_occupied_housing_units.json "https://api.census.gov/data/2021/acs/acs5? get=B25003_002E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/owner_occupied_housing_units.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/owner_occupied_housing_units.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln owner_occupied_housing_units if do_update = 1 then wget -O download/renter_occupied_housing_units.json "https://api.census.gov/data/2021/acs/acs5? get=B25003_003E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/renter_occupied_housing_units.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/renter_occupied_housing_units.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln renter_occupied_housing_units if do_update = 1 then wget -O download/vacant_housing_units.json "https://api.census.gov/data/2021/acs/acs5? get=B25004_001E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/vacant_housing_units.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/vacant_housing_units.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln vacant_housing_units if do_update = 1 then wget -O download/owner_occupied_housing_units_with_mortgage.json "https://api.census.gov/data/2021/acs/acs5? get=B25027_002E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/owner_occupied_housing_units_with_mortgage.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/owner_occupied_housing_units_with_mortgage.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln owner_occupied_housing_units_with_mortgage if do_update = 1 then wget -O download/overcrowded.json "https://api.census.gov/data/2021/acs/acs5? get=B25014_005E%2CB25014_011E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/overcrowded.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/overcrowded.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln overcrowded if do_update = 1 then wget -O download/severely_overcrowded.json "https://api.census.gov/data/2021/acs/acs5? get=B25014_006E%2CB25014_007E%2CB25014_012E%2CB25014_013E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/severely_overcrowded.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/severely_overcrowded.json | sed -e "s/\",\"//5g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln severely_overcrowded if do_update = 1 then wget -O download/overcrowded__owner_occupied.json "https://api.census.gov/data/2021/acs/acs5? get=B25014_005E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/overcrowded__owner_occupied.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/overcrowded__owner_occupied.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln overcrowded__owner_occupied if do_update = 1 then wget -O download/overcrowded__renter_occupied.json "https://api.census.gov/data/2021/acs/acs5? get=B25014_011E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/overcrowded__renter_occupied.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/overcrowded__renter_occupied.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln overcrowded__renter_occupied if do_update = 1 then wget -O download/lacking_complete_plumbing_facilities.json "https://api.census.gov/data/2021/acs/acs5? get=B25047_003E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/lacking_complete_plumbing_facilities.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/lacking_complete_plumbing_facilities.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln lacking_complete_plumbing_facilities if do_update = 1 then wget -O download/lacking_complete_kitchen_facilities.json "https://api.census.gov/data/2021/acs/acs5? get=B25051_003E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/lacking_complete_kitchen_facilities.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/lacking_complete_kitchen_facilities.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln lacking_complete_kitchen_facilities if do_update = 1 then wget -O download/median_monthly_housing_costs.json "https://api.census.gov/data/2021/acs/acs5? get=B25105_001E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/median_monthly_housing_costs.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/median_monthly_housing_costs.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln median_monthly_housing_costs if do_update = 1 then wget -O download/median_household_income.json "https://api.census.gov/data/2021/acs/acs5? get=B19013_001E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/median_household_income.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/median_household_income.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln median_household_income if do_update = 1 then wget -O download/median_monthly_owner_costs.json "https://api.census.gov/data/2021/acs/acs5? get=B25088_001E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/median_monthly_owner_costs.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/median_monthly_owner_costs.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln median_monthly_owner_costs if do_update = 1 then wget -O download/median_monthly_renter_costs.json "https://api.census.gov/data/2021/acs/acs5? get=B25058_001E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/median_monthly_renter_costs.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/median_monthly_renter_costs.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln median_monthly_renter_costs if do_update = 1 then wget -O download/median_monthly_owner_costs__percent_household_income.json "https://api.census.gov/data/2021/acs/acs5? get=B25092_001E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/median_monthly_owner_costs__percent_household_income.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/median_monthly_owner_costs__percent_household_income.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln median_monthly_owner_costs__percent_household_income if do_update = 1 then wget -O download/median_monthly_renter_costs__percent_household_income.json "https://api.census.gov/data/2021/acs/acs5? get=B25071_001E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/median_monthly_renter_costs__percent_household_income.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/median_monthly_renter_costs__percent_household_income.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln median_monthly_renter_costs__percent_household_income if do_update = 1 then wget -O download/cost_burdened_owner_occupied_housing_units.json "https://api.census.gov/data/2021/acs/acs5? get=B25091_008E%2CB25091_009E%2CB25091_010E%2CB25091_011E%2CB25091_019E%2CB25091_020E%2CB25091_021E%2CB25091_022E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/cost_burdened_owner_occupied_housing_units.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/cost_burdened_owner_occupied_housing_units.json | sed -e "s/\",\"//9g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln cost_burdened_owner_occupied_housing_units if do_update = 1 then wget -O download/cost_burdened_owner_occupied_housing_units_with_mortgage.json "https://api.census.gov/data/2021/acs/acs5? get=B25091_008E%2CB25091_009E%2CB25091_010E%2CB25091_011E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/cost_burdened_owner_occupied_housing_units_with_mortgage.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/cost_burdened_owner_occupied_housing_units_with_mortgage.json | sed -e "s/\",\"//5g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln cost_burdened_owner_occupied_housing_units_with_mortgage if do_update = 1 then wget -O download/cost_burdened_owner_occupied_housing_units_without_mortgage.json "https://api.census.gov/data/2021/acs/acs5? get=B25091_019E%2CB25091_020E%2CB25091_021E%2CB25091_022E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/cost_burdened_owner_occupied_housing_units_without_mortgage.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/cost_burdened_owner_occupied_housing_units_without_mortgage.json | sed -e "s/\",\"//5g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln cost_burdened_owner_occupied_housing_units_without_mortgage if do_update = 1 then wget -O download/cost_burdened_renter_housing_units.json "https://api.census.gov/data/2021/acs/acs5? get=B25070_007E%2CB25070_008E%2CB25070_009E%2CB25070_010E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/cost_burdened_renter_housing_units.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/cost_burdened_renter_housing_units.json | sed -e "s/\",\"//5g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln cost_burdened_renter_housing_units if do_update = 1 then wget -O download/high_school_degree__ged_or_lower.json "https://api.census.gov/data/2021/acs/acs5? get=B15003_002E%2CB15003_003E%2CB15003_004E%2CB15003_005E%2CB15003_006E%2CB15003_007E%2CB15003_008E%2CB15003_009E%2CB15003_010E%2CB15003_011E%2CB15003_012E%2CB15003_013E%2CB15003_014E%2CB15003_015E%2CB15003_016E%2CB15003_017E%2CB15003_018E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/high_school_degree__ged_or_lower.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/high_school_degree__ged_or_lower.json | sed -e "s/\",\"//18g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln high_school_degree__ged_or_lower if do_update = 1 then wget -O download/some_college__no_degree.json "https://api.census.gov/data/2021/acs/acs5? get=B15003_019E%2CB15003_020E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/some_college__no_degree.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/some_college__no_degree.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln some_college__no_degree if do_update = 1 then wget -O download/associates_degree.json "https://api.census.gov/data/2021/acs/acs5? get=B15003_021E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/associates_degree.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/associates_degree.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln associates_degree if do_update = 1 then wget -O download/bachelors_degree.json "https://api.census.gov/data/2021/acs/acs5? get=B15003_022E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/bachelors_degree.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/bachelors_degree.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln bachelors_degree if do_update = 1 then wget -O download/graduate_professional_degree.json "https://api.census.gov/data/2021/acs/acs5? get=B15003_023E%2CB15003_024E%2CB15003_025E&key=&for=block+group%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/graduate_professional_degree.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/graduate_professional_degree.json | sed -e "s/\",\"//4g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln graduate_professional_degree # Set datestamp as needed if do_update == 1 then echo $date > datestamp ######### # Step 2 - SQL script (pseudo code): #### # create variable columns in geometry table and join individual variables to the geometries using geoid as the primary key. -- join total_population to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN total_population integer; UPDATE mdc_2021_acs_5yr_bg_cra SET total_population = st.agg FROM (SELECT geoid, (b01001_001e::integer) AS agg FROM total_population) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.total_population IS 'b01001_001e'; DROP TABLE total_population; -- join age_17_years_and_younger to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN age_17_years_and_younger integer; UPDATE mdc_2021_acs_5yr_bg_cra SET age_17_years_and_younger = st.agg FROM (SELECT geoid, (b01001_003e::integer+b01001_004e::integer+b01001_005e::integer+b01001_006e::integer+b01001_027e::integer+b01001_028e::integer+b01001_029e::integer+b01001_030e::integer) AS agg FROM age_17_years_and_younger) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.age_17_years_and_younger IS 'b01001_003e+b01001_004e+b01001_005e+b01001_006e+b01001_027e+b01001_028e+b01001_029e+b01001_030e'; DROP TABLE age_17_years_and_younger; -- join persons_between_the_age_of_18_and_64 to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN persons_between_the_age_of_18_and_64 integer; UPDATE mdc_2021_acs_5yr_bg_cra SET persons_between_the_age_of_18_and_64 = st.agg FROM (SELECT geoid, (b01001_007e::integer+b01001_008e::integer+b01001_009e::integer+b01001_010e::integer+b01001_011e::integer+b01001_012e::integer+b01001_013e::integer+b01001_014e::integer+b01001_015e::integer+b01001_016e::integer+b01001_017e::integer+b01001_018e::integer+b01001_019e::integer+b01001_031e::integer+b01001_032e::integer+b01001_033e::integer+b01001_034e::integer+b01001_035e::integer+b01001_036e::integer+b01001_037e::integer+b01001_038e::integer+b01001_039e::integer+b01001_040e::integer+b01001_041e::integer+b01001_042e::integer+b01001_043e::integer) AS agg FROM persons_between_the_age_of_18_and_64) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.persons_between_the_age_of_18_and_64 IS 'b01001_007e+b01001_008e+b01001_009e+b01001_010e+b01001_011e+b01001_012e+b01001_013e+b01001_014e+b01001_015e+b01001_016e+b01001_017e+b01001_018e+b01001_019e+b01001_031e+b01001_032e+b01001_033e+b01001_034e+b01001_035e+b01001_036e+b01001_037e+b01001_038e+b01001_039e+b01001_040e+b01001_041e+b01001_042e+b01001_043e'; DROP TABLE persons_between_the_age_of_18_and_64; -- join persons_65_years_old_and_over to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN persons_65_years_old_and_over integer; UPDATE mdc_2021_acs_5yr_bg_cra SET persons_65_years_old_and_over = st.agg FROM (SELECT geoid, (b01001_020e::integer+b01001_021e::integer+b01001_022e::integer+b01001_023e::integer+b01001_024e::integer+b01001_025e::integer+b01001_044e::integer+b01001_045e::integer+b01001_046e::integer+b01001_047e::integer+b01001_048e::integer+b01001_049e::integer) AS agg FROM persons_65_years_old_and_over) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.persons_65_years_old_and_over IS 'b01001_020e+b01001_021e+b01001_022e+b01001_023e+b01001_024e+b01001_025e+b01001_044e+b01001_045e+b01001_046e+b01001_047e+b01001_048e+b01001_049e'; DROP TABLE persons_65_years_old_and_over; -- join households_with_someone_over_65 to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN households_with_someone_over_65 integer; UPDATE mdc_2021_acs_5yr_bg_cra SET households_with_someone_over_65 = st.agg FROM (SELECT geoid, (b09020_002e::integer) AS agg FROM households_with_someone_over_65) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.households_with_someone_over_65 IS 'b09020_002e'; DROP TABLE households_with_someone_over_65; -- join households_with_someone_18_or_under to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN households_with_someone_18_or_under integer; UPDATE mdc_2021_acs_5yr_bg_cra SET households_with_someone_18_or_under = st.agg FROM (SELECT geoid, (b09002_001e::integer) AS agg FROM households_with_someone_18_or_under) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.households_with_someone_18_or_under IS 'b09002_001e'; DROP TABLE households_with_someone_18_or_under; -- join limited_english_speaking_households to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN limited_english_speaking_households integer; UPDATE mdc_2021_acs_5yr_bg_cra SET limited_english_speaking_households = st.agg FROM (SELECT geoid, (c16002_004e::integer+c16002_007e::integer+c16002_010e::integer+c16002_013e::integer) AS agg FROM limited_english_speaking_households) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.limited_english_speaking_households IS 'c16002_004e+c16002_007e+c16002_010e+c16002_013e'; DROP TABLE limited_english_speaking_households; -- join housing_units_with_no_vehicle_available to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN housing_units_with_no_vehicle_available integer; UPDATE mdc_2021_acs_5yr_bg_cra SET housing_units_with_no_vehicle_available = st.agg FROM (SELECT geoid, (b25044_003e::integer) AS agg FROM housing_units_with_no_vehicle_available) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.housing_units_with_no_vehicle_available IS 'b25044_003e'; DROP TABLE housing_units_with_no_vehicle_available; -- join households_with_income_less_than_15000_per_year to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN households_with_income_less_than_15000_per_year integer; UPDATE mdc_2021_acs_5yr_bg_cra SET households_with_income_less_than_15000_per_year = st.agg FROM (SELECT geoid, (b19001_002e::integer+b19001_003e::integer) AS agg FROM households_with_income_less_than_15000_per_year) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.households_with_income_less_than_15000_per_year IS 'b19001_002e+b19001_003e'; DROP TABLE households_with_income_less_than_15000_per_year; -- join households_with_income_15000_34999_per_year to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN households_with_income_15000_34999_per_year integer; UPDATE mdc_2021_acs_5yr_bg_cra SET households_with_income_15000_34999_per_year = st.agg FROM (SELECT geoid, (b19001_004e::integer+b19001_005e::integer+b19001_006e::integer+b19001_007e::integer) AS agg FROM households_with_income_15000_34999_per_year) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.households_with_income_15000_34999_per_year IS 'b19001_004e+b19001_005e+b19001_006e+b19001_007e'; DROP TABLE households_with_income_15000_34999_per_year; -- join households_with_income_35000_49999_per_year to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN households_with_income_35000_49999_per_year integer; UPDATE mdc_2021_acs_5yr_bg_cra SET households_with_income_35000_49999_per_year = st.agg FROM (SELECT geoid, (b19001_008e::integer+b19001_009e::integer+b19001_010e::integer) AS agg FROM households_with_income_35000_49999_per_year) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.households_with_income_35000_49999_per_year IS 'b19001_008e+b19001_009e+b19001_010e'; DROP TABLE households_with_income_35000_49999_per_year; -- join percentage_persons_living_below_poverty_line to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN percentage_persons_living_below_poverty_line integer; UPDATE mdc_2021_acs_5yr_bg_cra SET percentage_persons_living_below_poverty_line = st.agg FROM (SELECT geoid, (b17001_001e::integer) AS agg FROM percentage_persons_living_below_poverty_line) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.percentage_persons_living_below_poverty_line IS 'b17001_001e'; DROP TABLE percentage_persons_living_below_poverty_line; -- join total_housing_units to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN total_housing_units integer; UPDATE mdc_2021_acs_5yr_bg_cra SET total_housing_units = st.agg FROM (SELECT geoid, (b25001_001e::integer) AS agg FROM total_housing_units) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.total_housing_units IS 'b25001_001e'; DROP TABLE total_housing_units; -- join occupied_housing_units to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN occupied_housing_units integer; UPDATE mdc_2021_acs_5yr_bg_cra SET occupied_housing_units = st.agg FROM (SELECT geoid, (b25002_002e::integer) AS agg FROM occupied_housing_units) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.occupied_housing_units IS 'b25002_002e'; DROP TABLE occupied_housing_units; -- join owner_occupied_housing_units to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN owner_occupied_housing_units integer; UPDATE mdc_2021_acs_5yr_bg_cra SET owner_occupied_housing_units = st.agg FROM (SELECT geoid, (b25003_002e::integer) AS agg FROM owner_occupied_housing_units) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.owner_occupied_housing_units IS 'b25003_002e'; DROP TABLE owner_occupied_housing_units; -- join renter_occupied_housing_units to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN renter_occupied_housing_units integer; UPDATE mdc_2021_acs_5yr_bg_cra SET renter_occupied_housing_units = st.agg FROM (SELECT geoid, (b25003_003e::integer) AS agg FROM renter_occupied_housing_units) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.renter_occupied_housing_units IS 'b25003_003e'; DROP TABLE renter_occupied_housing_units; -- join vacant_housing_units to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN vacant_housing_units integer; UPDATE mdc_2021_acs_5yr_bg_cra SET vacant_housing_units = st.agg FROM (SELECT geoid, (b25004_001e::integer) AS agg FROM vacant_housing_units) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.vacant_housing_units IS 'b25004_001e'; DROP TABLE vacant_housing_units; -- join owner_occupied_housing_units_with_mortgage to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN owner_occupied_housing_units_with_mortgage integer; UPDATE mdc_2021_acs_5yr_bg_cra SET owner_occupied_housing_units_with_mortgage = st.agg FROM (SELECT geoid, (b25027_002e::integer) AS agg FROM owner_occupied_housing_units_with_mortgage) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.owner_occupied_housing_units_with_mortgage IS 'b25027_002e'; DROP TABLE owner_occupied_housing_units_with_mortgage; -- join overcrowded to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN overcrowded integer; UPDATE mdc_2021_acs_5yr_bg_cra SET overcrowded = st.agg FROM (SELECT geoid, (b25014_005e::integer+b25014_011e::integer) AS agg FROM overcrowded) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.overcrowded IS 'b25014_005e+b25014_011e'; DROP TABLE overcrowded; -- join severely_overcrowded to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN severely_overcrowded integer; UPDATE mdc_2021_acs_5yr_bg_cra SET severely_overcrowded = st.agg FROM (SELECT geoid, (b25014_006e::integer+b25014_007e::integer+b25014_012e::integer+b25014_013e::integer) AS agg FROM severely_overcrowded) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.severely_overcrowded IS 'b25014_006e+b25014_007e+b25014_012e+b25014_013e'; DROP TABLE severely_overcrowded; -- join overcrowded__owner_occupied to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN overcrowded__owner_occupied integer; UPDATE mdc_2021_acs_5yr_bg_cra SET overcrowded__owner_occupied = st.agg FROM (SELECT geoid, (b25014_005e::integer) AS agg FROM overcrowded__owner_occupied) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.overcrowded__owner_occupied IS 'b25014_005e'; DROP TABLE overcrowded__owner_occupied; -- join overcrowded__renter_occupied to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN overcrowded__renter_occupied integer; UPDATE mdc_2021_acs_5yr_bg_cra SET overcrowded__renter_occupied = st.agg FROM (SELECT geoid, (b25014_011e::integer) AS agg FROM overcrowded__renter_occupied) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.overcrowded__renter_occupied IS 'b25014_011e'; DROP TABLE overcrowded__renter_occupied; -- join lacking_complete_plumbing_facilities to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN lacking_complete_plumbing_facilities integer; UPDATE mdc_2021_acs_5yr_bg_cra SET lacking_complete_plumbing_facilities = st.agg FROM (SELECT geoid, (b25047_003e::integer) AS agg FROM lacking_complete_plumbing_facilities) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.lacking_complete_plumbing_facilities IS 'b25047_003e'; DROP TABLE lacking_complete_plumbing_facilities; -- join lacking_complete_kitchen_facilities to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN lacking_complete_kitchen_facilities integer; UPDATE mdc_2021_acs_5yr_bg_cra SET lacking_complete_kitchen_facilities = st.agg FROM (SELECT geoid, (b25051_003e::integer) AS agg FROM lacking_complete_kitchen_facilities) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.lacking_complete_kitchen_facilities IS 'b25051_003e'; DROP TABLE lacking_complete_kitchen_facilities; -- join median_monthly_housing_costs to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN median_monthly_housing_costs integer; UPDATE mdc_2021_acs_5yr_bg_cra SET median_monthly_housing_costs = st.agg FROM (SELECT geoid, (b25105_001e::integer) AS agg FROM median_monthly_housing_costs) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.median_monthly_housing_costs IS 'b25105_001e'; DROP TABLE median_monthly_housing_costs; -- join median_household_income to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN median_household_income integer; UPDATE mdc_2021_acs_5yr_bg_cra SET median_household_income = st.agg FROM (SELECT geoid, (b19013_001e::integer) AS agg FROM median_household_income) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.median_household_income IS 'b19013_001e'; DROP TABLE median_household_income; -- join median_monthly_owner_costs to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN median_monthly_owner_costs integer; UPDATE mdc_2021_acs_5yr_bg_cra SET median_monthly_owner_costs = st.agg FROM (SELECT geoid, (b25088_001e::integer) AS agg FROM median_monthly_owner_costs) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.median_monthly_owner_costs IS 'b25088_001e'; DROP TABLE median_monthly_owner_costs; -- join median_monthly_renter_costs to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN median_monthly_renter_costs integer; UPDATE mdc_2021_acs_5yr_bg_cra SET median_monthly_renter_costs = st.agg FROM (SELECT geoid, (b25058_001e::integer) AS agg FROM median_monthly_renter_costs) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.median_monthly_renter_costs IS 'b25058_001e'; DROP TABLE median_monthly_renter_costs; -- join median_monthly_owner_costs__percent_household_income to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN median_monthly_owner_costs__percent_household_income real; UPDATE mdc_2021_acs_5yr_bg_cra SET median_monthly_owner_costs__percent_household_income = st.agg FROM (SELECT geoid, (b25092_001e::real) AS agg FROM median_monthly_owner_costs__percent_household_income) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.median_monthly_owner_costs__percent_household_income IS 'b25092_001e'; DROP TABLE median_monthly_owner_costs__percent_household_income; -- join median_monthly_renter_costs__percent_household_income to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN median_monthly_renter_costs__percent_household_income real; UPDATE mdc_2021_acs_5yr_bg_cra SET median_monthly_renter_costs__percent_household_income = st.agg FROM (SELECT geoid, (b25071_001e::real) AS agg FROM median_monthly_renter_costs__percent_household_income) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.median_monthly_renter_costs__percent_household_income IS 'b25071_001e'; DROP TABLE median_monthly_renter_costs__percent_household_income; -- join cost_burdened_owner_occupied_housing_units to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN cost_burdened_owner_occupied_housing_units integer; UPDATE mdc_2021_acs_5yr_bg_cra SET cost_burdened_owner_occupied_housing_units = st.agg FROM (SELECT geoid, (b25091_008e::integer+b25091_009e::integer+b25091_010e::integer+b25091_011e::integer+b25091_019e::integer+b25091_020e::integer+b25091_021e::integer+b25091_022e::integer) AS agg FROM cost_burdened_owner_occupied_housing_units) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.cost_burdened_owner_occupied_housing_units IS 'b25091_008e+b25091_009e+b25091_010e+b25091_011e+b25091_019e+b25091_020e+b25091_021e+b25091_022e'; DROP TABLE cost_burdened_owner_occupied_housing_units; -- join cost_burdened_owner_occupied_housing_units_with_mortgage to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN cost_burdened_owner_occupied_housing_units_with_mortgage integer; UPDATE mdc_2021_acs_5yr_bg_cra SET cost_burdened_owner_occupied_housing_units_with_mortgage = st.agg FROM (SELECT geoid, (b25091_008e::integer+b25091_009e::integer+b25091_010e::integer+b25091_011e::integer) AS agg FROM cost_burdened_owner_occupied_housing_units_with_mortgage) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.cost_burdened_owner_occupied_housing_units_with_mortgage IS 'b25091_008e+b25091_009e+b25091_010e+b25091_011e'; DROP TABLE cost_burdened_owner_occupied_housing_units_with_mortgage; -- join cost_burdened_owner_occupied_housing_units_without_mortgage to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN cost_burdened_owner_occupied_housing_units_without_mortgage integer; UPDATE mdc_2021_acs_5yr_bg_cra SET cost_burdened_owner_occupied_housing_units_without_mortgage = st.agg FROM (SELECT geoid, (b25091_019e::integer+b25091_020e::integer+b25091_021e::integer+b25091_022e::integer) AS agg FROM cost_burdened_owner_occupied_housing_units_without_mortgage) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.cost_burdened_owner_occupied_housing_units_without_mortgage IS 'b25091_019e+b25091_020e+b25091_021e+b25091_022e'; DROP TABLE cost_burdened_owner_occupied_housing_units_without_mortgage; -- join cost_burdened_renter_housing_units to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN cost_burdened_renter_housing_units integer; UPDATE mdc_2021_acs_5yr_bg_cra SET cost_burdened_renter_housing_units = st.agg FROM (SELECT geoid, (b25070_007e::integer+b25070_008e::integer+b25070_009e::integer+b25070_010e::integer) AS agg FROM cost_burdened_renter_housing_units) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.cost_burdened_renter_housing_units IS 'b25070_007e+b25070_008e+b25070_009e+b25070_010e'; DROP TABLE cost_burdened_renter_housing_units; -- join high_school_degree__ged_or_lower to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN high_school_degree__ged_or_lower integer; UPDATE mdc_2021_acs_5yr_bg_cra SET high_school_degree__ged_or_lower = st.agg FROM (SELECT geoid, (b15003_002e::integer+b15003_003e::integer+b15003_004e::integer+b15003_005e::integer+b15003_006e::integer+b15003_007e::integer+b15003_008e::integer+b15003_009e::integer+b15003_010e::integer+b15003_011e::integer+b15003_012e::integer+b15003_013e::integer+b15003_014e::integer+b15003_015e::integer+b15003_016e::integer+b15003_017e::integer+b15003_018e::integer) AS agg FROM high_school_degree__ged_or_lower) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.high_school_degree__ged_or_lower IS 'b15003_002e+b15003_003e+b15003_004e+b15003_005e+b15003_006e+b15003_007e+b15003_008e+b15003_009e+b15003_010e+b15003_011e+b15003_012e+b15003_013e+b15003_014e+b15003_015e+b15003_016e+b15003_017e+b15003_018e'; DROP TABLE high_school_degree__ged_or_lower; -- join some_college__no_degree to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN some_college__no_degree integer; UPDATE mdc_2021_acs_5yr_bg_cra SET some_college__no_degree = st.agg FROM (SELECT geoid, (b15003_019e::integer+b15003_020e::integer) AS agg FROM some_college__no_degree) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.some_college__no_degree IS 'b15003_019e+b15003_020e'; DROP TABLE some_college__no_degree; -- join associates_degree to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN associates_degree integer; UPDATE mdc_2021_acs_5yr_bg_cra SET associates_degree = st.agg FROM (SELECT geoid, (b15003_021e::integer) AS agg FROM associates_degree) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.associates_degree IS 'b15003_021e'; DROP TABLE associates_degree; -- join bachelors_degree to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN bachelors_degree integer; UPDATE mdc_2021_acs_5yr_bg_cra SET bachelors_degree = st.agg FROM (SELECT geoid, (b15003_022e::integer) AS agg FROM bachelors_degree) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.bachelors_degree IS 'b15003_022e'; DROP TABLE bachelors_degree; -- join graduate_professional_degree to mdc_2021_acs_5yr_bg_cra ALTER TABLE mdc_2021_acs_5yr_bg_cra ADD COLUMN graduate_professional_degree integer; UPDATE mdc_2021_acs_5yr_bg_cra SET graduate_professional_degree = st.agg FROM (SELECT geoid, (b15003_023e::integer+b15003_024e::integer+b15003_025e::integer) AS agg FROM graduate_professional_degree) st WHERE mdc_2021_acs_5yr_bg_cra.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_bg_cra.graduate_professional_degree IS 'b15003_023e+b15003_024e+b15003_025e'; DROP TABLE graduate_professional_degree; DELETE FROM mdc_2021_acs_5yr_bg_cra WHERE total_population is Null;