PostGIS Cookbook - Second Edition

How to do it...

We will then populate the centroid table by generating the cluster ID for each geometry in chp03.earthquakes using the ST_ClusterKMeans function, and then we will use the ST_Centroid function to calculate the 10 centroids for each cluster:

INSERT INTO chp04.earthq_cent (the_geom, cid) ( 
  SELECT DISTINCT ST_SetSRID(ST_Centroid(tab2.ge2), 4326) as centroid,  tab2.cid FROM( 
    SELECT ST_UNION(tab.ge) OVER (partition by tab.cid ORDER BY tab.cid)     as ge2, tab.cid as cid FROM( 
      SELECT ST_ClusterKMeans(e.the_geom, 10) OVER() AS cid, e.the_geom       as ge FROM chp03.earthquakes as e) as tab 
  )as tab2 
); 

If we check the inserted rows with the following command:

SELECT * FROM chp04.earthq_cent; 

The output will be as follows:

Then, insert the ...