PostGIS Clustering. Group by geographic point location proximity

Question

I have a table where i keep user's location history. The system gets user's current location each minute and writes it into "user_locations" table.

Later on; when showing the locations that the user has been; this is what it seems like when selecting all records from "user_locations":

Apparently the user has been too lazy all day but it ends up so that i have to handle the outcomes of his laziness -which he is unaware of i think-.

Okay. What i want to do is to show only one marker in a (let's say) 50 meter radius and the marker label to indicate: "The user has been here from 02:00 to 23:00". Of course the dates would be the min and max date values of the records in the cluster.

How can i achieve this?

Here is my table structure:

• Table Name: user_locations
• Columns: id, user_id, created_at (timestamp), location (Point, geography, srid:4326)

One important thing to note is to break the cluster once the user has gone out of the radius. I mean; let's say the user has been sitting in his office from 08:00 to 13:00 and went to lunch at 13:00 and came back to office at 14:00 and took out of the office at 18:00. This would mean 3 clusters. 2 on office and 1 on lunch restaurant. So the clustering would be based on location but a location outside of the given perimeter would break the clustering and even if later the location becomes in the same place, it would start another cluster.

I hope i was able to tell what is on my mind.

Regards.

Answer 1

You could use a spatial clustering algorithm I wrote to group together points for each user within a day within a maximum radius of meters. Then look at the breadth of times within each cluster and split apart clusters that have discontinuities in time.