Is There a Postgres Closest Operator

Is there a postgres CLOSEST operator?

I may be a little off on the syntax, but this parameterized query (all the ? take the '1' of the original question) should run fast, basically 2 B-Tree lookups [assuming number is indexed].

SELECT * FROM
(
  (SELECT id, number FROM t WHERE number >= ? ORDER BY number LIMIT 1) AS above
  UNION ALL
  (SELECT id, number FROM t WHERE number < ? ORDER BY number DESC LIMIT 1) as below
) 
ORDER BY abs(?-number) LIMIT 1;

The query plan for this with a table of ~5e5 rows (with an index on number) looks like this:

psql => explain select * from (
        (SELECT id, number FROM t WHERE number >= 1 order by number limit 1) 
        union all
        (select id, number from t where number < 1 order by number desc limit 1)
) as make_postgresql_happy 
order by abs (1 - number) 
limit 1;
                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.24..0.24 rows=1 width=12)
   ->  Sort  (cost=0.24..0.24 rows=2 width=12)
         Sort Key: (abs((1::double precision - public.t.number)))
         ->  Result  (cost=0.00..0.23 rows=2 width=12)
               ->  Append  (cost=0.00..0.22 rows=2 width=12)
                     ->  Limit  (cost=0.00..0.06 rows=1 width=12)
                           ->  Index Scan using idx_t on t  (cost=0.00..15046.74 rows=255683 width=12)
                                 Index Cond: (number >= 1::double precision)
                     ->  Limit  (cost=0.00..0.14 rows=1 width=12)
                           ->  Index Scan Backward using idx_t on t  (cost=0.00..9053.67 rows=66136 width=12)
                                 Index Cond: (number < 1::double precision)
(11 rows)

Find closest match from 2 columns in postgresql

You could store the data as PostGIS 'point' type instead of coordinate values:

• https://postgis.net/docs/ST_MakePoint.html
• https://postgis.net/docs/ST_GeomFromText.html

This would empower you with all the PostGIS functionality such as:

• https://postgis.net/docs/ST_DWithin.html

Then you could create a GiST index and use the PostGIS <-> operator to take advantage of index assisted nearest neighbor result sets. The 'nearest neighbor' functionality is pretty common. Here is a great explanation:

• https://postgis.net/workshops/postgis-intro/knn.html

“KNN” stands for “K nearest neighbours”, where “K” is the number of neighbours you are looking for.

KNN is a pure index based nearest neighbour search. By walking up and down the index, the search can find the nearest candidate geometries without using any magical search radius numbers, so the technique is suitable and high performance even for very large tables with highly variable data densities.

PostgreSQL to iterate through rows and find the closest match using custom distance-function

Generally speaking you can solve this type of problems by using a stored function, written in Java or Scala (some might prefer PL/SQL, C or C++).

PostgreSql supports (Java based) stored functions, so let the SQL query fetch the data, and pass it to a stored function. The stored function returns the distance, so you can filter/sort etc. on it.

Based on a table like this

create table point(vector float8[]);
insert into point values('{0.0, 0.0, 0.0}');
insert into point values('{0.5, 0.5, 0.5}');

with a Java function like this:

public class PlJava {
    public final static double distance2(double[] v1, double[] v2) {
        return Math.sqrt(Math.pow(v2[0] - v1[0], 2)
          + Math.pow(v2[1] - v1[1], 2) + Math.pow(v2[2] - v1[2], 2));
    }
}

and the function declaration in SQL:

CREATE FUNCTION pljava.distance2(float8[], float8[])
  RETURNS float8
  AS 'PlJava.distance2'
  IMMUTABLE
  LANGUAGE java;

your query could look like this:

select
    point.*, 
    pljava.distance2(vector, '{1.0, 1.0, 1.0}') as dist
  from
    point 
  order by
    dist;    

which results in

    vector     |       dist  
---------------+-------------------  
 {0.5,0.5,0.5} | 0.866025403784439  
 {0,0,0}       |  1.73205080756888  

