2

I have the setup of the tiger geocoder for 15 states, the geocoding time for each address for each record takes between 30-100ms for most of the cases. The problem I'm having is that for a group of states: GA, CA, KS, NV the accuracy is very low, for all the cases the rating is 100. I'm comparing the local results from the results from the government site:

For all the states for which I get rating 100, the results are quite different than the ones from the census site, E.G.

For the address: 1030 Fennel Flower Ct, Las Vegas, NV 89138

I get this result:

rating |        lon        |       lat        | address | predirabbrev | streetname | streettypeabbrev | postdirabbrev | internal | location  | stateabbrev |  zip  | parsed 
--------+-------------------+------------------+---------+--------------+------------+------------------+---------------+----------+-----------+-------------+-------+--------
    100 | -115.260283810612 | 36.2287547147549 |         |              |            |                  |               |          | Las Vegas | NV          | 89138 | t  

The result from the census site: (-115.3580, 36.159386)

For: 2300 Windy Ridge Pkwy SE Atlanta GA 30339

geocoder=# SELECT g.rating, ST_X(geomout) As lon, ST_Y(geomout) As lat, (addy).* FROM geocode('2300 Windy Ridge Pkwy SE Atlanta GA 30339', 1) As g;
 rating |       lon        |       lat       | address | predirabbrev | streetname | streettypeabbrev | postdirabbrev | internal | location | stateabbrev |  zip  | parsed 
--------+------------------+-----------------+---------+--------------+------------+------------------+---------------+----------+----------+-------------+-------+--------
    100 | -84.423137810054 | 33.762673835605 |         |              |            |                  |               |          | Atlanta  | GA          | 30339 | t

The result from the census site: ( -84.46003, 33.905018)

There is another group of states like CT and MO for which the rating varies depending on the address and the result is closer than the ones from the census site:

For: 3510 E 27TH ST Kansas City Missouri 64127

rating |        lon        |       lat        | address | predirabbrev | streetname | streettypeabbrev | postdirabbrev | internal |  location   | stateabbrev |  zip  | parsed 
--------+-------------------+------------------+---------+--------------+------------+------------------+---------------+----------+-------------+-------------+-------+--------
      0 | -94.5420058110486 | 39.0768224510612 |    3510 | E            | 27th       | St               |               |          | Kansas City | MO          | 64127 | t

The result from the census site: (-94.54194, 39.076736)

I'm following the same procedure to load all the estates, how can I debug this?

EDIT:

For GA I got an error:

ERROR:  relation "tiger_data.ga_edges" does not exist

For MO I get:

geocoder=# SELECT count(*) FROM tiger_data.mo_edges;
  count  
---------
 2595279
(1 row)

I run drop_state_tables_generate_script as:

geocoder=# SELECT drop_state_tables_generate_script('GA');

got this output:

 drop_state_tables_generate_script 
-----------------------------------
 DROP TABLE tiger_data.ga_bg;     +
 DROP TABLE tiger_data.ga_cousub; +
 DROP TABLE tiger_data.ga_place;  +
 DROP TABLE tiger_data.ga_tract;  +
 DROP TABLE tiger_data.ga_zcta5;
(1 row)

While loading GA again I get this:

DROP SCHEMA
CREATE SCHEMA
Archive:  tl_2015_13_place.zip
 extracting: /GIS/gis/gisdata/temp/tl_2015_13_place.cpg  
  inflating: /GIS/gis/gisdata/temp/tl_2015_13_place.dbf  
  inflating: /GIS/gis/gisdata/temp/tl_2015_13_place.prj  
  inflating: /GIS/gis/gisdata/temp/tl_2015_13_place.shp  
  inflating: /GIS/gis/gisdata/temp/tl_2015_13_place.shp.ea.iso.xml  
  inflating: /GIS/gis/gisdata/temp/tl_2015_13_place.shp.iso.xml  
  inflating: /GIS/gis/gisdata/temp/tl_2015_13_place.shp.xml  
  inflating: /GIS/gis/gisdata/temp/tl_2015_13_place.shx  
unzip:  cannot find or open */tl_*_13*_place.zip, */tl_*_13*_place.zip.zip or */tl_*_13*_place.zip.ZIP.

No zipfiles found.
ERROR:  relation "ga_place" already exists

Is that normal?

  • See my note on other - answer. Do \t \a in psql. then run the SELECT drop_state_tables_generate_script('GA'); Then copy and paste to run the output of that query. The no zipfiles found error you can ignore, I think that was a bug in tiger geocoder I fixed that tries to unzip extra things. – LR1234567 Jun 21 '17 at 13:20
2

From the above it sounds like you are missing some tables. The census akamai firewall has caused a lot of problems and often causes this issue with half loaded states resulting. It seems to start blocking after you load about 3 states.

First to confirm this is the issue:

1) Check the tiger_data schema and verify you have the following for each state in question:

e.g for CA you should have: ca_addr, ca_cousub, ca_edges, ca_faces, ca_featnames, ca_place, there some zip ones as well but these are the core ones.

Check and make sure you have lots of data in ca_edges.

For example:

SELECT count(*) FROM tiger_data.ca_edges;

Returns: 3859799 (I have tiger 2016 data loaded)

SELECT count(*) FROM tiger_data.ga_edges;

Returns: 1977356

For GA with this query:

SELECT ST_AsText(geomout) FROM geocode('2300 Windy Ridge Pkwy SE Atlanta GA 30339',1);

I get: POINT(-84.4600550654882 33.9051143967142)

which looks pretty close to census answer.

If these are wrong, reload the state in question first by dropping the tables using this function:

http://postgis.net/docs/manual-2.3/Drop_State_Tables_Generate_Script.html

Note the function only generates the script you need to run to drop the tables, so copy and paste what it generates into psql.

Then reload the state in question.

Unfortunately haven't come up with a good fix for the firewall issue short of changing public ips or waiting it out.

  • I'm getting an error for that query, I update the question. – Luis Ramon Ramirez Rodriguez Jun 20 '17 at 12:23
  • Hmm to get rid of the + in psql, try doing \t \a firlst and then run the query. The error you get during reload, is probably because your execute failed because of the +s in there. – LR1234567 Jun 21 '17 at 13:16

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.