2

I don't know where's the mistake... I am using the process from the PostGIS documentation (http://postgis.net/docs/manual-2.1/postgis_installation.html#loading_extras_tiger_geocoder) . I created a database, installed the extensions, installed wget and 7zip to match the locations outlined in the doumentation, created profile in loader_platform (based on 'windows' profile, I am on windows 8), however when I try to run the Loader_Generate_Nation_Script generated by PGAdminIII I get lots of errors. The files are downloaded but I don't see any indication that any unzipping is done. If I ignore the errors and continue the process as explained in the documnetation, of course goeocoding doesn't work. I have the Loader_Generate_Nation_Script pasted here, and also the output from the Command Prompt Window below.

Can anybody help ?

LoaderGenerateNationScript:

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

cd \gisdata
%WGETTOOL% ftp://ftp2.census.gov/geo/tiger/TIGER2013/STATE/ --no-parent --relative --recursive --level=1 --accept=zip --mirror --reject=html 
cd \gisdata/ftp2.census.gov/geo/tiger/TIGER2013/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 information_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 KEY (statefp),CONSTRAINT uidx_state_all_stusps  UNIQUE (stusps), CONSTRAINT uidx_state_all_gid UNIQUE (gid) ) INHERITS(state); "
%SHP2PGSQL% -c -s 4269 -g the_geom   -W "latin1" tl_2013_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
%WGETTOOL% ftp://ftp2.census.gov/geo/tiger/TIGER2013/COUNTY/ --no-parent --relative --recursive --level=1 --accept=zip --mirror --reject=html 
cd \gisdata/ftp2.census.gov/geo/tiger/TIGER2013/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 information_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_all PRIMARY KEY (cntyidfp),CONSTRAINT uidx_tiger_data_county_all_gid UNIQUE (gid)  ) INHERITS(county); " 
%SHP2PGSQL% -c -s 4269 -g the_geom   -W "latin1" tl_2013_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 (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;"

Windows8 Command Prompt output:

Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.

C:\Users\user1>set TMPDIR=\gisdata\temp\

C:\Users\user1>set UNZIPTOOL="C:\Program Files\7-Zip\7z.exe"

C:\Users\user1>set WGETTOOL="C:\wget\wget.exe"

C:\Users\user1>set PGBIN=C:\Program Files\PostgreSQL\9.3\bin\

C:\Users\user1>set PGPORT=5432

C:\Users\user1>set PGHOST=localhost

C:\Users\user1>set PGUSER=postgres

C:\Users\user1>set PGPASSWORD=password1

C:\Users\user1>set PGDATABASE=my6thGIS

C:\Users\user1>set PSQL="%PGBIN%psql"

C:\Users\user1>set SHP2PGSQL="%PGBIN%shp2pgsql"

C:\Users\user1>cd \gisdata

C:\gisdata>
C:\gisdata>cd \gisdata

C:\gisdata>%WGETTOOL% ftp://ftp2.census.gov/geo/tiger/TIGER2013/STATE/ --no-pare
nt --relative --recursive --level=1 --accept=zip --mirror --reject=html
--2014-03-30 11:08:52--  ftp://ftp2.census.gov/geo/tiger/TIGER2013/STATE/
           => `ftp2.census.gov/geo/tiger/TIGER2013/STATE/.listing'
Resolving ftp2.census.gov... 148.129.75.35
Connecting to ftp2.census.gov|148.129.75.35|:21... connected.
Logging in as anonymous ... Logged in!
==> SYST ... done.    ==> PWD ... done.
==> TYPE I ... done.  ==> CWD /geo/tiger/TIGER2013/STATE ... done.
==> PASV ... done.    ==> LIST ... done.

    [                                    ] 197         --.-K/s   in 0s

2014-03-30 11:08:53 (4.83 MB/s) - `ftp2.census.gov/geo/tiger/TIGER2013/STATE/.li
sting' saved [197]

--2014-03-30 11:08:53--  ftp://ftp2.census.gov/geo/tiger/TIGER2013/STATE/tl_2013
_us_state.zip
           => `ftp2.census.gov/geo/tiger/TIGER2013/STATE/tl_2013_us_state.zip'
==> CWD not required.
==> PASV ... done.    ==> RETR tl_2013_us_state.zip ... done.
Length: 8599274 (8.2M)

100%[======================================>] 8,599,274    251K/s   in 24s

2014-03-30 11:09:17 (344 KB/s) - `ftp2.census.gov/geo/tiger/TIGER2013/STATE/tl_2
013_us_state.zip' saved [8599274]

FINISHED --2014-03-30 11:09:17--
Downloaded: 2 files, 8.2M in 24s (344 KB/s)

C:\gisdata>cd \gisdata/ftp2.census.gov/geo/tiger/TIGER2013/STATE

C:\gisdata\ftp2.census.gov\geo\tiger\TIGER2013\STATE>del %TMPDIR%\*.* /Q

C:\gisdata\ftp2.census.gov\geo\tiger\TIGER2013\STATE>%PSQL% -c "DROP SCHEMA IF E
XISTS tiger_staging CASCADE;"
NOTICE:  schema "tiger_staging" does not exist, skipping
DROP SCHEMA

C:\gisdata\ftp2.census.gov\geo\tiger\TIGER2013\STATE>%PSQL% -c "CREATE SCHEMA ti
ger_staging;"
CREATE SCHEMA

C:\gisdata\ftp2.census.gov\geo\tiger\TIGER2013\STATE>%PSQL% -c "DO language 'plp
gsql' $$ BEGIN IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE sc
hema_name = 'tiger_data' ) THEN CREATE SCHEMA tiger_data; END IF;  END $$"
DO

