pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Nikita Glukhov <n.gluhov@postgrespro.ru>
To: Alexander Korotkov <a.korotkov@postgrespro.ru>
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: Add missing operator <->(box, point)
Date: Mon, 8 Jul 2019 23:38:07 +0300
Message-ID: <9c1f39b6-7e11-fbda-d79a-3ff8a40eb1a2@postgrespro.ru> (raw )
In-Reply-To: <CAPpHfdv-oc10OsXyKuj5LHG9cFK2Wi0uMS3A-rjUs-q4mY9QFQ@mail.gmail.com >
References: <f71ba19d-d989-63b6-f04a-abf02ad9345d@postgrespro.ru >
<CAPpHfdv-oc10OsXyKuj5LHG9cFK2Wi0uMS3A-rjUs-q4mY9QFQ@mail.gmail.com >
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.gluhov@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.gluhov@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
Attachments:
[text/x-patch] 0001-Add-missing-distance-operators-v03.patch (95.4K, ../9c1f39b6-7e11-fbda-d79a-3ff8a40eb1a2@postgrespro.ru/3-0001-Add-missing-distance-operators-v03.patch)
download | inline diff:
From 14a0e22e1b68b084dd75a7f660955b52b6aaae79 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@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
[text/x-patch] 0002-Add-operator-box-point-to-GiST-box_ops-v03.patch (9.3K, ../9c1f39b6-7e11-fbda-d79a-3ff8a40eb1a2@postgrespro.ru/4-0002-Add-operator-box-point-to-GiST-box_ops-v03.patch)
download | inline diff:
From c79cea6630f0adfc95d7c803607257c93f959e87 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@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
[text/x-patch] 0003-Add-operator-box-point-to-SP-GiST-box_ops-v03.patch (9.4K, ../9c1f39b6-7e11-fbda-d79a-3ff8a40eb1a2@postgrespro.ru/5-0003-Add-operator-box-point-to-SP-GiST-box_ops-v03.patch)
download | inline diff:
From 48190c2289ca92156fbbab9fa6b86d048fda41c1 Mon Sep 17 00:00:00 2001
From: Nikita Glukhov <n.gluhov@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
view thread (10+ messages) latest in thread
Message-ID: <9c1f39b6-7e11-fbda-d79a-3ff8a40eb1a2@postgrespro.ru>
Permalink: ../9c1f39b6-7e11-fbda-d79a-3ff8a40eb1a2@postgrespro.ru/
Also on: postgresql.org/message-id/9c1f39b6-7e11-fbda-d79a-3ff8a40eb1a2@postgrespro.ru
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: n.gluhov@postgrespro.ru, a.korotkov@postgrespro.ru, tgl@sss.pgh.pa.us
Subject: Re: Add missing operator <->(box, point)
In-Reply-To: <9c1f39b6-7e11-fbda-d79a-3ff8a40eb1a2@postgrespro.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox