10. 地理编码处理功能参考

10.1. Tiger(地理编码和参考系统的拓扑集成) Geocoder

Tiger(地理编码和参考系统的拓扑集成) Geocoder（即：postgis_tiger_geocoder）包含 4 个组件： 数据加载函数集、地址标准化器、地址编码器和地址逆编码器。 它依赖 fuzzystrmatch、address_standardizer、address_standardizer_data_us 扩展的支持来运行。 安装上述扩展后，数据库中将会预置一批地理编码数据和人口数据。

KGIS 还支持其他2个开源的地理编码器，它们与 Tiger Geocoder 的不同之处，在于支持多国地理编码。

  • Nominatim 使用 OpenStreetMap 分类格式数据。它需要用 osm2pgsql 来加载数据，并且需要数据库环境有KingbaseES和 KGIS 插件。它被打包成一个web服务接口并且被设计成一个服务调用。和tiger地理编码器类似的是，它有一个地理转码和地理逆转码组件。从它的官方文档来看，关于它是否有一个像tiger 地理编码这样的纯SQL接口问题，或者在web接口中是否有一个比较好的逻辑处理，有些含糊不清。

  • GIS Graphy 同样可以利用 KGIS系统环境，并且和 Nominatim 一样使用 OpenStreetMap (OSM) 数据，并且使用加载工具来加载 OSM 数据。和 Nominatim 类似的是支持的地理编码不止是美国的。它以一个web服务的方式运行，并且依赖环境Java 1.5, Servlet应用, Solr. GisGraphy是跨平台的，除了其他灵巧的特性外，它也有一个逆地理转码组件。

10.1.1. Drop_Indexes_Generate_Script

Drop_Indexes_Generate_Script —这个函数会生成一个脚本，用于drop掉所有tiger schema和用户指定的schema下的非primary key和非唯一性索引之外的索引。如果没有指定schema，那么默认的schema是tiger_data。

用法

text Drop_Indexes_Generate_Script(text param_schema=tiger_data);

描述

这个函数会生成一个脚本，用于drop掉所有tiger schema和用户指定的schema下的非primary key和非唯一性索引之外的索引。

如果没有指定schema，那么默认的schema是tiger_data。

这个对于最小化索引膨胀（索引膨胀会误导查询优化器）或者减少不必要的空间很有用。这个函数可以和函数Install_Missing_Indexes一起使用，用于只添加地理编码所需的索引。

样例

SELECT drop_indexes_generate_script() As actionsql;

actionsql
---------------------------------------------------------
DROP INDEX tiger.idx_tiger_countysub_lookup_lower_name;
DROP INDEX tiger.idx_tiger_edges_countyfp;
DROP INDEX tiger.idx_tiger_faces_countyfp;
DROP INDEX tiger.tiger_place_the_geom_gist;
DROP INDEX tiger.tiger_edges_the_geom_gist;
DROP INDEX tiger.tiger_state_the_geom_gist;
DROP INDEX tiger.idx_tiger_addr_least_address;
DROP INDEX tiger.idx_tiger_addr_tlid;
DROP INDEX tiger.idx_tiger_addr_zip;
DROP INDEX tiger.idx_tiger_county_countyfp;
DROP INDEX tiger.idx_tiger_county_lookup_lower_name;
DROP INDEX tiger.idx_tiger_county_lookup_snd_name;
DROP INDEX tiger.idx_tiger_county_lower_name;
DROP INDEX tiger.idx_tiger_county_snd_name;
DROP INDEX tiger.idx_tiger_county_the_geom_gist;
DROP INDEX tiger.idx_tiger_countysub_lookup_snd_name;
DROP INDEX tiger.idx_tiger_cousub_countyfp;
DROP INDEX tiger.idx_tiger_cousub_cousubfp;
DROP INDEX tiger.idx_tiger_cousub_lower_name;
DROP INDEX tiger.idx_tiger_cousub_snd_name;
DROP INDEX tiger.idx_tiger_cousub_the_geom_gist;
DROP INDEX tiger_data.idx_tiger_data_ma_addr_least_address;
DROP INDEX tiger_data.idx_tiger_data_ma_addr_tlid;
DROP INDEX tiger_data.idx_tiger_data_ma_addr_zip;
DROP INDEX tiger_data.idx_tiger_data_ma_county_countyfp;
DROP INDEX tiger_data.idx_tiger_data_ma_county_lookup_lower_name;
DROP INDEX tiger_data.idx_tiger_data_ma_county_lookup_snd_name;
DROP INDEX tiger_data.idx_tiger_data_ma_county_lower_name;
DROP INDEX tiger_data.idx_tiger_data_ma_county_snd_name;
(1 row)

请参考

Install_Missing_Indexes, Missing_Indexes_Generate_Script

10.1.2. Drop_Nation_Tables_Generate_Script

Drop_Nation_Tables_Generate_Script —生成一个SQL脚本，这个脚本用于drop掉指定schema的所有表。生成的SQL脚本drop的表从county_all开始drop。

用法

text Drop_Nation_Tables_Generate_Script(text param_schema=tiger_data);

描述

生成一个SQL脚本，这个脚本用于drop掉指定schema的所有表。生成的SQL脚本drop的表从county_all开始drop。 如果想把tiger_2010数据升级到tiger_2011数据的话，这个很有用。

样例

假设数据库中已存在 tiger 数据，那么执行 Drop_Nation_Tables_Generate_Script 函数会输出类似如下内容：

SELECT drop_nation_tables_generate_script();
DROP_NATION_TABLES_GENERATE_SCRIPT
-------------------------------------------------
DROP TABLE tiger_data.county_all;
DROP TABLE tiger_data.county_all_lookup;
DROP TABLE tiger_data.state_all;
DROP TABLE tiger_data.ma_county;
DROP TABLE tiger_data.ma_state;
(1 row)

请参考

Loader_Generate_Nation_Script

10.1.3. Drop_State_Tables_Generate_Script

