The traditional comparison operators (=, <, and so on) can be applied to box types. Excepted equality and inequality, the other comparison operators are not useful in the real world but allow B-tree indexes to be constructed on box types. These operators define a total order. They compare first the SRID of spatiotemporal boxes, then the dimensions the boxes have, whether spatiotemporal boxes are geodetic, and the value type of temporal boxes. Boxes agreeing on all of these are compared on their time dimension and, if those are equal, on their value or space dimension.
Traditional comparisons
box {=, <>, <, >, <=, >=} box → boolean
cmp(box,box) → integer
Function cmp returns -1, 0, or 1 depending on whether the first argument is, respectively, less than, equal to, or greater than the second one.
SELECT tbox 'TBOXINT XT([1,1],[2001-01-01,2001-01-04])' = tbox 'TBOXINT XT([2,2], [2001-01-03,2001-01-05])'; -- false SELECT tbox 'TBOXFLOAT XT([1,1],[2001-01-01,2001-01-04])' <> tbox 'TBOXFLOAT XT([2,2],[2001-01-03,2001-01-05])'; -- true SELECT tbox 'TBOXINT XT([1,1],[2001-01-01,2001-01-04])' < tbox 'TBOXINT XT([1,2],[2001-01-03,2001-01-05])'; -- true SELECT tbox 'TBOXFLOAT XT([1,1],[2001-01-03,2001-01-04])' > tbox 'TBOXFLOAT XT((1,2),[2001-01-01,2001-01-05])'; -- true SELECT tbox 'TBOXINT XT([1,1],[2001-01-01,2001-01-04])' <= tbox 'TBOXINT XT([2,2], [2001-01-03,2001-01-05])'; -- true SELECT tbox 'TBOXFLOAT XT([1,1],[2001-01-01,2001-01-04])' >= tbox 'TBOXFLOAT XT([2,2], [2001-01-03,2001-01-05])'; -- false SELECT cmp(tbox 'TBOXFLOAT XT([1,1],[2001-01-01,2001-01-04])', tbox 'TBOXFLOAT XT([2,2],[2001-01-03,2001-01-05])'); -- -1
Hash values
hash(box) → integer hashExtended(box,seed bigint) → bigint
Function hash returns a 32-bit hash value and function hashExtended a 64-bit hash value computed with the given seed. Equal values have equal hash values, which is what hash indexes, hash joins, and hash aggregation rely on.
SELECT hash(tbox 'TBOXINT XT([1,1],[2001-01-01,2001-01-04])') = hash(tbox 'TBOXINT XT([1,2),[2001-01-01,2001-01-04])'); -- true SELECT hashExtended(stbox 'STBOX X((1,1),(2,2))', 0) <> hashExtended(stbox 'STBOX X((1,1),(2,2))', 1); -- true