load_tiger.sh

#!/bin/bash
## all these maps use NAD 83, in degree

echo "*** Downloading and loading US states." # expected number of records in DB: 51
mkdir -p ./temp
wget -N 'https://www2.census.gov/geo/tiger/TIGER2020/STATE/tl_2020_us_state.zip' -P ./temp
unzip -d ./temp/ ./temp/tl_2020_us_state.zip
ogr2ogr -f MySQL MySQL:FACT_geo,host=plazablanca.nilu.no,user=root,password=impadminPSWD! ./temp/tl_2020_us_state.shp -nln us_states -update -overwrite -lco engine=Aria  -t_srs EPSG:4326
rm -r ./temp
# to delete GUAM, Portorico, American Virgin Islands, etc
mariadb -h "plazablanca.nilu.no" -u "root" -pimpadminPSWD! -e "DELETE FROM FACT_geo.us_states WHERE statefp > 56"

echo "*** Downloading and loading US counties."# expected number of records in DB: 3,143
mkdir -p ./temp
wget -N 'https://www2.census.gov/geo/tiger/TIGER2020/COUNTY/tl_2020_us_county.zip' -P ./temp
unzip -d ./temp/ ./temp/tl_2020_us_county.zip
ogr2ogr -f MySQL MySQL:FACT_geo,host=plazablanca.nilu.no,user=root,password=impadminPSWD! ./temp/tl_2020_us_county.shp -nln us_counties -update -overwrite -lco engine=Aria -t_srs EPSG:4326
rm -r ./temp
# to delete GUAM, Portorico, American Virgin Islands, etc
mariadb -h "plazablanca.nilu.no" -u "root" -pimpadminPSWD! -e "DELETE FROM FACT_geo.us_counties WHERE statefp > 56"

echo "*** Downloading and loading US tracts." # expected number of records in DB: 84,414
# create db table on first state in the list
declare -a state_fips=("01")
for i in "${state_fips[@]}"
do
   mkdir -p ./temp
   wget -N "https://www2.census.gov/geo/tiger/TIGER2020/TRACT/tl_2020_${i}_tract.zip" -P ./temp
   unzip -d ./temp/ ./temp/tl_2020_${i}_tract.zip
   ogr2ogr -f MySQL MySQL:FACT_geo,host=plazablanca.nilu.no,user=root,password=impadminPSWD! ./temp/tl_2020_${i}_tract.shp -nln us_tracts -update -overwrite -lco engine=Aria -t_srs EPSG:4326
   rm -r ./temp
done
# append all other states
declare -a state_fips=("02" "04" "05" "06" "08" "09" "10" "11" "12" "13" "15" "16" "17" "18" "19" "20" "21" "22" "23" "24" "25" "26" "27" "28" "29" "30" "31" "32" "33" "34" "35" "36" "37" "38" "39" "40" "41" "42" "44" "45" "46" "47" "48" "49" "50" "51" "53" "54" "55" "56")
for i in "${state_fips[@]}"
do
   mkdir -p ./temp
   wget -N "https://www2.census.gov/geo/tiger/TIGER2020/TRACT/tl_2020_${i}_tract.zip" -P ./temp
   unzip -d ./temp/ ./temp/tl_2020_${i}_tract.zip
   ogr2ogr -f MySQL MySQL:FACT_geo,host=plazablanca.nilu.no,user=root,password=impadminPSWD! ./temp/tl_2020_${i}_tract.shp -nln us_tracts -update -append -t_srs EPSG:4326
   rm -r ./temp
done

echo "*** Downloading and loading US block groups." # expected number of records in DB: 239,780
# create db table on first state in the list
declare -a state_fips=("01")
for i in "${state_fips[@]}"
do
   mkdir -p ./temp
   wget -N "https://www2.census.gov/geo/tiger/TIGER2020/BG/tl_2020_${i}_bg.zip" -P ./temp
   unzip -d ./temp/ ./temp/tl_2020_${i}_bg.zip
   ogr2ogr -f MySQL MySQL:FACT_geo,host=plazablanca.nilu.no,user=root,password=impadminPSWD! ./temp/tl_2020_${i}_bg.shp -nln us_block_groups -update -overwrite -lco engine=Aria -t_srs EPSG:4326
   rm -r ./temp
done
# append all other states
declare -a state_fips=("02" "04" "05" "06" "08" "09" "10" "11" "12" "13" "15" "16" "17" "18" "19" "20" "21" "22" "23" "24" "25" "26" "27" "28" "29" "30" "31" "32" "33" "34" "35" "36" "37" "38" "39" "40" "41" "42" "44" "45" "46" "47" "48" "49" "50" "51" "53" "54" "55" "56")
for i in "${state_fips[@]}"
do
   mkdir -p ./temp
   wget -N "https://www2.census.gov/geo/tiger/TIGER2020/BG/tl_2020_${i}_bg.zip" -P ./temp
   unzip -d ./temp/ ./temp/tl_2020_${i}_bg.zip
   ogr2ogr -f MySQL MySQL:FACT_geo,host=plazablanca.nilu.no,user=root,password=impadminPSWD! ./temp/tl_2020_${i}_bg.shp -nln us_block_groups -update -append -t_srs EPSG:4326
   rm -r ./temp
done

echo "*** Downloading and loading US blocks." # expected number of records in DB: 8,132,968
# create db table on first state in the list
declare -a state_fips=("01")
for i in "${state_fips[@]}"
do
   mkdir -p ./temp
   wget -N "https://www2.census.gov/geo/tiger/TIGER2020/TABBLOCK20/tl_2020_${i}_tabblock20.zip" -P ./temp
   unzip -d ./temp/ ./temp/tl_2020_${i}_tabblock20.zip
   ogr2ogr -f MySQL MySQL:FACT_geo,host=plazablanca.nilu.no,user=root,password=impadminPSWD! ./temp/tl_2020_${i}_tabblock20.shp -nln us_blocks -update -overwrite -lco engine=Aria -t_srs EPSG:4326
   rm -r ./temp
done
# append all other states
declare -a state_fips=("02" "04" "05" "06" "08" "09" "10" "11" "12" "13" "15" "16" "17" "18" "19" "20" "21" "22" "23" "24" "25" "26" "27" "28" "29" "30" "31" "32" "33" "34" "35" "36" "37" "38" "39" "40" "41" "42" "44" "45" "46" "47" "48" "49" "50" "51" "53" "54" "55" "56")
for i in "${state_fips[@]}"
do
   mkdir -p ./temp
   wget -N "https://www2.census.gov/geo/tiger/TIGER2020/TABBLOCK20/tl_2020_${i}_tabblock20.zip" -P ./temp
   unzip -d ./temp/ ./temp/tl_2020_${i}_tabblock20.zip
   ogr2ogr -f MySQL MySQL:FACT_geo,host=plazablanca.nilu.no,user=root,password=impadminPSWD! ./temp/tl_2020_${i}_tabblock20.shp -nln us_blocks -update -append -t_srs EPSG:4326
   rm -r ./temp
done