#!/bin/sh

# This is the root directory of the TIGER data.
BASE="TIGER2007FE"

# Host to connect to
HOST="beren"

# Database to use
DB="gis"

# If we are processing a specific state
STATES=""

# If we are processing a specific county
COUNTIES=""

# If we are dropping things before loading them
DROP="false"

TEMP=`getopt -o s::c::d -n 'process_tiger.sh' -- "$@"`

if [ $? != 0 ] ; then echo "Terminating..." >&2 ; exit 1 ; fi

NATLAYERS="*"
STATES="[0-9][0-9]"
COUNTIES="[0-9][0-9][0-9]"

eval set -- "$TEMP"
while true; do
    case "$1" in
        -s) case "$2" in
                "") STATES="[0-9][0-9]"; shift 2 ;;
                *)  STATES="$2"; shift 2 ;;
            esac ;;
        -c) COUNTYLVL="true";
            case "$2" in
                "") COUNTIES="[0-9][0-9][0-9]"; shift 2 ;;
                *)  COUNTIES="$2"; shift 2 ;;
            esac ;;
        -d) DROP="true"; shift ;;
        --) shift ; break ;;
        *) echo "Internal error!" ; exit 1 ;;
    esac
done

# Handle case where we were given a 5-digit county
echo $COUNTIES | grep -qE '[0-9]{5}'
if [ $? -eq 0 ]; then
    STATES=`echo $COUNTIES | cut -c1,2`
    COUNTIES=`echo $COUNTIES | cut -c3,4,5`
fi

if [ -z "$STATES" -a ! -z "$COUNTIES" ]; then
    STATES="[0-9][0-9]"
fi

