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>
Best Answer
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.