gisdata.txt

CREATE EXTENSION if not exists  postgis;
CREATE EXTENSION if not exists  postgis_raster;
create extension if not exists "fuzzystrmatch";
create extension if not exists "postgis_tiger_geocoder";
CREATE EXTENSION if not exists  postgis_topology;
CREATE EXTENSION if not exists address_standardizer;
ALTER DATABASE deals_on_home_dev SET search_path=public, tiger;

SHOW search_path;
ALTER EXTENSION postgis UPDATE;
ALTER EXTENSION postgis_tiger_geocoder UPDATE;
SELECT install_missing_indexes();
 select pprint_addy(addy), ST_AsText(geomout), rating
FROM geocode('8232 South Houston Avenue, Chicago, Illinois 60617',1);

SELECT normalize_address('5879 North Kolmar Avenue, Chicago, IL 60646');

select version();
SELECT PostGIS_Full_Version();


WITH shapes (geom, attribute) AS (
  VALUES(
    (SELECT ST_MakeLine(ST_MakePoint(0,0), ST_MakePoint(10,10))), 2),
    ((SELECT ST_Envelope(ST_MakeBox2d(ST_MakePoint(0,0), ST_MakePoint(10,10)))), 3)
  )
  SELECT
      concat(
         '<path d= "',
         ST_AsSVG(geom,1), '" ',
         CASE WHEN attribute = 0 THEN 'stroke="red" stroke-width="3" fill="none"'
         ELSE 'stroke="black" stroke-width="2" fill="green"' END,
         ' />')
 FROM shapes;


 select pprint_addy(addy), ST_AsText(geomout), rating
FROM geocode('8232 South Houston Avenue, Chicago, Illinois 60617',2);




set -e -u
TMPDIR="/gisdata/temp/"
UNZIPTOOL=unzip
WGETTOOL="/usr/bin/wget"
#export PGBIN=/usr/lib/postgresql/9.4/bin
export PGBIN=/usr/local/pgsql/bin
export PGPORT=5432
export PGHOST=localhost
export PGUSER=postgres
export PGPASSWORD=password
export PGDATABASE=db_name
PSQL=${PGBIN}/psql
SHP2PGSQL=${PGBIN}/shp2pgsql
cd /gisdata


# ${PSQL} -c "DROP TABLE IF EXISTS tiger_data.IL_place;" 
# ${PSQL} -c "DROP TABLE IF EXISTS tiger_data.IL_cousub;" 
# ${PSQL} -c "DROP TABLE IF EXISTS tiger_data.IL_tract;"
# ${PSQL} -c "DROP TABLE IF EXISTS tiger_data.IL_bg;"
# ${PSQL} -c "DROP TABLE IF EXISTS tiger_data.IL_zcta5;"
# ${PSQL} -c "DROP TABLE IF EXISTS tiger_data.IL_faces;"
# ${PSQL} -c "DROP TABLE IF EXISTS tiger_data.IL_featnames;"
# ${PSQL} -c "DROP TABLE IF EXISTS tiger_data.IL_edges;"
# ${PSQL} -c "DROP TABLE IF EXISTS tiger_data.IL_zip_state_loc;"
# ${PSQL} -c "DROP TABLE IF EXISTS tiger_data.IL_zip_lookup_base;"
# ${PSQL} -c "DROP TABLE IF EXISTS tiger_data.IL_addr;"
# ${PSQL} -c "DROP TABLE IF EXISTS tiger_data.IL_zip_state;"
# ${PSQL} -c "DROP TABLE IF EXISTS tiger_data.IL_tabblock;"

