1

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?

2

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

| improve this answer | |

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.