Tally of sequences of points within point-specific buffer, rolling over timestamps, in PostGIS?

In a given string of points, I've been able to designate buffers for each point in the string (i.e. a string of points are relocations over time). However, I'd like to be able to then summarize how many sequences of points of more than, say, X points, fall within a single point-specific buffer. The idea being that this would quantify how many sequences of more than X points within a string of points are within one buffered area.

Here is some code thus far: SELECT id, ST_Buffer(geom_utm, 20) FROM schema.my_table GROUP BY id ORDER BY id;

Note: each point has an individual id to it, and its geom is in UTM (hence the 20 for 20 meter buffer).

Here's an image of a string of 20m buffers around each point in the string:

This image shows buffers around points which are on a map, ordered via timestamp. As you can see the buffers are heavily overlapping in some areas (see the dark blue spots). I'd like to count how many points occur in those areas where there is almost total overlap (i.e. dark blue spots). Those dark blue spots represent times where the individual (i.e. thing triggering GPS fixes) is not moving as much, because over time the individual remains within a point's buffer for a certain time window. I'd like to count how many times the individual stays within a given point's buffer for more than, say, X timestamps after the original point's timestamp. Or another way, how many times do we see point buffers overlapping entirely for at least X timestamps in a row.

Ideally, every point would have a code which states whether it is within a "resting" period or not, as in if you find some X points after an original point which are all within a buffer, all of those points would get a "1" because the individual was still from point A for X more points. Points where there is no resting, i.e. no buffer overlaps for more than X points, would get a "0", which means they are not resting at that timestamp.

• code thus far: SELECT id, ST_Buffer(geom_utm, 20) FROM schema.my_table GROUP BY id ORDER BY id; Oct 10, 2019 at 20:51
• Please Edit the question to contain code, where it can be formatted for legibility. Oct 11, 2019 at 1:43
• ...and maybe add an example (picture?) of what you are after? Oct 11, 2019 at 11:49
• I am not really getting what you really need. Please provide more information on what you already tried, what is your starting point and what you want to achieve with sample tables or/and code. Oct 11, 2019 at 14:33
• Would it not be easier to calculate their estimate speed? Nov 11, 2019 at 4:20

I think it should looks like this (buffer of 200):

``````SELECT
pt1.id_pt,
count(pt2.id) as nb_pt_in_buffer
FROM
points pt1
LEFT JOIN
points pt2
ON
ST_Intersects(pt2.geom, ST_Buffer(pt1.geom, 200))
GROUP BY pt1.id_pt
``````

But your problem looks like a clustering problem, so maybe you should take a look to the ST_ClusterDBScan function ? It depends of what you really want.

• Thank you for your reply! I think you're right that the ST_ClusterDBScan might be what I'm looking for- it looks like this function gives an identification value to points which fall within a certain area? How could I use this scan function to go through each point in the sequence, and "scan" for which points following it are within a 20meter cluster of that point? Oct 11, 2019 at 19:05
• You can try to run it with 20 as eps and your X as minpoints. You will have an id for your points, each new stop will have a different id (one by stop, the same id for all the points in this stop) and the points that are not in a stop will not have any ID. That just an id, you can also couple it with the distance between 2 consecutive GPS points or others things like that, there is actually a lot of science paper on stop detection in GPS track maybe you can take a look at it. Oct 14, 2019 at 7:36
• For example: An Improved DBSCAN Algorithm to Detect Stops in Individual Trajectories (researchgate.net/publication/…) Oct 14, 2019 at 7:38
• This is perfect, thank you! Oct 15, 2019 at 16:35