PostGIS geocoding from stdaddr?

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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;

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply