PostGIS TIGER Geocoder
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
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
WHERE os = 'sh';
Edit this new record in loader_platform. Change declare_sect to:
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
Save the record.
You only need to do this once. The basic SQL to run is:
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.
Make the script executable:
chmod +x /Users/Shared/tiger/tiger_nation.sh
And run it:
State data is installed similar to the national data. The base SQL command is:
The array is a comma-separated list of state abbreviations.
\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: