I am extremely new to PostGIS, and I am interested in visualizing a large GPS dataset. The dataset is updated daily and contains millions of points in the format (EPSG:4326):
Device ID Timestamp Latitude Longitude
Mapbox Vector Tiles seem like a good approach for visualizing GPS tracks on a map, but I do not have good intuition as to the most efficient design. Currently, I have the following approach:
- Use St_MakeLine to convert the GPS coordinates for each device to a line geometry, ordering by the timestamp
- Simplify the route using ST_Simplify to reduce the number of points in each line
- Transform each line into a Mapbox Vector tile using ST_AsMVT and ST_AsMVTGeom
This seems to work reasonably well with a small dataset, but does not scale well as the number of points for each device grows (the St_MakeLine and ST_Simplify calls become more and more expensive).
How is this typically handled? Should the dataset be further broken down into tracks, resulting in smaller line geometries? Should it be stored as lines instead of points? According to this question, adding new data is slow, but that may be acceptable with a large boost to the visualization performance.