postgresql - 如何将用户输入与模棱两可的城市名称相匹配？

Question

我们有一组如下所示的表格，我们使用其他表格来引用位置数据。一些例子是:

• 查找 X 市 X 英里范围内的所有公司
• 将公司简介的位置创建为 X 城市

我们也通过与 State 匹配解决了多个名称相似的城市的问题，但现在我们遇到了一组不同的问题。我们使用 Google 的 Place Autocomplete 进行地理编码以及将用户查询与我们的城市进行匹配。在 Google 的格式与我们的格式不同之前，这种方法工作得很好。

示例:圣。 Louis !== Saint Louis 和Ameca del Torro !== Ameca Torro

有没有办法在我们的查询中模糊匹配城市？

我们匹配城市的查询现在看起来像:

SELECT c.id
FROM city c
INNER JOIN state s
ON s.id = c.state_id
WHERE c.name = 'Los Angeles' AND s.short_name = 'CA'

我还考虑了非规范化城市并简单地存储坐标来完成半径搜索。现在我们的 company 表中有大约 200 万行，因此将对其执行半径搜索，而不是通过 city 表执行 JOIN 公司。这也意味着我们将无法为城市创建自定义区域(无论如何)，也无法在未来为城市添加其他属性。

我找到了 this answer但它基本上肯定了我们规范化输入的方式是一个好方法，但不是我们如何匹配我们的本地表(除非谷歌提供我不知道的城市名称导出)。

Answer 1

简短的回答是，您可以通过自定义搜索配置使用 Postgres 的全文搜索功能。

由于您处理地名，您可能希望避免词干提取，因此您可以使用简单的配置作为起点。您还可以添加对地名有意义的停用词(在上面的示例中，您可能会将“St.”、“Saint”和“del”视为停用词)。

设置您的自定义项的基本概述如下:

1. 创建一个停用词文件并将其放入 $SHAREDIR/tsearch_data Postgres 目录中。参见 https://www.postgresql.org/docs/9.1/static/textsearch-dictionaries.html#TEXTSEARCH-STOPWORDS .
2. 创建一个使用这个停用词列表的字典(您可以使用 pg_catalog.simple 作为您的模板字典)。参见 https://www.postgresql.org/docs/9.1/static/textsearch-dictionaries.html#TEXTSEARCH-SIMPLE-DICTIONARY .
3. 创建地名搜索配置。参见 https://www.postgresql.org/docs/9.1/static/textsearch-configuration.html .
4. 更改您的搜索配置以使用您在第 2 步中创建的词典(参见上面的链接)。

另外一个考虑是如何考虑国际化。您的第二个示例(Ameca del Torro 与 Ameca Torro)的问题似乎是名称的西类牙语与英语表示形式。如果是这种情况，您还可以考虑同时存储城市名称的“本地化”和“通用”(例如英语)版本。

最后，您的查询(使用全文搜索)可能如下所示(其中“地点”是您的搜索配置的名称):

SELECT cities."id"FROM cities    INNER JOIN "state" ON "state".id = cities.state_idWHERE    "state".short_name = 'CA'    AND TO_TSVECTOR('places', cities.name) @@ TO_TSQUERY('places', 'Los & Angeles')