PostGIS geocoding from stdaddr?

Question

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.

Answer 1

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;