主页 / dba / 问题 / 221318
Accepted
user3052682
Asked: 2018-10-30 22:40:21 +0800 CST

查找在交叉路口未分割的路段。（查询优化）

  • 772

我有一个查询，我试图在其中找到相交但未在交叉路口分开的路段，我还在 geom 列上创建了要点索引。这是 postgerSQL 脚本

SELECT a.id touched, b.id touching,  ST_Intersection(a.geom, b.geom)
FROM   pak_roads a, pak_roads b
WHERE ST_Crosses(a.geom, b.geom) and a.id < b.id and (a.MODE IS NULL
and b.MODE IS NULL )
and (a.grade_t IS NULL and a.grade_f IS NULL)
and (b.grade_t IS NULL and b.grade_f IS NULL) OR
-- "vertical" T bar, touching
(
 -- The "vertical" start node touches, but not on either of the "horizonal" nodes
 ST_Equals(ST_Intersection(a.geom, b.geom), ST_StartPoint(b.geom))
 AND NOT ST_Equals(ST_StartPoint(a.geom), ST_StartPoint(b.geom))
 AND NOT ST_Equals(ST_EndPoint(a.geom), ST_StartPoint(b.geom))
 ) OR (
 -- The "vertical" end node touches, but not on either of the "horizonal" nodes
 ST_Equals(ST_Intersection(a.geom, b.geom), ST_EndPoint(b.geom))
 AND NOT ST_Equals(ST_StartPoint(a.geom), ST_EndPoint(b.geom))
 AND NOT ST_Equals(ST_EndPoint(a.geom), ST_EndPoint(b.geom))
 )

逻辑工作正常，但完成此任务需要很多时间，有人可以告诉我如何优化此查询。

postgresql performance

1 个回答

  1. Best Answer

    我刚刚在第二个和第三个查询的开头添加了 st_intersects(a.geom,b.geom)，因为 st_intersects 速度很快，所以它只计算 st_intersection，其中 st_intersects 返回“True”，因此查询性能得到提高。下面是修改后的查询。

    SELECT a.id touched, b.id touching,  ST_Intersection(a.geom, b.geom)
FROM   pak_roads a, pak_roads b
WHERE ST_Crosses(a.geom, b.geom) and a.id < b.id and (a.MODE IS NULL
and b.MODE IS NULL )
and (a.grade_t IS NULL and a.grade_f IS NULL)
and (b.grade_t IS NULL and b.grade_f IS NULL) OR
-- "vertical" T bar, touching
(
-- The "vertical" start node touches, but not on either of the "horizonal" --nodes
st_intersects(a.geom,b.geom) and ST_Equals(ST_Intersection(a.geom, b.geom), 
ST_StartPoint(b.geom))
AND NOT ST_Equals(ST_StartPoint(a.geom), ST_StartPoint(b.geom))
AND NOT ST_Equals(ST_EndPoint(a.geom), ST_StartPoint(b.geom))
) OR (
-- The "vertical" end node touches, but not on either of the "horizonal" 
--nodes
st_intersects(a.geom,b.geom) and ST_Equals(ST_Intersection(a.geom, b.geom), ST_EndPoint(b.geom))
AND NOT ST_Equals(ST_StartPoint(a.geom), ST_EndPoint(b.geom))
AND NOT ST_Equals(ST_EndPoint(a.geom), ST_EndPoint(b.geom))
)

    但我认为还有一些改进的余地。

    • 0

相关问题