[postgis-tickets] [PostGIS] #3698: CENSUS Download path for TABBLOCK

[PostGIS]

#3698: CENSUS Download path for TABBLOCK
-----------------------------+---------------------------
  Reporter:  EvanCarroll     |      Owner:  robe
      Type:  defect          |     Status:  new
  Priority:  high            |  Milestone:  PostGIS 2.3.4
 Component:  tiger geocoder  |    Version:  2.3.x
Resolution:                  |   Keywords:
-----------------------------+---------------------------

Comment (by jeffreywescott):

 So, any recommendations for someone like me who has a use case that ''does
 require'' census block data?

 In the generated {{{load-states.sh}}} script I tried appending the
 filename references like so:

 {{{
 sed -i "s/_tabblock\./_tabblock10./g" load-states.sh
 }}}

 That seemed to get it to work. However, when I try to load a state (e.g.,
 {{{'CA'}}}), there are still no rows in the {{{tabblocks}}} table:

 {{{
 postgres=# select * from tabblock;
  gid | statefp | countyfp | tractce | blockce | tabblock_id | name | mtfcc
 | ur | uace | funcstat | aland | awater | intptlat | intptlon | the_geom
 -----+---------+----------+---------+---------+-------------+------+-------+----+------+----------+-------+--------+----------+----------+----------
 (0 rows)
 }}}

 I noticed that for most other tables, {{{loader_load_staged_data}}} is
 used to transfer the data from the {{{tiger_staging}}} to the
 {{{tiger_data}}} database, but that doesn't seem to be happening for the
 {{{tabblock}}} table in the script. Notice the difference between the
 {{{CA_tract}}} and {{{CA_tabblock}}} handling from the script excerpt
 below:

 {{{
 ${PSQL} -c "CREATE TABLE tiger_data.CA_tract(CONSTRAINT pk_CA_tract
 PRIMARY KEY (tract_id) ) INHERITS(tiger.tract); "
 ${SHP2PGSQL} -D -c -s 4269 -g the_geom   -W "latin1" tl_2013_06_tract.dbf
 tiger_staging.ca_tract | ${PSQL}
 ${PSQL} -c "ALTER TABLE tiger_staging.CA_tract RENAME geoid TO tract_id;
 SELECT loader_load_staged_data(lower('CA_tract'), lower('CA_tract')); "
         ${PSQL} -c "CREATE INDEX tiger_data_CA_tract_the_geom_gist ON
 tiger_data.CA_tract USING gist(the_geom);"
         ${PSQL} -c "VACUUM ANALYZE tiger_data.CA_tract;"
         ${PSQL} -c "ALTER TABLE tiger_data.CA_tract ADD CONSTRAINT
 chk_statefp CHECK (statefp = '06');"
 cd /gisdata
 #wget
 http://www2.census.gov/geo/tiger/TIGER2013/TABBLOCK/tl_2013_06_tabblock.zip
 --mirror --reject=html
 cd /gisdata/www2.census.gov/geo/tiger/TIGER2013/TABBLOCK
 rm -f ${TMPDIR}/*.*
 ${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
 ${PSQL} -c "CREATE SCHEMA tiger_staging;"
 for z in tl_2013_06*_tabblock.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
 cd $TMPDIR;

 ${PSQL} -c "CREATE TABLE tiger_data.CA_tabblock(CONSTRAINT pk_CA_tabblock
 PRIMARY KEY (tabblock_id)) INHERITS(tiger.tabblock);"
 ${SHP2PGSQL} -D -c -s 4269 -g the_geom   -W "latin1"
 tl_2013_06_tabblock.dbf tiger_staging.ca_tabblock | ${PSQL}
 ${PSQL} -c "ALTER TABLE tiger_staging.CA_tabblock RENAME geoid TO
 tabblock_id;"
 ${PSQL} -c "ALTER TABLE tiger_data.CA_tabblock ADD CONSTRAINT chk_statefp
 CHECK (statefp = '06');"
 ${PSQL} -c "CREATE INDEX tiger_data_CA_tabblock_the_geom_gist ON
 tiger_data.CA_tabblock USING gist(the_geom);"
 ${PSQL} -c "vacuum analyze tiger_data.CA_tabblock;"
 cd /gisdata
 #wget http://www2.census.gov/geo/tiger/TIGER2013/BG/tl_2013_06_bg.zip
 --mirror --reject=html
 cd /gisdata/www2.census.gov/geo/tiger/TIGER2013/BG
 rm -f ${TMPDIR}/*.*
 ${PSQL} -c "DROP SCHEMA IF EXISTS tiger_staging CASCADE;"
 ${PSQL} -c "CREATE SCHEMA tiger_staging;"
 for z in tl_2013_06*_bg.zip ; do $UNZIPTOOL -o -d $TMPDIR $z; done
 cd $TMPDIR;
 }}}

 Any ideas on how to make sure my {{{tabblock}}} table gets populated using
 the TIGER2016 data?

--
Ticket URL: <https://trac.osgeo.org/postgis/ticket/3698#comment:12>
PostGIS <http://trac.osgeo.org/postgis/>
The PostGIS Trac is used for bug, enhancement & task tracking, a user and developer wiki, and a view into the subversion code repository of PostGIS project.