0

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: enter image description here

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; – whitney vogel Oct 10 '19 at 20:51
  • 2
    Please Edit the question to contain code, where it can be formatted for legibility. – Vince Oct 11 '19 at 1:43
  • ...and maybe add an example (picture?) of what you are after? – ThingumaBob Oct 11 '19 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. – Losbaltica Oct 11 '19 at 14:33
  • Would it not be easier to calculate their estimate speed? – alphabetasoup Nov 11 '19 at 4:20
0

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? – whitney vogel Oct 11 '19 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. – robin loche Oct 14 '19 at 7:36
  • For example: An Improved DBSCAN Algorithm to Detect Stops in Individual Trajectories (researchgate.net/publication/…) – robin loche Oct 14 '19 at 7:38
  • This is perfect, thank you! – whitney vogel Oct 15 '19 at 16:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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