Find rows with a column value close to the one of the current row

Question

I have a table in the following format:

id	lon	lat
1	10.111	20.415
2	10.099	30.132
3	10.110	20.414

I would like to create a new column that returns all IDs such that lon and lat are less then a tolerance value away, i.e. abs(lon_i - lon_j) < tol AND abs(lat_i-lat_j) < tol for all i != j.

My initial thought going about it is to create a temporary duplicate of the table and then join the table giving the names of the columns lon_2, lat_2, and then:

SELECT id 
FROM TABLE_1
WHERE ABS(TABLE_1.lon - TABLE_1.lon_2) < tol 
  AND ABS(TABLE_1.lat - TABLE_1.lat_2) < tol

But there must be a better and more efficient way to approach it.

Answer 1

There is no need for a temporary table.

You can do a self join:

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;

This will return 1 row for each pair of ids that satisfies the conditions.

If you want for each id a comma separated list of all the ids that satisfy the conditions then you can aggregate and use 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;