Drop_State_Tables_Generate_Script —生成一个SQL脚本，这个脚本可以drop掉一个指定schema下的所有以某个州缩写字母作为前缀的所有表。如果没有指定schema的话，默认的schema是tiger_data

用法

text Drop_State_Tables_Generate_Script(text param_state, text param_schema=tiger_data);

描述

生成一个SQL脚本，这个脚本可以drop掉一个指定schema下的所有以某个州缩写字母作为前缀的所有表。如果没有指定schema的话， 默认的schema是tiger_data。这个函数是很有用的，比如在加载数据出错后，需要再次加载数据前，需要把以某个州开头的表全部drop掉，那么就可以使用这个函数。

样例

假设数据库中已存在 tiger 数据，那么执行 Drop_Nation_Tables_Generate_Script 函数会输出类似如下内容：

SELECT drop_state_tables_generate_script('PA');
DROP_STATE_TABLES_GENERATE_SCRIPT
-------------------------------------------------
DROP TABLE tiger_data.pa_addr;
DROP TABLE tiger_data.pa_county;
DROP TABLE tiger_data.pa_county_lookup;
DROP TABLE tiger_data.pa_cousub;
DROP TABLE tiger_data.pa_edges;
DROP TABLE tiger_data.pa_faces;
DROP TABLE tiger_data.pa_featnames;
DROP TABLE tiger_data.pa_place;
DROP TABLE tiger_data.pa_state;
DROP TABLE tiger_data.pa_zip_lookup_base;
DROP TABLE tiger_data.pa_zip_state;
DROP TABLE tiger_data.pa_zip_state_loc;
(1 row)

请参考

Loader_Generate_Script

10.1.4. Geocode

Geocode —获取一个字符串地址（或者其他标准化的地址），然后输出一个可能的地址集合，这些地址都包含一个point几何对象（以NAD83 参考系下的经纬度形式）和一个标准化地址以及一个地址匹配程度的评估值rating，rating值越低表示这个输出地址越匹配输入的地址。输出结果按照rating升序排列（即最匹配的放在前面）。这个函数可以设置一个最大的输出结果记录数，默认是10条记录和限制区域参数restrict_region（默认为NULL）

用法

setof record geocode(varchar address, integer max_results=10, geometry restrict_region=NULL, norm_addy OUT addy, geom-etry OUT geomout, integer OUT rating);
setof record geocode(norm_addy in_addy, integer max_results=10, geometry restrict_region=NULL, norm_addy OUT addy,geometry OUT geomout, integer OUT rating);

描述

获取一个字符串地址（或者其他标准化的地址），然后输出一个可能的地址集合，这些地址都包含一个point几何对象（以NAD83参考系下的经纬度形式）和一个标准化地址（addy）以及一个地址匹配程度的评估值rating，rating值越低表示这个输出地址越匹配输入的地址。输出结果按照rating升序排列（即最匹配的放在前面）。

这个函数会使用Tiger 地理数据 (edges,faces,addr), KingbaseES的 fuzzy字符串匹配(soundex,levenshtein) 和KGIS的线性插值函数来沿着Tiger的边进行地址插值匹配。输出结果字段rating值越大，输出地址和输入的地址越不匹配。被解码的点默认最多从街道地址的中心线偏离10米。

样例 : 基本用法

SELECT g.rating, ST_X(g.geomout) As lon, ST_Y(g.geomout) As lat,
(addy).address As stno, (addy).streetname As street,
(addy).streettypeabbrev As styp, (addy).location As city,
(addy).stateabbrev As st,(addy).zip
FROM geocode('75 State Street, Boston MA 02109') As g;

rating|  lon            |   lat          |stno|street|styp|city  | st |zip
------+-----------------+----------------+----+------+----+------+----+-------
0     |-71.0556722990239|42.3589914927049| 75 |State | St |Boston| MA | 02109

Even if zip is not passed in the geocoder can guess (took about 122-150 ms)
SELECT g.rating, ST_AsText(ST_SnapToGrid(g.geomout,0.00001)) As wktlonlat,
(addy).address As stno, (addy).streetname As street,
(addy).streettypeabbrev As styp, (addy).location As city,
(addy).stateabbrev As st,(addy).zip
FROM geocode('226 Hanover Street, Boston, MA',1) As g;

rating|  wktlonlat              | stno |street |styp|city  | st |zip
------+-------------------------+------+-------+----+------+----+-------
1     |POINT(-71.05528 42.36316)| 226  |Hanover| St |Boston| MA |02113

Can handle misspellings and provides more than one possible solution with ratings and takes longer (500ms).
SELECT g.rating, ST_AsText(ST_SnapToGrid(g.geomout,0.00001)) As wktlonlat,
(addy).address As stno, (addy).streetname As street,
(addy).streettypeabbrev As styp, (addy).location As city,
(addy).stateabbrev As st,(addy).zip
FROM geocode('31 - 37 Stewart Street, Boston, MA 02116') As g;

rating  |wktlonlat                  | stno | street | styp |city    | st |zip
--------+---------------------------+------+--------+------+--------+----+-------
70      | POINT(-71.06459 42.35113) |31    | Stuart | St   | Boston | MA | 02116

Using to do a batch geocode of addresses. Easiest is to set max_results=1.
Only process those not yet geocoded (have norating).
CREATE TABLE addresses_to_geocode(addid serial PRIMARY KEY, address text,
lon numeric, lat numeric, new_address text, rating integer);
INSERT INTO addresses_to_geocode(address) VALUES ('529 Main Street, Boston MA, 02129'),
('77 Massachusetts Avenue, Cambridge, MA 02139'),('25 Wizard of Oz, Walaford, KS 99912323'),
('26 Capen Street, Medford, MA'),
('124 Mount Auburn St, Cambridge, Massachusetts 02138'),('950 Main Street, Worcester, MA 01610');

