[postgis-users] Tiger 2010 Geocoder in PostGIS 1.5
Shibata Takeo
takeofuture at hotmail.com
Thu Jun 2 14:06:22 PDT 2011
Hi I am using Fedora 14 and Fedora 15.
I set Tiger Geocoder for Tiger 2009 using PostGIS1.5
(All installed by yum Fedora binary package).
It works fine.
But I found that there is some missing data so I would like to use Tiger 2010
(I assume that data is more recent one)
As far as I understand, Tiger 2010 data has different structure/schema from Tiger 2009
but I can upgrade using upgrade script.
I download geos 3.3 (since Fedora repository only have geos 3.2.1), gdal,
and download PostGIS2.0 and compile.
Then run
tiger_loader.sql
upgrade_geocode.sql from Tiger 2010 (created by PostGIS 2.0 installation)
But the resulting scripts does not work well.
I create the scripts using
SELECT loader_generate_script(ARRAY['DC'], 'sh');
(The only option is 'windows' and 'sh', no 'linux' option unlike Tiger 2009)
But this newly generated scripts does not work.ls
As described in ReadME, execute the following (create_geocode.sh in Census 2009)
"%PGBIN%\psql" -d "%THEDB%" -f "%PGCONTRIB%\fuzzystrmatch.sql"
"%PGBIN%\psql" -d "%THEDB%" -c "CREATE SCHEMA tiger"
"%PGBIN%\psql" -d "%THEDB%" -f "tables\lookup_tables_revised.sql"
"%PGBIN%\psql" -d "%THEDB%" -c "CREATE SCHEMA tiger_data"
"%PGBIN%\psql" -d "%THEDB%" -f "tiger_loader.sql"
"%PGBIN%\psql" -d "%THEDB%" -f "create_geocode.sql"
As described in ReadME, execute the following (upgrade_geocode.sh in Census 2010)
"%PGBIN%\psql" -d "%THEDB%" -f "tiger_loader.sql"
"%PGBIN%\psql" -d "%THEDB%" -f "upgrade_geocode.sql"
So I run the SQL in the
SELECT loader_generate_script(ARRAY['DC'], 'sh');
There are a lot of syntax error so I execute one by one by checking the sentence.
But the following SQL has the issue.
I am not sure where I have to look to correct the issue for the SQL syntax error (missing table).
The whole generated script is attached.
[root at gistmp temp]# psql -U postgres test -c "SELECT loader_load_staged_data(lower('DC_state10'), lower('DC_state'));"
NOTICE: 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;
CONTEXT: SQL function "loader_load_staged_data" statement 1
ERROR: syntax error at or near "FROM"
LINE 1: ...,aland,awater,intptlat,intptlon,the_geom) SELECT FROM tiger...
^
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;
CONTEXT: PL/pgSQL function "loader_load_staged_data" line 23 at EXECUTE statement
SQL function "loader_load_staged_data" statement 1
======Scripts===
#!/bin/bash
wget http://www2.census.gov/geo/pvs/tiger2010st/11_District_of_Columbia/ --no-parent --relative --recursive --level=2 --accept=zip,txt --mirror --reject=html
rm -f /gisdata/temp/*.*
cd /gisdata/www2.census.gov/geo/pvs/tiger2010st/11_District_of_Columbia
filelist=`ls -r *.zip`
for unzipfile in $filelist; do
unzip $unzipfile -d /gisdata/temp/
done
filelist=`ls -r */*.zip`
for unzipfile in $filelist; do
unzip $unzipfile -d /gisdata/temp/
done
cd /gisdata/temp
psql -U postgres test -c "CREATE TABLE tiger_data.DC_state(CONSTRAINT pk_DC_state PRIMARY KEY (gid) ) INHERITS(state);"
shp2pgsql -c -s 4269 -g the_geom -W "latin1" tl_2010_11_state10.dbf tiger_data.dc_state10 | psql -U postgres test
!!! Works fine until here
!!!!Error Happens Below psql
psql -U postgres test -c "SELECT loader_load_staged_data(lower('DC_state10'), lower('DC_state'));"
!!! I Stopped Here
psql -U postgres test -c "ALTER TABLE tiger_data.DC_state ADD CONSTRAINT uidx_DC_state_stusps UNIQUE (stusps);"
psql -U postgres test -c "CREATE INDEX tiger_data_DC_state_the_geom_gist ON tiger_data.DC_state USING gist(the_geom);"
psql -U postgres test -c "ALTER TABLE tiger_data.DC_state ADD CONSTRAINT chk_statefp CHECK (statefp = '11');"
psql -U postgres test -c "VACUUM ANALYZE tiger_data.DC_state"
psql -U postgres test -c "CREATE TABLE tiger_data.DC_county(CONSTRAINT pk_DC_county PRIMARY KEY (gid) ) INHERITS(county); "
shp2pgsql -c -s 4269 -g the_geom -W "latin1" tl_2010_11_county10.dbf tiger_data.dc_county10 | psql -U postgres test
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);"
psql -U postgres test -c "CREATE INDEX tiger_data_DC_county_the_geom_gist ON tiger_data.DC_county USING gist(the_geom);"
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);"
psql -U postgres test -c "VACUUM ANALYZE tiger_data.DC_county;"
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;"
psql -U postgres test -c "ALTER TABLE tiger_data.DC_county ADD CONSTRAINT chk_statefp CHECK (statefp = '11');"
psql -U postgres test -c "VACUUM ANALYZE tiger_data.DC_county_lookup;"
psql -U postgres test -c "CREATE TABLE tiger_data.DC_place(CONSTRAINT pk_DC_place10 PRIMARY KEY (plcidfp) ) INHERITS(place);"
shp2pgsql -c -s 4269 -g the_geom -W "latin1" tl_2010_11_place10.dbf tiger_data.dc_place10 | psql -U postgres test
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);"
psql -U postgres test -c "CREATE INDEX idx_DC_place_soundex_name ON tiger_data.DC_place USING btree (soundex(name));"
psql -U postgres test -c "CREATE INDEX tiger_data_DC_place_the_geom_gist ON tiger_data.DC_place USING gist(the_geom);"
psql -U postgres test -c "ALTER TABLE tiger_data.DC_place ADD CONSTRAINT chk_statefp CHECK (statefp = '11');"
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);"
shp2pgsql -c -s 4269 -g the_geom -W "latin1" tl_2010_11_cousub10.dbf tiger_data.dc_cousub10 | psql -U postgres test
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');"
psql -U postgres test -c "CREATE INDEX tiger_data_DC_cousub_the_geom_gist ON tiger_data.DC_cousub USING gist(the_geom);"
psql -U postgres test -c "CREATE TABLE tiger_data.DC_faces(CONSTRAINT pk_DC_faces PRIMARY KEY (gid)) INHERITS(faces);"
for z in *faces.dbf do
shp2pgsql -s 4269 -g the_geom -W "latin1" $z tiger_data.DC_faces | psql -U postgres test
psql -U postgres test -c "SELECT loader_load_staged_data(lower('DC_faces'), lower('DC_faces'));"
done
psql -U postgres test -c "CREATE INDEX tiger_data_DC_faces_the_geom_gist ON tiger_data.DC_faces USING gist(the_geom);"
psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_faces_tfid ON tiger_data.DC_faces USING btree (tfid);"
psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_faces_countyfp ON tiger_data.DC_faces USING btree (countyfp);"
psql -U postgres test -c "ALTER TABLE tiger_data.DC_faces ADD CONSTRAINT chk_statefp CHECK (statefp = '11');"
psql -U postgres test -c "vacuum analyze tiger_data.DC_faces;"
psql -U postgres test -c "CREATE TABLE tiger_data.DC_featnames(CONSTRAINT pk_DC_featnames PRIMARY KEY (gid)) INHERITS(featnames);"
for z in *featnames.dbf do
shp2pgsql -s 4269 -g the_geom -W "latin1" $z tiger_data.DC_featnames | psql -U postgres test
psql -U postgres test -c "SELECT loader_load_staged_data(lower('DC_featnames'), lower('DC_featnames'));"
done
psql -U postgres test -c "UPDATE tiger_data.DC_featnames SET statefp = '11' WHERE statefp IS NULL;"
psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_featnames_snd_name ON tiger_data.DC_featnames USING btree (soundex(name));"
psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_featnames_lname ON tiger_data.DC_featnames USING btree (lower(name));"
psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_featnames_tlid_statefp ON tiger_data.DC_featnames USING btree (tlid,statefp);"
psql -U postgres test -c "ALTER TABLE tiger_data.DC_featnames ADD CONSTRAINT chk_statefp CHECK (statefp = '11');"
psql -U postgres test -c "vacuum analyze tiger_data.DC_featnames;"
psql -U postgres test -c "CREATE TABLE tiger_data.DC_edges(CONSTRAINT pk_DC_edges PRIMARY KEY (gid)) INHERITS(edges);"
for z in *edges.dbf do
shp2pgsql -s 4269 -g the_geom -W "latin1" $z tiger_data.DC_edges | psql -U postgres test
psql -U postgres test -c "SELECT loader_load_staged_data(lower('DC_edges'), lower('DC_edges'));"
done
psql -U postgres test -c "ALTER TABLE tiger_data.DC_edges ADD CONSTRAINT chk_statefp CHECK (statefp = '11');"
psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_edges_tlid ON tiger_data.DC_edges USING btree (tlid);"
psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_edges_tfidr ON tiger_data.DC_edges USING btree (tfidr);"
psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_edges_tfidl ON tiger_data.DC_edges USING btree (tfidl);"
psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_edges_countyfp ON tiger_data.DC_edges USING btree (countyfp);"
psql -U postgres test -c "CREATE INDEX tiger_data_DC_edges_the_geom_gist ON tiger_data.DC_edges USING gist(the_geom);"
psql -U postgres test -c "ALTER TABLE tiger_data.DC_edges ADD CONSTRAINT chk_statefp CHECK (statefp = '11');"
psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_edges_zipl ON tiger_data.DC_edges USING btree (zipl);"
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);"
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;"
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));"
psql -U postgres test -c "ALTER TABLE tiger_data.DC_zip_state_loc ADD CONSTRAINT chk_statefp CHECK (statefp = '11');"
psql -U postgres test -c "vacuum analyze tiger_data.DC_edges;"
psql -U postgres test -c "vacuum analyze tiger_data.DC_zip_state_loc;"
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);"
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;"+
psql -U postgres test -c "ALTER TABLE tiger_data.DC_zip_lookup_base ADD CONSTRAINT chk_statefp CHECK (statefp = '11');"
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));"
psql -U postgres test -c "CREATE TABLE tiger_data.DC_addr(CONSTRAINT pk_DC_addr PRIMARY KEY (gid)) INHERITS(addr);"
for z in *addr.dbf do
shp2pgsql -s 4269 -g the_geom -W "latin1" $z tiger_data.DC_addr | psql -U postgres test
psql -U postgres test -c "SELECT loader_load_staged_data(lower('DC_addr'), lower('DC_addr'));"
done
psql -U postgres test -c "UPDATE tiger_data.DC_addr SET statefp = '11' WHERE statefp IS NULL;"
psql -U postgres test -c "ALTER TABLE tiger_data.DC_addr ADD CONSTRAINT chk_statefp CHECK (statefp = '11');"
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) );"
psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_addr_tlid_statefp ON tiger_data.DC_addr USING btree (tlid, statefp);"
psql -U postgres test -c "CREATE INDEX idx_tiger_data_DC_addr_zip ON tiger_data.DC_addr USING btree (zip);"
psql -U postgres test -c "CREATE TABLE tiger_data.DC_zip_state(CONSTRAINT pk_DC_zip_state PRIMARY KEY(zip,stusps)) INHERITS(zip_state); "
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;"
psql -U postgres test -c "ALTER TABLE tiger_data.DC_zip_state ADD CONSTRAINT chk_statefp CHECK (statefp = '11');"
psql -U postgres test -c "vacuum analyze tiger_data.DC_addr;"
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110602/1808f05a/attachment.html>
More information about the postgis-users
mailing list