2

I have been attempting to install tiger - geocoder in a postgres database. After muddling through examples and resources online for days I have been able to conjure up this much of the workflow.

I understand I am now to find a nationscript.bat file in gisdata folder. It is not found.

Here is what I have tried so far...

create new db

CREATE DATABASE geocoder;

Install necessary extensions...

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

Grant permissions to tiger…

GRANT USAGE ON SCHEMA tiger TO PUBLIC; 
GRANT USAGE ON SCHEMA tiger_data TO PUBLIC;
GRANT SELECT, REFERENCES, TRIGGER 
ON ALL TABLES IN SCHEMA tiger TO PUBLIC;
GRANT SELECT, REFERENCES, TRIGGER 
ON ALL TABLES IN SCHEMA tiger_data TO PUBLIC;
GRANT EXECUTE 
ON ALL FUNCTIONS IN SCHEMA tiger TO PUBLIC;
ALTER DEFAULT PRIVILEGES IN SCHEMA tiger_data 
GRANT SELECT,  REFERENCES 
ON TABLES TO PUBLIC;

Edited staging_fold field in tiger.loader_variables

  1. Running sql cmd \!pwd to find home folder produces ‘c:\users\noah’

  2. create new folders ‘gisdata’ and gisdata/temp inside home folder.

  3. set staging_fold variable to ‘/gisdata’

I then edited declare_sect field in the tiger.loader_platform table such that...

SELECT declare_sect FROM tiger.loader_platform WHERE os='windows';
                  declare_sect
------------------------------------------------
 set TMPDIR=${staging_fold}\temp\
 set UNZIPTOOL="C:\Program Files\7-Zip\7z.exe"
 set WGETTOOL="C:\wget-1.11.4\wget.exe"
 set PGBIN=C:\Program Files\PostgreSQL\9.5\bin\
 set PGPORT=5432
 set PGHOST=localhost
 set PGUSER=postgres
 set PGPASSWORD=pass
 set PGDATABASE=tiger
 set PSQL="%PGBIN%psql"
 set SHP2PGSQL="%PGBIN%shp2pgsql"
 cd ${staging_fold}

Finally running these commands to Generate nationscript.bat

\t
\a
\o /gisdata/nationscript.bat
SELECT loader_generate_nation_script('windows');
\o

Which produces the following results...

geocoder=#   \o /gisdata/nationscript.bat
/gisdata/nationscript.bat: No such file or directory
geocoder=#     SELECT loader_generate_nation_script('windows');

                                    loader_generate_nation_script


--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
---------------------
 set TMPDIR=\gisdata\temp\
 set UNZIPTOOL="C:\Program Files\7-Zip\7z.exe"
 set WGETTOOL="C:\wget-1.11.4"
 set PGBIN=C:\Program Files\PostgreSQL\9.5\bin\
 set PGPORT=5432
 set PGHOST=localhost
 set PGUSER=postgres
 set PGPASSWORD=pass
 set PGDATABASE=geocoder
 set PSQL="%PGBIN%psql"
 set SHP2PGSQL="%PGBIN%shp2pgsql"
 cd \gisdata

 cd \gisdata
 %WGETTOOL% ftp://ftp2.census.gov/geo/tiger/TIGER2015/STATE/ --no-parent --relat
ive --recursive --level=1 --accept=zip --mirror --reject=html
 cd \gisdata/ftp2.census.gov/geo/tiger/TIGER2015/STATE
 del %TMPDIR%\*.* /Q
 %PSQL% -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
 %PSQL% -c "CREATE SCHEMA tiger_staging;"
 %PSQL% -c "DO language 'plpgsql' $$ BEGIN IF NOT EXISTS (SELECT * FROM informat
ion_schema.schemata WHERE schema_name = 'tiger_data' ) THEN CREATE SCHEMA tiger_
data; END IF;  END $$"
 for /r %%z in (tl_*state.zip ) do %UNZIPTOOL% e %%z  -o%TMPDIR%
 cd %TMPDIR%
 %PSQL% -c "CREATE TABLE tiger_data.state_all(CONSTRAINT pk_state_all PRIMARY KE
Y (statefp),CONSTRAINT uidx_state_all_stusps  UNIQUE (stusps), CONSTRAINT uidx_s
tate_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_dat
a.state_all USING gist(the_geom);"
         %PSQL% -c "VACUUM ANALYZE tiger_data.state_all"
 cd \gisdata
 %WGETTOOL% ftp://ftp2.census.gov/geo/tiger/TIGER2015/COUNTY/ --no-parent --rela
tive --recursive --level=1 --accept=zip --mirror --reject=html
 cd \gisdata/ftp2.census.gov/geo/tiger/TIGER2015/COUNTY
 del %TMPDIR%\*.* /Q
 %PSQL% -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
 %PSQL% -c "CREATE SCHEMA tiger_staging;"
 %PSQL% -c "DO language 'plpgsql' $$ BEGIN IF NOT EXISTS (SELECT * FROM informat
ion_schema.schemata WHERE schema_name = 'tiger_data' ) THEN CREATE SCHEMA tiger_
data; END IF;  END $$"
 for /r %%z in (tl_*county.zip ) do %UNZIPTOOL% e %%z  -o%TMPDIR%
 cd %TMPDIR%
 %PSQL% -c "CREATE TABLE tiger_data.county_all(CONSTRAINT pk_tiger_data_county_a
ll 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 l
oader_load_staged_data(lower('county'), lower('county_all'));"
         %PSQL% -c "CREATE INDEX tiger_data_county_the_geom_gist ON tiger_data.c
ounty_all USING gist(the_geom);"
         %PSQL% -c "CREATE UNIQUE INDEX uidx_tiger_data_county_all_statefp_count
yfp ON tiger_data.county_all USING btree(statefp,countyfp);"
         %PSQL% -c "CREATE TABLE tiger_data.county_all_lookup ( CONSTRAINT pk_co
unty_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 inte
ger), c.name FROM tiger_data.county_all As c INNER JOIN state_lookup As s ON s.s
tatefp = c.statefp;"
         %PSQL% -c "VACUUM ANALYZE tiger_data.county_all_lookup;"
(1 row)

But still nothing is produced in gisdata folder and none of the tables above are produced in the database?

1

Nevermind , I found the issue. Apparently the \o /gisdata/nationscript.bat argument needed a full path.

\o c:/users/noah/gisdata/nationscript.bat 

worked to generate the script.

Also learned that the root folder referred to by the geocoder was c:/. All of the tiger files were extracted to c:/gisdata Thus correcting the above to \o c:/gisdata/nationscript.bat

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.