asmedrano/fips.sh

## fips.sh
declare -A FIPS
declare -A STNAME
FIPS[AK]=02; STNAME[AK]=Alaska
FIPS[AL]=01; STNAME[AL]=Alabama
FIPS[AR]=05; STNAME[AR]=Arkansas
FIPS[AS]=60; STNAME[AS]=American Samoa
FIPS[AZ]=04; STNAME[AZ]=Arizona
FIPS[CA]=06; STNAME[CA]=California
FIPS[CO]=08; STNAME[CO]=Colorado
FIPS[CT]=09; STNAME[CT]=Connecticut
FIPS[DC]=11; STNAME[DC]=District_of_Columbia
FIPS[DE]=10; STNAME[DE]=Delaware
FIPS[FL]=12; STNAME[FL]=Florida
FIPS[GA]=13; STNAME[GA]=Georgia
FIPS[GU]=66; STNAME[GU]=Guam
FIPS[HI]=15; STNAME[HI]=Hawaii
FIPS[IA]=19; STNAME[IA]=Iowa
FIPS[ID]=16; STNAME[ID]=Idaho
FIPS[IL]=17; STNAME[IL]=Illinois
FIPS[IN]=18; STNAME[IN]=Indiana
FIPS[KS]=20; STNAME[KS]=Kansas
FIPS[KY]=21; STNAME[KY]=Kentucky
FIPS[LA]=22; STNAME[LA]=Louisiana
FIPS[MA]=25; STNAME[MA]=Massachusetts
FIPS[MD]=24; STNAME[MD]=Maryland
FIPS[ME]=23; STNAME[ME]=Maine
FIPS[MI]=26; STNAME[MI]=Michigan
FIPS[MN]=27; STNAME[MN]=Minnesota
FIPS[MO]=29; STNAME[MO]=Missouri
FIPS[MS]=28; STNAME[MS]=Mississippi
FIPS[MT]=30; STNAME[MT]=Montana
FIPS[NC]=37; STNAME[NC]=North_Carolina
FIPS[ND]=38; STNAME[ND]=North_Dakota
FIPS[NE]=31; STNAME[NE]=Nebraska
FIPS[NH]=33; STNAME[NH]=New_Hampshire
FIPS[NJ]=34; STNAME[NJ]=New_Jersey
FIPS[NM]=35; STNAME[NM]=New_Mexico
FIPS[NV]=32; STNAME[NV]=Nevada
FIPS[NY]=36; STNAME[NY]=New_York
FIPS[OH]=39; STNAME[OH]=Ohio
FIPS[OK]=40; STNAME[OK]=Oklahoma
FIPS[OR]=41; STNAME[OR]=Oregon
FIPS[PA]=42; STNAME[PA]=Pennsylvania
FIPS[PR]=72; STNAME[PR]=Puerto_Rico
FIPS[RI]=44; STNAME[RI]=Rhode_Island
FIPS[SC]=45; STNAME[SC]=South_Carolina
FIPS[SD]=46; STNAME[SD]=South_Dakota
FIPS[TN]=47; STNAME[TN]=Tennessee
FIPS[TX]=48; STNAME[TX]=Texas
FIPS[UT]=49; STNAME[UT]=Utah
FIPS[VA]=51; STNAME[VA]=Virginia
FIPS[VI]=78; STNAME[VI]=Virgin_Islands
FIPS[VT]=50; STNAME[VT]=Vermont
FIPS[WA]=53; STNAME[WA]=Washington
FIPS[WI]=55; STNAME[WI]=Wisconsin
FIPS[WV]=54; STNAME[WV]=West_Virginia
FIPS[WY]=56; STNAME[WY]=Wyoming

## state_info.sh
set -e
set -u

