sql - 为什么我的Postgis ST_Intersects和基于ST_Azimuth的查询速度慢？

我有一个表，包含大约300 K路线，起点，终点，路线作为折线类型和多边形类型。
我做了一个self-join来查找路线之间的交叉点，但是查询非常慢。即使我运行explain analyze这个分析也没有返回。

CREATE INDEX gist_index_geo_poly_gon ON geo_akin USING gist (geo_polygon)
CREATE INDEX gist_index_geo_poly_line ON geo_akin USING gist (geo_polyline)
CREATE INDEX idx_id ON geo_akin USING btree (id)

我有上面的索引，查询如下：

 select
 ST_Intersection(second.geo_polyline, first.geo_polygon)  as intersection,
from geo_akin first
   , geo_akin second
where second.id != first.id
  and abs(sin(ST_Azimuth(second.actual_start_point, second.actual_end_point))- sin(ST_Azimuth(first.actual_start_point, first.actual_end_point)))<1
  and abs(cos(ST_Azimuth(second.actual_start_point, second.actual_end_point))-cos(ST_Azimuth(first.actual_start_point, first.actual_end_point)))<1
  and st_intersects(second.geo_polyline , first.geo_polygon);

下面是我的EXPLAIN结果：

   Nested Loop  (cost=0.15..45452.35 rows=128519 width=64)
  ->  Seq Scan on geo_akin found  (cost=0.00..2584.67 rows=3167 width=17077)
  ->  Index Scan using gist_index_geo_poly_line on geo_akin first  (cost=0.15..2.97 rows=1 width=4244)
        Index Cond: (geo_polyline && second.geo_polygon)
        Filter: ((id <> second.id) AND (abs((sin(st_azimuth(actual_start_point, actual_end_point)) - sin(st_azimuth(second.actual_start_point, second.actual_end_point)))) < '1'::double precision) AND (abs((cos(st_azimuth(actual_start_point, actual_end_point)) - cos(st_azimuth(second.actual_start_point, second.actual_end_point)))) < '1'::double precision) AND _st_intersects(geo_polyline, second.geo_polygon))

最佳答案

我能看到的是你的30万张桌子的自连接给了你9万个交叉口。我的建议是试着过滤连接到只有重叠的多边形，然后找到它们是否相交。
如果为边界多边形添加X1、Y1、X2、Y2并使用（ID、X1、Y1、X2、Y2）创建索引，则可以非常快速地找到重叠的多边形

SELECT ST_Intersection(b.geo_polyline, a.geo_polygon) AS intersection
FROM geo_akin a
JOIN geo_akin b
  ON a.X1 <= b.X2
 AND a.X2 >= b.X1
 AND a.Y1 <= b.Y2
 AND a.Y2 >= b.Y1
 AND a.id < b.id
WHERE abs(sin(ST_Azimuth(b.actual_start_point, b.actual_end_point))
        - sin(ST_Azimuth(a.actual_start_point, a.actual_end_point))) < 1
  AND abs(cos(ST_Azimuth(b.actual_start_point, b.actual_end_point))
        - cos(ST_Azimuth(a.actual_start_point, a.actual_end_point))) < 1
  AND st_intersects(b.geo_polyline, a.geo_polygon);

关于sql - 为什么我的Postgis ST_Intersects和基于ST_Azimuth的查询速度慢？，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57256421/