[postgis-users] Errors populating tiger data on windows

[Paragon Corporation]

Jonathan,

Sorry for your difficulties. I just realized the issue.  I think the windows
postgis 2.0.4 packaging we made was a faulty one in that it was exporting an
in the works  PostGIS 2.1 tiger geocoder that was probably buggy in the load
routine since we were in the middle of prepping it for 2011.  PostGIS 2.0
NEVER had tiger_2011.  It only had tiger_2010 scripts. 


If you are just starting out, I would suggest using the tiger extension
files packaged in this download which is updated for working with tiger_2013

http://winnie.postgis.net/download/windows/pg92/buildbot/postgis-pg92-binari
es-2.2.0devw64.zip

(just the ones in share/extension folder that are postgis_tiger_*** )


Then follow instructions here:

http://postgis.net/docs/manual-dev/postgis_installation.html#install_tiger_g
eocoder_extension


I did try loading up Hawaii with the 2.2 and didn't run into any issues.
We'll also be releasing PostGIS 2.1.0 soon, but that one is only set to go
to tiger_2012 and there is a bug in that one already noted that (that is
only works for PostgreSQL 9.3), but we have fixed for 2.1.1 to be backwards
compatible with 9.1 and 9.2.




-----Original Message-----
From: postgis-users-bounces at lists.osgeo.org
[mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Jonathan Haglund
Sent: Tuesday, September 10, 2013 4:55 PM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] Errors populating tiger data on windows

Hello all,

I am using the stock tiger scripts from PostgreSQL 9.2/PostGIS 2.0.3 64 bit
on Windows Server 2008 R2.  I edited them to use my password and file paths.
Running the "create_geocode.bat" script creates the tables fine, though it
throws errors saying some things already exist.  I then ran "SELECT
loader_generate_script(ARRAY['HI'], 'windows');" and executed that.  Its
fine for awhile, but eventually I get the following:

ERROR:  current transaction is aborted, commands ignored until end of
transaction block

If I break execution I get more info (line breaks edited after pasting from
the cmd prompt):

NOTICE:  INSERT INTO
tiger_data.hi_featnames(tlid,paflag,mtfcc,linearid,sufqual,suftyp,sufdir,pre
qual,pretyp,predir,sufqualabr,suftypabrv,sufdirabrv,prequalabr,
pretypabrv,predirabrv,name,fullname)
SELECT
paflag,mtfcc,linearid,sufqual,suftyp,sufdir,prequal,pretyp,predir,sufqualabr
,suftypabrv,sufdirabrv,prequalabr,pretypabrv,predirabrv,name,fullname,tlid
FROM tiger_staging.hi_featnames;
CONTEXT:  SQL function "loader_load_staged_data" statement 1
ERROR:  column "tlid" is of type bigint but expression is of type character
varying LINE 1: ...alabr,pretypabrv,predirabrv,name,fullname) SELECT
paflag,mtf...
                                                              ^
HINT:  You will need to rewrite or cast the expression.
QUERY:  INSERT INTO
tiger_data.hi_featnames(tlid,paflag,mtfcc,linearid,sufqual,suftyp,sufdir,pre
qual,pretyp,predir,sufqualabr,suftypabrv,sufdirabrv,prequalabr,pretypabrv,pr
edirabrv,name,fullname) 

SELECT
paflag,mtfcc,linearid,sufqual,suftyp,sufdir,prequal,pretyp,predir,sufqualabr
,suftypabrv,sufdirabrv,prequalabr,pretypabrv,predirabrv,name,fullname,tlid
FROM tiger_staging.hi_featnames;
CONTEXT:  PL/pgSQL function loader_load_staged_data(text,text,text[])
line 24 at
  EXECUTE statement
SQL function "loader_load_staged_data" statement 1

I have followed the instructions, started over many times in case I messed
up somewhere, but some things I noticed: The docs refer to tiger2010, but
only 2011 and 2012 scripts are present, within the 2011 folder.  The
"create_geocode.bat" script by default refers to "tiger_loader_2012.sql"
which is what I left it as.  Not every state throws this error, but most of
them do.  If I ignore it and let it finish geocoding technically seems to
work, but I have no way of knowing if my data is incomplete.

I can find no other reference to this issue and since I am using the scripts
as they came to me through the application stack builder I have no idea what
to do.  Please let me know if you need more information.

-Jonathan
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users