tiger geocoder 2011

Settings

1. Ubuntu 12.04
2. Postgresql 9.1
3. Postgis 2.0
4. Tiger 2011 Data

Installing Postgresql + postgis2.0

1. sudo apt-get install python-software-properties
2. sudo apt-add-repository ppa:sharpie/for-science
3. sudo apt-add-repository ppa:sharpie/postgis-nightly
4. sudo apt-get update
5. sudo apt-get install postgresql-9.1-postgis
6. sudo apt-get install build-essential postgresql-9.1 postgresql-server-dev-9.1 libxml2-dev proj libjson0-dev xsltproc docbook-xsl docbook-mathml gettext postgresql-contrib-9.1 pgadmin3

Updating gdal and geos libraries

1. sudo apt-add-repository ppa:olivier-berten/geo
2. sudo apt-get update
3. sudo apt-get install libgdal-dev
4. sudo apt-get install g++ ruby ruby1.8-dev swig swig2.0 unzip
5. wget http://download.osgeo.org/geos/geos-3.3.3.tar.bz2
6. tar xvfj geos-3.3.3.tar.bz2
7. cd geos-3.3.3
8. ./configure --enable-ruby --prefix=/usr
9. make
10. sudo make install

Building postgis2.0

1. wget http://postgis.refractions.net/download/postgis-2.0.0.tar.gz
2. tar zxvf postgis-2.0.0.tar.gz
3. cd postgis-2.0.0
4. ./configure --with-gui
5. make
6. sudo make install
7. sudo ldconfig
8. sudo make comments-install
9. sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/local/bin/shp2pgsql
10. sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/local/bin/pgsql2shp
11. sudo ln -sf /usr/share/postgresql-common/pg_wrapper /usr/local/bin/raster2pgsql

Building spatial db template

1. createdb postgis2
2. psql -d postgis2
3. psql>create extension postgis;
4. psql>create extension postgis_topology;

Building geocoder

1. createdb -T postgis2 geocoder
2. wget http://postgis.refractions.net/download/postgis-2.1.0SVN.tar.gz
3. tar zxvf postgis-2.1SVN.tar.gz
4. EDIT postgis-2.1SVN/extra/tiger-geocoder/tiger2011/tiger_loader.sql
   export PGBIN=/usr/bin
   export PGPORT=5432
   export PGHOST=localhost
   export PGUSER=postgres
   export PGPASSWORD=XXX
   export PGDATABASE=geocoder
   PSQL=${PGBIN}/psql
   SHP2PGSQL=${PGBIN}/shp2pgsql
5. EDIT postgis-2.1SVN/extra/create_geocode.sh
   PGPORT=5432
   PGHOST=localhost
   PGUSER=postgres
   PGPASSWORD=XXX
   THEDB=geocoder
   PGSQL_CMD=/usr/bin/psql
   PGCONTRIB=/usr/share/postgresql/9.1/contrib
   //unmark the following two lines
   ${PGSQL_CMD} -d "${THEDB}" -c "CREATE EXTENSION fuzzystrmatch.sql"
   ${PGSQL_CMD} -d "${THEDB}" -c "ALTER DATABASE ${THEDB} SET search_path=public,tiger;"
6. sh ./create_geocode.sh

Downloading tiger data

1. sudo mkdir -p /gisdata/temp
2. sudo chown -R postgres:postgres /gisdata
3. psql -d geocoder
4. psql>\a
5. psql>\t
6. psql>\o nation.sh
7. psql>select loader_generate_nation_script('sh');
8. psql>\q
9. sh ./nation.sh
10. psql -d geocoder
11. psql>\a
12. psql>\t
13. psql>\o all_states.sh
14. psql>select loader_generate_script(ARRAY['AK','AR','AS',...],'sh');
15. psql>\q
16. sh ./all_states.sh
17. psql -d geocoder
18. psql>select install_missing_indexes();

Testing geocoder

1. psql -d geocoder
2. get geocode of "27 Waterview Drive, Shelton, CT 06484)psql>select g.rating,ST_X(geomout) AS lon,ST_Y(geomout) AS lat, (addy).* from geocode('27 Waterview Drive, Shelton, CT 06484') AS g;  
3. get reverse-geocode of point(-73.0811694362766 41.2931353732512)psql>select pprint_addy(r.addy[1]) as st1, pprint_addy(r.addy[2]) as st2, pprint_addy(r.addy[3]) as st3, array_to_string(r.street,',') as cross_streets from reverse_geocode(st_geomfromtext('POINT(-73.0811694362766 41.2931353732512)',4269),true) as r;