can't geocode with PostGIS 2.1.1

Question

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>

score 1 · Answer 1 · answered Apr 05 '14 at 06:36

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.

answered Apr 05 '14 at 06:36

steelponey

55
4

5

So, all this problem was because of typos, etc.. If this is the case, perhaps removing the question would be advised – Alexandros Apr 05 '14 at 09: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: http://gis.stackexchange.com/questions/192593/postgis-how-can-i-improve-the-response-time-for-getting-the-routed-distance-bet . – kttii Jun 28 '16 at 18:15

steelponey · Answer 2 · 2014-04-01T06:21:52.370

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

edited Apr 01 '14 at 06:21

answered Apr 01 '14 at 05:59

steelponey

55
4

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 Apr 05 '14 at 04:18
Thanks for your comment LR1234567. It was mixed case my6thGIS. – steelponey Apr 05 '14 at 06:32

can't geocode with PostGIS 2.1.1

2 Answers2