PostGIS
Name
standardize_address — Returns an stdaddr form of an input address utilizing lex, gaz, and rule tables.
Synopsis
stdaddr +`*`+standardize_address*(`text `lextab, text
gaztab, text rultab, text address`)`;
stdaddr +`*`+standardize_address*(`text `lextab, text
gaztab, text rultab, text micro, text macro`)`;
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 onceVariant 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 301Using 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)