3

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.

Sample:

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?

  • oh I like this one...it's a pity I have no time right now, but if it's still unsolved, I'll come back later. – ThingumaBob May 29 '18 at 13:39
  • @ThingumaBob Did you have any further thoughts about this one? Any help would be appreciated! – Dave C Jun 1 '18 at 10:17
  • ah sry, yes I have a few ideas, but can't really get to test them, especially concerning speed (no data, no time really). priority is to identify the noise signals to make things easier, however 4M points are tough for anything temporary/in memory, and possibly even for crazy complex window function queries; e.g. you could update the table, add a column and assign a value to those noise geometries, either in a CASE condition using lag and lead to check for your parameters, or maybe with ST_ClusterDBSCAN to efficiently find those noise clusters. – ThingumaBob Jun 1 '18 at 11:49
  • having those as clusters, you could use their collected geometries' centroids as start/end points to create the journeys. this would probably be a lot easier within PL/pgSQL, but should be doable in plain SQL. would it be possible to upload sample data somewhere, e.g. for two Vehicle_IDs maybe (you could e.g. translate their geometries to anonymize them)? – ThingumaBob Jun 1 '18 at 11:58
  • Thanks! I can probably gather some sample data next week and upload it somewhere. Those 4 million points are reduced in noise already from 8M based on snapping the GPS points to the road network when the point falls within 10 metres of the road (although from talking to others, this seems to be a little tight, others have used 50/100m). – Dave C Jun 1 '18 at 14:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.