0

I recently loaded TIGER 2018 data for California. After fixing all the errors I have loaded all the data into the database. I checked each of the tables and all of them contain data.

From this example How to make a PostGIS TIGER Geocoder in Less than 5 Days I ran this query to check.

SELECT g.rating,
    ST_AsText(ST_SnapToGrid(g.geomout,0.00001)) As wktlonlat,
    (addy).address As stno,
    (addy).streetname As street,
    (addy).streettypeabbrev As styp,
    (addy).location As city,
    (addy).stateabbrev As st,(addy).zip
FROM geocode('424 3rd St, Davis, CA 95616',1) As g;

I get this result.

 rating |         wktlonlat          | stno | street | styp |    city     | st |  zip

 100    | POINT(-120.91795 38.29745) |      |        |      | Buena Vista | CA | 94502

As you can see the stno, street and styp are empty. I am not sure what the problem is as all the data has been loaded.

0

I recently went through the same tutorial and found that by default the nation_script does not load the ZCTA5 datasets, which the geocoder() stored procedure ultimately uses. Run this query, then reload all the data (nation_script and state_scripts):

update tiger.loader_lookuptables
set load = 'true'
where lookup_name = 'zcta5_raw';

If that doesn't fix it you may have deeper issues. It took me many days to get mine to work, mostly because I am not a DBA, just a normal user and did not have permissions for some of the actions the scripts take (create/drop schemas, drop tables, etc.) If that is your situation, you'll also need to create your own versions of tables/stored procedures to get around the permissions issues.

  • I changed my postgres version to 10.6. Activated zcta. when you generate the scripts there would be some syntax errors in the code which can be easily fixed. I then ran the nation and state scripts for all the states. Everything is working smoothly now. – sushmit sarmah Dec 20 '18 at 20:46
0

Works smoothly with Postgres 10.6. Generates the nation and state scripts correctly. I ran it for all the states and haven't faced a problem.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.