Making Lines from Points20 Mar 2015
Somehow 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.