ST_ClusterKMeans
Name
ST_ClusterKMeans — Windowing function that returns integer id for the cluster each input geometry is in.
Synopsis
integer
ST_ClusterKMeans
(
geometry winset
geom
, integer
number_of_clusters
)
;
Description
Returns 2D distance based k-means cluster number for each input geometry. The distance used for clustering is the distance between the centroids of the geometries.
Availability: 2.3.0 - requires GEOS
Examples
Generate dummy set of parcels for examples
CREATE TABLE parcels AS
SELECT lpad((row_number() over())::text,3,'0') As parcel_id, geom,
('{residential, commercial}'::text[])[1 + mod(row_number()OVER(),2)] As type
FROM
ST_Subdivide(ST_Buffer('LINESTRING(40 100, 98 100, 100 150, 60 90)'::geometry,
40, 'endcap=square'),12) As geom;
Original Parcels
|
Parcels color-coded by cluster number (cid)
SELECT ST_ClusterKMeans(geom, 5) OVER() AS cid, parcel_id, geom FROM parcels; -- result cid | parcel_id | geom -----+-----------+--------------- 0 | 001 | 0103000000... 0 | 002 | 0103000000... 1 | 003 | 0103000000... 0 | 004 | 0103000000... 1 | 005 | 0103000000... 2 | 006 | 0103000000... 2 | 007 | 0103000000... (7 rows)
|
-- Partitioning parcel clusters by type SELECT ST_ClusterKMeans(geom,3) over (PARTITION BY type) AS cid, parcel_id, type FROM parcels; -- result cid | parcel_id | type -----+-----------+------------- 1 | 005 | commercial 1 | 003 | commercial 2 | 007 | commercial 0 | 001 | commercial 1 | 004 | residential 0 | 002 | residential 2 | 006 | residential (7 rows)