Name

Geocode — 将地址作为字符串(或其他标准化地址)输入,并输出一组可能的位置,其中包括 NAD 83经纬度中的点几何图形、每个位置的标准化地址以及评级。 评级越低,匹配的可能性越大。 结果首先按最低评分排序。 可以选择传入最大结果数,默认为10,以及restrict_region(默认为NULL)

Synopsis

setof record geocode(varchar address, integer max_results=10, geometry restrict_region=NULL, norm_addy OUT addy, geometry 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);

描述

将地址作为字符串(或已经标准化的地址)并输出一组可能的位置,其中包括 NAD 83 经纬度中的点几何形状、每个位置的标准化地址 (addy) 以及评级。 评级越低,匹配的可能性越大。 结果首先按最低评分排序。 使用 Tiger 数据(edges、faces、addr)、PostgreSQL 模糊字符串匹配(soundex、levenshtein)和 PostGIS 线插值函数沿 Tiger 边缘插值地址。 评级越高,地理编码正确的可能性就越小。 地理编码点默认为从中心线到街道地址所在一侧 (L/R) 的偏移 10 米。

增强功能:2.0.0支持Tiger 2010结构化数据并修改了一些逻辑以提高地理编码的速度和准确性,并将点从中心线偏移到街道地址所在的一侧。 新参数 max_results 可用于指定最佳结果的数量或仅返回最佳结果。

示例:基本

下面的示例计时是在一台 3.0 GHZ 单处理器 Windows 7 计算机上进行的,该计算机具有 2GB 内存,运行 PostgreSQL 9.1rc1/PostGIS 2.0,加载了所有 MA、MN、CA、RI state Tiger 数据。

精确匹配的计算速度更快(61 毫秒)

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', 1) As g;
 rating |        lon        |      lat       | stno | street | styp |  city  | st |  zip
--------+-------------------+----------------+------+--------+------+--------+----+-------
      0 | -71.0557505845646 | 42.35897920691 |   75 | State  | St   | Boston | MA | 02109

即使 zip 没有传入地理编码器也可以猜测(大约需要 122-150 毫秒)

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

可以处理拼写错误,并提供不止一种可能的解决方案和评级,并且需要更长的时间(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',1) As g;
 rating |         wktlonlat         | stno | street | styp |  city  | st |  zip
--------+---------------------------+------+--------+------+--------+----+-------
     70 | POINT(-71.06466 42.35114) |   31 | Stuart | St   | Boston | MA | 02116
    

用于对地址进行批量地理编码。 最简单的方法是设置 max_results=1。 仅处理尚未地理编码的内容(没有评级)。

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.rating,-1), pprint_addy(g.addy),
       ST_X(g.geomout)::numeric(8,5), ST_Y(g.geomout)::numeric(8,5) )
FROM (SELECT addid, address
    FROM addresses_to_geocode
    WHERE rating IS NULL ORDER BY addid LIMIT 3) As a
    LEFT JOIN LATERAL geocode(a.address,1) As g ON true
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.07177 | 42.38357 | 529 Main St, Boston, MA 02129             |      0
     2 | 77 Massachusetts Avenue, Cambridge, MA 02139 | -71.09396 | 42.35961 | 77 Massachusetts Ave, Cambridge, MA 02139 |      0
     3 | 25 Wizard of Oz, Walaford, KS 99912323       | -97.92913 | 38.12717 | Willowbrook, KS 67502                     |    108
(3 rows)

示例:使用几何过滤器

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
--------+---------------------------+------+---------+------+------+----+-------
      7 | POINT(-70.96796 42.4659)  |  100 | Federal | St   | Lynn | MA | 01905
     16 | POINT(-70.96786 42.46853) | NULL | Federal | St   | Lynn | MA | 01905
(2 rows)

Time: 622.939 ms