I have a sneaking suspicion your fixed work not fo the reason you think 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
Numeric(10,0) and bigint as I recall are compatible (and wouldn't give you a varchar cast error anyway0. What did it is by dropping the tlid column you moved it to the end. The issue was paflag was going into tlid, but since you dropped, you got the columns in the right order. This probably makes it no longer compatible with the later versions of postgis tiger geocoder, though may it is. Not sure if I stil have them column order dependent. -----Original Message----- From: [email protected] [mailto:[email protected]] 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 [email protected] 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: [email protected] > [mailto:[email protected]] On Behalf Of Jonathan > Haglund > Sent: Tuesday, September 10, 2013 4:55 PM > To: [email protected] > 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 > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users > > > _______________________________________________ > postgis-users mailing list > [email protected] > http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users _______________________________________________ postgis-users mailing list [email protected] http://lists.osgeo.org/cgi-bin/mailman/listinfo/postgis-users
