I am building a mapping application that stores GPS tracks as lists of coordinates in a table called base_points
.
I have a web application that uses leaflet to display these tracks. When I move the map, I make an AJAX call to my backend, with the bounds of the current view.
I'd like to return a simplified set of points within the bounds of the map.
At first, I used this query which works pretty well:
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
)
);
The problem is that on certain zoom levels where the track exists and enters the viewport, I get a subset of the track as some parts are cut out by the geom @ ST_MakeEnvelop
. I'd like to find the min and max track point within the viewport, and use those to retrieve all the points, and then using ST_Simplify(ST_MakeLine)
.
I am trying the following SQL expression, but it doesn't work.
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));
I've edited this from earlier, and the query now works, but is very slow.
Here's an explain/analyze output:
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
If I separate out the queries and hard code the ids for comparison, the query runs in 15ms.
I figured it out. Doing a join is much faster.
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
INNER JOIN bounds ON
id < bounds.max
AND id > bounds.min ORDER BY time
)),
%s));
If someone can explain why, I'd greatly appreciate it!