-- only update the first 3 addresses (323-704 ms
--there are caching and shared memory effects so first geocode you do is always slower)
-- for large numbers of addresses you don’t want to update all at once
-- since the whole geocode must commit at once
-- For this example we rejoin with LEFT JOIN
-- and set to rating to -1 rating if no match
-- to ensure we don’t regeocode a bad address

UPDATE addresses_to_geocode
SET (rating, new_address, lon, lat)
= ( COALESCE((g.geo).rating,-1), pprint_addy((g.geo).addy),
ST_X((g.geo).geomout)::numeric(8,5), ST_Y((g.geo).geomout)::numeric(8,5) )
FROM (SELECT addid
FROM addresses_to_geocode
WHERE rating IS NULL ORDER BY addid LIMIT 3) As a LEFT JOIN (SELECT addid, (geocode(address,1)) As geo FROM addresses_to_geocode As ag
WHERE ag.rating IS NULL ORDER BY addid LIMIT 3) As g ON a.addid =
g.addid
WHERE a.addid = addresses_to_geocode.addid;
result
-----
Query returned successfully: 3 rows affected, 480 ms execution time.
SELECT * FROM addresses_to_geocode WHERE rating is not null;

addid |       address                               | lon       | lat      |new_address                               | rating
------+---------------------------------------------+-----------+----------+------------------------------------------+---------
1     | 529 Main Street, Boston MA, 02129           | -71.07181 | 42.38359 | 529 Main St, Boston, MA 02129            | 0
2     | 77 Massachusetts Avenue, Cambridge, MA02139 | -71.09428 | 42.35988 | 77 Massachusetts Ave, Cambridge, MA 02139| 0
3     | 25 Wizard of Oz, Walaford, KS 99912323      |           |          |                                          | -1

样例 : 使用 Geometry filter

SELECT g.rating, ST_AsText(ST_SnapToGrid(g.geomout,0.00001)) As wktlonlat, (addy).address As stno, (addy).streetname As street,
(addy).streettypeabbrev As styp,
(addy).location As city, (addy).stateabbrev As st,(addy).zip
FROM geocode('100 Federal Street, MA', 3, (SELECT ST_Union(the_geom)
FROM place WHERE statefp = '25' AND name = 'Lynn')::geometry
) As g;

rating  |wktlonlat                 | stno | street  | styp | city | st |zip
--------+--------------------------+------+---------+------+------+----+-------
8       | POINT(-70.96796 42.4659) | 100  | Federal | St   | Lynn | MA |01905

Total query runtime: 245 ms.

请参考

Normalize_Address, Pprint_Addy, ST_AsText, ST_SnapToGrid, ST_X, ST_Y

10.1.5. Geocode_Intersection

Geocode_Intersection — 输入参数是两条相交的街道以及参数state, city, zip，然后输出两条街道第一个交叉点的可能地址。这些地址都包含一个point几何对象（以NAD83 参考系下的经纬度形式）和一个标准化地址以及一个地址匹配程度的评估值rating，rating值越低表示这个输出地址越匹配输入的地址。输出结果按照rating升序排列（即最匹配的放在前面）。这个函数可以设置一个最大的输出结果记录数，默认是10条记录。

用法

setof record geocode_intersection(text roadway1, text roadway2, text in_state, text in_city, text in_zip, integer max_results=10,norm_addy OUT addy, geometry OUT geomout, integer OUT rating);

描述

输入参数是两条相交的街道以及参数state, city, zip，然后输出两条街道第一个交叉点的可能地址。这些地址都包含一个point几何对象（以NAD83 参考系下的经纬度形式）和一个标准化地址以及一个地址匹配程度的评估值rating，rating值越低表示这个输出地址越匹配输入的地址。输出结果按照rating升序排列（即最匹配的放在前面）。这个函数可以设置一个最大的输出结果记录数，默认是10条记录。这个函数会使用Tiger地理数据 (edges,faces,addr), KingbaseES的fuzzy字符串匹配(soundex,levenshtein) 和KGIS的线性插值函数来沿着Tiger的边进行地址插值匹配。

样例 : 基本用法

SELECT pprint_addy(addy), st_astext(geomout),rating
FROM geocode_intersection( 'Haverford St','Germania St', 'MA', 'Boston', '02130',1);

pprint_addy                       | st_astext                  | rating
----------------------------------+----------------------------+--------
98 Haverford St, Boston, MA 02130 | POINT(-71.101375 42.31376) | 0


SELECT pprint_addy(addy), st_astext(geomout),rating
FROM geocode_intersection('Weld', 'School', 'MA', 'Boston');

pprint_addy                    | st_astext                | rating
-------------------------------+--------------------------+------------------------
98 Weld Ave, Boston, MA 02119 | POINT(-71.099 42.314234)  | 399 Weld
Ave, Boston, MA 02119         | POINT(-71.099 42.314234)  | 3

请参考

Geocode, Pprint_Addy, ST_AsText

10.1.6. Get_Geocode_Setting

Get_Geocode_Setting —返回表tiger.geocode_settings某个setting_name的setting 值。

用法

text Get_Geocode_Setting(text setting_name);

描述

返回表tiger.geocode_settings某个setting_name的setting 值。这个设置允许进行函数的debugging 切换。这样解码地理位置的函数会根据settings控制rating。当前的settings列表如下：

name                        | setting | unit    | category  |short_desc
----------------------------+---------+---------+-----------+-----------------------------------------------------------
debug_geocode_address       | false   | boolean | debug     | outputs debug information in notice log such as queries when geocode_addresss is called if true
debug_geocode_intersection  | false   | boolean | debug     | outputs debug information in notice log such as queries when geocode_intersection is called if true
debug_normalize_address     | false   | boolean | debug     | outputs debug information in notice log such as queries
                            |         |         |           | and intermediate expressions when normalize_address is called if true
debug_reverse_geocode       | false   | boolean | debug     | if true, outputs debug information in notice log such as queries
                                                            | and intermediate expressions when reverse_geocode
reverse_geocode_numbered_roads | 0    | integer | rating    | For state and county highways, 0 - no preference in name
                            |         |         |           | , 1 - prefer the numbered highway name, 2 - prefer local state/county name
