Postgres GIS Address Standardizer

This is a question address canonicalization and parsing. Essentially what you’re talking about is handled through a gazetteer (geographical rule set). There are two ways to do this right,

address_standardizer from the PostGIS project and certainly better if you’re only using United States addresses.
pgsql-postal may be a better method for international addresses.

I’ll show the address standardizer version for the address,

And, then we can use it like this.

<span class="kwd">SELECT</span> <span class="pun">*</span> <span class="kwd">FROM</span><span class="pln"> standardize_address</span><span class="pun">(</span><span class="str">'us_lex'</span><span class="pun">,</span>
   <span class="str">'us_gaz'</span><span class="pun">,</span> <span class="str">'us_rules'</span><span class="pun">,</span> <span class="str">'10511 Homestead Rd, Pahrump, NV 89061'</span><span class="pun">);</span><span class="pln">
 building </span><span class="pun">|</span><span class="pln"> house_num </span><span class="pun">|</span><span class="pln"> predir </span><span class="pun">|</span><span class="pln"> qual </span><span class="pun">|</span><span class="pln"> pretype </span><span class="pun">|</span><span class="pln">   name    </span><span class="pun">|</span><span class="pln"> suftype </span><span class="pun">|</span><span class="pln"> sufdir </span><span class="pun">|</span><span class="pln"> ruralroute </span><span class="pun">|</span><span class="pln"> extra </span><span class="pun">|</span><span class="pln">  city   </span><span class="pun">|</span><span class="pln"> state  </span><span class="pun">|</span><span class="pln"> country </span><span class="pun">|</span><span class="pln"> postcode </span><span class="pun">|</span><span class="pln"> box </span><span class="pun">|</span><span class="pln"> unit 
</span><span class="com">----------+-----------+--------+------+---------+-----------+---------+--------+------------+-------+---------+--------+---------+----------+-----+------</span>
          <span class="pun">|</span> <span class="lit">10511</span>     <span class="pun">|</span>        <span class="pun">|</span>      <span class="pun">|</span>         <span class="pun">|</span><span class="pln"> HOMESTEAD </span><span class="pun">|</span><span class="pln"> ROAD    </span><span class="pun">|</span>        <span class="pun">|</span>            <span class="pun">|</span>       <span class="pun">|</span><span class="pln"> PAHRUMP </span><span class="pun">|</span><span class="pln"> NEVADA </span><span class="pun">|</span><span class="pln"> USA     </span><span class="pun">|</span> <span class="lit">89061</span>    <span class="pun">|</span>     <span class="pun">|</span> 
<span class="pun">(</span><span class="lit">1</span> <span class="kwd">row</span><span class="pun">)</span>