基于 PostgreSQL 的模糊地址匹配教程-Python教程-PHP中文网

基于 PostgreSQL 的模糊地址匹配教程

霞舞

发布： 2025-07-11 17:16:33

原创

310人浏览过

基于 postgresql 的模糊地址匹配教程

本文旨在提供一个基于 PostgreSQL 的模糊地址匹配方案。我们将探讨如何利用 pg_trgm 扩展提供的相似度函数，结合噪声词移除等预处理技术，来实现高效且准确的地址模糊匹配。本教程将提供具体的 SQL 示例，并讨论在 PostgreSQL 中直接实现和使用 Python 辅助处理的优劣。

引言

在数据清洗和集成过程中，经常会遇到需要匹配两组地址或名称数据的情况。由于数据录入错误、格式不统一或者信息不完整等原因，精确匹配往往无法满足需求，这时就需要采用模糊匹配技术。PostgreSQL 提供了多种扩展和函数，可以有效地解决这类问题。本文将重点介绍如何使用 pg_trgm 扩展，并结合其他技巧，实现高效且准确的地址模糊匹配。

使用 pg_trgm 扩展进行相似度匹配

pg_trgm 扩展提供了一系列用于计算字符串相似度的函数，其中 similarity() 函数尤其适用于模糊匹配。它基于 trigram (三个连续字符) 的匹配程度来评估字符串的相似度。相比于 levenshtein() 函数，similarity() 函数对字符串长度差异的敏感度较低，更适合处理地址数据中常见的不等长字符串匹配问题。

首先，需要确保 pg_trgm 扩展已安装并启用。可以使用以下 SQL 命令进行安装：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

登录后复制

然后，可以使用 similarity() 函数来比较两个字符串的相似度：

SELECT similarity('Abendsonne', 'Hotel Abendsonne'); -- 输出: 0.64705884
SELECT similarity('Abendsonne', 'Undine'); -- 输出: 0.05882353

登录后复制

从上面的例子可以看出，similarity() 函数能够更准确地评估 Abendsonne 和 Hotel Abendsonne 之间的相似度，而 levenshtein() 函数则无法区分。

更进一步，可以在查询中使用 similarity() 函数来查找与目标地址最相似的地址：

SELECT address, similarity(address, 'Otto-Johannsen-Str. 7') AS sml
FROM addresses
WHERE sml > 0.3 -- 设定相似度阈值
ORDER BY sml DESC
LIMIT 10; -- 返回最相似的 10 个结果

登录后复制

这个查询会从 addresses 表中选择与 'Otto-Johannsen-Str. 7' 相似度大于 0.3 的地址，并按照相似度降序排列，返回前 10 个结果。

利用索引加速查询

pg_trgm 扩展还支持创建 GIST 或 GIN 索引，以加速相似度查询。对于大型数据集，索引可以显著提高查询性能。

创建索引的 SQL 命令如下：

CREATE INDEX address_trgm_idx ON addresses USING GIST (address gist_trgm_ops);

登录后复制

或

Grammarly

Grammarly是一款在线语法纠正和校对工具，伟大的AI辅助写作工具

253

查看详情

CREATE INDEX address_trgm_idx ON addresses USING GIN (address gin_trgm_ops);

登录后复制

GIST 索引适用于更广泛的相似度查询，而 GIN 索引则更适合精确匹配和前缀匹配。选择哪种索引取决于具体的查询模式和数据特征。

预处理：移除噪声词

地址数据中常常包含一些噪声词，如 "Straße"、"Str."、"Hotel" 等。这些词语可能会影响相似度计算的准确性。因此，在进行相似度匹配之前，可以先移除这些噪声词。

可以使用 PostgreSQL 的 regexp_replace() 函数来实现噪声词移除。例如，以下 SQL 命令可以移除地址中的 "Straße" 和 "Str."：

SELECT regexp_replace(address, '(Straße|Str.)', '', 'g') AS cleaned_address
FROM addresses;

登录后复制

其中，g 标志表示全局替换，即替换所有匹配的字符串。

可以将噪声词移除和相似度计算结合起来，得到更准确的匹配结果：

SELECT
    address,
    similarity(regexp_replace(address, '(Straße|Str.)', '', 'g'), regexp_replace('Otto-Johannsen-Str. 7', '(Straße|Str.)', '', 'g')) AS sml
FROM addresses
WHERE sml > 0.3
ORDER BY sml DESC
LIMIT 10;

登录后复制

使用 Python 辅助处理

虽然 PostgreSQL 提供了强大的模糊匹配功能，但在某些情况下，使用 Python 辅助处理可能更加灵活和方便。例如，可以使用 Python 的 fuzzywuzzy 库来进行更复杂的字符串匹配。

以下是一个使用 fuzzywuzzy 库进行地址匹配的 Python 示例：

from fuzzywuzzy import fuzz
from fuzzywuzzy import process

choices = ["Otto-Johannsen-Straße 7", "Otto-Johannsen-Str. 7 Wohnung oben", "Antje's Hus", "Haus am Meer"]
query = "Otto-Johannsen-Str. 7"

result = process.extractOne(query, choices, scorer=fuzz.ratio)

print(result) # 输出: ('Otto-Johannsen-Str. 7 Wohnung oben', 90)

登录后复制

在这个例子中，process.extractOne() 函数会从 choices 列表中选择与 query 最相似的字符串，并返回相似度得分。

可以使用 psycopg2 库连接 PostgreSQL 数据库，并将 Python 的匹配结果更新到数据库中。