Census block level data (tabblock table) not installing

[jedaniel]

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.

[robe]

Tract loaded okay for me but tabblock is empty testing with 2019 data. Working on fixing that now.

[robe]

In 17825:

Fix issue with loading tabblock
Update geocoder to load 2019 data
Closes #4509 for PostGIS 3.0
References #4338 for PostGIS 3.0

[robe]

In 17826:

Fix issue with loading tabblock
Closes #4338 for PostGIS 2.5.4