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

Question

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.

Answer 1

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.