U.S. Department of Commerce, U.S. Census Bureau (2022). 2021 Miami-Dade ACS 5 Year Estimates - Tract Level - CAMP [gis dataset]. Department of Commerce. last updated: 2026-08-18 (update frequency: Never). A special collection of aggregated columns from the 2021 5-year ACS variables at the census tract level selected for use with the Climate and Equity Mapping Platform (CAMP). Please see the column metadata for the description of the columns. Key words: Florida, Census, ACS 2021, 2021, 5-Year Estimates, Tract, Census Tract, Demographic, Housing, Property, Population, CAMP Open Geospatial Consortium Standards:

dynamic vector tiles

Analytical Functions:

gdsc_get_attributes( table, lat, lng, identifier, normal )

ETL documentation

ETL for mdc_2021_acs_5yr_tract_camp

######### # 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_tract_camp/download -p /data/mdc_2021_acs_5yr_tract_camp/etl chmod -R 777 /data/mdc_2021_acs_5yr_tract_camp cd /data/mdc_2021_acs_5yr_tract_camp # set update flag based on last update and update frequency do_update=0 if date() < last_update + update_frequency else do_update = 1 # load fl_2021_tl_tract as dependency (see fl_2021_tl_tract for details). # 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=tract%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/hispanic_population.json" https://api.census.gov/data/2021/acs/acs5?get=B01001I_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/hispanic_population.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/hispanic_population.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln hispanic_population if do_update = 1 then wget -O download/white_population.json" https://api.census.gov/data/2021/acs/acs5?get=B01001A_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/white_population.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/white_population.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln white_population if do_update = 1 then wget -O download/black_population.json" https://api.census.gov/data/2021/acs/acs5?get=B01001B_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/black_population.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/black_population.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln black_population if do_update = 1 then wget -O download/foreign_born.json" https://api.census.gov/data/2021/acs/acs5?get=B05002_013E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/foreign_born.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/foreign_born.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln foreign_born if do_update = 1 then wget -O download/persons_with_disabilities.json" https://api.census.gov/data/2021/acs/acs5?get=B18101_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/persons_with_disabilities.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/persons_with_disabilities.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln persons_with_disabilities 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=tract%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=tract%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=tract%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=tract%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=tract%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=tract%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=tract%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=tract%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/median_household_income.json" https://api.census.gov/data/2021/acs/acs5?get=B19013_001E&key=&for=tract%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/aggregate_household_income.json" https://api.census.gov/data/2021/acs/acs5?get=B19025_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/aggregate_household_income.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/aggregate_household_income.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln aggregate_household_income 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=tract%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=tract%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=tract%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=tract%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=tract%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=tract%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=tract%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=tract%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=tract%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=tract%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=tract%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=tract%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/bedrooms_0.json" https://api.census.gov/data/2021/acs/acs5?get=B25041_002E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/bedrooms_0.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/bedrooms_0.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln bedrooms_0 if do_update = 1 then wget -O download/bedrooms_1.json" https://api.census.gov/data/2021/acs/acs5?get=B25041_003E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/bedrooms_1.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/bedrooms_1.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln bedrooms_1 if do_update = 1 then wget -O download/bedrooms_2.json" https://api.census.gov/data/2021/acs/acs5?get=B25041_004E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/bedrooms_2.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/bedrooms_2.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln bedrooms_2 if do_update = 1 then wget -O download/bedrooms_3.json" https://api.census.gov/data/2021/acs/acs5?get=B25041_005E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/bedrooms_3.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/bedrooms_3.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln bedrooms_3 if do_update = 1 then wget -O download/bedrooms_4_or_more.json" https://api.census.gov/data/2021/acs/acs5?get=B25041_006E%2CB25041_007E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/bedrooms_4_or_more.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/bedrooms_4_or_more.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln bedrooms_4_or_more if do_update = 1 then wget -O download/median_number_of_rooms.json" https://api.census.gov/data/2021/acs/acs5?get=B25018_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/median_number_of_rooms.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/median_number_of_rooms.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln median_number_of_rooms if do_update = 1 then wget -O download/median_year_structure_built.json" https://api.census.gov/data/2021/acs/acs5?get=B25035_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/median_year_structure_built.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/median_year_structure_built.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln median_year_structure_built if do_update = 1 then wget -O download/built_2010_present.json" https://api.census.gov/data/2021/acs/acs5?get=B25034_002E%2CB25034_003E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/built_2010_present.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/built_2010_present.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln built_2010_present if do_update = 1 then wget -O download/built_1990_2009.json" https://api.census.gov/data/2021/acs/acs5?get=B25034_004E%2CB25034_005E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/built_1990_2009.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/built_1990_2009.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln built_1990_2009 if do_update = 1 then wget -O download/built_1970_1989.json" https://api.census.gov/data/2021/acs/acs5?get=B25034_006E%2CB25034_007E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/built_1970_1989.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/built_1970_1989.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln built_1970_1989 if do_update = 1 then wget -O download/built_1950_1969.json" https://api.census.gov/data/2021/acs/acs5?get=B25034_008E%2CB25034_009E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/built_1950_1969.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/built_1950_1969.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln built_1950_1969 if do_update = 1 then wget -O download/built_1940_1949.json" https://api.census.gov/data/2021/acs/acs5?get=B25034_010E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/built_1940_1949.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/built_1940_1949.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln built_1940_1949 if do_update = 1 then wget -O download/built_1939_or_earlier.json" https://api.census.gov/data/2021/acs/acs5?get=B25034_011E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/built_1939_or_earlier.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/built_1939_or_earlier.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln built_1939_or_earlier 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=tract%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_monthly_owner_costs.json" https://api.census.gov/data/2021/acs/acs5?get=B25088_001E&key=&for=tract%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=tract%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=tract%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=tract%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=tract%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=tract%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=tract%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=tract%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 # 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_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN total_population integer; UPDATE mdc_2021_acs_5yr_tract_camp SET total_population = st.agg FROM (SELECT geoid, (b01001_001e::integer) AS agg FROM total_population) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.total_population IS 'b01001_001e'; DROP TABLE total_population; -- join hispanic_population to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN hispanic_population integer; UPDATE mdc_2021_acs_5yr_tract_camp SET hispanic_population = st.agg FROM (SELECT geoid, (b01001i_001e::integer) AS agg FROM hispanic_population) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.hispanic_population IS 'b01001i_001e'; DROP TABLE hispanic_population; -- join white_population to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN white_population integer; UPDATE mdc_2021_acs_5yr_tract_camp SET white_population = st.agg FROM (SELECT geoid, (b01001a_001e::integer) AS agg FROM white_population) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.white_population IS 'b01001a_001e'; DROP TABLE white_population; -- join black_population to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN black_population integer; UPDATE mdc_2021_acs_5yr_tract_camp SET black_population = st.agg FROM (SELECT geoid, (b01001b_001e::integer) AS agg FROM black_population) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.black_population IS 'b01001b_001e'; DROP TABLE black_population; -- join foreign_born to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN foreign_born integer; UPDATE mdc_2021_acs_5yr_tract_camp SET foreign_born = st.agg FROM (SELECT geoid, (b05002_013e::integer) AS agg FROM foreign_born) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.foreign_born IS 'b05002_013e'; DROP TABLE foreign_born; -- join persons_with_disabilities to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN persons_with_disabilities integer; UPDATE mdc_2021_acs_5yr_tract_camp SET persons_with_disabilities = st.agg FROM (SELECT geoid, (b18101_001e::integer) AS agg FROM persons_with_disabilities) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.persons_with_disabilities IS 'b18101_001e'; DROP TABLE persons_with_disabilities; -- join households_with_someone_over_65 to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN households_with_someone_over_65 integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.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_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN households_with_someone_18_or_under integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.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_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN limited_english_speaking_households integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.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_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN housing_units_with_no_vehicle_available integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.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_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN households_with_income_less_than_15000_per_year integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.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_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN households_with_income_15000_34999_per_year integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.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_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN households_with_income_35000_49999_per_year integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.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_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN percentage_persons_living_below_poverty_line integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.percentage_persons_living_below_poverty_line IS 'b17001_001e'; DROP TABLE percentage_persons_living_below_poverty_line; -- join median_household_income to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN median_household_income integer; UPDATE mdc_2021_acs_5yr_tract_camp SET median_household_income = st.agg FROM (SELECT geoid, (b19013_001e::integer) AS agg FROM median_household_income) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.median_household_income IS 'b19013_001e'; DROP TABLE median_household_income; -- join aggregate_household_income to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN aggregate_household_income integer; UPDATE mdc_2021_acs_5yr_tract_camp SET aggregate_household_income = st.agg FROM (SELECT geoid, (b19025_001e::integer) AS agg FROM aggregate_household_income) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.aggregate_household_income IS 'b19025_001e'; DROP TABLE aggregate_household_income; -- join total_housing_units to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN total_housing_units integer; UPDATE mdc_2021_acs_5yr_tract_camp SET total_housing_units = st.agg FROM (SELECT geoid, (b25001_001e::integer) AS agg FROM total_housing_units) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.total_housing_units IS 'b25001_001e'; DROP TABLE total_housing_units; -- join occupied_housing_units to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN occupied_housing_units integer; UPDATE mdc_2021_acs_5yr_tract_camp SET occupied_housing_units = st.agg FROM (SELECT geoid, (b25002_002e::integer) AS agg FROM occupied_housing_units) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.occupied_housing_units IS 'b25002_002e'; DROP TABLE occupied_housing_units; -- join owner_occupied_housing_units to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN owner_occupied_housing_units integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.owner_occupied_housing_units IS 'b25003_002e'; DROP TABLE owner_occupied_housing_units; -- join renter_occupied_housing_units to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN renter_occupied_housing_units integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.renter_occupied_housing_units IS 'b25003_003e'; DROP TABLE renter_occupied_housing_units; -- join vacant_housing_units to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN vacant_housing_units integer; UPDATE mdc_2021_acs_5yr_tract_camp SET vacant_housing_units = st.agg FROM (SELECT geoid, (b25004_001e::integer) AS agg FROM vacant_housing_units) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.vacant_housing_units IS 'b25004_001e'; DROP TABLE vacant_housing_units; -- join owner_occupied_housing_units_with_mortgage to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN owner_occupied_housing_units_with_mortgage integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.owner_occupied_housing_units_with_mortgage IS 'b25027_002e'; DROP TABLE owner_occupied_housing_units_with_mortgage; -- join overcrowded to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN overcrowded integer; UPDATE mdc_2021_acs_5yr_tract_camp SET overcrowded = st.agg FROM (SELECT geoid, (b25014_005e::integer+b25014_011e::integer) AS agg FROM overcrowded) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.overcrowded IS 'b25014_005e+b25014_011e'; DROP TABLE overcrowded; -- join severely_overcrowded to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN severely_overcrowded integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.severely_overcrowded IS 'b25014_006e+b25014_007e+b25014_012e+b25014_013e'; DROP TABLE severely_overcrowded; -- join overcrowded__owner_occupied to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN overcrowded__owner_occupied integer; UPDATE mdc_2021_acs_5yr_tract_camp SET overcrowded__owner_occupied = st.agg FROM (SELECT geoid, (b25014_005e::integer) AS agg FROM overcrowded__owner_occupied) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.overcrowded__owner_occupied IS 'b25014_005e'; DROP TABLE overcrowded__owner_occupied; -- join overcrowded__renter_occupied to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN overcrowded__renter_occupied integer; UPDATE mdc_2021_acs_5yr_tract_camp SET overcrowded__renter_occupied = st.agg FROM (SELECT geoid, (b25014_011e::integer) AS agg FROM overcrowded__renter_occupied) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.overcrowded__renter_occupied IS 'b25014_011e'; DROP TABLE overcrowded__renter_occupied; -- join lacking_complete_plumbing_facilities to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN lacking_complete_plumbing_facilities integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.lacking_complete_plumbing_facilities IS 'b25047_003e'; DROP TABLE lacking_complete_plumbing_facilities; -- join lacking_complete_kitchen_facilities to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN lacking_complete_kitchen_facilities integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.lacking_complete_kitchen_facilities IS 'b25051_003e'; DROP TABLE lacking_complete_kitchen_facilities; -- join bedrooms_0 to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN bedrooms_0 integer; UPDATE mdc_2021_acs_5yr_tract_camp SET bedrooms_0 = st.agg FROM (SELECT geoid, (b25041_002e::integer) AS agg FROM bedrooms_0) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.bedrooms_0 IS 'b25041_002e'; DROP TABLE bedrooms_0; -- join bedrooms_1 to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN bedrooms_1 integer; UPDATE mdc_2021_acs_5yr_tract_camp SET bedrooms_1 = st.agg FROM (SELECT geoid, (b25041_003e::integer) AS agg FROM bedrooms_1) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.bedrooms_1 IS 'b25041_003e'; DROP TABLE bedrooms_1; -- join bedrooms_2 to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN bedrooms_2 integer; UPDATE mdc_2021_acs_5yr_tract_camp SET bedrooms_2 = st.agg FROM (SELECT geoid, (b25041_004e::integer) AS agg FROM bedrooms_2) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.bedrooms_2 IS 'b25041_004e'; DROP TABLE bedrooms_2; -- join bedrooms_3 to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN bedrooms_3 integer; UPDATE mdc_2021_acs_5yr_tract_camp SET bedrooms_3 = st.agg FROM (SELECT geoid, (b25041_005e::integer) AS agg FROM bedrooms_3) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.bedrooms_3 IS 'b25041_005e'; DROP TABLE bedrooms_3; -- join bedrooms_4_or_more to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN bedrooms_4_or_more integer; UPDATE mdc_2021_acs_5yr_tract_camp SET bedrooms_4_or_more = st.agg FROM (SELECT geoid, (b25041_006e::integer+b25041_007e::integer) AS agg FROM bedrooms_4_or_more) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.bedrooms_4_or_more IS 'b25041_006e+b25041_007e'; DROP TABLE bedrooms_4_or_more; -- join median_number_of_rooms to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN median_number_of_rooms real; UPDATE mdc_2021_acs_5yr_tract_camp SET median_number_of_rooms = st.agg FROM (SELECT geoid, (b25018_001e::real) AS agg FROM median_number_of_rooms) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.median_number_of_rooms IS 'b25018_001e'; DROP TABLE median_number_of_rooms; -- join median_year_structure_built to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN median_year_structure_built integer; UPDATE mdc_2021_acs_5yr_tract_camp SET median_year_structure_built = st.agg FROM (SELECT geoid, (b25035_001e::integer) AS agg FROM median_year_structure_built) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.median_year_structure_built IS 'b25035_001e'; DROP TABLE median_year_structure_built; -- join built_2010_present to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN built_2010_present integer; UPDATE mdc_2021_acs_5yr_tract_camp SET built_2010_present = st.agg FROM (SELECT geoid, (b25034_002e::integer+b25034_003e::integer) AS agg FROM built_2010_present) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.built_2010_present IS 'b25034_002e+b25034_003e'; DROP TABLE built_2010_present; -- join built_1990_2009 to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN built_1990_2009 integer; UPDATE mdc_2021_acs_5yr_tract_camp SET built_1990_2009 = st.agg FROM (SELECT geoid, (b25034_004e::integer+b25034_005e::integer) AS agg FROM built_1990_2009) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.built_1990_2009 IS 'b25034_004e+b25034_005e'; DROP TABLE built_1990_2009; -- join built_1970_1989 to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN built_1970_1989 integer; UPDATE mdc_2021_acs_5yr_tract_camp SET built_1970_1989 = st.agg FROM (SELECT geoid, (b25034_006e::integer+b25034_007e::integer) AS agg FROM built_1970_1989) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.built_1970_1989 IS 'b25034_006e+b25034_007e'; DROP TABLE built_1970_1989; -- join built_1950_1969 to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN built_1950_1969 integer; UPDATE mdc_2021_acs_5yr_tract_camp SET built_1950_1969 = st.agg FROM (SELECT geoid, (b25034_008e::integer+b25034_009e::integer) AS agg FROM built_1950_1969) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.built_1950_1969 IS 'b25034_008e+b25034_009e'; DROP TABLE built_1950_1969; -- join built_1940_1949 to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN built_1940_1949 integer; UPDATE mdc_2021_acs_5yr_tract_camp SET built_1940_1949 = st.agg FROM (SELECT geoid, (b25034_010e::integer) AS agg FROM built_1940_1949) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.built_1940_1949 IS 'b25034_010e'; DROP TABLE built_1940_1949; -- join built_1939_or_earlier to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN built_1939_or_earlier integer; UPDATE mdc_2021_acs_5yr_tract_camp SET built_1939_or_earlier = st.agg FROM (SELECT geoid, (b25034_011e::integer) AS agg FROM built_1939_or_earlier) st WHERE mdc_2021_acs_5yr_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.built_1939_or_earlier IS 'b25034_011e'; DROP TABLE built_1939_or_earlier; -- join median_monthly_housing_costs to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN median_monthly_housing_costs integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.median_monthly_housing_costs IS 'b25105_001e'; DROP TABLE median_monthly_housing_costs; -- join median_monthly_owner_costs to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN median_monthly_owner_costs integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.median_monthly_owner_costs IS 'b25088_001e'; DROP TABLE median_monthly_owner_costs; -- join median_monthly_renter_costs to mdc_2021_acs_5yr_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN median_monthly_renter_costs integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.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_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN median_monthly_owner_costs__percent_household_income real; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.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_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN median_monthly_renter_costs__percent_household_income real; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.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_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN cost_burdened_owner_occupied_housing_units integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.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_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN cost_burdened_owner_occupied_housing_units_with_mortgage integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.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_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN cost_burdened_owner_occupied_housing_units_without_mortgage integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.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_tract_camp ALTER TABLE mdc_2021_acs_5yr_tract_camp ADD COLUMN cost_burdened_renter_housing_units integer; UPDATE mdc_2021_acs_5yr_tract_camp 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_tract_camp.geoid = st.geoid; COMMENT ON COLUMN mdc_2021_acs_5yr_tract_camp.cost_burdened_renter_housing_units IS 'b25070_007e+b25070_008e+b25070_009e+b25070_010e'; DROP TABLE cost_burdened_renter_housing_units; DELETE FROM mdc_2021_acs_5yr_tract_camp WHERE total_population is Null; # add local geometry column and reproject existing geometries into local EPSG: ALTER TABLE mdc_2021_acs_5yr_tract_camp DROP COLUMN IF EXISTS geom_local CASCADE; SELECT AddGeometryColumn ( 'mdc_2021_acs_5yr_tract_camp', 'geom_local', 2236, 'multipolygon', 2 ); UPDATE mdc_2021_acs_5yr_tract_camp SET geom_local=ST_MakeValid(ST_RemoveRepeatedPoints(ST_Transform(ST_Multi(geom),2236))); CREATE INDEX mdc_2021_acs_5yr_tract_camp_geom_local_idx ON mdc_2021_acs_5yr_tract_camp USING GIST (geom_local); NOTIFY pgrst, 'reload schema';