Alias

-

Description

It takes as input parameter an array of two double values, and returns the minimum second value of the array.

This function can be used to obtain the minimum distance between a set of points, as shown in the following examples. Within PostgreSQL it is used with the spatial index to reduce the search space.

Coordinate Dimensions

Spatial Standards Support

Examples

PostgreSQL

create table a(c1 double precision, c2 double precision);
insert into a values (10,2.456);
insert into a values (5,1.456);
insert into a values (20,2.256);

select minpair(Array[c1,c2]) from a;
  minpair  
-----------
 {5,1.456}
(1 row)

H2

create table a(c1 integer, c2 double);
	insert into a values (10,2.456);
	insert into a values (5,1.456);
	insert into a values (20,2.256);
	
	select minpair((c1::double,c2)) from a;

--Result
MINPAIR((CAST(C1 AS DOUBLE), C2))  
(5.0, 1.456)

How to use MinPair to obtain the minimum distance:

Suppose you have a table of points and you want to get which is the closest point to each point and the shortest distance between them.

CREATE TABLE points(gid serial PRIMARY KEY); SELECT AddGeometryColumn ('points','geom',-1,'POINT',2); --H2: SELECT AddGeometryColumn ('POINTS','GEOM',-1,'POINT',2); INSERT INTO points(geom) VALUES (ST_GeomfromText('POINT (8 10)')); ... gid | st_astext -----+--------------- 1 | POINT (8 10) 2 | POINT (18 15) 3 | POINT (11 15) 4 | POINT (13 13) 5 | POINT (11 12) 6 | POINT (0 11) 7 | POINT (15 3) 8 | POINT (28 17) 9 | POINT (19 23) 10 | POINT (27 8) 11 | POINT (7 2) 12 | POINT (20 10) 13 | POINT (7 23) (13 rows)

PostgreSQL

SELECT d1.gid, MinPair(ARRAY[d2.gid,distance(d1.geom,d2.geom)]), count(*) 
 FROM points d1, points d2 
 WHERE (ST_Expand (d1.geom,10) && d2.geom) and distance (d1.geom,d2.geom) > 0 
GROUP BY d1.gid;

 gid |        minpair        | count 
-----+-----------------------+-------
   1 | {5,3.60555127546399}  |     7
   2 | {4,5.3851648071345}   |     8
   3 | {4,2.82842712474619}  |     7
   4 | {5,2.23606797749979}  |     8
   5 | {4,2.23606797749979}  |     7
   6 | {1,8.06225774829855}  |     2
   7 | {11,8.06225774829855} |     5
   8 | {10,9.05538513813742} |     4
   9 | {2,8.06225774829855}  |     4
  10 | {12,7.28010988928052} |     3
  11 | {1,8.06225774829855}  |     4
  12 | {2,5.3851648071345}   |     7
  13 | {3,8.94427190999916}  |     2
(13 rows)

H2

SELECT d1.gid, MinPair((d2.gid::double,distance(d1.geom,d2.geom))) as minpair, count(*) FROM points d1, points d2 
WHERE distance (d1.geom,d2.geom) > 0 
GROUP BY d1.gid;

GID  	MINPAIR  			COUNT(*)  
1	(5.0, 3.605551275463989)	12
2	(4.0, 5.385164807134504)	12
3	(4.0, 2.8284271247461903)	12
4	(5.0, 2.23606797749979)		12
5	(4.0, 2.23606797749979)		12
6	(1.0, 8.06225774829855)		12
7	(11.0, 8.06225774829855)	12
8	(10.0, 9.055385138137417)	12
9	(2.0, 8.06225774829855)		12
10	(12.0, 7.280109889280518)	12
11	(1.0, 8.06225774829855)		12
12	(2.0, 5.385164807134504)	12
13	(3.0, 8.94427190999916)		12

Related functions