I have prepared a GPS-tracking dataset, currently consisting of one table with stopping points and one table with the raw GPS-points. The first table contains a column for any stopping point showing the id's of the grouped points.
Now, I want to connect the stopping points with lines, not only direct connections (st_makeline(geom, geom)), but with via points from the raw point table. Therefore, I tried something like
st_makeline(array[stoppingpoint, viapoints_lying_between_these, next_stoppingpoint])
Unfortunately, this query connects all via-points with the stopping point with one line, as you see here:
The query looks as follows:
st_makeline(stops.the_geom, raw_points.the_geom, ARRAY[lead(stops.the_geom) OVER (ORDER BY stops.group_id)])AS lines, stops.group_id, row_number() OVER (ORDER BY stops.group_id) AS line_id from // semantic: where raw_points.id lies between maximum_id_from_first_stop and minimum_id_from_next_stop
When I manually put the via points and the two stopping points in one array within the st_makeline-function, it works in terms of drawing one line.
Thank you for any advice!
okay, i did not solve the problem yet. So, here is the table for better understanding: What i need are lines from each stopping point (marked by column group_type), which are centroids of grouped points, to the next stopping point VIA the intermediate points. In this case, one line would start at the point with group_id 38, end at group_id 42 and be connected via points 498, 499 and 500.
I finally found a solution. First i merged all the consecutive waypoints (id 38-42 in the lower picture) with a window function giving new group_nr's:
sum(group_flag) OVER (ORDER BY id) AS group_nr FROM ( SELECT CASE WHEN lag(group_type) OVER (ORDER BY id) = group_type THEN ELSE 1 END AS group_flag from table) t1
Then i make lines from each group of waypoint (multipoints) and add the previous and the next stopping-point-geometry to each line:
-- 1. previous stop: st_addpoint(st_linefrommultipoint(groups.the_geom), lag(groups.the_geom) OVER (ORDER BY groups.group_id), 0) -- 2. next stop: st_addpoint(t1.new_geom, lead(t1.the_geom) OVER (ORDER BY t1.group_id)