use_pagc_address_parser     | false   | boolean | normalize | If set to true, will try to use the pagc_address normalizer instead of tiger built one

样例 ：返回调试设置

SELECT get_geocode_setting('debug_geocode_address') As result;

RESULT
---------
false

请参考

Set_Geocode_Setting

10.1.7. Get_Tract

Get_Tract — 根据geometry几何对象所在的位置，从人口普查表中返回这个位置的人口普查数据。

用法

text get_tract(geometry loc_geom, text output_field=name);

描述

根据geometry几何对象所在的位置，从人口普查表中返回这个位置的人口普查数据。如果没有指定参考系，默认使用NAD 83 经纬度作为参考系。

样例 : 基本用法

如果数据库中已有人口普查数据：

SELECT get_tract(ST_Point(-71.101375, 42.31376) ) As tract_name;

tract_name
---------
1203.01

--this one returns the tiger geoid

SELECT get_tract(ST_Point(-71.101375, 42.31376), 'tract_id' ) As tract_id;

tract_id
---------
25025120301

请参考

Geocode>

10.1.8. Install_Missing_Indexes

Install_Missing_Indexes — 找出在地理编码器中进行连接和过滤操作中涉及的表的key列中，哪些索引缺失了，然后添加这些缺失的索引。

用法

boolean Install_Missing_Indexes();

描述

在schema为tiger和tiger_data中找出在地理编码器中进行连接和过滤操作中涉及的表的key列中，哪些索引缺失了。 然后输出定义这些索引的SQL DDL语句，最后执行这些脚本来添加这些缺失的索引。 这个函数是很有用的函数，它添加让查询更快的新的索引，而这些索引可能在加载数据过程中丢失了。 这个函数可以和函数Missing_Indexes_Generate_Script联合使用，后者生成定义索引的SQL语句，并需要执行这个脚本。 这个函数在升级脚本update_geocode.sql中会有调用到。

样例

SELECT install_missing_indexes();

INSTALL_MISSING_INDEXES
-------------------------
t

请参考

Loader_Generate_Script, Missing_Indexes_Generate_Script

10.1.9. Loader_Generate_Census_Script

Loader_Generate_Census_Script — 生成一个指定操作系统平台的shell脚本。这个脚本会下载指定州的Tiger人口普查数据,bg和tabblock数据等表，然后加载到tiger_data这个schema下。每一个州的脚本单独返回一次记录。

用法

setof text loader_generate_census_script(text[] param_states, text os);

描述

生成一个指定操作系统平台的shell脚本。这个脚本会下载指定州的Tiger人口普查数据,bg和tabblock数据等表，然后加载到tiger_data这个schema下。每一个州的脚本单独返回一次记录。

这个函数在Linux平台上使用unzip和wget来下载数据，然后使用3.4.2 节来加载数据。注意这个函数处理的数据最小是一整个州的数据。它只会处理staging和temp下的目录。它使用下面的控制表来控制处理进程和不同操作系统的shell语法变量。

  1. loader_variables：变量表，记录不同变量的表，比如census site, year, data 和 staging schemas

  2. loader_platform ：不同平台的资料信息以及不同平台的可执行命令的位置。表的记录只带有Linux的平台的信息，可以添加更多平台的信息。

  3. loader_lookuptables 每条记录定义了一种类型的表 (state, county),是否要处理记录中的数据以及怎样加载这种类型的数据，并且定义了每一种类型导入数据，存储数据，添加、移除列、索引和约束的步骤。每个表的名字都以相应的州为前缀，并且从schema名称为tiger的表中继承过来。比如表tiger_data.ma_faces继承于表tiger.faces。

请参考

Loader_Generate_Script

10.1.10. Loader_Generate_Script

Loader_Generate_Script — 生成一个指定操作系统平台的shell脚本。这个脚本会下载指定州的Tiger人口普查数据,block groups（bg）和tabblock数据等表，然后加载到tiger_data这个schema下。每一个州的脚本单独返回一次记录。该函数的最新版本支持Tiger 2010结构化数据，并且会加载到census tract, block groups和 blocks tables表。

用法

setof text loader_generate_script(text[] param_states, text os);

描述

生成一个指定操作系统平台的shell脚本。这个脚本会下载指定州的Tiger人口普查数据,bg和tabblock数据等表，然后加载到tiger_data这个schema下。每一个州的脚本单独返回一次记录。这个函数在Linux平台上使用unzip（在Windows平台上默认使用7-zip）和wget来下载数据，然后使用3.4.2节来加载数据。注意这个函数处理的数据最小是一整个州的数据，但是可以重新下载、覆盖已有的数据。它只会处理staging和temp下的目录。它使用下面的控制表来控制处理进程和不同操作系统的shell语法变量。

  1. loader_variables：变量表，记录不同变量的表，比如census site, year, data 和 staging schemas

  2. loader_platform ：不同平台的资料信息以及不同平台的可执行命令的位置。表的记录只带有Linux的平台的信息，可以添加更多平台的信息。

  3. loader_lookuptables 每条记录定义了一种类型的表 (state, county),是否要处理记录中的数据以及怎样加载这种类型的数据，并且定义了每一种类型导入数据，存储数据，添加、移除列、索引和约束的步骤。每个表的名字都以相应的州为前缀，并且从schema名称为tiger的表中继承过来。比如表tiger_data.ma_faces继承于表tiger.faces。

支持Tiger 2010版本结构化数据，并且会加载如下表的数据： census tract(tract), block groups (bg)和 blocks(tabblocks)。

10.1.11. Loader_Generate_Nation_Script

Loader_Generate_Nation_Script — 生成一个指定平台的shell脚本，用于加载国家和州的数据。

用法

text loader_generate_nation_script(text os);

描述

