[postgis-users] Errors populating tiger data on windows

Thu Sep 12 21:04:02 PDT 2013

Typo. I meant 2.0.3. I had 2.0.4 in my head since that's what we are
currently working on packaging (as well as 2.1).
 2.0.4 we'll have in a week or 2, but it will just have tiger 2010 pretty
uninteresting.

The 2.0 branch doesn't have tiger 2011/2012.  That feature was introduced in
2.1.
The reason it accidentally got included in the 2.0 windows is that part of
the packaging is to do an svn export of the tiger folder
And that svn export part was mistakenly hardcoded to go to the 2.1 branch.

No plans to push 2011/2012 to 2.0.  The code base has changed so much
between the 2 that I wouldn't want to mess with it at this point.

There is a ticket to include tiger 2013 in 2.1.1.
http://trac.osgeo.org/postgis/ticket/2478

That I'm still debating.  I probably will just because its such a minor
change pretty much flipping a value in a lookup table.

Thanks,
Regina
http://www.postgis.us
http://postgis.net

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

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_t
> iger_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,sufd
> ir,pre qual,pretyp,predir,sufqualabr,suftypabrv,sufdirabrv,prequalabr,
> pretypabrv,predirabrv,name,fullname)
> SELECT
> paflag,mtfcc,linearid,sufqual,suftyp,sufdir,prequal,pretyp,predir,sufq
> ualabr 
> ,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,sufd
> ir,pre 
> qual,pretyp,predir,sufqualabr,suftypabrv,sufdirabrv,prequalabr,pretypa
> brv,pr
> edirabrv,name,fullname)
>
> SELECT
> paflag,mtfcc,linearid,sufqual,suftyp,sufdir,prequal,pretyp,predir,sufq
> ualabr 
> ,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
_______________________________________________
postgis-users mailing list
postgis-users at lists.osgeo.org
http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users