What is the best way to store list of GPS points in PostgreSQL 9.6 with postgis extension?

Question

PostgreSQL

We implement GPS tracking
We are thinking about how to store routes - a set of points We
do not plan to search for points, we only need to display them and build a curve at a certain moment
We disagreed with a colleague:
1. I suggest storing each point as a separate record
2. He suggests storing points in an array ( or in json format) on one line. Those. route - one line in the database with all
points
I don’t have any arguments in defense of my version at all, except for the principles of normalization =)
Tell me, please, which option would you choose and why

Answer 1

You mentioned postgis, so you need to use its geom format via st_makeline(). Well, or geojson in postgres jsonb fields.
These are the best options. The second is preferable for quick feedback to clients, and the first for filtering or processing in postgis. Since a true geojson can contain several geometries and you need to write a custom function for converting to postgis geometry, I would store and process it in postgis, and then convert it to geojson or another desired format afterward
. And the points themselves without a track do not carry any useful information, after all.

Answer 2

Your approach is much better, at least in that at some point you will probably want to intersect along the tracks, or select points inside the polygon. And you can deal with the performance problem by partitioning tables, for example, by the start time of the track. Erase old partitions and transfer them to the archive. Your version is at least much more flexible.

Answer 3

IMHO wif recursive coupled with a bunch of id, key, parent_id, route_id are much more convenient to maintain ...

Answer 4

Necroposthitis.
Your question googles pretty well, so I'll add my own answer.
The key is that you are tracking. The following entities are important for the subject area:

• Session - a relatively continuous (without large gaps) section of the movement trajectory: session boundaries in time, boundaries in space, session duration, session mileage, idle time, time in motion, average speed, maximum speed, median speed.
  
• Track point - message from GPS tracker:
  
  • time,
    
  • coordinates,
    
  • speed,
    
  • level of GPS and GSM signals,
    
  • fuel level,
    
  • door sensors,
    
  • engine sensors,
    
  • current GPS accuracy,
    
  • etc.
    
  
  
• Route - information about the trajectory without detailing the speed profile. Length, mat-expectation of travel time for different vehicles...
  
• Waypoint.
  

Obviously, in view of all this, you will not be able to store track points in postgis polylines.
However, you can cache their aggregated geometry in this format for speed and ease of use. But this very caching makes sense only if you really need this optimization (tracks are given slowly, sessions are slowly and difficult to join ...). Why do that. what could not be done?
And at the expense of the size of the database, I would not worry at all if I were you and your colleague. Now storages are cheap and scalable, it’s never too late to clean up old unnecessary data. Your data is perfectly indexed and filtered by time, you can merge old data dumps into cold storage, suddenly it’s time to make infographics or a big analysis.
I would be sorry to forego detailed information on track points in favor of some dubious premature optimization.