PostGIS TIGER Geocoder

2013-8-21

How to get the TIGER Geocoder working on OS X.

The main problem here is wget. OS X does not include this, but does have curl, and I don’t want to package

TODO I need help figuring out the equivalent curl commands to do exactly what wget does.

Use whatever interface you like – psql in a Terminal, phpPgAdmin or pgAdmin3.

Installation & Configuration

install extensions to database:

CREATE EXTENSION postgis;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;

This will create a new schema named “tiger”. Make sure the search path includes “tiger” and public – if running in a Terminal:

SET search_path TO tiger,public;

This is an option when running SQL in phpPgAdmin. In pgAdmin3 it’s automatic.

All tables named now will be in the tiger schema.

Now edit the loader_variables table, edit the 2012 (only) record. Change staging_fold to a path where you have permissions to write, like /Users/Shared/tiger (and make sure it exists). The examples below assume /Users/Shared/tiger.

Run some SQL on the loader_platform table:

INSERT INTO tiger.loader_platform(os, declare_sect, pgbin, wget, unzip_command, psql, path_sep,
loader, environ_set_command, county_process_command)
SELECT 'macosx', declare_sect, pgbin, wget, unzip_command, psql, path_sep,
loader, environ_set_command, county_process_command
FROM tiger.loader_platform
WHERE os = 'sh';

Edit this new record in loader_platform. Change declare_sect to:

TMPDIR="${staging_fold}/temp/"
UNZIPTOOL=unzip
WGETTOOL="/usr/bin/curl"
export PGBIN=/usr/local/pgsql-9.2/bin
export PGPORT=5432
export PGHOST=localhost
export PGUSER=postgres
export PGPASSWORD=yourpasswordhere
export PGDATABASE=geocoder
PSQL=${PGBIN}/psql
SHP2PGSQL=${PGBIN}/shp2pgsql
cd ${staging_fold}

Change the PGUSER and PGPASSWORD lines to a PG role with write and create permissions on the database. You can remove these lines if your Mac login user is also defined as a PG role with the same login password and the necessary permissions.

Change the PGDATABASE line to the name of the database.

Change wget field to curl.

Save the record.

National Setup

You only need to do this once. The basic SQL to run is:

select loader_generate_nation_script('macosx')

But you need to save it to a file. pgAdmin has an option for this when you run SQL. The staging_fold used above is a good place, name the file tiger_nation.sh.

For phpPgAdmin, you must select the output, copy, and paste into a new text file (ie with TextWrangler) and save manually. Not so bad, since you will be editing the script soon.

For psql, run this instead (specify a path such as the staging_fold used above):

\copy (select loader_generate_nation_script('macosx')) to '/Users/Shared/tiger/tiger_nation.sh' with binary

Edit this text file. Select from the top to just before TMPDIR and delete. Select the last 2 chars at the end (ˇˇ) and replace with a RETURN.

Whichever method used to generate the script, now edit it.

TODO figure out equivalent curl options to download

For now, just add a # before the 2 curl lines. Copy the expected download paths (look at “cd” lines following curl lines) and create them (in a Terminal):

mkdir -p /Users/Shared/tiger/ftp2.census.gov/geo/tiger/TIGER2012/STATE
mkdir -p /Users/Shared/tiger/ftp2.census.gov/geo/tiger/TIGER2012/COUNTY

Manually download the state and county files (Cyberduck or other ftp app) into the folders created.

If running in Postgres 9.2, remove “IF NOT EXISTS” from all CREATE SCHEMA lines.

Save script.

Make the script executable:

chmod +x /Users/Shared/tiger/tiger_nation.sh

And run it:

/Users/Shared/tiger/tiger_nation.sh

State Data

State data is installed similar to the national data. The base SQL command is:

select loader_generate_script(array['HI'],'macosx');

The array is a comma-separated list of state abbreviations.

For psql:

\copy (select loader_generate_script(array['HI'],'macosx')) to '/Users/Shared/gis/tiger/tiger_state.sh' with binary

Make the same edits as for the national script.

The folders needed are:

mkdir -p /Users/Shared/tiger/ftp2.census.gov/geo/tiger/TIGER2012/PLACE
mkdir -p /Users/Shared/tiger/ftp2.census.gov/geo/tiger/TIGER2012/COUSUB
mkdir -p /Users/Shared/tiger/ftp2.census.gov/geo/tiger/TIGER2012/TRACT
mkdir -p /Users/Shared/tiger/ftp2.census.gov/geo/tiger/TIGER2012/TABBLOCK
mkdir -p /Users/Shared/tiger/ftp2.census.gov/geo/tiger/TIGER2012/BG
mkdir -p /Users/Shared/tiger/ftp2.census.gov/geo/tiger/TIGER2010/ZCTA5/2010
mkdir -p /Users/Shared/tiger/ftp2.census.gov/geo/tiger/TIGER2012/FACES
mkdir -p /Users/Shared/tiger/ftp2.census.gov/geo/tiger/TIGER2012/FEATNAMES
mkdir -p /Users/Shared/tiger/ftp2.census.gov/geo/tiger/TIGER2012/EDGES
mkdir -p /Users/Shared/tiger/ftp2.census.gov/geo/tiger/TIGER2012/ADDR

and download the zip files for these into each folder (some may have multiple files for a state).

Make the script executable:

chmod +x /Users/Shared/tiger/tiger_state.sh

And run it:

/Users/Shared/tiger/tiger_state.sh