1

Similar to this question, my TIGER geocoder extension in PostGIS isn't returning any results. It seemed to all install correctly, so I imported DC, and:

psql -d geocoder -c "SELECT g.rating, ST_X(geomout) As lon, ST_Y(geomout) As lat, (addy).* FROM geocode('1731 New Hampshire Avenue Northwest, Washington, DC 20010', 1) As g;"

... from the command line returns:

rating | lon | lat | address | predirabbrev | streetname | streettypeabbrev | postdirabbrev | internal | location | stateabbrev | zip | parsed --------+-----+-----+---------+--------------+------------+------------------+---------------+----------+----------+-------------+-----+-------- (0 rows)

Unlike that other question, I have a repeatable case, since I'm trying to set this up in a Dockerfile. Can anyone see what might be wrong here? Is there a step that I've messed up?

Ubuntu (Trusty), PostgreSQL 9.3, PostGIS 2.1 (installed 'manually', i.e. not as an extension), attempting to use TIGER 2013 data.

2

Check if the data loaded successfully (sometimes a lot of census FTP dowloads fail and the load will continue. You won't know unless you captured that error log and check it). I just ran this query and geocodes perfectly using TIGER 2013.

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(normalize_address('1731 New Hampshire Avenue Northwest, Washington, DC 20010'), 1) As g;  

 rating |        lon        |       lat        | stno |    street     | styp |    city    | st |  zip  
--------+-------------------+------------------+------+---------------+------+------------+----+-------
      1 | -77.0398083968318 | 38.9133648716727 | 1731 | New Hampshire | Ave  | Washington | DC | 20009
  • has something changed in recent versions? When I run the query shown in this answer I get no results. – Joe Audette Oct 27 '18 at 16:30
  • 1
    it seems in my installation the zcta5 table is empty and there is no row for it in loader_lookuptables. I guess that is why I get no results. – Joe Audette Oct 27 '18 at 16:58

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.