fl_2021_acs_5yr_tract

gdsc

2021 Florida ACS 5 Year Estimates - Tract Level - FSR

Collections: Census | FSR

Citation: Department of Commerce, U.S. Census Bureau (2022). 2021 Florida ACS 5 Year Estimates - Tract Level - FSR [gis dataset]. Department of Commerce. https://www.census.gov/data/developers/data-sets/acs-5year.html. last updated: 2024-10-28 (update frequency: Never). Abstract: A special collection of aggregated columns from the 2021 5-year ACS variables at the census tract level selected for use with the Florida Stroke Registry. 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, Population, FSR

Attributes:

Name	Description	Type	Units
total_population	Total population in census tract	integer	people
hispanic_population	Number of people who identify as Hispanic or Latino any race	integer	people
creole_speaking	Number of Creole speaking people in the tract	int4	people
latin_america_born	Number of people who migrated from Latin America and the Caribbean	int4	people
persons_4_years_old_and_younger	Number of people 4 years old and younger	integer	people
age_of_5_and_19	Number of people with age of 5 to 20	integer	people
persons_between_the_age_of_20_and_64	Number of people between the age of 20 and 65	integer	people
persons_65_years_old_and_over	Number of people 65 years old and over	integer	people
born_in_haiti	Number of people born in Haiti	integer	people
born_in_venezuela	Number of people born in Venezuela	integer	people

