比较几何时查询慢 [英] Slow query when comparing geometries

问题描述

我正在尝试比较并创建一个新表.但是需要花费更多时间进行比较.

I am trying to compare and create a new table. But its taking more time to compare.

表1(模式)

+-------------+----------+-------------+
| Column      | Type     | Modifiers   |
|-------------+----------+-------------|
| line_id     | bigint   |             |
| junction    | integer  |             |
| geom        | geometry |             |
+-------------+----------+-------------+
Indexes:
    "points_geom_gix" gist (geom)

其中结包含0或1的地方.

Where junction contains either 0 or 1.

表2

+----------+----------+-------------+
| Column   | Type     | Modifiers   |
|----------+----------+-------------|
| line_id  | bigint   |             |
| geom     | geometry |             |
+----------+----------+-------------+
Indexes:
    "jxn_geom_gix" gist (geom)

我想通过比较两个表的几何形状来创建一个新的表表3.

I want to create a new table table 3 by comparing geometries of two tables.

条件

• 从两个几何相等的表中选择几何.
• 从表1中选择geom，其中联结点= 1并且geom不存在于 表3.

• Select geom from both tables where two geometries are equal.
• Select geom from table1 where junction = 1 and and geom not present in table 3.

我尝试如下

CREATE TABLE table3 as select a.geom from table1 a, table2 b where st_equals(a.geom,b.geom);

(在表3的geom列上创建要点索引)

(create gist index on geom column of table3)

和

INSERT INTO table3 SELECT a.geom from table1 a, table3 b where a.junction = 1 and NOT st_equals(a.geom,b.geom);

但是第二个查询要花费大量时间.

But second query is taking huge time.

有人会帮助我优化查询吗?

Will someone help me in optimizing query?

推荐答案

在最后一个sql中，您将产生近乎笛卡尔的结果.例如，如果表1中有10000个几何，junciton = 1，而表3中不存在，而表3中已经有10000个其他几何，那么对于每个juncition = 1的几何，您将返回10000行. 在这种情况下，当您要查找不在其他表中的某些行时，请使用EXISTS子句，它不会使结果成倍增加，也不会产生笛卡尔坐标.

With your last sql you produce nearly cartesian result. For example if you have 10 000 geometries with junciton =1 in table1 that not existing in table3, and in table 3 you have already 10 000 other geometries then for every geometry with juncition=1 you return 10 000 rows. In such situation when you want to find some row that is not in other table use EXISTS clause it will not multiple your results and will not produce cartesian.

INSERT INTO table3 
SELECT a.geom 
  from table1 a
 where a.junction = 1 
   and NOT exists (select from table3 b 
                    where st_equals(a.geom,b.geom)
                      and st_DWithin(a.geom, b.geom,0));

我编辑了查询-添加了st_Dwithin(a.geom，b.geom，0)，它应该使查询甚至更快，因为不存在的对象应该仅比较它们之间距离为0的这些几何(如果它们之间不存在0的距离)肯定不相等).通常，st_dwithin将使用gist索引来筛选距离不够近的几何图形.

I edited query - added st_Dwithin(a.geom, b.geom,0) it should make query even faster as not exists should compare only these geoms that are in 0 distance between them (if there is no 0 distance between them for sure there are not equal). Generaly st_dwithin will use gist indexes to filter geoms that are not close enough to be equal.

这篇关于比较几何时查询慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！