Normalize_Address — Given a textual street address, returns a composite
type that has road suffix, prefix and type standardized, street, streetname etc. broken into separate fields. This function
will work with just the lookup data packaged with the tiger_geocoder (no need for tiger census data).
Given a textual street address, returns a composite
type that has road suffix, prefix and type standardized, street, streetname etc. broken into separate fields. This is the first step in the geocoding process to
get all addresses into normalized postal form. No other data is required aside from what is packaged with the geocoder.
This function just uses the various direction/state/suffix lookup tables preloaded with the tiger_geocoder and located in the
schema, so it doesn't need you to download tiger census data or any other additional data to make use of it.
You may find the need to add more abbreviations or alternative namings to the various lookup tables in the
It uses various control lookup tables located in
schema to normalize the input address.
Fields in the
type object returned by this function in this order where () indicates a field required by the geocoder,  indicates an optional field:
(address) [predirAbbrev] (streetName) [streetTypeAbbrev] [postdirAbbrev] [internal] [location] [stateAbbrev] [zip] [parsed] [zip4] [address_alphanumeric]
Enhanced: 2.4.0 norm_addy object includes additional fields zip4 and address_alphanumeric.
addressis an integer: The street number
predirAbbrevis varchar: Directional prefix of road such as N, S, E, W etc. These are controlled using the
streetTypeAbbrevvarchar abbreviated version of street type: e.g. St, Ave, Cir. These are controlled using the
postdirAbbrevvarchar abbreviated directional suffice of road N, S, E, W etc. These are controlled using the
internalvarchar internal address such as an apartment or suite number.
locationvarchar usually a city or governing province.
stateAbbrevvarchar two character US State. e.g MA, NY, MI. These are controlled by the
zipvarchar 5-digit zipcode. e.g. 02109.
parsedboolean - denotes if addess was formed from normalize process. The normalize_address function sets this to true before returning the address.
zip4last 4 digits of a 9 digit zip code. Availability: PostGIS 2.4.0.
address_alphanumericFull street number even if it has alpha characters like 17R. Parsing of this is better using Pagc_Normalize_Address function. Availability: PostGIS 2.4.0.
Output select fields. Use Pprint_Addy if you want a pretty textual output.
SELECT address As orig, (g.na).streetname, (g.na).streettypeabbrev FROM (SELECT address, normalize_address(address) As na FROM addresses_to_geocode) As g; orig | streetname | streettypeabbrev -----------------------------------------------------+---------------+------------------ 28 Capen Street, Medford, MA | Capen | St 124 Mount Auburn St, Cambridge, Massachusetts 02138 | Mount Auburn | St 950 Main Street, Worcester, MA 01610 | Main | St 529 Main Street, Boston MA, 02129 | Main | St 77 Massachusetts Avenue, Cambridge, MA 02139 | Massachusetts | Ave 25 Wizard of Oz, Walaford, KS 99912323 | Wizard of Oz |