为联接表寻找(更大的)最近值

Question

我的PostgreSQL里有两张桌子。

• 地址(街道、城市、邮编)
• ZIP (zip，gps_lat，gps_long)

ZIP在两者中都是CHAR(5)，但是每个ZIP都是格式：'XXXXX‘5数字。例如：“55555”或“12345”。

我要选择所有的地址与加入的ZIP。问题是，有些地址有zip，它不在ZIP表中。对于这种情况，我想要连接最近(更大)的zip值。

我为它创建了DB函数(psc == zip)：

CREATE OR REPLACE FUNCTION lekari.get_psc(pscx character)
RETURNS character
LANGUAGE plpgsql
AS $function$
begin
    if exists (select 1 from spravni_celky.zip where psc = pscx) then
        return pscx;
    end if;

    while not exists (select 1 from spravni_celky.zip where psc = pscx) loop  
        pscx =  cast(cast(pscx as integer) + 1 as char(5));
    end loop;

return pscx;
end;$function$;

然后创建简单的选择：

select * from lekari.address lad
join spravni_celky.zip p on p.psc = lekari.get_psc(lad.psc)

它可以工作，但是对于地址中的12行(在ZIP中大约有200行)，查询时间几乎是4分钟！

B计划在DB中存储两个ZIP，一个是当前的，另一个是用于连接的。

非常感谢！

Answer 1

demo:db<>fiddle

SELECT DISTINCT ON (a.city, a.street, a.zip)
    *
FROM
    address a
JOIN
    zip z
ON a.zip <= z.zip
ORDER BY a.city, a.street, a.zip, z.zip

加入所有等于或更大的拉链。然后给出每个连接地址的第一个记录。

如果zip存在:第一个合适的ZIP等于。因此，第一次订购的记录是自己的拉链。

如果zip不存在:第一个合适的ZIP是下一个更大的。

DISTINCT ON给出了有序组的第一批记录。

Answer 2

您已经回答了您提出的问题，并对总体策略提出了一些评论。我只是想跳到这一点，因为，在前世，我写了一个邮编的距离计算器产品。这是伟大的，以前的互联网，但我不推荐现在。原因如下：

• 有负担得起的API做距离和接近计算与路边的准确性。比粗劣的质心计算要好得多。我不时地研究这个问题，但现在不知道什么是最好的解决方案。
• 如果你得到了你的长坐标的更新，球上距离的数学是非常基本的。到处都有例子。不是高保真，而是快。对于阻塞查询来说，这是一种很好的选择，在进行更敏感的搜索之前，您需要过滤到一个粗糙的区域。Postgres有一个点类型，但是您可能已经在使用它了。(我相信，PostGIS对R树有支持，这对于真正的多边形来说是一种非常棒的索引结构。)
• 如果必须使用邮政编码质心，则必须定期更新数据。没有可靠的方法来插值丢失的ZIP代码的坐标，您必须得到有人计算的坐标(“合成”，参见下一点)。下面是USPS提供的内容：https://www.unitedstateszipcodes.org/zip-code-database/
• “质心”正如人们所注意到的，邮政编码是一种路径，而不是多边形。所以多边形被伪造了。然后人工选择一个中心点。它可能在湖的中央，不是一个高保真度的点数据集。
• 带简单三角图的质心数据在很远的距离上工作正常，在密集区域不太大。所以，你的人口越密集，结果就越糟。因为世界不是一个球体，所以事情也搞砸了。
• 邮政编码数字序列并不是一种邻近性的度量。几个词:夏威夷，关岛，北马里亚纳，帕劳，阿拉斯加。
• 听起来你的前导零被覆盖了，比如01776。祝你好运。一些地方的开发人员(你好，加州，我在看你！)忘记像马萨诸塞州这样的地方，那里的邮政编码的第一个数字是0。
• 很明显，你在处理美国的数据，这常常导致一个问题：“加拿大呢？”不是的。根据皇家法令，加拿大每10个人就有一个邮政编码，或者什么的。为此，您最好使用路边级地址服务。