1

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.

Regards.

Improve this question
  • 1
    Could you group records by the 'hour' and grab the top record using row_number()? – DPSSpatial Sep 1 '15 at 17:08
  • or just one buffer (polygon) that is colour coded on laziness? with legend – Mapperz♦ Sep 1 '15 at 17:19
  • @mapBaker, I could. But grouping must also depend on "within 50 meter perimeter". How could i do both? – 1.44mb Sep 2 '15 at 5:28
  • @Mapperz, I'm sorry, i couldn't understand that. – 1.44mb Sep 2 '15 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... – DPSSpatial Sep 2 '15 at 14:05
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.

Improve this answer
  • 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. – 1.44mb Mar 15 '16 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 Mar 15 '16 at 16:53

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.