查找列值接近当前行的行

我有一个表格，格式如下:

<表类> id 经度 lat tbody><<tr>110.11120.415210.09930.132310.11020.414

不需要临时表

你可以做自连接:

SELECT t1.id, t2.id
FROM tablename t1 INNER JOIN tablename t2
ON t2.id <> t1.id AND ABS(t1.lon - t2.lon) < :tol AND ABS(t1.lat - t2.lat) < :tol;

这将为满足条件的每一对id返回一行。

如果您想为每个id提供一个逗号分隔的所有满足条件的id的列表，那么您可以聚合并使用STRING_AGG():

SELECT t1.id, STRING_AGG(t2.id, ';' ORDER BY t2.id)
FROM tablename t1 INNER JOIN tablename t2
ON t2.id <> t1.id AND ABS(t1.lon - t2.lon) < :tol AND ABS(t1.lat - t2.lat) < :tol
GROUP BY t1.id;

所有使得lon和lat小于容差值的id

一个简单的自连接，如forpas所示，可以解决这个任务。

然而时，简单查询必须计算两行的每个组合的的增量，然后消除给定容差之外的对。基本上是笛卡尔积，与O(N²)的比例非常大。如果您的表不是特别小，请继续阅读。

目标是得到O(N)代替。我们将使用一些高级概念:

最近邻搜索
GiST表达指数
LATERALjoin

最近邻查询

如果你不熟悉，这里有一个介绍最近邻搜索与PostGis(可能在使用您的地理编码?)。

无论哪种方式，我们都可以在Postgres中对point类型执行相同的操作。需要一个GiST索引，同时坚持原来的原始表，基于表达式:

CREATE INDEX tbl_point_gist_idx ON tbl USING GiST (point(lon, lat));

基本查询-快速但不完善

SELECT t.*, t1.id AS near_id, t1.lon AS near_lon, t1.lat AS near_lat
FROM   tbl t
JOIN   LATERAL (
SELECT *
FROM   tbl t1
WHERE  t1.id <> t.id
ORDER  BY point(t.lon, t.lat) <-> point(t1.lon, t1.lat)
LIMIT  5  -- arbitrary
) t1 ON abs(t.lon - t1.lon) < 0.0011
AND abs(t.lat - t1.lat) < 0.0011;

near_lat310.11020.414

最近邻查询

基本查询-快速但不完善

相关内容

最新更新

热门标签：