standardize_address

Description

Returns an stdaddr form of an input address utilizing lex table table name, gaz table , and rules table table names and an address.

Variant 1: Takes an address as a single line.

Variant 2: Takes an address as 2 parts. A micro consisting of standard first line of postal address e.g. house_num street , and a macro consisting of standard postal second line of an address e.g city, state postal_code country .

Availability: 2.2.0

This method needs address_standardizer extension.

Examples

Using address_standardizer_data_us extension

CREATE EXTENSION address_standardizer_data_us; -- only needs to be done once

Variant 1: Single line address. This doesn't work well with non-US addresses

SELECT house_num, name, suftype, city, country, state, unit  FROM standardize_address('us_lex',
			   'us_gaz', 'us_rules', 'One Devonshire Place, PH 301, Boston, MA 02109');

house_num |    name    | suftype |  city  | country |     state     |      unit
----------+------------+---------+--------+---------+---------------+-----------------
1         | DEVONSHIRE | PLACE   | BOSTON | USA     | MASSACHUSETTS | # PENTHOUSE 301

Using tables packaged with tiger geocoder. This example only works if you installed postgis_tiger_geocoder .

SELECT *  FROM standardize_address('tiger.pagc_lex',
         'tiger.pagc_gaz', 'tiger.pagc_rules', 'One Devonshire Place, PH 301, Boston, MA 02109-1234');

Make easier to read we'll dump output using hstore extension CREATE EXTENSION hstore; you need to install

SELECT (each(hstore(p))).*
 FROM standardize_address('tiger.pagc_lex', 'tiger.pagc_gaz',
   'tiger.pagc_rules', 'One Devonshire Place, PH 301, Boston, MA 02109') As p;

    key     |      value
------------+-----------------
 box        |
 city       | BOSTON
 name       | DEVONSHIRE
 qual       |
 unit       | # PENTHOUSE 301
 extra      |
 state      | MA
 predir     |
 sufdir     |
 country    | USA
 pretype    |
 suftype    | PL
 building   |
 postcode   | 02109
 house_num  | 1
 ruralroute |
(16 rows)
			

Variant 2: As a two part Address

SELECT (each(hstore(p))).*
 FROM standardize_address('tiger.pagc_lex', 'tiger.pagc_gaz',
   'tiger.pagc_rules', 'One Devonshire Place, PH 301', 'Boston, MA 02109, US') As p;

    key     |      value
------------+-----------------
 box        |
 city       | BOSTON
 name       | DEVONSHIRE
 qual       |
 unit       | # PENTHOUSE 301
 extra      |
 state      | MA
 predir     |
 sufdir     |
 country    | USA
 pretype    |
 suftype    | PL
 building   |
 postcode   | 02109
 house_num  | 1
 ruralroute |
(16 rows)

See Also

stdaddr , rules table , lex table , gaz table , Pagc_Normalize_Address