Installing TIGER geocoder?

Question

I am trying to install the tiger geocoder for postGIS. I am using the 2.1 version of postGIS on Windows 7. I am using this set of instruction to do so.

I am confused by what they mean be edit the paths in the declare_sect column to those that fit your pg, unzip,shp2pgsql, psql, etc path locations.

Here's the code that I am using to do this.

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

@RyanDalton - I tried to do what you suggested. However, I got the following error.

ERROR: unrecognized configuration parameter "unziptool." I'm not sure what I am doing wrong.

Fixed problem by using:

UPDATE tiger.loader_platform SET unzip_command = 'C:\Program Files\7-Zip\7z.exe' WHERE os = 'ravim';

Answer 1

The "paths" they are referring to are the program executables (*.exe) for your system, as mentioned in step 4. For example, if you are using 7-zip for unzipping data, you would replace:

<unzip_command> with "C:\Program Files (x86)\7-Zip\7z.exe" in the SELECT portion of the SQL statement.

You could make this easier by creating SQL variables representing each item by using the SET command, similar to what is shown on page 2 of PostGIS 2.0 Tiger Geocoder Cheatsheet under the heading of "Loader_Generate_Script":

SET unzip_command="C:\Program Files\7-Zip\7z.exe"

Answer 2

I've been struggling with this too (almost mindlessly following the PostGIS 2.2.0 Manual) and finally realized that declare_sect was just a field in this table multi-line statement of command statements that gets pasted into your script. This is what I had to do to get my username/password accurately set as well as the postgresql version and paths:

update tiger.loader_platform set declare_sect=
'TMPDIR="/data/gisdata/temp/"    
UNZIPTOOL=unzip                   
WGETTOOL="/usr/bin/wget"          
export PGBIN=/usr/pgsql-9.2/bin   
export PGPORT=5438                
export PGHOST=localhost
export PGUSER=special_user            
export PGPASSWORD=special_password
export PGDATABASE=special_database
PSQL=${PGBIN}/psql                
SHP2PGSQL=${PGBIN}/shp2pgsql      
cd /data/gisdata    
' where os='sh';

Now chances are really good that I got here via the wrong path -- I was surprised that the line for export PGBIN was refering to a completely different version of PG -- 9.0. In any case, this is what worked for me.

Answer 3

I agree with you that the instructions in this step are unclear. My interpretation is that you run this command, then edit the value of the declare_sect column in the loader_platform table that's created by the command. The value of the declare_sect column rows should be the path to the os commands listed (e.g. pg, unzip, etc...).