Attached 3rd version of the patches.
On 02.07.2019 21:55, Alexander Korotkov wrote:
On Tue, Jul 2, 2019 at 9:19 PM Nikita Glukhov<n.glu...@postgrespro.ru> wrote:
We could use commuted "const <-> var" operators for kNN searches, but the
current implementation requires the existence of "var <-> const" operators, and
order-by-op clauses are rebuilt using them (see match_clause_to_ordering_op()
at /src/backend/optimizer/path/indxpath.c).
But probably it's still worth to just add commutator for every <->
operator and close this question. Otherwise, it may arise again once
we want to add some more kNN support to opclasses or something. On
the other hand, are we already going to limit oid consumption?
All missing distance operators were added to the first patch.
On 08.07.2019 18:22, Alexander Korotkov wrote:
On Mon, Mar 11, 2019 at 2:49 AM Nikita Glukhov <n.glu...@postgrespro.ru> wrote:
2. Add <-> to GiST box_ops.
Extracted gist_box_distance_helper() common for gist_box_distance() and
gist_bbox_distance().
For me it doesn't look worth having two distinct functions
gist_box_distance_helper() and gist_bbox_distance(). What about
having just one and leave responsibility for recheck flag to the
caller?
gist_bbox_distance() was removed.
But maybe it would be better to replace two identical functions
gist_circle_distance() and gist_poly_distance() with the single
gist_bbox_distance()?
3. Add <-> to SP-GiST.
Changed only catalog and tests. Box case is already checked in
spg_box_quad_leaf_consistent():
out->recheckDistances = distfnoid == F_DIST_POLYP;
So, it seems to be fix of oversight in 2a6368343ff4. But assuming
fixing this requires catalog changes, we shouldn't backpatch this.
--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
>From 14a0e22e1b68b084dd75a7f660955b52b6aaae79 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.glu...@postgrespro.ru>
Date: Mon, 8 Jul 2019 22:55:00 +0300
Subject: [PATCH 1/3] Add missing distance operators
---
src/backend/utils/adt/geo_ops.c | 136 ++++-
src/include/catalog/pg_operator.dat | 42 +-
src/include/catalog/pg_proc.dat | 25 +
src/test/regress/expected/geometry.out | 986 +++++++++++++++++----------------
src/test/regress/sql/geometry.sql | 15 +-
5 files changed, 687 insertions(+), 517 deletions(-)
diff --git a/src/backend/utils/adt/geo_ops.c b/src/backend/utils/adt/geo_ops.c
index 373784f..6558ea3 100644
--- a/src/backend/utils/adt/geo_ops.c
+++ b/src/backend/utils/adt/geo_ops.c
@@ -2348,6 +2348,17 @@ dist_pl(PG_FUNCTION_ARGS)
PG_RETURN_FLOAT8(line_closept_point(NULL, line, pt));
}
+/*
+ * Distance from a line to a point
+ */
+Datum
+dist_lp(PG_FUNCTION_ARGS)
+{
+ LINE *line = PG_GETARG_LINE_P(0);
+ Point *pt = PG_GETARG_POINT_P(1);
+
+ PG_RETURN_FLOAT8(line_closept_point(NULL, line, pt));
+}
/*
* Distance from a point to a lseg
@@ -2362,13 +2373,20 @@ dist_ps(PG_FUNCTION_ARGS)
}
/*
- * Distance from a point to a path
+ * Distance from a lseg to a point
*/
Datum
-dist_ppath(PG_FUNCTION_ARGS)
+dist_sp(PG_FUNCTION_ARGS)
+{
+ LSEG *lseg = PG_GETARG_LSEG_P(0);
+ Point *pt = PG_GETARG_POINT_P(1);
+
+ PG_RETURN_FLOAT8(lseg_closept_point(NULL, lseg, pt));
+}
+
+static float8
+dist_ppath_internal(Point *pt, PATH *path)
{
- Point *pt = PG_GETARG_POINT_P(0);
- PATH *path = PG_GETARG_PATH_P(1);
float8 result = 0.0; /* keep compiler quiet */
bool have_min = false;
float8 tmp;
@@ -2403,7 +2421,31 @@ dist_ppath(PG_FUNCTION_ARGS)
}
}
- PG_RETURN_FLOAT8(result);
+ return result;
+}
+
+/*
+ * Distance from a point to a path
+ */
+Datum
+dist_ppath(PG_FUNCTION_ARGS)
+{
+ Point *pt = PG_GETARG_POINT_P(0);
+ PATH *path = PG_GETARG_PATH_P(1);
+
+ PG_RETURN_FLOAT8(dist_ppath_internal(pt, path));
+}
+
+/*
+ * Distance from a path to a point
+ */
+Datum
+dist_pathp(PG_FUNCTION_ARGS)
+{
+ PATH *path = PG_GETARG_PATH_P(0);
+ Point *pt = PG_GETARG_POINT_P(1);
+
+ PG_RETURN_FLOAT8(dist_ppath_internal(pt, path));
}
/*
@@ -2419,6 +2461,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
@@ -2431,6 +2485,18 @@ dist_sl(PG_FUNCTION_ARGS)
}
/*
+ * Distance from a line to a lseg
+ */
+Datum
+dist_ls(PG_FUNCTION_ARGS)
+{
+ LINE *line = PG_GETARG_LINE_P(0);
+ LSEG *lseg = PG_GETARG_LSEG_P(1);
+
+ PG_RETURN_FLOAT8(lseg_closept_line(NULL, lseg, line));
+}
+
+/*
* Distance from a lseg to a box
*/
Datum
@@ -2443,6 +2509,18 @@ dist_sb(PG_FUNCTION_ARGS)
}
/*
+ * Distance from a box to a lseg
+ */
+Datum
+dist_bs(PG_FUNCTION_ARGS)
+{
+ BOX *box = PG_GETARG_BOX_P(0);
+ LSEG *lseg = PG_GETARG_LSEG_P(1);
+
+ PG_RETURN_FLOAT8(box_closept_lseg(NULL, box, lseg));
+}
+
+/*
* Distance from a line to a box
*/
Datum
@@ -2462,13 +2540,27 @@ dist_lb(PG_FUNCTION_ARGS)
}
/*
- * Distance from a circle to a polygon
+ * Distance from a box to a line
*/
Datum
-dist_cpoly(PG_FUNCTION_ARGS)
+dist_bl(PG_FUNCTION_ARGS)
+{
+#ifdef NOT_USED
+ BOX *box = PG_GETARG_BOX_P(0);
+ LINE *line = PG_GETARG_LINE_P(1);
+#endif
+
+ /* need to think about this one for a while */
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("function \"dist_bl\" not implemented")));
+
+ PG_RETURN_NULL();
+}
+
+static float8
+dist_cpoly_internal(CIRCLE *circle, POLYGON *poly)
{
- CIRCLE *circle = PG_GETARG_CIRCLE_P(0);
- POLYGON *poly = PG_GETARG_POLYGON_P(1);
float8 result;
/* calculate distance to center, and subtract radius */
@@ -2477,7 +2569,31 @@ dist_cpoly(PG_FUNCTION_ARGS)
if (result < 0.0)
result = 0.0;
- PG_RETURN_FLOAT8(result);
+ return result;
+}
+
+/*
+ * Distance from a circle to a polygon
+ */
+Datum
+dist_cpoly(PG_FUNCTION_ARGS)
+{
+ CIRCLE *circle = PG_GETARG_CIRCLE_P(0);
+ POLYGON *poly = PG_GETARG_POLYGON_P(1);
+
+ PG_RETURN_FLOAT8(dist_cpoly_internal(circle, poly));
+}
+
+/*
+ * Distance from a polygon to a circle
+ */
+Datum
+dist_polyc(PG_FUNCTION_ARGS)
+{
+ POLYGON *poly = PG_GETARG_POLYGON_P(0);
+ CIRCLE *circle = PG_GETARG_CIRCLE_P(1);
+
+ PG_RETURN_FLOAT8(dist_cpoly_internal(circle, poly));
}
/*
diff --git a/src/include/catalog/pg_operator.dat b/src/include/catalog/pg_operator.dat
index bacafa5..96823cd 100644
--- a/src/include/catalog/pg_operator.dat
+++ b/src/include/catalog/pg_operator.dat
@@ -660,22 +660,40 @@
{ oid => '613', descr => 'distance between',
oprname => '<->', oprleft => 'point', oprright => 'line',
- oprresult => 'float8', oprcode => 'dist_pl' },
+ oprresult => 'float8', oprcom => '<->(line,point)',oprcode => 'dist_pl' },
+{ oid => '760', descr => 'distance between',
+ oprname => '<->', oprleft => 'line', oprright => 'point',
+ oprresult => 'float8', oprcom => '<->(point,line)', oprcode => 'dist_lp' },
{ oid => '614', descr => 'distance between',
oprname => '<->', oprleft => 'point', oprright => 'lseg',
- oprresult => 'float8', oprcode => 'dist_ps' },
+ oprresult => 'float8', oprcom => '<->(lseg,point)',oprcode => 'dist_ps' },
+{ oid => '761', descr => 'distance between',
+ oprname => '<->', oprleft => 'lseg', oprright => 'point',
+ oprresult => 'float8', oprcom => '<->(point,lseg)', oprcode => 'dist_sp' },
{ 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' },
+ oprresult => 'float8', oprcom => '<->(line,lseg)', oprcode => 'dist_sl' },
+{ oid => '762', descr => 'distance between',
+ oprname => '<->', oprleft => 'line', oprright => 'lseg',
+ oprresult => 'float8', oprcom => '<->(lseg,line)', oprcode => 'dist_ls' },
{ oid => '617', descr => 'distance between',
oprname => '<->', oprleft => 'lseg', oprright => 'box', oprresult => 'float8',
- oprcode => 'dist_sb' },
+ oprcom => '<->(box,lseg)', oprcode => 'dist_sb' },
+{ oid => '763', descr => 'distance between',
+ oprname => '<->', oprleft => 'box', oprright => 'lseg', oprresult => 'float8',
+ oprcom => '<->(lseg,box)', oprcode => 'dist_bs' },
{ oid => '618', descr => 'distance between',
oprname => '<->', oprleft => 'point', oprright => 'path',
- oprresult => 'float8', oprcode => 'dist_ppath' },
+ oprresult => 'float8', oprcom => '<->(path,point)', oprcode => 'dist_ppath' },
+{ oid => '784', descr => 'distance between',
+ oprname => '<->', oprleft => 'path', oprright => 'point',
+ oprresult => 'float8', oprcom => '<->(point,path)', oprcode => 'dist_pathp' },
{ oid => '620', descr => 'equal',
oprname => '=', oprcanmerge => 't', oprcanhash => 't', oprleft => 'float4',
@@ -1692,12 +1710,20 @@
oprcode => 'dist_polyp' },
{ oid => '1523', descr => 'distance between',
oprname => '<->', oprleft => 'circle', oprright => 'polygon',
- oprresult => 'float8', oprcode => 'dist_cpoly' },
+ oprresult => 'float8', oprcom => '<->(polygon,circle)',
+ oprcode => 'dist_cpoly' },
+{ oid => '1383', descr => 'distance between',
+ oprname => '<->', oprleft => 'polygon', oprright => 'circle',
+ oprresult => 'float8', oprcom => '<->(circle,polygon)',
+ oprcode => 'dist_polyc' },
# additional geometric operators - thomas 1997-07-09
{ oid => '1524', descr => 'distance between',
oprname => '<->', oprleft => 'line', oprright => 'box', oprresult => 'float8',
- oprcode => 'dist_lb' },
+ oprcom => '<->(box,line)', oprcode => 'dist_lb' },
+{ oid => '1382', descr => 'distance between',
+ oprname => '<->', oprleft => 'box', oprright => 'line', oprresult => 'float8',
+ oprcom => '<->(line,box)', oprcode => 'dist_bl' },
{ oid => '1525', descr => 'intersect',
oprname => '?#', oprleft => 'lseg', oprright => 'lseg', oprresult => 'bool',
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 604470c..e0852b8 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1062,12 +1062,21 @@
{ oid => '363',
proname => 'dist_ps', prorettype => 'float8', proargtypes => 'point lseg',
prosrc => 'dist_ps' },
+{ oid => '380',
+ proname => 'dist_sp', prorettype => 'float8', proargtypes => 'lseg point',
+ prosrc => 'dist_sp' },
{ 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' },
+{ oid => '381',
+ proname => 'dist_bs', prorettype => 'float8', proargtypes => 'box lseg',
+ prosrc => 'dist_bs' },
{ oid => '366',
proname => 'close_ps', prorettype => 'point', proargtypes => 'point lseg',
prosrc => 'close_ps' },
@@ -1086,6 +1095,9 @@
{ oid => '371',
proname => 'dist_ppath', prorettype => 'float8', proargtypes => 'point path',
prosrc => 'dist_ppath' },
+{ oid => '421',
+ proname => 'dist_pathp', prorettype => 'float8', proargtypes => 'path point',
+ prosrc => 'dist_pathp' },
{ oid => '372',
proname => 'on_sb', prorettype => 'bool', proargtypes => 'lseg box',
prosrc => 'on_sb' },
@@ -1403,15 +1415,28 @@
{ oid => '725',
proname => 'dist_pl', prorettype => 'float8', proargtypes => 'point line',
prosrc => 'dist_pl' },
+{ oid => '702',
+ proname => 'dist_lp', prorettype => 'float8', proargtypes => 'line point',
+ prosrc => 'dist_lp' },
{ oid => '726',
proname => 'dist_lb', prorettype => 'float8', proargtypes => 'line box',
prosrc => 'dist_lb' },
+{ oid => '703',
+ proname => 'dist_bl', prorettype => 'float8', proargtypes => 'box line',
+ prosrc => 'dist_bl' },
{ oid => '727',
proname => 'dist_sl', prorettype => 'float8', proargtypes => 'lseg line',
prosrc => 'dist_sl' },
+{ oid => '704',
+ proname => 'dist_ls', prorettype => 'float8', proargtypes => 'line lseg',
+ prosrc => 'dist_ls' },
+
{ oid => '728',
proname => 'dist_cpoly', prorettype => 'float8',
proargtypes => 'circle polygon', prosrc => 'dist_cpoly' },
+{ oid => '785',
+ proname => 'dist_polyc', prorettype => 'float8',
+ proargtypes => 'polygon circle', prosrc => 'dist_polyc' },
{ oid => '729',
proname => 'poly_distance', prorettype => 'float8',
proargtypes => 'polygon polygon', prosrc => 'poly_distance' },
diff --git a/src/test/regress/expected/geometry.out b/src/test/regress/expected/geometry.out
index 055d32c..5b9d370 100644
--- a/src/test/regress/expected/geometry.out
+++ b/src/test/regress/expected/geometry.out
@@ -428,384 +428,384 @@ ERROR: value out of range: overflow
SELECT p1.f1, p2.f1, p1.f1 / p2.f1 FROM POINT_TBL p1, POINT_TBL p2 WHERE p2.f1 ~= '(0,0)'::point;
ERROR: division by zero
-- Distance to line
-SELECT p.f1, l.s, p.f1 <-> l.s FROM POINT_TBL p, LINE_TBL l;
- f1 | s | ?column?
--------------------+---------------------------------------+--------------------
- (0,0) | {0,-1,5} | 5
- (0,0) | {1,0,5} | 5
- (0,0) | {0,3,0} | 0
- (0,0) | {1,-1,0} | 0
- (0,0) | {-0.4,-1,-6} | 5.57086014531
- (0,0) | {-0.000184615384615,-1,15.3846153846} | 15.3846151224
- (0,0) | {3,NaN,5} | NaN
- (0,0) | {NaN,NaN,NaN} | NaN
- (0,0) | {0,-1,3} | 3
- (0,0) | {-1,0,3} | 3
- (-10,0) | {0,-1,5} | 5
- (-10,0) | {1,0,5} | 5
- (-10,0) | {0,3,0} | 0
- (-10,0) | {1,-1,0} | 7.07106781187
- (-10,0) | {-0.4,-1,-6} | 1.85695338177
- (-10,0) | {-0.000184615384615,-1,15.3846153846} | 15.3864612763
- (-10,0) | {3,NaN,5} | NaN
- (-10,0) | {NaN,NaN,NaN} | NaN
- (-10,0) | {0,-1,3} | 3
- (-10,0) | {-1,0,3} | 13
- (-3,4) | {0,-1,5} | 1
- (-3,4) | {1,0,5} | 2
- (-3,4) | {0,3,0} | 4
- (-3,4) | {1,-1,0} | 4.94974746831
- (-3,4) | {-0.4,-1,-6} | 8.17059487979
- (-3,4) | {-0.000184615384615,-1,15.3846153846} | 11.3851690368
- (-3,4) | {3,NaN,5} | NaN
- (-3,4) | {NaN,NaN,NaN} | NaN
- (-3,4) | {0,-1,3} | 1
- (-3,4) | {-1,0,3} | 6
- (5.1,34.5) | {0,-1,5} | 29.5
- (5.1,34.5) | {1,0,5} | 10.1
- (5.1,34.5) | {0,3,0} | 34.5
- (5.1,34.5) | {1,-1,0} | 20.7889393669
- (5.1,34.5) | {-0.4,-1,-6} | 39.4973984303
- (5.1,34.5) | {-0.000184615384615,-1,15.3846153846} | 19.1163258281
- (5.1,34.5) | {3,NaN,5} | NaN
- (5.1,34.5) | {NaN,NaN,NaN} | NaN
- (5.1,34.5) | {0,-1,3} | 31.5
- (5.1,34.5) | {-1,0,3} | 2.1
- (-5,-12) | {0,-1,5} | 17
- (-5,-12) | {1,0,5} | 0
- (-5,-12) | {0,3,0} | 12
- (-5,-12) | {1,-1,0} | 4.94974746831
- (-5,-12) | {-0.4,-1,-6} | 7.42781352708
- (-5,-12) | {-0.000184615384615,-1,15.3846153846} | 27.3855379948
- (-5,-12) | {3,NaN,5} | NaN
- (-5,-12) | {NaN,NaN,NaN} | NaN
- (-5,-12) | {0,-1,3} | 15
- (-5,-12) | {-1,0,3} | 8
- (1e-300,-1e-300) | {0,-1,5} | 5
- (1e-300,-1e-300) | {1,0,5} | 5
- (1e-300,-1e-300) | {0,3,0} | 1e-300
- (1e-300,-1e-300) | {1,-1,0} | 1.41421356237e-300
- (1e-300,-1e-300) | {-0.4,-1,-6} | 5.57086014531
- (1e-300,-1e-300) | {-0.000184615384615,-1,15.3846153846} | 15.3846151224
- (1e-300,-1e-300) | {3,NaN,5} | NaN
- (1e-300,-1e-300) | {NaN,NaN,NaN} | NaN
- (1e-300,-1e-300) | {0,-1,3} | 3
- (1e-300,-1e-300) | {-1,0,3} | 3
- (1e+300,Infinity) | {0,-1,5} | Infinity
- (1e+300,Infinity) | {1,0,5} | NaN
- (1e+300,Infinity) | {0,3,0} | Infinity
- (1e+300,Infinity) | {1,-1,0} | Infinity
- (1e+300,Infinity) | {-0.4,-1,-6} | Infinity
- (1e+300,Infinity) | {-0.000184615384615,-1,15.3846153846} | Infinity
- (1e+300,Infinity) | {3,NaN,5} | NaN
- (1e+300,Infinity) | {NaN,NaN,NaN} | NaN
- (1e+300,Infinity) | {0,-1,3} | Infinity
- (1e+300,Infinity) | {-1,0,3} | NaN
- (NaN,NaN) | {0,-1,5} | NaN
- (NaN,NaN) | {1,0,5} | NaN
- (NaN,NaN) | {0,3,0} | NaN
- (NaN,NaN) | {1,-1,0} | NaN
- (NaN,NaN) | {-0.4,-1,-6} | NaN
- (NaN,NaN) | {-0.000184615384615,-1,15.3846153846} | NaN
- (NaN,NaN) | {3,NaN,5} | NaN
- (NaN,NaN) | {NaN,NaN,NaN} | NaN
- (NaN,NaN) | {0,-1,3} | NaN
- (NaN,NaN) | {-1,0,3} | NaN
- (10,10) | {0,-1,5} | 5
- (10,10) | {1,0,5} | 15
- (10,10) | {0,3,0} | 10
- (10,10) | {1,-1,0} | 0
- (10,10) | {-0.4,-1,-6} | 18.5695338177
- (10,10) | {-0.000184615384615,-1,15.3846153846} | 5.38276913903
- (10,10) | {3,NaN,5} | NaN
- (10,10) | {NaN,NaN,NaN} | NaN
- (10,10) | {0,-1,3} | 7
- (10,10) | {-1,0,3} | 7
+SELECT p.f1, l.s, p.f1 <-> l.s AS dist_pl, l.s <-> p.f1 AS dist_lp FROM POINT_TBL p, LINE_TBL l;
+ f1 | s | dist_pl | dist_lp
+-------------------+---------------------------------------+--------------------+--------------------
+ (0,0) | {0,-1,5} | 5 | 5
+ (0,0) | {1,0,5} | 5 | 5
+ (0,0) | {0,3,0} | 0 | 0
+ (0,0) | {1,-1,0} | 0 | 0
+ (0,0) | {-0.4,-1,-6} | 5.57086014531 | 5.57086014531
+ (0,0) | {-0.000184615384615,-1,15.3846153846} | 15.3846151224 | 15.3846151224
+ (0,0) | {3,NaN,5} | NaN | NaN
+ (0,0) | {NaN,NaN,NaN} | NaN | NaN
+ (0,0) | {0,-1,3} | 3 | 3
+ (0,0) | {-1,0,3} | 3 | 3
+ (-10,0) | {0,-1,5} | 5 | 5
+ (-10,0) | {1,0,5} | 5 | 5
+ (-10,0) | {0,3,0} | 0 | 0
+ (-10,0) | {1,-1,0} | 7.07106781187 | 7.07106781187
+ (-10,0) | {-0.4,-1,-6} | 1.85695338177 | 1.85695338177
+ (-10,0) | {-0.000184615384615,-1,15.3846153846} | 15.3864612763 | 15.3864612763
+ (-10,0) | {3,NaN,5} | NaN | NaN
+ (-10,0) | {NaN,NaN,NaN} | NaN | NaN
+ (-10,0) | {0,-1,3} | 3 | 3
+ (-10,0) | {-1,0,3} | 13 | 13
+ (-3,4) | {0,-1,5} | 1 | 1
+ (-3,4) | {1,0,5} | 2 | 2
+ (-3,4) | {0,3,0} | 4 | 4
+ (-3,4) | {1,-1,0} | 4.94974746831 | 4.94974746831
+ (-3,4) | {-0.4,-1,-6} | 8.17059487979 | 8.17059487979
+ (-3,4) | {-0.000184615384615,-1,15.3846153846} | 11.3851690368 | 11.3851690368
+ (-3,4) | {3,NaN,5} | NaN | NaN
+ (-3,4) | {NaN,NaN,NaN} | NaN | NaN
+ (-3,4) | {0,-1,3} | 1 | 1
+ (-3,4) | {-1,0,3} | 6 | 6
+ (5.1,34.5) | {0,-1,5} | 29.5 | 29.5
+ (5.1,34.5) | {1,0,5} | 10.1 | 10.1
+ (5.1,34.5) | {0,3,0} | 34.5 | 34.5
+ (5.1,34.5) | {1,-1,0} | 20.7889393669 | 20.7889393669
+ (5.1,34.5) | {-0.4,-1,-6} | 39.4973984303 | 39.4973984303
+ (5.1,34.5) | {-0.000184615384615,-1,15.3846153846} | 19.1163258281 | 19.1163258281
+ (5.1,34.5) | {3,NaN,5} | NaN | NaN
+ (5.1,34.5) | {NaN,NaN,NaN} | NaN | NaN
+ (5.1,34.5) | {0,-1,3} | 31.5 | 31.5
+ (5.1,34.5) | {-1,0,3} | 2.1 | 2.1
+ (-5,-12) | {0,-1,5} | 17 | 17
+ (-5,-12) | {1,0,5} | 0 | 0
+ (-5,-12) | {0,3,0} | 12 | 12
+ (-5,-12) | {1,-1,0} | 4.94974746831 | 4.94974746831
+ (-5,-12) | {-0.4,-1,-6} | 7.42781352708 | 7.42781352708
+ (-5,-12) | {-0.000184615384615,-1,15.3846153846} | 27.3855379948 | 27.3855379948
+ (-5,-12) | {3,NaN,5} | NaN | NaN
+ (-5,-12) | {NaN,NaN,NaN} | NaN | NaN
+ (-5,-12) | {0,-1,3} | 15 | 15
+ (-5,-12) | {-1,0,3} | 8 | 8
+ (1e-300,-1e-300) | {0,-1,5} | 5 | 5
+ (1e-300,-1e-300) | {1,0,5} | 5 | 5
+ (1e-300,-1e-300) | {0,3,0} | 1e-300 | 1e-300
+ (1e-300,-1e-300) | {1,-1,0} | 1.41421356237e-300 | 1.41421356237e-300
+ (1e-300,-1e-300) | {-0.4,-1,-6} | 5.57086014531 | 5.57086014531
+ (1e-300,-1e-300) | {-0.000184615384615,-1,15.3846153846} | 15.3846151224 | 15.3846151224
+ (1e-300,-1e-300) | {3,NaN,5} | NaN | NaN
+ (1e-300,-1e-300) | {NaN,NaN,NaN} | NaN | NaN
+ (1e-300,-1e-300) | {0,-1,3} | 3 | 3
+ (1e-300,-1e-300) | {-1,0,3} | 3 | 3
+ (1e+300,Infinity) | {0,-1,5} | Infinity | Infinity
+ (1e+300,Infinity) | {1,0,5} | NaN | NaN
+ (1e+300,Infinity) | {0,3,0} | Infinity | Infinity
+ (1e+300,Infinity) | {1,-1,0} | Infinity | Infinity
+ (1e+300,Infinity) | {-0.4,-1,-6} | Infinity | Infinity
+ (1e+300,Infinity) | {-0.000184615384615,-1,15.3846153846} | Infinity | Infinity
+ (1e+300,Infinity) | {3,NaN,5} | NaN | NaN
+ (1e+300,Infinity) | {NaN,NaN,NaN} | NaN | NaN
+ (1e+300,Infinity) | {0,-1,3} | Infinity | Infinity
+ (1e+300,Infinity) | {-1,0,3} | NaN | NaN
+ (NaN,NaN) | {0,-1,5} | NaN | NaN
+ (NaN,NaN) | {1,0,5} | NaN | NaN
+ (NaN,NaN) | {0,3,0} | NaN | NaN
+ (NaN,NaN) | {1,-1,0} | NaN | NaN
+ (NaN,NaN) | {-0.4,-1,-6} | NaN | NaN
+ (NaN,NaN) | {-0.000184615384615,-1,15.3846153846} | NaN | NaN
+ (NaN,NaN) | {3,NaN,5} | NaN | NaN
+ (NaN,NaN) | {NaN,NaN,NaN} | NaN | NaN
+ (NaN,NaN) | {0,-1,3} | NaN | NaN
+ (NaN,NaN) | {-1,0,3} | NaN | NaN
+ (10,10) | {0,-1,5} | 5 | 5
+ (10,10) | {1,0,5} | 15 | 15
+ (10,10) | {0,3,0} | 10 | 10
+ (10,10) | {1,-1,0} | 0 | 0
+ (10,10) | {-0.4,-1,-6} | 18.5695338177 | 18.5695338177
+ (10,10) | {-0.000184615384615,-1,15.3846153846} | 5.38276913903 | 5.38276913903
+ (10,10) | {3,NaN,5} | NaN | NaN
+ (10,10) | {NaN,NaN,NaN} | NaN | NaN
+ (10,10) | {0,-1,3} | 7 | 7
+ (10,10) | {-1,0,3} | 7 | 7
(90 rows)
-- Distance to line segment
-SELECT p.f1, l.s, p.f1 <-> l.s FROM POINT_TBL p, LSEG_TBL l;
- f1 | s | ?column?
--------------------+-------------------------------+--------------------
- (0,0) | [(1,2),(3,4)] | 2.2360679775
- (0,0) | [(0,0),(6,6)] | 0
- (0,0) | [(10,-10),(-3,-4)] | 4.88901207039
- (0,0) | [(-1000000,200),(300000,-40)] | 15.3846151224
- (0,0) | [(11,22),(33,44)] | 24.5967477525
- (0,0) | [(-10,2),(-10,3)] | 10.1980390272
- (0,0) | [(0,-20),(30,-20)] | 20
- (0,0) | [(NaN,1),(NaN,90)] | NaN
- (-10,0) | [(1,2),(3,4)] | 11.1803398875
- (-10,0) | [(0,0),(6,6)] | 10
- (-10,0) | [(10,-10),(-3,-4)] | 8.0622577483
- (-10,0) | [(-1000000,200),(300000,-40)] | 15.3864612763
- (-10,0) | [(11,22),(33,44)] | 30.4138126515
- (-10,0) | [(-10,2),(-10,3)] | 2
- (-10,0) | [(0,-20),(30,-20)] | 22.360679775
- (-10,0) | [(NaN,1),(NaN,90)] | NaN
- (-3,4) | [(1,2),(3,4)] | 4.472135955
- (-3,4) | [(0,0),(6,6)] | 4.94974746831
- (-3,4) | [(10,-10),(-3,-4)] | 8
- (-3,4) | [(-1000000,200),(300000,-40)] | 11.3851690367
- (-3,4) | [(11,22),(33,44)] | 22.803508502
- (-3,4) | [(-10,2),(-10,3)] | 7.07106781187
- (-3,4) | [(0,-20),(30,-20)] | 24.1867732449
- (-3,4) | [(NaN,1),(NaN,90)] | NaN
- (5.1,34.5) | [(1,2),(3,4)] | 30.5722096028
- (5.1,34.5) | [(0,0),(6,6)] | 28.5142069853
- (5.1,34.5) | [(10,-10),(-3,-4)] | 39.3428519556
- (5.1,34.5) | [(-1000000,200),(300000,-40)] | 19.1163258281
- (5.1,34.5) | [(11,22),(33,44)] | 13.0107647738
- (5.1,34.5) | [(-10,2),(-10,3)] | 34.932220084
- (5.1,34.5) | [(0,-20),(30,-20)] | 54.5
- (5.1,34.5) | [(NaN,1),(NaN,90)] | NaN
- (-5,-12) | [(1,2),(3,4)] | 15.2315462117
- (-5,-12) | [(0,0),(6,6)] | 13
- (-5,-12) | [(10,-10),(-3,-4)] | 8.10179143093
- (-5,-12) | [(-1000000,200),(300000,-40)] | 27.3855379949
- (-5,-12) | [(11,22),(33,44)] | 37.5765884561
- (-5,-12) | [(-10,2),(-10,3)] | 14.8660687473
- (-5,-12) | [(0,-20),(30,-20)] | 9.43398113206
- (-5,-12) | [(NaN,1),(NaN,90)] | NaN
- (1e-300,-1e-300) | [(1,2),(3,4)] | 2.2360679775
- (1e-300,-1e-300) | [(0,0),(6,6)] | 1.41421356237e-300
- (1e-300,-1e-300) | [(10,-10),(-3,-4)] | 4.88901207039
- (1e-300,-1e-300) | [(-1000000,200),(300000,-40)] | 15.3846151224
- (1e-300,-1e-300) | [(11,22),(33,44)] | 24.5967477525
- (1e-300,-1e-300) | [(-10,2),(-10,3)] | 10.1980390272
- (1e-300,-1e-300) | [(0,-20),(30,-20)] | 20
- (1e-300,-1e-300) | [(NaN,1),(NaN,90)] | NaN
- (1e+300,Infinity) | [(1,2),(3,4)] | Infinity
- (1e+300,Infinity) | [(0,0),(6,6)] | Infinity
- (1e+300,Infinity) | [(10,-10),(-3,-4)] | Infinity
- (1e+300,Infinity) | [(-1000000,200),(300000,-40)] | Infinity
- (1e+300,Infinity) | [(11,22),(33,44)] | Infinity
- (1e+300,Infinity) | [(-10,2),(-10,3)] | Infinity
- (1e+300,Infinity) | [(0,-20),(30,-20)] | Infinity
- (1e+300,Infinity) | [(NaN,1),(NaN,90)] | Infinity
- (NaN,NaN) | [(1,2),(3,4)] | NaN
- (NaN,NaN) | [(0,0),(6,6)] | NaN
- (NaN,NaN) | [(10,-10),(-3,-4)] | NaN
- (NaN,NaN) | [(-1000000,200),(300000,-40)] | NaN
- (NaN,NaN) | [(11,22),(33,44)] | NaN
- (NaN,NaN) | [(-10,2),(-10,3)] | NaN
- (NaN,NaN) | [(0,-20),(30,-20)] | NaN
- (NaN,NaN) | [(NaN,1),(NaN,90)] | NaN
- (10,10) | [(1,2),(3,4)] | 9.21954445729
- (10,10) | [(0,0),(6,6)] | 5.65685424949
- (10,10) | [(10,-10),(-3,-4)] | 18.15918769
- (10,10) | [(-1000000,200),(300000,-40)] | 5.38276913904
- (10,10) | [(11,22),(33,44)] | 12.0415945788
- (10,10) | [(-10,2),(-10,3)] | 21.1896201004
- (10,10) | [(0,-20),(30,-20)] | 30
- (10,10) | [(NaN,1),(NaN,90)] | NaN
+SELECT p.f1, l.s, p.f1 <-> l.s AS dist_ps, l.s <-> p.f1 AS dist_sp FROM POINT_TBL p, LSEG_TBL l;
+ f1 | s | dist_ps | dist_sp
+-------------------+-------------------------------+--------------------+--------------------
+ (0,0) | [(1,2),(3,4)] | 2.2360679775 | 2.2360679775
+ (0,0) | [(0,0),(6,6)] | 0 | 0
+ (0,0) | [(10,-10),(-3,-4)] | 4.88901207039 | 4.88901207039
+ (0,0) | [(-1000000,200),(300000,-40)] | 15.3846151224 | 15.3846151224
+ (0,0) | [(11,22),(33,44)] | 24.5967477525 | 24.5967477525
+ (0,0) | [(-10,2),(-10,3)] | 10.1980390272 | 10.1980390272
+ (0,0) | [(0,-20),(30,-20)] | 20 | 20
+ (0,0) | [(NaN,1),(NaN,90)] | NaN | NaN
+ (-10,0) | [(1,2),(3,4)] | 11.1803398875 | 11.1803398875
+ (-10,0) | [(0,0),(6,6)] | 10 | 10
+ (-10,0) | [(10,-10),(-3,-4)] | 8.0622577483 | 8.0622577483
+ (-10,0) | [(-1000000,200),(300000,-40)] | 15.3864612763 | 15.3864612763
+ (-10,0) | [(11,22),(33,44)] | 30.4138126515 | 30.4138126515
+ (-10,0) | [(-10,2),(-10,3)] | 2 | 2
+ (-10,0) | [(0,-20),(30,-20)] | 22.360679775 | 22.360679775
+ (-10,0) | [(NaN,1),(NaN,90)] | NaN | NaN
+ (-3,4) | [(1,2),(3,4)] | 4.472135955 | 4.472135955
+ (-3,4) | [(0,0),(6,6)] | 4.94974746831 | 4.94974746831
+ (-3,4) | [(10,-10),(-3,-4)] | 8 | 8
+ (-3,4) | [(-1000000,200),(300000,-40)] | 11.3851690367 | 11.3851690367
+ (-3,4) | [(11,22),(33,44)] | 22.803508502 | 22.803508502
+ (-3,4) | [(-10,2),(-10,3)] | 7.07106781187 | 7.07106781187
+ (-3,4) | [(0,-20),(30,-20)] | 24.1867732449 | 24.1867732449
+ (-3,4) | [(NaN,1),(NaN,90)] | NaN | NaN
+ (5.1,34.5) | [(1,2),(3,4)] | 30.5722096028 | 30.5722096028
+ (5.1,34.5) | [(0,0),(6,6)] | 28.5142069853 | 28.5142069853
+ (5.1,34.5) | [(10,-10),(-3,-4)] | 39.3428519556 | 39.3428519556
+ (5.1,34.5) | [(-1000000,200),(300000,-40)] | 19.1163258281 | 19.1163258281
+ (5.1,34.5) | [(11,22),(33,44)] | 13.0107647738 | 13.0107647738
+ (5.1,34.5) | [(-10,2),(-10,3)] | 34.932220084 | 34.932220084
+ (5.1,34.5) | [(0,-20),(30,-20)] | 54.5 | 54.5
+ (5.1,34.5) | [(NaN,1),(NaN,90)] | NaN | NaN
+ (-5,-12) | [(1,2),(3,4)] | 15.2315462117 | 15.2315462117
+ (-5,-12) | [(0,0),(6,6)] | 13 | 13
+ (-5,-12) | [(10,-10),(-3,-4)] | 8.10179143093 | 8.10179143093
+ (-5,-12) | [(-1000000,200),(300000,-40)] | 27.3855379949 | 27.3855379949
+ (-5,-12) | [(11,22),(33,44)] | 37.5765884561 | 37.5765884561
+ (-5,-12) | [(-10,2),(-10,3)] | 14.8660687473 | 14.8660687473
+ (-5,-12) | [(0,-20),(30,-20)] | 9.43398113206 | 9.43398113206
+ (-5,-12) | [(NaN,1),(NaN,90)] | NaN | NaN
+ (1e-300,-1e-300) | [(1,2),(3,4)] | 2.2360679775 | 2.2360679775
+ (1e-300,-1e-300) | [(0,0),(6,6)] | 1.41421356237e-300 | 1.41421356237e-300
+ (1e-300,-1e-300) | [(10,-10),(-3,-4)] | 4.88901207039 | 4.88901207039
+ (1e-300,-1e-300) | [(-1000000,200),(300000,-40)] | 15.3846151224 | 15.3846151224
+ (1e-300,-1e-300) | [(11,22),(33,44)] | 24.5967477525 | 24.5967477525
+ (1e-300,-1e-300) | [(-10,2),(-10,3)] | 10.1980390272 | 10.1980390272
+ (1e-300,-1e-300) | [(0,-20),(30,-20)] | 20 | 20
+ (1e-300,-1e-300) | [(NaN,1),(NaN,90)] | NaN | NaN
+ (1e+300,Infinity) | [(1,2),(3,4)] | Infinity | Infinity
+ (1e+300,Infinity) | [(0,0),(6,6)] | Infinity | Infinity
+ (1e+300,Infinity) | [(10,-10),(-3,-4)] | Infinity | Infinity
+ (1e+300,Infinity) | [(-1000000,200),(300000,-40)] | Infinity | Infinity
+ (1e+300,Infinity) | [(11,22),(33,44)] | Infinity | Infinity
+ (1e+300,Infinity) | [(-10,2),(-10,3)] | Infinity | Infinity
+ (1e+300,Infinity) | [(0,-20),(30,-20)] | Infinity | Infinity
+ (1e+300,Infinity) | [(NaN,1),(NaN,90)] | Infinity | Infinity
+ (NaN,NaN) | [(1,2),(3,4)] | NaN | NaN
+ (NaN,NaN) | [(0,0),(6,6)] | NaN | NaN
+ (NaN,NaN) | [(10,-10),(-3,-4)] | NaN | NaN
+ (NaN,NaN) | [(-1000000,200),(300000,-40)] | NaN | NaN
+ (NaN,NaN) | [(11,22),(33,44)] | NaN | NaN
+ (NaN,NaN) | [(-10,2),(-10,3)] | NaN | NaN
+ (NaN,NaN) | [(0,-20),(30,-20)] | NaN | NaN
+ (NaN,NaN) | [(NaN,1),(NaN,90)] | NaN | NaN
+ (10,10) | [(1,2),(3,4)] | 9.21954445729 | 9.21954445729
+ (10,10) | [(0,0),(6,6)] | 5.65685424949 | 5.65685424949
+ (10,10) | [(10,-10),(-3,-4)] | 18.15918769 | 18.15918769
+ (10,10) | [(-1000000,200),(300000,-40)] | 5.38276913904 | 5.38276913904
+ (10,10) | [(11,22),(33,44)] | 12.0415945788 | 12.0415945788
+ (10,10) | [(-10,2),(-10,3)] | 21.1896201004 | 21.1896201004
+ (10,10) | [(0,-20),(30,-20)] | 30 | 30
+ (10,10) | [(NaN,1),(NaN,90)] | NaN | NaN
(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 dist_pb, b.f1 <-> p.f1 AS dist_bp FROM POINT_TBL p, BOX_TBL b;
+ f1 | f1 | dist_pb | dist_bp
+-------------------+---------------------+--------------------+--------------------
+ (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
-SELECT p.f1, p1.f1, p.f1 <-> p1.f1 FROM POINT_TBL p, PATH_TBL p1;
- f1 | f1 | ?column?
--------------------+---------------------------+--------------------
- (0,0) | [(1,2),(3,4)] | 2.2360679775
- (0,0) | ((1,2),(3,4)) | 2.2360679775
- (0,0) | [(0,0),(3,0),(4,5),(1,6)] | 0
- (0,0) | ((1,2),(3,4)) | 2.2360679775
- (0,0) | ((1,2),(3,4)) | 2.2360679775
- (0,0) | [(1,2),(3,4)] | 2.2360679775
- (0,0) | ((10,20)) | 22.360679775
- (0,0) | [(11,12),(13,14)] | 16.2788205961
- (0,0) | ((11,12),(13,14)) | 16.2788205961
- (-10,0) | [(1,2),(3,4)] | 11.1803398875
- (-10,0) | ((1,2),(3,4)) | 11.1803398875
- (-10,0) | [(0,0),(3,0),(4,5),(1,6)] | 10
- (-10,0) | ((1,2),(3,4)) | 11.1803398875
- (-10,0) | ((1,2),(3,4)) | 11.1803398875
- (-10,0) | [(1,2),(3,4)] | 11.1803398875
- (-10,0) | ((10,20)) | 28.2842712475
- (-10,0) | [(11,12),(13,14)] | 24.1867732449
- (-10,0) | ((11,12),(13,14)) | 24.1867732449
- (-3,4) | [(1,2),(3,4)] | 4.472135955
- (-3,4) | ((1,2),(3,4)) | 4.472135955
- (-3,4) | [(0,0),(3,0),(4,5),(1,6)] | 4.472135955
- (-3,4) | ((1,2),(3,4)) | 4.472135955
- (-3,4) | ((1,2),(3,4)) | 4.472135955
- (-3,4) | [(1,2),(3,4)] | 4.472135955
- (-3,4) | ((10,20)) | 20.6155281281
- (-3,4) | [(11,12),(13,14)] | 16.1245154966
- (-3,4) | ((11,12),(13,14)) | 16.1245154966
- (5.1,34.5) | [(1,2),(3,4)] | 30.5722096028
- (5.1,34.5) | ((1,2),(3,4)) | 30.5722096028
- (5.1,34.5) | [(0,0),(3,0),(4,5),(1,6)] | 28.793402022
- (5.1,34.5) | ((1,2),(3,4)) | 30.5722096028
- (5.1,34.5) | ((1,2),(3,4)) | 30.5722096028
- (5.1,34.5) | [(1,2),(3,4)] | 30.5722096028
- (5.1,34.5) | ((10,20)) | 15.3055545473
- (5.1,34.5) | [(11,12),(13,14)] | 21.9695243462
- (5.1,34.5) | ((11,12),(13,14)) | 21.9695243462
- (-5,-12) | [(1,2),(3,4)] | 15.2315462117
- (-5,-12) | ((1,2),(3,4)) | 15.2315462117
- (-5,-12) | [(0,0),(3,0),(4,5),(1,6)] | 13
- (-5,-12) | ((1,2),(3,4)) | 15.2315462117
- (-5,-12) | ((1,2),(3,4)) | 15.2315462117
- (-5,-12) | [(1,2),(3,4)] | 15.2315462117
- (-5,-12) | ((10,20)) | 35.3411940941
- (-5,-12) | [(11,12),(13,14)] | 28.8444102037
- (-5,-12) | ((11,12),(13,14)) | 28.8444102037
- (1e-300,-1e-300) | [(1,2),(3,4)] | 2.2360679775
- (1e-300,-1e-300) | ((1,2),(3,4)) | 2.2360679775
- (1e-300,-1e-300) | [(0,0),(3,0),(4,5),(1,6)] | 1.41421356237e-300
- (1e-300,-1e-300) | ((1,2),(3,4)) | 2.2360679775
- (1e-300,-1e-300) | ((1,2),(3,4)) | 2.2360679775
- (1e-300,-1e-300) | [(1,2),(3,4)] | 2.2360679775
- (1e-300,-1e-300) | ((10,20)) | 22.360679775
- (1e-300,-1e-300) | [(11,12),(13,14)] | 16.2788205961
- (1e-300,-1e-300) | ((11,12),(13,14)) | 16.2788205961
- (1e+300,Infinity) | [(1,2),(3,4)] | Infinity
- (1e+300,Infinity) | ((1,2),(3,4)) | Infinity
- (1e+300,Infinity) | [(0,0),(3,0),(4,5),(1,6)] | Infinity
- (1e+300,Infinity) | ((1,2),(3,4)) | Infinity
- (1e+300,Infinity) | ((1,2),(3,4)) | Infinity
- (1e+300,Infinity) | [(1,2),(3,4)] | Infinity
- (1e+300,Infinity) | ((10,20)) | Infinity
- (1e+300,Infinity) | [(11,12),(13,14)] | Infinity
- (1e+300,Infinity) | ((11,12),(13,14)) | Infinity
- (NaN,NaN) | [(1,2),(3,4)] | NaN
- (NaN,NaN) | ((1,2),(3,4)) | NaN
- (NaN,NaN) | [(0,0),(3,0),(4,5),(1,6)] | NaN
- (NaN,NaN) | ((1,2),(3,4)) | NaN
- (NaN,NaN) | ((1,2),(3,4)) | NaN
- (NaN,NaN) | [(1,2),(3,4)] | NaN
- (NaN,NaN) | ((10,20)) | NaN
- (NaN,NaN) | [(11,12),(13,14)] | NaN
- (NaN,NaN) | ((11,12),(13,14)) | NaN
- (10,10) | [(1,2),(3,4)] | 9.21954445729
- (10,10) | ((1,2),(3,4)) | 9.21954445729
- (10,10) | [(0,0),(3,0),(4,5),(1,6)] | 7.81024967591
- (10,10) | ((1,2),(3,4)) | 9.21954445729
- (10,10) | ((1,2),(3,4)) | 9.21954445729
- (10,10) | [(1,2),(3,4)] | 9.21954445729
- (10,10) | ((10,20)) | 10
- (10,10) | [(11,12),(13,14)] | 2.2360679775
- (10,10) | ((11,12),(13,14)) | 2.2360679775
+SELECT p.f1, p1.f1, p.f1 <-> p1.f1 AS dist_ppath, p1.f1 <-> p.f1 AS dist_pathp FROM POINT_TBL p, PATH_TBL p1;
+ f1 | f1 | dist_ppath | dist_pathp
+-------------------+---------------------------+--------------------+--------------------
+ (0,0) | [(1,2),(3,4)] | 2.2360679775 | 2.2360679775
+ (0,0) | ((1,2),(3,4)) | 2.2360679775 | 2.2360679775
+ (0,0) | [(0,0),(3,0),(4,5),(1,6)] | 0 | 0
+ (0,0) | ((1,2),(3,4)) | 2.2360679775 | 2.2360679775
+ (0,0) | ((1,2),(3,4)) | 2.2360679775 | 2.2360679775
+ (0,0) | [(1,2),(3,4)] | 2.2360679775 | 2.2360679775
+ (0,0) | ((10,20)) | 22.360679775 | 22.360679775
+ (0,0) | [(11,12),(13,14)] | 16.2788205961 | 16.2788205961
+ (0,0) | ((11,12),(13,14)) | 16.2788205961 | 16.2788205961
+ (-10,0) | [(1,2),(3,4)] | 11.1803398875 | 11.1803398875
+ (-10,0) | ((1,2),(3,4)) | 11.1803398875 | 11.1803398875
+ (-10,0) | [(0,0),(3,0),(4,5),(1,6)] | 10 | 10
+ (-10,0) | ((1,2),(3,4)) | 11.1803398875 | 11.1803398875
+ (-10,0) | ((1,2),(3,4)) | 11.1803398875 | 11.1803398875
+ (-10,0) | [(1,2),(3,4)] | 11.1803398875 | 11.1803398875
+ (-10,0) | ((10,20)) | 28.2842712475 | 28.2842712475
+ (-10,0) | [(11,12),(13,14)] | 24.1867732449 | 24.1867732449
+ (-10,0) | ((11,12),(13,14)) | 24.1867732449 | 24.1867732449
+ (-3,4) | [(1,2),(3,4)] | 4.472135955 | 4.472135955
+ (-3,4) | ((1,2),(3,4)) | 4.472135955 | 4.472135955
+ (-3,4) | [(0,0),(3,0),(4,5),(1,6)] | 4.472135955 | 4.472135955
+ (-3,4) | ((1,2),(3,4)) | 4.472135955 | 4.472135955
+ (-3,4) | ((1,2),(3,4)) | 4.472135955 | 4.472135955
+ (-3,4) | [(1,2),(3,4)] | 4.472135955 | 4.472135955
+ (-3,4) | ((10,20)) | 20.6155281281 | 20.6155281281
+ (-3,4) | [(11,12),(13,14)] | 16.1245154966 | 16.1245154966
+ (-3,4) | ((11,12),(13,14)) | 16.1245154966 | 16.1245154966
+ (5.1,34.5) | [(1,2),(3,4)] | 30.5722096028 | 30.5722096028
+ (5.1,34.5) | ((1,2),(3,4)) | 30.5722096028 | 30.5722096028
+ (5.1,34.5) | [(0,0),(3,0),(4,5),(1,6)] | 28.793402022 | 28.793402022
+ (5.1,34.5) | ((1,2),(3,4)) | 30.5722096028 | 30.5722096028
+ (5.1,34.5) | ((1,2),(3,4)) | 30.5722096028 | 30.5722096028
+ (5.1,34.5) | [(1,2),(3,4)] | 30.5722096028 | 30.5722096028
+ (5.1,34.5) | ((10,20)) | 15.3055545473 | 15.3055545473
+ (5.1,34.5) | [(11,12),(13,14)] | 21.9695243462 | 21.9695243462
+ (5.1,34.5) | ((11,12),(13,14)) | 21.9695243462 | 21.9695243462
+ (-5,-12) | [(1,2),(3,4)] | 15.2315462117 | 15.2315462117
+ (-5,-12) | ((1,2),(3,4)) | 15.2315462117 | 15.2315462117
+ (-5,-12) | [(0,0),(3,0),(4,5),(1,6)] | 13 | 13
+ (-5,-12) | ((1,2),(3,4)) | 15.2315462117 | 15.2315462117
+ (-5,-12) | ((1,2),(3,4)) | 15.2315462117 | 15.2315462117
+ (-5,-12) | [(1,2),(3,4)] | 15.2315462117 | 15.2315462117
+ (-5,-12) | ((10,20)) | 35.3411940941 | 35.3411940941
+ (-5,-12) | [(11,12),(13,14)] | 28.8444102037 | 28.8444102037
+ (-5,-12) | ((11,12),(13,14)) | 28.8444102037 | 28.8444102037
+ (1e-300,-1e-300) | [(1,2),(3,4)] | 2.2360679775 | 2.2360679775
+ (1e-300,-1e-300) | ((1,2),(3,4)) | 2.2360679775 | 2.2360679775
+ (1e-300,-1e-300) | [(0,0),(3,0),(4,5),(1,6)] | 1.41421356237e-300 | 1.41421356237e-300
+ (1e-300,-1e-300) | ((1,2),(3,4)) | 2.2360679775 | 2.2360679775
+ (1e-300,-1e-300) | ((1,2),(3,4)) | 2.2360679775 | 2.2360679775
+ (1e-300,-1e-300) | [(1,2),(3,4)] | 2.2360679775 | 2.2360679775
+ (1e-300,-1e-300) | ((10,20)) | 22.360679775 | 22.360679775
+ (1e-300,-1e-300) | [(11,12),(13,14)] | 16.2788205961 | 16.2788205961
+ (1e-300,-1e-300) | ((11,12),(13,14)) | 16.2788205961 | 16.2788205961
+ (1e+300,Infinity) | [(1,2),(3,4)] | Infinity | Infinity
+ (1e+300,Infinity) | ((1,2),(3,4)) | Infinity | Infinity
+ (1e+300,Infinity) | [(0,0),(3,0),(4,5),(1,6)] | Infinity | Infinity
+ (1e+300,Infinity) | ((1,2),(3,4)) | Infinity | Infinity
+ (1e+300,Infinity) | ((1,2),(3,4)) | Infinity | Infinity
+ (1e+300,Infinity) | [(1,2),(3,4)] | Infinity | Infinity
+ (1e+300,Infinity) | ((10,20)) | Infinity | Infinity
+ (1e+300,Infinity) | [(11,12),(13,14)] | Infinity | Infinity
+ (1e+300,Infinity) | ((11,12),(13,14)) | Infinity | Infinity
+ (NaN,NaN) | [(1,2),(3,4)] | NaN | NaN
+ (NaN,NaN) | ((1,2),(3,4)) | NaN | NaN
+ (NaN,NaN) | [(0,0),(3,0),(4,5),(1,6)] | NaN | NaN
+ (NaN,NaN) | ((1,2),(3,4)) | NaN | NaN
+ (NaN,NaN) | ((1,2),(3,4)) | NaN | NaN
+ (NaN,NaN) | [(1,2),(3,4)] | NaN | NaN
+ (NaN,NaN) | ((10,20)) | NaN | NaN
+ (NaN,NaN) | [(11,12),(13,14)] | NaN | NaN
+ (NaN,NaN) | ((11,12),(13,14)) | NaN | NaN
+ (10,10) | [(1,2),(3,4)] | 9.21954445729 | 9.21954445729
+ (10,10) | ((1,2),(3,4)) | 9.21954445729 | 9.21954445729
+ (10,10) | [(0,0),(3,0),(4,5),(1,6)] | 7.81024967591 | 7.81024967591
+ (10,10) | ((1,2),(3,4)) | 9.21954445729 | 9.21954445729
+ (10,10) | ((1,2),(3,4)) | 9.21954445729 | 9.21954445729
+ (10,10) | [(1,2),(3,4)] | 9.21954445729 | 9.21954445729
+ (10,10) | ((10,20)) | 10 | 10
+ (10,10) | [(11,12),(13,14)] | 2.2360679775 | 2.2360679775
+ (10,10) | ((11,12),(13,14)) | 2.2360679775 | 2.2360679775
(81 rows)
-- Distance to polygon
-SELECT p.f1, p1.f1, p.f1 <-> p1.f1 FROM POINT_TBL p, POLYGON_TBL p1;
- f1 | f1 | ?column?
--------------------+----------------------------+---------------
- (0,0) | ((2,0),(2,4),(0,0)) | 0
- (0,0) | ((3,1),(3,3),(1,0)) | 1
- (0,0) | ((1,2),(3,4),(5,6),(7,8)) | 2.2360679775
- (0,0) | ((7,8),(5,6),(3,4),(1,2)) | 2.2360679775
- (0,0) | ((1,2),(7,8),(5,6),(3,-4)) | 1.58113883008
- (0,0) | ((0,0)) | 0
- (0,0) | ((0,1),(0,1)) | 1
- (-10,0) | ((2,0),(2,4),(0,0)) | 10
- (-10,0) | ((3,1),(3,3),(1,0)) | 11
- (-10,0) | ((1,2),(3,4),(5,6),(7,8)) | 11.1803398875
- (-10,0) | ((7,8),(5,6),(3,4),(1,2)) | 11.1803398875
- (-10,0) | ((1,2),(7,8),(5,6),(3,-4)) | 11.1803398875
- (-10,0) | ((0,0)) | 10
- (-10,0) | ((0,1),(0,1)) | 10.0498756211
- (-3,4) | ((2,0),(2,4),(0,0)) | 4.472135955
- (-3,4) | ((3,1),(3,3),(1,0)) | 5.54700196225
- (-3,4) | ((1,2),(3,4),(5,6),(7,8)) | 4.472135955
- (-3,4) | ((7,8),(5,6),(3,4),(1,2)) | 4.472135955
- (-3,4) | ((1,2),(7,8),(5,6),(3,-4)) | 4.472135955
- (-3,4) | ((0,0)) | 5
- (-3,4) | ((0,1),(0,1)) | 4.24264068712
- (5.1,34.5) | ((2,0),(2,4),(0,0)) | 30.6571362002
- (5.1,34.5) | ((3,1),(3,3),(1,0)) | 31.5699223946
- (5.1,34.5) | ((1,2),(3,4),(5,6),(7,8)) | 26.5680258958
- (5.1,34.5) | ((7,8),(5,6),(3,4),(1,2)) | 26.5680258958
- (5.1,34.5) | ((1,2),(7,8),(5,6),(3,-4)) | 26.5680258958
- (5.1,34.5) | ((0,0)) | 34.8749193547
- (5.1,34.5) | ((0,1),(0,1)) | 33.8859853037
- (-5,-12) | ((2,0),(2,4),(0,0)) | 13
- (-5,-12) | ((3,1),(3,3),(1,0)) | 13.416407865
- (-5,-12) | ((1,2),(3,4),(5,6),(7,8)) | 15.2315462117
- (-5,-12) | ((7,8),(5,6),(3,4),(1,2)) | 15.2315462117
- (-5,-12) | ((1,2),(7,8),(5,6),(3,-4)) | 11.313708499
- (-5,-12) | ((0,0)) | 13
- (-5,-12) | ((0,1),(0,1)) | 13.9283882772
- (1e-300,-1e-300) | ((2,0),(2,4),(0,0)) | 0
- (1e-300,-1e-300) | ((3,1),(3,3),(1,0)) | 1
- (1e-300,-1e-300) | ((1,2),(3,4),(5,6),(7,8)) | 2.2360679775
- (1e-300,-1e-300) | ((7,8),(5,6),(3,4),(1,2)) | 2.2360679775
- (1e-300,-1e-300) | ((1,2),(7,8),(5,6),(3,-4)) | 1.58113883008
- (1e-300,-1e-300) | ((0,0)) | 0
- (1e-300,-1e-300) | ((0,1),(0,1)) | 1
- (1e+300,Infinity) | ((2,0),(2,4),(0,0)) | Infinity
- (1e+300,Infinity) | ((3,1),(3,3),(1,0)) | Infinity
- (1e+300,Infinity) | ((1,2),(3,4),(5,6),(7,8)) | Infinity
- (1e+300,Infinity) | ((7,8),(5,6),(3,4),(1,2)) | Infinity
- (1e+300,Infinity) | ((1,2),(7,8),(5,6),(3,-4)) | Infinity
- (1e+300,Infinity) | ((0,0)) | Infinity
- (1e+300,Infinity) | ((0,1),(0,1)) | Infinity
- (NaN,NaN) | ((2,0),(2,4),(0,0)) | 0
- (NaN,NaN) | ((3,1),(3,3),(1,0)) | 0
- (NaN,NaN) | ((1,2),(3,4),(5,6),(7,8)) | 0
- (NaN,NaN) | ((7,8),(5,6),(3,4),(1,2)) | 0
- (NaN,NaN) | ((1,2),(7,8),(5,6),(3,-4)) | 0
- (NaN,NaN) | ((0,0)) | 0
- (NaN,NaN) | ((0,1),(0,1)) | 0
- (10,10) | ((2,0),(2,4),(0,0)) | 10
- (10,10) | ((3,1),(3,3),(1,0)) | 9.89949493661
- (10,10) | ((1,2),(3,4),(5,6),(7,8)) | 3.60555127546
- (10,10) | ((7,8),(5,6),(3,4),(1,2)) | 3.60555127546
- (10,10) | ((1,2),(7,8),(5,6),(3,-4)) | 3.60555127546
- (10,10) | ((0,0)) | 14.1421356237
- (10,10) | ((0,1),(0,1)) | 13.4536240471
+SELECT p.f1, p1.f1, p.f1 <-> p1.f1 AS dist_ppoly, p1.f1 <-> p.f1 AS dist_polyp FROM POINT_TBL p, POLYGON_TBL p1;
+ f1 | f1 | dist_ppoly | dist_polyp
+-------------------+----------------------------+---------------+---------------
+ (0,0) | ((2,0),(2,4),(0,0)) | 0 | 0
+ (0,0) | ((3,1),(3,3),(1,0)) | 1 | 1
+ (0,0) | ((1,2),(3,4),(5,6),(7,8)) | 2.2360679775 | 2.2360679775
+ (0,0) | ((7,8),(5,6),(3,4),(1,2)) | 2.2360679775 | 2.2360679775
+ (0,0) | ((1,2),(7,8),(5,6),(3,-4)) | 1.58113883008 | 1.58113883008
+ (0,0) | ((0,0)) | 0 | 0
+ (0,0) | ((0,1),(0,1)) | 1 | 1
+ (-10,0) | ((2,0),(2,4),(0,0)) | 10 | 10
+ (-10,0) | ((3,1),(3,3),(1,0)) | 11 | 11
+ (-10,0) | ((1,2),(3,4),(5,6),(7,8)) | 11.1803398875 | 11.1803398875
+ (-10,0) | ((7,8),(5,6),(3,4),(1,2)) | 11.1803398875 | 11.1803398875
+ (-10,0) | ((1,2),(7,8),(5,6),(3,-4)) | 11.1803398875 | 11.1803398875
+ (-10,0) | ((0,0)) | 10 | 10
+ (-10,0) | ((0,1),(0,1)) | 10.0498756211 | 10.0498756211
+ (-3,4) | ((2,0),(2,4),(0,0)) | 4.472135955 | 4.472135955
+ (-3,4) | ((3,1),(3,3),(1,0)) | 5.54700196225 | 5.54700196225
+ (-3,4) | ((1,2),(3,4),(5,6),(7,8)) | 4.472135955 | 4.472135955
+ (-3,4) | ((7,8),(5,6),(3,4),(1,2)) | 4.472135955 | 4.472135955
+ (-3,4) | ((1,2),(7,8),(5,6),(3,-4)) | 4.472135955 | 4.472135955
+ (-3,4) | ((0,0)) | 5 | 5
+ (-3,4) | ((0,1),(0,1)) | 4.24264068712 | 4.24264068712
+ (5.1,34.5) | ((2,0),(2,4),(0,0)) | 30.6571362002 | 30.6571362002
+ (5.1,34.5) | ((3,1),(3,3),(1,0)) | 31.5699223946 | 31.5699223946
+ (5.1,34.5) | ((1,2),(3,4),(5,6),(7,8)) | 26.5680258958 | 26.5680258958
+ (5.1,34.5) | ((7,8),(5,6),(3,4),(1,2)) | 26.5680258958 | 26.5680258958
+ (5.1,34.5) | ((1,2),(7,8),(5,6),(3,-4)) | 26.5680258958 | 26.5680258958
+ (5.1,34.5) | ((0,0)) | 34.8749193547 | 34.8749193547
+ (5.1,34.5) | ((0,1),(0,1)) | 33.8859853037 | 33.8859853037
+ (-5,-12) | ((2,0),(2,4),(0,0)) | 13 | 13
+ (-5,-12) | ((3,1),(3,3),(1,0)) | 13.416407865 | 13.416407865
+ (-5,-12) | ((1,2),(3,4),(5,6),(7,8)) | 15.2315462117 | 15.2315462117
+ (-5,-12) | ((7,8),(5,6),(3,4),(1,2)) | 15.2315462117 | 15.2315462117
+ (-5,-12) | ((1,2),(7,8),(5,6),(3,-4)) | 11.313708499 | 11.313708499
+ (-5,-12) | ((0,0)) | 13 | 13
+ (-5,-12) | ((0,1),(0,1)) | 13.9283882772 | 13.9283882772
+ (1e-300,-1e-300) | ((2,0),(2,4),(0,0)) | 0 | 0
+ (1e-300,-1e-300) | ((3,1),(3,3),(1,0)) | 1 | 1
+ (1e-300,-1e-300) | ((1,2),(3,4),(5,6),(7,8)) | 2.2360679775 | 2.2360679775
+ (1e-300,-1e-300) | ((7,8),(5,6),(3,4),(1,2)) | 2.2360679775 | 2.2360679775
+ (1e-300,-1e-300) | ((1,2),(7,8),(5,6),(3,-4)) | 1.58113883008 | 1.58113883008
+ (1e-300,-1e-300) | ((0,0)) | 0 | 0
+ (1e-300,-1e-300) | ((0,1),(0,1)) | 1 | 1
+ (1e+300,Infinity) | ((2,0),(2,4),(0,0)) | Infinity | Infinity
+ (1e+300,Infinity) | ((3,1),(3,3),(1,0)) | Infinity | Infinity
+ (1e+300,Infinity) | ((1,2),(3,4),(5,6),(7,8)) | Infinity | Infinity
+ (1e+300,Infinity) | ((7,8),(5,6),(3,4),(1,2)) | Infinity | Infinity
+ (1e+300,Infinity) | ((1,2),(7,8),(5,6),(3,-4)) | Infinity | Infinity
+ (1e+300,Infinity) | ((0,0)) | Infinity | Infinity
+ (1e+300,Infinity) | ((0,1),(0,1)) | Infinity | Infinity
+ (NaN,NaN) | ((2,0),(2,4),(0,0)) | 0 | 0
+ (NaN,NaN) | ((3,1),(3,3),(1,0)) | 0 | 0
+ (NaN,NaN) | ((1,2),(3,4),(5,6),(7,8)) | 0 | 0
+ (NaN,NaN) | ((7,8),(5,6),(3,4),(1,2)) | 0 | 0
+ (NaN,NaN) | ((1,2),(7,8),(5,6),(3,-4)) | 0 | 0
+ (NaN,NaN) | ((0,0)) | 0 | 0
+ (NaN,NaN) | ((0,1),(0,1)) | 0 | 0
+ (10,10) | ((2,0),(2,4),(0,0)) | 10 | 10
+ (10,10) | ((3,1),(3,3),(1,0)) | 9.89949493661 | 9.89949493661
+ (10,10) | ((1,2),(3,4),(5,6),(7,8)) | 3.60555127546 | 3.60555127546
+ (10,10) | ((7,8),(5,6),(3,4),(1,2)) | 3.60555127546 | 3.60555127546
+ (10,10) | ((1,2),(7,8),(5,6),(3,-4)) | 3.60555127546 | 3.60555127546
+ (10,10) | ((0,0)) | 14.1421356237 | 14.1421356237
+ (10,10) | ((0,1),(0,1)) | 13.4536240471 | 13.4536240471
(63 rows)
-- Closest point to line
@@ -1252,6 +1252,8 @@ SELECT l1.s, l2.s, l1.s <-> l2.s FROM LINE_TBL l1, LINE_TBL l2;
-- Distance to box
SELECT l.s, b.f1, l.s <-> b.f1 FROM LINE_TBL l, BOX_TBL b;
ERROR: function "dist_lb" not implemented
+SELECT l.s, b.f1, b.f1 <-> l.s FROM LINE_TBL l, BOX_TBL b;
+ERROR: function "dist_bl" not implemented
-- Intersect with line
SELECT l1.s, l2.s FROM LINE_TBL l1, LINE_TBL l2 WHERE l1.s ?# l2.s;
s | s
@@ -1848,89 +1850,89 @@ SELECT l1.s, l2.s FROM LSEG_TBL l1, LSEG_TBL l2 WHERE l1.s ?-| l2.s;
(2 rows)
-- Distance to line
-SELECT l.s, l1.s, l.s <-> l1.s FROM LSEG_TBL l, LINE_TBL l1;
- s | s | ?column?
--------------------------------+---------------------------------------+----------------
- [(1,2),(3,4)] | {0,-1,5} | 1
- [(0,0),(6,6)] | {0,-1,5} | 0
- [(10,-10),(-3,-4)] | {0,-1,5} | 9
- [(-1000000,200),(300000,-40)] | {0,-1,5} | 0
- [(11,22),(33,44)] | {0,-1,5} | 17
- [(-10,2),(-10,3)] | {0,-1,5} | 2
- [(0,-20),(30,-20)] | {0,-1,5} | 25
- [(NaN,1),(NaN,90)] | {0,-1,5} | NaN
- [(1,2),(3,4)] | {1,0,5} | 6
- [(0,0),(6,6)] | {1,0,5} | 5
- [(10,-10),(-3,-4)] | {1,0,5} | 2
- [(-1000000,200),(300000,-40)] | {1,0,5} | 0
- [(11,22),(33,44)] | {1,0,5} | 16
- [(-10,2),(-10,3)] | {1,0,5} | 5
- [(0,-20),(30,-20)] | {1,0,5} | 5
- [(NaN,1),(NaN,90)] | {1,0,5} | NaN
- [(1,2),(3,4)] | {0,3,0} | 2
- [(0,0),(6,6)] | {0,3,0} | 0
- [(10,-10),(-3,-4)] | {0,3,0} | 4
- [(-1000000,200),(300000,-40)] | {0,3,0} | 0
- [(11,22),(33,44)] | {0,3,0} | 22
- [(-10,2),(-10,3)] | {0,3,0} | 2
- [(0,-20),(30,-20)] | {0,3,0} | 20
- [(NaN,1),(NaN,90)] | {0,3,0} | NaN
- [(1,2),(3,4)] | {1,-1,0} | 0.707106781187
- [(0,0),(6,6)] | {1,-1,0} | 0
- [(10,-10),(-3,-4)] | {1,-1,0} | 0.707106781187
- [(-1000000,200),(300000,-40)] | {1,-1,0} | 0
- [(11,22),(33,44)] | {1,-1,0} | 7.77817459305
- [(-10,2),(-10,3)] | {1,-1,0} | 8.48528137424
- [(0,-20),(30,-20)] | {1,-1,0} | 14.1421356237
- [(NaN,1),(NaN,90)] | {1,-1,0} | NaN
- [(1,2),(3,4)] | {-0.4,-1,-6} | 7.79920420344
- [(0,0),(6,6)] | {-0.4,-1,-6} | 5.57086014531
- [(10,-10),(-3,-4)] | {-0.4,-1,-6} | 0
- [(-1000000,200),(300000,-40)] | {-0.4,-1,-6} | 0
- [(11,22),(33,44)] | {-0.4,-1,-6} | 30.0826447847
- [(-10,2),(-10,3)] | {-0.4,-1,-6} | 3.71390676354
- [(0,-20),(30,-20)] | {-0.4,-1,-6} | 1.85695338177
- [(NaN,1),(NaN,90)] | {-0.4,-1,-6} | NaN
- [(1,2),(3,4)] | {-0.000184615384615,-1,15.3846153846} | 11.3840613445
- [(0,0),(6,6)] | {-0.000184615384615,-1,15.3846153846} | 9.3835075324
- [(10,-10),(-3,-4)] | {-0.000184615384615,-1,15.3846153846} | 19.3851689004
- [(-1000000,200),(300000,-40)] | {-0.000184615384615,-1,15.3846153846} | 0
- [(11,22),(33,44)] | {-0.000184615384615,-1,15.3846153846} | 6.61741527185
- [(-10,2),(-10,3)] | {-0.000184615384615,-1,15.3846153846} | 12.3864613274
- [(0,-20),(30,-20)] | {-0.000184615384615,-1,15.3846153846} | 35.3790763202
- [(NaN,1),(NaN,90)] | {-0.000184615384615,-1,15.3846153846} | NaN
- [(1,2),(3,4)] | {3,NaN,5} | NaN
- [(0,0),(6,6)] | {3,NaN,5} | NaN
- [(10,-10),(-3,-4)] | {3,NaN,5} | NaN
- [(-1000000,200),(300000,-40)] | {3,NaN,5} | NaN
- [(11,22),(33,44)] | {3,NaN,5} | NaN
- [(-10,2),(-10,3)] | {3,NaN,5} | NaN
- [(0,-20),(30,-20)] | {3,NaN,5} | NaN
- [(NaN,1),(NaN,90)] | {3,NaN,5} | NaN
- [(1,2),(3,4)] | {NaN,NaN,NaN} | NaN
- [(0,0),(6,6)] | {NaN,NaN,NaN} | NaN
- [(10,-10),(-3,-4)] | {NaN,NaN,NaN} | NaN
- [(-1000000,200),(300000,-40)] | {NaN,NaN,NaN} | NaN
- [(11,22),(33,44)] | {NaN,NaN,NaN} | NaN
- [(-10,2),(-10,3)] | {NaN,NaN,NaN} | NaN
- [(0,-20),(30,-20)] | {NaN,NaN,NaN} | NaN
- [(NaN,1),(NaN,90)] | {NaN,NaN,NaN} | NaN
- [(1,2),(3,4)] | {0,-1,3} | 0
- [(0,0),(6,6)] | {0,-1,3} | 0
- [(10,-10),(-3,-4)] | {0,-1,3} | 7
- [(-1000000,200),(300000,-40)] | {0,-1,3} | 0
- [(11,22),(33,44)] | {0,-1,3} | 19
- [(-10,2),(-10,3)] | {0,-1,3} | 0
- [(0,-20),(30,-20)] | {0,-1,3} | 23
- [(NaN,1),(NaN,90)] | {0,-1,3} | NaN
- [(1,2),(3,4)] | {-1,0,3} | 0
- [(0,0),(6,6)] | {-1,0,3} | 0
- [(10,-10),(-3,-4)] | {-1,0,3} | 0
- [(-1000000,200),(300000,-40)] | {-1,0,3} | 0
- [(11,22),(33,44)] | {-1,0,3} | 8
- [(-10,2),(-10,3)] | {-1,0,3} | 13
- [(0,-20),(30,-20)] | {-1,0,3} | 0
- [(NaN,1),(NaN,90)] | {-1,0,3} | NaN
+SELECT l.s, l1.s, l.s <-> l1.s AS dist_sl, l1.s <-> l.s AS dist_ls FROM LSEG_TBL l, LINE_TBL l1;
+ s | s | dist_sl | dist_ls
+-------------------------------+---------------------------------------+----------------+----------------
+ [(1,2),(3,4)] | {0,-1,5} | 1 | 1
+ [(0,0),(6,6)] | {0,-1,5} | 0 | 0
+ [(10,-10),(-3,-4)] | {0,-1,5} | 9 | 9
+ [(-1000000,200),(300000,-40)] | {0,-1,5} | 0 | 0
+ [(11,22),(33,44)] | {0,-1,5} | 17 | 17
+ [(-10,2),(-10,3)] | {0,-1,5} | 2 | 2
+ [(0,-20),(30,-20)] | {0,-1,5} | 25 | 25
+ [(NaN,1),(NaN,90)] | {0,-1,5} | NaN | NaN
+ [(1,2),(3,4)] | {1,0,5} | 6 | 6
+ [(0,0),(6,6)] | {1,0,5} | 5 | 5
+ [(10,-10),(-3,-4)] | {1,0,5} | 2 | 2
+ [(-1000000,200),(300000,-40)] | {1,0,5} | 0 | 0
+ [(11,22),(33,44)] | {1,0,5} | 16 | 16
+ [(-10,2),(-10,3)] | {1,0,5} | 5 | 5
+ [(0,-20),(30,-20)] | {1,0,5} | 5 | 5
+ [(NaN,1),(NaN,90)] | {1,0,5} | NaN | NaN
+ [(1,2),(3,4)] | {0,3,0} | 2 | 2
+ [(0,0),(6,6)] | {0,3,0} | 0 | 0
+ [(10,-10),(-3,-4)] | {0,3,0} | 4 | 4
+ [(-1000000,200),(300000,-40)] | {0,3,0} | 0 | 0
+ [(11,22),(33,44)] | {0,3,0} | 22 | 22
+ [(-10,2),(-10,3)] | {0,3,0} | 2 | 2
+ [(0,-20),(30,-20)] | {0,3,0} | 20 | 20
+ [(NaN,1),(NaN,90)] | {0,3,0} | NaN | NaN
+ [(1,2),(3,4)] | {1,-1,0} | 0.707106781187 | 0.707106781187
+ [(0,0),(6,6)] | {1,-1,0} | 0 | 0
+ [(10,-10),(-3,-4)] | {1,-1,0} | 0.707106781187 | 0.707106781187
+ [(-1000000,200),(300000,-40)] | {1,-1,0} | 0 | 0
+ [(11,22),(33,44)] | {1,-1,0} | 7.77817459305 | 7.77817459305
+ [(-10,2),(-10,3)] | {1,-1,0} | 8.48528137424 | 8.48528137424
+ [(0,-20),(30,-20)] | {1,-1,0} | 14.1421356237 | 14.1421356237
+ [(NaN,1),(NaN,90)] | {1,-1,0} | NaN | NaN
+ [(1,2),(3,4)] | {-0.4,-1,-6} | 7.79920420344 | 7.79920420344
+ [(0,0),(6,6)] | {-0.4,-1,-6} | 5.57086014531 | 5.57086014531
+ [(10,-10),(-3,-4)] | {-0.4,-1,-6} | 0 | 0
+ [(-1000000,200),(300000,-40)] | {-0.4,-1,-6} | 0 | 0
+ [(11,22),(33,44)] | {-0.4,-1,-6} | 30.0826447847 | 30.0826447847
+ [(-10,2),(-10,3)] | {-0.4,-1,-6} | 3.71390676354 | 3.71390676354
+ [(0,-20),(30,-20)] | {-0.4,-1,-6} | 1.85695338177 | 1.85695338177
+ [(NaN,1),(NaN,90)] | {-0.4,-1,-6} | NaN | NaN
+ [(1,2),(3,4)] | {-0.000184615384615,-1,15.3846153846} | 11.3840613445 | 11.3840613445
+ [(0,0),(6,6)] | {-0.000184615384615,-1,15.3846153846} | 9.3835075324 | 9.3835075324
+ [(10,-10),(-3,-4)] | {-0.000184615384615,-1,15.3846153846} | 19.3851689004 | 19.3851689004
+ [(-1000000,200),(300000,-40)] | {-0.000184615384615,-1,15.3846153846} | 0 | 0
+ [(11,22),(33,44)] | {-0.000184615384615,-1,15.3846153846} | 6.61741527185 | 6.61741527185
+ [(-10,2),(-10,3)] | {-0.000184615384615,-1,15.3846153846} | 12.3864613274 | 12.3864613274
+ [(0,-20),(30,-20)] | {-0.000184615384615,-1,15.3846153846} | 35.3790763202 | 35.3790763202
+ [(NaN,1),(NaN,90)] | {-0.000184615384615,-1,15.3846153846} | NaN | NaN
+ [(1,2),(3,4)] | {3,NaN,5} | NaN | NaN
+ [(0,0),(6,6)] | {3,NaN,5} | NaN | NaN
+ [(10,-10),(-3,-4)] | {3,NaN,5} | NaN | NaN
+ [(-1000000,200),(300000,-40)] | {3,NaN,5} | NaN | NaN
+ [(11,22),(33,44)] | {3,NaN,5} | NaN | NaN
+ [(-10,2),(-10,3)] | {3,NaN,5} | NaN | NaN
+ [(0,-20),(30,-20)] | {3,NaN,5} | NaN | NaN
+ [(NaN,1),(NaN,90)] | {3,NaN,5} | NaN | NaN
+ [(1,2),(3,4)] | {NaN,NaN,NaN} | NaN | NaN
+ [(0,0),(6,6)] | {NaN,NaN,NaN} | NaN | NaN
+ [(10,-10),(-3,-4)] | {NaN,NaN,NaN} | NaN | NaN
+ [(-1000000,200),(300000,-40)] | {NaN,NaN,NaN} | NaN | NaN
+ [(11,22),(33,44)] | {NaN,NaN,NaN} | NaN | NaN
+ [(-10,2),(-10,3)] | {NaN,NaN,NaN} | NaN | NaN
+ [(0,-20),(30,-20)] | {NaN,NaN,NaN} | NaN | NaN
+ [(NaN,1),(NaN,90)] | {NaN,NaN,NaN} | NaN | NaN
+ [(1,2),(3,4)] | {0,-1,3} | 0 | 0
+ [(0,0),(6,6)] | {0,-1,3} | 0 | 0
+ [(10,-10),(-3,-4)] | {0,-1,3} | 7 | 7
+ [(-1000000,200),(300000,-40)] | {0,-1,3} | 0 | 0
+ [(11,22),(33,44)] | {0,-1,3} | 19 | 19
+ [(-10,2),(-10,3)] | {0,-1,3} | 0 | 0
+ [(0,-20),(30,-20)] | {0,-1,3} | 23 | 23
+ [(NaN,1),(NaN,90)] | {0,-1,3} | NaN | NaN
+ [(1,2),(3,4)] | {-1,0,3} | 0 | 0
+ [(0,0),(6,6)] | {-1,0,3} | 0 | 0
+ [(10,-10),(-3,-4)] | {-1,0,3} | 0 | 0
+ [(-1000000,200),(300000,-40)] | {-1,0,3} | 0 | 0
+ [(11,22),(33,44)] | {-1,0,3} | 8 | 8
+ [(-10,2),(-10,3)] | {-1,0,3} | 13 | 13
+ [(0,-20),(30,-20)] | {-1,0,3} | 0 | 0
+ [(NaN,1),(NaN,90)] | {-1,0,3} | NaN | NaN
(80 rows)
-- Distance to line segment
@@ -2004,49 +2006,49 @@ SELECT l1.s, l2.s, l1.s <-> l2.s FROM LSEG_TBL l1, LSEG_TBL l2;
(64 rows)
-- Distance to box
-SELECT l.s, b.f1, l.s <-> b.f1 FROM LSEG_TBL l, BOX_TBL b;
- s | f1 | ?column?
--------------------------------+---------------------+----------------
- [(1,2),(3,4)] | (2,2),(0,0) | 0
- [(1,2),(3,4)] | (3,3),(1,1) | 0
- [(1,2),(3,4)] | (-2,2),(-8,-10) | 3
- [(1,2),(3,4)] | (2.5,3.5),(2.5,2.5) | 0
- [(1,2),(3,4)] | (3,3),(3,3) | 0.707106781187
- [(0,0),(6,6)] | (2,2),(0,0) | 0
- [(0,0),(6,6)] | (3,3),(1,1) | 0
- [(0,0),(6,6)] | (-2,2),(-8,-10) | 2
- [(0,0),(6,6)] | (2.5,3.5),(2.5,2.5) | 0
- [(0,0),(6,6)] | (3,3),(3,3) | 0
- [(10,-10),(-3,-4)] | (2,2),(0,0) | 4.88901207039
- [(10,-10),(-3,-4)] | (3,3),(1,1) | 6.21602963235
- [(10,-10),(-3,-4)] | (-2,2),(-8,-10) | 0
- [(10,-10),(-3,-4)] | (2.5,3.5),(2.5,2.5) | 8.20655597529
- [(10,-10),(-3,-4)] | (3,3),(3,3) | 8.87006475627
- [(-1000000,200),(300000,-40)] | (2,2),(0,0) | 13.3842459258
- [(-1000000,200),(300000,-40)] | (3,3),(1,1) | 12.3840613274
- [(-1000000,200),(300000,-40)] | (-2,2),(-8,-10) | 13.3849843873
- [(-1000000,200),(300000,-40)] | (2.5,3.5),(2.5,2.5) | 11.8841536436
- [(-1000000,200),(300000,-40)] | (3,3),(3,3) | 12.3840613274
- [(11,22),(33,44)] | (2,2),(0,0) | 21.9317121995
- [(11,22),(33,44)] | (3,3),(1,1) | 20.6155281281
- [(11,22),(33,44)] | (-2,2),(-8,-10) | 23.8537208838
- [(11,22),(33,44)] | (2.5,3.5),(2.5,2.5) | 20.3592730715
- [(11,22),(33,44)] | (3,3),(3,3) | 20.6155281281
- [(-10,2),(-10,3)] | (2,2),(0,0) | 10
- [(-10,2),(-10,3)] | (3,3),(1,1) | 11
- [(-10,2),(-10,3)] | (-2,2),(-8,-10) | 2
- [(-10,2),(-10,3)] | (2.5,3.5),(2.5,2.5) | 12.5
- [(-10,2),(-10,3)] | (3,3),(3,3) | 13
- [(0,-20),(30,-20)] | (2,2),(0,0) | 20
- [(0,-20),(30,-20)] | (3,3),(1,1) | 21
- [(0,-20),(30,-20)] | (-2,2),(-8,-10) | 10.1980390272
- [(0,-20),(30,-20)] | (2.5,3.5),(2.5,2.5) | 22.5
- [(0,-20),(30,-20)] | (3,3),(3,3) | 23
- [(NaN,1),(NaN,90)] | (2,2),(0,0) | NaN
- [(NaN,1),(NaN,90)] | (3,3),(1,1) | NaN
- [(NaN,1),(NaN,90)] | (-2,2),(-8,-10) | NaN
- [(NaN,1),(NaN,90)] | (2.5,3.5),(2.5,2.5) | NaN
- [(NaN,1),(NaN,90)] | (3,3),(3,3) | NaN
+SELECT l.s, b.f1, l.s <-> b.f1 AS dist_sb, b.f1 <-> l.s AS dist_bs FROM LSEG_TBL l, BOX_TBL b;
+ s | f1 | dist_sb | dist_bs
+-------------------------------+---------------------+----------------+----------------
+ [(1,2),(3,4)] | (2,2),(0,0) | 0 | 0
+ [(1,2),(3,4)] | (3,3),(1,1) | 0 | 0
+ [(1,2),(3,4)] | (-2,2),(-8,-10) | 3 | 3
+ [(1,2),(3,4)] | (2.5,3.5),(2.5,2.5) | 0 | 0
+ [(1,2),(3,4)] | (3,3),(3,3) | 0.707106781187 | 0.707106781187
+ [(0,0),(6,6)] | (2,2),(0,0) | 0 | 0
+ [(0,0),(6,6)] | (3,3),(1,1) | 0 | 0
+ [(0,0),(6,6)] | (-2,2),(-8,-10) | 2 | 2
+ [(0,0),(6,6)] | (2.5,3.5),(2.5,2.5) | 0 | 0
+ [(0,0),(6,6)] | (3,3),(3,3) | 0 | 0
+ [(10,-10),(-3,-4)] | (2,2),(0,0) | 4.88901207039 | 4.88901207039
+ [(10,-10),(-3,-4)] | (3,3),(1,1) | 6.21602963235 | 6.21602963235
+ [(10,-10),(-3,-4)] | (-2,2),(-8,-10) | 0 | 0
+ [(10,-10),(-3,-4)] | (2.5,3.5),(2.5,2.5) | 8.20655597529 | 8.20655597529
+ [(10,-10),(-3,-4)] | (3,3),(3,3) | 8.87006475627 | 8.87006475627
+ [(-1000000,200),(300000,-40)] | (2,2),(0,0) | 13.3842459258 | 13.3842459258
+ [(-1000000,200),(300000,-40)] | (3,3),(1,1) | 12.3840613274 | 12.3840613274
+ [(-1000000,200),(300000,-40)] | (-2,2),(-8,-10) | 13.3849843873 | 13.3849843873
+ [(-1000000,200),(300000,-40)] | (2.5,3.5),(2.5,2.5) | 11.8841536436 | 11.8841536436
+ [(-1000000,200),(300000,-40)] | (3,3),(3,3) | 12.3840613274 | 12.3840613274
+ [(11,22),(33,44)] | (2,2),(0,0) | 21.9317121995 | 21.9317121995
+ [(11,22),(33,44)] | (3,3),(1,1) | 20.6155281281 | 20.6155281281
+ [(11,22),(33,44)] | (-2,2),(-8,-10) | 23.8537208838 | 23.8537208838
+ [(11,22),(33,44)] | (2.5,3.5),(2.5,2.5) | 20.3592730715 | 20.3592730715
+ [(11,22),(33,44)] | (3,3),(3,3) | 20.6155281281 | 20.6155281281
+ [(-10,2),(-10,3)] | (2,2),(0,0) | 10 | 10
+ [(-10,2),(-10,3)] | (3,3),(1,1) | 11 | 11
+ [(-10,2),(-10,3)] | (-2,2),(-8,-10) | 2 | 2
+ [(-10,2),(-10,3)] | (2.5,3.5),(2.5,2.5) | 12.5 | 12.5
+ [(-10,2),(-10,3)] | (3,3),(3,3) | 13 | 13
+ [(0,-20),(30,-20)] | (2,2),(0,0) | 20 | 20
+ [(0,-20),(30,-20)] | (3,3),(1,1) | 21 | 21
+ [(0,-20),(30,-20)] | (-2,2),(-8,-10) | 10.1980390272 | 10.1980390272
+ [(0,-20),(30,-20)] | (2.5,3.5),(2.5,2.5) | 22.5 | 22.5
+ [(0,-20),(30,-20)] | (3,3),(3,3) | 23 | 23
+ [(NaN,1),(NaN,90)] | (2,2),(0,0) | NaN | NaN
+ [(NaN,1),(NaN,90)] | (3,3),(1,1) | NaN | NaN
+ [(NaN,1),(NaN,90)] | (-2,2),(-8,-10) | NaN | NaN
+ [(NaN,1),(NaN,90)] | (2.5,3.5),(2.5,2.5) | NaN | NaN
+ [(NaN,1),(NaN,90)] | (3,3),(3,3) | NaN | NaN
(40 rows)
-- Intersect with line segment
diff --git a/src/test/regress/sql/geometry.sql b/src/test/regress/sql/geometry.sql
index ce98b3e..939e307 100644
--- a/src/test/regress/sql/geometry.sql
+++ b/src/test/regress/sql/geometry.sql
@@ -71,19 +71,19 @@ SELECT p1.f1, p2.f1, p1.f1 / p2.f1 FROM POINT_TBL p1, POINT_TBL p2 WHERE p2.f1[0
SELECT p1.f1, p2.f1, p1.f1 / p2.f1 FROM POINT_TBL p1, POINT_TBL p2 WHERE p2.f1 ~= '(0,0)'::point;
-- Distance to line
-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 AS dist_pl, l.s <-> p.f1 AS dist_lp FROM POINT_TBL p, LINE_TBL l;
-- Distance to line segment
-SELECT p.f1, l.s, p.f1 <-> l.s FROM POINT_TBL p, LSEG_TBL l;
+SELECT p.f1, l.s, p.f1 <-> l.s AS dist_ps, l.s <-> p.f1 AS dist_sp 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 dist_pb, b.f1 <-> p.f1 AS dist_bp 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;
+SELECT p.f1, p1.f1, p.f1 <-> p1.f1 AS dist_ppath, p1.f1 <-> p.f1 AS dist_pathp FROM POINT_TBL p, PATH_TBL p1;
-- Distance to polygon
-SELECT p.f1, p1.f1, p.f1 <-> p1.f1 FROM POINT_TBL p, POLYGON_TBL p1;
+SELECT p.f1, p1.f1, p.f1 <-> p1.f1 AS dist_ppoly, p1.f1 <-> p.f1 AS dist_polyp FROM POINT_TBL p, POLYGON_TBL p1;
-- Closest point to line
SELECT p.f1, l.s, p.f1 ## l.s FROM POINT_TBL p, LINE_TBL l;
@@ -127,6 +127,7 @@ SELECT l1.s, l2.s, l1.s <-> l2.s FROM LINE_TBL l1, LINE_TBL l2;
-- Distance to box
SELECT l.s, b.f1, l.s <-> b.f1 FROM LINE_TBL l, BOX_TBL b;
+SELECT l.s, b.f1, b.f1 <-> l.s FROM LINE_TBL l, BOX_TBL b;
-- Intersect with line
SELECT l1.s, l2.s FROM LINE_TBL l1, LINE_TBL l2 WHERE l1.s ?# l2.s;
@@ -191,13 +192,13 @@ SELECT l1.s, l2.s FROM LSEG_TBL l1, LSEG_TBL l2 WHERE l1.s ?|| l2.s;
SELECT l1.s, l2.s FROM LSEG_TBL l1, LSEG_TBL l2 WHERE l1.s ?-| l2.s;
-- Distance to line
-SELECT l.s, l1.s, l.s <-> l1.s FROM LSEG_TBL l, LINE_TBL l1;
+SELECT l.s, l1.s, l.s <-> l1.s AS dist_sl, l1.s <-> l.s AS dist_ls FROM LSEG_TBL l, LINE_TBL l1;
-- Distance to line segment
SELECT l1.s, l2.s, l1.s <-> l2.s FROM LSEG_TBL l1, LSEG_TBL l2;
-- Distance to box
-SELECT l.s, b.f1, l.s <-> b.f1 FROM LSEG_TBL l, BOX_TBL b;
+SELECT l.s, b.f1, l.s <-> b.f1 AS dist_sb, b.f1 <-> l.s AS dist_bs FROM LSEG_TBL l, BOX_TBL b;
-- Intersect with line segment
SELECT l.s, l1.s FROM LSEG_TBL l, LINE_TBL l1 WHERE l.s ?# l1.s;
--
2.7.4
>From c79cea6630f0adfc95d7c803607257c93f959e87 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.glu...@postgrespro.ru>
Date: Thu, 7 Mar 2019 23:49:31 +0300
Subject: [PATCH 2/3] Add operator <->(box, point) to GiST box_ops
---
doc/src/sgml/gist.sgml | 1 +
src/backend/access/gist/gistproc.c | 49 +++++++++++++++---------
src/include/catalog/pg_amop.dat | 3 ++
src/include/catalog/pg_amproc.dat | 2 +
src/include/catalog/pg_proc.dat | 4 ++
src/test/regress/expected/gist.out | 76 ++++++++++++++++++++++++++++++++++++++
src/test/regress/sql/gist.sql | 16 ++++++++
7 files changed, 134 insertions(+), 17 deletions(-)
diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml
index 763b8cf..2b43dfc 100644
--- a/doc/src/sgml/gist.sgml
+++ b/doc/src/sgml/gist.sgml
@@ -83,6 +83,7 @@
<literal>~=</literal>
</entry>
<entry>
+ <literal><-></literal>
</entry>
</row>
<row>
diff --git a/src/backend/access/gist/gistproc.c b/src/backend/access/gist/gistproc.c
index 1826b51..a668283 100644
--- a/src/backend/access/gist/gistproc.c
+++ b/src/backend/access/gist/gistproc.c
@@ -1464,26 +1464,13 @@ gist_point_distance(PG_FUNCTION_ARGS)
PG_RETURN_FLOAT8(distance);
}
-/*
- * The inexact GiST distance method for geometric types that store bounding
- * boxes.
- *
- * Compute lossy distance from point to index entries. The result is inexact
- * because index entries are bounding boxes, not the exact shapes of the
- * indexed geometric types. We use distance from point to MBR of index entry.
- * This is a lower bound estimate of distance from point to indexed geometric
- * type.
- */
static float8
-gist_bbox_distance(GISTENTRY *entry, Datum query,
- StrategyNumber strategy, bool *recheck)
+gist_box_distance_helper(GISTENTRY *entry, Datum query,
+ StrategyNumber strategy)
{
float8 distance;
StrategyNumber strategyGroup = strategy / GeoStrategyNumberOffset;
- /* Bounding box distance is always inexact. */
- *recheck = true;
-
switch (strategyGroup)
{
case PointStrategyNumberGroup:
@@ -1500,6 +1487,32 @@ gist_bbox_distance(GISTENTRY *entry, Datum query,
}
Datum
+gist_box_distance(PG_FUNCTION_ARGS)
+{
+ GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
+ Datum query = PG_GETARG_DATUM(1);
+ StrategyNumber strategy = (StrategyNumber) PG_GETARG_UINT16(2);
+
+ /* Oid subtype = PG_GETARG_OID(3); */
+ /* bool *recheck = (bool *) PG_GETARG_POINTER(4); */
+ float8 distance;
+
+ distance = gist_box_distance_helper(entry, query, strategy);
+
+ PG_RETURN_FLOAT8(distance);
+}
+
+/*
+ * The inexact GiST distance methods for geometric types that store bounding
+ * boxes.
+ *
+ * Compute lossy distance from point to index entries. The result is inexact
+ * because index entries are bounding boxes, not the exact shapes of the
+ * indexed geometric types. We use distance from point to MBR of index entry.
+ * This is a lower bound estimate of distance from point to indexed geometric
+ * type.
+ */
+Datum
gist_circle_distance(PG_FUNCTION_ARGS)
{
GISTENTRY *entry = (GISTENTRY *) PG_GETARG_POINTER(0);
@@ -1510,7 +1523,8 @@ gist_circle_distance(PG_FUNCTION_ARGS)
bool *recheck = (bool *) PG_GETARG_POINTER(4);
float8 distance;
- distance = gist_bbox_distance(entry, query, strategy, recheck);
+ distance = gist_box_distance_helper(entry, query, strategy);
+ *recheck = true;
PG_RETURN_FLOAT8(distance);
}
@@ -1526,7 +1540,8 @@ gist_poly_distance(PG_FUNCTION_ARGS)
bool *recheck = (bool *) PG_GETARG_POINTER(4);
float8 distance;
- distance = gist_bbox_distance(entry, query, strategy, recheck);
+ distance = gist_box_distance_helper(entry, query, strategy);
+ *recheck = true;
PG_RETURN_FLOAT8(distance);
}
diff --git a/src/include/catalog/pg_amop.dat b/src/include/catalog/pg_amop.dat
index cf63eb7..ebc38ae 100644
--- a/src/include/catalog/pg_amop.dat
+++ b/src/include/catalog/pg_amop.dat
@@ -1081,6 +1081,9 @@
amopstrategy => '13', amopopr => '~(box,box)', amopmethod => 'gist' },
{ amopfamily => 'gist/box_ops', amoplefttype => 'box', amoprighttype => 'box',
amopstrategy => '14', amopopr => '@(box,box)', amopmethod => 'gist' },
+{ amopfamily => 'gist/box_ops', amoplefttype => 'box', amoprighttype => 'point',
+ amopstrategy => '15', amoppurpose => 'o', amopopr => '<->(box,point)',
+ amopmethod => 'gist', amopsortfamily => 'btree/float_ops' },
# gist point_ops
{ amopfamily => 'gist/point_ops', amoplefttype => 'point',
diff --git a/src/include/catalog/pg_amproc.dat b/src/include/catalog/pg_amproc.dat
index 020b741..5567b7e 100644
--- a/src/include/catalog/pg_amproc.dat
+++ b/src/include/catalog/pg_amproc.dat
@@ -419,6 +419,8 @@
amprocrighttype => 'box', amprocnum => '6', amproc => 'gist_box_picksplit' },
{ amprocfamily => 'gist/box_ops', amproclefttype => 'box',
amprocrighttype => 'box', amprocnum => '7', amproc => 'gist_box_same' },
+{ amprocfamily => 'gist/box_ops', amproclefttype => 'box',
+ amprocrighttype => 'box', amprocnum => '8', amproc => 'gist_box_distance' },
{ amprocfamily => 'gist/poly_ops', amproclefttype => 'polygon',
amprocrighttype => 'polygon', amprocnum => '1',
amproc => 'gist_poly_consistent' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index e0852b8..762c099 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7844,6 +7844,10 @@
{ oid => '2584', descr => 'GiST support',
proname => 'gist_box_same', prorettype => 'internal',
proargtypes => 'box box internal', prosrc => 'gist_box_same' },
+{ oid => '3998', descr => 'GiST support',
+ proname => 'gist_box_distance', prorettype => 'float8',
+ proargtypes => 'internal box int2 oid internal',
+ prosrc => 'gist_box_distance' },
{ oid => '2585', descr => 'GiST support',
proname => 'gist_poly_consistent', prorettype => 'bool',
proargtypes => 'internal polygon int2 oid internal',
diff --git a/src/test/regress/expected/gist.out b/src/test/regress/expected/gist.out
index 0a43449..2234876 100644
--- a/src/test/regress/expected/gist.out
+++ b/src/test/regress/expected/gist.out
@@ -203,6 +203,82 @@ select b from gist_tbl where b <@ box(point(5,5), point(6,6));
(6,6),(6,6)
(21 rows)
+-- Also test an index-only knn-search
+explain (costs off)
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by b <-> point(5.2, 5.91);
+ QUERY PLAN
+------------------------------------------------------
+ Index Only Scan using gist_tbl_box_index on gist_tbl
+ Index Cond: (b <@ '(6,6),(5,5)'::box)
+ Order By: (b <-> '(5.2,5.91)'::point)
+(3 rows)
+
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by b <-> point(5.2, 5.91);
+ b
+-------------------------
+ (5.55,5.55),(5.55,5.55)
+ (5.6,5.6),(5.6,5.6)
+ (5.5,5.5),(5.5,5.5)
+ (5.65,5.65),(5.65,5.65)
+ (5.45,5.45),(5.45,5.45)
+ (5.7,5.7),(5.7,5.7)
+ (5.4,5.4),(5.4,5.4)
+ (5.75,5.75),(5.75,5.75)
+ (5.35,5.35),(5.35,5.35)
+ (5.8,5.8),(5.8,5.8)
+ (5.3,5.3),(5.3,5.3)
+ (5.85,5.85),(5.85,5.85)
+ (5.25,5.25),(5.25,5.25)
+ (5.9,5.9),(5.9,5.9)
+ (5.2,5.2),(5.2,5.2)
+ (5.95,5.95),(5.95,5.95)
+ (5.15,5.15),(5.15,5.15)
+ (6,6),(6,6)
+ (5.1,5.1),(5.1,5.1)
+ (5.05,5.05),(5.05,5.05)
+ (5,5),(5,5)
+(21 rows)
+
+-- Check commuted case as well
+explain (costs off)
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by point(5.2, 5.91) <-> b;
+ QUERY PLAN
+------------------------------------------------------
+ Index Only Scan using gist_tbl_box_index on gist_tbl
+ Index Cond: (b <@ '(6,6),(5,5)'::box)
+ Order By: (b <-> '(5.2,5.91)'::point)
+(3 rows)
+
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by point(5.2, 5.91) <-> b;
+ b
+-------------------------
+ (5.55,5.55),(5.55,5.55)
+ (5.6,5.6),(5.6,5.6)
+ (5.5,5.5),(5.5,5.5)
+ (5.65,5.65),(5.65,5.65)
+ (5.45,5.45),(5.45,5.45)
+ (5.7,5.7),(5.7,5.7)
+ (5.4,5.4),(5.4,5.4)
+ (5.75,5.75),(5.75,5.75)
+ (5.35,5.35),(5.35,5.35)
+ (5.8,5.8),(5.8,5.8)
+ (5.3,5.3),(5.3,5.3)
+ (5.85,5.85),(5.85,5.85)
+ (5.25,5.25),(5.25,5.25)
+ (5.9,5.9),(5.9,5.9)
+ (5.2,5.2),(5.2,5.2)
+ (5.95,5.95),(5.95,5.95)
+ (5.15,5.15),(5.15,5.15)
+ (6,6),(6,6)
+ (5.1,5.1),(5.1,5.1)
+ (5.05,5.05),(5.05,5.05)
+ (5,5),(5,5)
+(21 rows)
+
drop index gist_tbl_box_index;
-- Test that an index-only scan is not chosen, when the query involves the
-- circle column (the circle opclass does not support index-only scans).
diff --git a/src/test/regress/sql/gist.sql b/src/test/regress/sql/gist.sql
index 657b195..b9d398e 100644
--- a/src/test/regress/sql/gist.sql
+++ b/src/test/regress/sql/gist.sql
@@ -109,6 +109,22 @@ select b from gist_tbl where b <@ box(point(5,5), point(6,6));
-- execute the same
select b from gist_tbl where b <@ box(point(5,5), point(6,6));
+-- Also test an index-only knn-search
+explain (costs off)
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by b <-> point(5.2, 5.91);
+
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by b <-> point(5.2, 5.91);
+
+-- Check commuted case as well
+explain (costs off)
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by point(5.2, 5.91) <-> b;
+
+select b from gist_tbl where b <@ box(point(5,5), point(6,6))
+order by point(5.2, 5.91) <-> b;
+
drop index gist_tbl_box_index;
-- Test that an index-only scan is not chosen, when the query involves the
--
2.7.4
>From 48190c2289ca92156fbbab9fa6b86d048fda41c1 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.glu...@postgrespro.ru>
Date: Thu, 7 Mar 2019 23:49:47 +0300
Subject: [PATCH 3/3] Add operator <->(box, point) to SP-GiST box_ops
---
doc/src/sgml/spgist.sgml | 1 +
src/include/catalog/pg_amop.dat | 4 ++
src/test/regress/expected/box.out | 82 +++++++++++++++++++++++++-----
src/test/regress/expected/sanity_check.out | 2 +
src/test/regress/sql/box.sql | 70 ++++++++++++++++++++-----
5 files changed, 135 insertions(+), 24 deletions(-)
diff --git a/doc/src/sgml/spgist.sgml b/doc/src/sgml/spgist.sgml
index a816856..81ddf5d 100644
--- a/doc/src/sgml/spgist.sgml
+++ b/doc/src/sgml/spgist.sgml
@@ -139,6 +139,7 @@
<literal>|&></literal>
</entry>
<entry>
+ <literal><-></literal>
</entry>
</row>
<row>
diff --git a/src/include/catalog/pg_amop.dat b/src/include/catalog/pg_amop.dat
index ebc38ae..232557e 100644
--- a/src/include/catalog/pg_amop.dat
+++ b/src/include/catalog/pg_amop.dat
@@ -1546,6 +1546,10 @@
amopstrategy => '11', amopopr => '|>>(box,box)', amopmethod => 'spgist' },
{ amopfamily => 'spgist/box_ops', amoplefttype => 'box', amoprighttype => 'box',
amopstrategy => '12', amopopr => '|&>(box,box)', amopmethod => 'spgist' },
+{ amopfamily => 'spgist/box_ops', amoplefttype => 'box',
+ amoprighttype => 'point', amopstrategy => '15', amoppurpose => 'o',
+ amopopr => '<->(box,point)', amopmethod => 'spgist',
+ amopsortfamily => 'btree/float_ops' },
# SP-GiST poly_ops (supports polygons)
{ amopfamily => 'spgist/poly_ops', amoplefttype => 'polygon',
diff --git a/src/test/regress/expected/box.out b/src/test/regress/expected/box.out
index 998b522..4d0f169 100644
--- a/src/test/regress/expected/box.out
+++ b/src/test/regress/expected/box.out
@@ -480,23 +480,33 @@ DROP INDEX box_spgist;
--
-- Test the SP-GiST index on the larger volume of data
--
-CREATE TABLE quad_box_tbl (b box);
+CREATE TABLE quad_box_tbl (id int, b box);
INSERT INTO quad_box_tbl
- SELECT box(point(x * 10, y * 10), point(x * 10 + 5, y * 10 + 5))
- FROM generate_series(1, 100) x,
- generate_series(1, 100) y;
+ SELECT (x - 1) * 100 + y, box(point(x * 10, y * 10), point(x * 10 + 5, y * 10 + 5))
+ FROM generate_series(1, 100) x,
+ generate_series(1, 100) y;
-- insert repeating data to test allTheSame
INSERT INTO quad_box_tbl
- SELECT '((200, 300),(210, 310))'
- FROM generate_series(1, 1000);
+ SELECT i, '((200, 300),(210, 310))'
+ FROM generate_series(10001, 11000) AS i;
INSERT INTO quad_box_tbl
- VALUES
- (NULL),
- (NULL),
- ('((-infinity,-infinity),(infinity,infinity))'),
- ('((-infinity,100),(-infinity,500))'),
- ('((-infinity,-infinity),(700,infinity))');
+VALUES
+ (11001, NULL),
+ (11002, NULL),
+ (11003, '((-infinity,-infinity),(infinity,infinity))'),
+ (11004, '((-infinity,100),(-infinity,500))'),
+ (11005, '((-infinity,-infinity),(700,infinity))');
CREATE INDEX quad_box_tbl_idx ON quad_box_tbl USING spgist(b);
+-- get reference results for ORDER BY distance from seq scan
+SET enable_seqscan = ON;
+SET enable_indexscan = OFF;
+SET enable_bitmapscan = OFF;
+CREATE TABLE quad_box_tbl_ord_seq1 AS
+SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
+FROM quad_box_tbl;
+CREATE TABLE quad_box_tbl_ord_seq2 AS
+SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
+FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))';
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = ON;
@@ -578,6 +588,54 @@ SELECT count(*) FROM quad_box_tbl WHERE b ~= box '((200,300),(205,305))';
1
(1 row)
+-- test ORDER BY distance
+SET enable_indexscan = ON;
+SET enable_bitmapscan = OFF;
+EXPLAIN (COSTS OFF)
+SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
+FROM quad_box_tbl;
+ QUERY PLAN
+---------------------------------------------------------
+ WindowAgg
+ -> Index Scan using quad_box_tbl_idx on quad_box_tbl
+ Order By: (b <-> '(123,456)'::point)
+(3 rows)
+
+CREATE TEMP TABLE quad_box_tbl_ord_idx1 AS
+SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
+FROM quad_box_tbl;
+SELECT *
+FROM quad_box_tbl_ord_seq1 seq FULL JOIN quad_box_tbl_ord_idx1 idx
+ ON seq.n = idx.n AND seq.id = idx.id AND
+ (seq.dist = idx.dist OR seq.dist IS NULL AND idx.dist IS NULL)
+WHERE seq.id IS NULL OR idx.id IS NULL;
+ n | dist | id | n | dist | id
+---+------+----+---+------+----
+(0 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
+FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))';
+ QUERY PLAN
+---------------------------------------------------------
+ WindowAgg
+ -> Index Scan using quad_box_tbl_idx on quad_box_tbl
+ Index Cond: (b <@ '(500,600),(200,300)'::box)
+ Order By: (b <-> '(123,456)'::point)
+(4 rows)
+
+CREATE TEMP TABLE quad_box_tbl_ord_idx2 AS
+SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
+FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))';
+SELECT *
+FROM quad_box_tbl_ord_seq2 seq FULL JOIN quad_box_tbl_ord_idx2 idx
+ ON seq.n = idx.n AND seq.id = idx.id AND
+ (seq.dist = idx.dist OR seq.dist IS NULL AND idx.dist IS NULL)
+WHERE seq.id IS NULL OR idx.id IS NULL;
+ n | dist | id | n | dist | id
+---+------+----+---+------+----
+(0 rows)
+
RESET enable_seqscan;
RESET enable_indexscan;
RESET enable_bitmapscan;
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
index 8ff0da1..d6e75ff 100644
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -165,6 +165,8 @@ pg_user_mapping|t
point_tbl|t
polygon_tbl|t
quad_box_tbl|t
+quad_box_tbl_ord_seq1|f
+quad_box_tbl_ord_seq2|f
quad_point_tbl|t
quad_poly_tbl|t
radix_text_tbl|t
diff --git a/src/test/regress/sql/box.sql b/src/test/regress/sql/box.sql
index 6710fc9..cd3e002 100644
--- a/src/test/regress/sql/box.sql
+++ b/src/test/regress/sql/box.sql
@@ -192,28 +192,41 @@ DROP INDEX box_spgist;
--
-- Test the SP-GiST index on the larger volume of data
--
-CREATE TABLE quad_box_tbl (b box);
+CREATE TABLE quad_box_tbl (id int, b box);
INSERT INTO quad_box_tbl
- SELECT box(point(x * 10, y * 10), point(x * 10 + 5, y * 10 + 5))
- FROM generate_series(1, 100) x,
- generate_series(1, 100) y;
+ SELECT (x - 1) * 100 + y, box(point(x * 10, y * 10), point(x * 10 + 5, y * 10 + 5))
+ FROM generate_series(1, 100) x,
+ generate_series(1, 100) y;
-- insert repeating data to test allTheSame
INSERT INTO quad_box_tbl
- SELECT '((200, 300),(210, 310))'
- FROM generate_series(1, 1000);
+ SELECT i, '((200, 300),(210, 310))'
+ FROM generate_series(10001, 11000) AS i;
INSERT INTO quad_box_tbl
- VALUES
- (NULL),
- (NULL),
- ('((-infinity,-infinity),(infinity,infinity))'),
- ('((-infinity,100),(-infinity,500))'),
- ('((-infinity,-infinity),(700,infinity))');
+VALUES
+ (11001, NULL),
+ (11002, NULL),
+ (11003, '((-infinity,-infinity),(infinity,infinity))'),
+ (11004, '((-infinity,100),(-infinity,500))'),
+ (11005, '((-infinity,-infinity),(700,infinity))');
CREATE INDEX quad_box_tbl_idx ON quad_box_tbl USING spgist(b);
+-- get reference results for ORDER BY distance from seq scan
+SET enable_seqscan = ON;
+SET enable_indexscan = OFF;
+SET enable_bitmapscan = OFF;
+
+CREATE TABLE quad_box_tbl_ord_seq1 AS
+SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
+FROM quad_box_tbl;
+
+CREATE TABLE quad_box_tbl_ord_seq2 AS
+SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
+FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))';
+
SET enable_seqscan = OFF;
SET enable_indexscan = ON;
SET enable_bitmapscan = ON;
@@ -232,6 +245,39 @@ SELECT count(*) FROM quad_box_tbl WHERE b @> box '((201,301),(202,303))';
SELECT count(*) FROM quad_box_tbl WHERE b <@ box '((100,200),(300,500))';
SELECT count(*) FROM quad_box_tbl WHERE b ~= box '((200,300),(205,305))';
+-- test ORDER BY distance
+SET enable_indexscan = ON;
+SET enable_bitmapscan = OFF;
+
+EXPLAIN (COSTS OFF)
+SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
+FROM quad_box_tbl;
+
+CREATE TEMP TABLE quad_box_tbl_ord_idx1 AS
+SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
+FROM quad_box_tbl;
+
+SELECT *
+FROM quad_box_tbl_ord_seq1 seq FULL JOIN quad_box_tbl_ord_idx1 idx
+ ON seq.n = idx.n AND seq.id = idx.id AND
+ (seq.dist = idx.dist OR seq.dist IS NULL AND idx.dist IS NULL)
+WHERE seq.id IS NULL OR idx.id IS NULL;
+
+
+EXPLAIN (COSTS OFF)
+SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
+FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))';
+
+CREATE TEMP TABLE quad_box_tbl_ord_idx2 AS
+SELECT rank() OVER (ORDER BY b <-> point '123,456') n, b <-> point '123,456' dist, id
+FROM quad_box_tbl WHERE b <@ box '((200,300),(500,600))';
+
+SELECT *
+FROM quad_box_tbl_ord_seq2 seq FULL JOIN quad_box_tbl_ord_idx2 idx
+ ON seq.n = idx.n AND seq.id = idx.id AND
+ (seq.dist = idx.dist OR seq.dist IS NULL AND idx.dist IS NULL)
+WHERE seq.id IS NULL OR idx.id IS NULL;
+
RESET enable_seqscan;
RESET enable_indexscan;
RESET enable_bitmapscan;
--
2.7.4
