Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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!


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


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
          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)
share|improve this question
add comment

1 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 )

share|improve this answer
thanks a lot, but i do not understand your answer. my problem is, that the raw points (via points) are not grouped and that they lie in an other table as the start and arrival points. The table with the stopping points give me the lowest and the highest id of the points, that have to build the line-segments between the stoppng points... –  Berlin_J Feb 12 '13 at 14:55
So you want to have line from that upper left corner point thru all point to next stopping point on right ? –  simplexio Feb 13 '13 at 8:22
right, but the two fat points are stored in another table than the via-points. –  Berlin_J Feb 14 '13 at 9:44
i assume that you insert data in order fro gps. -> id (serial) can be used to order data so –  simplexio Feb 14 '13 at 11:26
add comment

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.