1

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.

Dataset Requirements

  • 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.

Usage Requirements

  • 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.

Thoughts

  • 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_id values 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.

4
  • 1
    You probably need both, but this is more an issue for a week-long professional consultation, with another two weeks to develop and present a report, not an opinion request on the Internet. – Vince Apr 24 at 14:00
  • 3
    The tracks alternative feels very theoretical and ineffective, especially because XYZM is not enough for you and you should create your own way to storage multidimensional track points. Points with track_id and related table for track attributes is simple and you can certainly find examples because it is the way how the widely used GPX exchange format is designed en.wikipedia.org/wiki/GPS_Exchange_Format. You would only need tracks with geometry for visualizing the tracks as linestrings. Try to avoid that and build lines on-the-fly from track points when needed. – user30184 Apr 24 at 14:15
  • @Vince You are right! That's why, as soon as I have made some progress, I will come back to share my experience. – Vector Zita Apr 25 at 10:26
  • 1
    Just so that I can understand how to be even more specific, what is the key difference between this question or this question, and the one that I have posted here, which renders my question opinion-based but the other two not opinion-based? Would this question be a better fit for, e.g., the dba stackexchange? – Vector Zita Apr 26 at 22:33