pagc_normalize_address (standardize_address), doesn't convert numbered street name right

[robe]

This isn't so much wrong as, just not pretty 9nd Leo needs it pretty since he uses it to standardize the look of his addresses) and with the TIGER data the way it is, ends up geocoding wrong and often being confused for highway and route numbers.

Here is an example I have in regress, where I prefer the default normalize_address behavior over the address standardizer one.

-- good
test_tiger=# SELECT  address, streetname, streettypeabbrev FROM normalize_address('212 3rd Ave N Suite 560, Minneapolis, MN 55401');
 address | streetname | streettypeabbrev
---------+------------+------------------
     212 | 3rd        | Ave
(1 row)

--bad
test_tiger=# SELECT  address, streetname, streettypeabbrev FROM pagc_normalize_address('212 3rd Ave N Suite 560, Minneapolis, MN 55401');
 address | streetname | streettypeabbrev
---------+------------+------------------
     212 | 3          | AVE
(1 row)

-- bad

test_tiger=# SELECT *
test_tiger-# FROM standardize_address('pagc_lex'
test_tiger(#        , 'pagc_gaz'
test_tiger(#        , 'pagc_rules'
test_tiger(# ,'212 3rd Ave N Suite 560, Minneapolis, MN 55401'  ) ;
 building | house_num | predir | qual | pretype | name | suftype | sufdir | ruralroute | extra |    city     | state | country | postcode | box |   unit
----------+-----------+--------+------+---------+------+---------+--------+------------+-------+-------------+-------+---------+----------+-----+-----------
          | 212       |        |      |         | 3    | AVE     | N      |            |       | MINNEAPOLIS | MN    | USA     | 55401    |     | SUITE 560
(1 row)

--bad
CREATE EXTENSION address_standardizer_data_us;
SELECT *
FROM standardize_address('us_lex'
        , 'us_gaz'
        , 'us_rules'
 ,'212 3rd Ave N Suite 560, Minneapolis, MN 55401'  ) ;
 building | house_num | predir | qual | pretype | name | suftype | sufdir | ruralroute | extra |    city     |   state   | country | postcode | box |   unit
----------+-----------+--------+------+---------+------+---------+--------+------------+-------+-------------+-----------+---------+----------+-----+-----------
          | 212       |        |      |         | 3    | AVENUE  | NORTH  |            |       | MINNEAPOLIS | MINNESOTA | USA     | 55401    |     | SUITE 560
(1 row)

I have this slated for 2.3, but hoping to fix for 2.2 and will push back when I do. I think this can be corrected by updated the pagc_* tables packaged with tiger. I'm hesitant to make the same change to the address_standardizer_data_us tables since I wanted to keep those compatible with what Steve had.

[robe]

Fixed at r13991 and added some additional regress tests. so now

SELECT *
FROM standardize_address('pagc_lex'
       , 'pagc_gaz'
     , 'pagc_rules'
 ,'212 3rd Ave N Suite 560, Minneapolis, MN 55401'  ) ;

 building | house_num | predir | qual | pretype | name | suftype | sufdir | ruralroute | extra |    city     | state | country | postcode | box |   unit
----------+-----------+--------+------+---------+------+---------+--------+------------+-------+-------------+-------+---------+----------+-----+-----------
          | 212       |        |      |         | 3RD  | AVE     | N      |            |       | MINNEAPOLIS | MN    | USA     | 55401    |     | SUITE 560
(1 row)

and still handles word numbered street names:

 SELECT * FROM pagc_normalize_address('3937 Forty Third AVE S, MINNEAPOLIS, MN 55406');

becomes:

 address | predirabbrev | streetname | streettypeabbrev | postdirabbrev | internal |  location   | stateabbrev |  zip  | parsed
---------+--------------+------------+------------------+---------------+----------+-------------+-------------+-------+--------
    3937 |              | 43RD       | AVE              | S             |          | MINNEAPOLIS | MN          | 55406 | t
(1 row)