I am currently working my way through PostgreSQL with PostGIS, trying to figure out the optimal way to handle the storage and manipulation of a very large dataset. I have read this and this question but I am still not very certain as to how to optimally approach my scenario.
- My dataset will involve tracks, updating in near real-time. Each track consists of a line with approximately one point per second (I do need to keep all points).
- Any single track may span a few hours or more, so, typically, tracks could easily reach up to ~20000-30000 points each.
- For each point, I will need additional attributes per position. At the very least, I will need to record altitudes and timestamps, but I may also need to store the velocity (it may be provided by the transmitting system) and another few things may come up as well (such as the signaling of characteristic events along the track).
- I may need to record up to 10000-20000 of tracks per day.
- Inserts to the database may generally have to take place as often as every few minutes.
As a back-of-the-envelope calculation, this translates to 600 million (30000 points per track * 20000 tracks, as a worst case scenario) points daily, which will have to be inserted into the database.
- Most of the time, the queries to the database will be spatial, temporal, and based on some other track attributes.
- A typical query would involve a day's worth, so approximately 600 million points (assembled in lines, naturally).
- A worst case query could involve a year's worth.
- I can imagine the simplest case of a POINTS table with
[Track_ID, X, Y, Altitude, Timestamp, Event]. As per my previous requirements, we are talking billions to trillions of records going down that route (as half a billion points would probably be reached within a single day).
- Alternatively, I can use a TRACKS table, with a geometry field (linestring). I could store time in the M coordinate but I have generally seen discouraging suggestions regarding any usage of the M coordinate. Additionally, I don't really only have four attributes
(x, y, z, time), but a minimum of 5
(x, y, z, time,event)and may also need others, such as velocity. I have thought about, maybe, using two geometry fields, one to store linestrings with points as
(x, y, z)coordinates and another one to store other numeric values as, again, linestrings with points, but in the form of
(timestamp, velocity, event_id)for coordinates. I would somehow encode
event_idvalues to external textual descriptions, as I really cannot store text values in geometry fields.
Should I prefer a POINTS table (and try to optimize that case) or a TRACKS table with geometry fields? How would I accommodate my additional attributes in the TRACKS table scenario?
Due to limited experience, I am relatively less concerned as to what I should do, I am far more concerned as to what I should avoid doing.