Database Reference
In-Depth Information
Algorithm 18.
SELECT * FROM Landscapes
WHERE Manhattan_distance(Picture_Histo&Center, Example) <= 0.03;
SELECT * FROM (
SELECT Id, Place, Photographer, ROW_NUMBER() OVER (
ORDER BY Manhattan_distance(Picture_Histo&Center, Example) AS rn
FROM Landscapes
) WHERE rn <= 5;
Algorithm 19.
CREATE OPERATOR Manhattan_dist BINDING (BLOB, BLOB)
RETURN FLOAT USING Manhattan_distance;
The execution of queries in FMI-SiR
O
is illustrated in Figure 5. Before posing a query it is necessary to
load the feature vector of the query center into a database variable. If the feature vector is already stored
in the database, it is queried and stored in the variable. Otherwise, the application calls the generateSig-
nature function, providing the query image (Step 1). The DBMS submits the image to the FMI-SiR
O
's
feature extractor, which generates the feature vector that is in turn stored in the variable (steps 2 to 4).
Thereafter, the query is submitted to the DBMS (Step 5), which forwards the distance calculation to the
FMI-SiR
O
's access method library (Step 6a), obtaining the distances (Step 7a). Based on the distances,
the DBMS executes the query and returns the result data to the application (Step 8).
FMI-SiR
O
also provides new metric index structures to speed up similarity queries. They are built on
the associated access method library and are accessible by the query processor through
SQL operators
,
which are defined in the Oracle DBMS as links between user-defined functions and index types. FMI-
SiR
O
defines operators to enable the indexed execution of both range and
k
-NN queries. For instance,
the following instruction creates an operator for range queries employing the Manhattan distance: (see
Algorithm 19)
To create a new index type it is necessary to have a data type implementing the methods required by
the Oracle Extensible Indexing Interface. Such data types in FMI-SiR
O
have the following header: (see
Algorithm 20)where each of these methods maps to an external C++ function that executes the respec-
tive action on the index using access method library. The new index types associate the operators and
the implementation types, as exemplified below: (see Algorithm 21)
The FMI-SiR
O
indexes are managed in the same way as the built-in ones. To execute indexed similar-
ity queries, the user must create an index for the feature vector and write the queries using the operators
associated to the respective index type. For example, the following instructions create an index and
executes the same aforementioned queries, but now using the index: (see Algorithm 22)
The value provided in the PARAMETERS clause of the index creation instruction is the index page
size. Note that indexed k-NN queries in FMI-SiR
O
do not require explicitly ordering the rows according
to their distances to the query center, because the k-NN condition is tested during the index search. The
