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.