4

I am working on adding 2000 random points to each of 2 polygons and then clustering the points in those polygons into 4 clusters for each polygon. When I apply ST_ClusterKMeans() in PostGIS, it divides the entirety of points (2000 points in each of 2 polygons) into 4 clusters spread across 2 polygons (Figure 1). However, I would expect there to be 2000 points divided into 4 clusters for each polygon.

How can I apply ST_ClusterKMeans() to each polygon so that each polygon has 4 clusters of points?


CREATE TABLE sample_pnts AS
  SELECT (ST_Dump(ST_GeneratePoints(geom, 2000))).geom AS geom
  FROM sample_polys;

CREATE TABLE sample_pnts_clustered AS
  SELECT geom, ST_ClusterKMeans(geom, 4) over () AS cluster
  FROM sample_pnts;

Figure 1

enter image description here

1
  • 1
    It's a window function; you should be able to PARTITION BY <poly_id>. Note, though, that it generates a cluster id sequence starting from 0 per <poly_id>, so you'll have to identify a cluster by both ids.
    – geozelot
    Oct 14, 2020 at 6:21

1 Answer 1

5

Since ST_ClusterKMeans has been re-implemented as Window Function, it is trivial to PARTITION BY an arbitrary category definition, and have the function work on each window individually.

ST_ClusterKMeans will use a 0 based sequence for each window it operates in, meaning that the result will have cluster ids 0..n for each (hierarchy of) partition key(s) used in the PARTITION BY expression; in order to distinguish clusters, it is necessary to reference them by (all) the partition key(s) plus cluster id.

To get cluster for each set of points in a polygon, assuming you have them assigned a poly_id:

WITH
    polys(poly_id, geom) AS (
        VALUES  (1, 'POLYGON((0 0, 0 5, 5 5, 5 0, 0 0))'::GEOMETRY),
                (2, 'POLYGON((10 10, 10 15, 15 15, 15 10, 10 10))'::GEOMETRY)
    )
SELECT  polys.poly_id,
        ST_ClusterKMeans(pts.geom, 4) OVER(PARTITION BY polys.poly_id) AS cluster_id,
        pts.geom
FROM    polys,
        LATERAL ST_Dump(ST_GeneratePoints(polys.geom, 1000, 1)) AS pts
ORDER BY
        1, 2
;

ST_ClusterKMeans on point sets, partitioned by polygon id.

To reference individual clusters, use the partition key poly_id plus cluster_id, e.g. to get point counts per cluster:

SELECT poly_id,
       cluster_id,
       COUNT(*)
FROM   <above_query_or_derived_table>
GROUP BY
       poly_id, cluster_id
ORDER BY
       poly_id, cluster_id
;

returns

 poly_id | cluster_id | count 
---------+------------+-------
       1 |          0 |   252
       1 |          1 |   246
       1 |          2 |   253
       1 |          3 |   249
       2 |          0 |   252
       2 |          1 |   246
       2 |          2 |   253
       2 |          3 |   249
(8 rows)


This works best, and most performant, on base type partition keys (e.g. INT), but if necessary you can use the geom column to partition by.

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.