function getstinfo () {
	local ST=$1; shift

	local STCODES=(AK AL AR AS AZ CA CO CT DC DE FL GA GU HI IA ID IL IN KS KY
		LA MA MD ME MI MN MO MS MT NC ND NE NH NJ NM NV NY OH OK OR PA PR RI
		SC SD TN TX UT VA VI VT WA WI WV WY)

	local STNAMES=(Alaska Alabama Arkansas American_Samoa Arizona California
		 Colorado Connecticut District_Of_Columbia Delaware Florida Georgia
		 Guam Hawaii Iowa Idaho Illinois Indiana Kansas Kentucky Louisiana
		 Massachusetts Maryland Maine Michigan Minnesota Missouri Mississippi
		 Montana North_Carolina North_Dakota Nebraska New_Hampshire New_Jersey
		 New_Mexico Nevada New_York Ohio Oklahoma Oregon Pennsylvania
		 Puerto_Rico Rhode_Island South_Carolina South_Dakota Tennessee Texas
		 Utah Virginia Virgin_Islands Vermont Washington Wisconsin
		 West_Virginia Wyoming)

	local STFIPS=(02 01 05 60 04 06 08 09 11 10 12 13 66 15 19 16 17 18 20 21 22
		25 24 23 26 27 29 28 30 37 38 31 33 34 35 32 36 39 40 41 42 72 44 45
		46 47 48 49 51 78 50 53 55 54 56)

	for (( i=0; i<${#STCODES[*]}; i++ )) ; do
		if [[  ${STCODES[$i]} == $ST ]]; then
			echo ${STFIPS[$i]}_${STNAMES[i]} ${STNAMES[i]}  ${STFIPS[$i]}
			return 0
		fi
	done

	echo "\"$ST\": code not found" 1>&2
	return 1
}

FOO=$(getstinfo $1)
for x in $FOO; do
	echo $x
done;

# CSTNAME=${FIPS[$STABB]}_${STATEFULLNAME[$STABB]}
# STNAME=${STATEFULLNAME[$STABB]}
# STFIPS=${FIPS[$STABB]}
exit 1

## tiger_postgis_loader.sh
## Bash options
set -e
set -u

: <<TODO
    Command line switches: Option for clean, option for download, everything else really.
    Fix code to drop ALL tables with state prefix.
    Guard against using a cluttered /tmp/gisdata
TODO


####################################################################
##  COMMAND LINE OPTIONS -- not ready for prime time.  Need defaults
####################################################################
# source fips.sh

# STABB=EMPTY
# DOWNLOAD=TRUE
# DROPTABLES=TRUE
# TINSTALL=FALSE
# CLEANUP=TRUE
# CENSUS_URL="www2.census.gov/geo/pvs/tiger2010st"

# for x in "$@"; do
# 	case ${x%=*} in
# 		--state)
# 			STABB=${x#*=} ;;
# 		--download)
# 			DOWNLOAD=${x#*=} ;;
# 		--droptables)
# 			DROPTABLES=${x#*=} ;;
# 		--tinstall)
# 			TINSTALL=${x#*=} ;;
# 		--cleanup)
# 			CLEANUP=${x#*=} ;;
# 		--census-url)
# 			CENSUS_URL=${x#*=} ;;
# 	esac
# done

# CSTNAME=${FIPS[$STABB]}_${STATEFULLNAME[$STABB]}
# STNAME=${STATEFULLNAME[$STABB]}
# STFIPS=${FIPS[$STABB]}

# if [[ $STABB == EMPTY ]]; then
# 	echo "Must specify a state with --state=XX option"
# 	exit 1
# fi

####################################################################
##  Per user and US state variables
####################################################################

# Choose your state
 STABB=OR
 CSTNAME=41_Oregon
 STNAME=Oregon
 STFIPS=41

# STABB=WA
# CSTNAME=53_Washington
# STNAME=Washington
# STFIPS=53

DOWNLOAD=TRUE					# download new files?
DROPTABLES=TRUE					# drop tables for this state
TINSTALL=FALSE					# install geocoder -- experimental!
CLEANUP=TRUE

## Choose your download source
CENSUS_URL="www2.census.gov/geo/pvs/tiger2010st"

## Choose your database
export PGPORT=5432
export PGHOST=localhost
export PGDATABASE=geocoder
## export PGUSER=""             # probably best to set this in the shell beforehand
## export PGPASSWORD=""         # probably best to set this in the shell beforehand

## test for a running PG, abort if not available

if [[ ! $(psql --command="select 1") ]] ; then
	echo "POSTGRES NOT RUNNING.  ABORTING." 1>&2
	exit 1
else
	echo "POSTGRES RUNNING. PROCEEDING." 1>&2
fi


####################################################################
##  Infrastructure variables -- probably dont need to change
####################################################################
TMPDIR="/tmp/gisdata"
UNZIPTOOL=unzip
_PGOPTS=" -v ON_ERROR_STOP=1 -v AUTOCOMMIT=on"


#################################################################
### Functions
#################################################################
function getshp(){
    local STFIPS=$1; local STABB=$2; local TOPIC=$3;
    local F=${STABB}_${TOPIC}.sql
    shp2pgsql -c -s 4269 -g the_geom -W latin1 tl_2010_${STFIPS}_${TOPIC}.dbf  "tiger_staging.${STABB}_${TOPIC}" |  iconv -f LATIN1 -t UTF-8 > $F
    psql -q $_PGOPTS -f $F
}


###################################################################
##  Begin script.  If you need to change anything below, please file a
##  bug report
###################################################################
echo "START LOADING" 1>&2
if [ ! -e $TMPDIR ]; then
   mkdir -p $TMPDIR
fi
pushd  $TMPDIR
rm -f "*.sql"


## Download new data if appropriate -- snarf and unzip into current dir
if [[ $DOWNLOAD == TRUE ]]; then
	rm -f *.dbf *.sql *.xml *.err *.out *.prj *.shp *.xml *.shx
	wget "http://${CENSUS_URL}/${CSTNAME}/" \
		--no-directories --no-parent --relative --recursive --level=2 --accept=zip,txt --mirror --timestamping --reject=html
	for z in *.zip; do
		unzip $z -d $TMPDIR
	done
fi

## Drop tables if appropriate
set +e
if [[ $DROPTABLES == TRUE ]]; then
	psql -e --no-psqlrc --variable=AUTOCOMMIT=on --variable=ON_ERROR_STOP=off <<EOF
		drop table tiger_data.${STABB}_addr cascade;
		drop table tiger_data.${STABB}_county cascade;
		drop table tiger_data.${STABB}_county_lookup cascade;
		drop table tiger_data.${STABB}_cousub cascade;
		drop table tiger_data.${STABB}_edges cascade;
		drop table tiger_data.${STABB}_faces cascade;
		drop table tiger_data.${STABB}_featnames cascade;
		drop table tiger_data.${STABB}_place cascade;
		drop table tiger_data.${STABB}_state cascade;
		drop table tiger_data.${STABB}_zip_lookup_base cascade;
		drop table tiger_data.${STABB}_zip_state_loc cascade;
EOF
fi
set -e


## Set up staging schemas
if psql  $_PGOPTS -c "DROP SCHEMA tiger_staging CASCADE;"; then
   echo "Dropping tiger_staging schema!" 1>&2
else
   echo "No tiger_staging schema!" 1>&2
fi
psql $_PGOPTS -c "CREATE SCHEMA tiger_staging;"


#################################################################
## Begin loading code
#################################################################

## load state boundaries.
getshp $STFIPS $STABB state10
psql -q ${_PGOPTS}  <<EOF
    BEGIN;
    CREATE TABLE tiger_data.${STABB}_state(CONSTRAINT pk_${STABB}_state PRIMARY KEY (gid) ) INHERITS(state);
    SELECT loader_load_staged_data(lower('${STABB}_state10'), lower('${STABB}_state'));
    ALTER TABLE tiger_data.${STABB}_state ADD CONSTRAINT uidx_${STABB}_state_stusps UNIQUE (stusps);
    CREATE INDEX tiger_data_${STABB}_state_the_geom_gist ON tiger_data.${STABB}_state USING gist(the_geom);
    COMMIT;
    VACUUM ANALYZE tiger_data.${STABB}_state;
EOF


## load county boundaries
getshp $STFIPS $STABB county10
psql -q $_PGOPTS <<EOF
    BEGIN;
    CREATE TABLE tiger_data.${STABB}_county(CONSTRAINT pk_${STABB}_county PRIMARY KEY (gid) ) INHERITS(county);
    ALTER TABLE tiger_staging.${STABB}_county10 RENAME geoid10 TO cntyidfp;
    SELECT loader_load_staged_data(lower('${STABB}_county10'), lower('${STABB}_county'));
    ALTER TABLE tiger_data.${STABB}_county ADD CONSTRAINT uidx_${STABB}_county_cntyidfp UNIQUE (cntyidfp);
    CREATE INDEX tiger_data_${STABB}_county_the_geom_gist ON tiger_data.${STABB}_county USING gist(the_geom);
    COMMIT;
    VACUUM ANALYZE tiger_data.${STABB}_county;
EOF


## create "county lookup"
psql -q $_PGOPTS <<EOF
    BEGIN;
    CREATE TABLE tiger_data.${STABB}_county_lookup ( CONSTRAINT pk_${STABB}_county_lookup PRIMARY KEY (st_code, co_code))
                      INHERITS (county_lookup);
    INSERT INTO tiger_data.${STABB}_county_lookup(st_code, state, co_code, name)
                      SELECT CAST(statefp as integer), '${STABB}', CAST(countyfp as integer), name FROM tiger_data.${STABB}_county;
    COMMIT;
    VACUUM ANALYZE tiger_data.${STABB}_county_lookup;
EOF


### create place point
getshp $STFIPS $STABB place10
psql  $_PGOPTS <<EOF
    BEGIN;
    CREATE TABLE tiger_data.${STABB}_place(CONSTRAINT pk_${STABB}_place10 PRIMARY KEY (plcidfp) ) INHERITS(place);
    ALTER TABLE tiger_staging.${STABB}_place10 RENAME geoid10 TO plcidfp;
    SELECT loader_load_staged_data(lower('${STABB}_place10'), lower('${STABB}_place'));
    ALTER TABLE tiger_data.${STABB}_place ADD CONSTRAINT uidx_${STABB}_place_gid UNIQUE (gid);
    CREATE INDEX idx_${STABB}_place_soundex_name ON tiger_data.${STABB}_place USING btree (soundex(name));
    COMMIT;
    VACUUM ANALYZE tiger_data.${STABB}_place;
EOF


### create "cousub"
getshp $STFIPS $STABB cousub10
psql -e $_PGOPTS <<EOF
    BEGIN;
    CREATE TABLE tiger_data.${STABB}_cousub(CONSTRAINT pk_${STABB}_cousub PRIMARY KEY (cosbidfp),
                              CONSTRAINT uidx_${STABB}_cousub_gid UNIQUE (gid)) INHERITS(cousub);
    ALTER TABLE tiger_staging.${STABB}_cousub10 RENAME geoid10 TO cosbidfp;
    SELECT loader_load_staged_data(lower('${STABB}_cousub10'), lower('${STABB}_cousub'));
    ALTER TABLE tiger_data.${STABB}_cousub ADD CONSTRAINT chk_statefp CHECK (statefp = '${STFIPS}');
    CREATE INDEX tiger_data_${STABB}_cousub_the_geom_gist ON tiger_data.${STABB}_cousub USING gist(the_geom);
    COMMIT;
    VACUUM ANALYZE tiger_data.${STABB}_cousub;
EOF


### create faces -- one per county
psql -q $_PGOPTS -c "CREATE TABLE tiger_data.${STABB}_faces(CONSTRAINT pk_${STABB}_faces PRIMARY KEY (gid)) INHERITS(faces);"
for Z in *faces.dbf; do
    shp2pgsql -s 4269 -g the_geom -W latin1  $Z "tiger_staging.${STABB}_faces" |  iconv -f LATIN1 -t UTF-8 > $Z.sql
    psql -q $_PGOPTS -f $Z.sql
    psql -q $_PGOPTS -c "SELECT loader_load_staged_data(lower('${STABB}_faces'), lower('${STABB}_faces'));"
done
psql -e $_PGOPTS <<EOF
    BEGIN;
    CREATE INDEX tiger_data_${STABB}_faces_the_geom_gist ON tiger_data.${STABB}_faces USING gist(the_geom);
    CREATE INDEX idx_tiger_data_${STABB}_faces_tfid ON tiger_data.${STABB}_faces USING btree (tfid);
    ALTER TABLE tiger_data.${STABB}_faces ADD CONSTRAINT chk_statefp CHECK (statefp = '${STFIPS}');
    COMMIT;
    VACUUM ANALYZE tiger_data.${STABB}_faces;
EOF


## create feature names, one per county.  First time need iconv ... weird. ...
psql -q $_PGOPTS -c "CREATE TABLE tiger_data.${STABB}_featnames(CONSTRAINT pk_${STABB}_featnames PRIMARY KEY (gid)) INHERITS(featnames);"
for Z in *featnames.dbf ; do
    shp2pgsql -s 4269 -g the_geom -W latin1  $Z "tiger_staging.${STABB}_featnames" |  iconv -f LATIN1 -t UTF-8 > $Z.sql
    psql -q $_PGOPTS -f $Z.sql
    psql -q $_PGOPTS -c "SELECT loader_load_staged_data(lower('${STABB}_featnames'), lower('${STABB}_featnames'));"
done
psql -e $_PGOPTS <<EOF
  BEGIN;
  UPDATE tiger_data.${STABB}_featnames SET statefp = '${STFIPS}'  WHERE statefp IS NULL;
  CREATE INDEX idx_tiger_data_${STABB}_featnames_snd_name ON tiger_data.${STABB}_featnames USING btree (soundex(name));
  CREATE INDEX idx_tiger_data_${STABB}_featnames_lname ON tiger_data.${STABB}_featnames USING btree (lower(name));
  CREATE INDEX idx_tiger_data_${STABB}_featnames_tlid_statefp ON tiger_data.${STABB}_featnames USING btree (tlid,statefp);
  ALTER TABLE tiger_data.${STABB}_featnames ADD CONSTRAINT chk_statefp CHECK (statefp = '${STFIPS}');
  COMMIT;
  VACUUM ANALYZE tiger_data.${STABB}_featnames;
EOF


## create edges, one per county  # tl_2010_41071_edges.dbf  *edges.dbf
psql $_PGOPTS -c "CREATE TABLE tiger_data.${STABB}_edges(CONSTRAINT pk_${STABB}_edges PRIMARY KEY (gid)) INHERITS(edges);"
for Z in  *edges.dbf; do
    shp2pgsql  -s 4269 -g the_geom -W latin1 $Z tiger_staging.${STABB}_edges | iconv -f LATIN1 -t UTF-8 > $Z.sql
    psql -q $_PGOPTS -f $Z.sql
    psql -q -c "SELECT loader_load_staged_data(lower('${STABB}_edges'), lower('${STABB}_edges'));"
done
psql -e $_PGOPTS <<EOF
    BEGIN;
    ALTER TABLE tiger_data.${STABB}_edges ADD CONSTRAINT chk_statefp CHECK (statefp = '${STFIPS}');
    CREATE INDEX idx_tiger_data_${STABB}_edges_tlid ON tiger_data.${STABB}_edges USING btree (tlid);
    CREATE INDEX idx_tiger_data_${STABB}_edges_tfidr ON tiger_data.${STABB}_edges USING btree (tfidr);
    CREATE INDEX idx_tiger_data_${STABB}_edges_tfidl ON tiger_data.${STABB}_edges USING btree (tfidl);
    CREATE INDEX tiger_data_${STABB}_edges_the_geom_gist ON tiger_data.${STABB}_edges USING gist(the_geom);
    CREATE INDEX idx_tiger_data_${STABB}_edges_zipl ON tiger_data.${STABB}_edges USING btree (zipl);
    COMMIT;
    VACUUM ANALYZE tiger_data.${STABB}_edges;
EOF


## create zip_loc data for state
psql -e $_PGOPTS <<EOF
    BEGIN;
    CREATE TABLE tiger_data.${STABB}_zip_state_loc(CONSTRAINT pk_${STABB}_zip_state_loc PRIMARY KEY(zip,stusps,place))
        INHERITS(zip_state_loc);
    INSERT INTO tiger_data.${STABB}_zip_state_loc(zip,stusps,statefp,place)
        SELECT DISTINCT e.zipl, '${STABB}', '${STFIPS}', p.name
           FROM tiger_data.${STABB}_edges AS e
               INNER JOIN tiger_data.${STABB}_faces AS f ON (e.tfidl = f.tfid AND e.tfidr = f.tfid)
               INNER JOIN tiger_data.${STABB}_place As p ON (f.statefp = p.statefp AND f.placefp = p.placefp )
        WHERE e.zipl IS NOT NULL;
    CREATE INDEX idx_tiger_data_${STABB}_zip_state_loc_place ON tiger_data.${STABB}_zip_state_loc USING btree(soundex(place));
    ALTER TABLE tiger_data.${STABB}_zip_state_loc ADD CONSTRAINT chk_statefp CHECK (statefp = '${STFIPS}');
    COMMIT;
    VACUUM ANALYZE tiger_data.${STABB}_edges;
    VACUUM ANALYZE tiger_data.${STABB}_zip_state_loc;
EOF


## create zip lookup base
psql -e $_PGOPTS <<EOF
    BEGIN;
    CREATE TABLE tiger_data.${STABB}_zip_lookup_base(CONSTRAINT pk_${STABB}_zip_state_loc_city PRIMARY KEY(zip,state, county, city, statefp))
         INHERITS(zip_lookup_base);
    INSERT INTO tiger_data.${STABB}_zip_lookup_base(zip,state,county,city, statefp)
         SELECT DISTINCT e.zipl, '${STABB}', c.name,p.name,'${STFIPS}'
              FROM tiger_data.${STABB}_edges AS e
                  INNER JOIN tiger_data.${STABB}_county As c  ON (e.countyfp = c.countyfp AND e.statefp = c.statefp AND e.statefp = '${STFIPS}')
                  INNER JOIN tiger_data.${STABB}_faces AS f ON (e.tfidl = f.tfid  AND e.tfidr = f.tfid)
                  INNER JOIN tiger_data.${STABB}_place As p ON(f.statefp = p.statefp AND f.placefp = p.placefp )
              WHERE e.zipl IS NOT NULL;
    ALTER TABLE tiger_data.${STABB}_zip_lookup_base ADD CONSTRAINT chk_statefp CHECK (statefp = '${STFIPS}');
    CREATE INDEX idx_tiger_data_${STABB}_zip_lookup_base_citysnd ON tiger_data.${STABB}_zip_lookup_base USING btree(soundex(city));
    COMMIT;
    VACUUM ANALYZE tiger_data.${STABB}_zip_lookup_base;
EOF


## create address data, one per county
psql  $_PGOPTS -c "CREATE TABLE tiger_data.${STABB}_addr(CONSTRAINT pk_${STABB}_addr PRIMARY KEY (gid)) INHERITS(addr);"
for Z in *addr.dbf; do   #  *addr.dbf
    shp2pgsql  -s 4269 -g the_geom -W latin1 $Z tiger_staging.${STABB}_addr | iconv -f LATIN1 -t UTF-8 > $Z.sql
    psql -q $_PGOPTS -f $Z.sql
    psql -q -c "SELECT loader_load_staged_data(lower('${STABB}_addr'), lower('${STABB}_addr'));"
done
psql -e $_PGOPTS <<EOF
    BEGIN;
    UPDATE tiger_data.${STABB}_addr SET statefp = '${STFIPS}' WHERE statefp IS NULL;
    ALTER TABLE tiger_data.${STABB}_addr ADD CONSTRAINT chk_statefp CHECK (statefp = '${STFIPS}');
    CREATE INDEX idx_tiger_data_${STABB}_addr_least_address ON tiger_data.${STABB}_addr USING btree (least_hn(fromhn,tohn) );
    CREATE INDEX idx_tiger_data_${STABB}_addr_tlid_statefp ON tiger_data.${STABB}_addr USING btree (tlid, statefp);
    CREATE INDEX idx_tiger_data_${STABB}_addr_zip ON tiger_data.${STABB}_addr USING btree (zip);
    COMMIT;
    VACUUM ANALYZE tiger_data.${STABB}_addr;
EOF


## create zip for state
psql -e $_PGOPTS <<EOF
    BEGIN;
    CREATE TABLE tiger_data.${STABB}_zip_state(CONSTRAINT pk_${STABB}_zip_state PRIMARY KEY(zip,stusps)) INHERITS(zip_state);
    INSERT INTO tiger_data.${STABB}_zip_state(zip,stusps,statefp)
            SELECT DISTINCT zip, '${STABB}', '${STFIPS}' FROM tiger_data.${STABB}_addr WHERE zip is not null;
    ALTER TABLE tiger_data.${STABB}_zip_state ADD CONSTRAINT chk_statefp CHECK (statefp = '${STFIPS}');
    COMMIT;
    VACUUM ANALYZE tiger_data.${STABB}_zip_state;
EOF

#################################################################
##  Clean up and go home
#################################################################
if [[ $CLEANUP == TRUE ]]; then
	rm -f *.dbf *.sql *.xml *.err *.out *.prj *.shp *.xml *.shx
fi
popd

#################################################################
##  Experimental install script -- dont use yet
#################################################################

if [[ $TINSTALL == TRUE ]]; then
    echo "Installing geocoder in $PGDATABASE.  NOT LOADING DATA."
    psql $_PGOPTS -q -c "\i tiger_loader.sql"
    sh create_geocode.sh
    psql $_PGOPTS -q -c "alter database $PGDATABASE set search_path to \"$user\",public,tiger,postgig;"
    exit 0
fi
	declare -A FIPS
	declare -A STNAME
	FIPS[AK]=02; STNAME[AK]=Alaska
	FIPS[AL]=01; STNAME[AL]=Alabama
	FIPS[AR]=05; STNAME[AR]=Arkansas
	FIPS[AS]=60; STNAME[AS]=American Samoa
	FIPS[AZ]=04; STNAME[AZ]=Arizona
	FIPS[CA]=06; STNAME[CA]=California
	FIPS[CO]=08; STNAME[CO]=Colorado
	FIPS[CT]=09; STNAME[CT]=Connecticut
	FIPS[DC]=11; STNAME[DC]=District_of_Columbia
	FIPS[DE]=10; STNAME[DE]=Delaware
	FIPS[FL]=12; STNAME[FL]=Florida
	FIPS[GA]=13; STNAME[GA]=Georgia
	FIPS[GU]=66; STNAME[GU]=Guam
	FIPS[HI]=15; STNAME[HI]=Hawaii
	FIPS[IA]=19; STNAME[IA]=Iowa
	FIPS[ID]=16; STNAME[ID]=Idaho
	FIPS[IL]=17; STNAME[IL]=Illinois
	FIPS[IN]=18; STNAME[IN]=Indiana
	FIPS[KS]=20; STNAME[KS]=Kansas
	FIPS[KY]=21; STNAME[KY]=Kentucky
	FIPS[LA]=22; STNAME[LA]=Louisiana
	FIPS[MA]=25; STNAME[MA]=Massachusetts
	FIPS[MD]=24; STNAME[MD]=Maryland
	FIPS[ME]=23; STNAME[ME]=Maine
	FIPS[MI]=26; STNAME[MI]=Michigan
	FIPS[MN]=27; STNAME[MN]=Minnesota
	FIPS[MO]=29; STNAME[MO]=Missouri
	FIPS[MS]=28; STNAME[MS]=Mississippi
	FIPS[MT]=30; STNAME[MT]=Montana
	FIPS[NC]=37; STNAME[NC]=North_Carolina
	FIPS[ND]=38; STNAME[ND]=North_Dakota
	FIPS[NE]=31; STNAME[NE]=Nebraska
	FIPS[NH]=33; STNAME[NH]=New_Hampshire
	FIPS[NJ]=34; STNAME[NJ]=New_Jersey
	FIPS[NM]=35; STNAME[NM]=New_Mexico
	FIPS[NV]=32; STNAME[NV]=Nevada
	FIPS[NY]=36; STNAME[NY]=New_York
	FIPS[OH]=39; STNAME[OH]=Ohio
	FIPS[OK]=40; STNAME[OK]=Oklahoma
	FIPS[OR]=41; STNAME[OR]=Oregon
	FIPS[PA]=42; STNAME[PA]=Pennsylvania
	FIPS[PR]=72; STNAME[PR]=Puerto_Rico
	FIPS[RI]=44; STNAME[RI]=Rhode_Island
	FIPS[SC]=45; STNAME[SC]=South_Carolina
	FIPS[SD]=46; STNAME[SD]=South_Dakota
	FIPS[TN]=47; STNAME[TN]=Tennessee
	FIPS[TX]=48; STNAME[TX]=Texas
	FIPS[UT]=49; STNAME[UT]=Utah
	FIPS[VA]=51; STNAME[VA]=Virginia
	FIPS[VI]=78; STNAME[VI]=Virgin_Islands
	FIPS[VT]=50; STNAME[VT]=Vermont
	FIPS[WA]=53; STNAME[WA]=Washington
	FIPS[WI]=55; STNAME[WI]=Wisconsin
	FIPS[WV]=54; STNAME[WV]=West_Virginia
	FIPS[WY]=56; STNAME[WY]=Wyoming
	set -e
	set -u

	function getstinfo () {
	local ST=$1; shift

	local STCODES=(AK AL AR AS AZ CA CO CT DC DE FL GA GU HI IA ID IL IN KS KY
	LA MA MD ME MI MN MO MS MT NC ND NE NH NJ NM NV NY OH OK OR PA PR RI
	SC SD TN TX UT VA VI VT WA WI WV WY)

	local STNAMES=(Alaska Alabama Arkansas American_Samoa Arizona California
	Colorado Connecticut District_Of_Columbia Delaware Florida Georgia
	Guam Hawaii Iowa Idaho Illinois Indiana Kansas Kentucky Louisiana
	Massachusetts Maryland Maine Michigan Minnesota Missouri Mississippi
	Montana North_Carolina North_Dakota Nebraska New_Hampshire New_Jersey
	New_Mexico Nevada New_York Ohio Oklahoma Oregon Pennsylvania
	Puerto_Rico Rhode_Island South_Carolina South_Dakota Tennessee Texas
	Utah Virginia Virgin_Islands Vermont Washington Wisconsin
	West_Virginia Wyoming)

	local STFIPS=(02 01 05 60 04 06 08 09 11 10 12 13 66 15 19 16 17 18 20 21 22
	25 24 23 26 27 29 28 30 37 38 31 33 34 35 32 36 39 40 41 42 72 44 45
	46 47 48 49 51 78 50 53 55 54 56)

	for (( i=0; i<${#STCODES[*]}; i++ )) ; do
	if [[ ${STCODES[$i]} == $ST ]]; then
	echo ${STFIPS[$i]}_${STNAMES[i]} ${STNAMES[i]} ${STFIPS[$i]}
	return 0
	fi
	done

	echo "\"$ST\": code not found" 1>&2
	return 1
	}

	FOO=$(getstinfo $1)
	for x in $FOO; do
	echo $x
	done;

	# CSTNAME=${FIPS[$STABB]}_${STATEFULLNAME[$STABB]}
	# STNAME=${STATEFULLNAME[$STABB]}
	# STFIPS=${FIPS[$STABB]}
	exit 1
	## Bash options
	set -e
	set -u

	: <<TODO
	Command line switches: Option for clean, option for download, everything else really.
	Fix code to drop ALL tables with state prefix.
	Guard against using a cluttered /tmp/gisdata
	TODO


	####################################################################
	## COMMAND LINE OPTIONS -- not ready for prime time. Need defaults
	####################################################################
	# source fips.sh

	# STABB=EMPTY
	# DOWNLOAD=TRUE
	# DROPTABLES=TRUE
	# TINSTALL=FALSE
	# CLEANUP=TRUE
	# CENSUS_URL="www2.census.gov/geo/pvs/tiger2010st"

	# for x in "$@"; do
	# case ${x%=*} in
	# --state)
	# STABB=${x#*=} ;;
	# --download)
	# DOWNLOAD=${x#*=} ;;
	# --droptables)
	# DROPTABLES=${x#*=} ;;
	# --tinstall)
	# TINSTALL=${x#*=} ;;
	# --cleanup)
	# CLEANUP=${x#*=} ;;
	# --census-url)
	# CENSUS_URL=${x#*=} ;;
	# esac
	# done

	# CSTNAME=${FIPS[$STABB]}_${STATEFULLNAME[$STABB]}
	# STNAME=${STATEFULLNAME[$STABB]}
	# STFIPS=${FIPS[$STABB]}

	# if [[ $STABB == EMPTY ]]; then
	# echo "Must specify a state with --state=XX option"
	# exit 1
	# fi

	####################################################################
	## Per user and US state variables
	####################################################################

	# Choose your state
	STABB=OR
	CSTNAME=41_Oregon
	STNAME=Oregon
	STFIPS=41

	# STABB=WA
	# CSTNAME=53_Washington
	# STNAME=Washington
	# STFIPS=53

	DOWNLOAD=TRUE # download new files?
	DROPTABLES=TRUE # drop tables for this state
	TINSTALL=FALSE # install geocoder -- experimental!
	CLEANUP=TRUE

	## Choose your download source
	CENSUS_URL="www2.census.gov/geo/pvs/tiger2010st"

	## Choose your database
	export PGPORT=5432
	export PGHOST=localhost
	export PGDATABASE=geocoder
	## export PGUSER="" # probably best to set this in the shell beforehand
	## export PGPASSWORD="" # probably best to set this in the shell beforehand

	## test for a running PG, abort if not available

	if [[ ! $(psql --command="select 1") ]] ; then
	echo "POSTGRES NOT RUNNING. ABORTING." 1>&2
	exit 1
	else
	echo "POSTGRES RUNNING. PROCEEDING." 1>&2
	fi


	####################################################################
	## Infrastructure variables -- probably dont need to change
	####################################################################
	TMPDIR="/tmp/gisdata"
	UNZIPTOOL=unzip
	_PGOPTS=" -v ON_ERROR_STOP=1 -v AUTOCOMMIT=on"


	#################################################################
	### Functions
	#################################################################
	function getshp(){
	local STFIPS=$1; local STABB=$2; local TOPIC=$3;
	local F=${STABB}_${TOPIC}.sql
	shp2pgsql -c -s 4269 -g the_geom -W latin1 tl_2010_${STFIPS}_${TOPIC}.dbf "tiger_staging.${STABB}_${TOPIC}" \| iconv -f LATIN1 -t UTF-8 > $F
	psql -q $_PGOPTS -f $F
	}


	###################################################################
	## Begin script. If you need to change anything below, please file a
	## bug report
	###################################################################
	echo "START LOADING" 1>&2
	if [ ! -e $TMPDIR ]; then
	mkdir -p $TMPDIR
	fi
	pushd $TMPDIR
	rm -f "*.sql"


	## Download new data if appropriate -- snarf and unzip into current dir
	if [[ $DOWNLOAD == TRUE ]]; then
	rm -f .dbf .sql .xml .err .out .prj .shp .xml *.shx
	wget "http://${CENSUS_URL}/${CSTNAME}/" \
	--no-directories --no-parent --relative --recursive --level=2 --accept=zip,txt --mirror --timestamping --reject=html
	for z in *.zip; do
	unzip $z -d $TMPDIR
	done
	fi

	## Drop tables if appropriate
	set +e
	if [[ $DROPTABLES == TRUE ]]; then
	psql -e --no-psqlrc --variable=AUTOCOMMIT=on --variable=ON_ERROR_STOP=off <<EOF
	drop table tiger_data.${STABB}_addr cascade;
	drop table tiger_data.${STABB}_county cascade;
	drop table tiger_data.${STABB}_county_lookup cascade;
	drop table tiger_data.${STABB}_cousub cascade;
	drop table tiger_data.${STABB}_edges cascade;
	drop table tiger_data.${STABB}_faces cascade;
	drop table tiger_data.${STABB}_featnames cascade;
	drop table tiger_data.${STABB}_place cascade;
	drop table tiger_data.${STABB}_state cascade;
	drop table tiger_data.${STABB}_zip_lookup_base cascade;
	drop table tiger_data.${STABB}_zip_state_loc cascade;
	EOF
	fi
	set -e


	## Set up staging schemas
	if psql $_PGOPTS -c "DROP SCHEMA tiger_staging CASCADE;"; then
	echo "Dropping tiger_staging schema!" 1>&2
	else
	echo "No tiger_staging schema!" 1>&2
	fi
	psql $_PGOPTS -c "CREATE SCHEMA tiger_staging;"


	#################################################################
	## Begin loading code
	#################################################################

	## load state boundaries.
	getshp $STFIPS $STABB state10
	psql -q ${_PGOPTS} <<EOF
	BEGIN;
	CREATE TABLE tiger_data.${STABB}_state(CONSTRAINT pk_${STABB}_state PRIMARY KEY (gid) ) INHERITS(state);
	SELECT loader_load_staged_data(lower('${STABB}_state10'), lower('${STABB}_state'));
	ALTER TABLE tiger_data.${STABB}_state ADD CONSTRAINT uidx_${STABB}_state_stusps UNIQUE (stusps);
	CREATE INDEX tiger_data_${STABB}_state_the_geom_gist ON tiger_data.${STABB}_state USING gist(the_geom);
	COMMIT;
	VACUUM ANALYZE tiger_data.${STABB}_state;
	EOF


	## load county boundaries
	getshp $STFIPS $STABB county10
	psql -q $_PGOPTS <<EOF
	BEGIN;
	CREATE TABLE tiger_data.${STABB}_county(CONSTRAINT pk_${STABB}_county PRIMARY KEY (gid) ) INHERITS(county);
	ALTER TABLE tiger_staging.${STABB}_county10 RENAME geoid10 TO cntyidfp;
	SELECT loader_load_staged_data(lower('${STABB}_county10'), lower('${STABB}_county'));
	ALTER TABLE tiger_data.${STABB}_county ADD CONSTRAINT uidx_${STABB}_county_cntyidfp UNIQUE (cntyidfp);
	CREATE INDEX tiger_data_${STABB}_county_the_geom_gist ON tiger_data.${STABB}_county USING gist(the_geom);
	COMMIT;
	VACUUM ANALYZE tiger_data.${STABB}_county;
	EOF


	## create "county lookup"
	psql -q $_PGOPTS <<EOF
	BEGIN;
	CREATE TABLE tiger_data.${STABB}_county_lookup ( CONSTRAINT pk_${STABB}_county_lookup PRIMARY KEY (st_code, co_code))
	INHERITS (county_lookup);
	INSERT INTO tiger_data.${STABB}_county_lookup(st_code, state, co_code, name)
	SELECT CAST(statefp as integer), '${STABB}', CAST(countyfp as integer), name FROM tiger_data.${STABB}_county;
	COMMIT;
	VACUUM ANALYZE tiger_data.${STABB}_county_lookup;
	EOF


	### create place point
	getshp $STFIPS $STABB place10
	psql $_PGOPTS <<EOF
	BEGIN;
	CREATE TABLE tiger_data.${STABB}_place(CONSTRAINT pk_${STABB}_place10 PRIMARY KEY (plcidfp) ) INHERITS(place);
	ALTER TABLE tiger_staging.${STABB}_place10 RENAME geoid10 TO plcidfp;
	SELECT loader_load_staged_data(lower('${STABB}_place10'), lower('${STABB}_place'));
	ALTER TABLE tiger_data.${STABB}_place ADD CONSTRAINT uidx_${STABB}_place_gid UNIQUE (gid);
	CREATE INDEX idx_${STABB}_place_soundex_name ON tiger_data.${STABB}_place USING btree (soundex(name));
	COMMIT;
	VACUUM ANALYZE tiger_data.${STABB}_place;
	EOF


	### create "cousub"
	getshp $STFIPS $STABB cousub10
	psql -e $_PGOPTS <<EOF
	BEGIN;
	CREATE TABLE tiger_data.${STABB}_cousub(CONSTRAINT pk_${STABB}_cousub PRIMARY KEY (cosbidfp),
	CONSTRAINT uidx_${STABB}_cousub_gid UNIQUE (gid)) INHERITS(cousub);
	ALTER TABLE tiger_staging.${STABB}_cousub10 RENAME geoid10 TO cosbidfp;
	SELECT loader_load_staged_data(lower('${STABB}_cousub10'), lower('${STABB}_cousub'));
	ALTER TABLE tiger_data.${STABB}_cousub ADD CONSTRAINT chk_statefp CHECK (statefp = '${STFIPS}');
	CREATE INDEX tiger_data_${STABB}_cousub_the_geom_gist ON tiger_data.${STABB}_cousub USING gist(the_geom);
	COMMIT;
	VACUUM ANALYZE tiger_data.${STABB}_cousub;
	EOF


	### create faces -- one per county
	psql -q $_PGOPTS -c "CREATE TABLE tiger_data.${STABB}_faces(CONSTRAINT pk_${STABB}_faces PRIMARY KEY (gid)) INHERITS(faces);"
	for Z in *faces.dbf; do
	shp2pgsql -s 4269 -g the_geom -W latin1 $Z "tiger_staging.${STABB}_faces" \| iconv -f LATIN1 -t UTF-8 > $Z.sql
	psql -q $_PGOPTS -f $Z.sql
	psql -q $_PGOPTS -c "SELECT loader_load_staged_data(lower('${STABB}_faces'), lower('${STABB}_faces'));"
	done
	psql -e $_PGOPTS <<EOF
	BEGIN;
	CREATE INDEX tiger_data_${STABB}_faces_the_geom_gist ON tiger_data.${STABB}_faces USING gist(the_geom);
	CREATE INDEX idx_tiger_data_${STABB}_faces_tfid ON tiger_data.${STABB}_faces USING btree (tfid);
	ALTER TABLE tiger_data.${STABB}_faces ADD CONSTRAINT chk_statefp CHECK (statefp = '${STFIPS}');
	COMMIT;
	VACUUM ANALYZE tiger_data.${STABB}_faces;
	EOF


	## create feature names, one per county. First time need iconv ... weird. ...
	psql -q $_PGOPTS -c "CREATE TABLE tiger_data.${STABB}_featnames(CONSTRAINT pk_${STABB}_featnames PRIMARY KEY (gid)) INHERITS(featnames);"
	for Z in *featnames.dbf ; do
	shp2pgsql -s 4269 -g the_geom -W latin1 $Z "tiger_staging.${STABB}_featnames" \| iconv -f LATIN1 -t UTF-8 > $Z.sql
	psql -q $_PGOPTS -f $Z.sql
	psql -q $_PGOPTS -c "SELECT loader_load_staged_data(lower('${STABB}_featnames'), lower('${STABB}_featnames'));"
	done
	psql -e $_PGOPTS <<EOF
	BEGIN;
	UPDATE tiger_data.${STABB}_featnames SET statefp = '${STFIPS}' WHERE statefp IS NULL;
	CREATE INDEX idx_tiger_data_${STABB}_featnames_snd_name ON tiger_data.${STABB}_featnames USING btree (soundex(name));
	CREATE INDEX idx_tiger_data_${STABB}_featnames_lname ON tiger_data.${STABB}_featnames USING btree (lower(name));
	CREATE INDEX idx_tiger_data_${STABB}_featnames_tlid_statefp ON tiger_data.${STABB}_featnames USING btree (tlid,statefp);
	ALTER TABLE tiger_data.${STABB}_featnames ADD CONSTRAINT chk_statefp CHECK (statefp = '${STFIPS}');
	COMMIT;
	VACUUM ANALYZE tiger_data.${STABB}_featnames;
	EOF


	## create edges, one per county # tl_2010_41071_edges.dbf *edges.dbf
	psql $_PGOPTS -c "CREATE TABLE tiger_data.${STABB}_edges(CONSTRAINT pk_${STABB}_edges PRIMARY KEY (gid)) INHERITS(edges);"
	for Z in *edges.dbf; do
	shp2pgsql -s 4269 -g the_geom -W latin1 $Z tiger_staging.${STABB}_edges \| iconv -f LATIN1 -t UTF-8 > $Z.sql
	psql -q $_PGOPTS -f $Z.sql
	psql -q -c "SELECT loader_load_staged_data(lower('${STABB}_edges'), lower('${STABB}_edges'));"
	done
	psql -e $_PGOPTS <<EOF
	BEGIN;
	ALTER TABLE tiger_data.${STABB}_edges ADD CONSTRAINT chk_statefp CHECK (statefp = '${STFIPS}');
	CREATE INDEX idx_tiger_data_${STABB}_edges_tlid ON tiger_data.${STABB}_edges USING btree (tlid);
	CREATE INDEX idx_tiger_data_${STABB}_edges_tfidr ON tiger_data.${STABB}_edges USING btree (tfidr);
	CREATE INDEX idx_tiger_data_${STABB}_edges_tfidl ON tiger_data.${STABB}_edges USING btree (tfidl);
	CREATE INDEX tiger_data_${STABB}_edges_the_geom_gist ON tiger_data.${STABB}_edges USING gist(the_geom);
	CREATE INDEX idx_tiger_data_${STABB}_edges_zipl ON tiger_data.${STABB}_edges USING btree (zipl);
	COMMIT;
	VACUUM ANALYZE tiger_data.${STABB}_edges;
	EOF


	## create zip_loc data for state
	psql -e $_PGOPTS <<EOF
	BEGIN;
	CREATE TABLE tiger_data.${STABB}_zip_state_loc(CONSTRAINT pk_${STABB}_zip_state_loc PRIMARY KEY(zip,stusps,place))
	INHERITS(zip_state_loc);
	INSERT INTO tiger_data.${STABB}_zip_state_loc(zip,stusps,statefp,place)
	SELECT DISTINCT e.zipl, '${STABB}', '${STFIPS}', p.name
	FROM tiger_data.${STABB}_edges AS e
	INNER JOIN tiger_data.${STABB}_faces AS f ON (e.tfidl = f.tfid AND e.tfidr = f.tfid)
	INNER JOIN tiger_data.${STABB}_place As p ON (f.statefp = p.statefp AND f.placefp = p.placefp )
	WHERE e.zipl IS NOT NULL;
	CREATE INDEX idx_tiger_data_${STABB}_zip_state_loc_place ON tiger_data.${STABB}_zip_state_loc USING btree(soundex(place));
	ALTER TABLE tiger_data.${STABB}_zip_state_loc ADD CONSTRAINT chk_statefp CHECK (statefp = '${STFIPS}');
	COMMIT;
	VACUUM ANALYZE tiger_data.${STABB}_edges;
	VACUUM ANALYZE tiger_data.${STABB}_zip_state_loc;
	EOF


	## create zip lookup base
	psql -e $_PGOPTS <<EOF
	BEGIN;
	CREATE TABLE tiger_data.${STABB}_zip_lookup_base(CONSTRAINT pk_${STABB}_zip_state_loc_city PRIMARY KEY(zip,state, county, city, statefp))
	INHERITS(zip_lookup_base);
	INSERT INTO tiger_data.${STABB}_zip_lookup_base(zip,state,county,city, statefp)
	SELECT DISTINCT e.zipl, '${STABB}', c.name,p.name,'${STFIPS}'
	FROM tiger_data.${STABB}_edges AS e
	INNER JOIN tiger_data.${STABB}_county As c ON (e.countyfp = c.countyfp AND e.statefp = c.statefp AND e.statefp = '${STFIPS}')
	INNER JOIN tiger_data.${STABB}_faces AS f ON (e.tfidl = f.tfid AND e.tfidr = f.tfid)
	INNER JOIN tiger_data.${STABB}_place As p ON(f.statefp = p.statefp AND f.placefp = p.placefp )
	WHERE e.zipl IS NOT NULL;
	ALTER TABLE tiger_data.${STABB}_zip_lookup_base ADD CONSTRAINT chk_statefp CHECK (statefp = '${STFIPS}');
	CREATE INDEX idx_tiger_data_${STABB}_zip_lookup_base_citysnd ON tiger_data.${STABB}_zip_lookup_base USING btree(soundex(city));
	COMMIT;
	VACUUM ANALYZE tiger_data.${STABB}_zip_lookup_base;
	EOF


	## create address data, one per county
	psql $_PGOPTS -c "CREATE TABLE tiger_data.${STABB}_addr(CONSTRAINT pk_${STABB}_addr PRIMARY KEY (gid)) INHERITS(addr);"
	for Z in addr.dbf; do # addr.dbf
	shp2pgsql -s 4269 -g the_geom -W latin1 $Z tiger_staging.${STABB}_addr \| iconv -f LATIN1 -t UTF-8 > $Z.sql
	psql -q $_PGOPTS -f $Z.sql
	psql -q -c "SELECT loader_load_staged_data(lower('${STABB}_addr'), lower('${STABB}_addr'));"
	done
	psql -e $_PGOPTS <<EOF
	BEGIN;
	UPDATE tiger_data.${STABB}_addr SET statefp = '${STFIPS}' WHERE statefp IS NULL;
	ALTER TABLE tiger_data.${STABB}_addr ADD CONSTRAINT chk_statefp CHECK (statefp = '${STFIPS}');
	CREATE INDEX idx_tiger_data_${STABB}_addr_least_address ON tiger_data.${STABB}_addr USING btree (least_hn(fromhn,tohn) );
	CREATE INDEX idx_tiger_data_${STABB}_addr_tlid_statefp ON tiger_data.${STABB}_addr USING btree (tlid, statefp);
	CREATE INDEX idx_tiger_data_${STABB}_addr_zip ON tiger_data.${STABB}_addr USING btree (zip);
	COMMIT;
	VACUUM ANALYZE tiger_data.${STABB}_addr;
	EOF


	## create zip for state
	psql -e $_PGOPTS <<EOF
	BEGIN;
	CREATE TABLE tiger_data.${STABB}_zip_state(CONSTRAINT pk_${STABB}_zip_state PRIMARY KEY(zip,stusps)) INHERITS(zip_state);
	INSERT INTO tiger_data.${STABB}_zip_state(zip,stusps,statefp)
	SELECT DISTINCT zip, '${STABB}', '${STFIPS}' FROM tiger_data.${STABB}_addr WHERE zip is not null;
	ALTER TABLE tiger_data.${STABB}_zip_state ADD CONSTRAINT chk_statefp CHECK (statefp = '${STFIPS}');
	COMMIT;
	VACUUM ANALYZE tiger_data.${STABB}_zip_state;
	EOF

	#################################################################
	## Clean up and go home
	#################################################################
	if [[ $CLEANUP == TRUE ]]; then
	rm -f .dbf .sql .xml .err .out .prj .shp .xml *.shx
	fi
	popd

	#################################################################
	## Experimental install script -- dont use yet
	#################################################################

	if [[ $TINSTALL == TRUE ]]; then
	echo "Installing geocoder in $PGDATABASE. NOT LOADING DATA."
	psql $_PGOPTS -q -c "\i tiger_loader.sql"
	sh create_geocode.sh
	psql $_PGOPTS -q -c "alter database $PGDATABASE set search_path to \"$user\",public,tiger,postgig;"
	exit 0
	fi