时间戳

首页 > 解决方案 > 使用 PostGIS 查找最近点

python - 使用 PostGIS 查找最近点

问题描述

在带有 PostGIS 扩展的 PostgreSQL 12 上，我有两个表定义如下：

CREATE TABLE table_a (
    id_a integer,
    id_b integer,    
    coord geometry,
);

CREATE INDEX table_a_coord_idx ON table_a USING gist (coord);

CREATE TABLE table_b (
    id_b integer,
    coord geometry,
);

CREATE INDEX table_b_coord_idx ON table_b USING gist (coord);

这两个表都有大约 300 万个条目。这些coord列仅包含点几何。的值id_b最初为空。

我的目标是找到table_a最近点中的每个点table_b并填写该列id_b。我编写了一个小的 Python 脚本来通过基于索引的 KNN 搜索来实现这一点：

import psycopg2 as pg

conn = pg.connect()
cur = conn.cursor()

cnt = 0
cur.execute('SELECT id_a, coord FROM table_a WHERE id_b IS NULL')
for row in cur.fetchall():
    cnt += 1
    cur.execute('SELECT id_b FROM table_b ORDER BY geom <-> %s LIMIT 1;', (row[1],))
    nearest_vertex = cur.fetchone()
    cur.execute('UPDATE table_a SET id_b=%s WHERE id_a=%s', (nearest_vertex[0], row[0]))
    if cnt % 1000 == 0:
        conn.commit()
conn.commit()

此代码有效。但平均需要 0.6 秒才能完成一个条目，因此完成所有条目需要大约三周的时间。

有谁知道如何加快这个过程？

标签： pythonpostgresqlpostgisknn

解决方案

在循环中逐个处理记录会导致大量网络流量流向数据库。
相反，尝试在单个语句中一次更新所有条目（如果您愿意，可以从 pyton 脚本发送）。

UPDATE table_a 
SET id_b = (
  SELECT id_b 
  FROM table_b 
  ORDER BY table_b.geom <-> table_a.geom 
  LIMIT 1
)
WHERE id_b IS NULL;

推荐阅读