C:\gisdata\ftp2.census.gov\geo\tiger\TIGER2013\STATE>for /r %%z in (tl_*state.zi
p ) do %UNZIPTOOL% e %%z  -o%TMPDIR%
%%z was unexpected at this time.

C:\gisdata\ftp2.census.gov\geo\tiger\TIGER2013\STATE>cd %TMPDIR%

C:\gisdata\temp>%PSQL% -c "CREATE TABLE tiger_data.state_all(CONSTRAINT pk_state
_all PRIMARY KEY (statefp),CONSTRAINT uidx_state_all_stusps  UNIQUE (stusps), CO
NSTRAINT uidx_state_all_gid UNIQUE (gid) ) INHERITS(state); "
CREATE TABLE

C:\gisdata\temp>%SHP2PGSQL% -c -s 4269 -g the_geom   -W "latin1" tl_2013_us_stat
e.dbf tiger_staging.state | %PSQL%
Unable to open tl_2013_us_state.shp or tl_2013_us_state.SHP.
tl_2013_us_state.dbf: dbf file (.dbf) can not be opened.

C:\gisdata\temp>%PSQL% -c "SELECT loader_load_staged_data(lower('state'), lower(
'state_all')); "
NOTICE:  INSERT INTO tiger_data.state_all(region,division,statefp,statens,stusps
,name,lsad,mtfcc,funcstat,aland,awater,intptlat,intptlon,the_geom) SELECT  FROM
tiger_staging.state;
CONTEXT:  SQL function "loader_load_staged_data" statement 1
ERROR:  syntax error at or near "FROM"
LINE 1: ...,aland,awater,intptlat,intptlon,the_geom) SELECT  FROM tiger...
                                                             ^
QUERY:  INSERT INTO tiger_data.state_all(region,division,statefp,statens,stusps,
name,lsad,mtfcc,funcstat,aland,awater,intptlat,intptlon,the_geom) SELECT  FROM t
iger_staging.state;
CONTEXT:  PL/pgSQL function loader_load_staged_data(text,text,text[]) line 24 at
 EXECUTE statement
SQL function "loader_load_staged_data" statement 1

C:\gisdata\temp>%PSQL% -c "CREATE INDEX tiger_data_state_all_the_geom_gist ON ti
ger_data.state_all USING gist(the_geom);"
CREATE INDEX

C:\gisdata\temp>%PSQL% -c "VACUUM ANALYZE tiger_data.state_all"
VACUUM

C:\gisdata\temp>cd \gisdata

C:\gisdata>%WGETTOOL% ftp://ftp2.census.gov/geo/tiger/TIGER2013/COUNTY/ --no-par
ent --relative --recursive --level=1 --accept=zip --mirror --reject=html
--2014-03-30 11:09:18--  ftp://ftp2.census.gov/geo/tiger/TIGER2013/COUNTY/
           => `ftp2.census.gov/geo/tiger/TIGER2013/COUNTY/.listing'
Resolving ftp2.census.gov... 148.129.75.35
Connecting to ftp2.census.gov|148.129.75.35|:21... connected.
Logging in as anonymous ... Logged in!
==> SYST ... done.    ==> PWD ... done.
==> TYPE I ... done.  ==> CWD /geo/tiger/TIGER2013/COUNTY ... done.
==> PASV ... done.    ==> LIST ... done.

    [                                    ] 198         --.-K/s   in 0s

2014-03-30 11:09:19 (12.6 MB/s) - `ftp2.census.gov/geo/tiger/TIGER2013/COUNTY/.l
isting' saved [198]

--2014-03-30 11:09:19--  ftp://ftp2.census.gov/geo/tiger/TIGER2013/COUNTY/tl_201
3_us_county.zip
           => `ftp2.census.gov/geo/tiger/TIGER2013/COUNTY/tl_2013_us_county.zip'

==> CWD not required.
==> PASV ... done.    ==> RETR tl_2013_us_county.zip ... done.
Length: 73500446 (70M)

100%[======================================>] 73,500,446   186K/s   in 3m 59s

2014-03-30 11:13:18 (301 KB/s) - `ftp2.census.gov/geo/tiger/TIGER2013/COUNTY/tl_
2013_us_county.zip' saved [73500446]

FINISHED --2014-03-30 11:13:18--
Downloaded: 2 files, 70M in 3m 59s (301 KB/s)

C:\gisdata>cd \gisdata/ftp2.census.gov/geo/tiger/TIGER2013/COUNTY

C:\gisdata\ftp2.census.gov\geo\tiger\TIGER2013\COUNTY>del %TMPDIR%\*.* /Q