Open Geospatial Consortium Standards: dynamic vector tiles | | WMS (coming soon) | WFS (coming soon) Analytical Functions: gdsc_get_attributes(tablename=fl_2021_acs_5yr_tract_fsr,lat=25.718,lng=-80.279,identifier=geoid,attrs=[total_population,hispanic_population,creole_speaking,latin_america_born,persons_4_years_old_and_younger,age_of_5_and_19,persons_between_the_age_of_20_and_64,persons_65_years_old_and_over,born_in_haiti,born_in_venezuela],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/fl_2021_acs_5yr_tract_fsr/{download,etl} && cd /data/fl_2021_acs_5yr_tract_fsr # set update flag based on last update and update frequency do_update=0 if date() < last_update + update_frequency else do_update = 1 # download specific variables from the census API, reformat census json to csv, # and load as individual tables into postGIS with ogr2ogr. if do_update = 1 then wget -O download/total_population.json "https://api.census.gov/data/2021/acs/acs5? get=B01001_001E&key=&for=tract%3A%2A&in=state%3A12" 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" 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/creole_speaking.json "https://api.census.gov/data/2021/acs/acs5? get=C16001_008E&key=&for=tract%3A%2A&in=state%3A12" cols=$(head -n 1 download/creole_speaking.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/creole_speaking.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln creole_speaking if do_update = 1 then wget -O download/latin_america_born.json "https://api.census.gov/data/2021/acs/acs5? get=B05007_048E%2CB05007_045E%2CB05007_051E%2CB05007_042E&key=&for=tract%3A%2A&in=state%3A12" cols=$(head -n 1 download/latin_america_born.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/latin_america_born.json | sed -e "s/\",\"//5g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln latin_america_born if do_update = 1 then wget -O download/persons_4_years_old_and_younger.json "https://api.census.gov/data/2021/acs/acs5? get=B01001_003E%2CB01001_027E&key=&for=tract%3A%2A&in=state%3A12" cols=$(head -n 1 download/persons_4_years_old_and_younger.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/persons_4_years_old_and_younger.json | sed -e "s/\",\"//3g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln persons_4_years_old_and_younger if do_update = 1 then wget -O download/age_of_5_and_19.json "https://api.census.gov/data/2021/acs/acs5? get=B01001_004E%2CB01001_005E%2CB01001_006E%2CB01001_007E%2CB01001_028E%2CB01001_029E%2CB01001_030E%2CB01001_031E&key=&for=tract%3A%2A&in=state%3A12" cols=$(head -n 1 download/age_of_5_and_19.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/age_of_5_and_19.json | sed -e "s/\",\"//9g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln age_of_5_and_19 if do_update = 1 then wget -O download/persons_between_the_age_of_20_and_64.json "https://api.census.gov/data/2021/acs/acs5? get=B01001_008E%2CB01001_009E%2CB01001_010E%2CB01001_011E%2CB01001_012E%2CB01001_013E%2CB01001_014E%2CB01001_015E%2CB01001_016E%2CB01001_017E%2CB01001_018E%2CB01001_019E%2CB01001_032E%2CB01001_033E%2CB01001_034E%2CB01001_035E%2CB01001_036E%2CB01001_037E%2CB01001_038E%2CB01001_039E%2CB01001_040E%2CB01001_041E%2CB01001_042E%2CB01001_043E&key=&for=tract%3A%2A&in=state%3A12" cols=$(head -n 1 download/persons_between_the_age_of_20_and_64.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/persons_between_the_age_of_20_and_64.json | sed -e "s/\",\"//25g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln persons_between_the_age_of_20_and_64 if do_update = 1 then wget -O download/persons_65_years_old_and_over.json "https://api.census.gov/data/2021/acs/acs5? get=B01001_020E%2CB01001_021E%2CB01001_022E%2CB01001_023E%2CB01001_024E%2CB01001_025E%2CB01001_044E%2CB01001_045E%2CB01001_046E%2CB01001_047E%2CB01001_048E%2CB01001_049E&key=&for=tract%3A%2A&in=state%3A12" cols=$(head -n 1 download/persons_65_years_old_and_over.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/persons_65_years_old_and_over.json | sed -e "s/\",\"//13g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln persons_65_years_old_and_over if do_update = 1 then wget -O download/born_in_haiti.json "https://api.census.gov/data/2021/acs/acs5? get=B05006_138E&key=&for=tract%3A%2A&in=state%3A12" cols=$(head -n 1 download/born_in_haiti.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/born_in_haiti.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln born_in_haiti if do_update = 1 then wget -O download/born_in_venezuela.json "https://api.census.gov/data/2021/acs/acs5? get=B05006_164E&key=&for=tract%3A%2A&in=state%3A12" cols=$(head -n 1 download/born_in_venezuela.json | awk -F, '{print NF-2}') awk '{gsub(/\[|\]|.$/,""); print;}' download/born_in_venezuela.json | sed -e "s/\",\"//2g" -e "s/\"state.*\"/\"geoid\"/1" | ogr2ogr -f PostgreSQL PG:"" csv:/vsistdin/ -nln born_in_venezuela # 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 fl_2021_acs_5yr_tract_fsr ALTER TABLE fl_2021_acs_5yr_tract_fsr ADD COLUMN total_population integer; UPDATE fl_2021_acs_5yr_tract_fsr SET total_population = st.agg FROM (SELECT geoid, (b01001_001e::integer) AS agg FROM total_population) st WHERE fl_2021_acs_5yr_tract_fsr.geoid = st.geoid; COMMENT ON COLUMN fl_2021_acs_5yr_tract_fsr.total_population IS 'b01001_001e'; DROP TABLE total_population; -- join hispanic_population to fl_2021_acs_5yr_tract_fsr ALTER TABLE fl_2021_acs_5yr_tract_fsr ADD COLUMN hispanic_population integer; UPDATE fl_2021_acs_5yr_tract_fsr SET hispanic_population = st.agg FROM (SELECT geoid, (b01001i_001e::integer) AS agg FROM hispanic_population) st WHERE fl_2021_acs_5yr_tract_fsr.geoid = st.geoid; COMMENT ON COLUMN fl_2021_acs_5yr_tract_fsr.hispanic_population IS 'b01001i_001e'; DROP TABLE hispanic_population; -- join creole_speaking to fl_2021_acs_5yr_tract_fsr ALTER TABLE fl_2021_acs_5yr_tract_fsr ADD COLUMN creole_speaking int4; UPDATE fl_2021_acs_5yr_tract_fsr SET creole_speaking = st.agg FROM (SELECT geoid, (c16001_008e::int4) AS agg FROM creole_speaking) st WHERE fl_2021_acs_5yr_tract_fsr.geoid = st.geoid; COMMENT ON COLUMN fl_2021_acs_5yr_tract_fsr.creole_speaking IS 'c16001_008e'; DROP TABLE creole_speaking; -- join latin_america_born to fl_2021_acs_5yr_tract_fsr ALTER TABLE fl_2021_acs_5yr_tract_fsr ADD COLUMN latin_america_born int4; UPDATE fl_2021_acs_5yr_tract_fsr SET latin_america_born = st.agg FROM (SELECT geoid, (b05007_048e::int4+b05007_045e::int4+b05007_051e::int4+b05007_042e::int4) AS agg FROM latin_america_born) st WHERE fl_2021_acs_5yr_tract_fsr.geoid = st.geoid; COMMENT ON COLUMN fl_2021_acs_5yr_tract_fsr.latin_america_born IS 'b05007_048e+b05007_045e+b05007_051e+b05007_042e'; DROP TABLE latin_america_born; -- join persons_4_years_old_and_younger to fl_2021_acs_5yr_tract_fsr ALTER TABLE fl_2021_acs_5yr_tract_fsr ADD COLUMN persons_4_years_old_and_younger integer; UPDATE fl_2021_acs_5yr_tract_fsr SET persons_4_years_old_and_younger = st.agg FROM (SELECT geoid, (b01001_003e::integer+b01001_027e::integer) AS agg FROM persons_4_years_old_and_younger) st WHERE fl_2021_acs_5yr_tract_fsr.geoid = st.geoid; COMMENT ON COLUMN fl_2021_acs_5yr_tract_fsr.persons_4_years_old_and_younger IS 'b01001_003e+b01001_027e'; DROP TABLE persons_4_years_old_and_younger; -- join age_of_5_and_19 to fl_2021_acs_5yr_tract_fsr ALTER TABLE fl_2021_acs_5yr_tract_fsr ADD COLUMN age_of_5_and_19 integer; UPDATE fl_2021_acs_5yr_tract_fsr SET age_of_5_and_19 = st.agg FROM (SELECT geoid, (b01001_004e::integer+b01001_005e::integer+b01001_006e::integer+b01001_007e::integer+b01001_028e::integer+b01001_029e::integer+b01001_030e::integer+b01001_031e::integer) AS agg FROM age_of_5_and_19) st WHERE fl_2021_acs_5yr_tract_fsr.geoid = st.geoid; COMMENT ON COLUMN fl_2021_acs_5yr_tract_fsr.age_of_5_and_19 IS 'b01001_004e+b01001_005e+b01001_006e+b01001_007e+b01001_028e+b01001_029e+b01001_030e+b01001_031e'; DROP TABLE age_of_5_and_19; -- join persons_between_the_age_of_20_and_64 to fl_2021_acs_5yr_tract_fsr ALTER TABLE fl_2021_acs_5yr_tract_fsr ADD COLUMN persons_between_the_age_of_20_and_64 integer; UPDATE fl_2021_acs_5yr_tract_fsr SET persons_between_the_age_of_20_and_64 = st.agg FROM (SELECT geoid, (b01001_008e::integer+b01001_009e::integer+b01001_010e::integer+b01001_011e::integer+b01001_012e::integer+b01001_013e::integer+b01001_014e::integer+b01001_015e::integer+b01001_016e::integer+b01001_017e::integer+b01001_018e::integer+b01001_019e::integer+b01001_032e::integer+b01001_033e::integer+b01001_034e::integer+b01001_035e::integer+b01001_036e::integer+b01001_037e::integer+b01001_038e::integer+b01001_039e::integer+b01001_040e::integer+b01001_041e::integer+b01001_042e::integer+b01001_043e::integer) AS agg FROM persons_between_the_age_of_20_and_64) st WHERE fl_2021_acs_5yr_tract_fsr.geoid = st.geoid; COMMENT ON COLUMN fl_2021_acs_5yr_tract_fsr.persons_between_the_age_of_20_and_64 IS 'b01001_008e+b01001_009e+b01001_010e+b01001_011e+b01001_012e+b01001_013e+b01001_014e+b01001_015e+b01001_016e+b01001_017e+b01001_018e+b01001_019e+b01001_032e+b01001_033e+b01001_034e+b01001_035e+b01001_036e+b01001_037e+b01001_038e+b01001_039e+b01001_040e+b01001_041e+b01001_042e+b01001_043e'; DROP TABLE persons_between_the_age_of_20_and_64; -- join persons_65_years_old_and_over to fl_2021_acs_5yr_tract_fsr ALTER TABLE fl_2021_acs_5yr_tract_fsr ADD COLUMN persons_65_years_old_and_over integer; UPDATE fl_2021_acs_5yr_tract_fsr SET persons_65_years_old_and_over = st.agg FROM (SELECT geoid, (b01001_020e::integer+b01001_021e::integer+b01001_022e::integer+b01001_023e::integer+b01001_024e::integer+b01001_025e::integer+b01001_044e::integer+b01001_045e::integer+b01001_046e::integer+b01001_047e::integer+b01001_048e::integer+b01001_049e::integer) AS agg FROM persons_65_years_old_and_over) st WHERE fl_2021_acs_5yr_tract_fsr.geoid = st.geoid; COMMENT ON COLUMN fl_2021_acs_5yr_tract_fsr.persons_65_years_old_and_over IS 'b01001_020e+b01001_021e+b01001_022e+b01001_023e+b01001_024e+b01001_025e+b01001_044e+b01001_045e+b01001_046e+b01001_047e+b01001_048e+b01001_049e'; DROP TABLE persons_65_years_old_and_over; -- join born_in_haiti to fl_2021_acs_5yr_tract_fsr ALTER TABLE fl_2021_acs_5yr_tract_fsr ADD COLUMN born_in_haiti integer; UPDATE fl_2021_acs_5yr_tract_fsr SET born_in_haiti = st.agg FROM (SELECT geoid, (b05006_138e::integer) AS agg FROM born_in_haiti) st WHERE fl_2021_acs_5yr_tract_fsr.geoid = st.geoid; COMMENT ON COLUMN fl_2021_acs_5yr_tract_fsr.born_in_haiti IS 'b05006_138e'; DROP TABLE born_in_haiti; -- join born_in_venezuela to fl_2021_acs_5yr_tract_fsr ALTER TABLE fl_2021_acs_5yr_tract_fsr ADD COLUMN born_in_venezuela integer; UPDATE fl_2021_acs_5yr_tract_fsr SET born_in_venezuela = st.agg FROM (SELECT geoid, (b05006_164e::integer) AS agg FROM born_in_venezuela) st WHERE fl_2021_acs_5yr_tract_fsr.geoid = st.geoid; COMMENT ON COLUMN fl_2021_acs_5yr_tract_fsr.born_in_venezuela IS 'b05006_164e'; DROP TABLE born_in_venezuela; DELETE FROM fl_2021_acs_5yr_tract_fsr WHERE total_population is Null;

Sponsors:

UM Geospatial Digital Special Collections

2021 Florida ACS 5 Year Estimates - Tract Level - FSR