1

I'm working on setting up the TIGER geocoder on a Linux machine. I have installed PostgreSQL and PostGIS, now I'm working on the extension, point 2.8.1 of this guide:

I'm having problems with the points 4 to 8, since after running:

psql -c "SELECT Loader_Generate_Nation_Script('debbie')" -d geocoder -tA > /gisdata/nation_script_load.sh

The output script nation_script_load.sh is not been created.

I'm not getting any error at his point, just that the output folder is empty.

I guess the error is at some point during the step 4, but I'm not able to find it. I edited the corresponding paths as mention in the above tutorial: And then edit the paths in the declare_sect column to those that fit Debbie's pg, unzip,shp2pgsql, psql, etc path locations

update tiger.loader_platform set declare_sect=
UNZIPTOOL=”/usr/bin/unzip”                   
WGETTOOL="/usr/bin/wget"          
export PGBIN=”/usr/lib/postgresql/9.3/bin”   
export PGPORT=5432                
export PGHOST=localhost
export PGUSER=postgres            
export PGPASSWORD= my_password
export PGDATABASE=test1
PSQL=${PGBIN}/psql                
SHP2PGSQL=${PGBIN}/shp2pgsql';

EDIT:

After trying using:

psql -d test1 -A -t   -c "SELECT Loader_Generate_Nation_Script('sh')"

I'm getting this output:

TMPDIR="/gisdata/temp/"
UNZIPTOOL=unzip
WGETTOOL="/usr/bin/wget"
export PGBIN=/usr/lib/postgresql/9.4/bin
export PGPORT=5432
export PGHOST=localhost
export PGUSER=postgres
export PGPASSWORD=yourpasswordhere
export PGDATABASE=geocoder
PSQL=${PGBIN}/psql
SHP2PGSQL=${PGBIN}/shp2pgsql
cd /gisdata

cd /gisdata
wget ftp://ftp2.census.gov/geo/tiger/TIGER2015/STATE/ --no-parent --relative --recursive --level=1 --accept=zip --mirror --reject=html 
cd /gisdata/ftp2.census.gov/geo/tiger/TIGER2015/STATE
rm -f ${TMPDIR}/*.*
${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
${PSQL} -c "CREATE SCHEMA tiger_staging;"
for z in tl_*state.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
for z in */tl_*state.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;

${PSQL} -c "CREATE TABLE tiger_data.state_all(CONSTRAINT pk_state_all PRIMARY KEY (statefp),CONSTRAINT uidx_state_all_stusps  UNIQUE (stusps), CONSTRAINT uidx_state_all_gid UNIQUE (gid) ) INHERITS(tiger.state); "
${SHP2PGSQL} -D -c -s 4269 -g the_geom   -W "latin1" tl_2015_us_state.dbf tiger_staging.state | ${PSQL}
${PSQL} -c "SELECT loader_load_staged_data(lower('state'), lower('state_all')); "
    ${PSQL} -c "CREATE INDEX tiger_data_state_all_the_geom_gist ON tiger_data.state_all USING gist(the_geom);"
    ${PSQL} -c "VACUUM ANALYZE tiger_data.state_all"
cd /gisdata
wget ftp://ftp2.census.gov/geo/tiger/TIGER2015/COUNTY/ --no-parent --relative --recursive --level=1 --accept=zip --mirror --reject=html 
cd /gisdata/ftp2.census.gov/geo/tiger/TIGER2015/COUNTY
rm -f ${TMPDIR}/*.*
${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
${PSQL} -c "CREATE SCHEMA tiger_staging;"
for z in tl_*county.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
for z in */tl_*county.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
cd $TMPDIR;

${PSQL} -c "CREATE TABLE tiger_data.county_all(CONSTRAINT pk_tiger_data_county_all PRIMARY KEY (cntyidfp),CONSTRAINT uidx_tiger_data_county_all_gid UNIQUE (gid)  ) INHERITS(tiger.county); " 
${SHP2PGSQL} -D -c -s 4269 -g the_geom   -W "latin1" tl_2015_us_county.dbf tiger_staging.county | ${PSQL}
${PSQL} -c "ALTER TABLE tiger_staging.county RENAME geoid TO cntyidfp;  SELECT loader_load_staged_data(lower('county'), lower('county_all'));"
    ${PSQL} -c "CREATE INDEX tiger_data_county_the_geom_gist ON tiger_data.county_all USING gist(the_geom);"
    ${PSQL} -c "CREATE UNIQUE INDEX uidx_tiger_data_county_all_statefp_countyfp ON tiger_data.county_all USING btree(statefp,countyfp);"
    ${PSQL} -c "CREATE TABLE tiger_data.county_all_lookup ( CONSTRAINT pk_county_all_lookup PRIMARY KEY (st_code, co_code)) INHERITS (tiger.county_lookup);"
    ${PSQL} -c "VACUUM ANALYZE tiger_data.county_all;"
    ${PSQL} -c "INSERT INTO tiger_data.county_all_lookup(st_code, state, co_code, name) SELECT CAST(s.statefp as integer), s.abbrev, CAST(c.countyfp as integer), c.name FROM tiger_data.county_all As c INNER JOIN state_lookup As s ON s.statefp = c.statefp;"
    ${PSQL} -c "VACUUM ANALYZE tiger_data.county_all_lookup;" 

Is that the spected result?

  • what happens if you connect to the database and run SELECT Loader_Generate_Nation_Script('debbie') – Evan Carroll May 12 '17 at 17:08
1

When you run

SELECT Loader_Generate_Nation_Script('debbie');

You'll see nothing is outputted. That's because Loader_Generate_Nation_Script has a signature of,

text loader_generate_nation_script(text os);

So it expects it's first argument to be the operating system, currently windows, or sh. Try

psql -d geocoder -A -t \
  -c "SELECT Loader_Generate_Nation_Script('sh')" > /gisdata/nation_script_load.sh

Instead. And, when in doubt try to follow the instructions it can be finicky.

  • Now I'm getting a permission error: "Permission denied" Even I change the destination folder – Luis Ramon Ramirez Rodriguez May 16 '17 at 3:34
  • 1
    Whatever output directory you're trying for, you do not have write permissions to it. Try running it without the redirection to a file and see if it works. – Evan Carroll May 16 '17 at 4:10
  • I tried with: "psql -d test1 -A -t -c "SELECT Loader_Generate_Nation_Script('sh')"" I update the question, not sure if I'm getting the correct output. – Luis Ramon Ramirez Rodriguez May 16 '17 at 12:29
  • 1
    @LuisRamonRamirezRodriguez you can either modify the windows or sh profiles, or create your own. In those docs, in 2.8.4, they add a new profile called debbie. I assume you skipped that step, or it should work? – Evan Carroll May 16 '17 at 15:42
  • 1
    @LuisRamonRamirezRodriguez you either need to edit the columns in the respective row in the table tiger.loader_platform: pgadmin will help in this. – Evan Carroll May 16 '17 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.