时间戳

首页 > 解决方案 > 我可以使用 <-> 在 PostgreSQL 中找到数组之间的距离吗？

sql - 我可以使用 <-> 在 PostgreSQL 中找到数组之间的距离吗？

问题描述

据我从这篇文章中了解到，在处理几何数据类型时，您可以使用 <-> 距离运算符找到最近的邻居：

SELECT name, location --location is point
FROM geonames
ORDER BY location <-> '(29.9691,-95.6972)'
LIMIT 5;

您还可以使用 SP-GiST 索引进行一些优化：

CREATE INDEX idx_spgist_geonames_location ON geonames USING spgist(location);

但是我在文档中找不到任何关于将 <-> 运算符与数组一起使用的信息。double precision[]例如，如果我要使用而不是执行相同的查询point，那会起作用吗？

标签： sqlpostgresqlknn

解决方案

显然，我们不能。例如，我有一个简单的表：

CREATE TABLE test (
  id SERIAL PRIMARY KEY,
  loc double precision[]
);

我想从中查询文档，按距离排序，

SELECT loc FROM test ORDER BY loc <-> ARRAY[0, 0, 0, 0]::double precision[];

它不起作用：

 Query Error: error: operator does not exist: double precision[] <-> double precision[]

文档也没有提到数组的 <-> 。我在这个问题的接受答案中找到了一种解决方法，但它带来了一些限制，特别是在数组的长度上。虽然有一篇文章（用俄语写的），它提出了关于数组大小限制的解决方法。创建样本表：

import postgresql

def setup_db():
    db = postgresql.open('pq://user:pass@localhost:5434/db')
    db.execute("create extension if not exists cube;")
    db.execute("drop table if exists vectors")
    db.execute("create table vectors (id serial, file varchar, vec_low cube, vec_high cube);")
    db.execute("create index vectors_vec_idx on vectors (vec_low, vec_high);")

元素插入：

query = "INSERT INTO vectors (file, vec_low, vec_high) VALUES ('{}', CUBE(array[{}]), CUBE(array[{}]))".format(
            file_name,
            ','.join(str(s) for s in encodings[0][0:64]),
            ','.join(str(s) for s in encodings[0][64:128]),
        )
db.execute(query)

元素查询：

import time
import postgresql
import random

db = postgresql.open('pq://user:pass@localhost:5434/db')

for i in range(100):
    t = time.time()
    encodings = [random.random() for i in range(128)]

    threshold = 0.6
    query = "SELECT file FROM vectors WHERE sqrt(power(CUBE(array[{}]) <-> vec_low, 2) + power(CUBE(array[{}]) <-> vec_high, 2)) <= {} ".format(
        ','.join(str(s) for s in encodings[0:64]),
        ','.join(str(s) for s in encodings[64:128]),
        threshold,
    ) + \
            "ORDER BY sqrt(power(CUBE(array[{}]) <-> vec_low, 2) + power(CUBE(array[{}]) <-> vec_high, 2)) ASC LIMIT 1".format(
                ','.join(str(s) for s in encodings[0:64]),
                ','.join(str(s) for s in encodings[64:128]),
            )
    print(db.query(query))
    print('inset time', time.time() - t, 'ind', i)

推荐阅读