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: http://postgis.net/docs/ST_ClusterKMeans.html — Randomize, Mar 05 '18 at 16:54

score 3 · Accepted Answer · answered Mar 05 '18 at 18:02

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

How to add attributes to clustered points with Postgis?

1 Answers1

Linked