nation_script_load_vi.sh

TMPDIR="/gisdata/temp/"
UNZIPTOOL=unzip
WGETTOOL="/usr/bin/wget"
export PGBIN=/usr/lib/postgresql/14/bin
export PGPORT=5432
export PGHOST=localhost
export PGUSER=$1
export PGPASSWORD=$2
export PGDATABASE=osm
PSQL=${PGBIN}/psql
SHP2PGSQL=shp2pgsql
cd /gisdata

cd /gisdata
wget https://www2.census.gov/geo/tiger/TIGER2021/PLACE/tl_2021_78_place.zip --mirror --reject=html
cd /gisdata/www2.census.gov/geo/tiger/TIGER2021/PLACE
rm -f ${TMPDIR}/*.*
${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
${PSQL} -c "CREATE SCHEMA tiger_staging;"
for z in tl_2021_78*_place.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;

${PSQL} -c "CREATE TABLE tiger_data.VI_place(CONSTRAINT pk_VI_place PRIMARY KEY (plcidfp) ) INHERITS(tiger.place);" 
${SHP2PGSQL} -D -c -s 4269 -g the_geom   -W "latin1" tl_2021_78_place.dbf tiger_staging.vi_place | ${PSQL}
${PSQL} -c "ALTER TABLE tiger_staging.VI_place RENAME geoid TO plcidfp;SELECT loader_load_staged_data(lower('VI_place'), lower('VI_place')); ALTER TABLE tiger_data.VI_place ADD CONSTRAINT uidx_VI_place_gid UNIQUE (gid);"
${PSQL} -c "CREATE INDEX idx_VI_place_soundex_name ON tiger_data.VI_place USING btree (soundex(name));"
${PSQL} -c "CREATE INDEX tiger_data_VI_place_the_geom_gist ON tiger_data.VI_place USING gist(the_geom);"
${PSQL} -c "ALTER TABLE tiger_data.VI_place ADD CONSTRAINT chk_statefp CHECK (statefp = '78');"
cd /gisdata
wget https://www2.census.gov/geo/tiger/TIGER2021/COUSUB/tl_2021_78_cousub.zip --mirror --reject=html
cd /gisdata/www2.census.gov/geo/tiger/TIGER2021/COUSUB
rm -f ${TMPDIR}/*.*
${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
${PSQL} -c "CREATE SCHEMA tiger_staging;"
for z in tl_2021_78*_cousub.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;

${PSQL} -c "CREATE TABLE tiger_data.VI_cousub(CONSTRAINT pk_VI_cousub PRIMARY KEY (cosbidfp), CONSTRAINT uidx_VI_cousub_gid UNIQUE (gid)) INHERITS(tiger.cousub);" 
${SHP2PGSQL} -D -c -s 4269 -g the_geom   -W "latin1" tl_2021_78_cousub.dbf tiger_staging.vi_cousub | ${PSQL}
${PSQL} -c "ALTER TABLE tiger_staging.VI_cousub RENAME geoid TO cosbidfp;SELECT loader_load_staged_data(lower('VI_cousub'), lower('VI_cousub')); ALTER TABLE tiger_data.VI_cousub ADD CONSTRAINT chk_statefp CHECK (statefp = '78');"
${PSQL} -c "CREATE INDEX tiger_data_VI_cousub_the_geom_gist ON tiger_data.VI_cousub USING gist(the_geom);"
${PSQL} -c "CREATE INDEX idx_tiger_data_VI_cousub_countyfp ON tiger_data.VI_cousub USING btree(countyfp);"
cd /gisdata
wget https://www2.census.gov/geo/tiger/TIGER2021/TRACT/tl_2021_78_tract.zip --mirror --reject=html
cd /gisdata/www2.census.gov/geo/tiger/TIGER2021/TRACT
rm -f ${TMPDIR}/*.*
${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
${PSQL} -c "CREATE SCHEMA tiger_staging;"
for z in tl_2021_78*_tract.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;

${PSQL} -c "CREATE TABLE tiger_data.VI_tract(CONSTRAINT pk_VI_tract PRIMARY KEY (tract_id) ) INHERITS(tiger.tract); " 
${SHP2PGSQL} -D -c -s 4269 -g the_geom   -W "latin1" tl_2021_78_tract.dbf tiger_staging.vi_tract | ${PSQL}
${PSQL} -c "ALTER TABLE tiger_staging.VI_tract RENAME geoid TO tract_id; SELECT loader_load_staged_data(lower('VI_tract'), lower('VI_tract')); "
	${PSQL} -c "CREATE INDEX tiger_data_VI_tract_the_geom_gist ON tiger_data.VI_tract USING gist(the_geom);"
	${PSQL} -c "VACUUM ANALYZE tiger_data.VI_tract;"
	${PSQL} -c "ALTER TABLE tiger_data.VI_tract ADD CONSTRAINT chk_statefp CHECK (statefp = '78');"
cd /gisdata
wget https://www2.census.gov/geo/tiger/TIGER2021/TABBLOCK20/tl_2021_78_tabblock20.zip --mirror --reject=html
cd /gisdata/www2.census.gov/geo/tiger/TIGER2021/TABBLOCK20
rm -f ${TMPDIR}/*.*
${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
${PSQL} -c "CREATE SCHEMA tiger_staging;"
for z in tl_2021_78*_tabblock20.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;

${PSQL} -c "CREATE TABLE tiger_data.VI_tabblock20(CONSTRAINT pk_VI_tabblock20 PRIMARY KEY (geoid)) INHERITS(tiger.tabblock20);" 
${SHP2PGSQL} -D -c -s 4269 -g the_geom   -W "latin1" tl_2021_78_tabblock20.dbf tiger_staging.vi_tabblock20 | ${PSQL}
${PSQL} -c "SELECT loader_load_staged_data(lower('VI_tabblock20'), lower('VI_tabblock20')); "
${PSQL} -c "ALTER TABLE tiger_data.VI_tabblock20 ADD CONSTRAINT chk_statefp CHECK (statefp = '78');"
${PSQL} -c "CREATE INDEX tiger_data_VI_tabblock20_the_geom_gist ON tiger_data.VI_tabblock20 USING gist(the_geom);"
${PSQL} -c "vacuum analyze tiger_data.VI_tabblock20;"
cd /gisdata
wget --mirror  https://www2.census.gov/geo/tiger/TIGER2021/FACES/tl_2021_78010_faces.zip 
 wget --mirror  https://www2.census.gov/geo/tiger/TIGER2021/FACES/tl_2021_78020_faces.zip 
 wget --mirror  https://www2.census.gov/geo/tiger/TIGER2021/FACES/tl_2021_78030_faces.zip 
cd /gisdata/www2.census.gov/geo/tiger/TIGER2021/FACES/
rm -f ${TMPDIR}/*.*
${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
${PSQL} -c "CREATE SCHEMA tiger_staging;"
for z in tl_*_78*_faces*.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;

${PSQL} -c "CREATE TABLE tiger_data.VI_faces(CONSTRAINT pk_VI_faces PRIMARY KEY (gid)) INHERITS(tiger.faces);" 
for z in *faces*.dbf; do
${SHP2PGSQL} -D   -D -s 4269 -g the_geom -W "latin1" $z tiger_staging.VI_faces | ${PSQL}
${PSQL} -c "SELECT loader_load_staged_data(lower('VI_faces'), lower('VI_faces'));"
done

${PSQL} -c "CREATE INDEX tiger_data_VI_faces_the_geom_gist ON tiger_data.VI_faces USING gist(the_geom);"
	${PSQL} -c "CREATE INDEX idx_tiger_data_VI_faces_tfid ON tiger_data.VI_faces USING btree (tfid);"
	${PSQL} -c "CREATE INDEX idx_tiger_data_VI_faces_countyfp ON tiger_data.VI_faces USING btree (countyfp);"
	${PSQL} -c "ALTER TABLE tiger_data.VI_faces ADD CONSTRAINT chk_statefp CHECK (statefp = '78');"
	${PSQL} -c "vacuum analyze tiger_data.VI_faces;"
cd /gisdata
wget --mirror  https://www2.census.gov/geo/tiger/TIGER2021/FEATNAMES/tl_2021_78010_featnames.zip 
 wget --mirror  https://www2.census.gov/geo/tiger/TIGER2021/FEATNAMES/tl_2021_78020_featnames.zip 
 wget --mirror  https://www2.census.gov/geo/tiger/TIGER2021/FEATNAMES/tl_2021_78030_featnames.zip 
cd /gisdata/www2.census.gov/geo/tiger/TIGER2021/FEATNAMES/
rm -f ${TMPDIR}/*.*
${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
${PSQL} -c "CREATE SCHEMA tiger_staging;"
for z in tl_*_78*_featnames*.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;

${PSQL} -c "CREATE TABLE tiger_data.VI_featnames(CONSTRAINT pk_VI_featnames PRIMARY KEY (gid)) INHERITS(tiger.featnames);ALTER TABLE tiger_data.VI_featnames ALTER COLUMN statefp SET DEFAULT '78';" 
for z in *featnames*.dbf; do
${SHP2PGSQL} -D   -D -s 4269 -g the_geom -W "latin1" $z tiger_staging.VI_featnames | ${PSQL}
${PSQL} -c "SELECT loader_load_staged_data(lower('VI_featnames'), lower('VI_featnames'));"
done

${PSQL} -c "CREATE INDEX idx_tiger_data_VI_featnames_snd_name ON tiger_data.VI_featnames USING btree (soundex(name));"
${PSQL} -c "CREATE INDEX idx_tiger_data_VI_featnames_lname ON tiger_data.VI_featnames USING btree (lower(name));"
${PSQL} -c "CREATE INDEX idx_tiger_data_VI_featnames_tlid_statefp ON tiger_data.VI_featnames USING btree (tlid,statefp);"
${PSQL} -c "ALTER TABLE tiger_data.VI_featnames ADD CONSTRAINT chk_statefp CHECK (statefp = '78');"
${PSQL} -c "vacuum analyze tiger_data.VI_featnames;"
cd /gisdata
wget --mirror  https://www2.census.gov/geo/tiger/TIGER2021/EDGES/tl_2021_78010_edges.zip 
 wget --mirror  https://www2.census.gov/geo/tiger/TIGER2021/EDGES/tl_2021_78020_edges.zip 
 wget --mirror  https://www2.census.gov/geo/tiger/TIGER2021/EDGES/tl_2021_78030_edges.zip 
cd /gisdata/www2.census.gov/geo/tiger/TIGER2021/EDGES/
rm -f ${TMPDIR}/*.*
${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
${PSQL} -c "CREATE SCHEMA tiger_staging;"
for z in tl_*_78*_edges*.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;

${PSQL} -c "CREATE TABLE tiger_data.VI_edges(CONSTRAINT pk_VI_edges PRIMARY KEY (gid)) INHERITS(tiger.edges);"
for z in *edges*.dbf; do
${SHP2PGSQL} -D   -D -s 4269 -g the_geom -W "latin1" $z tiger_staging.VI_edges | ${PSQL}
${PSQL} -c "SELECT loader_load_staged_data(lower('VI_edges'), lower('VI_edges'));"
done

${PSQL} -c "ALTER TABLE tiger_data.VI_edges ADD CONSTRAINT chk_statefp CHECK (statefp = '78');"
${PSQL} -c "CREATE INDEX idx_tiger_data_VI_edges_tlid ON tiger_data.VI_edges USING btree (tlid);"
${PSQL} -c "CREATE INDEX idx_tiger_data_VI_edgestfidr ON tiger_data.VI_edges USING btree (tfidr);"
${PSQL} -c "CREATE INDEX idx_tiger_data_VI_edges_tfidl ON tiger_data.VI_edges USING btree (tfidl);"
${PSQL} -c "CREATE INDEX idx_tiger_data_VI_edges_countyfp ON tiger_data.VI_edges USING btree (countyfp);"
${PSQL} -c "CREATE INDEX tiger_data_VI_edges_the_geom_gist ON tiger_data.VI_edges USING gist(the_geom);"
${PSQL} -c "CREATE INDEX idx_tiger_data_VI_edges_zipl ON tiger_data.VI_edges USING btree (zipl);"
${PSQL} -c "CREATE TABLE tiger_data.VI_zip_state_loc(CONSTRAINT pk_VI_zip_state_loc PRIMARY KEY(zip,stusps,place)) INHERITS(tiger.zip_state_loc);"
${PSQL} -c "INSERT INTO tiger_data.VI_zip_state_loc(zip,stusps,statefp,place) SELECT DISTINCT e.zipl, 'VI', '78', p.name FROM tiger_data.VI_edges AS e INNER JOIN tiger_data.VI_faces AS f ON (e.tfidl = f.tfid OR e.tfidr = f.tfid) INNER JOIN tiger_data.VI_place As p ON(f.statefp = p.statefp AND f.placefp = p.placefp ) WHERE e.zipl IS NOT NULL;"
${PSQL} -c "CREATE INDEX idx_tiger_data_VI_zip_state_loc_place ON tiger_data.VI_zip_state_loc USING btree(soundex(place));"
${PSQL} -c "ALTER TABLE tiger_data.VI_zip_state_loc ADD CONSTRAINT chk_statefp CHECK (statefp = '78');"
${PSQL} -c "vacuum analyze tiger_data.VI_edges;"
${PSQL} -c "vacuum analyze tiger_data.VI_zip_state_loc;"
${PSQL} -c "CREATE TABLE tiger_data.VI_zip_lookup_base(CONSTRAINT pk_VI_zip_state_loc_city PRIMARY KEY(zip,state, county, city, statefp)) INHERITS(tiger.zip_lookup_base);"
${PSQL} -c "INSERT INTO tiger_data.VI_zip_lookup_base(zip,state,county,city, statefp) SELECT DISTINCT e.zipl, 'VI', c.name,p.name,'78'  FROM tiger_data.VI_edges AS e INNER JOIN tiger.county As c  ON (e.countyfp = c.countyfp AND e.statefp = c.statefp AND e.statefp = '78') INNER JOIN tiger_data.VI_faces AS f ON (e.tfidl = f.tfid OR e.tfidr = f.tfid) INNER JOIN tiger_data.VI_place As p ON(f.statefp = p.statefp AND f.placefp = p.placefp ) WHERE e.zipl IS NOT NULL;"
${PSQL} -c "ALTER TABLE tiger_data.VI_zip_lookup_base ADD CONSTRAINT chk_statefp CHECK (statefp = '78');"
${PSQL} -c "CREATE INDEX idx_tiger_data_VI_zip_lookup_base_citysnd ON tiger_data.VI_zip_lookup_base USING btree(soundex(city));"
cd /gisdata
wget --mirror  https://www2.census.gov/geo/tiger/TIGER2021/ADDR/tl_2021_78010_addr.zip 
 wget --mirror  https://www2.census.gov/geo/tiger/TIGER2021/ADDR/tl_2021_78020_addr.zip 
 wget --mirror  https://www2.census.gov/geo/tiger/TIGER2021/ADDR/tl_2021_78030_addr.zip 
cd /gisdata/www2.census.gov/geo/tiger/TIGER2021/ADDR/
rm -f ${TMPDIR}/*.*
${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
${PSQL} -c "CREATE SCHEMA tiger_staging;"
for z in tl_*_78*_addr*.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;

${PSQL} -c "CREATE TABLE tiger_data.VI_addr(CONSTRAINT pk_VI_addr PRIMARY KEY (gid)) INHERITS(tiger.addr);ALTER TABLE tiger_data.VI_addr ALTER COLUMN statefp SET DEFAULT '78';" 
for z in *addr*.dbf; do
${SHP2PGSQL} -D   -D -s 4269 -g the_geom -W "latin1" $z tiger_staging.VI_addr | ${PSQL}
${PSQL} -c "SELECT loader_load_staged_data(lower('VI_addr'), lower('VI_addr'));"
done

${PSQL} -c "ALTER TABLE tiger_data.VI_addr ADD CONSTRAINT chk_statefp CHECK (statefp = '78');"
	${PSQL} -c "CREATE INDEX idx_tiger_data_VI_addr_least_address ON tiger_data.VI_addr USING btree (least_hn(fromhn,tohn) );"
	${PSQL} -c "CREATE INDEX idx_tiger_data_VI_addr_tlid_statefp ON tiger_data.VI_addr USING btree (tlid, statefp);"
	${PSQL} -c "CREATE INDEX idx_tiger_data_VI_addr_zip ON tiger_data.VI_addr USING btree (zip);"
	${PSQL} -c "CREATE TABLE tiger_data.VI_zip_state(CONSTRAINT pk_VI_zip_state PRIMARY KEY(zip,stusps)) INHERITS(tiger.zip_state); "
	${PSQL} -c "INSERT INTO tiger_data.VI_zip_state(zip,stusps,statefp) SELECT DISTINCT zip, 'VI', '78' FROM tiger_data.VI_addr WHERE zip is not null;"
	${PSQL} -c "ALTER TABLE tiger_data.VI_zip_state ADD CONSTRAINT chk_statefp CHECK (statefp = '78');"
	${PSQL} -c "vacuum analyze tiger_data.VI_addr;"