Subject: [PATCH 1/3] Add operator <->(box, point)

From: Nikita Glukhov <[email-masked]>
Date: Thu, 7 Mar 2019 20:22:49 +0300

---
 src/backend/utils/adt/geo_ops.c        | 12 +++++
 src/include/catalog/pg_operator.dat    |  5 +-
 src/include/catalog/pg_proc.dat        |  3 ++
 src/test/regress/expected/geometry.out | 96 +++++++++++++++++-----------------
 src/test/regress/sql/geometry.sql      |  2 +-
 5 files changed, 68 insertions(+), 50 deletions(-)

diff --git a/src/backend/utils/adt/geo_ops.c b/src/backend/utils/adt/geo_ops.c
index 373784f..d8ecfe3 100644
--- a/src/backend/utils/adt/geo_ops.c
+++ b/src/backend/utils/adt/geo_ops.c
@@ -2419,6 +2419,18 @@ dist_pb(PG_FUNCTION_ARGS)
 }
 
 /*
+ * Distance from a box to a point
+ */
+Datum
+dist_bp(PG_FUNCTION_ARGS)
+{
+	BOX		   *box = PG_GETARG_BOX_P(0);
+	Point	   *pt = PG_GETARG_POINT_P(1);
+
+	PG_RETURN_FLOAT8(box_closept_point(NULL, box, pt));
+}
+
+/*
  * Distance from a lseg to a line
  */
 Datum
diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat
index bacafa5..ae5ed1e 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -666,7 +666,10 @@
   oprresult => 'float8', oprcode => 'dist_ps' },
 { oid => '615', descr => 'distance between',
   oprname => '<->', oprleft => 'point', oprright => 'box',
-  oprresult => 'float8', oprcode => 'dist_pb' },
+  oprresult => 'float8', oprcom => '<->(box,point)', oprcode => 'dist_pb' },
+{ oid => '606', descr => 'distance between',
+  oprname => '<->', oprleft => 'box', oprright => 'point',
+  oprresult => 'float8', oprcom => '<->(point,box)', oprcode => 'dist_bp' },
 { oid => '616', descr => 'distance between',
   oprname => '<->', oprleft => 'lseg', oprright => 'line',
   oprresult => 'float8', oprcode => 'dist_sl' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 8733524..54327bf 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1065,6 +1065,9 @@
 { oid => '364',
   proname => 'dist_pb', prorettype => 'float8', proargtypes => 'point box',
   prosrc => 'dist_pb' },
+{ oid => '357',
+  proname => 'dist_bp', prorettype => 'float8', proargtypes => 'box point',
+  prosrc => 'dist_bp' },
 { oid => '365',
   proname => 'dist_sb', prorettype => 'float8', proargtypes => 'lseg box',
   prosrc => 'dist_sb' },
diff --git a/src/test/regress/expected/geometry.out b/src/test/regress/expected/geometry.out
index 055d32c..6be3a39 100644
--- a/src/test/regress/expected/geometry.out
+++ b/src/test/regress/expected/geometry.out
@@ -602,54 +602,54 @@ SELECT p.f1, l.s, p.f1 <-> l.s FROM POINT_TBL p, LSEG_TBL l;
 (72 rows)
 
 -- Distance to box
-SELECT p.f1, b.f1, p.f1 <-> b.f1 FROM POINT_TBL p, BOX_TBL b;
-        f1         |         f1          |      ?column?      
--------------------+---------------------+--------------------
- (0,0)             | (2,2),(0,0)         |                  0
- (0,0)             | (3,3),(1,1)         |      1.41421356237
- (0,0)             | (-2,2),(-8,-10)     |                  2
- (0,0)             | (2.5,3.5),(2.5,2.5) |      3.53553390593
- (0,0)             | (3,3),(3,3)         |      4.24264068712
- (-10,0)           | (2,2),(0,0)         |                 10
- (-10,0)           | (3,3),(1,1)         |      11.0453610172
- (-10,0)           | (-2,2),(-8,-10)     |                  2
- (-10,0)           | (2.5,3.5),(2.5,2.5) |       12.747548784
- (-10,0)           | (3,3),(3,3)         |      13.3416640641
- (-3,4)            | (2,2),(0,0)         |      3.60555127546
- (-3,4)            | (3,3),(1,1)         |      4.12310562562
- (-3,4)            | (-2,2),(-8,-10)     |                  2
- (-3,4)            | (2.5,3.5),(2.5,2.5) |      5.52268050859
- (-3,4)            | (3,3),(3,3)         |       6.0827625303
- (5.1,34.5)        | (2,2),(0,0)         |      32.6475113906
- (5.1,34.5)        | (3,3),(1,1)         |      31.5699223946
- (5.1,34.5)        | (-2,2),(-8,-10)     |      33.2664996656
- (5.1,34.5)        | (2.5,3.5),(2.5,2.5) |       31.108841187
- (5.1,34.5)        | (3,3),(3,3)         |      31.5699223946
- (-5,-12)          | (2,2),(0,0)         |                 13
- (-5,-12)          | (3,3),(1,1)         |      14.3178210633
- (-5,-12)          | (-2,2),(-8,-10)     |                  2
- (-5,-12)          | (2.5,3.5),(2.5,2.5) |      16.3248277173
- (-5,-12)          | (3,3),(3,3)         |                 17
- (1e-300,-1e-300)  | (2,2),(0,0)         | 1.41421356237e-300
- (1e-300,-1e-300)  | (3,3),(1,1)         |      1.41421356237
- (1e-300,-1e-300)  | (-2,2),(-8,-10)     |                  2
- (1e-300,-1e-300)  | (2.5,3.5),(2.5,2.5) |      3.53553390593
- (1e-300,-1e-300)  | (3,3),(3,3)         |      4.24264068712
- (1e+300,Infinity) | (2,2),(0,0)         |           Infinity
- (1e+300,Infinity) | (3,3),(1,1)         |           Infinity
- (1e+300,Infinity) | (-2,2),(-8,-10)     |           Infinity
- (1e+300,Infinity) | (2.5,3.5),(2.5,2.5) |           Infinity
- (1e+300,Infinity) | (3,3),(3,3)         |           Infinity
- (NaN,NaN)         | (2,2),(0,0)         |                NaN
- (NaN,NaN)         | (3,3),(1,1)         |                NaN
- (NaN,NaN)         | (-2,2),(-8,-10)     |                NaN
- (NaN,NaN)         | (2.5,3.5),(2.5,2.5) |                NaN
- (NaN,NaN)         | (3,3),(3,3)         |                NaN
- (10,10)           | (2,2),(0,0)         |       11.313708499
- (10,10)           | (3,3),(1,1)         |      9.89949493661
- (10,10)           | (-2,2),(-8,-10)     |      14.4222051019
- (10,10)           | (2.5,3.5),(2.5,2.5) |      9.92471662064
- (10,10)           | (3,3),(3,3)         |      9.89949493661
+SELECT p.f1, b.f1, p.f1 <-> b.f1 AS pt_box_dist, b.f1 <-> p.f1 AS box_pt_dist FROM POINT_TBL p, BOX_TBL b;
+        f1         |         f1          |    pt_box_dist     |    box_pt_dist     
+-------------------+---------------------+--------------------+--------------------
+ (0,0)             | (2,2),(0,0)         |                  0 |                  0
+ (0,0)             | (3,3),(1,1)         |      1.41421356237 |      1.41421356237
+ (0,0)             | (-2,2),(-8,-10)     |                  2 |                  2
+ (0,0)             | (2.5,3.5),(2.5,2.5) |      3.53553390593 |      3.53553390593
+ (0,0)             | (3,3),(3,3)         |      4.24264068712 |      4.24264068712
+ (-10,0)           | (2,2),(0,0)         |                 10 |                 10
+ (-10,0)           | (3,3),(1,1)         |      11.0453610172 |      11.0453610172
+ (-10,0)           | (-2,2),(-8,-10)     |                  2 |                  2
+ (-10,0)           | (2.5,3.5),(2.5,2.5) |       12.747548784 |       12.747548784
+ (-10,0)           | (3,3),(3,3)         |      13.3416640641 |      13.3416640641
+ (-3,4)            | (2,2),(0,0)         |      3.60555127546 |      3.60555127546
+ (-3,4)            | (3,3),(1,1)         |      4.12310562562 |      4.12310562562
+ (-3,4)            | (-2,2),(-8,-10)     |                  2 |                  2
+ (-3,4)            | (2.5,3.5),(2.5,2.5) |      5.52268050859 |      5.52268050859
+ (-3,4)            | (3,3),(3,3)         |       6.0827625303 |       6.0827625303
+ (5.1,34.5)        | (2,2),(0,0)         |      32.6475113906 |      32.6475113906
+ (5.1,34.5)        | (3,3),(1,1)         |      31.5699223946 |      31.5699223946
+ (5.1,34.5)        | (-2,2),(-8,-10)     |      33.2664996656 |      33.2664996656
+ (5.1,34.5)        | (2.5,3.5),(2.5,2.5) |       31.108841187 |       31.108841187
+ (5.1,34.5)        | (3,3),(3,3)         |      31.5699223946 |      31.5699223946
+ (-5,-12)          | (2,2),(0,0)         |                 13 |                 13
+ (-5,-12)          | (3,3),(1,1)         |      14.3178210633 |      14.3178210633
+ (-5,-12)          | (-2,2),(-8,-10)     |                  2 |                  2
+ (-5,-12)          | (2.5,3.5),(2.5,2.5) |      16.3248277173 |      16.3248277173
+ (-5,-12)          | (3,3),(3,3)         |                 17 |                 17
+ (1e-300,-1e-300)  | (2,2),(0,0)         | 1.41421356237e-300 | 1.41421356237e-300
+ (1e-300,-1e-300)  | (3,3),(1,1)         |      1.41421356237 |      1.41421356237
+ (1e-300,-1e-300)  | (-2,2),(-8,-10)     |                  2 |                  2
+ (1e-300,-1e-300)  | (2.5,3.5),(2.5,2.5) |      3.53553390593 |      3.53553390593
+ (1e-300,-1e-300)  | (3,3),(3,3)         |      4.24264068712 |      4.24264068712
+ (1e+300,Infinity) | (2,2),(0,0)         |           Infinity |           Infinity
+ (1e+300,Infinity) | (3,3),(1,1)         |           Infinity |           Infinity
+ (1e+300,Infinity) | (-2,2),(-8,-10)     |           Infinity |           Infinity
+ (1e+300,Infinity) | (2.5,3.5),(2.5,2.5) |           Infinity |           Infinity
+ (1e+300,Infinity) | (3,3),(3,3)         |           Infinity |           Infinity
+ (NaN,NaN)         | (2,2),(0,0)         |                NaN |                NaN
+ (NaN,NaN)         | (3,3),(1,1)         |                NaN |                NaN
+ (NaN,NaN)         | (-2,2),(-8,-10)     |                NaN |                NaN
+ (NaN,NaN)         | (2.5,3.5),(2.5,2.5) |                NaN |                NaN
+ (NaN,NaN)         | (3,3),(3,3)         |                NaN |                NaN
+ (10,10)           | (2,2),(0,0)         |       11.313708499 |       11.313708499
+ (10,10)           | (3,3),(1,1)         |      9.89949493661 |      9.89949493661
+ (10,10)           | (-2,2),(-8,-10)     |      14.4222051019 |      14.4222051019
+ (10,10)           | (2.5,3.5),(2.5,2.5) |      9.92471662064 |      9.92471662064
+ (10,10)           | (3,3),(3,3)         |      9.89949493661 |      9.89949493661
 (45 rows)
 
 -- Distance to path
diff --git a/src/test/regress/sql/geometry.sql b/src/test/regress/sql/geometry.sql
index ce98b3e..8fd02cf 100644
--- a/src/test/regress/sql/geometry.sql
+++ b/src/test/regress/sql/geometry.sql
@@ -77,7 +77,7 @@ SELECT p.f1, l.s, p.f1 <-> l.s FROM POINT_TBL p, LINE_TBL l;
 SELECT p.f1, l.s, p.f1 <-> l.s FROM POINT_TBL p, LSEG_TBL l;
 
 -- Distance to box
-SELECT p.f1, b.f1, p.f1 <-> b.f1 FROM POINT_TBL p, BOX_TBL b;
+SELECT p.f1, b.f1, p.f1 <-> b.f1 AS pt_box_dist, b.f1 <-> p.f1 AS box_pt_dist FROM POINT_TBL p, BOX_TBL b;
 
 -- Distance to path
 SELECT p.f1, p1.f1, p.f1 <-> p1.f1 FROM POINT_TBL p, PATH_TBL p1;
-- 
2.7.4

--------------07EE9595454874974EAD75E7
Content-Type: text/x-patch;
 name="0002-Add-operator-box-point-to-GiST-box_ops-v02.patch"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="0002-Add-operator-box-point-to-GiST-box_ops-v02.patch"