Visualizing large GPS dataset tracks [closed]

Question

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:

1. Use St_MakeLine to convert the GPS coordinates for each device to a line geometry, ordering by the timestamp
2. Simplify the route using ST_Simplify to reduce the number of points in each line
3. 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.

Answer 1

I've got the exact same kind of data. Millions of points per day coming and visualisation has always been an issue. Some tips that have helped me:

• Build spatial indexes on your data link
• Build indexes on time (and any other relevant variable, GPS_ID or what not) link
• Partition your tables by month/week. Generally you're only going to be querying for a limited amount of time so having partitioned tables helps out. This is easier if you're using postgres 10. If you're using inherited tables to do partitioning then indexes don't get inherited (in Postgres 9.x). link
• Don't bother trying to visualise all the tracks at once, it's just a mess. You can hook up QGIS directly to your postgis DB and view subsets of the data in that but your results may vary depending on how big your data gets. QGIS
• Visualise aggregated data. I built a 0.1 x 0.1 degree grid and laid the tracks over that (for global data, you might want to go smaller). I counted the amount of tracks that intersected each grid point and displayed that. It's pretty expensive but you end up with a heatmap. You can also do some other weird stuff like showing the average speed, bearing etc for each lat/lon bin. Or else you can use the QGIS heatmap tool but that's not great.

In general this is a pretty tough question. It really depends on what you want to do with the data. There are some datasets out there that are comparible so that could be of some help.

Building indexes will help with ST_Makeline. You could also save the results into a materialised view so that you don't have to keep building them each time. If you're partitioning your tables then having a precalculated view for each table makes sense.

Making a materialised view for a week's worth of tracks:

CREATE MATERIALIZED VIEW tracks_7days
SELECT 
 st_makeline(data_table.point) AS track, 
 data_table.gps_id 
FROM data_table 
WHERE 
 data_table.event_time >= (now()::timestamp without time zone - '7 days'::interval) 
AND 
 data_table.event_time <= now()::timestamp without time zone 
GROUP BY data_table.gps_id