DISTANCE

On this page

Parameterized Function Syntax
Vector Similarity Search
Utility

The SQL DISTANCE function in Polypheny is a versatile tool primarily used for comparing two arrays based on a specific metric. This function plays a pivotal role in k-nearest-neighbour (kNN) search, allowing for the identification of entries based on their distance to a specific vector.

Parameterized Function Syntax

The general syntax for the DISTANCE function is as follows:

DISTANCE(<target array>, <array to compare with>, <metric> [, <weights>])

<target array>: The array from your table you want to compare with another array.
<array to compare with>: The array you are comparing the target array with.
<metric>: The metric used to calculate the distance. This can be one of the following: ‘L1’, ‘L2’, ‘L2SQUARED’, ‘CHISQUARED’, ‘COSINE’, ‘INNER_PRODUCT’.
<weights> (optional): An array of weights for weighted distance calculations.

Examples

Consider you have a table ProductVectors with a column ProductFeatures storing arrays representing product features. You can use the DISTANCE function to find products similar to a specific product feature vector:

SELECT id, DISTANCE(ProductFeatures, ARRAY[...], 'L2') as dist 
FROM ProductVectors 
ORDER BY dist ASC 
LIMIT 5;

This query will return the IDs of the five products whose feature vectors have the smallest L2 distance to the given array.

To use the function with weights, you could write:

SELECT id, DISTANCE(ProductFeatures, ARRAY[...], 'L2', ARRAY[...]) as dist 
FROM ProductVectors 
ORDER BY dist ASC 
LIMIT 5;

Vector Similarity Search

While Polypheny can always compute the general DISTANCE function internally, it is recommended to write similarity queries in a stricter form, so the computation can be pushed down.

For the computation to be pushed down, two conditions must be met:

The column must be declared as a vector data type.
The storage adapter must support the requested metric.

If either condition is missing, the query will still execute successfully and return the correct result, but Polypheny will compute the distance internally rather than pushing it down.

Similarity search queries can be written using the dedicated, non-parameterized functions listed below. However, it is not always necessary to use it as mentioned in the subsection to Syntax Rules and Limitations.

Dedicated Non-Parameterized Functions

Metric	Named Function	Element Type	Comment
L1	L1_DISTANCE	REAL, FLOAT
L2	L2_DISTANCE	REAL, FLOAT
COSINE	COSINE_DISTANCE	REAL, FLOAT	Returns 1 - cos(a, b), not the similarity.
INNER_PRODUCT	INNER_PRODUCT_DISTANCE	REAL, FLOAT	Returns the negative inner product.
HAMMING	HAMMING_DISTANCE	BOOLEAN
JACCARD	JACCARD_DISTANCE	BOOLEAN	Returns 0.0 when the union is empty.

Syntax Rules and Limitations

For L1, L2, COSINE, and INNER_PRODUCT, the general and dedicated syntaxes are equivalent. For example, DISTANCE(f, ARRAY[...], 'L2') executes exactly like L2_DISTANCE(f, ARRAY[...]). Furthermore the corresponding infix operators can be used interchangeably with the non-parameterized distance functions for all metrics listed above.
As soon as a <weights> array is introduced, the query bypasses the adapter and is always computed internally by Polypheny.
You currently must use the dedicated functions for HAMMING and JACCARD. The general DISTANCE syntax does not support them because it only accepts numeric arrays.

Examples Using Non-Parameterized Functions

CREATE TABLE ProductVectors (
    id             INTEGER NOT NULL,
    ProductFeatures REAL VECTOR(3),
    BinaryFeatures  BOOLEAN VECTOR(3),
    PRIMARY KEY (id)
) ON STORE postgres;

SELECT id, L2_DISTANCE(ProductFeatures, ARRAY[1.0, 2.0, 3.0]) AS dist
FROM ProductVectors
ORDER BY dist ASC
LIMIT 5;

SELECT id, HAMMING_DISTANCE(BinaryFeatures, ARRAY[TRUE, FALSE, FALSE]) AS dist
FROM ProductVectors
ORDER BY dist ASC
LIMIT 5;

Adding a vector index can further speed up similarity search queries. Such an index is approximate so the result can differ from the exact distance. See Vector Indexes.

Utility

The DISTANCE function is particularly useful for scenarios where you need to identify similarities or differences between data points in multi-dimensional space. These include:

Recommendation Systems: You can use the DISTANCE function to recommend similar products or services to users based on their past behaviors or preferences.
Clustering: The function can be useful in clustering analysis where you want to group similar data points together.
Anomaly Detection: The DISTANCE function can help identify outliers in your dataset, which deviate significantly from the other data points.

Remember that the right choice of distance metric depends on the nature of your data and the specific requirements of your use case.

Documentation Markdown Syntax