How to get the highest repetition for a point type data in PgSQL
Need to calculate the most repeated coordinate (more precisely within a range of 100 meters)
Sample data column name coordinates
user_id coordinates 11ce4d96-4f64-421e-a56d-d436a804e522 (22.34,454.455) 11ce4d96-4f64-421e-a56d-d436a804e522 (12.2323,90.23233) 11ce4d96-4f64-421e-a56d-d436a804e522 (28.6986724,77.2972819) 11ce4d96-4f64-421e-a56d-d436a804e522 (28.6986176,77.2972501) 11ce4d96-4f64-421e-a56d-d436a804e522 (28.6987351,77.2973343) 11ce4d96-4f64-421e-a56d-d436a804e522 (28.6986208,77.2972428) be34f634-4ab3-4407-89ac-8f79e2d62232 (28.6987699,77.2972538) be34f634-4ab3-4407-89ac-8f79e2d62232 (28.6987699,77.2972538) be34f634-4ab3-4407-89ac-8f79e2d62232 (28.6987699,77.2972538) be34f634-4ab3-4407-89ac-8f79e2d62232 (28.6987699,77.2972538) be34f634-4ab3-4407-89ac-8f79e2d62232 (28.6987259,77.2972478) be34f634-4ab3-4407-89ac-8f79e2d62232 (28.6986413,77.2972698)
Advertisement
Answer
Is this what you want?
select distinct on (user) user, point(coordinates[0], coordinates[1]), count(*) from t group by user, coordinates[0], coordinates[1] order by user, count(*) desc;
It seems to be what you are describing.