生成一个指定平台的shell脚本，用于载入表county_all, county_all_lookup, state_all 到schema为tiger_data里面。这些表分别继承schema 为tiger下的表county, county_lookup, state tables 。这个函数在Linux平台上使用unzip和wget来下载数据，然后使用3.4.2 节来加载数据。注意这个函数处理的数据最小是一整个州的数据。它只会处理staging和temp下的目录。它使用下面的控制表来控制处理进程和不同操作系统的shell语法变量。

  1. loader_variables：变量表，记录不同变量的表，比如census site, year, data 和 staging schemas

  2. loader_platform：不同平台的资料信息以及不同平台的可执行命令的位置。表的记录只带有Linux的平台的信息，可以添加更多平台的信息。

  3. loader_lookuptables 每条记录定义了一种类型的表 (state, county),是否要处理记录中的数据以及怎样加载这种类型的数据，并且定义了每一种类型导入数据，存储数据，添加、移除列、索引和约束的步骤。每个表的名字都以相应的州为前缀，并且从schema名称为tiger的表中继承过来。比如表tiger_data.ma_faces继承于表tiger.faces。

注意

注意如果运行的是tiger_2010版本，但想要加载的是数据却是tiger_2011版本，需要在运行这个脚本之前，先运行drop语句 Drop_Nation_Tables_Generate_Script

样例

生成在 Linux/Unix 系统上加载数据的脚本。

SELECT loader_generate_nation_script('sh');

请参考

Loader_Generate_Script

10.1.12. Missing_Indexes_Generate_Script

Missing_Indexes_Generate_Script —找出在地理编码器中进行连接和过滤操作中涉及的表的key列中，哪些索引缺失了。然后输出定义这些索引的SQL DDL语句

用法

text Missing_Indexes_Generate_Script();

描述

在schema为tiger和tiger_data中找出在地理编码器中进行连接和过滤操作中涉及的表的key列中，哪些索引缺失了。 然后输出定义这些索引的SQL DDL语句，最后执行这些脚本来添加这些缺失的索引。 这个函数是很有用的函数，它添加让查询更快的新的索引，而这些索引可能在加载数据过程中丢失了。 因为tiger做了一些修改提升，这个函数会自动适应新的正在使用的索引。 如果这个函数什么也没有输出，这意味着的表已经有了相关的key的索引。

样例

SELECT missing_indexes_generate_script();

-- output: This was run on a database that was created before many corrections were made to the loading script ---
CREATE INDEX idx_tiger_county_countyfp ON tiger.county USING btree(countyfp);
CREATE INDEX idx_tiger_cousub_countyfp ON tiger.cousub USING btree(countyfp);
CREATE INDEX idx_tiger_edges_tfidr ON tiger.edges USING btree(tfidr);
CREATE INDEX idx_tiger_edges_tfidl ON tiger.edges USING btree(tfidl);
CREATE INDEX idx_tiger_zip_lookup_all_zip ON tiger.zip_lookup_all USING btree(zip);
CREATE INDEX idx_tiger_data_ma_county_countyfp ON tiger_data.ma_county USING btree(countyfp);
CREATE INDEX idx_tiger_data_ma_cousub_countyfp ON tiger_data.ma_cousub USING btree(countyfp);
CREATE INDEX idx_tiger_data_ma_edges_countyfp ON tiger_data.ma_edges USING USING
CREATE INDEX idx_tiger_data_ma_faces_countyfp ON tiger_data.ma_faces USING btree(countyfp);

请参考

Loader_Generate_Script, Install_Missing_Indexes

10.1.13. Normalize_Address

Normalize_Address — 根据给出的文本街道地址，返回一个复合的norm_addy类型值，这个值包含路的suffix, prefix 和 typestandardized, street, streetname等等分开的字段。这个函数只处理哪些打包在tiger_geocoder中的数据。

用法

norm_addy normalize_address(varchar in_address);

描述

根据给出的文本街道地址，返回一个复合的norm_addy 类型值，这个值包含路的suffix, prefix 和 typestandardized, street,streetname 等等分开的字段。这个函数只处理哪些打包在tiger_geocoder中的数据。

把所有的地址先标准化成邮政格式的地址，是地理编码处理过程的第一步。这个函数不需要除了解码器以外的其他数据。这个函数只是使用加载在tiger 这个schema下的表tiger_geocoder的不同direction/state/suffix 的数据。因此这个函数不需要去下载tiger的人口普查数据或者其他任何附加数据就可以使用。可能会发现需要在tiger schema下的不同的lookup 表中添加更多的缩写或其他名称。这个函数使用不同的tiger schema下的lookup control表来标准化输入的地址。这个函数返回的norm_addy类型的对象可以有如下顺序： ()表示这是编码必须的一个字段，而[] 表示一个可选的字段：

(address) [predirAbbrev] (streetName) [streetTypeAbbrev] [postdirAbbrev] [internal] [location] [stateAbbrev] [zip]

  1. address是一个整型值，表示街道号。

  2. predirAbbrev varchar类型，类型道路方向的前缀缩写，比如N, S, E, W。这个参数是由方向查找表direction_lookup来控制的。

  3. streetName varchar类型

  4. streetTypeAbbrev varchar类型，表示街道类型的缩写，例如 St, Ave, Cir。这个是由街道类型查找表street_type_lookup来控制的。

  5. postdirAbbrev varchar 类型道路方向的后缀缩写，比如N, S, E, W。这个参数是由方向查找direction_lookup来控制的。

  6. internal varchar类型，内部地址表示一个公寓或者套房号码。

  7. location varchar类型通常是一个城市或者一个省份。

  8. stateAbbrev varchar two character US State. e.g MA, NY, MI. These are controlled by the state_lookup table.

  9. zip varchar类型，表示5位数的邮政编码，比如02109.

  10. parsed boolean类型–表示地址是否是标准化的地址。函数normalize_address在返回地址之前，把这个参数设置为true。

样例

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 Worcester, MA        | Worcester, MA | 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  |

请参考

Geocode, Pprint_Addy

10.1.14. Pagc_Normalize_Address

