<html>
<head>
<style><!--
.hmmessage P
{
margin:0px;
padding:0px
}
body.hmmessage
{
font-size: 10pt;
font-family:メイリオ
}
--></style>
</head>
<body class='hmmessage'>
Hi I am using Fedora 14 and Fedora 15.<br>I set Tiger Geocoder for Tiger 2009 using PostGIS1.5 <br>(All installed by yum Fedora binary package).<br>It works fine.<br><br>But I found that there is some missing data so I would like to use Tiger 2010 <br>(I assume that data is more recent one)<br>As far as I understand, Tiger 2010 data has different structure/schema from Tiger 2009<br>but I can upgrade using upgrade script.<br><br>I download geos 3.3 (since Fedora repository only have geos 3.2.1), gdal,<br>and download PostGIS2.0 and compile.<br><br>Then run<br>tiger_loader.sql<br>upgrade_geocode.sql from Tiger 2010 (created by PostGIS 2.0 installation)<br>But the resulting scripts does not work well.<br><br>I create the scripts using<br>SELECT loader_generate_script(ARRAY['DC'], 'sh'); <br>(The only option is 'windows' and 'sh', no 'linux' option unlike Tiger 2009)<br>But this newly generated scripts does not work.ls<br><br>As described in ReadME, execute the following (create_geocode.sh in Census 2009)<br><br>"%PGBIN%\psql" -d "%THEDB%" -f "%PGCONTRIB%\fuzzystrmatch.sql"<br>"%PGBIN%\psql" -d "%THEDB%" -c "CREATE SCHEMA tiger"<br>"%PGBIN%\psql" -d "%THEDB%" -f "tables\lookup_tables_revised.sql"<br>"%PGBIN%\psql" -d "%THEDB%" -c "CREATE SCHEMA tiger_data"<br>"%PGBIN%\psql" -d "%THEDB%" -f "tiger_loader.sql"<br>"%PGBIN%\psql" -d "%THEDB%" -f "create_geocode.sql"<br><br>As described in ReadME, execute the following (upgrade_geocode.sh in Census 2010)<br>"%PGBIN%\psql" -d "%THEDB%" -f "tiger_loader.sql"<br>"%PGBIN%\psql" -d "%THEDB%" -f "upgrade_geocode.sql"<br><br>So I run the SQL in the<br>SELECT loader_generate_script(ARRAY['DC'], 'sh');<br><br>There are a lot of syntax error so I execute one by one by checking the sentence.<br>But the following SQL has the issue.<br>I am not sure where I have to look to correct the issue for the SQL syntax error (missing table).<br>The whole generated script is attached.<br><br>[root@gistmp temp]# psql -U postgres test -c "SELECT loader_load_staged_data(lower('DC_state10'), lower('DC_state'));"<br>NOTICE: INSERT INTO tiger_data.dc_state(region,division,statefp,statens,stusps,name,lsad,mtfcc,funcstat,aland,awater,intptlat,intptlon,the_geom)<br>SELECT FROM tiger_staging.dc_state10;<br>CONTEXT: SQL function "loader_load_staged_data" statement 1<br>ERROR: syntax error at or near "FROM"<br>LINE 1: ...,aland,awater,intptlat,intptlon,the_geom) SELECT FROM tiger...<br> ^<br>QUERY: INSERT INTO tiger_data.dc_state(region,division,statefp,statens,stusps,name,lsad,mtfcc,funcstat,aland,awater,intptlat,intptlon,the_geom) SELECT FROM tiger_staging.dc_state10;<br>CONTEXT: PL/pgSQL function "loader_load_staged_data" line 23 at EXECUTE statement<br>SQL function "loader_load_staged_data" statement 1<br><br>======Scripts===<br>#!/bin/bash <br>wget http://www2.census.gov/geo/pvs/tiger2010st/11_District_of_Columbia/ --no-parent --relative --recursive --level=2 --accept=zip,txt --mirror --reject=html <br><br>rm -f /gisdata/temp/*.* <br> cd /gisdata/www2.census.gov/geo/pvs/tiger2010st/11_District_of_Columbia<br><br>filelist=`ls -r *.zip`<br>for unzipfile in $filelist; do<br> unzip $unzipfile -d /gisdata/temp/<br>done<br><br>filelist=`ls -r */*.zip`<br>for unzipfile in $filelist; do<br> unzip $unzipfile -d /gisdata/temp/<br>done<br> <br>cd /gisdata/temp <br><br>psql -U postgres test -c "CREATE TABLE tiger_data.DC_state(CONSTRAINT pk_DC_state PRIMARY KEY (gid) ) INHERITS(state);"<br> <br>shp2pgsql -c -s 4269 -g the_geom -W "latin1" tl_2010_11_state10.dbf tiger_data.dc_state10 | psql -U postgres test <br><br>!!! Works fine until here<br>!!!!Error Happens Below psql<br>psql -U postgres test -c "SELECT loader_load_staged_data(lower('DC_state10'), lower('DC_state'));"<br><br>!!! I Stopped Here<br>psql -U postgres test -c "ALTER TABLE tiger_data.DC_state ADD CONSTRAINT uidx_DC_state_stusps UNIQUE (stusps);" <br>psql -U postgres test -c "CREATE INDEX tiger_data_DC_state_the_geom_gist ON tiger_data.DC_state USING gist(the_geom);" <br><br>psql -U postgres test -c "ALTER TABLE tiger_data.DC_state ADD CONSTRAINT chk_statefp CHECK (statefp = '11');" <br><br>psql -U postgres test -c "VACUUM ANALYZE tiger_data.DC_state"<br><br>psql -U postgres test -c "CREATE TABLE tiger_data.DC_county(CONSTRAINT pk_DC_county PRIMARY KEY (gid) ) INHERITS(county); "<br> <br>shp2pgsql -c -s 4269 -g the_geom -W "latin1" tl_2010_11_county10.dbf tiger_data.dc_county10 | psql -U postgres test <br><br>psql -U postgres test -c "ALTER TABLE tiger_data.DC_county10 RENAME geoid10 TO cntyidfp; SELECT loader_load_staged_data(lower('DC_county10'), lower('DC_county')); ALTER TABLE tiger_data.DC_county ADD CONSTRAINT uidx_DC_county_cntyidfp UNIQUE (cntyidfp);"<br><br>psql -U postgres test -c "CREATE INDEX tiger_data_DC_county_the_geom_gist ON tiger_data.DC_county USING gist(the_geom);"<br><br>psql -U postgres test -c "CREATE TABLE tiger_data.DC_county_lookup ( CONSTRAINT pk_DC_county_lookup PRIMARY KEY (st_code, co_code)) INHERITS (county_lookup);" <br>psql -U postgres test -c "VACUUM ANALYZE tiger_data.DC_county;" <br>psql -U postgres test -c "INSERT INTO tiger_data.DC_county_lookup(st_code, state, co_code, name) SELECT CAST(statefp as integer), 'DC', CAST(countyfp as integer), name FROM tiger_data.DC_county;" <br>psql -U postgres test -c "ALTER TABLE tiger_data.DC_county ADD CONSTRAINT chk_statefp CHECK (statefp = '11');" <br>psql -U postgres test -c "VACUUM ANALYZE tiger_data.DC_county_lookup;" <br>psql -U postgres test -c "CREATE TABLE tiger_data.DC_place(CONSTRAINT pk_DC_place10 PRIMARY KEY (plcidfp) ) INHERITS(place);" <br> shp2pgsql -c -s 4269 -g the_geom -W "latin1" tl_2010_11_place10.dbf tiger_data.dc_place10 | psql -U postgres test <br>psql -U postgres test -c "ALTER TABLE tiger_data.DC_place10 RENAME geoid10 TO plcidfp;SELECT loader_load_staged_data(lower('DC_place10'), lower('DC_place')); ALTER TABLE tiger_data.DC_place ADD CONSTRAINT uidx_DC_place_gid UNIQUE (gid);" <br>psql -U postgres test -c "CREATE INDEX idx_DC_place_soundex_name ON tiger_data.DC_place USING btree (soundex(name));" <br>psql -U postgres test -c "CREATE INDEX tiger_data_DC_place_the_geom_gist ON tiger_data.DC_place USING gist(the_geom);" <br>psql -U postgres test -c "ALTER TABLE tiger_data.DC_place ADD CONSTRAINT chk_statefp CHECK (statefp = '11');" <br>psql -U postgres test -c "CREATE TABLE tiger_data.DC_cousub(CONSTRAINT pk_DC_cousub PRIMARY KEY (cosbidfp), CONSTRAINT uidx_DC_cousub_gid UNIQUE (gid)) INHERITS(cousub);" <br> shp2pgsql -c -s 4269 -g the_geom -W "latin1" tl_2010_11_cousub10.dbf tiger_data.dc_cousub10 | psql -U postgres test <br>psql -U postgres test -c "ALTER TABLE tiger_data.DC_cousub10 RENAME geoid10 TO cosbidfp;SELECT loader_load_staged_data(lower('DC_cousub10'), lower('DC_cousub')); ALTER TABLE tiger_data.DC_cousub ADD CONSTRAINT chk_statefp CHECK (statefp = '11');" <br>psql -U postgres test -c "CREATE INDEX tiger_data_DC_cousub_the_geom_gist ON tiger_data.DC_cousub USING gist(the_geom);" <br>psql -U postgres test -c "CREATE TABLE tiger_data.DC_faces(CONSTRAINT pk_DC_faces PRIMARY KEY (gid)) INHERITS(faces);" <br> for z in *faces.dbf do <br> shp2pgsql -s 4269 -g the_geom -W "latin1" $z tiger_data.DC_faces | psql -U postgres test <br>psql -U postgres test -c "SELECT loader_load_staged_data(lower('DC_faces'), lower('DC_faces'));" <br> done <br> <br>psql -U postgres test -c "CREATE INDEX tiger_data_DC_faces_the_geom_gist ON tiger_data.DC_faces USING gist(the_geom);" <br>psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_faces_tfid ON tiger_data.DC_faces USING btree (tfid);" <br>psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_faces_countyfp ON tiger_data.DC_faces USING btree (countyfp);" <br>psql -U postgres test -c "ALTER TABLE tiger_data.DC_faces ADD CONSTRAINT chk_statefp CHECK (statefp = '11');" <br>psql -U postgres test -c "vacuum analyze tiger_data.DC_faces;" <br>psql -U postgres test -c "CREATE TABLE tiger_data.DC_featnames(CONSTRAINT pk_DC_featnames PRIMARY KEY (gid)) INHERITS(featnames);" <br> for z in *featnames.dbf do <br> shp2pgsql -s 4269 -g the_geom -W "latin1" $z tiger_data.DC_featnames | psql -U postgres test <br>psql -U postgres test -c "SELECT loader_load_staged_data(lower('DC_featnames'), lower('DC_featnames'));" <br> done <br> <br>psql -U postgres test -c "UPDATE tiger_data.DC_featnames SET statefp = '11' WHERE statefp IS NULL;" <br>psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_featnames_snd_name ON tiger_data.DC_featnames USING btree (soundex(name));" <br>psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_featnames_lname ON tiger_data.DC_featnames USING btree (lower(name));" <br>psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_featnames_tlid_statefp ON tiger_data.DC_featnames USING btree (tlid,statefp);" <br>psql -U postgres test -c "ALTER TABLE tiger_data.DC_featnames ADD CONSTRAINT chk_statefp CHECK (statefp = '11');" <br>psql -U postgres test -c "vacuum analyze tiger_data.DC_featnames;" <br>psql -U postgres test -c "CREATE TABLE tiger_data.DC_edges(CONSTRAINT pk_DC_edges PRIMARY KEY (gid)) INHERITS(edges);" <br> for z in *edges.dbf do <br> shp2pgsql -s 4269 -g the_geom -W "latin1" $z tiger_data.DC_edges | psql -U postgres test <br>psql -U postgres test -c "SELECT loader_load_staged_data(lower('DC_edges'), lower('DC_edges'));" <br> done <br> <br>psql -U postgres test -c "ALTER TABLE tiger_data.DC_edges ADD CONSTRAINT chk_statefp CHECK (statefp = '11');" <br>psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_edges_tlid ON tiger_data.DC_edges USING btree (tlid);" <br>psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_edges_tfidr ON tiger_data.DC_edges USING btree (tfidr);" <br>psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_edges_tfidl ON tiger_data.DC_edges USING btree (tfidl);" <br>psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_edges_countyfp ON tiger_data.DC_edges USING btree (countyfp);" <br>psql -U postgres test -c "CREATE INDEX tiger_data_DC_edges_the_geom_gist ON tiger_data.DC_edges USING gist(the_geom);" <br>psql -U postgres test -c "ALTER TABLE tiger_data.DC_edges ADD CONSTRAINT chk_statefp CHECK (statefp = '11');" <br>psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_edges_zipl ON tiger_data.DC_edges USING btree (zipl);" <br>psql -U postgres test -c "CREATE TABLE tiger_data.DC_zip_state_loc(CONSTRAINT pk_DC_zip_state_loc PRIMARY KEY(zip,stusps,place)) INHERITS(zip_state_loc);" <br>psql -U postgres test -c "INSERT INTO tiger_data.DC_zip_state_loc(zip,stusps,statefp,place) SELECT DISTINCT e.zipl, 'DC', '11', p.name FROM tiger_data.DC_edges AS e INNER JOIN tiger_data.DC_faces AS f ON (e.tfidl = f.tfid OR e.tfidr = f.tfid) INNER JOIN tiger_data.DC_place As p ON(f.statefp = p.statefp AND f.placefp = p.placefp ) WHERE e.zipl IS NOT NULL;" <br>psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_zip_state_loc_place ON tiger_data.DC_zip_state_loc USING btree(soundex(place));" <br>psql -U postgres test -c "ALTER TABLE tiger_data.DC_zip_state_loc ADD CONSTRAINT chk_statefp CHECK (statefp = '11');" <br>psql -U postgres test -c "vacuum analyze tiger_data.DC_edges;" <br>psql -U postgres test -c "vacuum analyze tiger_data.DC_zip_state_loc;" <br>psql -U postgres test -c "CREATE TABLE tiger_data.DC_zip_lookup_base(CONSTRAINT pk_DC_zip_state_loc_city PRIMARY KEY(zip,state, county, city, statefp)) INHERITS(zip_lookup_base);" <br>psql -U postgres test -c "INSERT INTO tiger_data.DC_zip_lookup_base(zip,state,county,city, statefp) SELECT DISTINCT e.zipl, 'DC', c.name,p.name,'11' FROM tiger_data.DC_edges AS e INNER JOIN tiger_data.DC_county As c ON (e.countyfp = c.countyfp AND e.statefp = c.statefp AND e.statefp = '11') INNER JOIN tiger_data.DC_faces AS f ON (e.tfidl = f.tfid OR e.tfidr = f.tfid) INNER JOIN tiger_data.DC_place As p ON(f.statefp = p.statefp AND f.placefp = p.placefp ) WHERE e.zipl IS NOT NULL;"+<br>psql -U postgres test -c "ALTER TABLE tiger_data.DC_zip_lookup_base ADD CONSTRAINT chk_statefp CHECK (statefp = '11');" <br>psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_zip_lookup_base_citysnd ON tiger_data.DC_zip_lookup_base USING btree(soundex(city));" <br>psql -U postgres test -c "CREATE TABLE tiger_data.DC_addr(CONSTRAINT pk_DC_addr PRIMARY KEY (gid)) INHERITS(addr);" <br> for z in *addr.dbf do <br> shp2pgsql -s 4269 -g the_geom -W "latin1" $z tiger_data.DC_addr | psql -U postgres test <br>psql -U postgres test -c "SELECT loader_load_staged_data(lower('DC_addr'), lower('DC_addr'));" <br> done <br> <br>psql -U postgres test -c "UPDATE tiger_data.DC_addr SET statefp = '11' WHERE statefp IS NULL;" <br>psql -U postgres test -c "ALTER TABLE tiger_data.DC_addr ADD CONSTRAINT chk_statefp CHECK (statefp = '11');" <br>psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_addr_least_address ON tiger_data.ma_addr USING btree (least_hn(fromhn,tohn) );" <br>psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_addr_tlid_statefp ON tiger_data.DC_addr USING btree (tlid, statefp);" <br>psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_addr_zip ON tiger_data.DC_addr USING btree (zip);" <br>psql -U postgres test -c "CREATE TABLE tiger_data.DC_zip_state(CONSTRAINT pk_DC_zip_state PRIMARY KEY(zip,stusps)) INHERITS(zip_state); " <br>psql -U postgres test -c "INSERT INTO tiger_data.DC_zip_state(zip,stusps,statefp) SELECT DISTINCT zip, 'DC', '11' FROM tiger_data.DC_addr WHERE zip is not null;" <br>psql -U postgres test -c "ALTER TABLE tiger_data.DC_zip_state ADD CONSTRAINT chk_statefp CHECK (statefp = '11');" <br>psql -U postgres test -c "vacuum analyze tiger_data.DC_addr;"<br><br> </body>
</html>