Tiles also support the full set of comparison operators. The ordering is total and is defined on the QUADBIN cell first, then the pixel type, the width, the height, and finally the pixel bytes; it is meant for indexing and deduplication rather than for a spatial interpretation. A default btree operator class makes ORDER BY, DISTINCT and merge joins work on raquet columns, and a default hash operator class enables hash joins and hash aggregation.
Traditional comparisons
cell {=, <>, <, >, <=, >=} cell → boolean
tcell {=, <>, <, >, <=, >=} tcell → boolean
The functions backing these operators are eq, ne, lt, le, ge and gt, together with cmp({cell,tcell},{cell,tcell}) → integer, which returns -1, 0, or 1. The operators over h3index are those of the h3 extension, which the functions answer as.
SELECT cmp(h3index '831c02fffffffff', h3index '8a2a1072b59ffff'); -- -1 SELECT th3index '831c02fffffffff@2001-01-01' = th3index '831c02fffffffff@2001-01-01'; -- t SELECT tquadbin '48a6227affffffff@2001-01-01' = tquadbin '48a6227affffffff@2001-01-01'; -- t SELECT ts2cell '47c3c@2001-01-01' = ts2cell '47c3c@2001-01-01'; -- t
Ever and always comparisons
{cell,tcell} {?=, ?<>, %=, %<>} {cell,tcell} → boolean
The probe operand may be a bare cell identifier or another tcell trajectory, in either operand position. The e-prefixed forms hold when the relationship holds at at least one instant, the a-prefixed forms when it holds across the entire time span.
SELECT th3index '[831c02fffffffff@2001-01-01, 880326b885fffff@2001-01-05]' ?= h3index '880326b885fffff'; -- t SELECT tquadbin '[48a6227affffffff@2001-01-01, 485623ffffffffff@2001-01-05]' ?= quadbin '485623ffffffffff'; -- t SELECT ts2cell '[47c3c@2001-01-01, 47c4@2001-01-05]' ?= s2cell '47c4'; -- t
Temporal comparisons
{cell,tcell} {#=, #<>} {cell,tcell} → tbool
Return a temporal boolean giving the per-instant equality, or inequality, between a temporal cell and a probe
SELECT th3index '831c02fffffffff@2001-01-01' #= h3index '831c02fffffffff'; -- t@2001-01-01 SELECT tquadbin '48a6227affffffff@2001-01-01' #= quadbin '48a6227affffffff'; -- t@2001-01-01 SELECT ts2cell '47c3c@2001-01-01' #= s2cell '47c3c'; -- t@2001-01-01
Compare two Raquet tiles
raquet {=, <>, <, <=, >=, >} raquet → boolean
The functions backing these operators are eq, ne, lt, le, ge and gt, together with cmp(raquet,raquet) → integer, which returns -1, 0, or 1.
-- Two tiles with the same cell, layout and pixels are equal
SELECT raquet('\x0102'::bytea, 2, 1, quadbin '4817ffffffffffff',
'uint8') = raquet('\x0102'::bytea, 2, 1, quadbin '4817ffffffffffff', 'uint8');
-- true
-- Deduplicate tiles, which the btree and hash operator classes support
SELECT DISTINCT tile FROM elevation_tiles;
Hash values
hash({cell,cellset,tcell,raquet}) → integer
hashExtended({cell,cellset,tcell,raquet},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 having equal hash values. Over h3index they answer as the functions h3index_hash and h3index_hash_extended of the h3 extension, which provides the type.
SELECT hash(th3index '831c02fffffffff@2001-01-01') =
hash(th3index '{831c02fffffffff@2001-01-01}');
-- t
SELECT hash(quadbinset '{48a6227affffffff, 485623ffffffffff}') =
hash(quadbinset '{485623ffffffffff, 48a6227affffffff}');
-- t
SELECT hashExtended(s2cell '47c3c', 0) <> hashExtended(s2cell '47c3c', 1);
-- t