Querying Multimedia Data by Similarity in Relational DBMS - Advanced Database Query Systems - page 343

The construction to express a similarity join compares a complex attribute from the left table R to a
compatible attribute from the right table S , in the format R.attr 1 NEAR S.attr 2 <...>. The similarity join
syntaxes are expressed as regular joins, either in the FROM or in the WHERE clauses. Two complex
attributes are compatible if they are associated with the same metric (consequently, they are of the same
type). The construction R.attr 1 NEAR S.attr 2 RANGE ξ expresses a range join, the construction R.attr 1
NEAR S.attr 2 STOP AFTER k expresses a nearest join, and the construction R.attr 1 NEAR ANY S.attr 2
STOP AFTER k expresses a closest join. For example, the following command retrieves the 5 landscapes
pairs whose pictures look more similar to each other: (see Algorithm 11)
Variations on the basic command can be expressed with modifiers in the command. If one wants to
retrieve the most dissimilar elements instead of the most similar, the keyword NEAR is replaced by the
keyword FAR. If more than one metric was defined, the default one is used, unless the clause BY <met-
ric name> is declared. Concerning the running example, to select up to fifty landscapes that are among
the most similar pictures to a given query image considering the Histo&Center metric and the fifty most
similar pictures to another query image regarding the Texture metric, the following command can be
stated: (see Algorithm 12)
Queries with predicates limited to k neighbors (either selections or joins) can take into account the
occurrence of ties. The default behavior of a k -limited query is retrieving k elements without ties, as it
is the behaviour of most of the works reported in the literature. However, the SQL extension allows
specifying WITH TIE LIST following the STOP AFTER specification, to ask for every element tied at
the same distance of the k -th nearest neighbor to the query element.
Both STOP AFTER and RANGE can be specified in the same query. In this case, the answer is
limited by having at most k elements and elements not farther (or nearer) than a distance ξ from the
query center. For example, the command (see Algorithm 13)retrieves from the 5 images most similar to
the query image that are not farther than 0.03 units from the query image. On the other hand, if neither
STOP AFTER nor RANGE is specified, then RANGE 0 (zero) is assumed.
Algorithm 11.
SELECT * FROM Landscapes L1, Landscapes L2
WHERE L1.Picture NEAR L2.Picture STOP AFTER 5;
Algorithm 12.
SELECT * FROM Landscapes WHERE
Picture NEAR 'example1.jpg' By Histo&Center STOP AFTER 50 AND
Picture NEAR 'example2.jpg' By Texture STOP AFTER 50;
Algorithm 13.
SELECT * FROM Landscapes
WHERE Picture NEAR 'example.jpg' STOP AFTER 5 RANGE 0.03;