如何使用Postgres+geonames转储数据实现带优先级的地名全文搜索

你当前的问题核心是只依赖了全文搜索的文本匹配度排序，没有加入地理实体的重要性权重，所有匹配到「paris」的结果文本得分差异极小，才会出现美国的小地点优先返回的情况，可以按照以下步骤调整：

• 第一步：复用geonames数据集自带的字段计算权重，不需要额外导入外部数据
  权重计算参考geonames的feature_class（实体类型）、feature_code（实体细分类）、population（人口）三个核心字段即可，法国巴黎的feature_code为PPLC（国家首都），人口超过200万，而你当前返回的美国结果大多是教堂、公墓、溪流分支这类非居住类实体，人口为0，天然重要性极低。
  你可以自定义权重规则，参考示例如下：

  • 国家首都（feature_code = 'PPLC'）：加10000权重分
  • 一级行政区首府（feature_code = 'PPLA'）：加5000权重分
  • 人口超过10万的普通城市（feature_code LIKE 'PPL%' AND population > 100000）：加1000权重分
  • 人口低于10万的村镇类居住点（feature_code LIKE 'PPL%'）：加100权重分
  • 非居住类实体（比如公墓、教堂、设施等）：加10权重分
  • 人口附加分直接取COALESCE(population, 0) / 1000，人口越高得分越高

• 第二步：调整查询SQL，用综合得分排序
  把全文搜索匹配分和上述权重分相加得到综合得分，按综合得分倒序即可，参考SQL如下：

SELECT 
    geonameid, name, latitude, longitude, country,
    -- 综合得分
    (ts_rank(to_tsvector('english', name), plainto_tsquery('english', 'paris')) * 10) +
    CASE 
        WHEN feature_code = 'PPLC' THEN 10000
        WHEN feature_code = 'PPLA' THEN 5000
        WHEN feature_code LIKE 'PPL%' AND population > 100000 THEN 1000
        WHEN feature_code LIKE 'PPL%' THEN 100
        ELSE 10
    END +
    COALESCE(population, 0) / 1000 AS total_score
FROM geonames
WHERE to_tsvector('english', name) @@ plainto_tsquery('english', 'paris')
ORDER BY total_score DESC
LIMIT 10;

• 第三步（可选优化）：性能优化
  如果数据量超过百万级，可以预先把name字段对应的tsvector存入单独字段并建立GIN索引，也可以把常用权重规则的得分预先计算存入字段加索引，避免查询时实时计算损耗性能。
  如果业务有特定面向的用户区域，还可以额外增加国家维度的权重，比如面向欧洲用户给欧盟国家的结果额外加权重，进一步贴合业务需求。

内容的提问来源于stack exchange，提问作者Ahmed Amine Lamrhary