Find nearby points in space and time

Question

I have a table that has an ID, point geometry and time. It's basically GPS coords and some other data. I want to be able to select all rows that interact (come close in space and time) to my target ID.

For example I get my target track, John's points, with a simple query and then I can create a line from those points, buffer it and fetch all other rows that fall within that buffered track.

In the image below I have all of John's points for a month and all points from other ID's for the same month that fall within 10 km of his track. This works fine if I limit to a day or two.

The problem I'm experiencing is that as the time limit increases I'm getting more and more bad matches; the points of John's track that happen on the 15th are surrounded by tracks happening on the 1st or 30th.

So how should I be doing this? Create CTE of John's points and then some kind of moving window over that to only fetch points that are within 1 km and 1 hour of John's point X?

I'm not sure what tools to use...

SQL I use to get image (although I colour John's points differently):

WITH target_track AS 
    (SELECT DISTINCT ON (name) 
  name, 
  ST_MakeLine(gps_point) AS track, 
  ST_Buffer(ST_MakeLine(POSITION), 0.1) AS buff_track 
     FROM gps_history
    WHERE 
    name = 'john'
    AND
     event_time BETWEEN '2019-01-01' and '2019-01-30'
    GROUP BY name)

SELECT gps_history.* FROM
gps_history, target_track
WHERE
ST_Within(gps_point, target_track.buff_track)
AND
event_time BETWEEN '2019-01-01' and '2019-01-30'

I use group by and distinct to get a single row for the target_track CTE and then use that to find all the points that are in that time/space region.

Answer 1

If you work with timeseries, Postgis now have a native way to handle these: trajectory (it's based on LINESTRINGM, you can see for example in the link or in the first function you should use: ST_IsValidTrajectory).

It does not have a lot of function (in my opinion), but in my understanding it can answer your problem. Once you have your lines transformed into trajectories, you can use for example ST_ClosestPointOfApproach to get the closest point between 2 trajectories (both in time and space), and see if it respects your requirement.

You should probably use ST_CPAWithin before to filter most of your trajectories that you don't want, but unfortunately if you can pass a distance as a parameter, you cannot pass a max time difference that you want, that's why you need to use ST_ClosestPointOfApproach after.