Vjylku

I'm working with postgis and followed many tutorials out there to try to use tiger data.

this one seems to be the best:

https://bluehatrecord.wordpress.com/2015/07/06/installing-the-tiger-geocoder-with-rhel-6-6-postgresql-9-4-postgis-2-1-7-and-the-tiger-2013-census-data/

To save time and make sure things are working, I reduced it to a smaller set of states to populate my Tiger data.

Looking in the output, I'm seeing things like this:

Unable to open tl_2016_44007_addr.shp or tl_2016_44007_addr.SHP.

tl_2016_44007_addr.dbf: shape (.shp) or index files (.shx) can not be opened, will just import attribute data.

and

Unable to open tl_2016_36019_featnames.shp or tl_2016_36019_featnames.SHP.

tl_2016_36019_featnames.dbf: shape (.shp) or index files (.shx) can not be opened, will just import attribute data.

and sometimes:

HTTP request sent, awaiting response... 304 Not Modified

File ‘www2.census.gov/geo/tiger/TIGER2016/PLACE/tl_2016_11_place.zip’ not modified on server. Omitting download.

I assume the second error means that it already downloaded the file, so it skipped it?

I ran the script again with just DC as a state, and even went with the author's example for a sanity check:

SELECT g.rating, ST_X(g.geomout) As lon, ST_Y(g.geomout) As lat, (addy).address As stno, (addy).streetname As street, (addy).streettypeabbrev As styp, (addy).location As city, (addy).stateabbrev As st, (addy).zip FROM geocode('1600 Pennsylvania Avenue Northwest, Washington, DC 20500') As g;

Success! I got back the matching data, which is good, but when I ran his second example to confirm:

SELECT g.*, ST_X(g.geomout) As lon,ST_Y(g.geomout) As lat,(addy).*, bg.tabblock_id AS FIPS FROM geocode('1600 Pennsylvania Avenue Northwest, Washington, DC 20500') As g

JOIN tabblock As bg

ON (g.geomout && bg.the_geom)

WHERE ST_Within (g.geomout, bg.the_geom);

it returned back 0 records. I did a select * from tabblock and it returned 0 records.

What table is that, and should it normally download based on the script?

If not, how is this table populated?

The 304 error that you're getting is expected since you're re-running the script and the data has already been downloaded locally for most of the data sets and isn't deleted between runs.

There seems to be two bugs in the function that generates the load script that provides the wrong URL for the tabblocks dataset, however.

First, if you try to visit the tabblocks URL for the link in the script for it, you'll get a 404 error. In 2014 TIGER started adding the number 10 to the ZIP files. For example, it went from:

http://www2.census.gov/geo/tiger/TIGER2016/TABBLOCK/tl_2016_25_tabblock.zip

to

http://www2.census.gov/geo/tiger/TIGER2016/TABBLOCK/tl_2016_25_tabblock10.zip

This also applies to all the files within the zipped folder.

Second, the command to move the data from the staging schema to the tiger_data schema is missing altogether.

The following Python script can be used to fix both issues:

state_file_path = 'path/to/your/script'

with open(state_file_path, 'r') as file:

    script_data = file.read()



script_data_correct_tabblock = script_data 

    .replace('_tabblock.zip', '_tabblock10.zip') 

    .replace('tl_2016_25_tabblock.dbf', 'tl_2016_25_tabblock10.dbf') 

    .replace(

        '"ALTER TABLE tiger_staging.MA_tabblock RENAME geoid10 TO tabblock_id;"',

        '"ALTER TABLE tiger_staging.MA_tabblock RENAME geoid10 TO tabblock_id; SELECT loader_load_staged_data(lower('MA_tabblock'), lower('MA_tabblock'));"')



with open(state_file_path, 'w') as file:

    file.write(script_data_correct_tabblock)