PostGIS provides many different methods of address standardization

Some newer methods return stdaddr,

  • standardize_address (which requires explicit lex, gaz, and rules)

Others return norm_addy

  • pagc_normalize_address (which provides default pagc_lex, pagc_gaz, and pagc_rules) to standardize_address
  • normalize_address (which is the original)

However, geocode() only takes norm_addy, how do I cast a stdaddr to norm_addy. I intend to cache my stdaddr on a table and I'd like to have a quick and fast way to finish the geocode.

up vote 0 down vote accepted

From this example, and from the code shown in \df+ pagc_normalize_address I was able to write this CAST.

CREATE OR REPLACE FUNCTION stdaddr2normaddy(addr stdaddr) RETURNS norm_addy AS $body$
  SELECT (
    (addr).house_num,  --address         
    null,              --predirabbrev    
    (addr).name,       --streetname      
    (addr).suftype,    --streettypeabbrev
    null,              --postdirabbrev   
    (addr).unit,       --internal        
    (addr).city,       --location        
    (addr).state,      --stateabbrev     
    (addr).postcode,   --zip             
    true               --parsed          
  )::norm_addy;
$body$ LANGUAGE sql IMMUTABLE;

CREATE CAST (stdaddr AS norm_addy)
  WITH FUNCTION stdaddr2normaddy(stdaddr)
  AS IMPLICIT;

Now you can test it out...

SELECT x.* FROM pagc_normalize_address(
  '3311 My Street Dr., Kingwood Texas, 77345'
) AS x;

SELECT (g.x).*
FROM (                                              
  SELECT x::norm_addy
  FROM standardize_address(
    'tiger.pagc_lex',
    'tiger.pagc_gaz',
    'tiger.pagc_rules',
    '3311 My Street Dr.',
    'Kingwood Texas, 77345'
  ) AS x
) AS g;

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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