sql - 在另一个查询的 WHERE 子句中使用一个 PostGIS SQL 查询的结果
问题描述
我正在构建一个地图应用程序,它将 GPS 轨迹作为坐标列表存储在名为base_points
.
我有一个使用传单来显示这些曲目的 Web 应用程序。当我移动地图时,我使用当前视图的边界对我的后端进行 AJAX 调用。
我想返回地图范围内的一组简化点。
起初,我使用了这个运行良好的查询:
SELECT ST_AsGeoJSON(
ST_Simplify(
ST_MakeLine(ARRAY(SELECT geom
FROM base_point
WHERE track_id = %s
AND geom @
ST_MakeEnvelope(
%s, %s, %s, %s, %s)
ORDER BY time)),
%s
)
);
问题是,在轨道存在并进入视口的某些缩放级别上,我得到了轨道的一个子集,因为某些部分被geom @ ST_MakeEnvelop
. 我想在视口中找到最小和最大跟踪点,并使用它们来检索所有点,然后使用ST_Simplify(ST_MakeLine)
.
我正在尝试以下 SQL 表达式,但它不起作用。
WITH bounds as (SELECT MIN(id) as min, MAX(id) as max
FROM base_point
WHERE track_id = %s
AND geom @ ST_MakeEnvelope(%s, %s, %s, %s, %s))
SELECT ST_AsGeoJSON(ST_Simplify(ST_MakeLine(ARRAY(
SELECT geom
FROM base_point, bounds
WHERE track_id = %s
AND id < bounds.max
AND id > bounds.min ORDER BY time
)),
%s));
我之前已经编辑过这个,现在查询可以工作了,但是速度很慢。
这是一个解释/分析输出:
Result (cost=8.95..34.46 rows=1 width=32) (actual time=59806.894..59806.896 rows=1 loops=1)
InitPlan 1 (returns $2)
-> Sort (cost=8.95..8.95 rows=1 width=40) (actual time=59803.755..59804.242 rows=14001 loops=1)
" Sort Key: base_point.""time"""
Sort Method: quicksort Memory: 1478kB
-> Nested Loop (cost=4.88..8.94 rows=1 width=40) (actual time=13.074..59799.753 rows=14001 loops=1)
Join Filter: ((base_point.id < (max(base_point_1.id))) AND (base_point.id > (min(base_point_1.id))))
Rows Removed by Join Filter: 523
-> Index Scan using base_point_track_id_b527d95c on base_point (cost=0.43..4.45 rows=1 width=44) (actual time=0.031..2.440 rows=14524 loops=1)
Index Cond: (track_id = 40)
-> Aggregate (cost=4.46..4.46 rows=1 width=8) (actual time=4.117..4.117 rows=1 loops=14524)
-> Index Scan using base_point_track_id_b527d95c on base_point base_point_1 (cost=0.43..4.45 rows=1 width=4) (actual time=0.005..3.477 rows=14003 loops=14524)
Index Cond: (track_id = 40)
Filter: (geom @ '0103000020E61000000100000005000000C3F5285C8FF256C08FC2F5285C4F4340C3F5285C8FF256C07B14AE47E15A4440A4703D0AD73355C07B14AE47E15A4440A4703D0AD73355C08FC2F5285C4F4340C3F5285C8FF256C08FC2F5285C4F4340'::geometry)
Rows Removed by Filter: 521
Planning Time: 0.294 ms
Execution Time: 59807.087 ms
如果我将查询分离出来并对 id 进行硬编码以进行比较,则查询将在 15 毫秒内运行。
解决方案
为您的 from 子句添加界限。选择 geom From base_point, bounds Where.....
推荐阅读
- c# - 无法访问受保护的成员控件。CreateHandle()
- java - 删除第一次出现的双向链表 Java
- c# - Net core 2 地图产品在结果 SQL 中具有多个属性
- machine-learning - 如何在 Keras 中的模型之前获得密集层的权重(内核)?
- c# - Windows 窗体 DataGridView - 自定义单元格以根据出生日期查看年龄
- prestashop - 在子域中实现 Prestashop 多站点
- scala - 如何简洁地检查元组是否没有重叠成员
- haskell - Haskell 中的异构引用相等
- reactjs - 在 React 中加载没有云的 TinyMCE
- events - Cloud Foundry - 如何订阅/观看 firehose 事件?