Re: Add missing operator <->(box, point)

Attached 2nd version of the patches.

On 20.04.2019 16:41, Fabien COELHO wrote:

> About the test, I'd suggest to name the result columns, eg "pt to box
> dist" and "box to pt dist", otherwise why all is repeated is unclear.

Fixed.

On 02.07.2019 7:01, Tom Lane wrote:

> [ warning, drive-by comment ahead ]
>
> Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> writes:
>> I notice that other distance tests do not test for commutativity. Are they
>> also not implemented, or just not tested? If not implemented, I'd suggest
>> to add them in the same batch.
> Yeah ... just looking at operators named <->, I see
>
> regression=# select oid, oid::regoperator, oprcom, oprcode from pg_operator where oprname = '<->';
> oid | oid | oprcom | oprcode
> ------+----------------------+--------+---------------------------
> 517 | <->(point,point) | 517 | point_distance
> 613 | <->(point,line) | 0 | dist_pl
> 614 | <->(point,lseg) | 0 | dist_ps
> 615 | <->(point,box) | 0 | dist_pb
> 616 | <->(lseg,line) | 0 | dist_sl
> 617 | <->(lseg,box) | 0 | dist_sb
> 618 | <->(point,path) | 0 | dist_ppath
> 706 | <->(box,box) | 706 | box_distance
> 707 | <->(path,path) | 707 | path_distance
> 708 | <->(line,line) | 708 | line_distance
> 709 | <->(lseg,lseg) | 709 | lseg_distance
> 712 | <->(polygon,polygon) | 712 | poly_distance
> 1520 | <->(circle,circle) | 1520 | circle_distance
> 1522 | <->(point,circle) | 3291 | dist_pc
> 3291 | <->(circle,point) | 1522 | dist_cpoint
> 3276 | <->(point,polygon) | 3289 | dist_ppoly
> 3289 | <->(polygon,point) | 3276 | dist_polyp
> 1523 | <->(circle,polygon) | 0 | dist_cpoly
> 1524 | <->(line,box) | 0 | dist_lb
> 5005 | <->(tsquery,tsquery) | 0 | pg_catalog.tsquery_phrase
> (20 rows)
>
> It's not clear to me why to be particularly more excited about
> <->(box, point) than about the other missing cases here.
>
> regards, tom lane

The original goal was to add support of ordering by distance to point to
all geometric opclasses. As you can see, GiST and SP-GiST box_ops has no
distance operator while more complex circle_ops and poly_ops have it:

SELECT
amname,
opcname,
amopopr::regoperator AS dist_opr
FROM
pg_opclass LEFT JOIN
pg_amop ON amopfamily = opcfamily AND amoppurpose = 'o',
pg_am,
pg_type
WHERE
opcmethod = pg_am.oid AND
opcintype = pg_type.oid AND
typcategory = 'G'
ORDER BY 1, 2;

amname | opcname | dist_opr
--------+-------------------+--------------------
brin | box_inclusion_ops |
gist | box_ops |
gist | circle_ops | <->(circle,point)
gist | point_ops | <->(point,point)
gist | poly_ops | <->(polygon,point)
spgist | box_ops |
spgist | kd_point_ops | <->(point,point)
spgist | poly_ops | <->(polygon,point)
spgist | quad_point_ops | <->(point,point)
(9 rows)

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).

--
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company