standardize_address — 使用lex、gaz和规则表返回输入地址的stdaddr形式。
stdaddr standardize_address(
text lextab, text gaztab, text rultab, text address)
;
stdaddr standardize_address(
text lextab, text gaztab, text rultab, text micro, text macro)
;
返回一个 标准地址 使用输入地址的格式 Lex表 表名、 GAZ表 ,以及 规则表 表名和地址。
变体1:将地址作为单行。
变体2:将地址分为两部分。一个 micro
由标准的第一行邮政地址组成,例如 house_num street
,以及由地址的标准邮政第二行组成的宏,例如 city, state postal_code country
。
可用性:2.2.0
This method needs address_standardizer extension.
使用ADDRESS_STANDIZER_DATA_US扩展
CREATE EXTENSION address_standardizer_data_us; -- only needs to be done once
变体1:单行地址。这对非美国地址不起作用
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
使用与Tiger地理编码器打包的表。此示例仅在安装了 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');
为了便于阅读,我们将使用hstore扩展创建扩展hstore;您需要安装
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)
备选方案2:作为两部分地址
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)