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);"
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110602/6cc679c4/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: load.sh
Type: application/x-sh
Size: 46844 bytes
Desc: not available
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20110602/6cc679c4/attachment.sh>