postgis_tiger_geocoder

pig repo add pgdg -u          # 添加 PGDG 仓库并更新缓存

CREATE EXTENSION postgis_tiger_geocoder CASCADE;  -- 依赖: postgis, fuzzystrmatch

CREATE EXTENSION postgis_tiger_geocoder CASCADE;

-- 生成下载和加载某个州数据的脚本
-- （例如，马萨诸塞州 = 'MA'）
SELECT loader_generate_script(ARRAY['MA'], 'sh');

-- 安装缺失的索引以提升性能
SELECT install_missing_indexes();

-- 更新统计信息
ANALYZE tiger.addr;
ANALYZE tiger.edges;
ANALYZE tiger.faces;

-- 基本地理编码
SELECT g.rating, ST_X(g.geomout) AS lon, ST_Y(g.geomout) AS lat,
       pprint_addy(g.addy) AS address
FROM geocode('1600 Pennsylvania Ave NW, Washington, DC 20500') AS g;

-- 限制返回结果数量的地理编码
SELECT g.rating, ST_AsText(g.geomout), pprint_addy(g.addy)
FROM geocode('100 Main St, Boston, MA', 3) AS g;

-- 从表中批量地理编码
SELECT a.id, g.rating, g.geomout, pprint_addy(g.addy)
FROM addresses a, LATERAL geocode(a.address_string, 1) AS g;

SELECT pprint_addy(r.addy[1]) AS address
FROM reverse_geocode(ST_SetSRID(ST_MakePoint(-77.0365, 38.8977), 4326)) AS r;

SELECT *
FROM normalize_address('1600 Pennsylvania Avenue NW, Washington, DC 20500');

-- 格式化输出标准化地址
SELECT pprint_addy(normalize_address('100 main street boston ma 02101'));

-- 查看当前设置
SELECT * FROM tiger.geocode_settings;

-- 调整设置（例如，增加调试级别）
UPDATE tiger.geocode_settings SET val = 'true' WHERE name = 'debug_geocode_address';