计算距离的查询卡住了 PostgresDB [英] The query for calculating distance stuck the PostgresDB

问题描述

我的 PostgreSQL 数据库函数有问题.

I have a problem with my PostgreSQL Database Function.

我正在尝试使用 PostGIS 工具计算两个地理点之间的距离.这两个点在单独的表中，这两个表之间的 KEY 是 site_id 列.

I am trying to calculate the distance between two Geo points with PostGIS tools. The two points are in separate tables, the KEY between those two tables is the site_id column.

sites 表结构为:

The sites table structure is:

CREATE TABLE sites(
  site_id INT,
  site_name text,
  latitude float ( 6 ),
  longitude float ( 6 ),
  geom geometry

);

点表结构为:

CREATE TABLE dots(
  dot_id INT,
  site_id INT,
  latitude float ( 6 ),
  longitude float ( 6 ),
  rsrp float ( 6 ),
  dist INT,
  project_id INT,
  dist_from_site INT,
  geom geometry,
  dist_from_ref INT;
);

计算距离的函数适用于小表，但不适用于 50K 行.

The function that calculates the distance is working on small tables but not on 50K rows.

UPDATE dots 
  SET dist_from_site = t.my_dist
  FROM (
      SELECT dots.site_id, ROUND(100*ST_Distance(dots.geom, sites.geom)) my_dist
      FROM  dots 
      INNER JOIN sites on dots.site_id = sites.site_id 
      WHERE sites.site_id = dots.site_id AND dots.dist_from_site is NULL
  ) t 
  WHERE dots.site_id = t.site_id AND dots.dist_from_site is NULL;

知道如何解决运行时的问题并使PGDB不会卡住吗?

Any idea how to solve the problem on run time and make the PGDB won't be stuck?

表格图片:

推荐答案

你不需要在 FROM 子句中重复点表:

You don't need to repeat the dots table in the FROM clause:

UPDATE dots d
SET dist_from_site =  ROUND(100*ST_Distance(d.geom, s.geom))
FROM sites s
WHERE d.site_id = s.site_id
AND d.dist_from_site is NULL
        ;

---

我希望 site_id 是唯一的，至少在 sites 中是这样，否则每个目标行都会有多个更新.

---

I hope that site_id is UNIQUE, at least in sites, otherwise you would get multiple updates per target row.

这篇关于计算距离的查询卡住了 PostgresDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！