Hi.
I've faced an issue with Box type comparison that exists almost for a five years.
> create table t (b box);
CREATE TABLE
> select count(*), b from t group by b;
ERROR: could not identify an equality operator for type box
As mentioned in http://www.postgresql.org/message-id/Pine.LNX.4.64.1012040051500.12632@sn.sai.msu.ru
That can be fixed by b-tree equality for boxes, but we need some decisions there. We can compare
floats up to certain threshold or strictly, and box equality can be defined as coordinate equality or as equality of
areas. In a case of threshold-based comparison we will not lose equality due to rounding errors, for example
applying commutative functions in different order will preserve equality, but we can face non-transitive equalities,
like
box1 == box2, box2 == box3, but box1 != box3 and GROUP BY can produce strange results. With strict comparison equality
istransitive, but we can lose that equality due to rounding.
Now in geo_decls.h we have:
#define EPSILON 1.0E-06
#define FPeq(A,B) (fabs((A) - (B)) <= EPSILON)
and equality means equal areas.
But for GROUP BY it better be opposite: equality of coordinates and strict comparison.
Simplest workaround in my perspective is to add another operator for box type (e.g. ==) that will perform strict
comparison
of coordinates and use it in b-tree ops.
Any objections/suggestions?
-----------------
Stas Kelvich
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company