Pagc_Normalize_Address —根据给出的文本街道地址，返回一个复合的norm_addy类型值，这个值包含路的suffix, prefix 和 typestandardized, street,streetname 等等分开的字段。这个函数只处理哪些打包在tiger_geocoder中的lookuptable表的数据。需要address_standardizer 插件。

用法

norm_addy pagc_normalize_address(varchar in_address);

描述

根据给出的文本街道地址，返回一个复合的norm_addy类型值，这个值包含路的suffix, prefix 和 typestandardized, street,streetname等等分开的字段。把所有的地址先标准化成邮政格式的地址，是地理编码处理过程的第一步。这个函数只处理哪些打包在tiger_geocoder中的数据。

这个函数只是使用tiger schema下的用tiger_geocoder加载的以pagc_为前缀的lookup表。因此这个函数不需要去下载tiger的人口普查数据或者其他任何附加数据就可以使用。可能会发现需要在tiger schema下的不同的lookup表中添加更多的缩写或其他名称。这个函数使用不同的tiger schema下的lookup control表来标准化输入的地址。这个函数返回的norm_addy类型的对象可以有如下顺序： ()表示这是编码必须的一个字段，而[] 表示一个可选的字段：

这个函数版本使用PAGC地址标准化C插件，可以自行去下载。这个插件在类型转换和数据格式化方面有一些轻微的变化，并且有一些比较重要的功能突破。

(address) [predirAbbrev] (streetName) [streetTypeAbbrev] [postdirAbbrev] [internal] [location] [stateAbbrev] [zip]

这原生的address_standardizer 插件返回的standardaddr这时候要比norm_addy功能丰富一些，因为这个插件是支持国际化的地址(包括国家).standardaddr地址等价的字段如下：house_num,predir, name, suftype, sufdir, unit, city, state, postcode

  1. address是一个整型值，表示街道号。

  2. predirAbbrev varchar类型，类型道路方向的前缀缩写，比如N, S, E, W。这个参数是由方向查找表direction_lookup来控制的。

  3. streetName varchar类型

  4. streetTypeAbbrev varchar类型，表示街道类型的缩写，例如 St, Ave, Cir。这个是由街道类型查找表street_type_lookup来控制的。

  5. postdirAbbrev varchar 类型道路方向的后缀缩写，比如N, S, E, W。这个参数是由方向查找表direction_lookup来控制的。

  6. internal varchar类型，内部地址表示一个公寓或者套房号码。

  7. location varchar类型通常是一个城市或者一个省份。

  8. stateAbbrev varchar two character US State. e.g MA, NY, MI. These are controlled by the state_lookup table.

  9. zip varchar类型，表示5位数的邮政编码，比如02109.

  10. parsed boolean类型–表示地址是否是标准化的地址。函数normalize_address在返回地址之前，把这个参数设置为true。

样例

Single call example

SELECT addy.*
FROM pagc_normalize_address('9000 E ROO ST STE 999, Springfield, CO') AS addy;

address|predirabbrev|streetname|streettypeabbrev|postdirabbrev|internal|location|stateabbrev|zip| parsed
-------+------------+----------+----------------+-------------+--------+--------+-----------+---+--------
9000   | E          | ROO      | ST             |            |SUITE 999|SPRINGFIELD | CO    |   | t

Batch call. There are currently speed issues with the way postgis_tiger_geocoder wraps the address_standardizer. These willhopefully be resolved in later editions. To work around them, if you need speed for batch geocoding to call generate a normaddy in batch mode, you are encouraged to directly call the address_standardizer standardize_address function as shown below whichis similar exercise to what we did in Normalize_Address that uses data created in Geocode.

WITH g AS (SELECT address, ROW((sa).house_num, (sa).predir, (sa).name
  , (sa).suftype, (sa).sufdir, (sa).unit , (sa).city, (sa).state, (sa).postcode, true)::
    norm_addy As na
FROM (SELECT address, standardize_address('tiger.pagc_lex' , 'tiger.pagc_gaz' , 'tiger.pagc_rules', address) As sa
FROM addresses_to_geocode) As g)
SELECT address As orig, (g.na).streetname, (g.na).streettypeabbrev
FROM g;

orig                                                 | streetname    |streettypeabbrev
-----------------------------------------------------+---------------+------------------
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     |
26 Capen Street, Medford, MA                         | CAPEN         | ST
124 Mount Auburn St,Cambridge,Massachusetts 02138    | MOUNT AUBURN  | ST
950 Main Street, Worcester, MA 01610                 | MAIN          | ST

请参考

Normalize_Address, Geocode

10.1.15. Pprint_Addy

Pprint_Addy — 根据给定的复合类型norm_addy对象，返回这个地址的可读性更好的展示。通常和函数normalize_address一起使用。

用法

varchar pprint_addy(norm_addy in_addy);

描述

根据给定的复合类型norm_addy对象，返回这个地址的可读性更好的展示。通常和函数normalize_address一起使用。除了打包在编码器以外的数据外，不需要其他数据。这个函数通常和函数Normalize_Address一起使用。

样例

Pretty print a single address

SELECT pprint_addy(normalize_address('202 East Fremont Street, Las Vegas, Nevada 89101'))
As pretty_address;

         pretty_address
---------------------------------------
202 E Fremont St, Las Vegas, NV 89101

Pretty print address a table of addresses

SELECT address As orig, pprint_addy(normalize_address(address)) As pretty_address
    FROM addresses_to_geocode;

orig                                                 |  pretty_address
-----------------------------------------------------+-----------------------------------------
529 Main Street, Boston MA, 02129                    | 529 Main St, Boston MA, 02129
77 Massachusetts Avenue, Cambridge, MA 02139         | 77 Massachusetts Ave, Cambridge, MA 02139
28 Capen Street, Medford, MA                         | 28 Capen St, Medford, MA
124 Mount Auburn St, Cambridge, Massachusetts 02138  | 124 Mount Auburn St, Cambridge, MA 02138
950 Main Street, Worcester, MA 01610                 | 950 Main St, Worcester, MA 01610

请参考

Normalize_Address

10.1.16. Reverse_Geocode

Reverse_Geocode — 输入参数是一个已知空间参考系下的point几何对象，返回值是一条包含理论上可能地址的数组和交叉街道的数组。如果参数include_strnum_range = true，表示把交叉街道的街道范围也算在内。

用法

record Reverse_Geocode(geometry pt, boolean include_strnum_range=false, geometry[] OUT intpt, norm_addy[] OUT addy,varchar[] OUT street);

描述

输入参数是一个已知空间参考系下的point几何对象，返回值是一条包含理论上可能地址的数组和交叉街道的数组。如果参数include_strnum_range = true，表示把交叉街道的街道范围也算在内。如果没有传递include_strnum_range参数值，则它的默认值是false。输出的地址是按照和目标道路的接近程度倒序排列的，因此第一个地址是最接近正确的。为什么说是理论的地址而不是实际的地址呢，因为Tiger的数据并没有真正的地址，而只是街道范围。因此理论地址只是基于街道范围做的插值地址。比如像我这边地址之一进行插值后，会返回26 Court St. 和 26 Court Sq，虽然没有26 Court Sq这个地址。这是因为这个点可能是2个街道相交的地方，因此逻辑插值的地方在两个街道。该逻辑还假定地址是沿着一条街道，当然是错误的，

因为可以有一个市政建设占用了一个很好的块的街道范围内，其余的建筑物聚集在最后。

注意:这个函数依赖Tiger数据。如果还没有加载覆盖该点的数据，那么会返回都是NULL的一条记录。

返回记录包含的元素如下：

  1. intpt：point类型的数组，是贴金输入点的中心线的点。数量和地址数量一致。

  2. addy：是一个norm_addy类型的数组（标准化地址），这些地址是可能匹配输入点的地址数组，数组的第一个元素是最可能匹配输入点的。一般来说，数组元素应该只有一个，除非这个点恰好是2个或3个街道的交点，或者这个点不在路上或者在路边上。

  3. street ：是一个varchar类型的数组。这些是有交叉的街道(就是有相交的街道，或者街道投影的点)。

样例

Example of a point at the corner of two streets, but closest to one.This is approximate location of MIT: 77 Massachusetts Ave,Cambridge, MA 02139 注意 that although we don’t have 3 streets, KingbaseES will just return null for entries above our upperbound so safe to use. This includes street ranges

SELECT pprint_addy(r.addy[1]) As st1, pprint_addy(r.addy[2]) As st2, pprint_addy(r.addy[3])
  As st3, array_to_string(r.street, ',') As cross_streets
  FROM reverse_geocode(ST_GeomFromText('POINT(-71.093902 42.359446)',4269),true) As r ;

  result
  ------
  st1                                        | st2 | st3 | cross_streets
  -------------------------------------------+-----+-----+---------------------------------------------
  67 Massachusetts Ave, Cambridge, MA 02139  |     |     | 67 - 127 Massachusetts Ave,32 - 88 Vassar St

Here we choose not to include the address ranges for the cross streets and picked a location really really close to a corner of 2streets thus could be known by two different addresses.
SELECT pprint_addy(r.addy[1]) As st1, pprint_addy(r.addy[2]) As st2, pprint_addy(r.addy[3]) As st3, array_to_string(r.street, ',') As cross_str
FROM reverse_geocode(ST_GeomFromText('POINT(-71.06941 42.34225)',4269)) As r;

result
--------
st1                              | st2                             | st3 | cross_str
---------------------------------+---------------------------------+-----+------------------------
5 Bradford St, Boston, MA 02118  | 49 Waltham St, Boston, MA 02118 |     |Waltham St

For this one we reuse our geocoded example from Geocode and we only want the primary address and at most 2 cross streets.

SELECT actual_addr, lon, lat, pprint_addy((rg).addy[1]) As int_addr1,
(rg).street[1] As cross1, (rg).street[2] As cross2FROM (SELECT address
As actual_addr, lon, lat, reverse_geocode( ST_SetSRID(ST_Point(lon,lat),4326) ) As rg
FROM addresses_to_geocode WHERE rating > -1) As foo;

actual_addr                                 |   lon   | lat      | int_addr1                        |   cross1        | cross2
--------------------------------------------+---------+----------+----------------------------------+-----------------+-----------
529 Main Street, Boston MA, 02129           |-71.07181| 42.38359 | 527 Main St,                     |Boston, MA 02129 | Medford St
77 Massachusetts Avenue, Cambridge, MA 02139|-71.09428| 42.35988 | 77 Massachusetts Ave, Cambridge, | Vassar St       |
                                                                     MA 02139
26 Capen Street, Medford, MA                |-71.12377| 42.41101 | 9 Edison Ave, Medford, MA 02155  | Capen St        | Tesla Ave
124 Mount Auburn St, Cambridge,             |-71.12304| 42.37328 | 3 University Rd, Cambridge,      | Mount Auburn St |
  Massachusetts 02138                                                 MA 02138
950 Main Street, Worcester, MA 01610        |-71.82368| 42.24956 | 3 Maywood St Worcester, MA 01603 | Main St         | Maywood P

请参考

Pprint_Addy, Geocode

10.1.17. Topology_Load_Tiger

Topology_Load_Tiger —把tiger数据定义的区域加载到一个KGIS Topology拓扑结构中，然后把tiger数据转换成拓扑结构所在的空间参考系中以及再把point分割到拓扑结构的tolerance容差精度。

用法

text Topology_Load_Tiger(varchar topo_name, varchar region_type, varchar region_id);

描述

把tiger数据定义的区域加载到一个KGIS Topology拓扑结构中，然后把faces, nodes 和 edge等对象转换到目标拓扑结构所在的空间参考系中，同时把点分割到目标拓扑的tolerance精度。新创建的faces,nodes, edges保持和原始的tiger 数据的faces, nodes,edges一样的对象ID，以便新数据集将来更容易和原始的tiger数据保持一致性。这个函数的返回值是处理过程的概述。这个函数是很有用的，比如在想重新划分需要的新形成的多边形或者想让生成多边形没有交叠。