${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
${PSQL} -c "CREATE SCHEMA tiger_staging;"

cd /gisdata
wget ftp://ftp2.census.gov/geo/tiger/TIGER2015/STATE/ --no-parent --relative --recursive --level=1 --accept=zip --mirror --reject=html 
cd /gisdata/ftp2.census.gov/geo/tiger/TIGER2015/STATE
rm -f ${TMPDIR}/*.*
${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
${PSQL} -c "CREATE SCHEMA tiger_staging;"
for z in tl_*state.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;

${PSQL} -c "CREATE TABLE tiger_data.state_all(CONSTRAINT pk_state_all PRIMARY KEY (statefp),CONSTRAINT uidx_state_all_stusps  UNIQUE (stusps), CONSTRAINT uidx_state_all_gid UNIQUE (gid) ) INHERITS(tiger.state); "
${SHP2PGSQL} -D -c -s 4269 -g the_geom   -W "latin1" tl_2015_us_state.dbf tiger_staging.state | ${PSQL}
${PSQL} -c "SELECT loader_load_staged_data(lower('state'), lower('state_all')); "
${PSQL} -c "CREATE INDEX tiger_data_state_all_the_geom_gist ON tiger_data.state_all USING gist(the_geom);"
${PSQL} -c "VACUUM ANALYZE tiger_data.state_all"

cd /gisdata
wget ftp://ftp2.census.gov/geo/tiger/TIGER2015/COUNTY/ --no-parent --relative --recursive --level=1 --accept=zip --mirror --reject=html 
cd /gisdata/ftp2.census.gov/geo/tiger/TIGER2015/COUNTY
rm -f ${TMPDIR}/*.*
${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
${PSQL} -c "CREATE SCHEMA tiger_staging;"
for z in tl_*county.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;

${PSQL} -c "CREATE TABLE tiger_data.county_all(CONSTRAINT pk_tiger_data_county_all PRIMARY KEY (cntyidfp),CONSTRAINT uidx_tiger_data_county_all_gid UNIQUE (gid)  ) INHERITS(tiger.county); " 
${SHP2PGSQL} -D -c -s 4269 -g the_geom   -W "latin1" tl_2015_us_county.dbf tiger_staging.county | ${PSQL}
${PSQL} -c "ALTER TABLE tiger_staging.county RENAME geoid TO cntyidfp;  SELECT loader_load_staged_data(lower('county'), lower('county_all'));"
${PSQL} -c "CREATE INDEX tiger_data_county_the_geom_gist ON tiger_data.county_all USING gist(the_geom);"
${PSQL} -c "CREATE UNIQUE INDEX uidx_tiger_data_county_all_statefp_countyfp ON tiger_data.county_all USING btree(statefp,countyfp);"
${PSQL} -c "CREATE TABLE tiger_data.county_all_lookup ( CONSTRAINT pk_county_all_lookup PRIMARY KEY (st_code, co_code)) INHERITS (tiger.county_lookup);"
${PSQL} -c "VACUUM ANALYZE tiger_data.county_all;"
${PSQL} -c "INSERT INTO tiger_data.county_all_lookup(st_code, state, co_code, name) SELECT CAST(s.statefp as integer), s.abbrev, CAST(c.countyfp as integer), c.name FROM tiger_data.county_all As c INNER JOIN state_lookup As s ON s.statefp = c.statefp;"
${PSQL} -c "VACUUM ANALYZE tiger_data.county_all_lookup;" 


cd /gisdata
wget ftp://ftp2.census.gov/geo/tiger/TIGER2015/PLACE/tl_*_17_* --no-parent --relative --recursive --level=2 --accept=zip --mirror --reject=html 
cd /gisdata/ftp2.census.gov/geo/tiger/TIGER2015/PLACE
rm -f ${TMPDIR}/*.*
for z in tl_*_17*_place.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;

${PSQL} -c "CREATE TABLE tiger_data.IL_place(CONSTRAINT pk_IL_place PRIMARY KEY (plcidfp) ) INHERITS(tiger.place);" 
${SHP2PGSQL} -D -c -s 4269 -g the_geom   -W "latin1" tl_2015_17_place.dbf tiger_staging.il_place | ${PSQL}
${PSQL} -c "ALTER TABLE tiger_staging.IL_place RENAME geoid TO plcidfp;SELECT loader_load_staged_data(lower('IL_place'), lower('IL_place')); ALTER TABLE tiger_data.IL_place ADD CONSTRAINT uidx_IL_place_gid UNIQUE (gid);"
${PSQL} -c "CREATE INDEX idx_IL_place_soundex_name ON tiger_data.IL_place USING btree (soundex(name));" 
${PSQL} -c "CREATE INDEX tiger_data_IL_place_the_geom_gist ON tiger_data.IL_place USING gist(the_geom);"
${PSQL} -c "ALTER TABLE tiger_data.IL_place ADD CONSTRAINT chk_statefp CHECK (statefp = '17');"


cd /gisdata
wget ftp://ftp2.census.gov/geo/tiger/TIGER2015/COUSUB/tl_*_17_* --no-parent --relative --recursive --level=2 --accept=zip --mirror --reject=html 
cd /gisdata/ftp2.census.gov/geo/tiger/TIGER2015/COUSUB
rm -f ${TMPDIR}/*.*
for z in tl_*_17*_cousub.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;
${PSQL} -c "CREATE TABLE tiger_data.IL_cousub(CONSTRAINT pk_IL_cousub PRIMARY KEY (cosbidfp), CONSTRAINT uidx_IL_cousub_gid UNIQUE (gid)) INHERITS(tiger.cousub);" 
${SHP2PGSQL} -D -c -s 4269 -g the_geom   -W "latin1" tl_2015_17_cousub.dbf tiger_staging.il_cousub | ${PSQL}
${PSQL} -c "ALTER TABLE tiger_staging.IL_cousub RENAME geoid TO cosbidfp;SELECT loader_load_staged_data(lower('IL_cousub'), lower('IL_cousub')); ALTER TABLE tiger_data.IL_cousub ADD CONSTRAINT chk_statefp CHECK (statefp = '17');"
${PSQL} -c "CREATE INDEX tiger_data_IL_cousub_the_geom_gist ON tiger_data.IL_cousub USING gist(the_geom);"
${PSQL} -c "CREATE INDEX idx_tiger_data_IL_cousub_countyfp ON tiger_data.IL_cousub USING btree(countyfp);"

cd /gisdata
wget ftp://ftp2.census.gov/geo/tiger/TIGER2015/TRACT/tl_*_17_* --no-parent --relative --recursive --level=2 --accept=zip --mirror --reject=html 
cd /gisdata/ftp2.census.gov/geo/tiger/TIGER2015/TRACT
rm -f ${TMPDIR}/*.*
for z in tl_*_17*_tract.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;
${PSQL} -c "CREATE TABLE tiger_data.IL_tract(CONSTRAINT pk_IL_tract PRIMARY KEY (tract_id) ) INHERITS(tiger.tract); " 
${SHP2PGSQL} -D -c -s 4269 -g the_geom   -W "latin1" tl_2015_17_tract.dbf tiger_staging.il_tract | ${PSQL}
${PSQL} -c "ALTER TABLE tiger_staging.IL_tract RENAME geoid TO tract_id;  SELECT loader_load_staged_data(lower('IL_tract'), lower('IL_tract')); "
${PSQL} -c "CREATE INDEX tiger_data_IL_tract_the_geom_gist ON tiger_data.IL_tract USING gist(the_geom);"
${PSQL} -c "VACUUM ANALYZE tiger_data.IL_tract;"
${PSQL} -c "ALTER TABLE tiger_data.IL_tract ADD CONSTRAINT chk_statefp CHECK (statefp = '17');"

cd /gisdata
wget ftp://ftp2.census.gov/geo/tiger/TIGER2015/BG/tl_*_17_* --no-parent --relative --recursive --level=2 --accept=zip --mirror --reject=html 
cd /gisdata/ftp2.census.gov/geo/tiger/TIGER2015/BG
rm -f ${TMPDIR}/*.*
for z in tl_*_17*_bg.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;
${PSQL} -c "CREATE TABLE tiger_data.IL_bg(CONSTRAINT pk_IL_bg PRIMARY KEY (bg_id)) INHERITS(tiger.bg);" 
${SHP2PGSQL} -D -c -s 4269 -g the_geom   -W "latin1" tl_2015_17_bg.dbf tiger_staging.il_bg | ${PSQL}
${PSQL} -c "ALTER TABLE tiger_staging.IL_bg RENAME geoid TO bg_id;  SELECT loader_load_staged_data(lower('IL_bg'), lower('IL_bg')); "
${PSQL} -c "ALTER TABLE tiger_data.IL_bg ADD CONSTRAINT chk_statefp CHECK (statefp = '17');"
${PSQL} -c "CREATE INDEX tiger_data_IL_bg_the_geom_gist ON tiger_data.IL_bg USING gist(the_geom);"
${PSQL} -c "vacuum analyze tiger_data.IL_bg;"

cd /gisdata
wget ftp://ftp2.census.gov/geo/tiger/TIGER2010/ZCTA5/2010/tl_*_17* --no-parent --relative --recursive --level=2 --accept=zip --mirror --reject=html 
cd /gisdata/ftp2.census.gov/geo/tiger/TIGER2010/ZCTA5/2010
rm -f ${TMPDIR}/*.*
for z in tl_*_17*_zcta510*.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;
${PSQL} -c "CREATE TABLE tiger_data.IL_zcta5(CONSTRAINT pk_IL_zcta5 PRIMARY KEY (zcta5ce,statefp), CONSTRAINT uidx_IL_zcta5_gid UNIQUE (gid)) INHERITS(tiger.zcta5);" 
for z in *zcta510*.dbf; do 
${SHP2PGSQL} -D   -D -s 4269 -g the_geom -W "latin1" $z tiger_staging.IL_zcta510 | ${PSQL} 
${PSQL} -c "SELECT loader_load_staged_data(lower('IL_zcta510'), lower('IL_zcta5'));"
done
${PSQL} -c "ALTER TABLE tiger_data.IL_zcta5 ADD CONSTRAINT chk_statefp CHECK (statefp = '17');"
${PSQL} -c "CREATE INDEX tiger_data_IL_zcta5_the_geom_gist ON tiger_data.IL_zcta5 USING gist(the_geom);"

cd /gisdata
wget ftp://ftp2.census.gov/geo/tiger/TIGER2015/FACES/tl_*_17* --no-parent --relative --recursive --level=2 --accept=zip --mirror --reject=html 
cd /gisdata/ftp2.census.gov/geo/tiger/TIGER2015/FACES/
rm -f ${TMPDIR}/*.*
for z in tl_*_17*_faces*.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;
${PSQL} -c "CREATE TABLE tiger_data.IL_faces(CONSTRAINT pk_IL_faces PRIMARY KEY (gid)) INHERITS(tiger.faces);" 
for z in *faces*.dbf; do 
${SHP2PGSQL} -D -s 4269 -g the_geom -W "latin1" $z tiger_staging.IL_faces | ${PSQL} 
${PSQL} -c "SELECT loader_load_staged_data(lower('IL_faces'), lower('IL_faces'));"
done
${PSQL} -c "CREATE INDEX tiger_data_IL_faces_the_geom_gist ON tiger_data.IL_faces USING gist(the_geom);"
${PSQL} -c "CREATE INDEX idx_tiger_data_IL_faces_tfid ON tiger_data.IL_faces USING btree (tfid);"
${PSQL} -c "CREATE INDEX idx_tiger_data_IL_faces_countyfp ON tiger_data.IL_faces USING btree (countyfp);"
${PSQL} -c "ALTER TABLE tiger_data.IL_faces ADD CONSTRAINT chk_statefp CHECK (statefp = '17');"
${PSQL} -c "vacuum analyze tiger_data.IL_faces;"

cd /gisdata
wget ftp://ftp2.census.gov/geo/tiger/TIGER2015/FEATNAMES/tl_*_17* --no-parent --relative --recursive --level=2 --accept=zip --mirror --reject=html 
cd /gisdata/ftp2.census.gov/geo/tiger/TIGER2015/FEATNAMES/
rm -f ${TMPDIR}/*.*
for z in tl_*_17*_featnames*.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;
${PSQL} -c "CREATE TABLE tiger_data.IL_featnames(CONSTRAINT pk_IL_featnames PRIMARY KEY (gid)) INHERITS(tiger.featnames);ALTER TABLE tiger_data.IL_featnames ALTER COLUMN statefp SET DEFAULT '17';" 
for z in *featnames*.dbf; do 
${SHP2PGSQL} -D -s 4269 -g the_geom -W "latin1" $z tiger_staging.IL_featnames | ${PSQL} 
${PSQL} -c "SELECT loader_load_staged_data(lower('IL_featnames'), lower('IL_featnames'));"
done
${PSQL} -c "CREATE INDEX idx_tiger_data_IL_featnames_snd_name ON tiger_data.IL_featnames USING btree (soundex(name));"
${PSQL} -c "CREATE INDEX idx_tiger_data_IL_featnames_lname ON tiger_data.IL_featnames USING btree (lower(name));"
${PSQL} -c "CREATE INDEX idx_tiger_data_IL_featnames_tlid_statefp ON tiger_data.IL_featnames USING btree (tlid,statefp);"
${PSQL} -c "ALTER TABLE tiger_data.IL_featnames ADD CONSTRAINT chk_statefp CHECK (statefp = '17');"
${PSQL} -c "vacuum analyze tiger_data.IL_featnames;"

cd /gisdata
wget ftp://ftp2.census.gov/geo/tiger/TIGER2015/EDGES/tl_*_17* --no-parent --relative --recursive --level=2 --accept=zip --mirror --reject=html 
cd /gisdata/ftp2.census.gov/geo/tiger/TIGER2015/EDGES/
rm -f ${TMPDIR}/*.*
for z in tl_*_17*_edges*.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;
${PSQL} -c "CREATE TABLE tiger_data.IL_edges(CONSTRAINT pk_IL_edges PRIMARY KEY (gid)) INHERITS(tiger.edges);" 
for z in *edges*.dbf; do 
${SHP2PGSQL} -D -s 4269 -g the_geom -W "latin1" $z tiger_staging.IL_edges | ${PSQL} 
${PSQL} -c "SELECT loader_load_staged_data(lower('IL_edges'), lower('IL_edges'));"
done
${PSQL} -c "ALTER TABLE tiger_data.IL_edges ADD CONSTRAINT chk_statefp CHECK (statefp = '17');"
${PSQL} -c "CREATE INDEX idx_tiger_data_IL_edges_tlid ON tiger_data.IL_edges USING btree (tlid);"
${PSQL} -c "CREATE INDEX idx_tiger_data_IL_edgestfidr ON tiger_data.IL_edges USING btree (tfidr);"
${PSQL} -c "CREATE INDEX idx_tiger_data_IL_edges_tfidl ON tiger_data.IL_edges USING btree (tfidl);"
${PSQL} -c "CREATE INDEX idx_tiger_data_IL_edges_countyfp ON tiger_data.IL_edges USING btree (countyfp);"
${PSQL} -c "CREATE INDEX tiger_data_IL_edges_the_geom_gist ON tiger_data.IL_edges USING gist(the_geom);"
${PSQL} -c "CREATE INDEX idx_tiger_data_IL_edges_zipl ON tiger_data.IL_edges USING btree (zipl);"
${PSQL} -c "CREATE TABLE tiger_data.IL_zip_state_loc(CONSTRAINT pk_IL_zip_state_loc PRIMARY KEY(zip,stusps,place)) INHERITS(tiger.zip_state_loc);"
${PSQL} -c "INSERT INTO tiger_data.IL_zip_state_loc(zip,stusps,statefp,place) SELECT DISTINCT e.zipl, 'IL', '17', p.name FROM tiger_data.IL_edges AS e INNER JOIN tiger_data.IL_faces AS f ON (e.tfidl = f.tfid OR e.tfidr = f.tfid) INNER JOIN tiger_data.IL_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_IL_zip_state_loc_place ON tiger_data.IL_zip_state_loc USING btree(soundex(place));"
${PSQL} -c "ALTER TABLE tiger_data.IL_zip_state_loc ADD CONSTRAINT chk_statefp CHECK (statefp = '17');"
${PSQL} -c "vacuum analyze tiger_data.IL_edges;"
${PSQL} -c "vacuum analyze tiger_data.IL_zip_state_loc;"
${PSQL} -c "CREATE TABLE tiger_data.IL_zip_lookup_base(CONSTRAINT pk_IL_zip_state_loc_city PRIMARY KEY(zip,state, county, city, statefp)) INHERITS(tiger.zip_lookup_base);"
${PSQL} -c "INSERT INTO tiger_data.IL_zip_lookup_base(zip,state,county,city, statefp) SELECT DISTINCT e.zipl, 'IL', c.name,p.name,'17'  FROM tiger_data.IL_edges AS e INNER JOIN tiger.county As c  ON (e.countyfp = c.countyfp AND e.statefp = c.statefp AND e.statefp = '17') INNER JOIN tiger_data.IL_faces AS f ON (e.tfidl = f.tfid OR e.tfidr = f.tfid) INNER JOIN tiger_data.IL_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.IL_zip_lookup_base ADD CONSTRAINT chk_statefp CHECK (statefp = '17');"
${PSQL} -c "CREATE INDEX idx_tiger_data_IL_zip_lookup_base_citysnd ON tiger_data.IL_zip_lookup_base USING btree(soundex(city));" 

cd /gisdata
wget ftp://ftp2.census.gov/geo/tiger/TIGER2015/ADDR/tl_*_17* --no-parent --relative --recursive --level=2 --accept=zip --mirror --reject=html 
cd /gisdata/ftp2.census.gov/geo/tiger/TIGER2015/ADDR/
rm -f ${TMPDIR}/*.*
for z in tl_*_17*_addr*.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;
${PSQL} -c "CREATE TABLE tiger_data.IL_addr(CONSTRAINT pk_IL_addr PRIMARY KEY (gid)) INHERITS(tiger.addr);ALTER TABLE tiger_data.IL_addr ALTER COLUMN statefp SET DEFAULT '17';" 
for z in *addr*.dbf; do 
${SHP2PGSQL} -D -s 4269 -g the_geom -W "latin1" $z tiger_staging.IL_addr | ${PSQL} 
${PSQL} -c "SELECT loader_load_staged_data(lower('IL_addr'), lower('IL_addr'));"
done
${PSQL} -c "ALTER TABLE tiger_data.IL_addr ADD CONSTRAINT chk_statefp CHECK (statefp = '17');"
${PSQL} -c "CREATE INDEX idx_tiger_data_IL_addr_least_address ON tiger_data.IL_addr USING btree (least_hn(fromhn,tohn) );"
${PSQL} -c "CREATE INDEX idx_tiger_data_IL_addr_tlid_statefp ON tiger_data.IL_addr USING btree (tlid, statefp);"
${PSQL} -c "CREATE INDEX idx_tiger_data_IL_addr_zip ON tiger_data.IL_addr USING btree (zip);"
${PSQL} -c "CREATE TABLE tiger_data.IL_zip_state(CONSTRAINT pk_IL_zip_state PRIMARY KEY(zip,stusps)) INHERITS(tiger.zip_state); "
${PSQL} -c "INSERT INTO tiger_data.IL_zip_state(zip,stusps,statefp) SELECT DISTINCT zip, 'IL', '17' FROM tiger_data.IL_addr WHERE zip is not null;"
${PSQL} -c "ALTER TABLE tiger_data.IL_zip_state ADD CONSTRAINT chk_statefp CHECK (statefp = '17');"
${PSQL} -c "vacuum analyze tiger_data.IL_addr;"

cd /gisdata
wget ftp://ftp2.census.gov/geo/tiger/TIGER2015/TABBLOCK/tl_*_17* --no-parent --relative --recursive --level=2 --accept=zip --mirror --reject=html 
cd /gisdata/ftp2.census.gov/geo/tiger/TIGER2015/TABBLOCK/
rm -f ${TMPDIR}/*.*
for z in tl_*_17*_tabblock*.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;
${PSQL} -c "CREATE TABLE tiger_data.IL_tabblock(CONSTRAINT pk_IL_tabblock PRIMARY KEY (tabblock_id)) INHERITS(tiger.tabblock);" 
for z in *tabblock*.dbf; do 
${SHP2PGSQL} -D -s 4269 -g the_geom -W "latin1" $z tiger_staging.IL_tabblock | ${PSQL} 
${PSQL} -c "SELECT loader_load_staged_data(lower('IL_tabblock'), lower('IL_tabblock'));"
done
${PSQL} -c "ALTER TABLE tiger_data.IL_tabblock ADD CONSTRAINT chk_statefp CHECK (statefp = '17');"
${PSQL} -c "CREATE INDEX tiger_data_IL_tabblock_the_geom_gist ON tiger_data.IL_tabblock USING gist(the_geom);"
${PSQL} -c "vacuum analyze tiger_data.IL_tabblock;"