[postgis-tickets] [PostGIS] #4338: Census block level data (tabblock table) not installing

[PostGIS]

#4338: Census block level data (tabblock table) not installing
-------------------------------------------+---------------------------
 Reporter:  jedaniel                       |      Owner:  robe
     Type:  defect                         |     Status:  new
 Priority:  high                           |  Milestone:  PostGIS 2.5.2
Component:  tiger geocoder                 |    Version:  2.5.x
 Keywords:  census block tabblock windows  |
-------------------------------------------+---------------------------
 When trying to install the Census tract, bg, and tabblock (census blocks)
 data, the tabblock table is never populated (tract and bg tables appear
 fine) under Windows.  Have not tested this under Linux nor OSX, but
 problem persisted across multiple states, and appears whether one uses
 loader_generate_script() (with appropriate UPDATE
 tiger.loader_lookuptables) or loader_generate_census_script() functions.

 As best I can tell, the problem stems from this line in the generated
 batch file that are supposed to import the data:

 {{{
 %PSQL% -c "ALTER TABLE tiger_staging.MA_tract RENAME geoid TO tract_id;
 SELECT loader_load_staged_data(lower('MA_tract'), lower('MA_tract')); "
 }}}

 The problem stems from the ALTER TABLE statement in this line which
 incorrectly tries to rename **geoid** column of the table
 **tiger_staging.MA_tabblock**, which does not exist.  If the ALTER
 statement fails, then loader_load_staged_data() fails due to mismatched
 column names (i.e. geoid is supposed to be renamed to tract_id).

 Altering the line as follows appears to remedy the problem:

 {{{
 %PSQL% -c "ALTER TABLE tiger_staging.MA_tabblock10 RENAME geoid10 TO
 tabblock_id;  SELECT loader_load_staged_data(lower('MA_tabblock10'),
 lower('MA_tabblock')); "
 }}}

 That is to say, the proper name of the table in tiger_staging should be
 **MA_tabblock10** and not **MA_tabblock**.

 Fixing the loader_generate_script batch file by hand appears to remedy the
 problem, but hopefully a more elegant solution can be applied down the
 line.

-- 
Ticket URL: <https://trac.osgeo.org/postgis/ticket/4338>
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.