C:\gisdata\ftp2.census.gov\geo\tiger\TIGER2013\COUNTY>%PSQL% -c "DROP SCHEMA IF
EXISTS tiger_staging CASCADE;"
DROP SCHEMA

C:\gisdata\ftp2.census.gov\geo\tiger\TIGER2013\COUNTY>%PSQL% -c "CREATE SCHEMA t
iger_staging;"
CREATE SCHEMA

C:\gisdata\ftp2.census.gov\geo\tiger\TIGER2013\COUNTY>%PSQL% -c "DO language 'pl
pgsql' $$ BEGIN IF NOT EXISTS (SELECT * FROM information_schema.schemata WHERE s
chema_name = 'tiger_data' ) THEN CREATE SCHEMA tiger_data; END IF;  END $$"
DO

C:\gisdata\ftp2.census.gov\geo\tiger\TIGER2013\COUNTY>for /r %%z in (tl_*county.
zip ) do %UNZIPTOOL% e %%z  -o%TMPDIR%
%%z was unexpected at this time.

C:\gisdata\ftp2.census.gov\geo\tiger\TIGER2013\COUNTY>cd %TMPDIR%

C:\gisdata\temp>%PSQL% -c "CREATE TABLE tiger_data.county_all(CONSTRAINT pk_tige
r_data_county_all PRIMARY KEY (cntyidfp),CONSTRAINT uidx_tiger_data_county_all_g
id UNIQUE (gid)  ) INHERITS(county); "
CREATE TABLE

C:\gisdata\temp>%SHP2PGSQL% -c -s 4269 -g the_geom   -W "latin1" tl_2013_us_coun
ty.dbf tiger_staging.county | %PSQL%
Unable to open tl_2013_us_county.shp or tl_2013_us_county.SHP.
tl_2013_us_county.dbf: dbf file (.dbf) can not be opened.

C:\gisdata\temp>%PSQL% -c "ALTER TABLE tiger_staging.county RENAME geoid TO cnty
idfp;  SELECT loader_load_staged_data(lower('county'), lower('county_all'));"
ERROR:  relation "tiger_staging.county" does not exist

C:\gisdata\temp>%PSQL% -c "CREATE INDEX tiger_data_county_the_geom_gist ON tiger
_data.county_all USING gist(the_geom);"
CREATE INDEX

C:\gisdata\temp>%PSQL% -c "CREATE UNIQUE INDEX uidx_tiger_data_county_all_statef
p_countyfp ON tiger_data.county_all USING btree(statefp,countyfp);"
CREATE INDEX

C:\gisdata\temp>%PSQL% -c "CREATE TABLE tiger_data.county_all_lookup ( CONSTRAIN
T pk_county_all_lookup PRIMARY KEY (st_code, co_code)) INHERITS (county_lookup);
"
CREATE TABLE

C:\gisdata\temp>%PSQL% -c "VACUUM ANALYZE tiger_data.county_all;"
VACUUM

C:\gisdata\temp>%PSQL% -c "INSERT INTO tiger_data.county_all_lookup(st_code, sta
te, 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;"
INSERT 0 0

C:\gisdata\temp>%PSQL% -c "VACUUM ANALYZE tiger_data.county_all_lookup;"
VACUUM

C:\gisdata\temp>
Improve this question

2 Answers 2

Reset to default
1

In the end I got this to work. Out of frustration and not being sure what was happening I downloaded multiple versions of postgreSQL 64 bit and also OpenGeo 32 bit. I didn't realize the ports and the server names were not matching. Once I fixed that the geocoding works.

Now the dilemma is how to batch geocode a large number of addresses (like 1000), when geocoding 3 addresses takes 40 seconds.

Improve this answer
2
  • 5
    So, all this problem was because of typos, etc.. If this is the case, perhaps removing the question would be advised
    – Alexandros
    Commented Apr 5, 2014 at 9:31
  • You are probably either missing indexes, need to vacuum analyze or need to configure the server for performance. You also have to run install_missing_indexes after you load all your data. See my post about improving performance: gis.stackexchange.com/questions/192593/… .
    – kttii
    Commented Jun 28, 2016 at 18:15
0

Did actually find one problem:

%%z was unexpected at this time.

And with this the unzipping was not working.

I was copying and pasting from Wordpad into Command Prompt window (so that I see the error messages).The double percent sign "%%" is for commands in batch files. In Command Prompt window it should be "%" sign. Instead of copying and pasting the code from the batch file to Command Prompt window I am now pasting the complete path of the .bat file into the Command Prompt window. Now unzipping works. Getting another error: FATAL: database xxx does not exist

Improve this answer
2
  • What is the name of your database. Is it my6thGIS or my6thgis ? I generally avoid mixed case database names because you have to quote them everywhere. When I wrote this I never tested with mixed case database names, so its possible its not handling that.
    – Regina Obe
    Commented Apr 5, 2014 at 4:18
  • Thanks for your comment LR1234567. It was mixed case my6thGIS.
    – steelponey
    Commented Apr 5, 2014 at 6:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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