[postgis-users] Errors populating tiger data on windows

[Jonathan Haglund]

Real quick: I figured out a solution, though not the root cause.  
Because the error is a type mismatch I simply corrected the type in the 
skeleton.  For whatever reason (the part I can't debug), when the data 
population scripts create the addr, edges and featnames tables through 
inheritance they convert the tlid column from numeric(10,0) to bigint.  
The following statements fix everything:

ALTER TABLE tiger.addr DROP COLUMN tlid;
ALTER TABLE tiger.edges DROP COLUMN tlid;
ALTER TABLE tiger.featnames DROP COLUMN tlid;

ALTER TABLE tiger.addr ADD COLUMN tlid numeric(10,0);
ALTER TABLE tiger.edges ADD COLUMN tlid numeric(10,0);
ALTER TABLE tiger.featnames ADD COLUMN tlid numeric(10,0);

What doesn't jive with your explanation, though, is that I am loading 
the 2012 data.  Only the documentation in 2.0.3 refers to 2010; the 
actual scripts are 2011 and 2012.  Also, 2.0.4 isn't available for 
Windows through the regular channel: 
http://download.osgeo.org/postgis/windows/pg92/

All said and done, will there be an upgrade path for 2.0.3 + tiger 2012 
to 2.1.x and tiger 2013?

Jonathan Haglund
The Go Travel Sites
801-783-5071
jonathan.haglund at gotravelsites.com
www.gotravelsites.com



On Wednesday, September 11, 2013 9:41:51 PM, Paragon Corporation wrote:
> 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
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users