I have roughly 4,000,000 points of GPS data stored in a PostgreSQL/PostGIS database. The points are from multiple vehicles over the last 2 years.
Msgid Time Speed Vehicle_ID Geom 1 2017-01-01 07:48:00 48 V1 (valid geom point) 2 2017-01-01 07:49:00 42 V2 “” 3 2017-01-01 07:50:00 2 V1 “” 4 2017-01-01 07:49:00 15 V2 “” 5 2017-01-02 08:49:00 0 V3 “” 6 2017-01-02 08:50:00 23 V3 “”
I need to separate the points into journeys and create a linestring between them. My first thoughts are to try and base this on time and distance. Something along the lines of partitioning it by the Vehicle_ID, ordering it by time and ranking it. This is to account for the vehicle sending its location approximately every 30 seconds, even when it returns to its base location. If it doesn’t have signal at a point in time, it will just skip sending it, so throughout the data there is gaps of 1-2 minutes. Also when it is turned off at base location it stops sending a signal. Distance needs to be factored in as the vehicles are left on for large amounts of time, so can be pinging within a 20 m radius and I don’t want the linestring to continue at this point. Ideally the query would only create a new journey when it doesn’t send a signal for 3-5+ minutes and doesn’t move more than 20 metres. I’m unsure of the parameters until I can view some results.
Is there a way of putting the distance and time filters within a rank function?
Or would it be wise to try and calculate a speed and base it on that?
And radial distance?