agent-app/rag-server/face-feat68

RAG示例：人脸向量数据库

• pgvector 介绍文档 https://cheatsheet.md/zh/vector-database/pgvector.zh

NODE环境

npm install canvas --canvas_binary_host_mirror=https://registry.npmmirror.com/-/binary/canvas

数据库环境

• https://pkgs.org/download/postgresql-11-pgvector-dbgsym

• https://learn.microsoft.com/zh-cn/azure/cosmos-db/postgresql/howto-use-pgvector

  # Add PostgreSQL repository as described on its homepage:
  # https://wiki.postgresql.org/wiki/Apt
  # Update the package index:
  # Create the file repository configuration: 
  sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list' 
  # Import the repository signing key: 
  wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add - 
  sudo apt-get update
  
  # Install postgresql-11-pgvector deb package:
  apt search postgresql pgvector
  sudo apt-get install postgresql-15-pgvector
  
  # 激活插件
  su postgres
  psql
  CREATE EXTENSION IF NOT EXISTS vector
  SCHEMA public;
  

• 修改配置文件 postgresql.conf

  ## 如果插件未在列表中显示，你需要编辑postgresql.conf文件并将vector插件的名称添加到shared_preload_libraries配置项中。例如：
  shared_preload_libraries = 'vector';
  
  ## 请确保你已经正确安装了vector插件，并且已经在你的PostgreSQL数据库中启用了该插件。运行以下命令来确认插件是否已加载：
  su postgres
  psql
  SHOW shared_preload_libraries;
  
  ## 保存文件后，重新启动PostgreSQL服务器以使更改生效。
  service postgresql restart
  
  # 语句查询
  
  <-> 	欧几里得距离
  <#> 	负内积
  <=> 	余弦距离
  

数据范式

• 创建 Schema "FaceReport"

  • user Pointer<_User> 用户指针
  • photo String类型 正脸照片图片地址
  • feat68 Array类型 存储面部68特征点向量

  • title String类型 面部数据标题

    # 安装
    npm i parse-dashboard -g
    # 启动看板
    parse-dashboard --appId dev --masterKey devmk --serverURL http://dev.fmode.cn:1337/parse --appName DevServer
    

• 创建拓展字段

  -- 数据字段
  ALTER TABLE "FaceReport" ADD COLUMN feat68_vector vector(128);
  

测试向量检索语句

-- 列出所有的面部数据
SELECT * FROM "FaceReport";

-- 相似度计算


select "objectId" as id,"title","photo","createdAt", array_agg(item)::vector(128) <=> ARRAY[-0.09852483868598938,0.0748210921883583,0.031563639640808105,-0.1323895901441574,-0.14048264920711517,-0.012380637228488922,-0.1202174723148346,-0.11705487966537476,0.12698683142662048,-0.218950554728508,0.16882860660552979,-0.109050452709198,-0.1343892216682434,-0.03263712674379349,-0.08884422481060028,0.238133043050766,-0.1850930005311966,-0.1274271309375763,-0.031015701591968536,0.017501287162303925,0.08274881541728973,-0.02009255811572075,-0.03606291860342026,0.07826575636863708,-0.07460039108991623,-0.30363327264785767,-0.11924955248832703,-0.013626940548419952,-0.045659542083740234,-0.05412667989730835,-0.06456897407770157,0.06692294776439667,-0.18031014502048492,-0.0011228621006011963,-0.009975329041481018,0.11013024300336838,-0.00493689626455307,-0.08558250218629837,0.10225555300712585,0.02723834663629532,-0.25496453046798706,0.10651601850986481,0.05334452539682388,0.22334304451942444,0.15225529670715332,0.007631596177816391,0.019638724625110626,-0.1368170976638794,0.14633668959140778,-0.18137460947036743,0.026605065912008286,0.10837362706661224,0.06883929669857025,0.027700066566467285,0.04362597316503525,-0.11393266171216965,0.050470441579818726,0.1353151500225067,-0.11388298869132996,0.03125010430812836,0.10460706800222397,-0.03446819260716438,-0.01624279096722603,-0.16958579421043396,0.2160552591085434,0.17320333421230316,-0.11017127335071564,-0.25261038541793823,0.12614592909812927,-0.096388079226017,-0.08947526663541794,0.07287181168794632,-0.14514991641044617,-0.17512711882591248,-0.24459412693977356,0.02685403823852539,0.3455549478530884,0.16986696422100067,-0.16841250658035278,0.05638973042368889,-0.006193479523062706,-0.02861897647380829,0.0987851619720459,0.18471932411193848,0.014859762042760849,0.04832335561513901,-0.0328541025519371,0.019863054156303406,0.2237083464860916,-0.04242844134569168,-0.017962101846933365,0.2283974587917328,-0.01908118650317192,0.039537280797958374,-0.044663406908512115,-0.03942383825778961,-0.10947410017251968,0.07384784519672394,-0.13645696640014648,-0.022529728710651398,0.0006778687238693237,-0.024799048900604248,-0.04216424375772476,0.11292757093906403,-0.13180139660835266,0.15981130301952362,-0.07501605898141861,0.037828102707862854,-0.04268859326839447,0.019408375024795532,-0.09866605699062347,0.030573979020118713,0.11470682919025421,-0.18785522878170013,0.1709749549627304,0.135052889585495,0.11981010437011719,0.07612602412700653,0.1714332103729248,0.08336580544710159,0.03716219961643219,-0.012700825929641724,-0.23010127246379852,-0.008748907595872879,0.13277511298656464,-0.09321203827857971,0.08568182587623596,0.012791123241186142]::vector(128) as cos
         from (
          SELECT *, jsonb_array_elements("feat68")::float AS item from (
            SELECT "objectId","title","photo","feat68","createdAt"
            FROM "FaceReport"
            WHERE "feat68" is not null
            ORDER BY "createdAt" DESC 
            limit 20
            ) as rt1
          ) as rt
          GROUP BY "objectId",rt."photo","createdAt","title"
          ORDER BY cos;