# Making Lines from Points

20 Mar 2015Somehow I’ve gotten through 10 years of SQL without ever learning this construction, which I found while proof-reading a colleague’s blog post and looked so unlikely that I had to test it before I believed it actually worked. Just goes to show, there’s always something new to learn.

Suppose you have a GPS location table:

**gps_id**: integer**geom**: geometry**gps_time**: timestamp**gps_track_id**: integer

You can get a correct set of lines from this collection of points with just this SQL:

Those of you who already knew about placing `ORDER BY`

within an aggregate function are going “duh”, and the rest of you are, like me, going “whaaaaaa?”

Prior to this, I would solve this problem by ordering all the groups in a CTE or sub-query first, and only then pass them to the aggregate make-line function. This, is, so, much, nicer.