# How to add attributes to clustered points with Postgis?

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?

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`.)
{"ft_lang_label":"__label__en","ft_lang_prob":0.6799618,"math_prob":0.8774962,"size":896,"snap":"2020-45-2020-50","text_gpt3_token_len":245,"char_repetition_ratio":0.17376682,"word_repetition_ratio":0.0,"special_character_ratio":0.35044643,"punctuation_ratio":0.19186047,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.96763074,"pos_list":[0],"im_url_duplicate_count":[null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2020-11-27T06:07:01Z\",\"WARC-Record-ID\":\"<urn:uuid:ce4c9711-e2f7-4314-9ac6-cba512e039e6>\",\"Content-Length\":\"149323\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:16134f6a-e93d-4fbc-9e3f-4b88c6d42f75>\",\"WARC-Concurrent-To\":\"<urn:uuid:3c7692a5-46e9-458a-8406-204751c9aeba>\",\"WARC-IP-Address\":\"151.101.129.69\",\"WARC-Target-URI\":\"https://gis.stackexchange.com/questions/273604/how-to-add-attributes-to-clustered-points-with-postgis?noredirect=1\",\"WARC-Payload-Digest\":\"sha1:JGEJ2GUI3DUKDYUGXKZ3E277NVALQD4A\",\"WARC-Block-Digest\":\"sha1:MFPW5CHH57DIFSUIRNMGBRFL5N4LLTMM\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2020/CC-MAIN-2020-50/CC-MAIN-2020-50_segments_1606141189141.23_warc_CC-MAIN-20201127044624-20201127074624-00154.warc.gz\"}"}