4

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.

John's GPS track and all GPS tracks that might interact

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.

4
  • 1
    Mhhh, so far I would say you should be able to solve your problem with some where condition on datetime column when you create your buffer and when you retrieve your points ... should not be harder I think (or I misunderstand something) Sep 19, 2019 at 9:30
  • I added the SQL I used to get the picture. The problem is that the data returned is fetching all messages that fall within the buffered track and the whole month period. So when John is on the far left side, at the end of the track, I'm getting data for GPS's that were there when he was on the far right side. They were close in space but very far away in time..
    – RedM
    Sep 19, 2019 at 9:41
  • Ok, I would suggest to create several path , let's say one per day and only consider points around each geom at the given date time. Your approach is good but you need to split your path in time too. You then need to change your first query with a Group By maybe... Sep 19, 2019 at 9:46
  • Figuring out a sweet spot of time duration and then rerunning this query a bunch of times will work. I kind of want to figure out how to find points that are nearby in space and time in a single query since this will help me in the future when I want to do some long term looks at the data to find specific events: "Find all the points in the last year when John slowed down next to another person"
    – RedM
    Sep 19, 2019 at 10:03

1 Answer 1

2

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.

2
  • This looks proimsing, I've edited the SQL to make trajectories from all the gps points in the buffer: ST_MakeLine(ST_MakePoint(longitude, latitude, extract(epoch from event_time)) ORDER BY event_time) AS track_traj but ST_CLosestPointof Approach fails: NOTICE: Line does not have M dimension ERROR: Both input geometries must have a measure dimension
    – RedM
    Sep 19, 2019 at 11:26
  • Whoops, the correct function is ST_MakePoint(Lon, Lat, 0, epoch) rather than (Lon, Lat, Epoch)
    – RedM
    Sep 19, 2019 at 11:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.