Update

Stored functions can be written in C and C++ as well. C++ requires more effort, because the interface to PostgreSql is using the C calling convention. See Using C++ for Extensibility

PostgreSQL complex query to find closest matching values for two fields

You can try something like this:

SELECT *
FROM table1
WHERE healthtime = '2:00'
ORDER BY ((longitude::double precision - (-110.3421))^2 +
          (latitude::double precision -  (38.7587  ))^2)
LIMIT 1

It will return you the point with minimum "distance" : ( (lon-lon1)^2 + (lat-lat1)^2 -> min ) .

My SQLFiddle with example.

UPD Test query:

SELECT *, ((longitude::double precision - (-110.3421))^2 +
           (latitude::double precision -  (38.7587  ))^2) as distance,
          (abs(longitude::double precision - (-110.3421)) +
           abs(latitude::double precision -  (38.7587  ))) as distance2
FROM table1
WHERE time = '2:00'
ORDER BY ((longitude::double precision - (-110.3421))^2 +
          (latitude::double precision -  (38.7587  ))^2)

LIMIT 2

Gives 2 best results:

| TIME | LONGITUDE | LATITUDE |   DISTANCE | DISTANCE2 |
--------------------------------------------------------
| 2:00 | -110.2743 |  38.7983 |   0.006165 |    0.1074 |
| 2:00 | -110.4365 |  38.7463 | 0.00906512 |    0.1068 |

DISTANCE = (lon-lon1)^2 + (lat-lat1)^2 (my variant).

DISTANCE2 = abs(lon-lon1) + abs(lat-lat1) (your variant).

Your variant gives different "closest". You can use any variant of "distance" calculation, but I prefer "classical" way - root((lon-lon1)^2 + (lat-lat1)^2).

New SQLFiddle

nearest point in postgres tables

can you create a field column geom with geometry type? update using ST_point.

Then you do

with cte as (
      SELECT A.geom, B.geom, B.block_id,
             ST_Distance(A.geom, B.geom) as dist -- check this value first
      FROM TableA as A
      CROSS JOIN TableB as B
      WHERE ST_Distance(A.geom, B.geom) < 16000 -- 10 miles
)
SELECT *
FROM  cte

EDIT:

Lets assume your CTE works, then you can find the nearest point like this

with cte as (
  ....
), sort as (
   SELECT *, row_number() over (partition by A.id order by dist) as  rn
   FROM cte
)
SELECT *
FROM sort 
WHERE rn = 1

find the nearest location by latitude and longitude in postgresql

select * from (
SELECT  *,( 3959 * acos( cos( radians(6.414478) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(12.466646) ) + sin( radians(6.414478) ) * sin( radians( lat ) ) ) ) AS distance 
FROM station_location
) al
where distance < 5
ORDER BY distance
LIMIT 20;

What Postgres 13 index types support distance searches?

timestamp type supports KNN with GiST indexes using the <-> operator created by the btree_gist extension.

You can check if a specific column of a specific index supports it, like this:

select pg_index_column_has_property('pgbench_history_mtime_idx'::regclass,1,'distance_orderable');

PostgreSQL return exact or closest date to queried date

If you want the closest date before, do it this way:

SELECT year, session_date
FROM calendar_dates
WHERE session_date < '$date_string'
ORDER BY session_date DESC
LIMIT 1;

The closest date after uses similar logic.

For the closest on either side:

SELECT year, session_date
FROM calendar_dates
ORDER BY abs(session_date - date '$date_string') 
LIMIT 1;

Closest value within tolerance band for multiple ids

The best way to do this in Postgres uses distinct on:

SELECT DISTINCT ON (channelid, curveid) t.*
FROM table  
ORDER by channelid, curveid, abs(xvalue - $myvalue);

If you know the nearest value is within 0.5 of $myvalue for all combinations you want, then you can add your where clause.

---

Related Topics