3

I am using ST_ClusterKMeans in PostGIS to perform a spatial clustering.

CREATE TABLE kmeans10 AS
WITH s AS
(SELECT p.*, ST_ClusterKMeans(p.geom, 10) OVER() cid
 FROM p)
SELECT ST_UNION(s.geom) OVER (partition by s.cid ORDER BY s.cid) geom, s.cid, 
FROM s

The initial result looks like this:

enter image description here

Based on the initial result, I'd like to divide the cluster with the maximum number of points into another 5 sub-clusters.

WITH s AS
(SELECT * FROM kmeans10
WHERE cid in
(SELECT cid
FROM kmeans10
GROUP BY cid
ORDER BY COUNT(cid) DESC
LIMIT 1))

SELECT s.geom, ST_ClusterKMeans(s.geom, 5) OVER () s_cid
FROM s
ORDER BY s_cid DESC

However, I do not get the 5 sub-clusters as desired but this notice:

kmeans_init: there are at least xxx duplicate inputs, number of output clusters may be less than you requested

What's wrong with my query? Is there any better solution to nest the spatial clustering action?

UPDATE ON 2021-05-10

I refine the initial kmeans10 query as per the advice from @geozelot, by using GROUP BY instead of ST_Union. Then the consecutive query works properly.

CREATE TABLE kmeans10 AS
WITH s AS
(SELECT p.*, ST_ClusterKMeans(p.geom, 10) OVER() cid
 FROM p)
SELECT s.geom, s.cid 
FROM s
GROUP BY s.cid, s.geom

1 Answer 1

2

You ST_Union the initial geometries by cid, so the consecutive run can only consider a single (Multi)geometry for clustering [*] - which it naturally fails at.

Insert the atomic rows into kmeans10 instead, then run the consecutive clustering (the second query should work as expected).


[*] Note that the ST_Union window function returns the same union for every row in the sub-query matching the partition key, meaning that the consecutive run actually clusters the same unioned geometry that many times! Use a GROUP BY for that scenario instead.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.