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":
Location results when the user has been too lazy all day
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.


  • 1
    Could you group records by the 'hour' and grab the top record using row_number()? Commented Sep 1, 2015 at 17:08
  • or just one buffer (polygon) that is colour coded on laziness? with legend
    – Mapperz
    Commented Sep 1, 2015 at 17:19
  • @mapBaker, I could. But grouping must also depend on "within 50 meter perimeter". How could i do both?
    – tozlu
    Commented Sep 2, 2015 at 5:28
  • @Mapperz, I'm sorry, i couldn't understand that.
    – tozlu
    Commented Sep 2, 2015 at 5:28
  • I think what @Mapperz is talking about relates to my suggestion - creating a 50m buffer on a point at the top of the hour, grabbing all the points that fall within that buffer, counting them up, and using that count as a value to drive a symbol 'of laziness' that hour... Commented Sep 2, 2015 at 14:05

1 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.

  • Thanks for the algorithm. This is not direct answer of the question but since no answer has been posted for a long time, this can be counted as an answer, i suppose.
    – tozlu
    Commented Mar 15, 2016 at 16:49
  • 1
    Hmmm... you could have a look at the previous version of the algorithm, which uses a less intelligent loop, and have the function loop over time and start a new cluster once a point exceeds 50m from the first one gis.stackexchange.com/a/115715/36886
    – raphael
    Commented Mar 15, 2016 at 16:53

Your Answer

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

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