#!/bin/bash
#
# tigerimport.sh - (c) 2005 Schuyler Erle <schuyler@geocoder.us>
#   Last updated Fri Mar  4 08:13:28 PST 2005
#
# tigerimport.sh imports the CompleteChain, Landmarks, AreaLandMarks, and
# Polygon layers from a TIGER/Line .ZIP file into a PostGIS database. You
# must have ogr2ogr from GDAL (http://gdal.org), as well as the GDAL
# Python extensions.
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
# 
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You can download a copy of the GPL from http://www.gnu.org/copyleft/gpl.html,
# or write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330,
# Boston, MA  02111-1307, USA.

MODE=$1
DATABASE=$2
ZIP=$3

TIGERPOLY=tigerpoly.py

case $MODE in
    append) PRE="-append"; POST="" ;;
    update) PRE="-update"; POST="" ;;
    create) PRE=""; POST="-lco OVERWRITE=YES" ;;
    *) echo "Usage: $0 [append|update|create] <TIGER/Line .zip> <database>";
       exit -1;;
esac

FILE=$(basename $ZIP)
FILE=${FILE#tgr}
FILE=${FILE%.zip}
TIGER=TGR$FILE.RT1

SRID=4269
cat > nad83_srs.txt <<End
GEOGCS["GCS_North_American_1983",DATUM["D_North_American_1983",SPHEROID["GRS_1980",6378137,298.257222101]],PRIMEM["Greenwich",0],UNIT["Degree",0.017453292519943295]]
End

OGROPTS="-f PostgreSQL"
SRS="-a_srs nad83_srs.txt"
POST="$POST -lco DIM=2"

unzip $ZIP 

echo Importing CompleteChain into database $DATABASE...
ogr2ogr $PRE $OGROPTS $SRS PG:dbname=$DATABASE $TIGER CompleteChain $POST

echo Importing PolyChainLink into database $DATABASE...
ogr2ogr $OGROPTS $SRS PG:dbname=$DATABASE $TIGER PolyChainLink \
    -lco OVERWRITE=YES

echo Importing Landmarks into database $DATABASE...
ogr2ogr $PRE $OGROPTS $SRS PG:dbname=$DATABASE $TIGER Landmarks $POST \
    -nlt GEOMETRY

echo Importing AreaLandmarks into database $DATABASE...
ogr2ogr $OGROPTS $SRS PG:dbname=$DATABASE $TIGER AreaLandmarks \
    -lco OVERWRITE=YES

echo Extracting polygons from $TIGER...
python $TIGERPOLY $TIGER tmp_poly_$$.shp

echo Importing polygons into database $DATABASE...
ogr2ogr $PRE $OGROPTS $SRS PG:dbname=$DATABASE tmp_poly_$$.shp $POST \
    -nlt GEOMETRY -nln polygon

echo Updating area landmarks in database $DATABASE...
psql $DATABASE <<End
    update landmarks set wkb_geometry = 
	(select polygonize(c.wkb_geometry)
	    from arealandmarks a, polychainlink p, completechain c
	    where a.land = landmarks.land
	    and a.polyid = polyidr
	    and (polyidl) not in (
		select a2.polyid from arealandmarks a2 where a.land = a2.land)
	    and c.tlid = p.tlid
	    group by a.land)
	where file = $FILE;
End

echo Updating point landmarks in database $DATABASE...
psql $DATABASE <<End
    update landmarks set wkb_geometry = 
	GeomFromEWKT( 'SRID=$SRID;POINT(' || (lalong / 1000000) || ' ' 
					   || (lalat / 1000000) || ')' ) 
	where wkb_geometry is null 
	and lalat is not null and lalong is not null;
End

echo Fixing broken landmark polygons in database $DATABASE...
psql $DATABASE <<End
    update landmarks
	set wkb_geometry = Buffer(wkb_geometry, 0)
	where file = $FILE and not isvalid(wkb_geometry);
End

echo Cleaning up database $DATABASE...
psql $DATABASE <<End
    select DropGeometryColumn( 'public', 'arealandmarks', 'wkb_geometry' );
    select DropGeometryColumn( 'public', 'polychainlink', 'wkb_geometry' );

    drop table arealandmarks;
    drop table polychainlink;
End

echo Cleaning up file system...
rm -f tmp_poly_$$.{shp,shx,dbf,prj} TGR$FILE.{RT*,MET}