# Loop through the state directories
for statedir in $BASE/${STATES}_*; do
    if [ "$statedir" = "$BASE/${STATES}_*" ]; then
		echo "$BASE/${STATES}_* did not match anything!"
		continue
    fi
    STATE=`basename $statedir | cut -f1 -d_`
    SCHEMA="${PREFIX}_${STATE}"
    FILEBASE="${SETBASE}_${STATE}"
    echo "Processing state $STATE..." >&2
    echo "set search_path = tiger_${STATE}, public;"
    echo "Processing layer addr..." >&2

    echo "begin;"

    echo "create table addr ("
    echo "  tlid integer,"
    echo "  fromhn varchar(12),"
    echo "  tohn varchar(12),"
    echo "  side varchar(1),"
    echo "  zip varchar(5),"
    echo "  plus4 varchar(4),"
    echo "  fromtyp varchar(1),"
    echo "  totyp varchar(1),"
    echo "  arid varchar(22),"
    echo "  mtfcc varchar(5)"
    echo ");"

    for codir in $statedir/${STATE}${COUNTIES}_*; do
        COUNTY=`basename $codir | cut -c3- | cut -f1 -d_`
        echo "Processing county $COUNTY..." >&2
        echo "insert into addr select * from c${COUNTY}_addr;"
    done

    echo "create table addrfn ("
    echo "  arid varchar(22),"
    echo "  linearid varchar(22)"
    echo ");"

    for codir in $statedir/${STATE}${COUNTIES}_*; do
        COUNTY=`basename $codir | cut -c3- | cut -f1 -d_`
        echo "Processing county $COUNTY..." >&2
        echo "insert into addrfn select * from c${COUNTY}_addrfn;"
    done

    echo "create sequence arealm_gid_seq;"
    echo "create table arealm ("
    echo "  gid integer not null default nextval('arealm_gid_seq'),"
    echo "  statefp varchar(2),"
    echo "  countyfp varchar(3),"
    echo "  countyns varchar(8),"
    echo "  ansicode varchar(8),"
    echo "  areaid varchar(22),"
    echo "  fullname varchar(120),"
    echo "  mtfcc varchar(5),"
    echo "  the_geom geometry"
    echo ");"

    for codir in $statedir/${STATE}${COUNTIES}_*; do
        COUNTY=`basename $codir | cut -c3- | cut -f1 -d_`
        echo "Processing county $COUNTY..." >&2
        echo "insert into arealm (statefp, countyfp, countyns, ansicode, areaid, fullname, mtfcc, the_geom)"
        echo "  select statefp, countyfp, countyns, ansicode, areaid, fullname, mtfcc, the_geom from c${COUNTY}_arealm;"
    done

    echo "alter table arealm add primary key (gid);"
    echo "create index arealm_the_geom_gist on arealm using gist (the_geom gist_geometry_ops);"
    echo "alter table arealm add constraint enforce_dims_the_geom CHECK (ndims(the_geom) = 2);"
    echo "alter table arealm add constraint enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON' OR the_geom IS NULL);"
    echo "alter table arealm add constraint enforce_srid_the_geom CHECK (srid(the_geom) = 4269);"

    echo "insert into geometry_columns values ('','tiger_${STATE}','arealm','the_geom',2,4269,'MULTIPOLYGON');"

    echo "create sequence areawater_gid_seq;"
    echo "create table areawater ("
    echo "  gid integer not null default nextval('areawater_gid_seq'),"
    echo "  statefp varchar(2),"
    echo "  countyfp varchar(3),"
    echo "  countyns varchar(8),"
    echo "  ansicode varchar(8),"
    echo "  hydroid varchar(22),"
    echo "  fullname varchar(120),"
    echo "  mtfcc varchar(5),"
    echo "  the_geom geometry"
    echo ");"

    for codir in $statedir/${STATE}${COUNTIES}_*; do
        COUNTY=`basename $codir | cut -c3- | cut -f1 -d_`
        echo "Processing county $COUNTY..." >&2
        echo "insert into areawater (statefp, countyfp, countyns, ansicode, hydroid, fullname, mtfcc, the_geom)"
        echo "  select statefp, countyfp, countyns, ansicode, hydroid, fullname, mtfcc, the_geom from c${COUNTY}_areawater;"
    done

    echo "alter table areawater add primary key (gid);"
    echo "create index areawater_the_geom_gist on areawater using gist (the_geom gist_geometry_ops);"
    echo "alter table areawater add constraint enforce_dims_the_geom CHECK (ndims(the_geom) = 2);"
    echo "alter table areawater add constraint enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON' OR the_geom IS NULL);"
    echo "alter table areawater add constraint enforce_srid_the_geom CHECK (srid(the_geom) = 4269);"

    echo "insert into geometry_columns values ('','tiger_${STATE}','areawater','the_geom',2,4269,'MULTIPOLYGON');"

    echo "create sequence bg00_gid_seq;"
    echo "create table bg00 ("
    echo "  gid integer not null default nextval('bg00_gid_seq'),"
    echo "  statefp00 varchar(2),"
    echo "  countyfp00 varchar(3),"
    echo "  tractce00 varchar(6),"
    echo "  blkgrpce00 varchar(1),"
    echo "  bkgpidfp00 varchar(12),"
    echo "  namelsad00 varchar(100),"
    echo "  mtfcc00 varchar(5),"
    echo "  funcstat00 varchar(1),"
    echo "  the_geom geometry"
    echo ");"

    for codir in $statedir/${STATE}${COUNTIES}_*; do
        COUNTY=`basename $codir | cut -c3- | cut -f1 -d_`
        echo "Processing county $COUNTY..." >&2
        echo "insert into bg00 (statefp00, countyfp00, tractce00, blkgrpce00, bkgpidfp00, namelsad00, mtfcc00, funcstat00, the_geom)"
        echo "  select statefp00, countyfp00, tractce00, blkgrpce00, bkgpidfp00, namelsad00, mtfcc00, funcstat00, the_geom from c${COUNTY}_bg00;"
    done

    echo "alter table bg00 add primary key (gid);"
    echo "create index bg00_the_geom_gist on bg00 using gist (the_geom gist_geometry_ops);"
    echo "alter table bg00 add constraint enforce_dims_the_geom CHECK (ndims(the_geom) = 2);"
    echo "alter table bg00 add constraint enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON' OR the_geom IS NULL);"
    echo "alter table bg00 add constraint enforce_srid_the_geom CHECK (srid(the_geom) = 4269);"

    echo "insert into geometry_columns values ('','tiger_${STATE}','bg00','the_geom',2,4269,'MULTIPOLYGON');"

    echo "create sequence cousub_gid_seq;"
    echo "create table cousub ("
    echo "  gid integer not null default nextval('cousub_gid_seq'),"
    echo "  statefp varchar(2),"
    echo "  countyfp varchar(3),"
    echo "  cousubns varchar(8),"
    echo "  cosbidfp varchar(10),"
    echo "  name varchar(100),"
    echo "  namelsad varchar(100),"
    echo "  lsad varchar(2),"
    echo "  classfp varchar(2),"
    echo "  mtfcc varchar(5),"
    echo "  ur varchar(1),"
    echo "  funcstat varchar(1),"
    echo "  the_geom geometry"
    echo ");"

    for codir in $statedir/${STATE}${COUNTIES}_*; do
        COUNTY=`basename $codir | cut -c3- | cut -f1 -d_`
        echo "Processing county $COUNTY..." >&2
        echo "insert into cousub (statefp, countyfp, cousubns, cosbidfp, name, namelsad, lsad, classfp, mtfcc, ur, funcstat, the_geom)"
        echo "  select statefp, countyfp, cousubns, cosbidfp, name, namelsad, lsad, classfp, mtfcc, ur, funcstat, the_geom from c${COUNTY}_cousub;"
    done

    echo "alter table cousub add primary key (gid);"
    echo "create index cousub_the_geom_gist on cousub using gist (the_geom gist_geometry_ops);"
    echo "alter table cousub add constraint enforce_dims_the_geom CHECK (ndims(the_geom) = 2);"
    echo "alter table cousub add constraint enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON' OR the_geom IS NULL);"
    echo "alter table cousub add constraint enforce_srid_the_geom CHECK (srid(the_geom) = 4269);"

    echo "insert into geometry_columns values ('','tiger_${STATE}','cousub','the_geom',2,4269,'MULTIPOLYGON');"

    echo "create sequence edges_gid_seq;"
    echo "create table edges ("
    echo "  gid integer not null default nextval('edges_gid_seq'),"
    echo "  statefp varchar(2),"
    echo "  countyfp varchar(3),"
    echo "  countyns varchar(8),"
    echo "  tlid bigint,"
    echo "  tfidl bigint,"
    echo "  tfidr bigint,"
    echo "  mtfcc varchar(5),"
    echo "  fullname varchar(100),"
    echo "  smid varchar(22),"
    echo "  lfromadd varchar(12),"
    echo "  ltoadd varchar(12),"
    echo "  rfromadd varchar(12),"
    echo "  rtoadd varchar(12),"
    echo "  zipl varchar(5),"
    echo "  zipr varchar(5),"
    echo "  featcat varchar(1),"
    echo "  hydroflg varchar(1),"
    echo "  railflg varchar(1),"
    echo "  roadflg varchar(1),"
    echo "  olfflg varchar(1),"
    echo "  passflg varchar(1),"
    echo "  divroad varchar(1),"
    echo "  exttyp varchar(1),"
    echo "  deckedroad varchar(1),"
    echo "  artpath varchar(1),"
    echo "  the_geom geometry"
    echo ");"

    for codir in $statedir/${STATE}${COUNTIES}_*; do
        COUNTY=`basename $codir | cut -c3- | cut -f1 -d_`
        echo "Processing county $COUNTY..." >&2
        echo "insert into edges (statefp, countyfp, countyns, tlid, tfidl, tfidr, mtfcc, fullname, smid, lfromadd, ltoadd, rfromadd, rtoadd,"
        echo "  zipl, zipr, featcat, hydroflg, railflg, roadflg, olfflg, passflg, divroad, exttyp, deckedroad, artpath, the_geom)"
        echo "  select statefp, countyfp, countyns, tlid, tfidl, tfidr, mtfcc, fullname, smid, lfromadd, ltoadd, rfromadd, rtoadd,"
        echo "  zipl, zipr, featcat, hydroflg, railflg, roadflg, olfflg, passflg, divroad, exttyp, deckedroad, artpath, the_geom"
        echo "  from c${COUNTY}_edges;"
    done

    echo "alter table edges add primary key (gid);"
    echo "create index edges_the_geom_gist on edges using gist (the_geom gist_geometry_ops);"
    echo "alter table edges add constraint enforce_dims_the_geom CHECK (ndims(the_geom) = 2);"
    echo "alter table edges add constraint enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTILINESTRING' OR the_geom IS NULL);"
    echo "alter table edges add constraint enforce_srid_the_geom CHECK (srid(the_geom) = 4269);"

    echo "insert into geometry_columns values ('','tiger_${STATE}','edges','the_geom',2,4269,'MULTILINESTRING');"

    echo "create table faces ("
    echo "    Tlid      	   integer,"
    echo "    Statefp           varchar(     2),"
    echo "    Countyfp          varchar(     3),"
    echo "    Statefp00         varchar(     2),"
    echo "    Countyfp00        varchar(     3),"
    echo "    Tractce00         varchar(     6),"
    echo "    Blkgrpce00        varchar(     1),"
    echo "    Blockce00         varchar(     4),"
    echo "    Suffix1ce         varchar(     1),"
    echo "    Cousubfp          varchar(     5),"
    echo "    Submcdfp          varchar(     5),"
    echo "    Placefp           varchar(     5),"
    echo "    Conctyfp          varchar(     5),"
    echo "    Uace              varchar(     5),"
    echo "    Cd108fp           varchar(     2),"
    echo "    Cdfp              varchar(     2),"
    echo "    Cdsessn           varchar(     3),"
    echo "    Vtdst00           varchar(     6),"
    echo "    Sldust            varchar(     3),"
    echo "    Sldlst            varchar(     3),"
    echo "    Aiannhce          varchar(     4),"
    echo "    Comptyp           varchar(     1),"
    echo "    Anrcfp            varchar(     5),"
    echo "    Trsubce           varchar(     3),"
    echo "    Ttractce00        varchar(     6),"
    echo "    Csafp             varchar(     3),"
    echo "    Cbsafp            varchar(     5),"
    echo "    Metdivfp          varchar(     5),"
    echo "    Cnectafp          varchar(     3),"
    echo "    Nectafp           varchar(     5),"
    echo "    Nctadvfp          varchar(     5),"
    echo "    Elsdlea           varchar(     5),"
    echo "    Scsdlea           varchar(     5),"
    echo "    Unsdlea           varchar(     5),"
    echo "    Lwflag            varchar(     1)"
    echo ");"

    for codir in $statedir/${STATE}${COUNTIES}_*; do
        COUNTY=`basename $codir | cut -c3- | cut -f1 -d_`
        echo "Processing county $COUNTY..." >&2
        echo "insert into faces select * from c${COUNTY}_faces;"
    done

    echo "create table facesah ("
    echo "  tfid integer,"
    echo "  hydroid varchar(22)"
    echo ");"

    for codir in $statedir/${STATE}${COUNTIES}_*; do
        COUNTY=`basename $codir | cut -c3- | cut -f1 -d_`
        echo "Processing county $COUNTY..." >&2
        echo "insert into facesah select * from c${COUNTY}_facesah;"
    done

    echo "create table facesal ("
    echo "  tfid integer,"
    echo "  areaid varchar(22)"
    echo ");"

    for codir in $statedir/${STATE}${COUNTIES}_*; do
        COUNTY=`basename $codir | cut -c3- | cut -f1 -d_`
        echo "Processing county $COUNTY..." >&2
        echo "insert into facesal select * from c${COUNTY}_facesal;"
    done

    echo "create table featnames ("
    echo "    Tlid      	   integer,"
    echo "    Fullname          varchar(   100),"
    echo "    Name              varchar(   100),"
    echo "    Predirabrv        varchar(    15),"
    echo "    Pretypabrv        varchar(    50),"
    echo "    Prequalabr        varchar(    15),"
    echo "    Sufdirabrv        varchar(    15),"
    echo "    Suftypabrv        varchar(    50),"
    echo "    Sufqualabr        varchar(    15),"
    echo "    Predir            varchar(     2),"
    echo "    Pretyp            varchar(     3),"
    echo "    Prequal           varchar(     2),"
    echo "    Sufdir            varchar(     2),"
    echo "    Suftyp            varchar(     3),"
    echo "    Sufqual           varchar(     2),"
    echo "    Linearid          varchar(    22),"
    echo "    Mtfcc             varchar(     5),"
    echo "    Paflag            varchar(     1)"
    echo ");"

    for codir in $statedir/${STATE}${COUNTIES}_*; do
        COUNTY=`basename $codir | cut -c3- | cut -f1 -d_`
        echo "Processing county $COUNTY..." >&2
        echo "insert into featnames select * from c${COUNTY}_featnames;"
    done

    echo "create sequence pointlm_gid_seq;"
    echo "create table pointlm ("
    echo "  gid integer not null default nextval('pointlm_gid_seq'),"
    echo "  statefp varchar(2),"
    echo "  countyfp varchar(3),"
    echo "  countyns varchar(8),"
    echo "  pointid varchar(22),"
    echo "  fullname varchar(100),"
    echo "  mtfcc varchar(5),"
    echo "  the_geom geometry"
    echo ");"

    for codir in $statedir/${STATE}${COUNTIES}_*; do
        COUNTY=`basename $codir | cut -c3- | cut -f1 -d_`
        echo "Processing county $COUNTY..." >&2
        echo "insert into pointlm (statefp, countyfp, countyns, pointid, fullname, mtfcc, the_geom)"
        echo "  select statefp, countyfp, countyns, pointid, fullname, mtfcc, the_geom from c${COUNTY}_pointlm;"
    done

    echo "alter table pointlm add primary key (gid);"
    echo "create index pointlm_the_geom_gist on pointlm using gist (the_geom gist_geometry_ops);"
    echo "alter table pointlm add constraint enforce_dims_the_geom CHECK (ndims(the_geom) = 2);"
    echo "alter table pointlm add constraint enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT' OR the_geom IS NULL);"
    echo "alter table pointlm add constraint enforce_srid_the_geom CHECK (srid(the_geom) = 4269);"

    echo "insert into geometry_columns values ('','tiger_${STATE}','pointlm','the_geom',2,4269,'POINT');"

    echo "create sequence tabblock_gid_seq;"
    echo "create table tabblock ("
    echo "  gid integer not null default nextval('tabblock_gid_seq'),"
    echo "  statefp varchar(2),"
    echo "  countyfp varchar(3),"
    echo "  countyns varchar(8),"
    echo "  statefp00 varchar(2),"
    echo "  countyfp00 varchar(3),"
    echo "  tractce00 varchar(6),"
    echo "  blockce00 varchar(4),"
    echo "  suffix1ce varchar(1),"
    echo "  blkidfp varchar(17),"
    echo "  name varchar(11),"
    echo "  mtfcc varchar(5),"
    echo "  ur00 varchar(1),"
    echo "  uace00 varchar(5),"
    echo "  funcstat varchar(1),"
    echo "  the_geom geometry"
    echo ");"

    for codir in $statedir/${STATE}${COUNTIES}_*; do
        COUNTY=`basename $codir | cut -c3- | cut -f1 -d_`
        echo "Processing county $COUNTY..." >&2
        echo "insert into tabblock (statefp, countyfp, countyns, statefp00, countyfp00, tractce00, blockce00,"
        echo "  suffix1ce, blkidfp, name, mtfcc, ur00, uace00, funcstat, the_geom)"
        echo "  select statefp, countyfp, countyns, statefp00, countyfp00, tractce00, blockce00,"
        echo "  suffix1ce, blkidfp, name, mtfcc, ur00, uace00, funcstat, the_geom"
        echo "  from c${COUNTY}_tabblock;"
    done

    echo "alter table tabblock add primary key (gid);"
    echo "create index tabblock_the_geom_gist on tabblock using gist (the_geom gist_geometry_ops);"
    echo "alter table tabblock add constraint enforce_dims_the_geom CHECK (ndims(the_geom) = 2);"
    echo "alter table tabblock add constraint enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON' OR the_geom IS NULL);"
    echo "alter table tabblock add constraint enforce_srid_the_geom CHECK (srid(the_geom) = 4269);"

    echo "insert into geometry_columns values ('','tiger_${STATE}','tabblock','the_geom',2,4269,'MULTIPOLYGON');"

    echo "create sequence tract00_gid_seq;"
    echo "create table tract00 ("
    echo "  gid integer not null default nextval('tract00_gid_seq'),"
    echo "  statefp00 varchar(2),"
    echo "  countyfp00 varchar(3),"
    echo "  tractce00 varchar(6),"
    echo "  ctidfp00 varchar(11),"
    echo "  name00 varchar(100),"
    echo "  namelsad00 varchar(100),"
    echo "  mtfcc00 varchar(5),"
    echo "  funcstat00 varchar(1),"
    echo "  the_geom geometry"
    echo ");"

    for codir in $statedir/${STATE}${COUNTIES}_*; do
        COUNTY=`basename $codir | cut -c3- | cut -f1 -d_`
        echo "Processing county $COUNTY..." >&2
        echo "insert into tract00 (statefp00, countyfp00, tractce00, ctidfp00,"
        echo "  name00, namelsad00, mtfcc00, funcstat00, the_geom)"
        echo "  select statefp00, countyfp00, tractce00, ctidfp00,"
        echo "  name00, namelsad00, mtfcc00, funcstat00, the_geom"
        echo "  from c${COUNTY}_tract00;"
    done

    echo "alter table tract00 add primary key (gid);"
    echo "create index tract00_the_geom_gist on tract00 using gist (the_geom gist_geometry_ops);"
    echo "alter table tract00 add constraint enforce_dims_the_geom CHECK (ndims(the_geom) = 2);"
    echo "alter table tract00 add constraint enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON' OR the_geom IS NULL);"
    echo "alter table tract00 add constraint enforce_srid_the_geom CHECK (srid(the_geom) = 4269);"

    echo "insert into geometry_columns values ('','tiger_${STATE}','tract00','the_geom',2,4269,'MULTIPOLYGON');"

    echo "create sequence vtd00_gid_seq;"
    echo "create table vtd00 ("
    echo "  gid integer not null default nextval('vtd00_gid_seq'),"
    echo "  statefp00 varchar(2),"
    echo "  countyfp00 varchar(3),"
    echo "  vtdst00 varchar(6),"
    echo "  vtdidfp00 varchar(11),"
    echo "  vtdi00 varchar(1),"
    echo "  name00 varchar(100),"
    echo "  namelsad00 varchar(100),"
    echo "  lsad00 varchar(2),"
    echo "  mtfcc00 varchar(5),"
    echo "  funcstat00 varchar(1),"
    echo "  the_geom geometry"
    echo ");"

    for codir in $statedir/${STATE}${COUNTIES}_*; do
        COUNTY=`basename $codir | cut -c3- | cut -f1 -d_`
        echo "Processing county $COUNTY..." >&2
        echo "insert into vtd00 (statefp00, countyfp00, vtdst00, vtdidfp00, vtdi00,"
        echo "  name00, namelsad00, lsad00, mtfcc00, funcstat00, the_geom)"
        echo "  select statefp00, countyfp00, vtdst00, vtdidfp00, vtdi00,"
        echo "  name00, namelsad00, lsad00, mtfcc00, funcstat00, the_geom"
        echo "  from c${COUNTY}_vtd00;"
    done

    echo "alter table vtd00 add primary key (gid);"
    echo "create index vtd00_the_geom_gist on vtd00 using gist (the_geom gist_geometry_ops);"
    echo "alter table vtd00 add constraint enforce_dims_the_geom CHECK (ndims(the_geom) = 2);"
    echo "alter table vtd00 add constraint enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'MULTIPOLYGON' OR the_geom IS NULL);"
    echo "alter table vtd00 add constraint enforce_srid_the_geom CHECK (srid(the_geom) = 4269);"

    echo "insert into geometry_columns values ('','tiger_${STATE}','vtd00','the_geom',2,4269,'MULTIPOLYGON');"

    echo "commit;"

done