From cff9fbdcd9d3633a28665c0636e6d3a5a0e8512b Mon Sep 17 00:00:00 2001 From: Nikita Glukhov Date: Thu, 7 Mar 2019 20:22:49 +0300 Subject: [PATCH 1/3] Add operator <->(box, point) --- 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