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