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.

Could you group records by the 'hour' and grab the top record using row_number()? — DPSSpatial_BoycottingGISSE, Sep 01 '15 at 17:08
or just one buffer (polygon) that is colour coded on laziness? with legend — Mapperz, Sep 01 '15 at 17:19
@mapBaker, I could. But grouping must also depend on "within 50 meter perimeter". How could i do both? — tozlu, Sep 02 '15 at 05: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_BoycottingGISSE, Sep 02 '15 at 14:05
Thanks, could you provide some scratch queries as answer for me to build up on? I guess there will be multiple queries. — tozlu, Sep 02 '15 at 14:14

score 1 · Accepted Answer · edited Apr 13 '17 at 12:33

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.

edited Apr 13 '17 at 12:33

Community

1

answered Mar 15 '16 at 16:42

raphael

3,407
23
61

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 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 http://gis.stackexchange.com/a/115715/36886 – raphael Mar 15 '16 at 16:53

PostGIS Clustering. Group by geographic point location proximity

1 Answers1