注意

这个函数依赖于Tiger数据以及KGIS拓扑模块的安装。想要更多新，请参考第8章。如果还没有加载覆盖感兴趣点的数据，那么将不会创建任何拓扑结构记录。如果使用拓扑函数没能创建成功一个拓扑结构时候，这个函数同样也会执行失败。

注意

大多数的拓扑验证失败都是因为转换后的边上的点没有排齐或者是有交叠导致的tolerance容差问题。如果遇到拓扑验证失败情况，想要修正这种情况，需要提高或者降低tolerance的精度。

需要的参数：

  1. topo_name ：已经存在的KGIS拓扑结构名称，是要加载数据的目标拓扑。

  2. region_type：缓冲区类型，当前只支持地方和区县，的开发计划是支持更多的类型。定义缓冲区的表有tiger.place,tiger.county等等。

  3. region_id：这个与TIGER称之为geoid的等价。这是表中区域的唯一ID。对于place类型，这个字段的是只是表tiger.place的plcidfp字段值，对于county类型，这个值是表tiger.county的cntyidfp字段值。

样例 : Boston, Massachusetts Topology

Create a topology for Boston, Massachusetts in Mass State Plane Feet
(2249) with tolerance 0.25 feet and then load in Bostoncity tiger faces,
edges, nodes.

SELECT topology.CreateTopology('topo_boston', 2249, 0.25);
createtopology
--------------
15

SELECT tiger.topology_load_tiger('topo_boston', 'place', '2507000');
-- topology_loader_tiger --
29722 edges holding in temporary. 11108 faces added. 1875 edges of faces added. 20576
nodes added.
19962 nodes contained in a face. 0 edge start end corrected. 31597 edges added.

-- 41 ms --
SELECT topology.TopologySummary('topo_boston');
-- topologysummary--
Topology topo_boston (15), SRID 2249, precision 0.25
20576 nodes, 31597 edges, 11109 faces, 0 topogeoms in 0 layers

-- 28,797 ms to validate yeh returned no errors --
SELECT * FROM

  topology.ValidateTopology('topo_boston');

error              | id1      | id2
-------------------+----------+-----------

样例 : Suffolk, Massachusetts Topology

Create a topology for Suffolk, Massachusetts in Mass State Plane Meters
(26986) with tolerance 0.25 meters and then load inSuffolk county tiger
faces, edges, nodes.

SELECT topology.CreateTopology('topo_suffolk', 26986, 0.25);

-- must have been warmed up after loading boston
SELECT tiger.topology_load_tiger('topo_suffolk', 'county', '25025');
-- topology_loader_tiger --
36003 edges holding in temporary. 13518 faces added. 2172 edges of faces added.
24761 nodes added. 24075 nodes contained in a face. 0 edge start end corrected. 38175 edges added.
-- 31 ms --
SELECT topology.TopologySummary('topo_suffolk');
-- topologysummary--
Topology topo_suffolk (14), SRID 26986, precision 0.25
24761 nodes, 38175 edges, 13519 faces, 0 topogeoms in 0 layers

-- 33,606 ms to validate --
SELECT * FROM
      topology.ValidateTopology('topo_suffolk');

error              | id1      | id2
-------------------+----------+-----------
coincident nodes   | 81045651 | 81064553
edge crosses node  | 81045651 | 85737793
edge crosses node  | 81045651 | 85742215
edge crosses node  | 81045651 | 620628939
edge crosses node  | 81064553 | 85697815
edge crosses node  | 81064553 | 85728168
edge crosses node  | 81064553 | 85733413

请参考

CreateTopology, CreateTopoGeom, TopologySummary, ValidateTopology

10.1.18. Set_Geocode_Setting

Set_Geocode_Setting —设置影响geocode函数的行为的setting参数。

用法

text Set_Geocode_Setting(text setting_name, text setting_value);

描述

使用存储在tiger.geocode_settings表中的值为该函数的setting相关的参数指定值。通过settings可以切换函数的debugging类型，然后函数的转码的rating值就可以做到控制。settings列表参考Get_Geocode_Setting.

样例 ：返回调试设置

If you run Geocode when this function is true, the NOTICE log will output timing and queries.

SELECT set_geocode_setting('debug_geocode_address', 'true') As result;

RESULT
---------
true

请参考

Get_Geocode_Setting

# java # servlet # 开发语言
Logo
亚马逊云科技技术品牌专区

更多推荐

cover

企业物联网平台如何选择？

avatar 亚马逊云科技技术品牌专区

STM32节点移植lorawan协议连接腾讯云物联网开发平台（IoT Explorer）

STM32移植lorawan协议连接腾讯云物联网开发平台（IoT Explorer）前言前言在移植协议之前，先给大家科普一下Lora 和 lorawan 的区别。LoRa 是LPWAN通信技术中的一种，是美国Semtech公司采用和推广的一种基于扩频技术的超远距离无线传输方案。这一方案改变了以往关于传输距离与功耗的折衷考虑方式为用户提供一种简单的能实现远距离、长电池寿命、大容量的系统，进而扩...

avatar 亚马逊云科技技术品牌专区

从华为的MQTT到TdEngineRPC，解读物联网时代的分布式

今天中秋节，笔者首先祝各位读者们中秋快乐，之所以在今天这个团圆节来谈分布式的话题，就是要聊聊物联网是如何通过MQTT连接各类终端，如何通过RPC整合各种数据的。下面就通过代码+动图的方式来解读一下华为LiteOS的MQTT与TD的RPC。MQTT协议MQTT是一个客户机服务器发布/订阅消息传输协议。它重量轻、开放、简单、易于实现。这些特性使其非常适合在物联网的低带宽、...

avatar 亚马逊云科技技术品牌专区