How to add attributes to clustered points with Postgis?

Question

I already have found a similar answer here:

Spatial clustering with PostGIS including attributes

but grouping after by cluster.

I cluster my data in this way:

SELECT ST_AsText(unnest(ST_ClusterWithin(geom, 2))) from ( values (ST_MakePoint(10,9),'mark',20), (ST_MakePoint(10,9),'mark',22), (ST_MakePoint(10,10),'steve',21), (ST_MakePoint(10,13),'john',23) ) T(geom,name,age);

that correctly returns:

 st_astext ---------------------------------------------------------- GEOMETRYCOLLECTION(POINT(10 9),POINT(10 9),POINT(10 10)) GEOMETRYCOLLECTION(POINT(10 13))

But what I really need is that once the data is clustered by "location" it needs to pick the first in alphabetical order desc, and if the same name, by the age desc. A sort of:

select * from ( select *, row_number () over (order by name desc, age desc) from (pre-clustered-data) T ) K where row_number = 1;

How can I do that with Postgis?

this actually is a windowing function: postgis.net/docs/ST_ClusterKMeans.html — Randomize
– Randomize, Commented Mar 5, 2018 at 16:54

dbaston · Accepted Answer · 2018-03-05 18:02:11Z

If you have PostGIS 2.3 or later, I can't think of a good reason to use ST_ClusterWithin. You can perform an equivalent clustering using ST_ClusterDBSCAN but without losing your attributes:

SELECT name, age, cluster_id, geom FROM ( SELECT name, age, geom, ST_ClusterDBSCAN(geom, eps := 2) OVER() AS cluster_id, FROM my_data ) sq GROUP BY cluster_id

You can then feed the results of the above into whatever process/logic is selecting a representative item from each cluster. (If you're looking to do it in SQL, try computing the rank of each name, partitioned by cluster_id, and then each age, partitioned by cluster_id and name.)

Stack Exchange Network

How to add attributes to clustered points with Postgis?

1 Answer 1

Linked

Hot Network Questions

How to add attributes to clustered points with Postgis?

1 Answer 1

Linked

Related

Hot Network Questions