#4338 closed defect (fixed)

Census block level data (tabblock table) not installing

Reported by: jedaniel Owned by: robe
Priority: high Milestone: PostGIS 2.5.4
Component: tiger geocoder Version: 2.5.x
Keywords: census block tabblock windows Cc:


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.

Change History (5)

comment:1 Changed 19 months ago by robe

Milestone: PostGIS 2.5.2PostGIS 3.0.0

comment:2 Changed 12 months ago by robe

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

comment:3 Changed 12 months ago by 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

comment:4 Changed 12 months ago by robe

Milestone: PostGIS 3.0.0PostGIS 2.5.4

comment:5 Changed 12 months ago by robe

Resolution: fixed
Status: newclosed

In 17826:

Fix issue with loading tabblock
Closes #4338 for PostGIS 2.5.4

Note: See TracTickets for help on using tickets.