2

I have gone through all of the of the steps to load Tiger Geocoder Extension, but something is not working. When I run through a number of addresses, all addresses are being assigned to the what I assume is the zip code centriod.

I run the code below which coalesces the columns containing the address elements into a format required for the geocode function. The second column I normalize the address to check that the normalizer is working.

The final column I ask for the geocode for the address. As the table below shows, the geocoder seems to ignore the street address and geocodes the zip code.

Any thoughts on what I have done wrong? (I do realize that I should remove the rows with PO Box, missing addresses or that lack street number, but that is a a second step to figuring out why the street number and name are not being processed in the geocode function.)

SELECT allpgq_ut.addr || ' ' || allpgq_ut.city || ', '|| allpgq_ut.st || ' ' || allpgq_ut.zip As geo,
normalize_address(allpgq_ut.addr || ' ' || allpgq_ut.city || ', '|| allpgq_ut.st || ' ' || allpgq_ut.zip) As geo2,
geocode(allpgq_ut.addr || ' ' || allpgq_ut.city || ', '|| allpgq_ut.st || ' ' || allpgq_ut.zip,1) As geo3
FROM allpgq_ut ORDER BY allpgq_ut.zip, allpgq_ut.addr limit 20

geo geo2    geo3
 Altamont, UT 84001 (,,,,,,Altamont,UT,84001,t) ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
 Altamont, UT 84001 (,,,,,,Altamont,UT,84001,t) ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
 Altamont, UT 84001 (,,,,,,Altamont,UT,84001,t) ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
 Altamont, UT 84001 (,,,,,,Altamont,UT,84001,t) ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
1 Altmnt Altamont, UT 84001 (1,,Altmnt,,,,Altamont,UT,84001,t)  ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
1304 North  15000 Altamont, UT 84001    (1304,N,15000,,,,Altamont,UT,84001,t)   ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
13750 West Sr 87 Altamont, UT 84001 (13750,W,"Sr 87",,,,Altamont,UT,84001,t)    ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
14535 West 4000 North Altamont, UT 84001    (14535,W,4000,,N,,Altamont,UT,84001,t)  ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
14535 West 4000 North Altamont, UT 84001    (14535,W,4000,,N,,Altamont,UT,84001,t)  ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
15250 West 4000 North Altamont, UT 84001    (15250,W,4000,,N,,Altamont,UT,84001,t)  ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
15268 West 4000 Altamont, UT 84001  (15268,W,4000,,,,Altamont,UT,84001,t)   ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
153 South 13500 Altamont, UT 84001  (153,S,13500,,,,Altamont,UT,84001,t)    ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
153 South 13500 Altamont, UT 84001  (153,S,13500,,,,Altamont,UT,84001,t)    ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
15487 West 5000 North Altamont, UT 84001    (15487,W,5000,,N,,Altamont,UT,84001,t)  ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
15547 West 4100 North Altamont, UT 84001    (15547,W,4100,,N,,Altamont,UT,84001,t)  ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
15548 West 4000 North Altamont, UT 84001    (15548,W,4000,,N,,Altamont,UT,84001,t)  ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
15548 West 4000 North Altamont, UT 84001    (15548,W,4000,,N,,Altamont,UT,84001,t)  ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
15548 West 4000 North Altamont, UT 84001    (15548,W,4000,,N,,Altamont,UT,84001,t)  ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
15548 West 4000 North Altamont, UT 84001    (15548,W,4000,,N,,Altamont,UT,84001,t)  ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
15585 West 4000 North Altamont, UT 84001    (15585,W,4000,,N,,Altamont,UT,84001,t)  ("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)
  • As an additional note, all of the data files loaded properly (I looked at the data) and I was able to search and find the correct street names in featnames, the roads in edges and the address range in addr. I also check that the inherits worked. I also did the grant all to schemes. Given the fact that I am getting a result for the zip code, it implies the problem in the geocode function??? I have something that tweaks it he wrong why and just not sure what else to check. – user88574 Dec 22 '16 at 1:12
1

The geocode() function is having trouble distinguishing between the elements your are passing so it returns the zipcode because that is the best it can figure out. It will produce better results when you separate each element (street, city, state, zip, etc) by commas rather than spaces. Try this statement:

    SELECT allpgq_ut.addr || ',' || allpgq_ut.city || ','|| allpgq_ut.st || ',' || allpgq_ut.zip As geo,
        normalize_address(allpgq_ut.addr || ',' || allpgq_ut.city || ','|| allpgq_ut.st || ',' || allpgq_ut.zip) As geo2,
        geocode(allpgq_ut.addr || ',' || allpgq_ut.city || ','|| allpgq_ut.st || ',' || allpgq_ut.zip,1) As geo3
    FROM allpgq_ut 
    ORDER BY allpgq_ut.zip, allpgq_ut.addr
    LIMIT 20

UPDATE

The following statements identify how commas help segregate the incoming data to produce different results using the data you provided:

SELECT geocode('1304 N 15000 Altamont,UT 84001') limit 1 -- top result is '("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)'
SELECT geocode('1304 N 15000,Altamont,UT,84001') limit 1 -- top reust is '("(,,15000,,W,,Altamont,UT,84001,t)",0101000020AD1000000826202499915BC0CDD0A2CD0F294440,9)'

SELECT geocode('14535 West 4000 North Altamont,UT 84001') limit 1 -- top result is '("(,,,,,,Altamont,UT,84001,t)",0101000020AD100000724EABF777925BC0367B8298E92D4440,100)'
SELECT geocode('14535 West 4000,North Altamont,UT,84001') limit 1 -- top result is '("(98,,4000,,N,,Altamont,UT,84001,t)",0101000020AD100000F81981E55C925BC060B22CC9FF2D4440,20)'
  • Thanks for your suggestion. I tried this but it did not solve my problem. – user88574 Jan 20 '17 at 5:16
  • @user88574 I added more clarification to the answer. – kttii Jan 20 '17 at 15:04

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.