Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hkaQb-0007nx-NT for pgsql-hackers@arkaria.postgresql.org; Mon, 08 Jul 2019 20:40:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hkaQa-0005iT-HH for pgsql-hackers@arkaria.postgresql.org; Mon, 08 Jul 2019 20:40:00 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hkaQa-0005dv-1d for pgsql-hackers@lists.postgresql.org; Mon, 08 Jul 2019 20:40:00 +0000 Received: from cyclops.postgrespro.ru ([93.174.131.138] helo=mail.postgrespro.ru) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hkaQU-0004rt-L8 for pgsql-hackers@postgresql.org; Mon, 08 Jul 2019 20:39:59 +0000 Received: from localhost (localhost [127.0.0.1]) by mail.postgrespro.ru (Postfix) with ESMTP id 73A2821C2337; Mon, 8 Jul 2019 23:39:52 +0300 (MSK) X-Virus-Scanned: Debian amavisd-new at postgrespro.ru X-Spam-Flag: NO X-Spam-Score: 0 X-Spam-Level: X-Spam-Status: No, score=x tagged_above=-99 required=4 WHITELISTED tests=[] autolearn=unavailable Received: from [10.100.236.29] (unknown [195.82.154.27]) (using TLSv1.2 with cipher ECDHE-RSA-AES128-GCM-SHA256 (128/128 bits)) (Client did not present a certificate) by mail.postgrespro.ru (Postfix) with ESMTPSA id C4A8021C22FC; Mon, 8 Jul 2019 23:39:48 +0300 (MSK) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mail; t=1562618389; bh=sf/zxc0NAfxAg6gEJ/62baazMRM7sMT8mbe/o5YW43U=; h=Subject:To:Cc:References:From:Date:In-Reply-To; b=BKfa+2NUKtgtAHlXjZwLkbfoKd1rTMJhrUQVszyRQZlUzVeQOBvz4iyRmAejpfRDi 6Qm/OornQyqepPyhLHdxQtD9lRKaa4piV9NyW4xO9hE1L7gGPHbcyrohLSJlVD954G NkGD53UNxwVTxAIVOrLyDA2y+qnPP7AVoFEHfV10= Subject: Re: Add missing operator <->(box, point) To: Alexander Korotkov , PostgreSQL Hackers Cc: Tom Lane References: From: Nikita Glukhov Organization: Postgres Professional Message-ID: <9c1f39b6-7e11-fbda-d79a-3ff8a40eb1a2@postgrespro.ru> Date: Mon, 8 Jul 2019 23:38:07 +0300 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101 Thunderbird/60.7.2 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------C466D744B1EAB9FA85D22553" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------C466D744B1EAB9FA85D22553 Content-Type: multipart/alternative; boundary="------------EC91E1103968D1D98DAC6B30" --------------EC91E1103968D1D98DAC6B30 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit 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 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 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 --------------EC91E1103968D1D98DAC6B30 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 7bit 
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
--------------EC91E1103968D1D98DAC6B30-- --------------C466D744B1EAB9FA85D22553 Content-Type: text/x-patch; name="0001-Add-missing-distance-operators-v03.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0001-Add-missing-distance-operators-v03.patch" From 14a0e22e1b68b084dd75a7f660955b52b6aaae79 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov 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 --------------C466D744B1EAB9FA85D22553 Content-Type: text/x-patch; name="0002-Add-operator-box-point-to-GiST-box_ops-v03.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0002-Add-operator-box-point-to-GiST-box_ops-v03.patch" From c79cea6630f0adfc95d7c803607257c93f959e87 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov 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 @@ ~= + <-> 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 --------------C466D744B1EAB9FA85D22553 Content-Type: text/x-patch; name="0003-Add-operator-box-point-to-SP-GiST-box_ops-v03.patch" Content-Transfer-Encoding: 7bit Content-Disposition: attachment; filename="0003-Add-operator-box-point-to-SP-GiST-box_ops-v03.patch" From 48190c2289ca92156fbbab9fa6b86d048fda41c1 Mon Sep 17 00:00:00 2001 From: Nikita Glukhov 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 @@ |&> + <-> 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 --------------C466D744B1EAB9FA85D22553--