mdc_2022_acs_5yr_tract

gdsc

2022 Miami-Dade ACS 5 Year Estimates - Tract Level - CCE

Collections: us-census | climate-and-equity-mapping-program | miami-affordability-project

U.S. Department of Commerce, U.S. Census Bureau, Geography Division, Spatial Data Collection and Products Branch (2023). 2022 Miami-Dade ACS 5 Year Estimates - Tract Level - CCE [gis dataset]. Department of Commerce. https://www.census.gov/data/developers/data-sets/acs-5year.html. last updated: 0001-01-01 (update frequency: Never). A special collection of aggregated columns from the 2022 5-year ACS variables at the census tract level selected for use with the University of Miami Office of Community and Civic Engagement (CCE). Please see the column metadata for the description of the columns. Key words: Florida, Census, ACS 2022, 2022, 5-Year Estimates, Tract, Census Tract, Demographic, Housing, Property, Population, CAMP

Attributes:

Name	Description	Type	Unit
pop_size	Total Population	integer	People
his_pop_size	Total Hispanic Population	integer	People
his_pop_size_pct	Hispanic Percentage of Population	real	Percentage
wh_pop_size	Total White Population	integer	People
wh_pop_size_pct	Hispanic Percentage of Population	real	Percentage
blk_pop_size	Total Black Population	integer	People
blk_pop_size_pct	Black Percentage of Population	real	Percentage
fb_pop_size	Total Foreign Born Population	integer	People
fb_pop_size_pct	Foreign Born Percentage of Population	real	Percentage
pwd_pop_size	Total Population of Persons with Disabilities	integer	People
pwd_pop_size_pct	Percentage of Persons with Disabilities in Total Population	real	Percentage
ppl_over_65	Households with Someone 65 or Over	integer	Households
ppl_over_65_pct	Percentage of Households with Someone 65 or Over	real	Percentage
ppl_under_18	Households with Someone 18 or Under	integer	Households
ppl_under_18_pct	Percentage of Households with Someone 18 or Under	real	Percentage
lim_eng_hh	Limited English-Speaking Households	integer	People
lim_eng_hh_pct	Percentage of Limited English-Speaking Households	real	Percentage
renter_pop	Number of Renter Occupied Units	integer	Renters
renter_pop_pct	Percentage of households occupied by renters	real	Percentage
owner_pop	Number of Owner Occupied Units	integer	Owners
owner_pop_pct	Percentage of Owner Occupied Units	real	Percentage
owner_with_mortgage_pop	Number of Owner Occupied Units with a Mortgage	integer	People
owner_with_mortgage_pop_pct	Percentage of Owner Occupied Units with a Mortgage	real	Percentage
owner_without_mortgage_pop	Number of Owner Occupied Units without a Mortgage	integer	People
owner_without_mortgage_pop_pct	Percentage of Owner Occupied Units without a Mortgage	real	Percentage
twentyfive_plus_pop_nohs	Total Population 25 years and over with less than a high school degree	integer	People
twentyfive_plus_pop_nohs_pct	Percentage of Total Population 25 years and over with less than a high school degree	real	Percentage
twentyfive_plus_pop_hs	Total Population 25 years and over with a high school degree	integer	People
twentyfive_plus_pop_hs_pct	Percentage of Total Population 25 years and over with a high school degree	real	Percentage
twentyfive_plus_pop_sc	Total Population 25 years and over with some college but no degree	integer	People
twentyfive_plus_pop_sc_pct	Percentage of Total Population 25 years and over with some college but no degree	real	Percentage
twentyfive_plus_pop_aa	Total Population 25 years and over with an associate's degree	integer	People
twentyfive_plus_pop_aa_pct	Percentage of Total Population 25 years and over with an associate's degree	real	Percentage
twentyfive_plus_pop_b	Total Population 25 years and over with a Bachelor's degree	integer	People
twentyfive_plus_pop_b_pct	Percentage of Total Population 25 years and over with a Bachelor's degree	real	Percentage
twentyfive_plus_pop_gop	Total Population 25 years and over with a Graduate or professional degree	integer	People
twentyfive_plus_pop_gop_pct	Percentage of Total Population 25 years and over with a Graduate or professional degree	real	Percentage
hh_size	Total Household size	real	People
avg_inc	Median Household Income	real	Dollars
cb_owner_units	Cost-Burdened Owner-Occupied Housing Units	integer	Households
cb_owner_units_pct	Percentage of Cost-Burdened Owner-Occupied Housing Units	real	Percentage
cb_owner_with_mortgage_units	Cost-Burdened Owner-Occupied Housing Units with a Mortgage	integer	Households with Mortgage
cb_owner_with_mortgage_units_pct	Percentage of Cost-Burdened Owner-Occupied Housing Units with a Mortgage	real	Percentage
cb_owner_without_mortgage_units	Cost-Burdened Owner-Occupied Housing Units without a Mortgage	integer	Households
cb_owner_without_mortgage_units_pct	Percentage of Cost-Burdened Owner-Occupied Housing Units without a Mortgage	real	Percentage
cb_renter	Cost-Burdened Renter Housing Units	integer	Rental Units
cb_renter_pct	Percentage of Cost-Burdened Renter Housing Units	real	Percentage

Open Geospatial Consortium Standards: dynamic vector tiles Analytical Functions: gdsc_get_attributes(tablename=mdc_2022_acs_5yr_tract_cce,lat=25.718,lng=-80.279,identifier=geoid,normal='')

######### # 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_2022_acs_5yr_tract_cce/{download,etl} && cd /data/mdc_2022_acs_5yr_tract_cce # 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_2022_tl_tract as dependency (see fl_2022_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/pop_size.json" https://api.census.gov/data/2022/acs/acs5?get=B01003_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/pop_size.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/pop_size.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln pop_size if do_update = 1 then wget -O download/his_pop_size.json" https://api.census.gov/data/2022/acs/acs5?get=B03002_012E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/his_pop_size.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/his_pop_size.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln his_pop_size if do_update = 1 then wget -O download/his_pop_size_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B03002_012E%2CB01003_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/his_pop_size_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/his_pop_size_pct.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln his_pop_size_pct if do_update = 1 then wget -O download/wh_pop_size.json" https://api.census.gov/data/2022/acs/acs5?get=B03002_003E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/wh_pop_size.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/wh_pop_size.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln wh_pop_size if do_update = 1 then wget -O download/wh_pop_size_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B03002_003E%2CB01003_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/wh_pop_size_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/wh_pop_size_pct.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln wh_pop_size_pct if do_update = 1 then wget -O download/blk_pop_size.json" https://api.census.gov/data/2022/acs/acs5?get=B03002_004E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/blk_pop_size.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/blk_pop_size.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln blk_pop_size if do_update = 1 then wget -O download/blk_pop_size_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B03002_004E%2CB01003_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/blk_pop_size_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/blk_pop_size_pct.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln blk_pop_size_pct if do_update = 1 then wget -O download/fb_pop_size.json" https://api.census.gov/data/2022/acs/acs5?get=B05002_013E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/fb_pop_size.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/fb_pop_size.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln fb_pop_size if do_update = 1 then wget -O download/fb_pop_size_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B05002_013E%2CB01003_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/fb_pop_size_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/fb_pop_size_pct.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln fb_pop_size_pct if do_update = 1 then wget -O download/pwd_pop_size.json" https://api.census.gov/data/2022/acs/acs5?get=B18101_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/pwd_pop_size.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/pwd_pop_size.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln pwd_pop_size if do_update = 1 then wget -O download/pwd_pop_size_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B18101_001E%2CB01003_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/pwd_pop_size_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/pwd_pop_size_pct.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln pwd_pop_size_pct if do_update = 1 then wget -O download/ppl_over_65.json" https://api.census.gov/data/2022/acs/acs5?get=B11007_002E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/ppl_over_65.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/ppl_over_65.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln ppl_over_65 if do_update = 1 then wget -O download/ppl_over_65_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B11007_002E%2CB11007_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/ppl_over_65_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/ppl_over_65_pct.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln ppl_over_65_pct if do_update = 1 then wget -O download/ppl_under_18.json" https://api.census.gov/data/2022/acs/acs5?get=B11005_002E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/ppl_under_18.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/ppl_under_18.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln ppl_under_18 if do_update = 1 then wget -O download/ppl_under_18_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B11005_002E%2CB11005_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/ppl_under_18_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/ppl_under_18_pct.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln ppl_under_18_pct if do_update = 1 then wget -O download/lim_eng_hh.json" https://api.census.gov/data/2022/acs/acs5?get=B16002_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/lim_eng_hh.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/lim_eng_hh.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln lim_eng_hh if do_update = 1 then wget -O download/lim_eng_hh_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B16002_001E%2CB01003_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/lim_eng_hh_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/lim_eng_hh_pct.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln lim_eng_hh_pct if do_update = 1 then wget -O download/renter_pop.json" https://api.census.gov/data/2022/acs/acs5?get=B25003_003E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/renter_pop.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/renter_pop.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln renter_pop if do_update = 1 then wget -O download/renter_pop_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B25003_003E%2CB25003_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/renter_pop_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/renter_pop_pct.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln renter_pop_pct if do_update = 1 then wget -O download/owner_pop.json" https://api.census.gov/data/2022/acs/acs5?get=B25003_002E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/owner_pop.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/owner_pop.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln owner_pop if do_update = 1 then wget -O download/owner_pop_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B25003_002E%2CB25003_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/owner_pop_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/owner_pop_pct.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln owner_pop_pct if do_update = 1 then wget -O download/owner_with_mortgage_pop.json" https://api.census.gov/data/2022/acs/acs5?get=B25081_002E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/owner_with_mortgage_pop.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/owner_with_mortgage_pop.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln owner_with_mortgage_pop if do_update = 1 then wget -O download/owner_with_mortgage_pop_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B25081_002E%2CB25003_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/owner_with_mortgage_pop_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/owner_with_mortgage_pop_pct.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln owner_with_mortgage_pop_pct if do_update = 1 then wget -O download/owner_without_mortgage_pop.json" https://api.census.gov/data/2022/acs/acs5?get=B25027_010E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/owner_without_mortgage_pop.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/owner_without_mortgage_pop.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln owner_without_mortgage_pop if do_update = 1 then wget -O download/owner_without_mortgage_pop_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B25027_010E%2CB25003_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/owner_without_mortgage_pop_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/owner_without_mortgage_pop_pct.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln owner_without_mortgage_pop_pct if do_update = 1 then wget -O download/twentyfive_plus_pop_nohs.json" https://api.census.gov/data/2022/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&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/twentyfive_plus_pop_nohs.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/twentyfive_plus_pop_nohs.json | sed -e "s/\",\"//16g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln twentyfive_plus_pop_nohs if do_update = 1 then wget -O download/twentyfive_plus_pop_nohs_pct.json" https://api.census.gov/data/2022/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_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/twentyfive_plus_pop_nohs_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/twentyfive_plus_pop_nohs_pct.json | sed -e "s/\",\"//17g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln twentyfive_plus_pop_nohs_pct if do_update = 1 then wget -O download/twentyfive_plus_pop_hs.json" https://api.census.gov/data/2022/acs/acs5?get=B15003_017E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/twentyfive_plus_pop_hs.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/twentyfive_plus_pop_hs.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln twentyfive_plus_pop_hs if do_update = 1 then wget -O download/twentyfive_plus_pop_hs_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B15003_017E%2CB15003_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/twentyfive_plus_pop_hs_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/twentyfive_plus_pop_hs_pct.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln twentyfive_plus_pop_hs_pct if do_update = 1 then wget -O download/twentyfive_plus_pop_sc.json" https://api.census.gov/data/2022/acs/acs5?get=B15003_019E%2CB15003_020E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/twentyfive_plus_pop_sc.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/twentyfive_plus_pop_sc.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln twentyfive_plus_pop_sc if do_update = 1 then wget -O download/twentyfive_plus_pop_sc_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B15003_019E%2CB15003_020E%2CB15003_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/twentyfive_plus_pop_sc_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/twentyfive_plus_pop_sc_pct.json | sed -e "s/\",\"//4g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln twentyfive_plus_pop_sc_pct if do_update = 1 then wget -O download/twentyfive_plus_pop_aa.json" https://api.census.gov/data/2022/acs/acs5?get=B15003_021E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/twentyfive_plus_pop_aa.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/twentyfive_plus_pop_aa.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln twentyfive_plus_pop_aa if do_update = 1 then wget -O download/twentyfive_plus_pop_aa_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B15003_021E%2CB15003_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/twentyfive_plus_pop_aa_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/twentyfive_plus_pop_aa_pct.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln twentyfive_plus_pop_aa_pct if do_update = 1 then wget -O download/twentyfive_plus_pop_b.json" https://api.census.gov/data/2022/acs/acs5?get=B15003_022E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/twentyfive_plus_pop_b.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/twentyfive_plus_pop_b.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln twentyfive_plus_pop_b if do_update = 1 then wget -O download/twentyfive_plus_pop_b_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B15003_022E%2CB15003_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/twentyfive_plus_pop_b_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/twentyfive_plus_pop_b_pct.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln twentyfive_plus_pop_b_pct if do_update = 1 then wget -O download/twentyfive_plus_pop_gop.json" https://api.census.gov/data/2022/acs/acs5?get=B15003_023E%2CB15003_024E%2CB15003_025E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/twentyfive_plus_pop_gop.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/twentyfive_plus_pop_gop.json | sed -e "s/\",\"//4g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln twentyfive_plus_pop_gop if do_update = 1 then wget -O download/twentyfive_plus_pop_gop_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B15003_023E%2CB15003_024E%2CB15003_025E%2CB15003_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/twentyfive_plus_pop_gop_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/twentyfive_plus_pop_gop_pct.json | sed -e "s/\",\"//5g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln twentyfive_plus_pop_gop_pct if do_update = 1 then wget -O download/hh_size.json" https://api.census.gov/data/2022/acs/acs5?get=B25010_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/hh_size.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/hh_size.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln hh_size if do_update = 1 then wget -O download/avg_inc.json" https://api.census.gov/data/2022/acs/acs5?get=B19013_001E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/avg_inc.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/avg_inc.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln avg_inc if do_update = 1 then wget -O download/cb_owner_units.json" https://api.census.gov/data/2022/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/cb_owner_units.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/cb_owner_units.json | sed -e "s/\",\"//9g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln cb_owner_units if do_update = 1 then wget -O download/cb_owner_units_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B25091_008E%2CB25091_009E%2CB25091_010E%2CB25091_011E%2CB25091_019E%2CB25091_020E%2CB25091_021E%2CB25091_022E%2CB25081_002E%2CB25027_010E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/cb_owner_units_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/cb_owner_units_pct.json | sed -e "s/\",\"//11g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln cb_owner_units_pct if do_update = 1 then wget -O download/cb_owner_with_mortgage_units.json" https://api.census.gov/data/2022/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/cb_owner_with_mortgage_units.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/cb_owner_with_mortgage_units.json | sed -e "s/\",\"//5g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln cb_owner_with_mortgage_units if do_update = 1 then wget -O download/cb_owner_with_mortgage_units_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B25091_008E%2CB25091_009E%2CB25091_010E%2CB25091_011E%2CB25091_002E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/cb_owner_with_mortgage_units_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/cb_owner_with_mortgage_units_pct.json | sed -e "s/\",\"//6g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln cb_owner_with_mortgage_units_pct if do_update = 1 then wget -O download/cb_owner_without_mortgage_units.json" https://api.census.gov/data/2022/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/cb_owner_without_mortgage_units.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/cb_owner_without_mortgage_units.json | sed -e "s/\",\"//5g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln cb_owner_without_mortgage_units if do_update = 1 then wget -O download/cb_owner_without_mortgage_units_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B25091_019E%2CB25091_020E%2CB25091_021E%2CB25091_022E%2CB25091_013E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/cb_owner_without_mortgage_units_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/cb_owner_without_mortgage_units_pct.json | sed -e "s/\",\"//6g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln cb_owner_without_mortgage_units_pct if do_update = 1 then wget -O download/cb_renter.json" https://api.census.gov/data/2022/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/cb_renter.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/cb_renter.json | sed -e "s/\",\"//5g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln cb_renter if do_update = 1 then wget -O download/cb_renter_pct.json" https://api.census.gov/data/2022/acs/acs5?get=B25070_007E%2CB25070_008E%2CB25070_009E%2CB25070_010E%2CB25003_003E&key=&for=tract%3A%2A&in=state%3A12&in=county%3A086" cols=$(head -n 1 download/cb_renter_pct.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/cb_renter_pct.json | sed -e "s/\",\"//6g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln cb_renter_pct # 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 pop_size to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN pop_size integer; UPDATE mdc_2022_acs_5yr_tract_cce SET pop_size = st.agg FROM (SELECT geoid, (b01003_001e::integer) AS agg FROM pop_size) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.pop_size IS 'b01003_001e'; DROP TABLE pop_size; -- join his_pop_size to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN his_pop_size integer; UPDATE mdc_2022_acs_5yr_tract_cce SET his_pop_size = st.agg FROM (SELECT geoid, (b03002_012e::integer) AS agg FROM his_pop_size) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.his_pop_size IS 'b03002_012e'; DROP TABLE his_pop_size; -- join his_pop_size_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN his_pop_size_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET his_pop_size_pct = st.agg FROM (SELECT geoid, (b03002_012e::real)/(b01003_001e::real) AS agg FROM his_pop_size_pct WHERE (b01003_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.his_pop_size_pct IS 'b03002_012e'; DROP TABLE his_pop_size_pct; -- join wh_pop_size to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN wh_pop_size integer; UPDATE mdc_2022_acs_5yr_tract_cce SET wh_pop_size = st.agg FROM (SELECT geoid, (b03002_003e::integer) AS agg FROM wh_pop_size) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.wh_pop_size IS 'b03002_003e'; DROP TABLE wh_pop_size; -- join wh_pop_size_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN wh_pop_size_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET wh_pop_size_pct = st.agg FROM (SELECT geoid, (b03002_003e::real)/(b01003_001e::real) AS agg FROM wh_pop_size_pct WHERE (b01003_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.wh_pop_size_pct IS 'b03002_003e'; DROP TABLE wh_pop_size_pct; -- join blk_pop_size to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN blk_pop_size integer; UPDATE mdc_2022_acs_5yr_tract_cce SET blk_pop_size = st.agg FROM (SELECT geoid, (b03002_004e::integer) AS agg FROM blk_pop_size) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.blk_pop_size IS 'b03002_004e'; DROP TABLE blk_pop_size; -- join blk_pop_size_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN blk_pop_size_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET blk_pop_size_pct = st.agg FROM (SELECT geoid, (b03002_004e::real)/(b01003_001e::real) AS agg FROM blk_pop_size_pct WHERE (b01003_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.blk_pop_size_pct IS 'b03002_004e'; DROP TABLE blk_pop_size_pct; -- join fb_pop_size to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN fb_pop_size integer; UPDATE mdc_2022_acs_5yr_tract_cce SET fb_pop_size = st.agg FROM (SELECT geoid, (b05002_013e::integer) AS agg FROM fb_pop_size) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.fb_pop_size IS 'b05002_013e'; DROP TABLE fb_pop_size; -- join fb_pop_size_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN fb_pop_size_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET fb_pop_size_pct = st.agg FROM (SELECT geoid, (b05002_013e::real)/(b01003_001e::real) AS agg FROM fb_pop_size_pct WHERE (b01003_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.fb_pop_size_pct IS 'b05002_013e'; DROP TABLE fb_pop_size_pct; -- join pwd_pop_size to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN pwd_pop_size integer; UPDATE mdc_2022_acs_5yr_tract_cce SET pwd_pop_size = st.agg FROM (SELECT geoid, (b18101_001e::integer) AS agg FROM pwd_pop_size) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.pwd_pop_size IS 'b18101_001e'; DROP TABLE pwd_pop_size; -- join pwd_pop_size_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN pwd_pop_size_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET pwd_pop_size_pct = st.agg FROM (SELECT geoid, (b18101_001e::real)/(b01003_001e::real) AS agg FROM pwd_pop_size_pct WHERE (b01003_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.pwd_pop_size_pct IS 'b18101_001e'; DROP TABLE pwd_pop_size_pct; -- join ppl_over_65 to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN ppl_over_65 integer; UPDATE mdc_2022_acs_5yr_tract_cce SET ppl_over_65 = st.agg FROM (SELECT geoid, (b11007_002e::integer) AS agg FROM ppl_over_65) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.ppl_over_65 IS 'b11007_002e'; DROP TABLE ppl_over_65; -- join ppl_over_65_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN ppl_over_65_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET ppl_over_65_pct = st.agg FROM (SELECT geoid, (b11007_002e::real)/(b11007_001e::real) AS agg FROM ppl_over_65_pct WHERE (b11007_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.ppl_over_65_pct IS 'b11007_002e'; DROP TABLE ppl_over_65_pct; -- join ppl_under_18 to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN ppl_under_18 integer; UPDATE mdc_2022_acs_5yr_tract_cce SET ppl_under_18 = st.agg FROM (SELECT geoid, (b11005_002e::integer) AS agg FROM ppl_under_18) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.ppl_under_18 IS 'b11005_002e'; DROP TABLE ppl_under_18; -- join ppl_under_18_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN ppl_under_18_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET ppl_under_18_pct = st.agg FROM (SELECT geoid, (b11005_002e::real)/(b11005_001e::real) AS agg FROM ppl_under_18_pct WHERE (b11005_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.ppl_under_18_pct IS 'b11005_002e'; DROP TABLE ppl_under_18_pct; -- join lim_eng_hh to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN lim_eng_hh integer; UPDATE mdc_2022_acs_5yr_tract_cce SET lim_eng_hh = st.agg FROM (SELECT geoid, (b16002_001e::integer) AS agg FROM lim_eng_hh) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.lim_eng_hh IS 'b16002_001e'; DROP TABLE lim_eng_hh; -- join lim_eng_hh_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN lim_eng_hh_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET lim_eng_hh_pct = st.agg FROM (SELECT geoid, (b16002_001e::real)/(b01003_001e::real) AS agg FROM lim_eng_hh_pct WHERE (b01003_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.lim_eng_hh_pct IS 'b16002_001e'; DROP TABLE lim_eng_hh_pct; -- join renter_pop to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN renter_pop integer; UPDATE mdc_2022_acs_5yr_tract_cce SET renter_pop = st.agg FROM (SELECT geoid, (b25003_003e::integer) AS agg FROM renter_pop) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.renter_pop IS 'b25003_003e'; DROP TABLE renter_pop; -- join renter_pop_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN renter_pop_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET renter_pop_pct = st.agg FROM (SELECT geoid, (b25003_003e::real)/(b25003_001e::real) AS agg FROM renter_pop_pct WHERE (b25003_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.renter_pop_pct IS 'b25003_003e'; DROP TABLE renter_pop_pct; -- join owner_pop to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN owner_pop integer; UPDATE mdc_2022_acs_5yr_tract_cce SET owner_pop = st.agg FROM (SELECT geoid, (b25003_002e::integer) AS agg FROM owner_pop) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.owner_pop IS 'b25003_002e'; DROP TABLE owner_pop; -- join owner_pop_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN owner_pop_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET owner_pop_pct = st.agg FROM (SELECT geoid, (b25003_002e::real)/(b25003_001e::real) AS agg FROM owner_pop_pct WHERE (b25003_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.owner_pop_pct IS 'b25003_002e'; DROP TABLE owner_pop_pct; -- join owner_with_mortgage_pop to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN owner_with_mortgage_pop integer; UPDATE mdc_2022_acs_5yr_tract_cce SET owner_with_mortgage_pop = st.agg FROM (SELECT geoid, (b25081_002e::integer) AS agg FROM owner_with_mortgage_pop) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.owner_with_mortgage_pop IS 'b25081_002e'; DROP TABLE owner_with_mortgage_pop; -- join owner_with_mortgage_pop_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN owner_with_mortgage_pop_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET owner_with_mortgage_pop_pct = st.agg FROM (SELECT geoid, (b25081_002e::real)/(b25003_001e::real) AS agg FROM owner_with_mortgage_pop_pct WHERE (b25003_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.owner_with_mortgage_pop_pct IS 'b25081_002e'; DROP TABLE owner_with_mortgage_pop_pct; -- join owner_without_mortgage_pop to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN owner_without_mortgage_pop integer; UPDATE mdc_2022_acs_5yr_tract_cce SET owner_without_mortgage_pop = st.agg FROM (SELECT geoid, (b25027_010e::integer) AS agg FROM owner_without_mortgage_pop) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.owner_without_mortgage_pop IS 'b25027_010e'; DROP TABLE owner_without_mortgage_pop; -- join owner_without_mortgage_pop_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN owner_without_mortgage_pop_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET owner_without_mortgage_pop_pct = st.agg FROM (SELECT geoid, (b25027_010e::real)/(b25003_001e::real) AS agg FROM owner_without_mortgage_pop_pct WHERE (b25003_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.owner_without_mortgage_pop_pct IS 'b25027_010e'; DROP TABLE owner_without_mortgage_pop_pct; -- join twentyfive_plus_pop_nohs to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN twentyfive_plus_pop_nohs integer; UPDATE mdc_2022_acs_5yr_tract_cce SET twentyfive_plus_pop_nohs = 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) AS agg FROM twentyfive_plus_pop_nohs) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.twentyfive_plus_pop_nohs 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'; DROP TABLE twentyfive_plus_pop_nohs; -- join twentyfive_plus_pop_nohs_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN twentyfive_plus_pop_nohs_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET twentyfive_plus_pop_nohs_pct = st.agg FROM (SELECT geoid, (b15003_002e::real+b15003_003e::real+b15003_004e::real+b15003_005e::real+b15003_006e::real+b15003_007e::real+b15003_008e::real+b15003_009e::real+b15003_010e::real+b15003_011e::real+b15003_012e::real+b15003_013e::real+b15003_014e::real+b15003_015e::real+b15003_016e::real)/(b15003_001e::real) AS agg FROM twentyfive_plus_pop_nohs_pct WHERE (b15003_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.twentyfive_plus_pop_nohs_pct 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'; DROP TABLE twentyfive_plus_pop_nohs_pct; -- join twentyfive_plus_pop_hs to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN twentyfive_plus_pop_hs integer; UPDATE mdc_2022_acs_5yr_tract_cce SET twentyfive_plus_pop_hs = st.agg FROM (SELECT geoid, (b15003_017e::integer) AS agg FROM twentyfive_plus_pop_hs) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.twentyfive_plus_pop_hs IS 'b15003_017e'; DROP TABLE twentyfive_plus_pop_hs; -- join twentyfive_plus_pop_hs_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN twentyfive_plus_pop_hs_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET twentyfive_plus_pop_hs_pct = st.agg FROM (SELECT geoid, (b15003_017e::real)/(b15003_001e::real) AS agg FROM twentyfive_plus_pop_hs_pct WHERE (b15003_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.twentyfive_plus_pop_hs_pct IS 'b15003_017e'; DROP TABLE twentyfive_plus_pop_hs_pct; -- join twentyfive_plus_pop_sc to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN twentyfive_plus_pop_sc integer; UPDATE mdc_2022_acs_5yr_tract_cce SET twentyfive_plus_pop_sc = st.agg FROM (SELECT geoid, (b15003_019e::integer+b15003_020e::integer) AS agg FROM twentyfive_plus_pop_sc) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.twentyfive_plus_pop_sc IS 'b15003_019e+b15003_020e'; DROP TABLE twentyfive_plus_pop_sc; -- join twentyfive_plus_pop_sc_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN twentyfive_plus_pop_sc_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET twentyfive_plus_pop_sc_pct = st.agg FROM (SELECT geoid, (b15003_019e::real+b15003_020e::real)/(b15003_001e::real) AS agg FROM twentyfive_plus_pop_sc_pct WHERE (b15003_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.twentyfive_plus_pop_sc_pct IS 'b15003_019e+b15003_020e'; DROP TABLE twentyfive_plus_pop_sc_pct; -- join twentyfive_plus_pop_aa to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN twentyfive_plus_pop_aa integer; UPDATE mdc_2022_acs_5yr_tract_cce SET twentyfive_plus_pop_aa = st.agg FROM (SELECT geoid, (b15003_021e::integer) AS agg FROM twentyfive_plus_pop_aa) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.twentyfive_plus_pop_aa IS 'b15003_021e'; DROP TABLE twentyfive_plus_pop_aa; -- join twentyfive_plus_pop_aa_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN twentyfive_plus_pop_aa_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET twentyfive_plus_pop_aa_pct = st.agg FROM (SELECT geoid, (b15003_021e::real)/(b15003_001e::real) AS agg FROM twentyfive_plus_pop_aa_pct WHERE (b15003_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.twentyfive_plus_pop_aa_pct IS 'b15003_021e'; DROP TABLE twentyfive_plus_pop_aa_pct; -- join twentyfive_plus_pop_b to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN twentyfive_plus_pop_b integer; UPDATE mdc_2022_acs_5yr_tract_cce SET twentyfive_plus_pop_b = st.agg FROM (SELECT geoid, (b15003_022e::integer) AS agg FROM twentyfive_plus_pop_b) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.twentyfive_plus_pop_b IS 'b15003_022e'; DROP TABLE twentyfive_plus_pop_b; -- join twentyfive_plus_pop_b_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN twentyfive_plus_pop_b_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET twentyfive_plus_pop_b_pct = st.agg FROM (SELECT geoid, (b15003_022e::real)/(b15003_001e::real) AS agg FROM twentyfive_plus_pop_b_pct WHERE (b15003_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.twentyfive_plus_pop_b_pct IS 'b15003_022e'; DROP TABLE twentyfive_plus_pop_b_pct; -- join twentyfive_plus_pop_gop to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN twentyfive_plus_pop_gop integer; UPDATE mdc_2022_acs_5yr_tract_cce SET twentyfive_plus_pop_gop = st.agg FROM (SELECT geoid, (b15003_023e::integer+b15003_024e::integer+b15003_025e::integer) AS agg FROM twentyfive_plus_pop_gop) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.twentyfive_plus_pop_gop IS 'b15003_023e+b15003_024e+b15003_025e'; DROP TABLE twentyfive_plus_pop_gop; -- join twentyfive_plus_pop_gop_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN twentyfive_plus_pop_gop_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET twentyfive_plus_pop_gop_pct = st.agg FROM (SELECT geoid, (b15003_023e::real+b15003_024e::real+b15003_025e::real)/(b15003_001e::real) AS agg FROM twentyfive_plus_pop_gop_pct WHERE (b15003_001e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.twentyfive_plus_pop_gop_pct IS 'b15003_023e+b15003_024e+b15003_025e'; DROP TABLE twentyfive_plus_pop_gop_pct; -- join hh_size to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN hh_size real; UPDATE mdc_2022_acs_5yr_tract_cce SET hh_size = st.agg FROM (SELECT geoid, (b25010_001e::real) AS agg FROM hh_size) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.hh_size IS 'b25010_001e'; DROP TABLE hh_size; -- join avg_inc to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN avg_inc real; UPDATE mdc_2022_acs_5yr_tract_cce SET avg_inc = st.agg FROM (SELECT geoid, (b19013_001e::real) AS agg FROM avg_inc) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.avg_inc IS 'b19013_001e'; DROP TABLE avg_inc; -- join cb_owner_units to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN cb_owner_units integer; UPDATE mdc_2022_acs_5yr_tract_cce SET cb_owner_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 cb_owner_units) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.cb_owner_units IS 'b25091_008e+b25091_009e+b25091_010e+b25091_011e+b25091_019e+b25091_020e+b25091_021e+b25091_022e'; DROP TABLE cb_owner_units; -- join cb_owner_units_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN cb_owner_units_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET cb_owner_units_pct = st.agg FROM (SELECT geoid, (b25091_008e::real+b25091_009e::real+b25091_010e::real+b25091_011e::real+b25091_019e::real+b25091_020e::real+b25091_021e::real+b25091_022e::real)/(b25081_002e::real+b25027_010e::real) AS agg FROM cb_owner_units_pct WHERE (b25081_002e::real > 0 AND b25027_010e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.cb_owner_units_pct IS 'b25091_008e+b25091_009e+b25091_010e+b25091_011e+b25091_019e+b25091_020e+b25091_021e+b25091_022e'; DROP TABLE cb_owner_units_pct; -- join cb_owner_with_mortgage_units to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN cb_owner_with_mortgage_units integer; UPDATE mdc_2022_acs_5yr_tract_cce SET cb_owner_with_mortgage_units = st.agg FROM (SELECT geoid, (b25091_008e::integer+b25091_009e::integer+b25091_010e::integer+b25091_011e::integer) AS agg FROM cb_owner_with_mortgage_units) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.cb_owner_with_mortgage_units IS 'b25091_008e+b25091_009e+b25091_010e+b25091_011e'; DROP TABLE cb_owner_with_mortgage_units; -- join cb_owner_with_mortgage_units_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN cb_owner_with_mortgage_units_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET cb_owner_with_mortgage_units_pct = st.agg FROM (SELECT geoid, (b25091_008e::real+b25091_009e::real+b25091_010e::real+b25091_011e::real)/(b25091_002e::real) AS agg FROM cb_owner_with_mortgage_units_pct WHERE (b25091_002e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.cb_owner_with_mortgage_units_pct IS 'b25091_008e+b25091_009e+b25091_010e+b25091_011e'; DROP TABLE cb_owner_with_mortgage_units_pct; -- join cb_owner_without_mortgage_units to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN cb_owner_without_mortgage_units integer; UPDATE mdc_2022_acs_5yr_tract_cce SET cb_owner_without_mortgage_units = st.agg FROM (SELECT geoid, (b25091_019e::integer+b25091_020e::integer+b25091_021e::integer+b25091_022e::integer) AS agg FROM cb_owner_without_mortgage_units) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.cb_owner_without_mortgage_units IS 'b25091_019e+b25091_020e+b25091_021e+b25091_022e'; DROP TABLE cb_owner_without_mortgage_units; -- join cb_owner_without_mortgage_units_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN cb_owner_without_mortgage_units_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET cb_owner_without_mortgage_units_pct = st.agg FROM (SELECT geoid, (b25091_019e::real+b25091_020e::real+b25091_021e::real+b25091_022e::real)/(b25091_013e::real) AS agg FROM cb_owner_without_mortgage_units_pct WHERE (b25091_013e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.cb_owner_without_mortgage_units_pct IS 'b25091_019e+b25091_020e+b25091_021e+b25091_022e'; DROP TABLE cb_owner_without_mortgage_units_pct; -- join cb_renter to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN cb_renter integer; UPDATE mdc_2022_acs_5yr_tract_cce SET cb_renter = st.agg FROM (SELECT geoid, (b25070_007e::integer+b25070_008e::integer+b25070_009e::integer+b25070_010e::integer) AS agg FROM cb_renter) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.cb_renter IS 'b25070_007e+b25070_008e+b25070_009e+b25070_010e'; DROP TABLE cb_renter; -- join cb_renter_pct to mdc_2022_acs_5yr_tract_cce ALTER TABLE mdc_2022_acs_5yr_tract_cce ADD COLUMN cb_renter_pct real; UPDATE mdc_2022_acs_5yr_tract_cce SET cb_renter_pct = st.agg FROM (SELECT geoid, (b25070_007e::real+b25070_008e::real+b25070_009e::real+b25070_010e::real)/(b25003_003e::real) AS agg FROM cb_renter_pct WHERE (b25003_003e::real > 0)) st WHERE mdc_2022_acs_5yr_tract_cce.geoid = st.geoid; COMMENT ON COLUMN mdc_2022_acs_5yr_tract_cce.cb_renter_pct IS 'b25070_007e+b25070_008e+b25070_009e+b25070_010e'; DROP TABLE cb_renter_pct; DELETE FROM mdc_2022_acs_5yr_tract_cce WHERE pop_size is Null; # add local geometry column and reproject existing geometries into local EPSG: SELECT AddGeometryColumn ( 'mdc_2022_acs_5yr_tract_cce', 'geom_local', 2236, 'multipolygon', 2 ); UPDATE mdc_2022_acs_5yr_tract_cce SET geom_local=ST_MakeValid(ST_RemoveRepeatedPoints(ST_Transform(ST_Multi(geom),2236))); CREATE INDEX mdc_2022_acs_5yr_tract_cce_geom_local_idx ON mdc_2022_acs_5yr_tract_cce USING GIST (geom_local); NOTIFY pgrst, 'reload schema';

University of MiamiGeospatial Digital Special Collections

2022 Miami-Dade ACS 5 Year Estimates - Tract Level - CCE

University of Miami
Geospatial Digital Special Collections