[GIS] How to draw a line between two places with via-points

polyline-creationpostgis

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:

enter image description 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!

UPDATE:

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.

enter image description here

UPDATE:

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)

Best Answer

Change subselect to suite your needs 

select  st_makeline(res.geom) 
FROM (SELECT geom from points group by group_id order by time_order)

Be sure that sub select returns data in right order

: edit

Does this one help ? 

SELECT id, geom from lines where line_id = 1  
union all 
select id, geom from stops where line_id = 1
where id in (
        select id, geom from 
    (SELECT MAX(id) as id from stops WHERE line_id = x  
    UNION ALL SELECT MIN(id) as id from stops WHERE line_id = x  
    UNION ALL select id from linepoints  WHERE line_id = x  
    ) as a group by id ) ORDER BY id

stops and lines table can't have same id's first unions stops and lines table and then make ordered list by id's from data which subselect returns

  1. subselect gets max id for line_id from table stops
  2. subselect gets min id for line_id from table stops
  3. gets all id's on line_id

(and i allready forgot did you have unique id's in stops and lines table )

Related Question